DBCC SHOWCONTIG

Cross-posted in: microsoft.public.sqlserver.programming &
microsoft.public.sqlserver.server
I have a procedure that loops through the databases and then their tables
and I then want to run DBCC SHOWCONTIG on each table. However, it seems that
DBCC SHOWCONTIG is insisting on only running in the current database, as
defined by the USE statement, or perhaps the database the procedure is being
run from , not sure as BOL is vague on this point.
I tried adding:
SET @.SQL = 'USE ' + @.DB
EXEC sp_executesql @.SQL
Which seems to execute fine.
I then execute:
EXEC ('DBCC SHOWCONTIG(''' + @.DB + '.' + @.Owner + '.' + @.Table + ''')')
I also tried to get and use the OBJECT_ID.
but I continue to get the same error:
Server: Msg 2506, Level 16, State 1, Line 1
Could not find a table or object name 'Data.dbo.sysobjects' in database
'master'.
I have a bad feeling that DBCC SHOWCONTIG can only be run in the current
database, hope I'm wrong.Jay,
> SET @.SQL = 'USE ' + @.DB
> EXEC sp_executesql @.SQL
But it does not mean you are now in @.DB. Current context has not changed.
use northwind
go
exec sp_executesql N'use pubs'
go
select db_name()
go
You have to add the USE statement as part of the batch.
exec sp_executesql N'use AdventureWorks; dbcc showcontig (''dbo.ErrorLog'',
1)'
go
AMB
"Jay" wrote:
> Cross-posted in: microsoft.public.sqlserver.programming &
> microsoft.public.sqlserver.server
> I have a procedure that loops through the databases and then their tables
> and I then want to run DBCC SHOWCONTIG on each table. However, it seems that
> DBCC SHOWCONTIG is insisting on only running in the current database, as
> defined by the USE statement, or perhaps the database the procedure is being
> run from , not sure as BOL is vague on this point.
> I tried adding:
> SET @.SQL = 'USE ' + @.DB
> EXEC sp_executesql @.SQL
> Which seems to execute fine.
> I then execute:
> EXEC ('DBCC SHOWCONTIG(''' + @.DB + '.' + @.Owner + '.' + @.Table + ''')')
> I also tried to get and use the OBJECT_ID.
> but I continue to get the same error:
> Server: Msg 2506, Level 16, State 1, Line 1
> Could not find a table or object name 'Data.dbo.sysobjects' in database
> 'master'.
> I have a bad feeling that DBCC SHOWCONTIG can only be run in the current
> database, hope I'm wrong.
>
>|||Works great, thanks
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3498EDC5-3762-4881-88AF-1E387056AE95@.microsoft.com...
> Jay,
>> SET @.SQL = 'USE ' + @.DB
>> EXEC sp_executesql @.SQL
> But it does not mean you are now in @.DB. Current context has not changed.
> use northwind
> go
> exec sp_executesql N'use pubs'
> go
> select db_name()
> go
> You have to add the USE statement as part of the batch.
> exec sp_executesql N'use AdventureWorks; dbcc showcontig
> (''dbo.ErrorLog'',
> 1)'
> go
>
> AMB
> "Jay" wrote:
>> Cross-posted in: microsoft.public.sqlserver.programming &
>> microsoft.public.sqlserver.server
>> I have a procedure that loops through the databases and then their tables
>> and I then want to run DBCC SHOWCONTIG on each table. However, it seems
>> that
>> DBCC SHOWCONTIG is insisting on only running in the current database, as
>> defined by the USE statement, or perhaps the database the procedure is
>> being
>> run from , not sure as BOL is vague on this point.
>> I tried adding:
>> SET @.SQL = 'USE ' + @.DB
>> EXEC sp_executesql @.SQL
>> Which seems to execute fine.
>> I then execute:
>> EXEC ('DBCC SHOWCONTIG(''' + @.DB + '.' + @.Owner + '.' + @.Table + ''')')
>> I also tried to get and use the OBJECT_ID.
>> but I continue to get the same error:
>> Server: Msg 2506, Level 16, State 1, Line 1
>> Could not find a table or object name 'Data.dbo.sysobjects' in database
>> 'master'.
>> I have a bad feeling that DBCC SHOWCONTIG can only be run in the current
>> database, hope I'm wrong.
>>

DBCC INDEXDEFRAG and Transaction Log

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:393199
Hi,
Today I met a problem with DBCC INDEXDEFRAG on SQL Server 2000 Enterprise
with SP3. I run DBCC INDEXDEFRAG on a table with only one primary key. The
size of the table is about 120MB, the size of the index is about 1.6MB.
Before running DBCC INDEXDEFRAG, I run DBCC SHOWCONTIG and found the Logical
Scan Fragmentation was about 10%. The problem is that the transaction log I
backup after running DBCC INDEXDEFRAG on this table is about 600MB. I did
backup transaction log before this operation. There is no other activities
on this server. And I checked there wasn't any open transaction. I don't
know why DBCC INDEXDEFRAG on a small index created so big transaction log.
I appreciate any information and help !
Thanks!
Bill
Bill Wang wrote:
> Hi,
> Today I met a problem with DBCC INDEXDEFRAG on SQL Server 2000
> Enterprise with SP3. I run DBCC INDEXDEFRAG on a table with only one
> primary key. The size of the table is about 120MB, the size of the
> index is about 1.6MB. Before running DBCC INDEXDEFRAG, I run DBCC
> SHOWCONTIG and found the Logical Scan Fragmentation was about 10%.
> The problem is that the transaction log I backup after running DBCC
> INDEXDEFRAG on this table is about 600MB. I did backup transaction
> log before this operation. There is no other activities on this
> server. And I checked there wasn't any open transaction. I don't
> know why DBCC INDEXDEFRAG on a small index created so big
> transaction log.
> I appreciate any information and help !
> Thanks!
>
> Bill
A problem existed in SQL 2000 RTM, but was fixed in SP1. Can you confirm
you are running SP3.
http://support.microsoft.com/kb/q282286/
THe following article may explain some of the looging requirements. See
the "Logging Considerations: DBCC DBREINDEX vs. DBCC INDEXDEFRAG"
section.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Yes, David. It's SP3. I read both of articles before this operation. I will
try on another table and comfirm this problem.
Thanks for oyur help!
Bill
"David Gugick" wrote:

> Bill Wang wrote:
> A problem existed in SQL 2000 RTM, but was fixed in SP1. Can you confirm
> you are running SP3.
> http://support.microsoft.com/kb/q282286/
> THe following article may explain some of the looging requirements. See
> the "Logging Considerations: DBCC DBREINDEX vs. DBCC INDEXDEFRAG"
> section.
> http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

DBCC INDEXDEFRAG and Transaction Log

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:393199
Hi,
Today I met a problem with DBCC INDEXDEFRAG on SQL Server 2000 Enterprise
with SP3. I run DBCC INDEXDEFRAG on a table with only one primary key. The
size of the table is about 120MB, the size of the index is about 1.6MB.
Before running DBCC INDEXDEFRAG, I run DBCC SHOWCONTIG and found the Logical
Scan Fragmentation was about 10%. The problem is that the transaction log I
backup after running DBCC INDEXDEFRAG on this table is about 600MB. I did
backup transaction log before this operation. There is no other activities
on this server. And I checked there wasn't any open transaction. I don't
know why DBCC INDEXDEFRAG on a small index created so big transaction log.
I appreciate any information and help !
Thanks!
BillBill Wang wrote:
> Hi,
> Today I met a problem with DBCC INDEXDEFRAG on SQL Server 2000
> Enterprise with SP3. I run DBCC INDEXDEFRAG on a table with only one
> primary key. The size of the table is about 120MB, the size of the
> index is about 1.6MB. Before running DBCC INDEXDEFRAG, I run DBCC
> SHOWCONTIG and found the Logical Scan Fragmentation was about 10%.
> The problem is that the transaction log I backup after running DBCC
> INDEXDEFRAG on this table is about 600MB. I did backup transaction
> log before this operation. There is no other activities on this
> server. And I checked there wasn't any open transaction. I don't
> know why DBCC INDEXDEFRAG on a small index created so big
> transaction log.
> I appreciate any information and help !
> Thanks!
>
> Bill
A problem existed in SQL 2000 RTM, but was fixed in SP1. Can you confirm
you are running SP3.
http://support.microsoft.com/kb/q282286/
THe following article may explain some of the looging requirements. See
the "Logging Considerations: DBCC DBREINDEX vs. DBCC INDEXDEFRAG"
section.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Yes, David. It's SP3. I read both of articles before this operation. I will
try on another table and comfirm this problem.
Thanks for oyur help!
Bill
"David Gugick" wrote:

> Bill Wang wrote:
> A problem existed in SQL 2000 RTM, but was fixed in SP1. Can you confirm
> you are running SP3.
> http://support.microsoft.com/kb/q282286/
> THe following article may explain some of the looging requirements. See
> the "Logging Considerations: DBCC DBREINDEX vs. DBCC INDEXDEFRAG"
> section.
> l]
>
> --
> David Gugick
> Quest Software
> [url]www.imceda.com" target="_blank">http://www.microsoft.com/technet/pr...]www.imceda.com
> www.quest.com
>