2

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.

Improve this question
1

1 Answer 1

Reset to default
2

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.

Improve this answer
3
  • Should I not consider SHRINK or TRUNCATE at all?
    – obautista
    Jul 24, 2011 at 13:45
  • 1
    A full database backup truncates the TRN logs for you. After than you can definitely do a SHRINK on the LDF files (first check in the database properties how much free space there is to ensure the backup did indeed truncate properly). If you don't care about backup, you could also change the database mode to simple, which removes TRN logs from the equation completely.
    – Ashley
    Jul 24, 2011 at 22:28
  • 2
    You need to take transaction log backups before the 8 GB of space in the log file will show up as free, OR set the recovery model of the database to SIMPLE. Do a search for Tripp shrink log files to get a full understanding of whether you should shrink your log files.
    – Jason Cumberland
    Jul 26, 2011 at 21:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .