Managing the growth of SQL Server .LDF file

Question

Today I see that an .ldf file of a SQL Server database is 66 GB large. MDF is only 200 MB large.

How can I disable logging in the .ldf file?

Answer 1

Your database is probably in Recovery Mode full so your best and easiest way to get it small is make Full Backup followed by incremental backups which will cut it down heavily.

If you don't want to do Incremental backups (because of lack of backup software for example) and you don't want LDF to be that big at all you need to set Recovery Mode to Simple.

To set your database logging to simple (but only if you do Full Backups of your database!).

1. Right click on your database
2. Choose Properties
3. Choose Options
4. Set Recovery mode to simple

To trim database log file do following steps:

Get the physical names of your database file (MDF) and log file (LDF): Run the following system stored procedure:

use <yourdatabasename>
exec sp_helpfile

This command will return a variety of information, including the physical size (the size column) and the path and name of your database and log files (in the filename column).

Record the name of the file from the “filename” colunm, excluding the path and file extension (e.g. if filename contains C:\sqldatabases\yourdatabase_data.mdf you want to save the string yourdatabase_data)

Truncate the database and shrink the database The following set of SQL will shrink your database and truncate the log file. File in the parmaters surrounded by <…>. Note that you’ll need the two filename values from step 1.

USE <yourdatabasename>
GO
BACKUP LOG <yourdatabasename> WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE (<yourdatabaselogfilename>, 1)
GO
DBCC SHRINKFILE (<yourdatabasedatafilename>, 1)
GO
exec sp_helpfile