Solved

shrink log file sql server 2008

Posted on 2014-03-30
6
823 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:
ScottPletcher 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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:ScottPletcher
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 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

12 Experts available now in Live!

Get 1:1 Help Now