Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
3  
Check out this page from Kimberly Tripp: sqlskills.com/blogs/kimberly/post/… –  user3914 Jul 24 '11 at 5:01
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
Should I not consider SHRINK or TRUNCATE at all? –  obautista Jul 24 '11 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 Steel Jul 24 '11 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 '11 at 21:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.