shrinking .ldf file and .mdf file in sql server

Question

shrinking .ldf file and .mdf file in sql server

Jai-448139

SSC Eights!

Points: 818
More actions
January 27, 2008 at 7:17 am

#119795

please advice me what does .ldf file consist of and can i shrink the .ldf , is it adviceble to shrink after the backup and how frequently it can be done on a production db
please advice me ,can i shrink the .mdf , it is adviceble to shrink after the backup and how frequently it can be done on a production db
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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

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

The ldf file is the transaction log. You can shrink it using DBCC shrink file, though if you have the necessary backups and such it should never be necessary.

The mdf (and ndf) are the data files. You can shrink them also with DBCC shrink file, however you shouldn't shrink these without a really good reason. For some of the problems of shrinking a data file, see Shrinking databases[/url] and also check out the two links at the end

Neither are something you should do on a regular basis.

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

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

You should be running regular log backups, this will remove the active portions of the log, and it should alleviate the need to shrink it.

Don't shrink the database unless you had a large, one time load. If it's getting larger, you probably need the space. Make space incresaes as you need them.

Paul Randal One Orange Chip Points: 29544 More actions · Answer 3

Here's some more in-depth info for you:

Why data file shrink is bad: http://www.sqlskills.com/blogs/paul/2007/11/13/AutoshrinkTurnItOFF.aspx

Running out of log space: http://www.sqlskills.com/blogs/paul/2007/09/24/SearchEngineQA1RunningOutOfTransactionLogSpace.aspx

Clearing the log: http://www.sqlskills.com/blogs/paul/2007/10/12/BACKUPLOGWITHNOLOGUseAbuseAndUndocumentedTraceFlagsToStopIt.aspx

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005