Thursday, January 04, 2007
Dumb Ass Error Messages
I love Microsoft error messages. I really do. Especially since I charge by the hour.
I was setting up a SQL "Maintenance Plan" to do regular backups and stuff. I've done it many times before, and it always worked pretty smoothly. After setting them up, I go through and manually run the Agent jobs to make sure they all work. They always have before, but as I said, I charge by the hour, so I go ahead and check them out. They all work except the transaction log backup step! Microsoft gives me the incredibly terse and ultimately meaningless error message shown here:
Now, this is simply not a lot to go on, so I examine the system logs and such and come upon the following:
I was setting up a SQL "Maintenance Plan" to do regular backups and stuff. I've done it many times before, and it always worked pretty smoothly. After setting them up, I go through and manually run the Agent jobs to make sure they all work. They always have before, but as I said, I charge by the hour, so I go ahead and check them out. They all work except the transaction log backup step! Microsoft gives me the incredibly terse and ultimately meaningless error message shown here:
Executed as user: CPAF\sqlserver. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Now, this is simply not a lot to go on, so I examine the system logs and such and come upon the following:
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'Games Database Maintenance'' (0x8B21AC552BA46346ABCA7FD26F22B684) - Status: Failed - Invoked on: 2007-01-04 15:35:54 - Message: The job failed. The Job was invoked by User CPAF\administrator. The last step to run was step 1 (Step 1).
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
This message is verbose and just as meaningless as the last. This has a very high words-to-information ratio. I wonder what the units are for this? We know that information is measured in bits, so the ratio is words per bit (words/bits). A message like the one above has many words and virtually no information -- a very high words/bit ratio. I propose we call this unit the "verbit". Therefore, we could say that this message has millions, perhaps trillions of verbits. Verbits are roughly equivalent to the "bullshit factor". But I digress...
At this point it will clarify things later if I explain I had just recently added a 2nd hard drive to offload the backups onto. When troubleshooting, one of the things you always look for is what changed. Chances are, the new thing is somehow involved in the problem. Factor the new hard drive in with the fact that most backup problems I have had in the past are simply security related (that's why you can't "open the backup device" when you try to backup databases to your Desktop -- unless you are running SQL Server as local system or administrator) and you'll understand why I quickly had myself convinced that the problem was related to the security settings of the new hard drive.
After spending several hours meticulously examining security settings, permissions, verifying accounts, inheritance, etc, it finally dawned on me that the problem had nothing to do with permissions. I was running a maintenance plan on a database that happened to be running in simple recovery mode! There is no option to backup a transaction log for simply recovery databases. An error message that at the indicated "One or more databases are using the simple recovery model" would have saved hours of red-herring work.
Thanks Microsoft! I'm going skiing this weekend because of the extra hours you cost me, er, my client! Oh, and in case you are wondering, this post has at least several million verbits. I like to think it is because of the embedded Microsoft error messages.
At this point it will clarify things later if I explain I had just recently added a 2nd hard drive to offload the backups onto. When troubleshooting, one of the things you always look for is what changed. Chances are, the new thing is somehow involved in the problem. Factor the new hard drive in with the fact that most backup problems I have had in the past are simply security related (that's why you can't "open the backup device" when you try to backup databases to your Desktop -- unless you are running SQL Server as local system or administrator) and you'll understand why I quickly had myself convinced that the problem was related to the security settings of the new hard drive.
After spending several hours meticulously examining security settings, permissions, verifying accounts, inheritance, etc, it finally dawned on me that the problem had nothing to do with permissions. I was running a maintenance plan on a database that happened to be running in simple recovery mode! There is no option to backup a transaction log for simply recovery databases. An error message that at the indicated "One or more databases are using the simple recovery model" would have saved hours of red-herring work.
Thanks Microsoft! I'm going skiing this weekend because of the extra hours you cost me, er, my client! Oh, and in case you are wondering, this post has at least several million verbits. I like to think it is because of the embedded Microsoft error messages.
