Shrink transaction log (LDF) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Shrink transaction log (LDF)

Author

Topic

hephie
Starting Member

15 Posts

Posted - 2009-03-04 : 05:03:17

I have a Microsoft SQL Server Enterprise Edition (64-bit) running on a Windows Server 2008.
Sql server version: 10.0.1600.22

Since a while I have two (intesively used) databases with a transaction log file (*.ldf) of each almost 50GB !!

How can you shrink these basterds?
I've tried many thinks, none of them worked :(

Database files and information:
DB Name: XMusiX
DB Path: D:\MSSQL\Databases\XMusiX.mdf
Logfile Name: XMusix_log
Logfile Path: D:\MSSQL\Databases\XMusiX_log.ldf

Not working (1)

DUMP TRAN 'XMusiX' WITH NO_LOG

Not working (2)

DBCC SHRINKFILE ('xmusix_log', 10) WITH NO_INFOMSGS

Not working (3) [A script I used on sql server 2005 to shrink all log files at once]

declare @ssql nvarchar(4000)
set @ssql= '
        if ''?'' not in (''tempdb'',''master'',''model'',''msdb'') begin
        use 
        declare @tsql nvarchar(4000) set @tsql = ''''
        declare @iLogFile int
        declare LogFiles cursor for
        select fileid from sysfiles where  status & 0x40 = 0x40
        open LogFiles
        fetch next from LogFiles into @iLogFile
        while @@fetch_status = 0
        begin
          set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 1) ''
          fetch next from LogFiles into @iLogFile
        end
        set @tsql = @tsql + '' BACKUP LOG  WITH TRUNCATE_ONLY '' + @tsql
        --print @tsql
        exec(@tsql)
        close LogFiles
        DEALLOCATE LogFiles
        end'

exec sp_msforeachdb @ssql

Not working (4)
In sql server management studio, Right click the database > tasks > shrink > Database
OR
In sql server management studio, Right click the database > tasks > shrink > Files

I'm out of things to do, please reply!!

Thanks for the one with a solution,
thomas

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-03-04 : 05:36:44

Have you backed these databases up ever? If not, do this before trying to shrink them.

hephie
Starting Member

15 Posts

Posted - 2009-03-04 : 05:41:42

databases are (automatically) backed up daily.

now what?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-03-04 : 05:52:38

I think you need to find out why it is not checkpointing. You are on the base release, so it is possible that this was one of the errors, try upgrading to one of the CU's that are available as I am on CU3 and it is working fine.

hephie
Starting Member

15 Posts

Posted - 2009-03-04 : 09:59:32

I have installed the CU3 Package.

Sql server 2008 version now is : 10.0.1787.0

Which command do i use best to shrink the logfile??
any of the command in my first post are still not working. :(

Regards
Thomas

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-04 : 11:47:10

If its emergency,you can change recovery model from full to simple and shrink log file but it is not recommended as it tends to increase VLFs for your log file. You need to take Log backup frequently to reduce this issue.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources