SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to put database in single user mode while dbcc CheckDB?


How to put database in single user mode while dbcc CheckDB?

Author
Message
prakash shinde
prakash shinde
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 3
I am trying to repair database by dbcc checkdb with repiar_fast, but it ask me to keep database in single user mode. Pl. let me know how to put database in single user mode ?





Thanks
Tommy Bollhofer
Tommy Bollhofer
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3730 Visits: 3359
In MSSQL 2000 -



EXEC sp_dboption 'DatabaseName', 'single user', 'true'

GO



In MSSQL 2005 -



ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

ALTER DATABASE [DatabaseName] SET SINGLE_USER

GO

Tommy

Follow @sqlscribe
ChiragNS
ChiragNS
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6361 Visits: 1865
hi



You can do this with SSMS. Right click on the db and select Properties. In the Options tab set the Restrict access to SINGLE_USER.

"Keep Trying"
Paul Randal
Paul Randal
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7810 Visits: 1719
SQL Server 2005 doesn't recommend using REPAIR_FAST... I removed the REPAIR_FAST functionality when I rewrote DBCC CHECKDB for SS2005. If you look in Books Online you'll see that the option is only there now for back-compat and doesn't do anything.



Before doing any repair you should use the option to restore from your backups...

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Greg Milner
Greg Milner
SSC-Addicted
SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)

Group: General Forum Members
Points: 495 Visits: 494
Great SQL Tool for Recovering corrupted databases - "Recovery for SQL Server"



I had someone from another unit of my company contact me yesterday. He had a database corrupted with a torn page error. (Yes, their RAID had gone on the fritz.) Many of the tables were unqueriable (is that a word?) and there were all sorts or problems and errors. I won't go into the sordid details.



Anyway, guess how recent their most recent backup was... right... NEVER! Doh!!!



So I found this tool. It will script out your entire database from a stand alone .mdb file and it will get as much of the data out for you as you are probably going to get. Check it out and save this link. It's $500 for a single user license but if you are in this situation it's well worth it. The Demo version is pretty cool in that it returns all the data with the primary keys but after the first few records puts DEMO in all the non-PK columns. With the demo you can at least see how much you are going to get back before you buy it.



So if your DBCC doesn't work, and your database is still corrupted, check it out:



http://officerecovery.com/mssql/index.htm



G. Milner
Paul Randal
Paul Randal
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7810 Visits: 1719
Very interesting - and who do you work for? Looks a bit like advertizing to me.



I've seen many of these come and go while in the SQL team - rarely can they salvage as much as you can get doing it yourself.



For a free solution, checkout emergency mode repair that we included in SS2005 - blog posts about it on my blog and my old MSDN Storage Engine blog. This will always salvage more data than one of these tools can.



Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Greg Milner
Greg Milner
SSC-Addicted
SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)

Group: General Forum Members
Points: 495 Visits: 494
Hi, Paul.



No, it's not an ad.



I am a DBA for company in downtown Portland that makes software for the banking industry. I just thought this was a good product.



The problem we had was with a SQL 2000 database, so there were no 2005 tools we could use with it.



One caveat would be that I would not recommend Recovery for SQL Server for a really huge database unless you have a lot of time on your hands.



Still, it did get as much data out of the corrupted database as was possible and was better than any other options.



Cheers.



Greg



G. Milner
Paul Randal
Paul Randal
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7810 Visits: 1719
ok - cool. No offense - just that I've seen a bunch of posts on various forums who are advertizing their product without giving any extra advice. And yes, on 2000 you've got no choice but to use one of these tools or extract out the data manually.



Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search