It’s a very old bad thing of SQLServer, but each time I need this I have to loose an hour to find the correct syntax.
Open Microsoft Query Analyzer if you have SQL Server 2000 or Microsoft Management Studio if you are using SQL Server 2005; note that you can manage SQLServer 2000 servers from Mirosoft Management Studio.
It’s very important to stop all activities on database, cutting transaction log file during a transaction it’s not a very good idea.
Create a new query and copy this command:
BACKUP LOG DATABASE_NAME WITH TRUNCATE_ONLYExample:
BACKUP LOG CIAdb WITH TRUNCATE_ONLY
Now once you have truncated transaction log file, you need now to shrink with a standard “compact database”.
Otherwise if you want to use a direct command as command above, copy into a query field:
DBCC SHRINKFILE (DATABASE_LOG_FILE_NAME , LOG_SIZE_YOU_WANT_TO_REACH)Example:
DBCC SHRINKFILE (CIAdb_log , 1)
Other way is to use a nice tool made by a CodeProject user, ShrinkingSQLServerTransLo
Bye
