Solved

shrink log file sql server 2008

Posted on 2014-03-30
6
846 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

617 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