MySQL 5.5.6 is not ACID compliant, here is an example:

mysql> drop table if exists mytable1;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> create table mytable1 (idontfeellucky int) ;

Query OK, 0 rows affected (0.44 sec)

mysql> show create table mytable1;

+——-+—————————————————————————————-+

| Table | Create Table |

+——-+—————————————————————————————-+

| mytable1 | CREATE TABLE `mytable1` (

`idontfeellucky` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+——-+—————————————————————————————-+

1 row in set (0.00 sec)

mysql> insert into mytable1 values (1);

Query OK, 1 row affected (0.90 sec)

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

mysql> show tables;

+—————-+

| Tables_in_test |

+—————-+

| exmpl_tbl |

| mytable1 |

| users |

+—————-+

3 rows in set (0.00 sec)

mysql> select * from mytable1;

+——+

| idontfeellucky |

+——+

| 1 |

+——+

1 row in set (0.00 sec)

mysql> select version();

+———–+

| version() |

+———–+

| 5.5.6-rc |

+———–+

1 row in set (0.00 sec)

If you need your relational sql database to be ACID compliant in areas like this, you should consider other options such as PostgreSQL which is also free but fully supports this feature.  Or, you could go with DB2, Oracle etc.

Thanks,

Brad Arrington, Unix/MySQL/PostgreSQL DBA

When I investigate a new SQL Server instance for the first time, I perform a number of standard checks to get a handle on the current health of the system. One of the checks I like to perform is a VLF count on each transaction log file associated with each database on the system.

Right now, some of you may be wondering what I’m talking about and some of you may be nodding your head in confirmation. Neither of these responses is surprising, as this topic is somewhat obscure, unless you just like reading about SQL Server internals as much as I do.

What is a VLF?

The transaction log of a SQL Server database is always most evident as one or more physical files on disk; however, the transaction log is internally managed as a set of virtual log files (VLFs) within the physical files. This type of internal configuration makes it easier for the storage engine to manage the transaction log and control the reuse of internal space efficiently and effectively.

How is the Number of VLFs Determined?

When you create the transaction log for your database, it will have between 2 and 16 VLF’s. The number of VLF’s is determined by the size of the log when it is first created. SQL Server uses a simple formula to determine the number of VLF’s created.

If the file size is between 1 MB and 64 MB, the storage engine will allocate 4 VLFs inside the file. If the file size is greater than 64 MB, but less than or equal to 1 GB in size, the storage engine will allocate 8 VLFs inside the file. If the file size is greater than 1 GB, then the storage engine will allocate 16 VLFs inside the file.

This formula is also used when “growing” the transaction log too. Furthermore, the transaction log will always grow and shrink along full VLF boundaries. It’s the smallest unit of change allowed within the file.

Visualizing our VLF Count

Now that we a good understanding of how the storage engine will allocate VLFs inside our transaction log, let’s take a look at how we actually see the VLFs in an active database. We’ll be using an undocumented DBCC command to have a look.

First, let’s create a simple database using the following settings.

USE [master]

GO

CREATE DATABASE [myTestDatabase] ON PRIMARY

( NAME = N’myDatabase’, FILENAME = N’C:\myDatabase.mdf’

,SIZE = 3072KB

,MAXSIZE = UNLIMITED

,FILEGROWTH = 1024KB )

LOG ON

( NAME = N’myDatabase_log’, FILENAME = N’C:\myDatabase_log.ldf’

,SIZE = 1024KB

,MAXSIZE = 2048GB

,FILEGROWTH = 1024KB)

GO

Now that the database has been created, let’s take a look at the number of VLFs that were created inside the transaction log.

We’ll be using the undocumented DBCC command called “LOGINFO” to view the VLF count for our database.

USE myTestDatabase;

GO

DBCC LOGINFO;

GO

The execution of the DBCC LOGINFO command outputs the results listed below.

The results of DBCC LOGINFO have a lot to tell us; however, to determine the number of VLFs in our database, we only need to count the number of records. Each record returned from the command represents a single VLF in our transaction log. You can see that the database we created earlier has a total of 4 VLFs.

As you can see, the formula illustrated previously has been used, and since the initial size of the log was between 1 MB and 64 MB, the storage engine created exactly 4 VLFs in this transaction log.

VLFs and Auto-growth

The SQL Server storage engine uses the same formula when allocating additional space to the transaction log file during an auto-growth event. Let’s simulate 5 auto-growth events of 1 MB each and then have a look at our VLF count.

If you remember the formula from above, you know that the storage engine will allocate 4 VLFs per auto-growth event, or a total of 20 additional VLFs.

When we execute DBCC LOGINFO again, we can see that our estimate was correct and we now have a total of 24 VLFs in our transaction log. We have 4 VLFs from the original creation of the 1 MB transaction log and we have an additional 20 VLFs from the five 1 MB auto-growth events.

At first glance, this type of behavior may not alarm you. In fact, this database may go on to have a happy, healthy life under the right workload. However, if this database is placed into production as a data warehouse or as the backend for a high transaction OLTP application then we’ll have problems.

For example, if this transaction log were to grow to 50 GB, or 51,200 MB, it would have a total of 204,824 VLFs. We got 4 VLFs during the initial file creation, then an additional 20 VLFs during the five 1 MB auto-growth events. Then, to top it all off, we got another 204,800 VLFs from the numerous auto-growth events needed to increase the log by 50 GB.

If this database continues to experience auto-growth at this rate, we can expect to see significant performance problems. We need to get this transaction log under control and implement the proper configuration changes to reign in this excessive VLF creation.

Implications and Mitigations

Simply put, having too many, or too few, VLFs in your transaction log file can seriously degrade all transaction log file related processes. These impacts will most likely occur during transaction log backups, service restarts when database recovery is running, transaction log clearing, etc. The good news is that this problem is easy to address, and with proper pre-allocation, unlikely to reoccur.

Generally speaking, if you find that you have a database transaction log with more than 50 VLFs you may want to perform some maintenance to reduce the count. Also, you’ll need to review the auto-growth settings and make the changes necessary to alleviate the problem from reoccurring in the future. The steps required to make these changes are fairly simple; however, you may need to experiment a bit in order the get the VLF count set properly.

You can use the following steps to adjust the VLF count for your transaction log. These steps should only be performed during a period of low activity.

First, perform a transaction log backup.

BACKUP LOG [myTestDatabase] TO DISK =

N’C:\myTestDatabase.bak’

GO

Once the transaction log backup has been completed, you are ready to shrink the transaction log and prepare for the pre-allocation steps.

USE myTestDatabase;

GO

DBCC SHRINKFILE(‘myTestDatabase_log’, TRUNCATEONLY)

GO

After this step, the transaction log has returned to its default initial size of 1 MB and is ready for pre-allocation.

I’m not going to go into all the factors used to determine the proper size for this transaction log. Instead, we’ll make an assumption that 60 GB is large enough, paired with consistent transaction log backups, to hold the largest individual transaction without requiring an auto-growth.

We’ll grow the transaction log to 60 GB by executing the following three queries.

ALTER DATABASE myTestDatabase

MODIFY FILE

(

NAME = myTestDatabase_log

, SIZE = 20 GB

)

GO

ALTER DATABASE myTestDatabase

MODIFY FILE

(

NAME = myTestDatabase_log

, SIZE = 40 GB

)

GO

ALTER DATABASE myTestDatabase

MODIFY FILE

(

NAME = myTestDatabase_log

, SIZE = 60 GB

)

GO

Based on our knowledge of the storage engine formula for VLF allocation, we know we will get 16 VLFs after executing each of the queries list above. This equals a total of 48 VLFs in our transaction log. When we add this to the already existing 4 VLFs, we have a total of 52 VLFs. We’re right on target.

Assuming our transaction log configuration is appropriate for our workload, and no auto-growth is required, we can expect our new VLF count to remain stable; however, if auto-growth should occur, we will add some number of additional VLFs to our transaction log. Due to this, it’s important to configure the initial size of our transaction log, and our auto-growth increment, to values that won’t cause our VLF count to spiral out of control again.

Conclusion

I encourage you to continue to research the internals of the transaction log. A thorough knowledge of this critical mechanism will be highly beneficial as you strive to enhance you own database environments.

Scott Caldwell, SQL Server DBA

In an earlier post, I introduced the concept of indispensability and I identified the following four factors as key to how indispensable you are perceived to be at work:

• Accomplishability: your ability to accomplish valued results.
• Value/Cost: the value delivered perception relative to the cost.
• Supply/Demand: the market dynamics of your position, skills, etc.
• Likeability:  how others perceive you.

In this post, I will delve into and expand upon the fourth factor: Likeability. Likeability is defined as having qualities that bring about a favorable regard from others.  Likable people tend to get along with most people.

Some of the qualities of likable people include:

• Friendly
• Realness
• Authentic
• Relevant
• Emphatic
• Honest
• Trustworthy
• Positive
• Helpful
• Realistic

Truly likeable people are liked by everyone:  Friends, family, loved ones, customers, superiors, peers and subordinates.  They are likable all around!

In his book, The Likeability Factor, Tim Sanders points out four key characteristics of likeability:

• Relevance: your capacity to connect with others’ interests, wants and needs.
• Empathy: your ability to recognize, acknowledge and experience other people’s feelings.
• Realness: the integrity that stands behind your likeability and guarantees its authenticity.
• Friendliness: your ability to communicate liking and openness to others.

One of Stephen Covey’s seven habits of highly effective people, “seek to understand then to be understood,” is a critical likeability factor.  When people sense you truly care about them, they will love you!  We simply and naturally love to be cared about.

One of the key reasons Likeability is important overall is because the choices others make about you tend to have a direct impact on the Key Quality of Life Factors:

• Your health
• Your relationships
• Your wealth

People tend to choose who they like. Think about it. You work for or with them, buy from them, vote for them, marry them, hire them, and so on.

Remember one important aspect of Likeability:  it is in the eyes of the beholder. That means you are only as likeable as others perceive you to be.

In the context of this blog series, Likeability is one of four factors of Indispensability.   If for any reason your Accomplishability is not where it needs to be, your skill’s Supply/Demand dynamic is not favorable, and your Value/Cost index is bellow 1.0, Likeability can save the day.  All things being equal, an honest and sincere 360-likeable person will be perceived to be more indispensable than the other less likable ones.

Well this brings  another blog series to a close.  I hope you kept up.  If you have, I thank you!!!  I hope you found some valuable information while reading along.  Now it’s time to develop your indispensability strategy:

1. Established what your customers expect you to accomplish.
2. Determine what your skill’s supply/demand situation is like.
3. Figure out your value/cost index.
4. Understand your likeability approach and make adjustments if need be.

Good luck!!!

The BEST is Yet to Come!

Epi Torres, CEO

In this quick presentation I’ll demonstrate how you can use a SSIS package in SQL Server 2008 to loop through multiple files of a certain type in order to load data into your database.

Thanks,

James Shropshire MCDBA, MCITP SQL Server

In an earlier post, I introduced the concept of indispensability and I identified the following four factors as key to how indispensable you are perceived to be at work:

• Accomplishability: your ability to accomplish valued results.
• Value/Cost: the value delivered perception relative to the cost.
• Supply/Demand: the market dynamics of your position, skills, etc.
• Likeability: how others perceive you.

In this post, I will delve into and expand upon the third factor: Supply/Demand.  Supply/Demand refers to the market dynamics for your skills.  The supply and demand dynamic affects job availability and the respective salaries companies are willing to pay.  When there is too much demand and not enough supply, salaries are driven up and vice versa.  These dynamics also drive the risk companies take outsourcing certain jobs.  Sometimes these imbalances create bubbles that can cause havoc in markets, especially when they burst.  There are global, national, regional and local market dynamics associated with most jobs.  Everyone should be highly aware and keep track of the market dynamics that affect their job.  There are many sources of such information.  The US Department of Labor keeps what seem to be the best and most accurate data.  Also, a number of private web sites offer job demand information that can help you keep track of the dynamics of your skill area.

Labor Statistics

One of my favorite reports is the 10-year Occupational Outlook Handbook issued by the U.S. Bureau of Labor Statistics’ Office of Occupational Statistics and Employment Projections. This handbook is updated every two years or so.   It is a very comprehensive report and covers most jobs in the US.  You can see the current copy here.

Here is a sample of a detailed report for IT Management by Industry.

Job Demand Stats

There are several web sites that aggregate job postings and share statistics on national and regional job demand for IT folks.

Indeed.com

Indeed offers a number of job demand perspectives. Here is one for job postings per capita by regions of the country.

Here is a portion of the job market competition perspective.

They also offer charts that track job postings by job type.  Here is a graph for IT Managers.

There are several more things to look at on this site.  It is pretty cool and easy to use!  Visit them here.

SimplyHired.com

This is another cool site!  This chart on their home page tracks job postings trends by hiring segments of the economy.

Here is a graph showing programming job trends by platform.

There is a lot to look at here also. You can visit the site here.

Keeping track of the supply/demand dynamics for your skill set is critical to being perceived as highly indispensable.  My advice to folks whose supply/demand dynamics are unfavorable is to crank up their efforts to ensure they do more with the Accomplishability, Value/Cost and Likeability aspects of their indispensability perception strategy.  It is critical!

Do you know and keep up with your skill market dynamics? If not, get on with it, and if necessary, adjust your strategy to ensure you’re keeping the highest indispensability perception possible!

The BEST is Yet to Come!

Epi Torres, CEO

In an earlier post, I introduced the concept of indispensability and I identified the following four factors as key to how indispensable you are perceived to be at work:

• Accomplishability: your ability to accomplish valued results.
• Value/Cost: the value delivered perception relative to the cost.
• Supply/Demand: the market dynamics of your position, skills, etc.
• Likeability:  how others perceive you.

In this post, I will delve into and expand upon the second factor: Value/Cost. The Value/Cost factor is simply the perception your employer has of your worth.  While there are intangible aspects to this perception, the key determinant is the amount of money you bring in relative to your compensation and benefits. Depending on your job, making this determination varies in its complexity and accuracy.  The perception of sales people or billable individuals is much easier to establish than that of a support person.

If you are in a job where the connection between your perceived value and your cost to the organization is not clearly evident, it may behoove you to do some homework to figure out your impact on the organization.  This is especially true if the supply/demand ratio is above 1.0.  In such cases, this factor can become a key competitive advantage to help your indispensability. Let me try to illustrate this with a simple example.

Say your job is to provide IT help desk support to engineering folks in your organization.  Engineers typically make decent salaries and work on critical projects with big impact to the organization.  If their system is down and they cannot do their work, it costs money to the organization, literally lots of money by the minute!  You need to guesstimate that cost by keeping track of how much time your support approach and efforts either preventing or solving problems saves.  It would surprise you and your superiors in many cases to discover how valuable you are, assuming you are good at doing what you do.  Keeping track of such information and providing your superiors and perhaps even your customers with regular updates can add another layer of indispensability to your position.  There are many other ways to establish how much value you bring.  Know your value and communicate it to make sure the key decision makers are fully aware of it all the time.

Do you know how much value you produce?  Go do the math — and let others know as soon as possible!   If you find it’s not enough, take action to improve and increase it.  Keep the measurements going until it looks good and then show it!

The BEST is Yet to Come!

Epi Torres, CEO

Transactional replication can sometimes be a tricky high availability option to work with.  Many companies are now using it more and more to offload reporting to a different server.  But as we know, making schema changes to the tables in a publication is something that must be done when the application changes or different business needs must be met.  The situation I have been running into more often is when one or more columns in a table need to be changed and the article needs to be updated.  If it is a small publication, the change is usually done by deleting the subscription, removing the article from the publication, updating the article, re-adding the article, re-adding the subscription and reinitializing the subscription.  But what happens when reintialization takes a long time to complete?  Sometimes we do not have the time to follow the previous method.

For example, when a publication is 500 GB in size and takes 20 hours to apply the snapshot to the subscriber it may not be viable to reinitialize even with a backup when we want to change a schema of a single article.  Whenever the publication is very large, we need to find a different way of making a snapshot of a single article.  We can do this by following the process below.

1. First, we turn off @allow_anonymous and @immediate_sync on the publication by doing the following:

EXEC sp_changepublication

@publication = ‘testpublication’,

@property = N’allow_anonymous’,

@value = ‘false’

GO

EXEC sp_changepublication

@publication = ‘testpublication’,

@property = N’immediate_sync’,

@value = ‘false’

GO

2. Then, we drop the article from the subscription.

EXEC sp_dropsubscription

@publication = ‘testpublication’,

@subscriber = ‘subscriber_name’,

@article = ‘article_we_want_to_change’

3. Next, we want to force an invalidate of the snapshot.

EXEC sp_droparticle

@publication = ‘testpublication’,

@article = ‘article_we_want_to_change’,

@force_invalidate_snapshot = 1

4. Now we can change the schema of the article we just removed from the subscription.

5. Then, we add the article we want to change back to the publication.

EXEC sp_addarticle

@publication = ‘testpublication’,

@article = ‘article_we_want_to_change’,

@source_object = ‘article_we_want_to_change’,

@force_invalidate_snapshot = 1

6. We will then want to refresh the subscription.

EXEC sp_refreshsubscriptions @publication = ‘testpublication’

7. Next we can start our snapshot agent which will snapshot only the article that we made changes to.

8. Next re-add the @immediate_sync and @allow_anonymous.

EXEC sp_changepublication

@publication = ‘testpublication’,

@property = N’immediate_sync’,

@value = ‘true’

GO

EXEC sp_changepublication

@publication = ‘testpublication’,

@property = N’allow_anonymous’,

@value = ‘true’

GO

Voilà!  We have successfully made a snapshot of a single article, eliminating the need to reinitialize the entire publication.  All the report users will be thanking you.

Tim Foley, Sr. SQL Server Database Administrator

In my last post, I introduced the concept of indispensability and I identified the following four factors as key to how indispensable you are perceived to be at work:

• Accomplishability: your ability to accomplish valued results.
• Value/Cost: the perception of value delivered relative to the cost.
• Supply/Demand: the market dynamics of your position, skills, etc.
• Likeability:  how others perceive you.

In this post, I will delve into and expand upon the first factor: Accomplishability.  Accomplishability is the perception others have of your ability to get things done.  However, it is not just your ability to get anything done. It’s your ability to get the right things done: Valued results!  Those are the results required and expected by your internal or external customers given your job function.  This factor is always critical but it becomes more so when the market dynamics of your job are not favorable.  If there is a lot more supply than demand for people with your knowledge, experience, skills, and abilities or if your salary is on the high end of the market, Accomplishability is more critical to sustaining your highest indispensability perception.  Finally, if for some reason your likeability is not the best, you must crank up the Accomplishability aspect to make up for any gaps given all other factors remain equal.

The first key to Accomplishability in this context is to identify the key valued results expected of you by your customers.  Customers are 360 degrees around you.  They include both internal and external ones when applicable as not everyone has direct external customers.  Internally, customers include your boss, your peers, and/or anyone who receives all or some of your work output be it a product or service.  Once you identify your key customers, then you can identify the set of valued results that would lead them to perceive you as indispensable.

Once you have your customers and their valued results identified, you need a strategy to deliver them.

At Remote DBA Experts, we deliver remote database administration services to our customers as either their sole DBA resource or as a supplemental resource to their internal individual DBA or team.  In these roles, we have discovered three highly valued results that our customers want us to deliver. When we deliver these results, our customers’ perception of our indispensability rises:

• Proactiveness
• Responsiveness
• Effectiveness

Proactiveness means preventing any and all possible database availability and performance problems.

Responsiveness means promptly acknowledging all customer requests, taking prompt effective action, keeping the customer informed of progress along the way, and bringing requests or issues to full closure.  Effectiveness means delivering the expected result. For Remote DBA Experts, this means delivering highly available and well-performing database systems.  This is the main focus of our DBAs, and as an organization, we must enable them to accomplish it.

Given the above, Remote DBA Experts measures Accomplishability based on these three service aspects. DBAs with the highest indispensability levels are those who have mastered all three.  Their customers love them and so do we!

The BEST is Yet to Come!

Epi Torres, CEO

There is no better strategy for job security than to become as indispensable as possible to your organization.  Indispensability is a sense or perception others have of you.  It is a belief that you would be “difficult” or even better “impossible” to replace if you were to leave your job.  Here are a few words that come to mind when I think about the term indispensable:

• Critical
• Vital
• Central
• Essential
• Necessary
• Required
• Crucial
• Obligatory
• Fundamental
• Key
• Must Have or Keep

There are many ways to develop such a reputation within your organization.  In this blog series, I plan to discuss approaches to help you develop a strategy to become indispensable in your job.  In this introductory post, I will outline the components that will make up a comprehensive approach to become and remain as indispensable as possible.

The following is a set of factors that influence how indispensable you are perceived to be by your organization. The value of each factor varies according to the situation and circumstances thereof:

• Accomplishability: your ability to accomplish valued results.
• Value/cost: the value delivered perception relative to the cost.
• Supply/Demand: the market dynamics of your position, skills, etc.
• Likeability:  how others perceive you.

In the following series of posts, I will delve into each of these factors and expand upon this subject to get you a better understanding and, ultimately, help you develop a strategy to become indispensable to your company.

I hope you will come back to read the rest of these posts.

The BEST is Yet to Come!

Epi Torres, CEO

For any SQL Server DBA who has been in the game longer than the advent of SQL Server 2005, there was one standard place to turn when you wanted to tune your database.  You would turn to the profiler!  If you had some experience with this, and had been burned by getting too small of a sample of throughput, you knew that you needed to run it for enough time to get a true sample of what your server was doing. You needed to get a true sample of both reads and writes so that you wouldn’t be skewed one way or the other when deciding how many indexes would be ideal.

However, the profiler has a fatal flaw.  It puts a bit of burden on the system.  Also, you have to run it on the production system in order to get the true test of throughput!  Or you must have a very sophisticated way of playing back the throughput on a test box that matches production.  Hmm.  Then, some DBAs starting thinking…What if I ran the trace from another box!  Viola, the server side trace.  For any production system, the server side trace is a fantastic tool for gaining statistics on deadlocks, indexes, long running queries, and a wealth of other statistical information that can help you improve the setup of your server as well as your code.  Was this the holy grail of tuning that us DBAs had been looking for?

In SQL Server 2005, Microsoft provided the Dynamic Management Views (DMVs).  These provide a constant, real-time view of the database and server.  No trace needs to be run, no data needs to be collected over time, no load on the system, no huge files to figure out where to put on another server, and no rotating file where you are trying to make sure you are not writing over the event that you are trying to capture.  All of the data is right there in the system for you!  You just have query it.  But wait, you say.  I tried to query the views and they weren’t there!   You may need to enable permissions to these views.  You do this through these commands:

• GRANT VIEW SERVER STATE to <Login>
• GRANT VIEW DATABASE STATE to <User>

A few helpful queries I have found to get information from the DMV are listed below. The following shows useful information about all indexes in a database:

SELECT object_id, index_id, user_seeks, user_scans, user_lookups

FROM  sys.dm_db_index_usage_stats

ORDER BY object_id, index_id

The following shows indexes which have never been used:

SELECT object_name(i.object_id),

i.name,

d.user_updates,

d.user_seeks,

d.user_scans,

d.user_lookups

FROM sys.indexes i

LEFT JOIN sys.dm_db_index_usage_stats d

ON d.object_id = i.object_id and

i.index_id = d.index_id and d.database_id = 5

WHERE objectproperty(i.object_id, ‘IsIndexable’) = 1 and

d.index_id is null or

(d.user_updates > 0 and d.user_seeks = 0

And d.user_scans = 0 and d.user_lookups = 0)

ORDER BY object_name(i.object_id)

The following query will tell what each connection is actually performing in SQL Server:

SELECT session_id, status, command, sql_handle, database_id

FROM sys.dm_exec_requests

WHERE session_id >=51

There is a wealth of information available on the DMVs both online and in books.  These views are a DBA’s best friend, and should be the first go-to for tuning opportunities.  There are still times when a server side trace can give more in depth information, such as when performance may differ at different times of day.  But for your average, run-of-the-mill tuning question, nothing can touch the DMVs for their ease of use and their lack of impact to the server.

Katy Park, SQL Server Team Lead