February 6, 2013 at 8:16 pm
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.
Regards,
SQLisAwe5oMe.
February 7, 2013 at 1:29 am
Something has a lock on the information your after.
Monitor for blocking while your expanding and see what is stopping you.
February 7, 2013 at 1:50 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2013 at 5:12 am
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.
Regards,
SQLisAwe5oMe.
February 7, 2013 at 5:15 am
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.
February 7, 2013 at 5:20 am
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.
Regards,
SQLisAwe5oMe.
February 7, 2013 at 6:12 am
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.
Regards,
SQLisAwe5oMe.
February 7, 2013 at 6:20 am
Yes as it has to replay the rebuild on the mirrored node.
February 7, 2013 at 8:13 am
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.
February 7, 2013 at 8:22 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2013 at 9:19 pm
Just to update you guys, here is what I found.
Whatever was blocking last night went away, I was able to access the database today. I found the log grew to 200gb in size, it was due to log backup being failed. So, I manually ran log backup and shrank and reclaimed the log space.
So, the weekly re-index job also failed due to not having enough log space, so, it should work fine now.
Thanks for all of your inputs.
SueTons.
Regards,
SQLisAwe5oMe.
February 10, 2013 at 9:50 am
Hi,
I change the Database Recovery Model to [Bulk Logged Mode] before I run by REBUILD INDEX job . After the RE-Index is done the database is put back to [Full Mode]
Changing the database Model to Bulk Logged Mode keep the logging to minimum. Otherwise there is a chance of running out of disk. Depends upon how much disc space you have left on your drive where the mdf files are.
February 10, 2013 at 7:32 pm
sdpages (2/10/2013)
Hi,I change the Database Recovery Model to [Bulk Logged Mode] before I run by REBUILD INDEX job . After the RE-Index is done the database is put back to [Full Mode]
Changing the database Model to Bulk Logged Mode keep the logging to minimum. Otherwise there is a chance of running out of disk. Depends upon how much disc space you have left on your drive where the mdf files are.
Sdpages, I understand your point however, I cannot put the database into bulk recov mode due to mirroring in place. Mirroring only works in full recov mode.
Thanks for your input though.
SueTons.
Regards,
SQLisAwe5oMe.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply