• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 865
  • Last Modified:

shrink log file sql server 2008

I have a log file that is ~8GB large. But it consistently has only 1% of data, as described by DBCC SQLPERF(LOGSPACE); this means, there's a lot of unused space. The db employs the full recovery model. How would I reduce the log to 1,024MB or 1GB via TSQL? I've seen code for this. But I want to see how others go about this.

Other than performing a full-backup of the db right before shrinking the file, are there any other steps to take? Should I do this during off-hours to reduce performance impact?

Are there any other things for me to keep in mind?

Thanks!

pae2
0
pae2
Asked:
pae2
3 Solutions
 
lruiz52Commented:
Check the link below for steps on how to shrink the log file;

http://help.fogcreek.com/8686/how-to-shrink-sql-server-transaction-logs
0
 
David ToddSenior DBACommented:
Hi,

The simple answer is:
dbcc shrinkfile( fileid, 1024 )
see http://msdn.microsoft.com/en-us//library/ms189493.aspx

But the last virtual log files (vlf) can be in use, so maybe you'll need to take a transaction log backup first, and maybe at times during the shrink. That is, don't shrink in one step, but in gradual steps.

HTH
  David
0
 
Scott PletcherSenior DBACommented:
First thing you need to decide: do you want/need a backup of the existing log data?
If not, you can do this to clear the log.  If you intend to go back to FULL mode, at most do a differential backup here, not a full backup, as you'll have to do a full backup at the end anyway.

USE <db_name>
--!!copy/save the logical file name of the *log* file (file#2), from column 1 of this output.
EXEC sp_helpfile

ALTER DATABASE <db_name> SET RECOVERY SIMPLE
CHECKPOINT
--shrink the log completely and reallocate, to insure not too many VLFs.
DBCC SHRINKFILE ( 2, 1 )
ALTER DATABASE <db_name> MODIFY FILE ( NAME = <log_logical_file_name>, SIZE = 1GB )

--hopefully log shows 1GB in output below
EXEC sp_helpfile


If you need FULL recovery mode in the future, run these commands:
ALTER DATABASE <db_name> SET RECOVERY FULL
BACKUP DATABASE <db_name> TO DISK = '...' WITH ...
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
pae2Author Commented:
ScottPletcher, thanks for the good/useful response. One question though: why take a full-backup at the end of those steps? Thanks! pae2
0
 
Scott PletcherSenior DBACommented:
When you switch a db from SIMPLE to FULL recover mode, SQL requires a full backup to create a valid recovery point for the db.  

From Books Online (I added the bold, that parts' not in BOL :-) ):
"
If you must switch from the simple recovery model to the full recovery model, we recommend that you:

Immediately after you complete the switch to the full recovery model or bulk-logged recovery model, take a full or differential database backup to start the log chain.

The switch to the full or bulk-logged recovery model takes effect only after the first data backup.

Schedule regular log backups and update your restore plan accordingly.

Log backups are an integral and fundamental aspect of the full and bulk-logged recovery models. Log backups allow the transaction log to be truncated. If you do not back up the log frequently enough, the transaction log can expand until it runs out of disk space.
"
0
 
pae2Author Commented:
Excellent - that's very good to know. Thank you Sir!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now