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
>

DBCC CHECKDB FAILED on SQL2k5x64 active\passive cluster

When using an active\passive sqlserver 2005 cluster (standard edition) on MS Server 2003 R2 Enterprise x64 with SP2 and I run the DBCC CHECKDB commands on any of the system databases I receive this error:

Check Database Integrity (selio)
Check Database integrity on Target server connection
Databases: All system databases
Include indexes
Task start: 2007-06-06T16:50:38.
Task end: 2007-06-06T16:50:38.
Failed-1073548784) Executing the query "DBCC CHECKDB WITH NO_INFOMSGS
" failed with the following error: "The database could not be exclusively locked to perform the operation.
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Command:USE [master]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [model]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [msdb]
GO
DBCC CHECKDB WITH NO_INFOMSGS

I don't receive this error when I run CHECKDB on the system databases on our test environment x64 box (same OS and hardware).

The file system needs to be NTFS for database snapshots. FAT will fail.|||the file system is using NTFS. This is a new cluster setup and sql installation. I just restored a user database to the cluster and tried DBCC CHECKDB on the user database and received the same error message. Part of the error message mentions "Result Set property not set correctly" as a possible solution. Do you know what this is referring to?