Can't shrink physical logfile size.

Question

Post reply

Can't shrink physical logfile size.

davidgray

SSC Eights!

Points: 860
More actions
January 28, 2013 at 10:09 am

#267807

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101103 More actions · Answer 1

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

SQLALX SSC Eights! Points: 956 More actions · Answer 2

How to shrink transaction log file: http://bit.ly/XaDOQr

davidgray SSC Eights! Points: 860 More actions · Answer 3

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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 SSC Eights! Points: 860 More actions · Answer 5

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:

ldf = 1mb

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

enabled on the server.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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 SSC Eights! Points: 860 More actions · Answer 7

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.