Auto Shrink Problem

A few months back, on one of our SQL Server 2005 system, our ETL process would failed and in the application log, we would get the following error:

Error: 1105, Severity: 17, State: 2

Could not allocate space for object ‘[Table_Name]’ in database ‘[Database_Name]’ because the ‘PRIMARY’ filegroup is full.

When seeing this error, my immediate thought was that we might have run out of disk space or the maximum file size that was set on the database Autogrowth setting has been reached. But then when I check those things, those were not the case. We seemed to have more than enough disk space to accommodate the new data, and both the data and log current file size were far below the specified maximum file size.

What could cause this error? So I decided to re-run the ETL process and monitor it. During one of the ETL process, one weird thing that I noticed is that in the middle of the ETL process, the target database data file size suddenly reduced in size, and not long after that, the ETL process would stopped with the above error. Granted that in one of the ETL steps, we truncate data from several of our existing tables, but I was not expecting to see the database data file shrink. Seeing this behavior, I quickly looked at the target database options and sure enough the Auto Shrink option for the database is turned on.

Auto Shrink feature could help to release free space that you have on your database file automatically. The problem is that there is no additional settings that allow you to control on when the Auto Shrink feature should run. In my case, the Auto Shrink shrank the database file during the ETL run and that was not good. So I turned off the Auto Shrink option on the database, and since then I have not gotten the above errors on subsequent ETL runs that we have.

Some Notes:

• Paul Randall (blog | twitter) explains it best on this article in regards to why we should turn off Auto Shrink option from our database.
• Microsoft best practices recommendation is to set the Auto Shrink option off, based on this article.

Patching a Punctured Aerobed

Aerobed is a great invention. It is one of those "great to have" gizmo in the closet. If you have family members/guests coming over for the night, all you need to do is to take the Aerobed out from the closet, plug it into the power outlet, flick a switch ...lo and behold in less than 5 minutes you have an extra bed. It is not as comfortable as a spring bed (although some people might argue that), but it is definitely much more comfortable than sleeping on a sleeping bag, or a couch (in some cases).

Air Leak

One of the worse things that can happen to an Aerobed is a puncture hole. Even the smallest hole can render the Aerobed useless. This actually happened to my Aerobed. Every time I inflate the Aerobed, within a few hours it would sagged so badly that I can not use it comfortably.

Fixing the Leak

Normally, Aerobed comes with a repair kit. But it seemed that I have misplaced mine. So I ended up using:

• Kwik Tek Vinyl Repair Kit (the kit contains a tube of glue like substance and several vinyl patches)
• Eclectic Shoe Goo Clear

As for patching the small hole that was on the Aerobed, I follow the following steps:

• Clean the area around the small hole on the Aerobed
• Apply generous amount of the glue from the Kwik Tek Vinyl Repair Kit to one of the vinyl patch. Then cover the small hole with the vinyl patch.
• Put some weight on the patch and let it sit for about 24 hours. I used the weight of several books. (Notes: After 24 hours, I tried to inflate the Aerobed, however, I still notice some air leak around the edges of the vinyl patch).
• So, I applied generous amount of Eclectic Shoe Goo around the edges of the vinyl patch, and let it sit for another 24 hours.

After I allow the Eclectic Shoe Goo to dry, I inflated the Aerobed and use it for several days. I must say that so far things are looking good. I did not notice any air leak coming from the area of the hole. Also for those several days, the Aerobed stayed inflated and firm.

Some Notes:

• I must say, if the hole on the Aerobed is really small, I think we can fix the Aerobed by just applying a generous portion of Shoe Goo.
• The glue from the Kwik Tek Vinyl Repair Kit and the Shoe Goo have a strong odor, so you might want to use them in well ventilated room.

SharePoint 2010 Usage Blob

A while back I wrote something about the existence of SharePoint usage information in Blob format, and Microsoft actually has a sample code on how to parse the blob. That blog entry can be found here. That’s actually for SharePoint 2007. Actually I was able to utilize that and build a small application that would output the SharePoint usage report. It’s been working pretty well actually.

Now with SharePoint 2010, I was not sure if it still has the usage information in Blob format. I tried to find some documentations online, but could not. So I just give it a try and test it. Happy to report that it seems SharePoint 2010 is still storing usage information in Blob format. The only thing that I am not sure about is whether or not Microsoft will continue to have this.

One thing to note is that one of the new big features that SharePoint 2010 has is the totally redesign usage reports from SharePoint 2007. I must say they look much better that SharePoint 2007 usage reports. In SharePoint 2007, it seems that usage report is an afterthought. But in SharePoint 2010, they seems to really think through the usage report feature well. I would really suggest to give it a looksie.

Related Posts:

• SharePoint Designer
• SharePoint Sample Usage Blob Parser Error

Cleaning Backup and Restore History

Every time you do backup and/or restore on your SQL server, entries are made to several tables in the msdb database. These things can really add up really quickly, especially if you do a lot of backups (like backing up the transaction log every 15 minutes) or restores (hopefully those restores were done on your development servers, instead of production servers). Obviously, this will cause your msdb database to grow over time. In addition, overtime, if you are using backup/restore UI on SQL Server Management Studio, you might notice slower performance when trying to retrieve the backup history.

By default, SQL Server would maintain the backup and restore history indefinitely. It is a good idea to clean up these backup and restore history periodically. There are a couple of ways of doing this.

Stored Procedures

There are a couple of stored procedures in msdb database that can be use to clean the tables in msdb database related to backup and restore.

sp_delete_backuphistory

This stored procedure accepts one parameter, which is the oldest_date (date time data type). This stored procedure would delete backup and restore history that are older than the date time value that you’ve specified on the oldest_date parameter.

EXEC msdb.dbo.sp_delete_backuphistory [@oldest_date =] ‘oldest_date’

For example, let say that you want to delete backup and restore history that are older than April 1, 2010, you can use the following query:

EXEC msdb.dbo.sp_delete_backuphistory ‘4/1/2010’

sp_delete_database_backuphistory

This stored procedure can be use to delete backup and restore history for a given database. It accepts one parameter, which is the database_name.

EXEC msdb.dbo.sp_delete_database_backuphistory [@database_name=] ‘database_name’

For example, let say that you want to delete backup and restore history related to AdventureWorks database, you can use the following query:

EXEC msdb.dbo.sp_delete_database_backuphistory ‘AdventureWorks’

Both stored procedures would delete entries from the following tables:

• backupfile
• backupfilegroup
• restorefile
• restorefilegroup
• restorehistory
• backupset
• backupmediafamily
• backupmediaset
• logmarkhistory (by way of delete trigger on backupset table).

For more information on what these tables are used for, you can go here (http://msdn.microsoft.com/en-us/library/aa260604(SQL.80).aspx).

Maintenance Plans

Alternatively, you can create maintenance plan to delete backup and restore history. All you need to do is to create maintenance plan that include History Cleanup Task. One thing to note is that History Cleanup Task does not only delete the backup and restore history, but also delete SQL job history and maintenance plan log. It executes these three stored procedures:

• sp_delete_backuphistory
• sp_purge_jobhistory
• sp_maintplan_delete_log

Take Away

It is a good idea to clean backup and restore history periodically. You can either create and schedule maintenance plan to run on regular basis or create and schedule SQL job using sp_delete_backuphistory stored procedure.

Backup with Copy_Only Option

Copy_Only option was introduced as one of the backup option starting with SQL Server 2005. It is a pretty useful backup option that I have been using it for a while now. When you back up your database or log with Copy_Only option, it will back up the database or log, and more importantly it will not disturb the backup sequence/routine that you might already have.

How is this useful?

Let say that you have established a routine in which you do a full backup on your database every once a week on Monday morning at 7:00 AM. You also have set up a differential backup to be done once a day at 8:00 PM. Then come Wednesday morning, your application vendor/developer manager is asking for the latest backup of your database to help to troubleshoot an application problem.

What to do?

There are several things that you can do. First you can immediately take a differential backup, and send the Monday’s full database backup and the newly created differential backup to your application vendor/developer manager.

Alternatively, you can also immediately take a full database backup, and send that full database backup to your application vendor/developer manager. However, one thing that you need to do is you need to make sure that you keep that full database backup. It is because the subsequent differential backups will depend on that full database backup that you’ve just take. (I guess a little bit of refresher is in order here. Differential backup contains changes that happen since the last full backup.) It’s just one more thing to worry about.

This is where the Copy_Only option can come in handy. In this scenario, you can use the Copy_Only option when taking the full database backup. The advantage of this is that this will not affect the subsequent differential backups. Those differential backups will still be based on the Monday’s full backup that you have. Let say that on Friday morning, you encountered a data corruption on that database. And you want to restore up to the Thursday differential backup. You can just restore the database from the Monday’s full backup and then restore the Thursday differential backup, and you should be golden.

Demonstration (Without Copy_Only option)

For this demonstration I am using AdventureWorks database. Let say that I have the following backup routine:

• Full Backup #1
• Differential Backup #1
• Differential Backup #2
• Full Backup #2
• Differential Backup #3

I can use the following script:

USE [master];

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Full_Backup_1.bak'
WITH NAME = 'Full Backup #1';

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\ Backup\AW\Diff_Backup_1.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #1'

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\ Backup\AW\Diff_Backup_2.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #2'

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\ Backup\AW\Full_Backup_2.bak'
WITH NAME = 'Full Backup #2';

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\ Backup\AW\Diff_Backup_3.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #3'

Now, if we try to restore the database from Full Backup #1 and then restore the Differential Backup #3, using the following script:

USE [master];

RESTORE DATABASE AW_Restore
FROM DISK = 'C:\Backup\AW\Full_Backup_1.bak'
WITH NORECOVERY,
MOVE 'AdventureWorks_Data' TO 'C:\Data\AW\AdventureWorks_Data.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Data\AW\AdventureWorks_Log.ldf';

RESTORE DATABASE AW_Restore
FROM DISK = 'C:\Backup\AW\Diff_Backup_3.bak'
WITH RECOVERY;

As expected, we will get the following error:

This differential backup cannot be restored because the database has not been restored to the correct earlier state.

Now, let’s look at why this is happening. Try running the following query:

SELECT name, type, is_copy_only, first_lsn, differential_base_lsn
FROM msdb..backupset
WHERE database_name = 'AdventureWorks'
ORDER BY backup_start_date ASC

The result is as follow (you might get a different set of result):

name	type	is_copy_only	first_lsn	differential_base_lsn
Full Backup #1	D	0	260000000007500037	NULL
Differential Backup #1	I	0	260000000009700034	260000000007500037
Differential Backup #2	I	0	260000000011200001	260000000007500037
Full Backup #2	D	0	260000000011600037	NULL
Differential Backup #3	I	0	260000000013700034	260000000011600037

You will notice that the Full Backup #1 has first_lsn of 260000000007500037. (LSN is Log Sequence Number). And if you noticed on the differential_base_lsn, the Differential Backup #1 and Differential Backup #2 are also having the same LSN of 260000000007500037. This would indicate that the Differential Backup #1 and Differential Backup #2 are based on Full Backup #1. You will need Full Backup #1 in order to restore from Differential Backup #1 or Differential Backup #2.

Notice also that once you take the Full Backup #2, the subsequent differential back up (Differential Backup #3), has a different base LSN than the Differential Backup #1 or Differential Backup #2. In order to restore from Differential Backup #3, you need Full Backup #2.

Demonstration (With Copy_Only Option)

Let say that I have the following backup routine:

• Full Backup #1
• Differential Backup #1
• Differential Backup #2
• Full Backup #2 (With Copy_Only Option)
• Differential Backup #3

I can use the following script:

USE [master];

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Full_Backup_1.bak'
WITH NAME = 'Full Backup #1';

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Diff_Backup_1.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #1'

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Diff_Backup_2.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #2'

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Full_Backup_2.bak'
WITH NAME = 'Full Backup #2 (With Copy_Only)', COPY_ONLY;

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Diff_Backup_3.bak
WITH DIFFERENTIAL, NAME = 'Differential Backup #3'

Now, if we try to restore the database from Full Backup #1 and then restore the Differential Backup #3, using the following script:

USE [master];

RESTORE DATABASE AW_Restore
FROM DISK = 'C:\Backup\AW\Full_Backup_1.bak'
WITH NORECOVERY,
MOVE 'AdventureWorks_Data' TO 'C:\Data\AW\AdventureWorks_Data.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Data\AW\AdventureWorks_Log.ldf';

RESTORE DATABASE AW_Restore
FROM DISK = 'C:\Backup\AW\Diff_Backup_3.bak'
WITH RECOVERY;

It went successfully. So what happen? Let’s try running the following query:

SELECT name, type, is_copy_only, first_lsn, differential_base_lsn
FROM msdb..backupset
WHERE database_name = 'AdventureWorks'
ORDER BY backup_start_date ASC

Here’s the result:

name	type	is_copy_only	first_lsn	differential_base_lsn
Full Backup #1	D	0	260000000015600037	NULL
Differential Backup #1	I	0	260000000017800034	260000000015600037
Differential Backup #2	I	0	260000000019300001	260000000015600037
Full Backup #2 (With Copy_Only)	D	1	260000000019500001	NULL
Differential Backup #3	I	0	260000000019700001	260000000015600037

As you can see, unlike a regular full database backup, when we use the Copy_Only option, the subsequent differential (Differential Backup #3) is still based on the Full Backup #1. Looking at the differential_base_lsn column, you can see that.

Take Away

You can use Copy_Only option with your backup for that “one off” situation, where you need to take a backup without disturbing your routine backup strategy.

Some Notes

• You can use Copy_Only option when you are doing full database backup or transaction log backup. You can not do it with the differential backup. If you put the Copy_Only option when you do the differential backup, SQL Server will ignore the Copy_Only option.
• You can not use the full backup that you take with the Copy_Only option as the base to restore subsequent differential backup.
• However, you can use the full backup that you take with the Copy_Only option as the base to restore subsequent log backup(s).