Warning
Potential for Data Loss: The steps detailed in the resolution of this article may result in a loss of critical data if not performed properly. Before beginning these steps, make sure all important data is backed up in the event of data loss. If you are unsure or unfamiliar with any complex steps detailed in this article, please contact Product Support Services for assistance.
Issue
taclogdata_log.ldf file is too big
Environment
SQL 2005
Cause
Fail to perform frequent SQL DB maintenance
Resolution
To Shrink the LOG file (.ldf) follow the procedure.
- Connect to taclogdata.mdf using SQL server management studio.
- Run the following command:
dbcc shrinkfile (taclogdata_log,100,truncateonly)
This will reduce the size (taclogdata_log.ldf) to 100 mb.
Note : You can get the logical file name for the log by running the following select statement:
(select * from sysfiles)
The following are the query results:
1 1 6400 -1 6400 2 0 taclogdata c:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\taclogdata.mdf
2 0 2624 268435456 1280 66 0 taclogdata_log c:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\taclogdata_log.LDF
If the database has the truncate log checkpoint turned off, then the transaction log file will be shrink to the target size only when the transaction log is backed up.
OR
If the truncate log on checkpoint is turned on then go to Enterprise Manager - all tasks - and click "truncate log". This will shrink the file down to the target size and will release all the unused space to the Operating System.
