Re: URGENT: SQL 6.5 Server Crash
From: Google Mike (googlemike_at_hotpop.com)
Date: 09/15/04
- Next message: TomT: "RE: Qry Analyzer Debug broken: XP SP2"
- Previous message: mickbw: "Re: Insert Into Select From Databases on 2 Servers"
- In reply to: Tibor Karaszi: "Re: URGENT: SQL 6.5 Server Crash"
- Messages sorted by: [ date ] [ thread ]
Date: 15 Sep 2004 07:38:47 -0700
"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com>
wrote in > 1. Let MS help you though this. This is not something that
can be done over a newsgroup
> conversation.
>
> 2. Meanwhile, we can try to assist you in sorting out a few things. Most important is a clear message of exactly what backups you have.
> Are these database backup done by SQL Server?
> Or file backups performed by some Windows backup software?
> If the later, were they performed while SQL Server was running?
> Also, check out the error message in SQL Server's errorlog file for when you start SQL Server. Also
> the Windows eventlog. Write down or copy the errors. These will be needed by MS when trying to
> determine what exactly happened.
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
Would you believe it? In fact we found out that someone on our admin
team switched our Brightstor transaction backup, which we do on the
hour, so that it was set to do a FULL backup, not just the TRANSACTION
backup! No wonder it was running out of locks! Once we fixed that, we
eliminated the problem.
But the other problem we had after this explosion was that our msdb
database got corrupted on SQL 6.5. Microsoft Premier Support then told
us they moved from a policy of "best effort" support on 6.5 to "no
support". Luckily, however, about 4 hours later our TAM was kind
enough to review the status and surprise me by calling me to ask if
they could take the ticket just this once as a good-will gesture.
Surprising to see that from Microsoft! :) So, we gave them the chance
and they used a whole mess of silly commands to get this server up and
with a rebuilt msdb. It was a miracle that we recovered this server.
We have like $10M of financial data on it. Another problem we had was
that our Brightstor backup only backed up the data, to my surprise,
not the schema as well. In fact, no one had exported the schema since
December 2003! So, now we have a policy to export the schema once a
week so that we're not put in this nail-biting situation again.
As for the commands and the process, it's more like a flowchart and
not something you can accomplish easily in a newsgroup. You're better
off calling Microsoft, but the sad news for anyone still stuck with
6.5 is that you can't get support on it anymore except from perhaps a
third party. We were lucky to even get support. If we weren't such a
huge business for MS, we probably would never have gotten the support.
Meanwhile, I'm going to do everything in my power in the coming weeks
to get us at least to SQL 7. We have these spaghetti code Delphi 3
apps (which I inherited) and they only have drivers to talk up to SQL
7. Hours and hour of getting the thing to talk to SQL 2000 have
failed. We could migrate to SQL 7 and set the database in 6.5
compatibility mode. We can't upgrade the Delphi 3 apps because mgmt
won't spend the dollars on that when we already have another project
in parallel to migrate us to another platform to replace these apps,
but that's 2 more years away to completion.
Some of the commands were:
sqlservr -c
-> This brings the server online in normal mode, exclusive of running
as a service, at a command prompt. (You can read the other startup
options in the SQL 6.5 books online.) This gave MS tech support an
idea of what was going on. They determined msdb was corrupt and that
was why the sqlservr would abruptly abend after about a minute.
<ctrl + c the cmd window to stop SQL between calls to the sqlservr.exe
command>
sqlservr -c -m -T 3608
-> This brings the server online as an application in single user
mode, recovers only the master database, and ad-hoc updates to the
system catalogs will be enabled.
sqlservr -c
begin tran
update master..sysdatabases set status = -32768 where name = 'your
database name'
-> This puts a database in emergency mode so that you can at least
boot your SQL server and poke around for more statistics. Then, if
that worked (you get a result of 1 row updated), do "commit".
<rename the msdb dat files>
drop database msdb
<load instmsdb.sql from INSTALL dir and run it>
<got an error, which is normal>
<shutdown db, rename the msdb dat files back, run sql with -c in cmd
again, and then attempt to load instmsdb.sql and run again -- should
work this time>
begin tran
update master..sysdatabases set status = 0 where name = 'your database
name'
-> This puts a database in normal recovery mode so that you can
continue. Then, if that worked (you get a result of 1 row updated), do
"commit".
sqlservr -c
--> to see if it starts to boot up normally and recover okay. We
crossed our fingers and it did. We then stopped this with CTRL+C once
it was stable and moved to starting it as a service. This through our
databases in one more recovery mode and we then did a quick backup,
dbcc checkdb, and then we scripted the databases so that we would have
the schema, not just the data, in case of a future crash.
- Next message: TomT: "RE: Qry Analyzer Debug broken: XP SP2"
- Previous message: mickbw: "Re: Insert Into Select From Databases on 2 Servers"
- In reply to: Tibor Karaszi: "Re: URGENT: SQL 6.5 Server Crash"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
