July 16, 2008 at 7:45 am
My transaction log files (.LDF) are full. I would like to know how I can purge them. I am running out of disk space.
thanks.
July 16, 2008 at 8:00 am
Right Click on your database-->Tasks-->Shrink-->Files
Select 'Log' from file type drop down. Press ok.
But if you have full recovery model, log file will not be reducing significantly so in this case u can detach the database move your database files to some other disk that has more space available and then attach the database again by selecting files from new location.
July 16, 2008 at 8:03 am
I assume your databases as set to full or bulk-insert mode so issue a backup log dbname command from QA (check BOL for exact syntax) or setup a maintenance plan to backup all user databases. If you do not need point-in-time recovery, set your databases to simple mode but otherwise, I'd schedule a job to perform the backups with some regularity, based on your company needs.
-- You can't be late until you show up.
July 16, 2008 at 8:07 am
usman.tanveer (7/16/2008)
Right Click on your database-->Tasks-->Shrink-->FilesSelect 'Log' from file type drop down. Press ok.
Backup your log files before you shrink them otherwise your simply wasting your time.
-- You can't be late until you show up.
July 16, 2008 at 8:15 am
And don't truncate the log unless you are willing to throw log records away and lose the ability to do point in time recovery until another full/diff backup is taken.
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
July 16, 2008 at 8:28 am
Everyone,
I am all set.
I shrunk the size of the logs. I changed the recover mode to 'Simple' and ran a full backup.
I will put in proper maintenance plans into place.
thank you everyone.
July 16, 2008 at 8:34 am
Do you need to be able to restore that database to the point of failure (should a failure occur) or is it acceptable to restore to the last full backup?
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
July 16, 2008 at 8:38 am
yes, it is Okay to restore it to the last full backup. This is not the most critical database.
thanks Gail.
July 16, 2008 at 9:06 am
Remember when you are working with this kind of issues you should have first a good plan for maintenance on your database so first make sure your database is in Full or Bulk_logged. Try to make a log backup but if you dont have a full backup you first have to do a full backup of your database, and later shrink the file with de DBCC command.
after that you have to make a full backup to not interfere with your manteinance plan and voila!!!
Have a good day.
😀
July 17, 2008 at 8:23 am
GilaMonster (7/16/2008)
Do you need to be able to restore that database to the point of failure (should a failure occur) or is it acceptable to restore to the last full backup?
I am experiencing the same issue, even if I backup my transaction log, it will not reduce in size. However, I do need to be able to restore the db to a point in failure. The database is clustered and mirrored to another cluster, so truncating the log is not an option for me. Could you please help?
The db is currently 36MB
The log is currently 1.8G
July 17, 2008 at 9:00 am
Simply backing up the log does NOT reduce the size but it frees up the unneeded transactions from the log, creating space within the log for additional, new transactions. You can always increase the frquency of your log backups, if necessary. Is disk space becoming critical to the point where you're contemplating how to shrink the log in your environment? If so, can you add more disk or can you move the data or log file to another local partition that has adequate space?
-- You can't be late until you show up.
July 17, 2008 at 9:04 am
no, this was a new deployment. We had accounted for plenty of disk space but there were no maintenance plans in place. I think we have more than sufficient space to allow us to not worry about it for the next several years. I am doing a full backup every night, differential every 6 hours and transaction log backup every hour. Things are in check now.
thanks!
July 17, 2008 at 9:06 am
BTW, I set it to Simple recovery mode yesterday. Did a Full backup yesterday.
Then, I turned it back to Full recovery mode before setting my maintenance plan in place which is:
Full backup once a night at 1 am
Differential every 6 hours
Transaction log backup every hour.
July 17, 2008 at 9:08 am
tosscrosby (7/17/2008)
Simply backing up the log does NOT reduce the size but it frees up the unneeded transactions from the log, creating space within the log for additional, new transactions. You can always increase the frquency of your log backups, if necessary. Is disk space becoming critical to the point where you're contemplating how to shrink the log in your environment? If so, can you add more disk or can you move the data or log file to another local partition that has adequate space?
Yes, space is an issue. The size of the disk is 1.99G, so I only have 116MB free. I guess I will change the backup log job to run more often and talk to the server admins about increasing disk space. However, I'm not sure that will all be able to take place before the drive fills up. I suppose there are no other options?
July 17, 2008 at 10:52 am
Well if you like to reduce the transaction of your database remember that backing up the transaction log will not reduce the size of the transaction so if you like to reduce the size you have to shrink the log
i usually prefferd to use the TSQL Statement
DBCC SHRINKFILE ('database_log',sizetoreach')
for information of the database_log you have to issue this command
select name from master.sys.database_files
in this output you will know the name of the log to reduce size...so if the name of the database_log is (test_log) and if i want to reduce the log to 100mbytes i will issue the next command.
DBCC SHRINKFILE ('test_log',100)
and thas all...if you need advice just let me know because i am working in an cluster environment and my log is around 80gb.
Have a nice day.
😀
Luis Guzman.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply