July 31, 2008 at 6:37 am
I have a ldf file that seems to be growing at a rate of 100 meg per day, this is on a test server where we are testing a new version of some software.
What is it that I can do to reduce the size of the file?
Also, how can I tell what is filling this file so rapidly?
Thanks in advance for any help.
July 31, 2008 at 7:37 am
Is your database in Full recovery mode? If yes, do you take transaction log backups?
If not your ldf will keep all the transactions and will grow unutil the disk is full or the max filesize is reached(if you defined one).
About your questions what is filling the file I can only guess since I don't know the system. Generally speaking all transaction on your database are logged in the ldf file. If 100 Mb per day is normal or not simply depends on the kind of activity on your DB.
[font="Verdana"]Markus Bohse[/font]
July 31, 2008 at 7:42 am
I have a ldf file that seems to be growing at a rate of 100 meg per day, this is on a test server where we are testing a new version of some software.
As this is a test server, either set the recovery model to SIMPLE, or run BACKUP DATABASE xxxx WITH TRUNCATE_ONLY on a periodic basis (e.g. overnight, at the end of each test etc)
What is it that I can do to reduce the size of the file?
Run DBCC SHRINKFILE (see BOL)
Also, how can I tell what is filling this file so rapidly?
If it is a moderately used test server then 100mb per day isn't much. If you do the above, then don't worry about it.
July 31, 2008 at 8:50 am
Can the DBCC command (shrinking) be accomplished through the task selection of the SSMS?
Would this accomplish the same thing?
July 31, 2008 at 8:55 am
Yes you can use the Shrink File task in SMSS,
Create a script from this if you want to know what has been run.
July 31, 2008 at 9:07 am
You need to perform that log backup before shrink will work. The active transactions (committed, not backed up), need to be removed.
July 31, 2008 at 9:13 am
That worked great - 1.4Gb ldf file shrunk to 1,024kb.
Did a full backup of the Database then executed the shrink, through T-SQL after generating the script.
Now lets see if the testers yell at me for anything missing 😀
July 31, 2008 at 10:04 am
Don't forget to set the DB to simple recovery mode or schedule log backups.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply