can't dbcc checkdb in single user mode

[360degreehosting]

Hi,

I have a database in MS SQL 2000 Server that I need to run the dbcc checkdb (db, REPAIR_ALLOW_DATA_LOSS)

I have tried putting the whole instance in single user mode with sqlservr -m and just the db in Enterprise Mgr. Which in it's self works. But when I try to connect with Query Analyzer or osql it says that i can't connect in single user mode.

I have stopped MS SQL Agent, IIS Admin, I have gone through Windows Services and stopped everything that isn't absolutely needed - but I am unable to find what is beating me in connecting to sql.

Does anyone have any suggestions please?

Regards,
Steve

 

[vbkris]

>>But when I try to connect with Query Analyzer or osql it says that i can't connect in single user mode.

hmm, i guess once you put in single user mode, the process that made it single user will be the only one capable of accessing it. it will deny other connections (which is what is happening when you open the query analyser)...

Known is handfull, Unknown is worldfull

 

[RobertT687]

I hit this problem once myself. Here is how I got around it. Run this script either from OSQL of from Query Analyzer:

use master
go
exec sp_dboption 'YourDBName', 'single user' , 'on'
DBCC checkdb('YourDBName',REPAIR_ALLOW_DATA_LOSS)
exec sp_dboption 'YourDBName', 'single user' , 'off'
go

Hope this does the trick.

 

[mrdenny]

If you start the SQL Server in single user mode and still can't log in that means that something else is logging in with sysadmin permissions. Probably your monitoring system.

Start the server up as normal, kick all users out of the user database, put it in single user mode and run dbcc checkdb. you may want to do this all in a single script so that you have a better chance of nothing else trying to get in there.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com