May 28, 2005 at 10:44 pm
We have Windows 2003, SQL Server 2000 Standard sp3. Our Company runs Trapeze which is a real time scheduling software.
Our Maintenence plan runs every night Integrity 1am Backup 1:45am Optimize 2:30am on one particular database.
Every once in awhile ill get a call at 4:30am that the early crew is not able to log in Trapeze. Ill connect to the SQL Server and notice that the Optimize job still shows its executing (normally it takes 15 min) Job history does not show fail or sucess, it simply freezes up. Have to reboot server to get the system back up and running.
One thing we did notice was the memory settings were set to the 2nd choice of use 3g memory and not dynamically set for range. Heard that might cause problems. So we set to dynamically memory range of 722 to 3g. Worked for a few weeks til it locked up again yesterday morn. Perhaps there is something else that we need to check to solve this problem.
Does the sequence of the plan matter? should we do optimize/integrity then backup?
Or should we load up sp4? figure its ok we dont use awe, cause we are 2000 standard edition.
Perhaps there is another option to optimize a databse instead of using the SQL version of the plan?
Thnx
May 30, 2005 at 5:23 am
Hi Dan, when you say Optimization freezes. Was there any blocking, ie run sp_who2. Also if your optimization is run from a wizard, perhaps you could create a separate job to optimize each table individually, ie DBCC REINDEX (table). Also if your job creates a log, perhaps it may point to the table it was optimizing at the time of freeze. My guess is some other job is causing blocking and optimization will sit there until it gets an exclusive lock on the table it is processing at that time. Also consider running a profiler for the duration of expected runtime of the optimization. Hope this is of use. Rgds Derek
May 30, 2005 at 7:13 am
Hi Derek,
No other jobs are running at the same time. Maintenence plan2 for master and the others are ran every 2 weeks on a Sunday night. We are basically a 1 database user.
Ill check on the logs and see what the last table was, before it just hoses up and Ill also run the sp_who2 command.
This is a 6gig database with hundreds of table, so im not sure if it best to individually reindex each table seperate.
This week ill look into the sp4 patch and create seperate jobs.
Thanks,
Dan
May 30, 2005 at 8:37 am
Hi Dan, creating a step for each table would be a nightmare for you having so many. My idea behind sp_who2 (when you log on at time of freeze) and profiler is to try and see what activity might be causing the issue. Below is some SQL that may be off use for you. It will scan sysobjects and reindex user tables prefixed with tbl_. You could modify this. In the advanced tab of the job step you could output the jobs results etc etc.
Best of luck with it.
Rgds, Derek
USE livedata
GO
DECLARE @spname varchar(255)
DECLARE @command_string varchar (255)
DECLARE spnames_cursor CURSOR FOR SELECT name FROM sysobjects (nolock)
WHERE type = 'U' and name like 'tbl_%'
OPEN spnames_cursor
FETCH NEXT FROM spnames_cursor INTO @spname
WHILE (@@fetch_status = 0)
BEGIN
SET @command_string = 'dbcc dbreindex (' + RTRIM(UPPER(@spname)) +')'
EXEC (@command_string)
Print ' '
FETCH NEXT FROM spnames_cursor INTO @spname
END
CLOSE spnames_cursor
DEALLOCATE spnames_cursor
GO
May 31, 2005 at 9:03 am
We had a similar problem with a client running a Commerce Server Application. Every time the optimizations task ran, the web application would be unavailable because the web application was always hitting the database server and the optimizations task had tables locked as it was being executed. We resolved this by running DBCC INDEXDEFRAG instead of DBCC REINDEX since INDEXDEFRAG does not lock tables which was the cause of our original problem.
SQL BOL is a good reference. Check out DBCC INDEXDEFRAG and DBCC SHOWCONTIG. The script below uses both. I modified it from the example under DBCC SHOWCONTIG. Only indexes with a specified amount of fragmentation are defragged.
/*Perform a 'USE <database name>' to select the database in which to run the script.*/
--This example shows a simple way to defragment all indexes in a database that is fragmented above a declared threshold.
/*
Created From: This script was originally taken from SQL BOL under "DBCC SHOWCONTIG" and then modified.
Modifications <March 9, 2005>:
- added @ScanDensity and @indexname variables
- replaced apostrophes in @tablename and @indexname
- modified PRINT statements & commented them out for scheduled job
- changed indexdefrag to be against @tablename and @indexname rather than @objectid and @indexid -- I had received errors before the change
Modifications <March 10, 2005>:
- added table_schema (table owner) field to tables cursor and dbcc showcontig statement since not all tables are owned by dbo
Purpose: To defrag indexes; the built-in db maintenance plan optimizations task brought the web site down during its execution
*/
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL
DECLARE @scandensity decimal
declare @indexname varchar(255)
declare @tableowner varchar(50)
-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 30.0
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME, TABLE_SCHEMA
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
-- Open the cursor
OPEN tables
-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename, @tableowner
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
select @tablename = replace(@tablename,'''','''''')
--print 'DBCC SHOWCONTIG (''[' + @tablename + ']'') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS'
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tableowner + '.[' + @tablename + ']'') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename, @tableowner
END
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag, ScanDensity, IndexName
FROM #fraglist
WHERE (LogicalFrag >= @maxfrag or ScanDensity < 75)
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag, @ScanDensity, @IndexName
WHILE @@FETCH_STATUS = 0
BEGIN
select @tablename = replace(@tablename,'''','''''')
select @indexname = replace(@indexname,'''','''''')
-- print ' '
-- PRINT 'Executing DBCC INDEXDEFRAG (0, [' + RTRIM(@tablename) + '], [' + RTRIM(@indexname) + '])'
-- print' - logical fragmentation currently ' + RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, [' + RTRIM(@tablename) + '], [' + RTRIM(@indexname) + '])'
EXEC (@execstr)
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag, @ScanDensity, @IndexName
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GO
May 31, 2005 at 11:12 am
Thanks Derek and susqtech for your replies.
Took some time today to look at all the logs and i found something very interesting. Seems we get this error:
Microsoft SQL-DMO (ODBC SQLState: 01000)] Error 0: This server has been disconnected. You must reconnect to perform this operation.
During the optimize procedure and it happens on various tables at random times.
Also noticed in the master database, in table sysserver serverid of 0 (which i think is correct) but it also has svrname and datasource name not matching its true server name, but instead it had DB as a name. This could very well cause it to loose connection during the optimize plan. not 100% sure this is the cause, but have you seen or heard of this error happening before?
Thanks
Dan
May 31, 2005 at 1:18 pm
when you change server names, just run the installer again - easiest way to update all the system tables with the new name.
June 1, 2005 at 2:19 am
Dan, sorry didn't come across that issue before. But I will bear in mind for myself going forward. Anyway goes without saying don't forget, backup your system databases if you are doing any manual updates. Rgds Derek.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply