How can I check out the reason a db is in recovery?

#1

This AM a database on my dev box is listed as being in recovery. I
checked the sql server log (in SSMS Management/SQL Server Logs) and all
it says is that there was a serious error. This db was not used for a
couple of days and the machine didn't crash or anything like that so I'm
disturbed that 'somehow' it became broken and I'd like to explore why.

The log I've seen so far just says

Error: 18456, Severity: 14, State: 38.

I can stop the db engine and delete/restore no problem (is a dev copy,
not production) but I'd rather see what else I might be able to learn
from this. What else can I check?

#2

On 26-04-2011 16:39, mat wrote:

Quote:

This AM a database on my dev box is listed as being in recovery. I
checked the sql server log (in SSMS Management/SQL Server Logs) and all
it says is that there was a serious error. This db was not used for a
couple of days and the machine didn't crash or anything like that so I'm
disturbed that 'somehow' it became broken and I'd like to explore why.

The log I've seen so far just says

Error: 18456, Severity: 14, State: 38.

Check out this site for an explanation of the State value

http://sqllearnings.blogspot.com/200...in-failed.html

#3

mat (mat (AT) notarealdotcom (DOT) adr) writes:

Quote:

This AM a database on my dev box is listed as being in recovery. I
checked the sql server log (in SSMS Management/SQL Server Logs) and all
it says is that there was a serious error. This db was not used for a
couple of days and the machine didn't crash or anything like that so I'm
disturbed that 'somehow' it became broken and I'd like to explore why.

So there is a stack dump in the log? The stack dump is not entirely
easy to read, but usually there is some indication of the statement
that was issued. Also, the date and time for the accident may give
some indication.

Maybe you have a maintenance job set up and something went wrong during
this job. Why something would go wrong all of a sudden? Bad hardware is
a common reason.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

#4

In article <Xns9ED3E7821B07FYazorman (AT) 127 (DOT) 0.0.1>, esquel (AT) sommarskog (DOT) se
says...

Quote:

mat (mat (AT) notarealdotcom (DOT) adr) writes:
This AM a database on my dev box is listed as being in recovery. I
checked the sql server log (in SSMS Management/SQL Server Logs) and all
it says is that there was a serious error. This db was not used for a
couple of days and the machine didn't crash or anything like that so I'm
disturbed that 'somehow' it became broken and I'd like to explore why.

So there is a stack dump in the log? The stack dump is not entirely
easy to read, but usually there is some indication of the statement
that was issued. Also, the date and time for the accident may give
some indication.

Maybe you have a maintenance job set up and something went wrong during
this job. Why something would go wrong all of a sudden? Bad hardware is
a common reason.

One thing I recall is that the production version of this database was
in recovery mode a couple of weeks ago. My copy is a restored backup of
that. The production db was damaged by someone at the data center
pulling out the wrong network cable.

I wonder if the restore my db went into might be related? If a db is
damaged and recovered, can it remain unstable?

I eventually gave up on allowing the recovery to complete so I stopped
the sql server instance, renamed the two mdf and ldf, and restored a
fresh backup. The restore took much much longer than it used to, and it
did the previous time also. I wonder if the restore took so long because
of the damaged db/recovery from a couple of weeks ago? Is that possible?

#5

mat (mat (AT) notarealdotcom (DOT) adr) writes:

Quote:

One thing I recall is that the production version of this database was
in recovery mode a couple of weeks ago. My copy is a restored backup of
that. The production db was damaged by someone at the data center
pulling out the wrong network cable.

I wonder if the restore my db went into might be related? If a db is
damaged and recovered, can it remain unstable?

Yes. More precisely, if a database is victim to a hardware crash, it is very
likely there is some degree of corruption, and this can certainly cause
trouble later on.

Quote:

I eventually gave up on allowing the recovery to complete so I stopped
the sql server instance, renamed the two mdf and ldf, and restored a
fresh backup. The restore took much much longer than it used to, and it
did the previous time also. I wonder if the restore took so long because
of the damaged db/recovery from a couple of weeks ago? Is that possible?

It is conceivable. In any case, you should run DBCC CHECKDB on the
database when you have restored it. Furthermore, you should run in
on the production database as well. If you have corruption in there,
you may be up for trouble in production as well.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

#6

In article <Xns9ED4F3F36D000Yazorman (AT) 127 (DOT) 0.0.1>, esquel (AT) sommarskog (DOT) se
says...

Quote:

mat (mat (AT) notarealdotcom (DOT) adr) writes:
One thing I recall is that the production version of this database was
in recovery mode a couple of weeks ago. My copy is a restored backup of
that. The production db was damaged by someone at the data center
pulling out the wrong network cable.

I wonder if the restore my db went into might be related? If a db is
damaged and recovered, can it remain unstable?

Yes. More precisely, if a database is victim to a hardware crash, it is very
likely there is some degree of corruption, and this can certainly cause
trouble later on.

I eventually gave up on allowing the recovery to complete so I stopped
the sql server instance, renamed the two mdf and ldf, and restored a
fresh backup. The restore took much much longer than it used to, and it
did the previous time also. I wonder if the restore took so long because
of the damaged db/recovery from a couple of weeks ago? Is that possible?

It is conceivable. In any case, you should run DBCC CHECKDB on the
database when you have restored it. Furthermore, you should run in
on the production database as well. If you have corruption in there,
you may be up for trouble in production as well.

I ran that and after many lines on the tables it ended with

CHECKDB found 0 allocation errors and 0 consistency errors in database
'mydb'.

I guess that means it's a healthy database? Nothing in the check output
looked like a report of problems.

Is there a way to 'rebuild' a database, as if one was importing from
scratch with a sql script? Even with the production db I have the
ability to take it offline in the evening for hours. Because the db
seems kind of not right, I'd like to at least consider doing this.
Another reason I'd like to do it is because the db has had shrink run on
it many times. Apparently that can cause index fragmentation, and a
performance hit as a result.

I don't think there is a way using tools included with SSMS to export an
entire db (minus log) to a script and then recreate by executing the
script? Do you have any recommondation for such a utility? The data file
is about 500mb.

Thank you for your help with this.

#7

On 28-04-2011 17:44, mat wrote:

Quote:

In article<Xns9ED4F3F36D000Yazorman (AT) 127 (DOT) 0.0.1>, esquel (AT) sommarskog (DOT) se
says...

mat (mat (AT) notarealdotcom (DOT) adr) writes:
One thing I recall is that the production version of this database was
in recovery mode a couple of weeks ago. My copy is a restored backup of
that. The production db was damaged by someone at the data center
pulling out the wrong network cable.

I wonder if the restore my db went into might be related? If a db is
damaged and recovered, can it remain unstable?

Yes. More precisely, if a database is victim to a hardware crash, it is very
likely there is some degree of corruption, and this can certainly cause
trouble later on.

I eventually gave up on allowing the recovery to complete so I stopped
the sql server instance, renamed the two mdf and ldf, and restored a
fresh backup. The restore took much much longer than it used to, and it
did the previous time also. I wonder if the restore took so long because
of the damaged db/recovery from a couple of weeks ago? Is that possible?

It is conceivable. In any case, you should run DBCC CHECKDB on the
database when you have restored it. Furthermore, you should run in
on the production database as well. If you have corruption in there,
you may be up for trouble in production as well.

I ran that and after many lines on the tables it ended with

CHECKDB found 0 allocation errors and 0 consistency errors in database
'mydb'.

I guess that means it's a healthy database? Nothing in the check output
looked like a report of problems.

Is there a way to 'rebuild' a database, as if one was importing from
scratch with a sql script? Even with the production db I have the
ability to take it offline in the evening for hours. Because the db
seems kind of not right, I'd like to at least consider doing this.
Another reason I'd like to do it is because the db has had shrink run on
it many times. Apparently that can cause index fragmentation, and a
performance hit as a result.

I don't think there is a way using tools included with SSMS to export an
entire db (minus log) to a script and then recreate by executing the
script? Do you have any recommondation for such a utility? The data file
is about 500mb.

-- Yeah there is.

-- First thing to do: generate the CREATE TABLE script from SSMS
-- Generate a INSERT INTO script for the data

-- Here' a very simple example that you can adapt to your situation:
-- Let's say you have on your production box Table1

create table Table1 (f1 char(20), f2 int)

insert into Table1 ( f1, f2) values ('v1', 12)
insert into Table1 (f1, f2) values ('v2', 13)

-- Create Table2 on your production box:
create table Table2 (f1 char(20), f2 int)

-- Generate the "insert into" script for Table2 based on the data of Table1

-- Make sure that you have output to text by hitting ctrl-t

SELECT ' insert into Table2 (f1, f2) values( ''' + ltrim(rtrim(f1)) +
''',' + ltrim(rtrim(cast( f2 as char(30))) ) + ')' from Table1

-- Now you can select the generated output and copy it to a query window

-- One caveat: If you have big tables, you may want to use this trick
with 1000 records at a time perhaps!

-- Oh yeah, remember that this example uses two different tables on the
same box! Copy the generated script to your dev box and replace "Table2"
with "Table1" before executing.

Hope this helps,
Henk

#8

Remember: create index scripts and all the other relevant stuff (PK, FK)
is not addressed in this example.
Also make sure that you execute your INSERT INTO scripts in the correct
order.
Foreign key data in tableX can't be inserted if tableY with the matching
primary key hasn't been filled yet!

Best,
Henk

On 28-04-2011 20:14, Henk van den Berg wrote:

Quote:

On 28-04-2011 17:44, mat wrote:
I don't think there is a way using tools included with SSMS to export an
entire db (minus log) to a script and then recreate by executing the
script? Do you have any recommondation for such a utility? The data file
is about 500mb.

-- Yeah there is.

-- First thing to do: generate the CREATE TABLE script from SSMS
-- Generate a INSERT INTO script for the data

-- Here' a very simple example that you can adapt to your situation:
-- Let's say you have on your production box Table1

create table Table1 (f1 char(20), f2 int)

insert into Table1 ( f1, f2) values ('v1', 12)
insert into Table1 (f1, f2) values ('v2', 13)

-- Create Table2 on your production box:
create table Table2 (f1 char(20), f2 int)

-- Generate the "insert into" script for Table2 based on the data of Table1

-- Make sure that you have output to text by hitting ctrl-t

SELECT ' insert into Table2 (f1, f2) values( ''' + ltrim(rtrim(f1)) +
''',' + ltrim(rtrim(cast( f2 as char(30))) ) + ')' from Table1

-- Now you can select the generated output and copy it to a query window

-- One caveat: If you have big tables, you may want to use this trick
with 1000 records at a time perhaps!

-- Oh yeah, remember that this example uses two different tables on the
same box! Copy the generated script to your dev box and replace "Table2"
with "Table1" before executing.

Hope this helps,
Henk

#9

Naturally, the same goes for UDF's, sprocs and all the other objects.

On 28-04-2011 20:28, Henk van den Berg wrote:

Quote:

Remember: create index scripts and all the other relevant stuff (PK, FK)
is not addressed in this example.

#10

mat (mat (AT) notarealdotcom (DOT) adr) writes:

Quote:

CHECKDB found 0 allocation errors and 0 consistency errors in database
'mydb'.

I guess that means it's a healthy database? Nothing in the check output
looked like a report of problems.

Nevertheless, you had some serious problem with a copy of the database
on your machine. Of course, it may been due to some damage that happened
when you copied the database.

I have actually run DBCC on a database, backed it up, restored it, and
ran DBCC again - to see new errors appear. But that was with SQL 6.0
which had quite a different architecture.

So the database looks clean on that level.

Quote:

Is there a way to 'rebuild' a database, as if one was importing from
scratch with a sql script? Even with the production db I have the
ability to take it offline in the evening for hours. Because the db
seems kind of not right, I'd like to at least consider doing this.
Another reason I'd like to do it is because the db has had shrink run on
it many times. Apparently that can cause index fragmentation, and a
performance hit as a result.

Yes, you should not shrink your database but in very exceptional cases.

If you are on SQL 2008, you can script a database including data from
SSMS. For SQL 2005, you can use the Database Publishing Wizard.

But it's better to just ALTER INDEX REBUILD on the tables if all you
want to do is to defragment. Scripting and recreating is more risky -
those tools are not 100% robust.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx