August 22, 2008 at 9:40 am
Hi,
I have a database Workspace (150 mdf and 10 ldf file sizes), in SQL Server Express edition.
I am having trouble at first time when trying to query tables inside Workspace database, when restarted the SQL Server and even after waited for 5-10 min.
In Details:
I stop SQL Server and start again, wait for 10 min, run query against Workspace, in another window (SSMS) I run a loop which query the sys.database [name] and [state_desc] for RECOVERYING status. The moment I issue the query againt Workspace I see Workspace database name with RECOVERING status.
Please, could any body give me some idea on this.
I set database auto_close false.
Thanks,
Sri.
August 24, 2008 at 12:01 pm
Look at the log.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 24, 2008 at 2:18 pm
Thanks Paul.
Inside log file I don't see the following messages for the available databases when I restart sql server service.
Example:
2008-08-24 15:10:57.07 spid3s Starting up database 'master'.
If I run a query for example say:
select * from Workspace .sys.tables in SSMS
Immediately the log file get updated with this message
2008-08-24 15:11:3.03 spid52s Starting up database 'Workspace '.
Now I don't see any recovery errors any more for Workspace database.
If we go back and check sql server 2000 log after restarting the server, we see:
Starting up database 'Workspace '.
Why not in SQL Server Express or SQL Server 2005 ?
Thank you for you valuable time.
Sri.
August 25, 2008 at 12:08 am
EXEC sp_resetstatus 'your_DB';
ALTER DATABASE your_DB SET EMERGENCY
DBCC checkdb('your_DB')
ALTER DATABASE your_DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('your_DB', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE your_DB SET MULTI_USER
replace your_db with the name of the Database.
Jay
August 25, 2008 at 12:40 am
Repair should be an absolute last resort, not the first thing to try. It should also never be done without ascertaining if there is corruption and if so, how bad.
Sri: It sounds like autoclose is still enabled. Could you please run the following.
select name, is_read_only, is_auto_close_on, is_auto_shrink_on, is_in_standby, is_cleanly_shutdown, recovery_model, state_desc
from sys.databases where name = 'Workspace'
There's nothing in the error log to indicate an error? When you run the query against sys.tables, do you get an error, and if so, what is it?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2008 at 8:15 am
Hi Gail,
select name, is_read_only, is_auto_close_on, is_auto_shrink_on, is_in_standby, is_cleanly_shutdown, recovery_model, state_desc
from sys.databases where name = 'WORKSPACE'
Resultset:
WORKSPACE000003ONLINE
The WORKSPACE database is not corrupted, it works fine once recovered 100%. Only thing I have to do to get 100% recovered after restarting the server is just ran a sample query say for example:
select [name] from workspace.sys.databases where 1=2
This dummy sql stmt wakes/run WORKSPACE database and I can see the message in the log file. From here onwards I don't get any problem at all (good to go).
--- These are the results from the query which Jay sends ---
Hello Jay:
2008-08-25 10:02:50.64 spid51 Setting database option EMERGENCY to ON for database WORKSPACE.
2008-08-25 10:02:50.90 spid51 Starting up database 'WORKSPACE'.
2008-08-25 10:02:50.90 spid51 The database 'WORKSPACE' is marked EMERGENCY_MODE and is in a state that does not allow recovery to be run.
2008-08-25 10:02:55.68 Server CPU time stamp frequency has changed from 399223 to 3192273 ticks per millisecond. The new frequency will be used.
2008-08-25 10:03:26.82 spid51 DBCC CHECKDB (WORKSPACE) executed by CONSUMERPOINT found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 5 seconds.
2008-08-25 10:03:51.61 spid51 Setting database option SINGLE_USER to ON for database WORKSPACE.
2008-08-25 10:04:00.42 spid51 SQL Server has encountered 3 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2008-08-25 10:04:00.42 spid51 SQL Server has encountered 3 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2008-08-25 10:04:00.42 spid51 SQL Server has encountered 3 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2008-08-25 10:04:00.42 spid51 Starting up database 'WORKSPACE'.
2008-08-25 10:04:07.11 spid51 EMERGENCY MODE DBCC CHECKDB (WORKSPACE, repair_allow_data_loss) executed by CONSUMERPOINT found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 6 seconds.
2008-08-25 10:04:10.20 spid51 Setting database option MULTI_USER to ON for database WORKSPACE.
---
My understanding here was, I am missing some thing simple setup that will make the database run whenever we restart sql server service.
Thanks for you valuable time Jay and Gail.
Sri.
August 25, 2008 at 10:17 am
Strange. It's as if it is in autoclose, despite not being set that way. That's certainly not normal behaviour. Strange. I'll look into this.
Some advice: CheckDB with any form of repair is a dangerous thing to run on a database and should not be run unless you know the database is corrupt, there's no way to fix it (including restoring from backup) and you have a good idea exactly what that statement is going to do.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply