Keep the size of the transaction log file in mind when you are restoring a database.

Over the weekend, the backup drive on one of our servers ran out of space. Without transaction log backups, the log grew several times until it consumed the log disk. At that point SQL choked.

We straightened out the SQL problems and things ran smoothly the rest of the weekend. Today we went to restore the backup to a test server. At this point we ran into a problem I haven’t seen before but should’ve anticipated–the backup wouldn’t restore.

A little background… We run full backups every morning and transaction logs throughout the day to a backup device which writes to a single .BAK file. The .BAK file is ~80GB. The data file is ~70GB. After all the transaction log growth, the tran log was ~50GB. The staging server had enough room for the backup. I was restoring over another database so space on the data drive wasn’t an issue. However, the log drive is only about ~50GB total and there are already a few log files on there. As you can imagine, when I went to restore the database, SQL wouldn’t let me, because even though my backup was only ~80GB, the restore process was trying to create a ~50GB log file on a disk that only had ~30GB free.

I couldn’t find any way to reconfigure the size of the log file as part of the restore. What I eventually ended up doing was shrinking the log file back down to a reasonable size in prod and then doing another full backup. I am not a fan of shrinking but this is certainly one of those times where shrinking is appropriate. Keep in mind that there are certain operations that shouldn’t be run concurrently with shrinking files. You can find out more here: http://msdn.microsoft.com/en-us/library/aa213772(SQL.80).aspx (This link is for SQL Server 2000).