0

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
Improve this question
2
  • 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
    Commented Jan 25, 2012 at 15:28
  • @HLGEM: off topic, have you visited "the heap" for SQL chat yet? chat.stackexchange.com/rooms/179/the-heap
    – gbn
    Commented Jan 25, 2012 at 15:50

1 Answer 1

Reset to default
-2

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.

Improve this answer
2
  • 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.
    – nvogel
    Commented Jan 25, 2012 at 22:15
  • WITH TRUNCATE_ONLY option is not supported in MS SQL Server 2008.
    – Alex_L
    Commented Jan 26, 2012 at 0:31