NOTE:  This is an expansion of a note that was posted on our company news page over a year ago, but I’ve recently encountered the same issue a couple of times within a week, so thought it was worth repeating.

Occasionally I’ve come across a site where backups are taken to disk = ‘NUL’. Note that’s NUL with 1 L and not NULL with 2 L’s. This allows a database in full recoverability to perform a “pretend” log backup and therefore the log file to be cleared and re-used. No resultant file is placed on disk so I’ve seen it recommended in a few places online as a quick fix where a log file has grown out of control.

The important thing to know about this is that you have just broken your recoverability chain and have no point in time recoverability until a new full backup is taken(Or if you want to be tricksey a differential backup will do the trick.. Therefore it should NEVER be part of a regular maintenance plan (especially in conjunction with a scheduled shrink….EK!).

If point in time recoverability is not important to you – use SIMPLE recoverability mode and your transaction log will be managed by SQL Server itself. If you do require point in time recoverability then schedule regular log backups and if your transaction log is still growing larger than expected then look at the activity that is causing it rather than resorting to a sledgehammer fix like using backup to disk = ‘NUL’. If you use that you have achieved nothing more or less than taking a regular log backup and then deleting the file.

Now that we’ve talked about it does, it’s worth noting that this may automatically start appearing in your backup reports without you having changed anything on the SQL Server.  You can get a list of backups that are taken on your server by using this script, and I suggest you do that, and check for any extra backups that occur.  If you start to see a bunch of log backups to the NUL device appear from nowhere, go talk to your system administrator about what new backup software they are using, and once they’ve admitted they have just put a new and shiny backup product in place you have my full permission to bash them upside the head and tell them not to change stuff on your server without talking to you.  There’s a couple of popular backup product which ‘helps’ people with their SQL backups by offering to ‘prune’ or ‘truncate’ SQL Logs.  Make sure you read what you mean before implementing this as you may be stung by the NUL backup issue.

The most annoying bit about this is it is a tickbox or checkbox which doesn’t give you information on what it will actually be doing.  System administrators never want extra logs filling up their disk space so a checkbox offering to ‘prune’ them sounds like a great idea.  Of course, a database level setting that automatically shrinks your database to stop it filling up all that disk space sounds great to a system administrator too(HINT:  It’s not).

Ever been fighting corruption on a server you don’t usually have anything to do with and suddenly get a message that C-drive is running out of space?  This happened to me today and not knowing the server I wasn’t sure if a very low amount of disk space on C-drive was normal or was something I was causing.  As I watched it I could see it continue to drop about 5-8mb a time.

I couldn’t identify where it was going, and being a client server I wasn’t keen to download some third party tool on there to tell me, so I did it the old fashioned way and looked through a bunch of likely suspects.  Eventually I checked with a colleague who went through roughly the same process before popping into the SQL log folder to check if there was huge logfiles being generated.  They were reasonably big, but more importantly was that every time one of my corrupt databases was being accessed it was generating an 8MB dump file.  There were 5-10 a minute being generated and the directory  contained some 4000 of them.  When I cleaned up those and moved off a couple of the larger error logs I was able to free up 15GB of space, and then taking the corrupted databases online and working on them one at a time reduced the buildup of stackdumps against the server to a manageable level.

When you are up to your ears in database corruption you don’t want to waste time dealing with not being able to open management studio properly etc because there is no disk space so hopefully this will be useful to someone.  By default your SQL error logs directory is going to be:

Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG

If you see similar symptoms have a look in there, clear out what you aren’t going to need and then get back to beating that corruption!

This week I was asked a ‘simple’ question by a client – why are the files for the primary databases smaller than the corresponding files on the log shipped secondary?  Obviously it needed to have something to do with either Growth or Shrink operations, so I set up a test to find the answer.

First a recap on what log shipping does.  Basically it is the process of backing up your databases and then restoring them automatically on another server, with a few bits or monitoring thrown in via management studio to make it a more manageable and versatile solution.  So any question around differences between the primary and secondary databases come down to the following question “Is it a logged operation?”  In this case the DBA that drew my attention to the question had already investigated shrinking the log file, and after the next backup,  copy and restore the log duly shrunk on the secondary server – so it appears that shrink is logged.  So that leaves growth.  It’s possible that the original database had a larger autogrowth which was later adjusted, and this change wasn’t transferred to production.  Let’s test.

Create the database with a stupid autogrowth setting:

CREATE DATABASE [Growtest]
ON PRIMARY
( NAME = N’Growtest’, FILENAME = N’D:\SQLData\growtest\Growtest.mdf’ , SIZE = 25600KB , FILEGROWTH = 50%)
LOG ON
( NAME = N’Growtest_log’, FILENAME = N’D:\SQLData\growtest\Growtest_log.ldf’ , SIZE = 18432KB , FILEGROWTH = 10%)
GO

And set it up for log shipping to Growtest_DR.  For brevity sake I won’t include the instructions on that, but if you need them there’s a pretty good step by step guide here.  Take a backup, copy and restore to make sure it’s all running properly, and check the file sizes:

As we expect both files are the same size.  Now let’s reset the autogrowth on Growtest from 50% to 10%:

ALTER DATABASE [Growtest] MODIFY FILE ( NAME = N’Growtest’, FILEGROWTH = 10%)
GO

And create a table and fill it with enough data to trigger an autogrowth:

CREATE TABLE [dbo].[SpamTable](

[randomspam] [char](8000) NOT NULL
) ON [PRIMARY]

BEGIN TRAN
DECLARE @counter INT
SET @counter = 1
WHILE (@counter <3000)
BEGIN
Set @counter=@counter+1
INSERT into dbo.spamtable values (@counter)
END

COMMIT TRAN

Then do a backup, copy, restore and check tables again:

The files on DR and production are still the same size.  What happens if we run the transaction but then roll it back instead of comitting it.  Does SQL bother to apply and rollback the transaction, or does it just remove the transaction from the logfile?

BEGIN TRAN
DECLARE @counter INT
SET @counter = 1
WHILE (@counter <500)
BEGIN
Set @counter=@counter+1
INSERT into dbo.spamtable values (@counter)
END
ROLLBACK TRAN

After a backup, copy, restore we still have the same file sizes on DR and Prod, so there goes our growth theory.  So let’s forget about what we were told when the problem was handed to us and do some shrinking.  First the test that was already performed:

DBCC SHRINKFILE (N’Growtest_log’ , 0, TRUNCATEONLY)

Sure enough the log file truncation is transferred across to the DR server:

Running out of ideas, we’ll try a shrink of the data file:

DBCC SHRINKFILE (N’Growtest’ , 0, TRUNCATEONLY)

And after a backup, copy and restore:

Finally, we have an answer.  While autogrowth changes, and log file shrinks all appear to be logged, the shrink of the data file is not.  So at some point someone has shrunk the data file on production, which is why the production data files take less space than the DR data files.  So where  tables are archived in production to free space, and the data file is shrunk(either with shrinkfile or shrink database), we need to be aware that this shrink is not transferred to the DR server.  If we expect to reclaim that space we need to refresh the logshipping by taking a full backup from Production and restoring it over the DR database.

Recently I was reviewing one of our supported instances and I saw a bunch of databases with 1MB autogrowth on the data file.  Most were quite new, and some had grown to quite a large size.  Paul Randal deals with ‘the default’ settings for autogrowth in this blog post, and says 1MB is not a good option.  No surprise there as for a highly transactional system you are going to have the data file growing all the time.  So I want to change it, but I also want to understand where it came from.

This is a managed server, so I know the model database is not going to have 1MB autogrowth set up, but just to be sure I check and confirm.  So if it’s not getting the default setting from the model database, where is it coming from?  Time to test. So when I use the Management Studio GUI it pulls settings from the model database as I’d expect.  And when I script those out using the ‘SCRIPT’ option in the create database wizard it faithfully transfers all the settings.  But what about if I just use ‘create database’ by itself.

CREATE DATABASE MODELTEST

Sure enough this grabs the default setting from when you install SQL Server – 1MB, and not the updated value in the model database.  I also noted it grabs the default data file size, but not the default log file size.  This was news to me, and led me to wonder what other settings were not being pulled from model.  So I changed a bunch of options in model on the options tab to test.

All of the other options I tested transferred properly.  Or at least as I anticipated.  So….nice one SQL Server…..you will transfer autoshrink being enabled but not transfer the log startup size or data growth settings.

From the moment I started playing with this SQL Server thing I have certain things repeated to me over and over again.  One of those is the importance of removing fragmentation from indexes.  There’s a whole bunch of stuff out there on the interweb on people rebuilding indexes and performance increasing, and so now…a few years further on in my journey it’s quite common for a client to come to me with questions about whether such and such an index has been rebuilt, or concern over fragmentation levels.  Increasingly I find myself explaining that rebuilding the index may help, but it’s quite an expensive operation, can cause blocking and may not be the problem anyway.

Now, I’m not saying that indexes shouldn’t be rebuilt or maintained.  Clearly they should, but there’s a bunch of other things that happen when we rebuild an index which may be what is actually resolving the performance issue that has raised the original flag.  Let’s look at them:

Statistics Are Updated

Whenever you rebuild an index, all the statistics related to that index are updated.  (Take Note:  This applies to rebuilding the index, if you are reorganising it the statistics are not automatically maintained).  Statistics are very powerful part of how SQL Server decides to access data.  Even with autoupdate statistics enabled statistics can get out of date really quickly, and query plans can be generated using incorrect information.  That can bring back information significantly slower than if the statistics are up to date.  The most common affect I see around this is around queries based on dates(and in particular that use a comparison against something like getdate() or CURRENT_TIMESTAMP).  Recently I saw a query which was running around 800 times a day and taking around 17 seconds per execution.  Without adjusting anything but updating the statistics that dropped down to 8 seconds.  Rebuilding the index would have resulted in the same performance improvement, but has a much higher impact on the server to achieve the same result.

Plans are recalculated

Related to the above, any plan that uses the index is recalculated based on the latest information – Fragmentation is removed(hopefully) and statistics are up to date, so SQL should choose a more appropriate plan.  Or it could be slow due to a parametrization issue which generated a plan based on one value and is now re-using the plan for other, less appropriate values.  Imagine a database which contained everyone in the worlds favourite colour.  If a plan is run the first time to get information for New Zealand population, and all subsequent queries are trying to get information about the US or Europe, SQL is obviously going to have trouble accessing the larger volumes of data in the same way it would for the smaller set of data associated with New Zealand.  Now, everyone in the world is going to be a big index to rebuild, where as in that case a recompile hint could see the same performance improvement.

So, in conclusion – yes your indexes need to be maintained, but if there is a performance issue on a server, it’s much better if you can narrow it down to a specific problem which can potentially be resolved surgically, than get out the second biggest hammer you have(behind restarting your server….and one day when I am brave enough I’ll post on what that is actually doing when it magically fixes your performance issues.)