October 16, 2012 at 1:38 pm
I am new to SQL Server 2008 R2. However, I do have many years as a DBA, but for other engines.
I am running across an anomaly in running a stored procedure. It is called sp_defragment_indexes,
which was installed when I arrived here. This SP works on some servers, including production, just
fine, but is failing with an ususual name in the error message on test servers:
Cannot find a table or object with the name "\ingres.pm_shopop_tbl". Check the system catalog.
[SQLSTATE 42S02] (Error 2501). The step failed.
Notice the "\" in the name listed in the error message. The message is reasonable - there is no
such table in the database.
In trying to track down the issue, I performed these tests:
use [test]
go
dbcc showcontig ('dbo.iso_currency_tbl')
go
select * from sysobjects
wHERE type ='U'
and name = 'iso_currency_tbl'
go
dbcc result:
Msg 2501, Level 16, State 45, Line 1
Cannot find a table or object with the name "dbo.iso_currency_tbl". Check the system catalog.
Using the table name without the "dbo" returns the same message.
However, the select returns a record!
(1 row(s) affected)
iso_currency_tbl803063850U 5 etc.
This table is NOT in the tables list in the database!
In the procedure, there is this SQL:
SELECT convert(varchar,so.id)
FROM sysobjects so
JOIN sysindexes si
ON so.id = si.id
WHERE so.type ='U'
AND si.indid < 2
AND si.rows > 0
Taking this query apart:
select * from sysobjects where name = 'iso_currency_tbl'
Record found!
select * from sysindexes where id = 803063850
Two records found, indid = 1 and indid = 2.
Why does the sysobjects table show iso_currency_tbl, but the table list does not and dbcc showcongfig
does not find it either? Can sysobjects get out of sync with the real objects that exist?
Any idea why there is a "\" in the message from the SP?
October 16, 2012 at 1:44 pm
i think the table might be in another schema, but you are assuming it's in the dbo schema, since that's what is your default.
try this instead...what schema does it really belong to?
select
SCHEMA_NAME(SCHEMA_ID),
name,
'DBCC showcontig (''' + quotename(SCHEMA_NAME(SCHEMA_ID)) + '.' + quotename(name)+''')' As cmd,
* from sys.objects
wHERE type ='U'
and name = 'iso_currency_tbl'
Lowell
October 16, 2012 at 2:04 pm
Thanks. I did have the wrong owner (it is "Adage"). Running the showcontig with that dbo works.
That solves that mystery. However, the error remains :-(.
I also just noticed that I had a typo in my original post!
The error message I am getting from the stored procedure is:
Cannot find a table or object with the name "\ingres.iso_currency_tbl". Check the system catalog. [SQLSTATE 42S02] (Error 2501). The step failed.
The other message in the original post came from a different server. Notice, however, that there is a "\" in both messages, and only the table name in the messages changed.
October 16, 2012 at 2:12 pm
well, i think the issue's in the procedure you are talking about ...i don't think it's schema aware;
i wouldn't take the error message from the scheduled job literally(that a slash exists in some object name)
if you post the procedure, we can help make it schema-aware , which i'm sure will make the error go away.
Lowell
October 16, 2012 at 2:43 pm
You found the issue. The SP was using a call to user_name(so.uid) to set the object's schema name in the dbcc command, where the uid is the value from the sysobjects table. In this case, this call returned "\ingress"! Certainly not the real schema name. Good catch!
I did not write this SP - it was written a few years ago. It indeed needs to be made "schema" aware (properly!).
Here it is:
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @objectowner VARCHAR(255)
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @indexname CHAR(255)
DECLARE @dbname sysname
DECLARE @tableid INT
DECLARE @tableidchar VARCHAR(255)
--check this is being run in a user database
SELECT @dbname = db_name()
IF @dbname IN ('master', 'msdb', 'model', 'tempdb')
BEGIN
PRINT 'This procedure should not be run in system databases.'
RETURN
END
--begin Stage 1: checking fragmentation
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT convert(varchar,so.id)
FROM sysobjects so
JOIN sysindexes si
ON so.id = si.id
WHERE so.type ='U'
AND si.indid < 2
AND si.rows > 0
-- Create the temporary table to hold fragmentation information
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 running dbcc showcontig on each one
FETCH NEXT
FROM tables
INTO @tableidchar
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (' + @tableidchar + ') with TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tableidchar
END
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
-- Report the ouput of showcontig for results checking
SELECT * FROM #fraglist
-- Begin Stage 2: (defrag) declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, ExtentFrag
FROM #fraglist f
JOIN sysobjects so ON f.ObjectId=so.id
WHERE ScanDensity <= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Write to output start time for information purposes
SELECT 'Started defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())
-- Open the cursor
OPEN indexes
-- Loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectowner, @objectid, @indexname, @frag
WHILE @@FETCH_STATUS = 0
BEGIN
SET QUOTED_IDENTIFIER ON
SELECT @execstr = 'DBCC DBREINDEX (' + "'" +RTRIM(@objectowner) + '.' + RTRIM(@tablename) + "'" +
', ' + RTRIM(@indexname) + ') WITH NO_INFOMSGS'
SELECT 'Now executing: '
SELECT(@execstr)
EXEC (@execstr)
SET QUOTED_IDENTIFIER OFF
FETCH NEXT
FROM indexes
INTO @tablename, @objectowner, @objectid, @indexname, @frag
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Report on finish time for information purposes
SELECT 'Finished defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())
-- Delete the temporary table
DROP TABLE #fraglist
GO
October 16, 2012 at 2:50 pm
ok for me the fix is easy;
there's a much better, award winning, peer reviewed and rather famous script to help handle things like reindexing.
it's been deployed by more businesses, on bigger servers and databases than i will ever have the pleasure of working on.
replace your current job with one using this scripts instead.
(one of the best tools in my toolbox)
Lowell
October 17, 2012 at 8:44 am
Again, my thanks! I will download your suggested scripts and see about installing them.
In the meantime, I found a fix (work-around) to the sp_defragment_indexes script in another older posting:
"The biggest problem with using this procedure in SQL 2005 is that the table owner is not necessarily the same as the schema that the table belongs to. You can't use the sysobjects uid value like this anymore. You can replace "ObjectOwner = user_name(so.uid)" with "ObjectOwner = object_schema_name(id)" "
Making that change resolved the issue.
Thanks again!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply