Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can't shrink physical logfile size. Expand / Collapse
Author
Message
Posted Monday, January 28, 2013 10:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 6, 2014 6:01 AM
Points: 34, Visits: 80
Hi,

Under Sql 2008 I really need to shrink the physical size of the logfile. The database uses full logging but is not currently in use I therefore know all transactions are committed and up to date. Physically the log is about 500mb and I would like to empty it and set it back to about 20mb. I have tried the following but the size will just not go down.

ALTER DATABASE [MyDb] SET RECOVERY SIMPLE
GO

DBCC SHRINKFILE('MyDb', 1)
GO

DBCC SHRINKFILE (N'MyDb_Log' , 0, TRUNCATEONLY)
GO


DBCC SHRINKDATABASE(N'MyDb', 5 )
GO

ALTER DATABASE [MyDb] SET RECOVERY FULL
GO

Any ideas?

Regards

Dave
Post #1412530
Posted Monday, January 28, 2013 10:13 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:59 AM
Points: 1,960, Visits: 2,894
SQL will tell you if/why it won't shrink the log:


SELECT log_reuse_wait_desc, * --col name may be a little off, from memory, but it's at least close
FROM sys.databases
WHERE name = 'yourDbName'


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1412535
Posted Monday, January 28, 2013 5:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 9, 2014 5:59 PM
Points: 22, Visits: 83
How to shrink transaction log file: http://bit.ly/XaDOQr
Post #1412713
Posted Tuesday, January 29, 2013 1:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 6, 2014 6:01 AM
Points: 34, Visits: 80
ScottPletcher (1/28/2013)
SQL will tell you if/why it won't shrink the log:


SELECT log_reuse_wait_desc, * --col name may be a little off, from memory, but it's at least close
FROM sys.databases
WHERE name = 'yourDbName'


Thanks, didn't know about that column, however it brings back the value 'NOTHING' when queried.

Could this be anything to do with local resources such as disk space or disk fragmentation? If I restore this database to
my local instance and back it up, the resultant .bkp file is a quarter of the size of the backup on the server.
Post #1412798
Posted Tuesday, January 29, 2013 1:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 42,438, Visits: 35,493
Most likely the active portion of the log is at the end of the file.

Try a few 'fake' operations (create a table, populate it and then drop it) interspaced with log backups and checkpoint and try again to shrink.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1412806
Posted Tuesday, January 29, 2013 1:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 6, 2014 6:01 AM
Points: 34, Visits: 80
SQLALX (1/28/2013)
How to shrink transaction log file: http://bit.ly/XaDOQr


Ok, I followed all the steps in that article and it did indeed shrink the logfile down to 1mb.
So now I have:

    mdf = 973mb
    ldf = 1mb


Which produces a backup file of 945mb. So does this sound about right, there is no compression
enabled on the server.

Post #1412809
Posted Tuesday, January 29, 2013 1:51 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 42,438, Visits: 35,493
Grow that log file to something sensible, and make sure that the autogrow settings aren't the default.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1412814
Posted Tuesday, January 29, 2013 2:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 6, 2014 6:01 AM
Points: 34, Visits: 80
GilaMonster (1/29/2013)
Grow that log file to something sensible, and make sure that the autogrow settings aren't the default.


Hi, yes I will. It was just on a test version of this "problem" database.
Post #1412821
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse