| Author |
Topic |
|
matt_calhoon
Posting Yak Master
235 Posts |
 Posted - 2004-07-05 : 22:30:37
|
| Hi there,recently I had run out of space on my sql 7.0 box. This has caused a few problems and now I have a new one;Whenever I restore a database it marks that database as suspect or Loading status. How can I fix this so the database is not loading - I checked the ldf and mdf file and it was in the correct location.I have tried:sp_resetstatusand DBCC DBRECOVER but has not helped!Any help appreciated. |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2004-07-05 : 22:35:22
|
| Did you try putting WITH RECOVERY on your restore statement? That should be the default, so I fear something like a corrupt backup, but it is worth a try to explicitly say WITH RECOVERY.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-05 : 22:53:54
|
| Also, are there any error messages produced in the SQL Server error logs when you do this?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
matt_calhoon
Posting Yak Master
235 Posts |
Posted - 2004-07-05 : 23:31:56
|
| I can successfully restore this database backup on 2 other machines, so the backup is fine. It seems apparant that if I do a restore, it corrupts the database.Another thing worth mentioning > 1. I created a new database and tried to restore this db from a backup2. the restore was successful but left db in suspect state.3. Tried to restore it again and it then restored A DIFFERENT DATABASE (the first database in EP as it starts with A) and rendered it suspect. At this stage I have stopped trying to restore any databases. |
|
|
|
matt_calhoon
Posting Yak Master
235 Posts |
Posted - 2004-07-05 : 23:34:21
|
| the only logs I see are:2004-07-06 09:42:04.92 spid26 Bypassing recovery for database 'AB_CRC' because it is marked IN LOAD. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-05 : 23:39:33
|
| The step 3 you posted would really worry me. Do you have the latest service pack for Windows and SQL Server installed?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
matt_calhoon
Posting Yak Master
235 Posts |
Posted - 2004-07-05 : 23:48:04
|
| Believe me, IM WORRIED!!!Im trying to determine what is going on with my sql box that has worked fine for most of its life. SQL 7.0 sp4. Windows 2000 with latest service packs.The running out of space issue couldnt of helped. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-06 : 00:41:01
|
| Running out of space issues can always be helped, and shouldn't ever occur period. You need to run a DBCC CHECKDB on all of your system databases and see if there are any errors. If that doesn't fix this, I would call Microsoft PSS. It costs $250 per instance and is money well spent.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
matt_calhoon
Posting Yak Master
235 Posts |
Posted - 2004-07-06 : 01:00:35
|
| The DBCC did not come up with errors.Unfortunately systems do run out of space if not monitored correctly... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-06 : 05:52:10
|
| The filesystem isn't knackered is it? Might be worth trying a CHKDSK (or whatever its now called) from DOS, on the physical drives - but you'll have to shut down SQL whilst its runningKristen |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2004-07-09 : 12:25:39
|
| Does anything else show up in the errorlogs or event logs? Perhaps a hardware error?-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
rcameron46
Starting Member
1 Post |
Posted - 2004-07-13 : 07:39:05
|
| I am havng this exact same problem, but I did not run out of disk space. I normally restore our backup database weekly to what we call our test data. Been doing this for months and all of a sudden when I restore I get a successfull completetion of the restore, but the status stays in loading for ever. I then have to detach and delete and start over. I can restore other databases on this server, but not our production database. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-13 : 07:42:18
|
| What SQL Server/Windows version and service pack are you on rcameron?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
matt_calhoon
Posting Yak Master
235 Posts |
Posted - 2004-07-13 : 20:24:31
|
| After receiving some help from Microsoft support, I executed the following stored procedure on the MSDB database:sp_delete_backuphistory [ @oldest_date = ] 'oldest_date' I then tried to restore a database and it didnt put it into a "loading" state.It seemed as though the backupfile table was corrupt. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-14 : 02:24:42
|
| Ah, that's useful to knowKristen |
|
|
|
|
Database LOADING status after restore