Top menu

Reducing the size of 'DeviceLockDB_log.LDF' transaction log file

DESCRIPTION

DeviceLock database transaction log 'DeviceLockDB_log.LDF' grows too big in a short period of time.

COMMENTS

The transaction log file stores the details of all the modifications that you perform on your SQL Server database and the details of the transactions that performed each modification. Because the transactional integrity is considered a fundamental and intrinsic characteristic of SQL Server, logging the details of the transactions cannot be turned off in SQL Server.

The SQL Server default (except local editions) leaves the databases in full recovery mode, which means that if the regular backups of the transaction log are not performed, the transaction log file grows to fill the disk, and you may not be able to perform any data modification operations on the SQL Server database.

RECOMMENDATIONS


1. You can change the recovery model from full to simple if you do not want to use the transaction log files during a disaster recovery operation.
To stop the transaction log file (.ldf) from growing:
MS SQL Studio Manager -> Right click on the database ->Properties-> Options-> Set model to simple--> OK.

Query:
sp_dboption [dbname], 'trunc. log on chkpt.', 'true'

2. Shrinking the transaction log file (.ldf):

BACKUP LOG db_name WITH TRUNCATE_ONLY

go

DBCC SHRINKFILE (db_log_name)

go

*Here db_log_name is the logical name of the transaction log being truncated

3. Shrinking the log file via detach/attach:
Detach the database--> delete/rename the log file--> attach the database - this will create a minimum size log file.
*Note that the log file must be deleted/renamed otherwise it will be re-used even though it is not specified in the attach.

Query1:
sp_detach_db [db_name]

Delete/rename the disk log file

Query2:
sp_attach_single_file_db [db_name], [filename]

Where [filename] is the name of the physical data file (.mdf).

NOTE

For more information about shrinking the transaction log files, see the following Microsoft Knowledge Base articles:
- http://msdn.microsoft.com/en-us/library/aa174538%28SQL.80%29.aspx;
- http://support.microsoft.com/kb/873235/en-us.