If you work with MS SQL server and have databases that handle lots of transactions then you no doubt have encountered the .ldf file growing to insane sizes if left unchecked. A large LDF file will result in the web application running extremely slow or most likely just timing out, not to mention affecting the performance of other databases setup on that same server. We used to manually detach the database rename the .ldf, once you re-attach the .mdf file it would start up a new .ldf log file and things would run speedy once again. While that worked it resulted in downtime, wasted resources, and unhappy clients.
Here is a solution that works perfectly and is automated. (assuming sql server 2000 or 2005 standard here)
- Launch Enterprise Manager and open Management
- Right click on Database Maintenance Plans > New plan
- Pick all your options to backup your database with the schedule and save the plan
- Drop down SQL Server Agent
- Right click on Jobs > New Job
- Give it a name and click on the second tab “steps“
- Add a second step under the initial backup by clicking New
- Choose Type TSQL, pick the database, and paste in this command
- Save it, click on step 1 hit adavanced and set “On success action:” to “Goto Step: 2” or it won’t run.
BACKUP LOG dbnamehere WITH TRUNCATE_ONLY
DBCC SHRINKFILE(dbnamehere_Log, 1)
We have a log file that used to run to 18 to 30GB quickly and now it is 1MB on a daily basis, pretty sweet and with no real down time. We have tried backing up the log and tried the built in shriking options with no luck, never worked. Sure there are plenty of other methods you can use, hope this helps someone else out there facing the same problem.
Hi, We have this problem and the log file reach 27 GB, so if I apply the above steps the log file size will reduce in size?..
Thank you
Yep, it will reduce it to probably less than 3MB when done. Give it a shot. As usual run a full backup first just in case you made some wrong selections.
thanks for your reply,
I did the maintenance plan and the job.
The sql server agent should be started? or it will start automatically, and shall we schedule the job or not.
Yes the sql server agent must be running or the maintenance plan won’t execute. And yes I would setup a schedule to run however often you need it to. We run it nightly on our high volume databases only.
Hi, Hope i’am not bothering you..I followed all the mentioned steps but it didn’t work, nothing start running, even that i schedule them all.
do you have any idea what shall i do..if you want any details i can give you.
thanks a lot for your help!!
For work with sql files try use-fix mdf,usually i use it,and it is free as how as i remember,program repair data from corrupted databases in the MS SQL Server format (files with the *.mdf extension),supports data extraction via the local area network,can save recovered data as SQL scripts, it is also possible to split data into files of any size,compatible with all supported versions of Microsoft Windows, such as Windows 98, Windows Me, Windows NT 4.0, Windows 2000, Windows XP, Windows XP SP2, Windows 2003 Server, Windows Vista,tool supports the following database formats: Microsoft SQL Server 7.0, 2000, 2005,also can repair .mdf files of Microsoft SQL Server 2005, repair mdf file of Microsoft SQL Server 2005 (64-bit).
Hi my dbname.mdf file is large 48 GB!!!!, where as when i take a back up its
6GB…
I need to reduce my dbname.mdf
Can these steps work?
Thank you for this! It worked perfectly on an .ldf that’s been giving me trouble for months!!
This approach does not work in SQL Server 2008. I get this message: “‘TRUNCATE_ONLY’ is not a recognized BACKUP option.” Anyone who has a similar problem and found a way to shrink the file?
You try changing SQL Server Compatibility mode to 80, not sure though.
Available Modes are:
60 = SQL Server 6.0
65 = SQL Server 6.5
70 = SQL Server 7.0
80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008
For people using SQL 2008, the “TRUNCATE_ONLY” option was deprecated.
Instead, the method required is as follows :
1) Determine the name of the file you wish to shrink using sp_helpfile
2) Change the database to Simple mode.
ALTER DATABASE SET RECOVERY SIMPLE;
2) Shrink the database file as mentioned in the post.
DBCC SHRINKFILE(, 100)
(the 100 refers to the target file size after shrinking. I suggest around 10% of the MDF file size).
3) Put the database back into full recovery mode (if it was that in the first place)
ALTER DATABASE SET RECOVERY FULL;
Be aware that this breaks the backup chain so subsequent transaction log backups or differential backups will fail until you perform a new, full backup.
Please read corrected version (previous post skipped all parenthesis).
For people using SQL 2008, the “TRUNCATE_ONLY” option was deprecated.
Instead, the method required is as follows :
1) Determine the name of the file you wish to shrink using sp_helpfile
2) Change the database to Simple mode.
ALTER DATABASE databasename SET RECOVERY SIMPLE;
2) Shrink the database file as mentioned in the post.
DBCC SHRINKFILE(datafile_log, 100)
(the 100 refers to the target file size after shrinking. I suggest around 10% of the MDF file size).
3) Put the database back into full recovery mode (if it was that in the first place)
ALTER DATABASE databasename SET RECOVERY FULL;
Be aware that this breaks the backup chain so subsequent transaction log backups or differential backups will fail until you perform a new, full backup.
Hi
I am trying to do Db log Shrink in 2008
How can i automate this without down time?