Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can't shrink physical logfile size.


Can't shrink physical logfile size.

Author
Message
davidgray
davidgray
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 229
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
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3946 Visits: 6686
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)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
SQLALX
SQLALX
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 83
How to shrink transaction log file: http://bit.ly/XaDOQr
davidgray
davidgray
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 229
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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47261 Visits: 44392
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, MVP, M.Sc (Comp Sci)
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


davidgray
davidgray
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 229
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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47261 Visits: 44392
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, MVP, M.Sc (Comp Sci)
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


davidgray
davidgray
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 229
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search