MS Sqlserver 2000 transaction log shrink step

First, review the transaction log size prior to the shrinking process.

USE [YourDatabaseNameHere]
GO
SELECT *
FROM sysfiles
WHERE name LIKE '%LOG%'
GO
 

Second, set the database recovery model to 'simple'. 

USE [YourDatabaseNameHere]
GO
ALTER DATABASE [YourDatabaseNameHere] SET RECOVERY SIMPLE
GO
 

Third, issue a checkpoint against the database to write the records from the transaction log to the database.

USE [YourDatabaseNameHere]
GO
CHECKPOINT
GO
 

Fourth, truncate the transaction log.

USE [YourDatabaseNameHere]
GO
BACKUP LOG [YourDatabaseNameHere] WITH NO_LOG
GO
 

Fifth, record the logical file name for the transaction log to use in the next step.

USE [YourDatabaseNameHere]
GO
SELECT Name
FROM sysfiles
WHERE name LIKE '%LOG%'
GO 

Sixth, to free the unused space in your transaction log and return the space back to the operating system, shrink the transaction log file.

USE [YourDatabaseNameHere]
GO
DBCC SHRINKFILE ([FileNameFromPreviousStep], [NeededFileSize])
GO
 

Seven, review the database transaction log size to verify it has been reduced.

USE [YourDatabaseNameHere]
GO
SELECT *
FROM sysfiles
WHERE name LIKE '%LOG%'
GO

Next Steps

• Review your key SQL Server databases to determine if the transaction log growth is out of control.
• Review this code and modify it for one of your databases.
• Once the scripts are modified, test the scripts in a test environment to ensure they meet your needs.
• Schedule time to shrink your databases and communicate the configuration changes.
• Continue to monitor the database sizes and the available disk space on your servers.