“Never” is a strong word, so let me say that we really really want to avoid it, if at all humanly possible. In short, we will have a (potentially) broken database, both at the physical level and at the logical level. Read on.
Just to be clear, when I refer to a “log file” here, I’m talking about a transaction log file, an .ldf file. Ever so often we see in forums how log files are “lost”, “disappears” or so. Now, SQL Server relies on the ldf file at startup to bring the database to a consistent state. This is known as “recovery”, or “startup recovery”. This is pretty well known, I for instance teach this at every intro level admin course. So, what if
- The ldf file isn’t there?
- Or isn’t accessible to the db engine?
- Or is broken somehow?
- Or is from a different point in time from the data file? (You’d be surprised to all the things one see over the years.)
Well, SQL Server will do the only reasonable, refuse us into the database and produce an error message (in eventlog etc).
What we see from time to time, is trying to “trick” SQL Server into re-creating an ldf file. So, why is this so bad? I will try to explain why. Let me first say that SQL Server doesn’t do these things to be mean to us, or to prove a point. If SQL Server know that the ldf file is not necessary for recovery (the database was “cleanly shutdown”), then it can and will re-create a log file for us at startup. The problem is that it isn’t these cases we see in forum. The cases we see in the forums is when this didn’t happen. SQL Server relied on the ldf file in order to bring the database to a consistent state.
Enough beating around the bush, here is an example of why we don’t want to trick SQL Server to forcefully re-create a log file:
Say you have a transaction in which you add a row to the order header table (oh) and five rows to the order details table (od). Physically, each command is reflected in the tlog, the page is modified (but still cached) and at commit, the log records are written to the ldf file. There are lots written about these algorithms, but the concepts are pretty simple. For more details, read this.
Say that we forcefully stop SQL Server, delete the ldf file, start SQL Server, see that the database isn’t accessible and somehow “trick” SQL Server into creating an ldf file for us. What is the problem? Why is this so bad? The problem is that you can have no confidence in the state of your data, both at the physical level and at the logical level. Let me start explaining what I mean by the logical level and problems at this level:
The logical level
By the logical level, I consider the user data. The rows in the tables, quite simply. We inserted one row in the oh table and five rows in the od table. These can be inserted into a total of two pages or 6 pages (in reality more, since each table is likely to have indexes etc, I’ll touch on this when we get to the logical level). Say that three of order details rows have been written to disk, but not the order header row, and not the other two order details rows. This is just an example; you can pick and choose any combo you want. It can get just as bad as you can imagine! You might think that the pages are in the end written in the same sequence as we modified them. No, that is now how the cookie crumbles (read the article I posted link to). You can probably imagine other scenarios, closer to your data. For instance, we will not do some certain modification to a table unless some other prior modification was also performed. In essence, rebuilding a log file leave us with no confidence in the data. Are you prepared to go through your database and manually verify all the data in there? Majority of you will say “no”, and it might not even be doable (data volumes, lack of information to verify etc). So, logical inconsistencies are bad. Really bad. We don’t want this. Ok?
The physical level
This log is not only used for “user-data”. It i also used for system data. Tables has indexes, where each row in a table is reflected by a row in each index. We have allocation pages stating what storage is used. We have IAM pages and PFS pages. We have linked lists. And whatnot. You probably realize that these structures also require that some modification is performed in full or not at all. (an extent allocation will be reflected in both the GAM or SGAM page and also in th extent data itself, etc). What do you think is used to protect this? Yes, you got it, the ldf file. Trick SQL Server into re-creating an ldf file and you have all sorts of physical inconsistencies. Actually, physical inconsistencies are a bit better than logical since we do have a way to check for these. I’m of course talking about the mighty DBCC CHECKDB command, a command with lots of smarts (right Paul?) to check that the db is consistent at the physical level. And what if it isn’t? CHECKDB spits out errors. Sure it has repair options, but those generally mean (unless you are the type of person who wins are lotteries) that the repair will rip out whatever makes the db inconsistent resulting in data loss (which also has no knowledge of what you might consider logical consistent data).
So, what to do?
I hope the answer is obvious. Don’t get into this situation in the first place. Don’t go deleting ldf files for instance. Do take backups frequently enough so you don’t end up in a situation like “I need to rebuild the log or I’m toast.”. If something strange happens, don’t start mucking about with the system unless you know what you are doing. Hire a specialist, call MS support or so. If I had a brand new Ferrari, I wouldn’t disassemble the engine in case I hear weird noise from the it. Heck, I barely pop the hood of my Beamer!
And no, I won’t get into what commands can be used to re-build the ldf file. I expect all readers of this to not get into a situation where it is needed. 🙂
(I understand one can encounter a machine with no backups and something happens to the ldf file, and such scnearios. Of course I do. I feel really really bad every time I read about such a situation, because there is no graceful way to handle it. That is why I have such a harsh tone above. I don’t want this to happen to anybody. One would hope that this is obvious, but nothing ever is on the Net. So I’ve learned. So, please leave such unconstructive comments out of the discussions!)