Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

shrink log file sql server 2008

Posted on 2014-03-30
6
Medium Priority
?
851 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 100 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 100 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 1800 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

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.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

719 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