Post reply

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

  • October 2, 2007 at 6:07 am

    #179489

    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

  • October 2, 2007 at 7:32 am

    #738144

    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

  • October 9, 2007 at 12:24 am

    #740247

    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"

  • October 9, 2007 at 8:41 pm

    #740608

    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

  • October 9, 2007 at 11:06 pm

    #740636

    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

  • October 9, 2007 at 11:31 pm

    #740639

    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

  • October 10, 2007 at 12:43 am

    #740652

    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

  • October 10, 2007 at 1:57 pm

    #740982

    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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply