Help Getting Back a Suspect Database

I had a database grow from 15Mb to 15Gb last week while I
was out of town and the standard Backups and Trans Log
Backups and Shrink Maintenance Plans didnt keep its size
down.
Well anyways now the log file is 15gb and the data file
is 15gb and SQL Enterprise Manager now reports the
database as suspect and I cant back it up or shrink it to
try and bring it down to a manageable size. The valid
backups are over a week old is there any way to recover
this database? It says that there is insufficient space
on the disk to work with the database. I have 70 gig free
now but it is still down. Any advice would be
appreciated. Thank you.did you try sp_resetstatus?
"Jerod" <jlindblom@.axonom.com> wrote in message
news:4a3101c3e445$53838370$a401280a@.phx.gbl...
> I had a database grow from 15Mb to 15Gb last week while I
> was out of town and the standard Backups and Trans Log
> Backups and Shrink Maintenance Plans didnt keep its size
> down.
> Well anyways now the log file is 15gb and the data file
> is 15gb and SQL Enterprise Manager now reports the
> database as suspect and I cant back it up or shrink it to
> try and bring it down to a manageable size. The valid
> backups are over a week old is there any way to recover
> this database? It says that there is insufficient space
> on the disk to work with the database. I have 70 gig free
> now but it is still down. Any advice would be
> appreciated. Thank you.|||Hi,
The below procedure is listed in on of the articles
published at SQLServerCentral.com by Brian Knight
A database can be marked for many reasons. Generally it
falls into the following conditions :
A database or log file is missing.
In SQL 6.5, a device may not be present or in 7.0/2000 a
file may not exist.
SQL Server may not have been able to restore the database
in ample time.
The database could be corrupt.
The database is being help by the operating system. This
could be a 3rd party backup software or defrag software.
I've had even a virus scanning software cause this once.
SQL Server does not have enough space to recover the
database on startup.
To fix this problem, perform the following functions:
Review the SQL Server and NT error logs to see if you can
find where the problem occured.
Start SQL Server in single user mode.
Go to your control panel and services.
Stop SQL Server
Add the -m switch in the parameters pane below.
Start SQL Server
Run sp_resetstatus with the @.dbname parameter. (ie :
sp_resetstatus @.dbname = "pubs")
Perform detailed DBCC checks (CHECKDB, CHECKALLOC, etc)
Run a few random queries to see if you experience any
problems.
If no problems occur, stop and start SQL Server and open
the database to production.
As an absolute last resort, you can place your database in
emergency mode. By placing it in this mode, you will be
allowed to copy data out of the database, even if the data
is corrupt. To place your database in emergency mode, use
the following command:
SP_CONFIGURE 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO
UPDATE master..sysdatabases set status = -32768 WHERE name
= 'pubs'
GO
SP_CONFIGURE 'allow updates', 0
RECONFIGURE WITH OVERRIDE
You can then BCP data out and place it into a different
database.
HTH
--
Regards
THIRUMAL REDDY MARAM
SysAdmin/SQLServerDBA
>--Original Message--
>did you try sp_resetstatus?
>
>"Jerod" <jlindblom@.axonom.com> wrote in message
>news:4a3101c3e445$53838370$a401280a@.phx.gbl...
>> I had a database grow from 15Mb to 15Gb last week while
I
>> was out of town and the standard Backups and Trans Log
>> Backups and Shrink Maintenance Plans didnt keep its size
>> down.
>> Well anyways now the log file is 15gb and the data file
>> is 15gb and SQL Enterprise Manager now reports the
>> database as suspect and I cant back it up or shrink it
to
>> try and bring it down to a manageable size. The valid
>> backups are over a week old is there any way to recover
>> this database? It says that there is insufficient space
>> on the disk to work with the database. I have 70 gig
free
>> now but it is still down. Any advice would be
>> appreciated. Thank you.
>
>.
>