April 9, 2003 at 10:31 pm
Hi all,
I am new to the group and am facing a problem. We have got a SQL 2000 TEST server (8.00.534) and have got several databases in it. Recently we got a database from production team and the performance of the database is very poor and decided to update the INDEX using a job.
At first I tried to run DBCC DBREINDEX on the databases. But that did not help much and was crashing much to often saying "A column has been specified more than once in the order by list. Columns in the order by list must be unique." . Later I used UPDATE STATISTICS. Now what the problem started is the SP fails and a sql server dump file is generated and shows the console message "fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process."
Any suggestion will be highly appreciated. I am attaching the sp that I ran below for u'r convinience.
------------------------------------------------------------------------------------------
/****** Object: Stored Procedure dbo.cnr_reorganize_all_indexes Script Date: 02/04/2003 08:75:00 PM******/
CREATE PROCEDURE cnr_reorganize_all_indexes @dbname varchar(30) = NULL
AS
set nocount on
/* See if the database exists */
if not exists (select * from sysdatabases where name like @dbname)
begin
raiserror(15010,-1,-1,@dbname)
return (1)
end
else
begin
DECLARE @tablename varchar(100)
declare @tableowner varchar(100)
DECLARE @tablename_header varchar(200)
EXEC ("DECLARE tnames_cursor CURSOR FOR SELECT distinct so.name, su.name FROM " +
@dbname + "." + @tableowner + ".sysobjects so, " +@dbname + "." + @tableowner + ".sysindexes si, " + @dbname + "." + @tableowner + ".sysusers su " +
"WHERE so.id=si.id and so.uid=su.uid and indid not in (0,255) and so.type='u' ORDER BY so.name")
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @tablename, @tableowner
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @tablename_header = "Reorganizing " + @tableowner + "." + RTRIM(@tablename)
--EXEC ( "DBCC DBREINDEX ('" + @dbname + "." + @tableowner + "." + @tablename + "', '', 0) with no_infomsgs" )
EXEC ( "UPDATE STATISTICS " + @dbname + "." + @tableowner + "." + @tablename +" WITH NORECOMPUTE" )
END
FETCH NEXT FROM tnames_cursor INTO @tablename, @tableowner
PRINT "Indexes have been reorganized for all tables."
DEALLOCATE tnames_cursor
end
PRINT "Turning Statistics ON"
EXEC ("ALTER DATABASE " + @dbname + " SET AUTO_UPDATE_STATISTICS OFF")
PRINT "Turned Statistics ON"
GO
------------------------------------------------------------------------------------------
Thanks
Anindya
April 10, 2003 at 4:48 am
Run DBCC CHECKDB to see if it reports anything unusual. But based on the message I would verify I don't have a corrupted index.
April 10, 2003 at 9:01 am
agree with Antares686 to DBCC checkdb/checktable as first step. If you have any corruption in a non-CI then you should be able to drop and recreate it.
I would bodge cursor code to generate just the TSQL (not execute it), and manually run in QA one statement at a time to see which blows up .
There is a difference between reindex [long job so schedule for weekend], and updatestats [quick so could do nightly].
Recommendation used to be to
auto-create stats ON
auto-update stats OFF (and DBA do manually)
but MS have enhanced to only do update stats when needed [batched, not every individual I/U/D statement], so guess this should suffice 99% of the time. But I always have weekend SQLAgent job for reindex.
Your script prints "Turning Statistics ON"
yet executes "SET AUTO_UPDATE_STATISTICS OFF" which sounds wrong.
Your code splits out owner and tblname (suggest you simplify to use tbl=su.name+'.['+so.name+']' in your cursor (and single @tbl elsewhere)
You also use distinct and indid not in (0,255). A simple indid < 2 should do instead.
You are using MSSQL-2000/SP2 (8.00.534) and _everybody_ should now be using SP3 (8.00.0760.03) that fixes Slammer worm virus. Who knows, may even fix your problem !
Dick
April 23, 2003 at 5:53 am
Thanks dick,
ya, i too find that, something is wrong in it. actually the DBCC CHECKDB did not reveal anything. but the databse somehow got corrupted. i found a large table has got corrupted, and i can not even run a single "SELECT" statement on it.
however, we have got another backup database(it was our dvelopment server) and we restorde all the data required from that.
Thanks mate
anindya
April 23, 2003 at 6:04 am
glad you managed to finger the bad table and get the db back together [from backup].
Don't forget the latest Service Pack !
Dick
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply