SQL Server 2008 ldf file size

SQL Server is smart in selecting great default options for a new database. If you can afford to lose the changes between backups to keep maintenance simple (and in most of non-critical databases it is a reasonable compromise)  and you backup your database regularly using a tool like SQLBackupAndFTP, then the “Simple” recovery model (selected by default) is appropriate for you.  However if you ever had massive data operations, you may notice that the size of your transaction log (LDF) file is huge. The reason for it is that SQL server does not automatically shrinks the size of transaction log.

To keep log file under control, it may be tempting to enable Auto Shrink option. That would be a mistake – it is an evil option that should always be off. Shrink is a heavy procedure that should only be used rarely.

Instead just run this command to decrease the size of the data and log files to leave 10 percent free space (read more):
DBCC SHRINKDATABASE (YourDatabaseName, 10)

For more precise operation use DBCC SHRINKFILE or you can just do it in Management Studio

It is not over yet! Most often shrink will increase fragmentation of your indexes. To defragment indexes, you’ll need to use ALTER DATABASE – see this link

Leave a Reply

Your email address will not be published. Required fields are marked *