Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to put database in single user mode while dbcc CheckDB? Expand / Collapse
Author
Message
Posted Tuesday, October 02, 2007 6:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 02, 2007 6:07 AM
Points: 2, 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
Post #405557
Posted Tuesday, October 02, 2007 7:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 4:45 PM
Points: 1,157, Visits: 3,247
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

Post #405613
Posted Tuesday, October 09, 2007 12:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 1:27 AM
Points: 2,366, Visits: 1,837
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"
Post #408307
Posted Tuesday, October 09, 2007 8:41 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:09 PM
Points: 2,035, Visits: 1,655
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
Post #408794
Posted Tuesday, October 09, 2007 11:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 9:00 PM
Points: 114, Visits: 447
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
Post #408826
Posted Tuesday, October 09, 2007 11:31 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:09 PM
Points: 2,035, Visits: 1,655
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
Post #408829
Posted Wednesday, October 10, 2007 12:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 9:00 PM
Points: 114, Visits: 447
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
Post #408845
Posted Wednesday, October 10, 2007 1:57 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:09 PM
Points: 2,035, Visits: 1,655
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
Post #409239
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse