Sql-server – Should I consider shrinking or truncating a big LDF file

databaseshrinksql server

I apologize for asking a question that gets asked a lot.

I have a database that has an 80GB LDF file. MDF is 230MB. I have never shrunk or truncated the database. I do not know much about shrink and truncate.

It is a SQL Server 2008 R2 Database. When I click SHRINK the wizard tells me I have:
81094.63 MB in currently allocated space
104.89 MB (0%) in available free space

I am also not sure about checking the "reorganize files before releasing unused space sql server" and what and if I should set the "maximum free space in files after shrinking" percentage field.

If I should shrink or truncate, what are the thoughts around scheduling a shrink or truncate job?

Thanks in advance for any tips or suggestions provided.

Best Answer

The biggest problem here seems to be that you aren't backing up that database, and therefore the TRN logs are not being truncated.

This means you have ALL transactions since the creation of that database still stored in the TRN log. Hence, if the data changes a lot but doesn't increase (ie, UPDATE commands instead of INSERT), that is why the LDF is so gigantic compared to the MDF.

Perform a full backup of the database using SQL Management Studio, a SQL management plan (recommended!) or some other backup method. Also consider setting up TRN log backups at the same time.

Related Topic