Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #271

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

Show all headers | View raw

mat (mat@notarealdotcom.adr) writes:
> 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.
 
> 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@sommarskog.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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

How can I check out the reason a db is in recovery? mat <mat@notarealdotcom.adr> - 2011-04-26 07:39 -0700
  Re: How can I check out the reason a db is in recovery? Henk van den Berg <hvandenberg@xs4all.nl> - 2011-04-26 17:31 +0200
  Re: How can I check out the reason a db is in recovery? Erland Sommarskog <esquel@sommarskog.se> - 2011-04-26 22:45 +0200
    Re: How can I check out the reason a db is in recovery? mat <mat@notarealdotcom.adr> - 2011-04-27 08:34 -0700
      Re: How can I check out the reason a db is in recovery? Erland Sommarskog <esquel@sommarskog.se> - 2011-04-27 23:58 +0200
        Re: How can I check out the reason a db is in recovery? mat <mat@notarealdotcom.adr> - 2011-04-28 08:44 -0700
          Re: How can I check out the reason a db is in recovery? Erland Sommarskog <esquel@sommarskog.se> - 2011-04-28 23:58 +0200
            Re: How can I check out the reason a db is in recovery? Henk van den Berg <hvandenberg@xs4all.nl> - 2011-04-29 08:05 +0200
          Re: How can I check out the reason a db is in recovery? Henk van den Berg <hvandenberg@xs4all.nl> - 2011-04-29 07:59 +0200

csiph-web