HOW TO: SharePoint Services DBCHECK Success!

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

General SQL Server Forums

New to SQL Server Administration

HOW TO: SharePoint Services DBCHECK Success!

Author

Topic

DAdmin
Starting Member

2 Posts

Posted - 2011-11-17 : 12:53:21

Recently our SharePoint Services server, our Intranet web site, was producing an error message. One of the content databases was in need of a CHECKDB. I had several challenges in resolving this issue.
1) Connection to the database requires a Named Pipe connection
2) The database is locked or in use by another connection
3) The database was now locked in Single User Mode

The following steps resolved all the issues and the SharePoint Server is now happy again. It took several iterations to come to this solution so I just thought I’d share my results. I’m only a network administrator with no budget so I had to resolve this on the few resources I had: time, Internet searches, forums like this, and a lot of trial and error.

My hope is you will find peace in your network.

Environment:
Windows Server 2003
SharePoint services
SQL 2005 Express
DB: MSSQL$MICROSOFT##SSEE
DB Name: WSS_Content_118e5acc9f9d487cafcd24a9780049ba

Windows Event log message:
Computer: [ESM-APP]
Monitor: [Event Log Monitor]
Description:
* Event Time: 14 Sep 2011 12:33:03 AM
* Source: MSSQL$MICROSOFT##SSEE
* Event Log: Application
* Type: Error Event
* Event ID: 824
* SQL Server detected a logical consistency-based I/O error: incorrect
checksum (expected: 0x44a75f1c; actual: 0x2411382a). It occurred during a read
of page (1:28776) in database ID 21 at offset 0x0000000e0d0000 in file
'C:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\MSSQL\DATA\WSS_Content_118e5acc9f9d487cafcd
24a9780049ba.mdf'. Additional messages in the SQL Server error log or system
event log may provide more detail. This is a severe error condition that
threatens database integrity and must be corrected immediately. Complete a
full database consistency check (DBCC CHECKDB). This error can be caused by
many factors; for more information, see SQL Server Books Online.

1. Connection to the database requires a Named Pipe connection

Using Microsoft SQL Server Management Studio Express connect to Server with:

Server name: \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
Windows Authentication (logged on as Administrator)

2. The database is locked or in use by another connection

Using Microsoft SQL Server Management Studio Express I ran the following query

Summary of query - Set the DB in single user mode, RUN CHECKDB, run SHRINKDB

---
SQL query
---

go
ALTER DATABASE ‘WSS_Content_118e5acc9f9d487cafcd24a9780049ba’ SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
go
DBCC checkdb (‘WSS_Content_118e5acc9f9d487cafcd24a9780049ba’, repair_allow_data_loss);
DBCC SHRINKDATABASE (\ ‘WSS_Content_118e5acc9f9d487cafcd24a9780049ba’);
Go

3. The database was now locked in Single User Mode

Summary of query - Find the SPID that is running against the database, kill the SPID, Alter the Database, restart the SQL Service using SERVICES.msc. This is a three step process. I’m sure there are great SQL DB Admins that could have done this whole thing in a single query however these steps resolved my issues nicely.

---
SQL query-Find the SPID
---
go
use master
select * from sysprocesses where dbid = db_id(‘WSS_Content_118e5acc9f9d487cafcd24a9780049ba’)
GO

---
SQL query-Kill the SPID
---
go
use master
select * from sysprocesses where dbid = db_id(‘WSS_Content_118e5acc9f9d487cafcd24a9780049ba’)
kill 58
select * from sysprocesses where dbid = db_id(‘WSS_Content_118e5acc9f9d487cafcd24a9780049ba’)
GO

---
SQL query-Alter the database back to multi user
---
go
use master
select * from sysprocesses where dbid = db_id(‘WSS_Content_118e5acc9f9d487cafcd24a9780049ba’)
alter database WSS_Content_118e5acc9f9d487cafcd24a9780049ba set multi_user with rollback immediate;
GO

Share your knowledge, express your thoughts, enjoy life and have a great day,
DAdmin

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-11-17 : 13:07:51

Too late now, but wish you had posted the results of the DBCC CHECKDB here before you had it delete data.

Hopefully you just lost a non-clustered index.

Allow_Data_Loss is a last resort. Often times the corruption can be repaired.

Now you'll need to investigate the cause of the corruption -- most likely a disk subsystem issue.

Are you taking regular backups? If you knew what you just dropped with the allow_data_loss, then you could restore a backup to a new database and copy it back.

If you still want to figure it out, restore the latest backup (to a new db) and query sys.objects and sys.indexes to see what exists in the backup that no longer exists in the live copy.

It's also possible that you just deleted documents that were uploiaded to sharepoint.

In the future, show us the output from your dbcc checkdb before proceding.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-11-17 : 13:11:28

Oh, and as far as accessing the database...

You should have shut down IIS. Then if needed set single_user and then back to multi_user. Then run your checkdb.

Why is named pipes required? I connect to my Sharepoint DBs via SSMS by specifying server name, as it's a default instance. Even in a named instance, you should be able to use servername\instanceName

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-17 : 13:21:55

Repairing a Sharepoint DB is not at all recommended. Because of the way sharepoint uses the DB, a small amount of data loss can render a site useless. For sharepoint, restore from backup is the only safe way of fixing corruption,

--
Gail Shaw
SQL Server MVP

DAdmin
Starting Member

2 Posts

Posted - 2011-11-18 : 13:50:44

Wow! I'm really out of my element with SQL/SharePoint. I’m very pleased you took the time to review my post. I had no idea the implications of my actions. The site seems to be okay. I did some manual file backups before editing the site like this for the important items. The stockholders use the site for file sharing business documents. I could have really shot myself in the foot here.

I want you all to know that your site is really grand. I tried my best to study the topics before starting and your site was the most help. Thanks for all you do here. I’m going to be a regular fan.

DAdmin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources