About a year ago, I wrote SQL SERVER – Shrinking Truncate Log File – Log Full. I was just going through some of the earlier posts and comments on this blog and one particular comment by Praveen Barath caught my eye.It provides a clear and detailed explanation of truncating log files in SQL Server 2005. I am sharing this explanation here with full credit to the original author.
Truncate Log Files in SQL Server 2005
Truncating log files in SQL Server 2005 differs significantly from SQL Server 2000. In SQL Server 2000, you could easily shrink a log file to any desired size using the Shrink operation. However, in SQL Server 2005, shrinking the log file can sometimes be more challenging due to differences in the way log management is handled.
Below are two common approaches for truncating or reducing log files in SQL Server 2005:
1. Shrink the Log File Size at the Right Time
One effective way to shrink a log file is to perform the operation immediately after importing or exporting data. For instance, if you use an SSIS package to load data into the database or export data from it, you can shrink the log file soon after. Here’s how:
- Highlight the database in SQL Server Management Studio (SSMS).
- Navigate to Tasks -> Shrink -> Files.
- Select the log file and specify the desired size (e.g., 1 MB).
- Click OK to complete the process.
Performing this operation immediately after bulk data operations ensures the log file can be reduced effectively.
2. Eliminate the Log File Completely
In some cases, you may want to remove a large log file entirely to free up disk space. For example, if you have a 40 GB log file and are certain it is no longer needed, you can remove it with the following steps:
Steps:
a. Detach the Database
- In SSMS, highlight the database.
- Navigate to Tasks -> Detach, then click OK.
b. Rename the Log File
- Go to the folder where the log file is stored.
- Rename the log file (e.g., change
test-drive_log.ldftotest-drive_log-aa.ldf).
c. Attach the Database Without the Log File
- In SSMS, go to Databases -> Attach.
- Click Add and select the primary database file (
.mdf). - Highlight the log file in the dialog and click the Remove button. This will attach the database without the log file.
d. Delete the Log File
- Once the database is attached successfully and its contents are verified, delete the old log file.
Important Notes
- Use Caution: Removing or truncating log files can cause data loss if not done carefully. Always ensure the database is backed up and no transactions are pending before performing these actions.
- Regular Maintenance: To avoid large log file sizes, ensure you regularly back up the transaction log and use appropriate recovery models (e.g., SIMPLE or FULL based on your requirements).
Final Thoughts
Shrinking or truncating log files in SQL Server 2005 requires careful timing and proper techniques. Both methods described above—shrinking at the right time and completely eliminating the log file—are effective under specific circumstances. Make sure to choose the approach that best suits your database needs.
If you have any experiences or additional tips related to log file management, feel free to share them in the comments.
Reference: Pinal Dave (https://blog.sqlauthority.com), Praveen Barath
19 Comments. Leave new
I have noticed that you have not included a step of backing up the database first, and when doing so, clicking remove inactive entries will verify that all transactions have made it through the log before deleting it. I have avoided the dreaded “Unable to Attach” corruption error by doing this fashion. Thanks for having a great site, although I am disappointed in your decision to not write 2k tips anymore. I understand it, but still have clients from all over who are still running 2k and will for quite a while longer. Still, thanks for all you do!
Shannon
Hi,
i am using sql server 2005 express edition,
I detach the database but i couldn’t find log folder anywhere. There is SQL serve logs folder under Management folder but it is not showing any rename option after right clicking on it, it just shows Refresh.
How can bring back my detach database as i don’t have any backup for that.
Sorry, i attached the detached file by right clicking on database->attach. But my questions of finding log folder is still unsolved.
Hi Sukh,
You need to find the Log file folder in your Windows folders by going to place where your SQL server is installed and has its data files. The path should be something like.. ‘D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\’. There you will see your Log file and MDF file.
– Gaurav
Can one programmatically schedule this?
If yes, then what would the BAT file look like?
Please advise.
Thank you.
— Mark Kamoski
Hi Pinal ,
If i create second log file for the one database. so how it works ?
We have an option with one of our server like disk space alert, when the usage os disk reaches 90-95% it wil create an alert and send an email to all DBA.
All the time Critical suituation is due to growth of LOG files only, rarely on MDF files. Is there any query to find which file MDF/LDF is growing or which is adapting more space.
Is there any way to find the file in short time period because we have to resolve it as quick as posible.
Regards,
Senthil kumar Rajendran.
Very nice and helpful article. Got a log file down from 524 meg to 500 k.
I am presently working on Upgradation project and the SQL Server is development phase n we are getting data from another database through SSIS. I am experiencing performance problem, where there are only 20-30 users who are using the SQL. There is a very slow running SSIS packages and sometimes they are stopping due to unkown reason n if they leave them..the next day its show tht the SSIS package ran successful. what are the things I am suppose to check for?..plz suggest me how to check the loading performance as its bulk loads n even mass updates are ran on the loaded tables.
thanks and regards
I just tried II. Eliminate the log file completely. The trick worked PERFECTLY!
Thank you for posting the solution!
this will not be done if database is mirrored., Whats procedure to shiriking mirror database.
Hi Pinal, I am using SQL Server 2005 Management studio and connected to SQL Server 2000 instance and I am able to get rid of the promary log file completely.
Thanks for posting the solution.
Hi Pinal,
Even after executing the above shrink query the size of the log file has not reduced. In my case the data file is around 180 mb, where as Log file is aroung 32 GB. Please let me know the log maintenance procedure to reduce the log file size.
Regards,
Uday
Hi!
What about removing the log file in Ms Sql 2008?
I tried to Detach&Attach the database, however I can’t attach it without the log file..
thank you,
yasemin
Hi pinal
how to create email alert for log is full in database.
please help me….
Hi Pinal,
thank you for this post. I initially had the same problem as Yasemin but found if the reference to the log file is removed when re-attaching, your solution works perfectly.
Hi,
If the log file size is huge and you are not able to truncate it then you can try these steps as well.
1. Goto to database properties.
2. Goto options.
3. Change the recovery model to simple and click over ok.
4. Restart the database.
5. Try shrinking the log file.
So easy i wish had thought of it. I’m on SQL Express 2008 r2 and it worked just fine.
Hi
I need one help very badly. My scenario is like this. I have1 DB on an Instance which is having a data of almost 50GB with around 20 million records. And we have one job after running which the data or records in the DB1 are pushed into an another DB which is running on another Instance. But the bad thing is that the Log file(on Instance 2) is getting filled and the job getting failed. I already kept the Log Drive size as 68GB and I can’t afford more than that. So is there any chance or settings by setting which the log file will be shrinked on fly and the transaction continues.