Binaryworld - Quickly Shrinking the Transaction Log ... [ SQL Server -> Backup-Restore ]

In this article I would like to discuss about the following two things:

1) About shrinking the transaction log.
2) Different ways of taking the database offline.

1) Shrinking the log:

The following is a trick for quickly shrinking the size of the transaction log which you think has become much larger than you would like and remember that this would not work if the database has more than one log file.

You can shrink the log by using sp_detach_db and sp_attach_db system stored procedures. For more information about detaching and attaching the databases refer BOL or go to www.swynk.com/friends/krishnan/movedatabase.asp.

When you detach the database using sp_detach_db, SQL Server will know that the database was cleanly shutdown and the log file need not have to be available to attach the database again. When you attach the database again SQL Server will create a new log file for you, which will be of the minimum size.

The following are the steps to do:

a) Detach the database using sp_detach_db procedure (before that ensure no processes are using the database files.)
b) Delete the log file.
c) Attach the database again using sp_attach_db procedure.

Since you are detaching and attaching the database within the same server you will not have the problem of broken logins.

For shrinking the log consisting of one or more log files, you can find very good documentation by searching the article # Q256650 (for SQL 7.0) and article # Q272318 (for SQL 2000) in Microsoft Knowledge Base.

Note: If you use the above method to shrink the log then I would recommend you to first detach the database using sp_detach_db before attaching the database because one advantage of using sp_detach_db is that the database will be shutdown cleanly.

Although it is not necessary that the database that you are attaching be previously detached using sp_detach_db but if you are doing like this then there is no guarantee that all the dirty pages from the database were written to the disk. Again if you are attaching the database using the log file too then there should be no problem because the log file will have a record of all completed transactions and a full recovery will be done to ensure database consistency when the database is attached. (But in this case the log file size will be same which is not our objective here)

2) Tip for taking the database offline:

In SQL Server 7.0 the functionality of taking the database offline is hidden from the Enterprise Manager. So you would take the database offline using T-SQL statement i.e. using sp_dboption procedure. For example to take the 'pubs' database offline you execute the following statement

sp_dboption 'pubs','offline','true'

But before taking the database offline ensure that no processes are using the database. You can make the database online by using the 'false' keyword as

sp_dboption 'pubs','offline','false'

Whereas in SQL Server 2000 you can take the database offline by using the Enterprise Manager i.e. By right clicking on the 'database' and then choose 'All tasks' and then choose 'Take offline'.

(Or) Alter database pubs set offline (replace 'offline' with 'online' to bring the database back online)

(Or) by using sp_dboption as above.