A Story of the Deleted Transaction Log
-
I've had similar problems a few weeks ago when a supportdesk just deleted the logfile because that was the standard procedure for the company in these situations.
Same situation as the article. In the middle of the night. Guess who got a nice e-mail from the CEO for screwing up the database server 😉
-
BobAtDBS (7/14/2008)
Why wouldn't you simply detach the database, then attach it back with the single file option? Sorry, I'm in a hurry this morning, but I'm almost certain I've moved databases that way without a transaction log.That only works if the DB was shut down cleanly. If the shutdown was not clean, any attempt to reattach without the log will fail.
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Bill Whitman (7/14/2008)
A maintenance plan should be set up to truncate, shrink, and backup the transaction log -- nightly, if necessary.Absolutely not. That's terrible advice to be giving.
Backup the log, sure, though more often than nightly is usually necessary. Truncate and shrink are not things that should be done to the log on a regular basis, if at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Michael Oberhardt (7/14/2008)
I've had a few issues with rogue transaction logs as well (on MSDE at any rate). Even on autoshrink I've had them grow to over 10GB, and didn't even notice it.They are pretty easy to deal with though - truncate them (WITH TRUNCATE ONLY) then shrink.
A lot of installations we have scheduled maintenance jobs that do this, and defrag indexes periodically.
That's an appalling thing to be doing at all to the log, let alone scheduled.
Please read through this - Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Raymond Xu (7/14/2008)
There are two ways to recover a single data file DB in SQL 2005.1. Create DB for attach ...
CREATE DATABASE database_name
ON [ ,...n ]
FOR { ATTACH [ WITH ]
| ATTACH_REBUILD_LOG }
2. If the log was lost during SQL server is down, once you start the sql server, a new log file will be generated automaticly.
Those only work if the DB was shut down cleanly. See the link I posted above.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
This is a perfect example of non dba technical staff having a little knowledge just enough to be very DANGEROUS! Let alone them having sysadmin access to that production box to even be able to do something like this! NO ONE and I mean NO ONE should ever detach a production database on his/her own without first talking to the Production DBA/Manager in charge, let alone deleting the log file afterward! I don't care if you have to call me or my boss in the middle of the night! DO IT first!!!There is definitely NO EXCUSE for this at all and it is a firing offense in my opinion. This could have easily been DISASTROUS for the company. Glad you got it back.. On a another note, another solution that could prevent this situation from ever getting to this. Install disk pager alerts that notify the DBA team once any data/T-Log drive goes under 2 GB of free disk space. Then the DBA would get the page and be able to deal with this BEFORE the T-log filled the drive up. Also, I have installed a db growth metrics package on each of my db servers that spit out a report to me twice a day that alerts me if I have a runaway t-log or excessive db growth in time fo me to deal with it.. God, this is such a preventable thing...An ounce of prevention is worth a pound of cure here.....and NO DB COWBOYS please! Ask first people! Travis.
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
-
GilaMonster (8/7/2009)
Bill Whitman (7/14/2008)
A maintenance plan should be set up to truncate, shrink, and backup the transaction log -- nightly, if necessary.Absolutely not. That's terrible advice to be giving.
Backup the log, sure, though more often than nightly is usually necessary. Truncate and shrink are not things that should be done to the log on a regular basis, if at all.
the only time I would do that is maybe on a dev server if space is a premium...
-
Mark Horninger (8/7/2009)
GilaMonster (8/7/2009)
Bill Whitman (7/14/2008)
A maintenance plan should be set up to truncate, shrink, and backup the transaction log -- nightly, if necessary.Absolutely not. That's terrible advice to be giving.
Backup the log, sure, though more often than nightly is usually necessary. Truncate and shrink are not things that should be done to the log on a regular basis, if at all.
the only time I would do that is maybe on a dev server if space is a premium...
On a dev server I'd just set all the DBs to Simple recovery. That way there's no need to worry about log backups in the first place.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
WRONG! A db in Simple recovery mode does NOT guarantee that the transaction log will
shrink in size or not grow and thus cure an out of disk space problem. Big myth. It simply "truncates" the log automatically, meaning committed transactions are just removed from the transaction log. The space freed up by removing those transactions is NOT removed from the log so it retains its physical 100GB size until it is shrunk, The freed space is just made available for re-use by future transactions. I have seen several very large T-log files on dbs that are in SIMPLE mode. Travis.
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
-
talltop (8/7/2009)
WRONG! A db in Simple recovery mode does NOT guarantee that the transaction log willshrink in size or not grow and thus cure an out of disk space problem. Big myth.
I never said it would. I said that I would set dev databases to simple recovery so that there's no need to worry about log backups.
That said, if you've got a DB in simple with a log that's growing out of control then either you've got replication that's not working properly, massive single transactions (index rebuilds) or long-running open transaction.
The space freed up by removing those transactions is NOT removed from the log so it retains its physical 100GB size until it is shrunk
Of course it's not. Doesn't happen in any of the recovery models. The only thing that shrinks a file (log or data) is DBCC ShrinkDatabase or DBCC ShrinkFile. That's ignoring the question of why you'd want to shrink the file at all. Unless it's grown unusually large and the free space inside is not likely to be reused in a reasonable amount of time, you wouldn't want to shrink because if you do, the file will simply grow again.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Because if that 100gb physical space is not reclaimed another t-log on that server may not be able to expand if that 100gb is the last 100gb available on the drive, that's why. Ok, so you are admitting that a DB in SIMPLE mode does not in itself address a t-log filling up a drive then? Your post seems to kind of state that in a round-a-bout way. Or maybe I am misreading your post... I like SIMPLE mode too but it is not a panacea in preventing problems like this...Travis.
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
-
NTFSUndelete or similar software might have been a better first option. When you delete a file the inodes are cleared but the data isn't and can often be recovered (although you would immediately want to stop SQL Server and anything else that writes to disk until you have done the recovery to stop the data being overwritten)...
James
--
James Moore
Red Gate Software Ltd -
I don't know which of my posts you're reading, but the only thing I said about Simple Recovery was
On a dev server I'd just set all the DBs to Simple recovery. That way there's no need to worry about log backups in the first place
I never stated it prevented the drive filling up. If you disagree, please find the post where I said that it did.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Ok, if that was your only point then I guess I'm not understanding the point of bringing simple mode up on a dev server in a thread where the t-log was filling up a drive on a production box to my knowledge. If that was your only point, to remind us all that simple mode does not require t-log backups and nothing else, ok, but most of us already know that. What's your point? and how does that address the problem stated originally in this article/thread?
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
-
---------
Bill: A maintenance plan should be set up to truncate, shrink, and backup the transaction log -- nightly, if necessary.
Me: Backup the log, sure, though more often than nightly is usually necessary. Truncate and shrink are not things that should be done to the log on a regular basis, if at all.
Mark: the only time I would do that is maybe on a dev server if space is a premium... (I assume talking about truncate and shrink)
Me: On a dev server I'd just set all the DBs to Simple recovery. That way there's no need to worry about log backups in the first place. (Pointing out that even on dev, I wouldn't do nightly log backups and/or truncates.)
----------
As for your assertion that most people know Simple recovery, I beg to differ. I've seen far too many cases where the DB's in full because that's the default and the people maintaining it don't have a clue what the recovery models do.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 15 posts - 46 through 60 (of 113 total)
You must be logged in to reply to this topic. Login to reply