To Re-index all indexes for a table.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » To Re-index all indexes for a table.

13 posts, Page 1 of 2

1 2 »»

To Re-index all indexes for a table.

Rate Topic

Display Mode

Topic Options

Author

Message

SQLCrazyCertified

Posted Wednesday, February 06, 2013 8:16 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 312, Visits: 1,868

Hi All,

I have to do re-index of all the indexes on a particular table, however, when I go to database properties or when I drop down under database/table..it's giving me an error like the following:

"Cannot show requested dialog.
Addidional information:
Cannot show requested dialog. (SqlMgmt)
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.Sqlserver.ConnectionInfo)
Lock request time out period exceeded. (Microsoft SQL server, Error: 1222)"

This database is mirrored as well. Please advise if you have any idea. Also, what are some of the things I need to check before doing the re-indexing. If anyone have any good script, please provide.

Thanks,
SueTons.

Post #1416790

anthony.green

Posted Thursday, February 07, 2013 1:29 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Something has a lock on the information your after.

Monitor for blocking while your expanding and see what is stopping you.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1416862

GilaMonster

Posted Thursday, February 07, 2013 1:50 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:16 PM
Points: 37,739, Visits: 30,015

Ignore the GUI, it's half-broken.

Query sys.dm_db_index_physical_stats for that table, that'll give you all the info the gui would.
To rebuild - ALTER INDEX ALL ON <table> REBUILD

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1416882

SQLCrazyCertified

Posted Thursday, February 07, 2013 5:12 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 312, Visits: 1,868

GilaMonster (2/7/2013)

Ignore the GUI, it's half-broken.

Query sys.dm_db_index_physical_stats for that table, that'll give you all the info the gui would.
To rebuild - ALTER INDEX ALL ON <table> REBUILD

Thanks guys,

Is rebuilding index done during off business hours? Or is it ok to do normal
Business hours?

SueTons.

Post #1416981

anthony.green

Posted Thursday, February 07, 2013 5:15 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Depends, if you have enterprise edition you can do you rebuilds online, providing that the index columns are of a compatable type, but usually do it out of hours.

Post #1416985

SQLCrazyCertified

Posted Thursday, February 07, 2013 5:20 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 312, Visits: 1,868

anthony.green (2/7/2013)

Depends, if you have enterprise edition you can do you rebuilds online, providing that the index columns are of a compatable type, but usually do it out of hours.

Ok....thank you.

SueTons.

Post #1416989

SQLCrazyCertified

Posted Thursday, February 07, 2013 6:12 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 312, Visits: 1,868

anthony.green (2/7/2013)

Depends, if you have enterprise edition you can do you rebuilds online, providing that the index columns are of a compatable type, but usually do it out of hours.

Another question, this db is mirrored, so, by doing re-indexing, does it affect mirroring anyway, since It's a fully logged operation?...please advise.

Thanks,
SueTons.

Post #1417028

anthony.green

Posted Thursday, February 07, 2013 6:20 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Yes as it has to replay the rebuild on the mirrored node.

http://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/efficient-index-maintenance-database-mirroring-140758

Post #1417033

catchmekiran

Posted Thursday, February 07, 2013 8:13 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, April 21, 2013 6:19 PM
Points: 18, Visits: 77

1. connect to the MASTER database of the server with the offending database
2. run the query below to find what transactions are open
3. kill any transaction that is blocking the SYSTEM SPIDS (1 to 50)

select * from master..sysprocesses where blocked <> 0
go
sp_who2 go
-- find the blocked SPIDS, then check for open trans below, any value other than 0 means an open tran
SELECT distinct(open_tran) FROM master..SYSPROCESSES WHERE SPID=<suspect SPID>
-- now kill it!
kill <suspect SPID>

The database will go back to normal as soon as the kill ends. No service to restart, no boot required.

Post #1417099

GilaMonster

Posted Thursday, February 07, 2013 8:22 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:16 PM
Points: 37,739, Visits: 30,015

Err... even ignoring that the connection that SSMS uses to display the dialog won't be a system thread and that the spid < 50 mean system is no longer true in SQL 2008, that's not a safe thing to do. What if those connections were running critical reports, or time-sensitive operations, or code that doesn't rollback properly?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1417109

« Prev Topic | Next Topic »

13 posts, Page 1 of 2

1 2 »»

Permissions