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. Change the recovery model from Full to Simple to stop the transaction log file (.LDF) from growing fast:

Open Microsoft SQL Server Management Studio -> right click on DeviceLock database -> Properties -> Options -> Recovery model: choose Simple from the drop-list -> OK.

2.1. Either shrink the transaction log file (.LDF) using the following query:

USE db_name

GO

DBCC SHRINKFILE (db_name_log, 1)

GO

where db_name is the name of DeviceLock database, db_name_log is the logical name the log file you need to shrunk, and change 1 to the number of MB you want the log file shrunk to.

2.2. Or delete the transaction log file via detach/attach operation:

In Microsoft SQL Server Management Studio -> right click on DeviceLock database -> Tasks -> Detach...-> delete or rename DeviceLock database transaction log file (.LDF) -> right click on the Databases node -> Attach... -> attach DeviceLock database -> after this a new minimum size LDF log file will be created.