March 31, 2010 at 5:47 am
Jeffrey
i understand exactly where you were coming from.
All
From BOL
However, if the old log file is not found and if the database was shut down cleanly and has no active log chain, the attach operation attempts to build a new log file for the database.
the key words here are "no active log chain" and "attempts to build a new log file".
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 31, 2010 at 7:01 am
As Paul said, this is all a bit academic as there's a better answer to the orginal query, but if the database was detached, which is an operation that can only occur after all users have been killed/disconnected and their transactions committed/rolledback, in what scenario can there be an active log chain?
Not having trust that a database file is guaranteed to be consistent after all transactions have been committed/rolledback would mean fundamentally not being able to trust that the database is ever consistent at all.
I understand that it's a topic that needs to be treated with some caution as there's a huge difference between this and for example running out of space on a disk due to transaction log growth, then stopping sql server and deleting the log file (which I think is where most of the horror stories come from).
Genuinely interested in being proven wrong with this, but would like to understand the SQL internals that could lead to corruption
March 31, 2010 at 7:26 am
HowardW (3/31/2010)
...but if the database was detached, which is an operation that can only occur after all users have been killed/disconnected and their transactions committed/rolledback, in what scenario can there be an active log chain? Not having trust that a database file is guaranteed to be consistent after all transactions have been committed/rolledback would mean fundamentally not being able to trust that the database is ever consistent at all.
This is kinda what I was getting at before. As far as I know, detaching a database cleanly involves rolling back any open transactions, performing a CHECKPOINT, setting the database OFFLINE, and then updating the system metadata to remove the database. Of course, a whole load of other stuff might occur too - like updating statistics, deleting any full-text indexes, and so on, but I think that is essentially it.
In principle, it is hard to see why a fully CHECKPOINTed database (with no transactions since the CHECKPOINT) would require a log file at all. I would expect CREATE DATABASE...FOR ATTACH_REBUILD_LOG to always succeed.
The problem, of course, is that it is not explicitly documented as such, and so we can't rely on it. The whole debate kinda ends there really. And, as far as sp_detach_db is concerned - it ends up calling the undocumented DBCC DETACHDB, so again, the behaviour is undefined. Any observations we might make depend on current implementation details, which could change.
Nevertheless, I feel happy saying that should I need to detach a database, I would fully expect it to be able to get it back online, even if the log file was damaged or missing - as long as the shutdown and detach were 'clean'. By clean, I mean that the database was set to SINGLE_USER mode, CHECKPOINTed, and then detached without errors or warnings.
There's always an exception, of course. My advice is to never detach a database in STANDBY mode. If any logs were written to the standby file, that database cannot be brought back online in a transactionally consistent state.
One last exciting thing. Backing up the tail of the log, just before detach, results in a very exciting error message at detach time.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply