Unable to recover space from log file
-
Hi. I am trying to recover unused space in a SQL log file back to the operating system, but have been unsucessful. The log file had grown to 4.6GB and I ran a backup after which the used space now only shows as 138MB. I tried to shrink the file both from the enterprise manager task pad and from the query analyzer, but cannot recover the space. Query analyzer returns a message that all logical log files are in use. Help!
-Al
-
http://www.sqlservercentral.com/scripts/contributions/26.asp
This usually works for me.
-
OK, I'm answering my own post, but here's what I did:
Backed up database normally.
ran BACKUP LOG database WITH TRUNCATE_ONLY
ran DBCC SHRINKFILE on logfile
Success!!
-
May I recommend NOT to use TRUNCATE_ONLY!!!!
you should be able to do it with a "normal" BACKUP LOG
from the turncate_only point forward your TLOG Backup will be useless to recover that database you will need Full DB Backup.
Thank God that option was removed from 2005.
* Noel -
you can say that again !
truncate_only seemed so easy, but making the new fullbackup has been forgotten many times
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
any objections to using this 'no_log' method ??
Backup log databasename with no_log
DBCC shrinkdatabase (databasename)
-
from BOL :
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and truncates the log. This option frees space. Specifying a backup device is unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable. For recovery purposes, immediately execute BACKUP DATABASE.
So, if you can live with the situation that you cannot recover point in time to a moment of the to-be-truncated log, and you make a fullbackup immediatly after the truncate, there is no problem but the recovery-gap itself.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Well thanks for clearing that up Al.
Nice use of the color RED too.
I'm just guessing here but..... it appears you're suggesting a backup be made before ever truncating a log file - unless you can live with the ramifications.
Cool.
-
No, he's suggesting with added emphasis that you make a backup (full or log) INSTEAD OF truncating. Forget that the NO LOG ! TRUNCATE options exist. These are not the options you're looking for...
-
Thank you Scott for the assistance
All Italics-characters are copied from books online. The coloring was just to emphasise the pittfalls.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I don't understand that at all. There are times when I have to shrink or even delete the entire log file because of a ridiculously small hard drive.
I'll backup the DB, then detach the .mdf and .ldf files - only re-attching the .mdf file.
Is this a cardinal DBA sin I'm committing here? I just thought it was a lot faster than running the DBCC commands listed above.
Hmmmmmm.....
Oh and I was just kidding about the red letters .... ha ha ha ... relax
-
There are a few missing details, such as the recovery mode, whether there is any other database activity, and what your tolerance is for data loss.
If the DB is not logged (simple recovery), you can do a full backup and then truncate to your heart's content. All changes since the last backup are already at risk in case of disaster, the truncation doesn't increase your exposure.
If it is logged, the truncation will make any further log backups useless. You can do a full backup, truncate, and then another full backup. The only data at risk is the activity between the full backups. If that is a tolerable risk go right ahead. But you're probably better off figuring out how to do it without truncation.
-
Lots of details missing - sorry.
The recovery mode is simple. There is no other DB activity. The SLA agreed to is 'restore to last night's backup @ midnight.' So I'm not too concerned for this client.
You've all pointed out some areas where my simplistic approach would hose up other clients databases while performing the DBCC commands - thanks.
I have a new appreciation for the members and perhaps I should pay closer attention to the details in BOL 😉
JT
-
If you use SIMPLE mode, you can't backup the log file. If you can't back it up, you can't restore it. So truncating doesn't matter.
If you use FULL (Or Bulk-logged) then you can backup the log file. And if you do truncate, you better do a full backup quickly.
FULL
TL Backup
TL Backup
Log TRUNCATE
TL Backup
DB Fails
In the above scenerio, you could restore the Full, and the first two TL Backups. But the third TL Backup would not restore as it's not part of the 'chain'. You would have to do:
FULL
TL Backup
TL Backup
Log Truncate
FULL
TL Backup
etc.
And hopefully the original poster realizes this and has done a full backup since the truncate.
-SQLBill
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply