How to decrease the Size of the Transaction Log in SQL Server 2000

Overview

Every Microsoft SQL Server 2000 database has a transaction log that records all transactions and the database modifications made by each transaction. This record of transactions and their modifications supports three operations:

• Recovery of individual transactions
• Recovery of all incomplete transactions when SQL Server is started
• Rolling a restored database forward to the point of failure

If log records were never deleted from the transaction log, the logical log would grow until it filled all the available space on the disks holding the physical log files. At some point in time, old log records no longer necessary for recovering or restoring a database must be deleted to make way for new log records.

SQL Server 2000 has to options to decrease the size of the Transaction Log:

	1.	Log truncation does not reduce the size of a physical log file, it reduces the size of the logical log and marks as inactive the virtual logs that do not hold any part of the logical log.
	2.	Log shrinking removes enough inactive virtual logs to reduce the log file to the requested size.

Example

Here is an example how boths steps can be performed:

Database is in FULL Recovery Mode

# For this example we switch to FULL Mode
USE master
ALTER DATABASE Curia SET RECOVERY FULL;
GO
The command(s) completed successfully.

# Add logical Devices for the Backup (The directories must exist!)
EXEC sp_addumpdevice 'disk', 'curia_dat',
'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\curia_dat.dat'
GO
(1 row(s) affected)
'Disk' device added.

EXEC sp_addumpdevice 'disk', 'curia_log',
'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\curia_log.dat'
GO
(1 row(s) affected)
'Disk' device added.

# Create a Backup before Truncating / Shrinking
BACKUP DATABASE Curia TO curia_dat
GO
Processed 26392 pages for database 'Curia', file 'MigrationBasisplus_Data' on file 9.
Processed 1 pages for database 'Curia', file 'MigrationBasisplus_Log' on file 9.
BACKUP DATABASE successfully processed 26393 pages in 9.756 seconds (22.161 MB/sec).

BACKUP LOG Curia TO curia_log
GO
Processed 1 pages for database 'Curia', file 'MigrationBasisplus_Log' on file 5.
BACKUP LOG successfully processed 1 pages in 0.065 seconds (0.039 MB/sec).

# Truncate the Transaction Log
BACKUP LOG Curia WITH TRUNCATE_ONLY
GO
The command(s) completed successfully.

# Drop logical Devices
sp_dropdevice 'curia_dat'
GO
Device dropped.

sp_dropdevice 'curia_log'
GO
Device dropped.

# Get the Name of the Transaction Log
USE curia
SELECT name FROM dbo.sysfiles
GO

# Shrink the physical Size of the Transaction Log to 20MB
USE curia
DBCC SHRINKFILE (MigrationBasisplus_Log, 20)
GO

Database is in SIMPLE Recovery Mode

# For this example we switch to SIMPLE Mode
USE master
ALTER DATABASE Curia SET RECOVERY SIMPLE;
GO
The command(s) completed successfully.

# Add logical Device for the Backup (The directories must exist!)
EXEC sp_addumpdevice 'disk', 'curia_dat',
'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\curia_dat.dat'
GO
(1 row(s) affected)
'Disk' device added.

# Create a Backup before Truncating / Shrinking
BACKUP DATABASE Curia TO curia_dat
GO
Processed 26392 pages for database 'Curia', file 'MigrationBasisplus_Data' on file 9.
Processed 1 pages for database 'Curia', file 'MigrationBasisplus_Log' on file 9.
BACKUP DATABASE successfully processed 26393 pages in 9.756 seconds (22.161 MB/sec).

# Truncate the Transaction Log
BACKUP LOG Curia WITH TRUNCATE_ONLY
GO
The command(s) completed successfully.

# Drop logical Device
sp_dropdevice 'curia_dat'
GO
Device dropped.

# Get the Name of the Transaction Log
USE curia
SELECT name FROM dbo.sysfiles
GO
The command(s) completed successfully.

# Shrink the physical Size of the Transaction Log to 20MB
USE curia
DBCC SHRINKFILE (MigrationBasisplus_Log, 20)
GO

More Information

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_8b51.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_876t.asp