stanl
08-13-2008, 07:59 AM
This is a continuation from the last thread about DBCC.....
Assuming I loop through a series of tables and call a function
oCmd = CreateObject("ADODB.Command")
oCmd.ActiveConnection=oConn
oCmd.CommandType = adCmdUnknown
oCmd.CommandTimeOut = 6000
oCmd.CommandText = "ALTER INDEX ALL ON " & cTable & " REBUILD WITH (ONLINE=ON)"
oCmd.Execute
....
While oCmd.State>=4
doevents
Wend
with a few seconds delay after that between each table. I tested this on 5 tables with a log file that records the time each table was used prior to oCmd.Execute
The time between table1 and table2 was 7 minutes
table2 - table3 2 minutes
table3 - table 4 2 seconds
table4 - table5 2 seconds
I know table3 - table4 take 4-6 minutes to reindex if done separately. So the question is "Does SQL Server run each process as a separate thread after a while so the command state returns to 0?" or "Does the command object just fail to execute?" [I find nothing in the errors collection].
Better yet, is there a way to determine when a reindex in done?:dunno
Stan
Assuming I loop through a series of tables and call a function
oCmd = CreateObject("ADODB.Command")
oCmd.ActiveConnection=oConn
oCmd.CommandType = adCmdUnknown
oCmd.CommandTimeOut = 6000
oCmd.CommandText = "ALTER INDEX ALL ON " & cTable & " REBUILD WITH (ONLINE=ON)"
oCmd.Execute
....
While oCmd.State>=4
doevents
Wend
with a few seconds delay after that between each table. I tested this on 5 tables with a log file that records the time each table was used prior to oCmd.Execute
The time between table1 and table2 was 7 minutes
table2 - table3 2 minutes
table3 - table 4 2 seconds
table4 - table5 2 seconds
I know table3 - table4 take 4-6 minutes to reindex if done separately. So the question is "Does SQL Server run each process as a separate thread after a while so the command state returns to 0?" or "Does the command object just fail to execute?" [I find nothing in the errors collection].
Better yet, is there a way to determine when a reindex in done?:dunno
Stan