Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Can't shrink physical logfile size.
Add to briefcase

Can't shrink physical logfile size. Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
thedavegray
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: Friday, April 26, 2013 12:51 AM
Points: 31, Visits: 71
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
ScottPletcher
Posted Monday, January 28, 2013 10:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 1,318, Visits: 1,764
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)
One man with courage makes a majority. Andrew Jackson
Post #1412535
SQLALX
Posted Monday, January 28, 2013 5:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 4:33 PM
Points: 21, Visits: 78
How to shrink transaction log file: http://bit.ly/XaDOQr
Post #1412713
thedavegray
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: Friday, April 26, 2013 12:51 AM
Points: 31, Visits: 71
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
GilaMonster
Posted Tuesday, January 29, 2013 1:36 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 37,663, Visits: 29,915
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
thedavegray
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: Friday, April 26, 2013 12:51 AM
Points: 31, Visits: 71
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
GilaMonster
Posted Tuesday, January 29, 2013 1:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 37,663, Visits: 29,915
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
thedavegray
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: Friday, April 26, 2013 12:51 AM
Points: 31, Visits: 71
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

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.