March 13, 2007 at 8:47 am
my db maintenance process is the following. i have a db on each server called index stats with two tables. one table to hold the fragmentation data from the system view that is run nightly and another as a log for the maintenance commands.
there are three scripts that run a few times each week. a 100% offline maintenance script, an online alter index script and an update statistics script. Having a problem with the last one. It keeps getting stuck on a different table every time and scans it over and over.
here is my script
Declare @table_name nvarchar(4000);
DECLARE @command nvarchar(4000);
/* get distinct list of tables in SCS database*/
SELECT distinct table_name
INTO index_stats..work_to_do_scs_stats
FROM index_stats..physical_stats where
database_id = 7 and object_id != 921119118
-- Declare the cursor for the list of partitions to be processed.
DECLARE stats CURSOR FOR SELECT * FROM index_stats..work_to_do_scs_stats;
-- Open the cursor.
OPEN stats;
-- Loop through the partitions.
WHILE (@@fetch_status <>1)
BEGIN;
FETCH NEXT
FROM stats
INTO @table_name
-- 10 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
SET @command = 'update statistics ' + @table_name;
insert index_stats..command (command, date) values (@command, getdate());
EXEC (@command);
PRINT N'Executed: ' + @command;
END;
FETCH NEXT FROM stats INTO @table_name;
-- Close and deallocate the cursor.
CLOSE stats;
DEALLOCATE stats;
-- Drop the temporary table.
drop table index_stats..work_to_do_scs_stats;
March 13, 2007 at 9:25 am
I use the following when I use cursors, see if that helps.
FETCH
NEXT FROM test_cursor INTO @name
WHILE
(@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- code
END
FETCH NEXT FROM test_cursor INTO @name
END
March 13, 2007 at 10:58 am
March 13, 2007 at 11:08 am
it was with the cursor
db has around 300 tables and most go through OK, but it gets stuck on a different one all the time
March 13, 2007 at 11:11 am
SQL Noob,
TRY this one , i have ammended your code a BIT. i have removed the temporary TABLE you were USING because it does NOT bring ANY benefits WITH it since the ultimate aim is to put the table names into the cursor
----
-- Declare the variables to store the values returned by FETCH.
Declare @table_name nvarchar(4000);
DECLARE @command nvarchar(4000);
DECLARE statis CURSOR
FOR SELECT table_name
FROM index_stats..physical_stats where
database_id = 7 and object_id != 921119118
OPEN statis
-- Perform the first fetch and store the values in variables.
FETCH NEXT FROM statis
INTO @table_name
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- 10 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
SET @command = 'update statistics ' + @table_name;
insert index_stats..command (command, date) values (@command, getdate());
EXEC (@command);
PRINT N'Executed: ' + @command;
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM statis
INTO @table_name
END
CLOSE statis
DEALLOCATE statis
GO
----
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply