Moving a SQL Server 2005 transaction log

Since I don’t do this very often, I always have to refresh my memory on the process. It is easily found via your favorite search engine, but since part of my reason for this blog is to consolidate the places I have to go to look for this kind of thing, I’m going to post it here. :)

1. Document the current database permissions.
2. Make sure there are no active connections to the database. Really, you need to do this.
3. Check where the current files are located before you detach the database.
4. Detach the database. In SQL Server Management Studio (SSMS), just right-click the database and choose Tasks > Detach… and click OK. The database will disappear from SSMS (gasp!).
5. Move the transaction log file.
6. Back in SSMS, right click Databases and pick Attach…, click Add, find your .mdb file and click OK.
7. In the dialog on the bottom part (database details), you will see the log file has Not Found under the Message column. Fix the pathname of the log file to the new location, use the … button if you like or just type it in. The error message should disappear.
8. Click OK. Your database is back.
9. Check the database permissions and fix them up as needed from the documentation you created earlier.

Shrinking SQL Server 2005 log files – part deux

In Shrinking SQL Server 2005 log files I gave some standard approaches to shrinking transaction logs. I was having trouble making those work on a development server, due in part to an extreme shortage of disk space. I came up with a neat little trick that seems to work pretty well, with some caveats.

The trick is to change the database recovery model from Full to Simple, change the recovery model back to Full, then do the dbcc shrinkfile. (The recovery model is found in the database’s properties in SQL Server Management Studio.) I think this works because changing the model to simple clears the committed transaction log entries, effectively emptying the transaction log if it is not actively being used (in which case this is probably not a good idea anyway). I should note that in case Something Bad Happens™, it would be prudent to take a backup of the database first if possible.

Hopefully the combination of the previous article and this little trick will help you get those transactions logs back under control.

Shrinking SQL Server 2005 log files

Note: for the purposes of this article I am assuming a full recovery model (which is the default).

I have occasionally had database transaction logs grow out of control on SQL Server 2005, e.g. 100M of data and 5G of transaction log. (Specifically, SharePoint configuration databases have done this to me a number of times.) This can affect performance and will most certainly affect backups, aside from obviously consuming inordinate amounts of disk space. You might be thinking “don’t autogrow the transaction log”, but that does not really solve the problem, it simply shortens the amount of time before you have to deal with it. :)

A transaction log has entries for each change you make to the database since the last backup. Internally, the transaction log file is divided into sections called virtual log files. When the current virtual log file fills up and a new one is needed, SQL Server 2005 will allocate the virtual log file that is closest to the beginning of the physical file(s). Under normal circumstances, following a database or transaction log backup, most of the virtual log files will be considered inactive, especially those at the end of the transaction log file. In such cases, if you tell SQL Server to shrink the files you get most of the unused disk space freed. What sometimes happens, though, is that a transaction log entry is still active in a virtual log file closer to the end of the physical file. In such cases, you may only see a small amount of file space in use because there are only one or a few virtual log files in use, but because of the location of the virtual log file within the physical file you cannot shrink the physical file.

To resolve this, first open SQL Server Management Studio to your database instance, and under Management open the Activity monitor. Look at the applications holding locks and shut them down or otherwise prevent them from maintaining a connection so that you have nothing actively using the database but your own login. Then open a new query and execute the following statements, one at a time.

BACKUP LOG database_name TO DISK = ‘X:\some\backup\folder\database.bak‘
DBCC SHRINKFILE (logical_database_log_name, 10) WITH NO_INFOMSGS

If you do that and the file does not shrink, or does not shrink very much, run the statements again; you may want a new backup filename. For some odd reason, it sometimes takes two runs to get a result. You read that right; even Microsoft’s own knowledgebase says to do the same (article 907511).

Another approach that has sometimes worked for me when the above does not:

• Assign a second log file
• Migrate all of the log to the 2nd log file
• Shrink the transaction log files
• Migrate all of the 2nd log back to the first
• Remove the second log

You can do all of this through SQL Server Management Studio.

Update: Please see my article Shrinking SQL Server 2005 log files – part deux for yet another approach.

Also, if you are not aware of it, a good practice is to create a maintenance plan that backs up the transaction logs on a periodic basis. I usually set up daily maintenance plans that backup the transaction logs, shrink the files, do index rebuilds and update statistics, then schedule these to run at a time least likely to interfere with anything but be done before users start their day.