DBCC single user problems - 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 2008 Forums

SQL Server Administration (2008)

DBCC single user problems

Author

Topic

Exgliderpilot
Starting Member

14 Posts

Posted - 2010-05-07 : 08:13:23

Hi I have a database that when
--ALTER DATABASE ThisOne SET single_user WITH ROLLBACK IMMEDIATE
--go

--DBCC checkdb ('ThisOne ', repair_allow_data_loss)
--go

was run, terminated with:

DBCC results for 'MemberSubSubSubQualName'.
There are 19940 rows in 61 pages for object "MemberSubSubSubQualName".
CHECKDB found 0 allocation errors and 51 consistency errors in database 'NursingPersonnel'.
CHECKDB fixed 0 allocation errors and 51 consistency errors in database 'NursingPersonnel'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 682, Level 22, State 146, Line 2
Internal error. Buffer provided to read column value is too small. Run DBCC CHECKDB to check for any corruption.
(the last two lines were repeated a dozen times)
then SSMS said - 'disconnected' at the bottom

I went to config manager and SQL was not running.
Rebooted the machine
SQL running but now in SSMS

The datbse is showing single user mode

and trying to run

ALTER DATABASE Broken SET MULTI_USER

Msg 924, Level 14, State 1, Line 1
Database 'Broken' is already open and can only have one user at a time.

Staff bank ageny software http:\\www.ava.co.uk

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-07 : 08:59:50

Someone has the sole allowed connection. Find that connection and kill it. Then you should be able to connect and change it to multi-user

btw, do you know how much data was lost by the repair? Was restoring from a clean backup not an option?

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-05-07 : 09:34:21

Gail: Rather than finding & killing the connection would running another

ALTER DATABASE ThisOne SET single_user WITH ROLLBACK IMMEDIATE

give (myself) the single connection?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-07 : 11:11:31

Testing....

Nope.
I set a DB to single user mode and connected a query window to it. Opened another query window (in master) and ran this:
ALTER DATABASE testing SET MULTI_USER WITH ROLLBACK IMMEDIATE

Result (after several seconds)
Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'testing' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

Attempting to set to single user gave exactly the same result.

Manually killing the connection and then running the alter database worked instantly.

--
Gail Shaw
SQL Server MVP

Exgliderpilot
Starting Member

14 Posts

Posted - 2010-05-07 : 11:28:34

Many thanks, I ran sp_who and killed the Id that was causing the problem. I have restored from a clean backup which is only a few hours old. Copying the detached (corrupted) files over for further analysis.

Thanks chaps

Staff bank ageny software http:\\www.ava.co.uk

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-07 : 12:02:40

Have you checked that the restored database is corruption-free? Are you sure that it occurred after that backup was taken?

--
Gail Shaw
SQL Server MVP

Exgliderpilot
Starting Member

14 Posts

Posted - 2010-05-07 : 12:39:23

Hi Gail
Yes thanks for your concern. I did a test restore on a different machine and it passed:

go

DBCC CHECKALLOC

go

DBCC CHECKDB

go

DBCC CHECKCATALOG

go

Which the corrupt databse did not.

The thought was a very good and important one. Thanks again!

Staff bank ageny software http:\\www.ava.co.uk

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-07 : 14:36:23

If this is SQL 2005, CheckDB runs checkalloc and checkcatlog. No need to run them separatly. On SQL 2000, CheckDB ran checkalloc, but not checkcatalog

--
Gail Shaw
SQL Server MVP

Exgliderpilot
Starting Member

14 Posts

Posted - 2010-05-08 : 05:12:31

Its 2008, So I guess it effectively ran checkalloc and checkcatlog twice. Thanks for the added info

Staff bank ageny software http:\\www.ava.co.uk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources