Feeds:
Posts
Comments

Without Instant File Initialization turned on, each time a data file on SQL Server 2005 and above is created or has to grow in size, the file or portion of the file added must be zeroed out.  Depending on the size of the file this can be a very time consuming task.

By turning on Database Instant File Initialization, you are telling the operating system to trust SQL Server to properly handle this allocation.  Since you are looking to enable Instant File Initialization you should also consider setting a proper auto growth setting for your database.  Something in MB verses percentage.  This should be a fail safe, you try to implement some type of policy or procedure to monitor the amount of free space in your databases and try to grow them after hours.  What me demonstrate this on YouTube.

While there isn’t an option within SQL Server to turn this on, the process is very simple.  You simply have to add a SQL Server group to a security policy.  To find which group to add you need to run lusrmgr.msc on the workstation or server in question.  Click on Groups, marked with a RED arrow on the top left of the image below.  Then look for the group that states “specified instance of SQL Server”  This is highlighted in the image below.  The group in my experience has nearly always been “SQLServerMSSQLUser”, plus Machine Name, then Instance Name.  Take note of this group (write it down) as you will need to add it to a local security policy.  You can now close out of lusrmgr.

Next click start, run and type SECPOL.MSC.  The dialog box in the image below will open up.  Expand “Local Policies”, click on “User Rights Assignment”, then scroll down the list of Policies until you see “Perform volume maintenance tasks”.  Double click on “Perform volume maintenance tasks”.

Click on “Add User or Group”

Click on “Object Types”

Uncheck all boxes except for “Groups” and click “OK”

Next make sure that “Locations” is your local workstation or server name, not the Active Directory Domain.  Next click “Advanced” and chose “Find Now”, a list of groups should now appear.  This is where you will scroll down the list and find the group you determined from above (lusrmgr.msc).  In my example it is “SQLServerMSSQLUser$WIN-LJDS9D9PTAA$MSSQLSERVER”.  Once you have selected your group click “OK”

You should now see your group listed.

Click “OK” and then “OK” one last time.  That is it. You can now close the Local Security Policy window.  When your database grows or when you add additional files, you should no longer have the long waits of the system having to zero out the additional space.  Want to see a live demo of this, check out my YouTube Channel

Additional things to note is
1) You could just grant access to the SQL Server Service account, however if this Service Account is ever changed you will have to go back and add the new account to the Security Policy.  Which ever way works best for you.  The important thing is to just have it on.
2) By not zeroing out the file there could possibly still be data that could be read.  This is a very slight security risk and there are only a few scenarios where this could pose a risk.  The average Joe could not access the data.
3) Instant File Initialization only works for your data files, it does not have any impact to your LOG files.

Want to see a live demo of this, check out my YouTube Channel

Advertisements

Without Instant File Initialization turned on, each time a data file on SQL Server 2005 and above is created or has to grow in size, the file or portion of the file added must be zeroed out.  Depending on the size of the file this can be a very time consuming task.

By turning on Database Instant File Initialization, you are telling the operating system to trust SQL Server to properly handle this allocation.  Since you are looking to enable Instant File Initialization you should also consider setting a proper auto growth setting for your database.  Something in MB verses percentage.  This should be a fail safe, you try to implement some type of policy or procedure to monitor the amount of free space in your databases and try to grow them after hours.

While there isn’t an option within SQL Server to turn this on, the process is very simple.  You simply have to add a SQL Server group to a security policy.  To find which group to add you need to run lusrmgr.msc on the workstation or server in question.  Click on Groups, marked with a RED arrow on the top left of the image below.  Then look for the group that states “specified instance of SQL Server”  This is highlighted in the image below.  The group in my experience has nearly always been “SQLServerMSSQLUser”, plus Machine Name, then Instance Name.  Take note of this group (write it down) as you will need to add it to a local security policy.  You can now close out of lusrmgr.

Next click start, run and type SECPOL.MSC.  The dialog box in the image below will open up.  Expand “Local Policies”, click on “User Rights Assignment”, then scroll down the list of Policies until you see “Perform volume maintenance tasks”.  Double click on “Perform volume maintenance tasks”.

Click on “Add User or Group”

Click on “Object Types”

Uncheck all boxes except for “Groups” and click “OK”

Next make sure that “Locations” is your local workstation or server name, not the Active Directory Domain.  Next click “Advanced” and chose “Find Now”, a list of groups should now appear.  This is where you will scroll down the list and find the group you determined from above (lusrmgr.msc).  In my example it is “SQLServerMSSQLUser$WIN-LJDS9D9PTAA$MSSQLSERVER”.  Once you have selected your group click “OK”

You should now see your group listed.

Click “OK” and then “OK” one last time.  That is it. You can now close the Local Security Policy window.  When your database grows or when you add additional files, you should no longer have the long waits of the system having to zero out the additional space.  Want to see a live demo of this, check out my Youtube Channel

Additional things to note is
1) You could just grant access to the SQL Server Service account, however if this Service Account is ever changed you will have to go back and add the new account to the Security Policy.  Which ever way works best for you.  The important thing is to just have it on.
2) By not zeroing out the file there could possibly still be data that could be read.  This is a very slight security risk and there are only a few scenarios where this could pose a risk.  The average Joe could not access the data.
3) Instant File Initialization only works for your data files, it does not have any impact to your LOG files.

Want to see a live demo of this, check out my Youtube Channel

A friend of mine contacted me through email today having a very common problem with a query he had written.  He received an error, “cannot resolve collation conflict for equal to operator”.

Early in my career I had experienced the same issue so I quickly searched my code snippets and found where I had used COLLATE DATABASE_DEFAULT after each of the comparison columns.  For example

SELECT COLUMN
FROM TABLE_A
INNER JOIN TABLE_B
WHERE TABLE_A.COLUMN COLLATE DATABASE_DEFAULT = TABLE_B.COLUMN COLLATE DATABASE_DEFAULT

If you experience the error “Cannot resolve collation conflict for equal to operator” I hope this solution helps you out.

Have you ever tried to restore a backup over an existing database and receive an error stating the tail of the log for the database has not been backed up? Many times if you are just restoring a database into a development environment you don’t care so you just restore WITH REPLACE and move on.  However if you want to ensure that your restore contains the latest transactions from the production database, simply make a transaction log backup.  Example

(BACKUP LOG [DB_NAME] TO DISK = ‘DRIVE:\PATH\DB_NAME_LOG.BAK’)

Now lets suppose you are working with a production system and something dreadful happens to the database.   Lets assume that you have a transactional database and take TLOG backups every 10 minutes. Now lets suppose at 2:00 in the afternoon a regular TLOG backup is taken and at 2:07 there is a hardware problem where your MDF file becomes corrupt. You now have to restore from backup. What happens to those 7 minutes of transactions?

Without recovering the tail end of the log, this data is gone. Since it was only the MDF file that was corrupt or lost, we can still take a log backup even without the MDF. To do so use

(BACKUP LOG [DB_NAME] TO DISK = ‘DRIVE:\PATH\DB_NAME_LOG.BAK’ WITH NO_TRUNCATE)

The key here is the NO_TRUNCATE statement.  Using NO_TRUNCATE allows you to be able to backup the transaction log without the database being online.  If your database is online you can just take a regular transaction log backup.

If you want to see this in action, check out my video.  In this video I go rouge and delete my MDF file and demonstrate how to backup and restore the tail end of the log.

How to backup and restore the tail end of a transaction log

I had the pleasure of attending SQL Saturday 112 this past weekend.  This was my second time speaking at a SQL Saturday in Birmingham.  I was selected to present two sessions again this year in the first and second time slot.  I started off with “Getting Involved and Getting Ahead”.  This session is all about the SQL Community and how volunteering and giving of your time can pay huge dividends in your career.  You get to learn so much more and at such an advanced rate that the sky is the limit.  While helping others you gain as much or more.  The contacts you make alone are work the investment of your time.

The second session I gave was a brand new session called “Know Backups and Know Recovery”.  I presented this session in March to the Columbus GA SQL Users Group.  I really enjoy this session and hope to make it my primary session I give.  In this session I cover best practices with make FULL, DIFFERENTIAL, and TLOG backups of your databases.  I find that most people are at least doing FULL and Transaction logs.  About 25% do weekly FULL and Daily DIFFERENTIAL backups.  I also have found that most attendees have not practiced restores to include their DIFF and Transaction log backups.  In this session I demo using a script to generate the restore scripts of the last FULL, DIFF and all transaction log backups since the last respective FULL or DIFFERENTIAL backup.  I also demo FILEGROUP backups, peace meal restores and even a TAIL LOG backup and restore.

In between giving these two sessions and the remainder of the day I helped work the RedGate booth and PASS Booth.  I had a great deal of fun helping to spread the word about all things PASS and helping to field questions about many of the RedGate products.  The organization team for SQL Saturday 112 did an excellent job putting on this event.  I really like the speaker shirt and the bags the team got.

The event location at Jefferson State Community College was great.  Large classrooms and a centrally located vendor area.  There was excellent signage leading up the event, schedules posted on the doors to the rooms, a very well put together event guide and tons of drinks, donuts and coffee.

I will definitely be back to this event as long as they are willing to have me.

My slides can be found on the SQL Saturday website here.

Image

I am not one of these anti PC sorta guys, but I do love my MacBook Pro.  I have owned a PC since 1993 when I got my Packard Bell 386 with Windows 3.1.  Even with my MacBook I have 9 Windows flavored VM’s on it.  To be honest, I have never had Windows run better than as a VM on my MacBook.  Due to having so many VM’s the 8 GB of ram I had just didn’t cut it on some occasions.

With wanting to dive more into Windows Failover Clustering and wanting to experiment more with SQL 2012 and “Always On” I knew I needed a bit more horsepower to do all the playing and studying I want “need” to do.  I posed the question to a superior and got the green light to make a modest increase in memory.  I can not wait to put this 16 GB of ram to the test.

Next I hope to get a Thunderbolt enclosure with an SSD to put a few VM’s on.

In just two days SQL Saturday 107 will be in full swing.  Tomorrow begins their precon with the infamous Kalen Delaney.  Those who were lucky enough to sign up and get one of the coveted spots are in for a treat.  Thanks to the Idera ACE program I was blessed to spend three full days with Kalen and pick up a lot of new tips and tricks to help me progress in my career.

I will be presenting two sessions at SQL Saturday 107.  My “You Inherited a Neglected Database” session and “It’s TEMPDB Why Should You Care”.  I recently presented my TEMPDB session in Atlanta and had a rough go at it.  Lets just say I had a very humbling experience and learned a few valuable things as a presenter.  The biggest issue I had was being very nervous.  I had a two fellow ACE’s in the room and several other folks that I know very well or know of.  The room was also at capacity (50+ folks).  I shall be much more calm in Houston and slow down my presentation.  Even though I had a few bumps in my session, I had a very large number of people approach me telling me it was an “awesome” session.  That made me feel much better.

If you are in the Houston area, I hope you are planning to attend this wonderful FREE event on Saturday.