databases.
Based on what Books Online recommends, I have ran a dbcc checktable, and
checkdb for that matter...no errors. I also verified the table(s) to exist i
n
the database and are also in the sysobjects. Does anyone have any other idea
s
as to why this job is failing and how I fix it?
Thank youCan you post the error output, exact DBCC command, and output from
sysobjects?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Josephine" <Josephine@.discussions.microsoft.com> wrote in message
news:3582C3AB-FE6A-45C5-9536-918962706A50@.microsoft.com...
> I run dbcc reindex at night and received error 2501 on several of the
> databases.
> Based on what Books Online recommends, I have ran a dbcc checktable, and
> checkdb for that matter...no errors. I also verified the table(s) to exist
in
> the database and are also in the sysobjects. Does anyone have any other
ideas
> as to why this job is failing and how I fix it?
> Thank you|||Thank you Paul, below is the information I'm dealing with.
Josie.
DBCC Command
*Perform a 'USE <database name>' to select the database in which to run the
script.*/
-- Declare variables
USE Hopping
SET NOCOUNT ON
DECLARE @.tablename VARCHAR (128)
DECLARE @.execstr VARCHAR (255)
DECLARE @.objectid INT
DECLARE @.indexid INT
DECLARE @.frag DECIMAL
DECLARE @.maxfrag DECIMAL
-- Decide on the maximum fragmentation to allow
SELECT @.maxfrag = 30.0
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
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
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @.tablename
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
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
' + RTRIM(@.indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@.frag)) + '%'
SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
' + RTRIM(@.indexid) + ')'
EXEC (@.execstr)
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GO
From Job History
Executed as user: DOMAIN\username. Could not find a table or object named
'AssignGOP'. Check sysobjects. [SQLSTATE 42S02] (Error 2501). The step
failed.
From Sysobjects
AssignGOP 1860201677 U 6 3 1619001347 592 0 0 2004-02-21
11:25:49.007 0 592 0 U 1 115 0 2004-02-21 11:25:49.007 0 0 0 0 0 0 0
"Paul S Randal [MS]" wrote:
> Can you post the error output, exact DBCC command, and output from
> sysobjects?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Josephine" <Josephine@.discussions.microsoft.com> wrote in message
> news:3582C3AB-FE6A-45C5-9536-918962706A50@.microsoft.com...
> in
> ideas
>
>|||I recognize that code - I wrote that BOL example :-)
One thing to be aware of - you're not using doing a reindex using this
script, you're doing a defrag. They're different operations. You should read
the whitepaper
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
to see if you really need to be doing this.
It looks like a call to DBCC SHOWCONTIG is failing for some reason - I
suspect there may be something wrong with your system catalogs. Can you run
DBCC CHECKCATALOG and DBCC CHECKDB on the Hopping database? What happens if
you try a select from the AssignGOP table?
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Josephine" <Josephine@.discussions.microsoft.com> wrote in message
news:D896B2C3-2834-4C60-A7D5-5F9045E5F753@.microsoft.com...
> Thank you Paul, below is the information I'm dealing with.
> Josie.
> DBCC Command
> *Perform a 'USE <database name>' to select the database in which to run
the[vbcol=seagreen]
> script.*/
> -- Declare variables
> USE Hopping
> SET NOCOUNT ON
> DECLARE @.tablename VARCHAR (128)
> DECLARE @.execstr VARCHAR (255)
> DECLARE @.objectid INT
> DECLARE @.indexid INT
> DECLARE @.frag DECIMAL
> DECLARE @.maxfrag DECIMAL
> -- Decide on the maximum fragmentation to allow
> SELECT @.maxfrag = 30.0
> -- Declare cursor
> DECLARE tables CURSOR FOR
> SELECT TABLE_NAME
> 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
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> -- Do the showcontig of all indexes of the table
> INSERT INTO #fraglist
> EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
> FETCH NEXT
> FROM tables
> INTO @.tablename
> 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
> FROM #fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
> -- Open the cursor
> OPEN indexes
> -- loop through the indexes
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
> ' + RTRIM(@.indexid) + ') - fragmentation currently '
> + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
> SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
> ' + RTRIM(@.indexid) + ')'
> EXEC (@.execstr)
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag
> END
> -- Close and deallocate the cursor
> CLOSE indexes
> DEALLOCATE indexes
> -- Delete the temporary table
> DROP TABLE #fraglist
> GO
>
> From Job History
> Executed as user: DOMAIN\username. Could not find a table or object named
> 'AssignGOP'. Check sysobjects. [SQLSTATE 42S02] (Error 2501). The ste
p
> failed.
> From Sysobjects
> AssignGOP 1860201677 U 6 3 1619001347 592 0 0 2004-02-21
> 11:25:49.007 0 592 0 U 1 115 0 2004-02-21 11:25:49.007 0 0 0 0 0 0 0
>
> "Paul S Randal [MS]" wrote:
>
rights.[vbcol=seagreen]
and[vbcol=seagreen]
exist[vbcol=seagreen]
other[vbcol=seagreen]|||Ha,ha! Yes, the BOL examples have helped me a bunch!
To answer you questions, I'm able to select the data in the AssignGOP table
just fine. It all looks accurate. I did a DBCC checkcatalog and checkdb and
there are no errors.
I do know there are no indexes on this table to defrag, so could this be
causing my issue?
I read through that article awhile back. If I recall, to reindex, you have
to be in single-user mode, which we can't do so in our enviroment, we've
chosen defragging indexes over reindexing. I'm sorry for the confusion in my
wording.
"Paul S Randal [MS]" wrote:
> I recognize that code - I wrote that BOL example :-)
> One thing to be aware of - you're not using doing a reindex using this
> script, you're doing a defrag. They're different operations. You should re
ad
> the whitepaper
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx[/ur
l]
> to see if you really need to be doing this.
> It looks like a call to DBCC SHOWCONTIG is failing for some reason - I
> suspect there may be something wrong with your system catalogs. Can you ru
n
> DBCC CHECKCATALOG and DBCC CHECKDB on the Hopping database? What happens i
f
> you try a select from the AssignGOP table?
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Josephine" <Josephine@.discussions.microsoft.com> wrote in message
> news:D896B2C3-2834-4C60-A7D5-5F9045E5F753@.microsoft.com...
> the
> rights.
> and
> exist
> other
>
>|||No need to apologize for being unclear - I'm happy to help.
You don't have to be in single-user mode to reindex. I think you're getting
confused with the concurrency possible while rebuilding an index. When
rebuilding a non-clustered index, the table is read-only and when rebuilding
a clustered index, the table is not even readable.
The lack of indexes does mean it won't be defragged but the cursor to select
indexes should skip the heap:
- the IndexDepth property for a heap is always zero.
Can you post the results from the following query? I believe you that you
have no indexes but I'd like to see what data is there for the heap.
select * from sysindexes where id=object_id('AssignGOP')
Thanks.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Josephine" <Josephine@.discussions.microsoft.com> wrote in message
news:30B5C3B8-58E2-4CF4-B9D6-A4D82A01C9F7@.microsoft.com...[vbcol=seagreen]
> Ha,ha! Yes, the BOL examples have helped me a bunch!
> To answer you questions, I'm able to select the data in the AssignGOP
table
> just fine. It all looks accurate. I did a DBCC checkcatalog and checkdb
and
> there are no errors.
> I do know there are no indexes on this table to defrag, so could this be
> causing my issue?
> I read through that article awhile back. If I recall, to reindex, you have
> to be in single-user mode, which we can't do so in our enviroment, we've
> chosen defragging indexes over reindexing. I'm sorry for the confusion in
my[vbcol=seagreen]
> wording.
> "Paul S Randal [MS]" wrote:
>
read[vbcol=seagreen]
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx[vbcol=seagreen]
run[vbcol=seagreen]
if[vbcol=seagreen]
rights.[vbcol=seagreen]
run[vbcol=seagreen]
named[vbcol=seagreen]
step[vbcol=seagreen]
the[vbcol=seagreen]
checktable,[vbcol=seagreen]
to[vbcol=seagreen]
No comments:
Post a Comment