question

askmlx121 avatar image
askmlx121 asked

Rebuild index of one Table took Timeout why?????

Hi One Database name Sales have salesorder table have Rows 2514 index size 968KB Recovery model:Full My problem is when i run the following rebuild index it went to time out. **alter index PK_salesorderid_cl on Sales.dbo.salesorder rebuild** Any idea???????????????????????????????????????????????? Note: DBCC OPENTRAN Oldest active transaction: SPID (server process ID): 120 UID (user ID) : -1 Name : INSERT LSN : (63532:1249:2) Start time : Apr 3 2013 2:26:18:943AM SID : 0x352fddc18de9664cbbaf6c9812eecad6 DBCC execution completed. If DBCC printed error messages, contact your system administrator.
sql-server-2005indextransactionrebuild
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ramesh 1 avatar image
ramesh 1 answered
if you are using sql server 2005 and above and Enterprise edtion , you can use online reindexing to resolve the issue. what is the query timeout set in your database did you run dbcc checktable command to check the how good and healthy is your table.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi denisrichi,ramesh We are using SQL 2005. query time out details: set lock timeout:-1 execution timeout:0 now i check the dbcc checktable and inform u
0 Likes 0 ·
Hi Ramesh I checked results for your ref below: DBCC CHECKTABLE ("dbo.salesorder "); DBCC results for 'salesorder '. There are 2514 rows in 195 pages for object "salesorder ". DBCC execution completed. If DBCC printed error messages, contact your system administrator.
0 Likes 0 ·
denisrichi avatar image
denisrichi answered
May be this is an error on a blocking issue. The query was blocked by something else and timed out. Try increasing your query timeout and see if this helps. Also try to rebuild the index online as said by ramesh
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tomgough79 avatar image
tomgough79 answered
To echo what previous users have said, if you don't have Enterprise edition, rebuilding an index is an offline operation Do you have an out of hours maintenance window you could use to carry out the work?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ramesh 1 avatar image
ramesh 1 answered
select * from sys.sysprocesses where bocked0 is this query returns any rows then check the issue for blocking
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.