Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Possible Duplicate:
Shrinking log file

Some of the sql log files (idf) are hugh like 5gb. Found a article (http://extended64.com/blogs/rhoffman/archive/2009/06/11/shrink-sql-server-2008-database-log-file-script.aspx) but not sure why he repeated dbcc shrinkfile 6 times.

Can someone please confirm and suggest a clean script to do this? Thanks

Use DatabaseName 
 GO
Alter Database DatabaseName Set Recovery Simple
GO
    Alter Database DatabaseName Set Recovery Full
    GO
    DBCC SHRINKFILE ('LogFileName', 1)
    GO
    DBCC SHRINKFILE ('LogFileName', 1)
   GO
   DBCC SHRINKFILE ('LogFileName', 1)
  GO
   DBCC SHRINKFILE ('LogFileName', 1)
   GO
   DBCC SHRINKFILE ('LogFileName', 1)
   GO
   DBCC SHRINKFILE ('LogFileName', 1)
   GO
share|improve this question

migrated from stackoverflow.com Jan 25 '12 at 15:50

This question came from our site for professional and enthusiast programmers.

marked as duplicate by Martin Smith, gbn, Nick Chammas, Mark Storey-Smith, jcolebrand Jan 26 '12 at 3:17

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

    
First you need to fix the reason why the log files are so big and that is because you are not properly doing transaction log backups! Shrinking is a poor idea without fixing the underlying problem. It will just reoccur. –  HLGEM Jan 25 '12 at 15:28
    
@HLGEM: off topic, have you visited "the heap" for SQL chat yet? chat.stackexchange.com/rooms/179/the-heap –  gbn Jan 25 '12 at 15:50

1 Answer 1

In my previous project we had an import from Excel => C# => Sql Server

and the log file starts to occupy the hard drive up to 50 GB in a month!!!! We adopted a query that time to resolve the space issues.

USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

Reference -> SQL SERVER – Shrinking Truncate Log File – Log Full

Finally we prepared the window service, which was supposed to run after a certain period of time in off hours.

share|improve this answer
2  
If your log files are growing regularly then shrinking like that is a massively counter-productive thing to do. Don't do it. By doing this you are invalidating the log backup sequence. My guess is you are using FULL recovery when you might be better off using SIMPLE. –  sqlvogel Jan 25 '12 at 22:15
    
WITH TRUNCATE_ONLY option is not supported in MS SQL Server 2008. –  Alex_L Jan 26 '12 at 0:31