SQL 2005 - lLter Index hangs

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

frankm - 15 Jul 2008 16:09 GMT

I have a table with just 75,000 rows that has 1 column as the PK. When I
attempted to run the rebuild command (either through a maintenance plan or
directly running the sql, the process would run many hours (6-8) and
sometimes it did not end at all. This locks the table so that it cannot be
accessed. Code below.

ALTER INDEX [Container_PK] ON [Container] REBUILD WITH ( PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
SORT_IN_TEMPDB = ON, ONLINE = OFF )

No one else is using the db.
The process viewer just shows "SUSPENDED" with tbl locks and ever increasing
waittimes.
Any ideas, I'm stumped. There is more than enough room for tempdb, tempdb
doesn't even grow in this period.
My solution was to drop and recreate the table, this worked. But it doesn't
explain what happened and why.
Checkdb showed nothing.

Version 2005 SP2+Q934459 9.00.3159

Thanks in advance

frankm

Reply to this Message

TheSQLGuru - 15 Jul 2008 16:14 GMT

1) what is the average row size?

2) does the table contain a lot of blob data?

Signature

Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

>I have a table with just 75,000 rows that has 1 column as the PK. When I
>attempted to run the rebuild command (either through a maintenance plan or
[quoted text clipped - 20 lines]
>
> frankm

Reply to this Message

frankm - 15 Jul 2008 16:25 GMT

1) ~900 bytes
2) no blobs (text, image etc)

> 1) what is the average row size?
>
[quoted text clipped - 24 lines]
>>
>> frankm

Reply to this Message

Charles Wang [MSFT] - 16 Jul 2008 07:01 GMT

Hi Frankm,
Altering index with ONLINE=OFF will hold a table lock on the table. I
recommend that you run "SELECT * FROM sys.dm_tran_locks" to check if there
are any locks that may cause a dead lock. Also I recommend that you check
if your database recovery mode is FULL in which case the INDEX operation
will be fully logged, this will also impact your performance. You may
change the database recovery mode to SIMPLE or BULK_LOGGED to see if it
helps. You can check your database recovery mode by running:
SELECT DATABASEPROPERTYEX('database name','Recovery');

To change the recovery mode, you can run:
ALTER DATABASE database_name SET RECOVERY SIMPLE;
GO

You may also refer to this KB article to monitor blocking in SQL Server
2005:
How to monitor blocking in SQL Server 2005 and in SQL Server 2000
http://support.microsoft.com/kb/271509/en-us

If you have any other questions or concerns, please feel free to let me
know. Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

=========================================================

Reply to this Message