Suspect Database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Suspect Database

Author

Topic

Kleber
Yak Posting Veteran

67 Posts

Posted - 2004-01-27 : 12:57:47

Hi all

I have a SQL Server 7.0 with a suspect database caused by insufficient disk space.

Now, i discovered that my operation team didn't make any backup of that database. (Yes, i'm gonna kill them!!!). But now I ask:

How may i recovery the database, without backup, whitout open it (it is on suspect mode and reset status didn't solve it).

Tks
Kleber
Brazil

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-27 : 13:00:40

Did you first reclaim some space?

Did you do a checkdb?

What's in the error logs?

How big a DB we talking about here?

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-27 : 13:03:27

Have you tried moving the MDF and LDF files to another server and attaching them there? Do you have enough disk space now? What happened when you tried to reset the status?

And who's the DBA that didn't create backups for the database? It's his/her number one job responsibility.

Tara

Kleber
Yak Posting Veteran

67 Posts

Posted - 2004-01-27 : 13:05:25

I tried to use checkdb, but the database was on suspect mode, so it doesn't work.
The database is 28Gb space.
The error log shows only "E:\mssqldata\dtbCCO_Data.MDF: Operating system error 112(There is not enough space on the disk.) encountered." after this "Recovery progress on database 'dtbCCO' (7): 96 percent."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-27 : 13:10:59

So did you clean up the disk space yet? If not then do that, then try resetting the status. If that works, perform backup immediately. Then fire DBA. :-)

Tara

Kleber
Yak Posting Veteran

67 Posts

Posted - 2004-01-27 : 13:12:30

Have you tried moving the MDF and LDF files to another server and attaching them there?

We don't have how to do it. This database is 28gb in size.

Do you have enough disk space now?

We have now, only 3Gb of free space. I think it is not enough.

What happened when you tried to reset the status?

The database appears as normal, but when i tried to call propeties, it didn't open.

And who's the DBA that didn't create backups for the database? It's his/her number one job responsibility.

There is backup job prepared to do it. But the responsible people to execute it, simply didn't and we didn't saw it. (I will fire some people today)

Kleber

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-27 : 13:16:40

You only have a 31 gb HD?

Can you add another drive to the server...one signficantly more space?

Also, do have previous dumps on the drive?

If you have only 3gb, I know why the dump didn't occur....

drive space is your #1 priority I would say.

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-27 : 13:17:09

I don't know what "we don't have how to do it" means. Do you mean that no one knows how to attach a database on another server or that you don't have another server for this?

What do you mean try to call properties? Reset the status after cleaning up disk space, then try selecting some data in Query Analyzer.

No one has to manually execute a backup job in order for it to work. It should be scheduled daily at a minimum for full backups.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-27 : 13:18:37

quote:
Originally posted by tduggan

So did you clean up the disk space yet? If not then do that, then try resetting the status. If that works, perform backup immediately. Then fire DBA. :-)

Tara

Tara,

One tough SQL Warrior Princess....

The dba is at fault for not indicating they couldn't dump the database...

I betcha there are lots of old dumps eating up drive space...

What about the tranny logs?

Brett

8-)

Kleber
Yak Posting Veteran

67 Posts

Posted - 2004-01-27 : 13:19:11

What i mean is i don't have any server with 30gb of free space. Sorry, it is not a perfect world.

And, (now i know) it's hard to backup without a tape on drive.

But, backing to the problem. At disk there is only data and log files. Nothing else. So, i am trying to shrink the other bases, but, it is not working at all.

stephe40
Posting Yak Master

218 Posts

Posted - 2004-01-27 : 13:26:33

Are there old backup files on the drive that you could remove to free up some space? Are there any other logs from other apps stored on that drive?

- Eric

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-27 : 13:27:56

You do not need a tape drive on the server to perform a backup. As a matter of fact, this isn't recommended anyway. You should be backing up the database to disk, then copying the BAK and TRN files to a tape that is located on a remote server.

Are there any available bays on the server so that you can add another disk?

You need to find adequate free disk space in order to fix this problem especially since you don't have a backup.

After you reset the status of the database, were you able to select any data from the database using Query Analyzer?

Tara

Kleber
Yak Posting Veteran

67 Posts

Posted - 2004-01-27 : 13:33:12

There is no free bay on the server. I don´t have any disks to add (I'm sending buy now).
And when a reset the status of database, and I cannot select any data. Always give a message saying that database is suspect.

I will try to detach other databases to free some space.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-27 : 13:41:00

What is the command that you ran to reset the status? With 3GB of free space, it should have worked. Does the Event log give any information about disk problems?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-27 : 13:44:26

I would think you should set an alert as well....

Brett

8-)

Kleber
Yak Posting Veteran

67 Posts

Posted - 2004-01-27 : 13:50:51

Well
There is no disk error on event viewer.
And i really don't know why it didn'work. I used this:

USE master
GO
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_resetstatus Mydatabase
GO
sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-27 : 13:54:37

I found this in SQL Server Books Online:

quote:

Insufficient Disk Space
During recovery, it is a rare but possible occurrence for the server to require additional log or data space. If additional space is unavailable and either the log or data files cannot grow, the server:

Reports error message 9002 or 1105 in the Microsoft® SQL Server™ error log.

Marks the database as suspect.

Takes the database offline.
To resolve the 9002 error message and bring the database online

Free disk space on any disk drive containing the log file for the related database. Freeing disk space allows the recovery system to grow the log file automatically.

Reset the suspect status by executing sp_resetstatus.

Run recovery by executing DBCC DBRECOVER (database).
-or-

Free disk space on a different disk drive.

Move the transaction log files with an insufficient amount of free disk space to the disk drive in Step 1.

Detach the database by executing sp_detach_db.

Attach the database by executing sp_attach_db, pointing to the moved files.
-or-

Add a log file to the suspect database and run recovery on the database by executing sp_add_log_file_recover_suspect_db.
To resolve the 1105 error message and bring the database online

Free disk space on any disk containing a file in the filegroup mentioned in the 1105 error message. Freeing disk space allows the files in the filegroup to grow.

Reset the suspect status by executing sp_resetstatus.

Run recovery by executing DBCC DBRECOVER (database).
-or-

Free disk space on a different disk drive.

Move the data files in the filegroup with an insufficient amount of free disk space to the disk drive in Step 1.

Detach the database by executing sp_detach_db.

Attach the database by executing sp_attach_db, pointing to the moved files.
-or-

Add a data file to the suspect database and run recovery on the database by executing sp_add_data_file_recover_suspect_db.

Tara

Kleber
Yak Posting Veteran

67 Posts

Posted - 2004-01-27 : 14:41:30

Tara
I'd like to say thank you for your help. (and the others too)
This last post. Solve my problem.
Now I'm firing the DBA, Backup team and two managers.
This issue will not happen again. Thank you very much.

Regards
Kleber
Brazil

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-01-27 : 14:56:10

Tara ... where the hell in Books online did you find that ??? Thats a new thing I learned today.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-27 : 14:57:36

I went to sp_resetstatus (T-SQL) and saw a link to Insufficient Disk Space. But you can find it in the index by gonig to insufficient space error then to Insufficient Disk Space.

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-01-27 : 15:57:05

Need a new DBA? I've always wanted to visit Brazil. lol

I've had to do this before, only difference being I had backups (duh). Our manager was just too cheap to buy some space. After all, the business can't afford it.

They lost more being cheap on space and old servers then they would have ever lost by buying a server. Gotta love it.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources