Archive | DBCC RSS feed for this section

Checking for Instant File Initialization

6 Jun [url=http://www.cartoonbucket.com/cartoons/road-runner-teasing-wile-e-coyote/][img]http://www.cartoonbucket.com/wp-content/uploads/2015/07/Road-Runner-Teasing-Wile-E.Coyote-600x489.jpg[/img][/url]

Background
When SQL Server extends data or log files it fills the newly-acquired space with zeroes, to overwrite any data that may have been there beforehand. This action takes place when a database is created, restored, files are extended or added.
This can take a long time, depending upon the amount of space being initialised. This is one good reason for sizing your file requirements accurately and choosing when to make any extensions, rather than having the system perform them when the system is probably busy and holding up any number of processes.

What is Instant File Initialization?
Instant File Initialization (IFI) removes the zero-fill operation, so once the space is allocated it is immediately available. This can save a considerable amount of time.

How do I check for IFI?
One way of being notified of the status of IFI is via SQLCop, a free add-on that can be integrated within Redgate’s SQL Test framework.

SQLCop Error Message


However, this requires that xp_cmdshell is enabled and so might not be suitable for all environments.

Another method is via Brent Ozar’s ‘sp_blitz’ procedure. For versions from SQL Server 2014 SP2 this is fine, as it reads the SQL Server error log for information message relating to IFI.

EXEC sys.xp_readerrorlog 0, 1, N'Database Instant File Initialization: enabled';

Response from xp_readerrlog

From SQL Server 2008R2 SP1 a DMV has been available, which shows (amongst other things) if IFI is enabled and the Service Account, which is required should you need to enable IFI.

SELECT  d.servicename ,
		d.service_account ,
		d.instant_file_initialization_enabled
FROM    sys.dm_server_services AS d;

Response from DMV

Caveats
IFI does not work against the log file – this must zero-fill as part of normal operation. It pays to size your log file correctly.

IFI does not work if Transparent Data Encryption (TDE) is used.

IFI does not work on sparse file (for example – database snapshots).

Trace flag 1806 disables IFI.

Because IFI does not overwrite newly-acquired disk space, it is technically possible to see the data within the deleted files that occupy this new disk space. This isn’t a trivial task (with DBCC PAGE) but may need to be considered.

Additional Links
sp_blitz
SQLCop
IFI and a DBCC PAGE example
Microsoft Docs on IFI

Checking SQL Backups Automatically

25 Jan

Background
Untested backups. An awful lot is said about taking regular backups, although an inordinate number of people out there appear to ignore it. What is less talked about is actually checking that the backups are usable – it’s no good having a series of backups for that fateful day to discover that when they’re needed that they’re useless.
Continue reading

Compress All DB Files on a Server Instance

30 Apr

Or rather, on a Test or Development server instance. If you need to do this to a Live server, then you have larger issues.

Most sites have servers for Developers and DBAs to work their dark magic, as well as various test servers for the next level of testing. These servers do, of course, have databases that nobody admits to creating and other more recognisable databases that have had huge amounts of test data inserted, updated, removed and then generally left, like a bloated whale carcass – taking far more space then they now need.

There are several options in these cases:

1. Remove the databases that are no longer required.
This isn’t a quick fix, when a server has run out of space and all development has come to an abrupt halt. The database might have a use that only a few people are aware of, and they’re off on holiday, in meetings or just ignoring my stroppy emails. The best solution in such cases is to take the database offline and wait a few days, so not an immediate fix for space shortage.

2. Move database files.
Also not necessarily an option. Some setups are in specific locations for a reason and there might be no viable storage anywhere else for that server.

3. Shrink the databases.
The most common option for these servers, but some can have a large number of files, so using SSMS for this can be rather tedious.

4. Ask for more Storage.

Hydra2
Yeah, right.

These servers aren’t treated well, but that is their purpose. It saves the QA and Live servers from a lot of grief later in the development cycle.

So, I had a look around for something that I could run against a server instance and would compress all of the databases for me. There are a few bits and pieces around, but nothing that quite did what I was after.
What I wanted was something that would give me some choice over what I compressed, because sometimes it is simply a waste of time to compress a very large file for for a little gain.

So, I have written something, stored it as a plain text file and it can be downloaded from here.

It doesn’t backup databases beforehand, nor does it check the health of the database (before or after). I was after something that performed the basics and saved me a lot of time.

And if you think this routine has solved all of your problems on your mission-critical systems, you really need to read this Paul Randal – Why You Should Not Shrink Your Data Files. The inside of your database will look like the car under those tank-tracks.