Solved

shrink log file sql server 2008

Posted on 2014-03-30
6
830 Views
Last Modified: 2014-04-01
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
Comment
Question by:pae2
6 Comments
 
LVL 17

Assisted Solution

by:lruiz52
lruiz52 earned 25 total points
ID: 39965829
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
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 25 total points
ID: 39965880
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 450 total points
ID: 39966619
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Closing Comment

by:pae2
ID: 39967742
ScottPletcher, thanks for the good/useful response. One question though: why take a full-backup at the end of those steps? Thanks! pae2
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39967808
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
 

Author Comment

by:pae2
ID: 39969971
Excellent - that's very good to know. Thank you Sir!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now