SharePoint Database Consistency Errors
Hi All,
I have noticed that my sharepoint content database contains many database consistency errors. I have run following SQL command to verify these errors
ALTER DATABASE your_database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('your_database_name', REPAIR_REPAIR_FAST)
ALTER DATABASE your_database_name SET MULTI_USER
Now I want to remove them safely with any application error. Is there any method to remove these errors ?
For Information: I know following SQL commands will remove these consistency errors but with data loss. I want to know what type of data will be lost and whether it is safe to run below commanrds with
REPAIR_ALLOW_DATA_LOSS parameter
ALTER DATABASE your_database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('your_database_name', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE your_database_name SET MULTI_USER
Thanks and Regards..
August 23rd, 2010 8:50am
Hi,
If you have consistency errors in SQL for a SharePoint database, the best route really is to create another database and have it attached to the current web application (http://technet.microsoft.com/en-us/library/cc825314(office.12).aspx),
and then run the mergecontent db command (http://technet.microsoft.com/en-us/library/cc825328(office.12).aspx)
to move all the sites to the new database.
Or alternatively, you can run a SharePoint backup of the affected web application and then restore it to a new database.
If you are still intent on correct the current database, just be aware that SharePoint may behave strangely some time down the future.
Also make sure you still fall in line with the following: http://support.microsoft.com/kb/841057/en-us
Regards, Savoeurn Va Microsoft Online Community Support
Free Windows Admin Tool Kit Click here and download it now
August 31st, 2010 9:18pm
Hi Savoeurn Va,
Thanks for your help. I am getting following error message while splitting sharepoint content databases using stsadm mergecontentdb command.
" The sqltransaction has completed; it is no longer usable "
Any idea how to resolve this issue ?
September 21st, 2010 1:35pm
Hi
It looks like your databases are more inconisistent then reported. You will have to use the following method on the database that didn't get merged. Hopefully you have a backup before you tried the merge contentdb command.
- Run stsadm -o backup against all of your site collections
- Detach the databases from SharePoint via Central Admin > Application management > content databases (If your running SQL Express don't do this it'll delete the databases)
- In Central Admin > Application management > content databases, click on "Add content databases"
- Enter in information to your SQL server and specify a database that does not exist.
Sharepoint will create this database for you in SQL.
- Run stsadm -o restore for all of your sites that you backed up.
Doing this will put all your sites back into the newly created database.
Regards, Savoeurn Va Microsoft Online Community Support
Free Windows Admin Tool Kit Click here and download it now
September 23rd, 2010 6:51pm
Dear Savoeurn Va,
Thanks for your reply. It really helps me in solving my problem. I tested SQL Commands with Allow_Data_Loss parameter and it works without any problem in my test environment. I have executed same solution on my live environement. It resolves my problem
For your Information: I executed following commands
ALTER DATABASE your_database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('your_database_name', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE your_database_name SET MULTI_USER
Cheers...
September 24th, 2010 10:23am