Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2008 Forums  Transact-SQL (2008)  Why does this query stop when it's not done?

Author  Topic  ED_KING Starting Member 7 Posts Posted - 2010-04-20 : 22:56:57 So i have a query that uses a cursor to do some re-indexing which runs once a month. The problem is it will say finished successfully but it ends in the midle of a job or right after a DB is complete even though its not done. This query goes through about 468 DB's and re-indexes everything... but each time it runs (even in the Management Studio) it stops at different DB each time. Sometimes it will finish most of the time after about an hour or two it will just stop saying "Query executed successfully"?? Query is below. Thanks! ------- DECLARE @db_name varchar(200) DECLARE ix_cursor CURSOR FOR select name from sys.databases where name like 'PP_%' order by name ASC OPEN ix_cursor FETCH NEXT FROM ix_cursor INTO @db_name WHILE (@@FETCH_STATUS =0) BEGIN print 'Working on... ' + @db_name print '' EXEC ap_RebuildIndexes @maxfrag=15.0, @maxdensity=75.0 , @databasename=@db_name FETCH NEXT FROM ix_cursor INTO @db_name END CLOSE ix_cursor DEALLOCATE ix_cursor EXEC ap_RebuildIndexes @maxfrag=15.0, @maxdensity=75.0 , @databasename='Beta1_System' EXEC ap_RebuildIndexes @maxfrag=15.0, @maxdensity=75.0 , @databasename='ED_SMI'