Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

A have the following table in a SQL Server 2000 database (service pack 2), with about 600,000 rows:

Id [int, primary key]  
Form [int]  
Element [int]  
Data [char(8000)]  
MoreData [varchar(8000)]

(I should note: the "Data" column probably holds less than 30 characters for almost every row. No idea why char(8000) was chosen previously.)

A simple INSERT INTO query with a single row of data was timing out on my website, and attempting to perform the same query directly in SQL Server Management Studio was fruitless after over 20 minutes of waiting. I ran DBCC SHOWCONTIG and received the following:

DBCC SHOWCONTIG scanning '(Table Name)' table...  
Table: '(Table Name)' (926730454); index ID: 0, database ID: 8  
TABLE level scan performed.  
- Pages Scanned................................: 2555  
- Extents Scanned..............................: 328  
- Extent Switches..............................: 327  
- Avg. Pages per Extent........................: 7.8  
- Scan Density [Best Count:Actual Count].......: 97.56% [320:328]  
- Extent Scan Fragmentation ...................: 99.39%  
- Avg. Bytes Free per Page.....................: 349.8  
- Avg. Page Density (full).....................: 95.68%  
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Thinking fragmentation was the problem, I ran DBREINDEX on the table for over two hours with no luck. After searching further, I also executed sp_lock and found over a million EXT (extent) locks, the bulk being attributed to the SPID for the website (same credentials I used to connect to the database in Management Studio). Most of the remainder were by SPID 123, which didn't come up using sp_who.

Is that number of locks (a million, for a fairly low traffic website) strange? Could they be caused by my attempts to rebuild the index when I killed the Management Studio window two or three times (for not responding)? Or could this be a problem with the physical drive?


Since gathering the above information a few hours ago the number of locks has dropped to only 16, and the table is working normally again. If anyone can help I'd still like some insight into what caused the problem so that it can be prevented in the future.

share|improve this question
    
Sql Server 2000 SP2 was released in 2003. How about an upgrade or even an update? You are missing a lot fixes. –  vonPryz Jan 9 at 8:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.