dbcc output capture

Hi
Is there a way to capture the dbcc output into a t-sql variable or in
table.
Thanks
MangeshMangesh Deshpande wrote:
> Hi
> Is there a way to capture the dbcc output into a t-sql variable
> or in table.
>
> Thanks
> Mangesh
Many DBCC statements in SQL 2000 offer a WITH TABLERESULTS option that
return the information as a result set. You can insert into a temp table
using:
create table #dbcc_test (tran_item nvarchar(128), tran_val
nvarchar(128))
insert into #dbcc_test
Exec ('dbcc opentran (''pubs'') with tableresults')
Select * from #dbcc_test
Drop table #dbcc_test
David Gugick
Imceda Software
www.imceda.com|||I'm not sure which command you're trying to capture results for, but please
note that the TABLERESULTS option is not supported for use with DBCC
commands unless Books Online specifically indicates its use in the syntax
diagram.
Thanks,
--
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23sso5TsIFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Mangesh Deshpande wrote:
> Many DBCC statements in SQL 2000 offer a WITH TABLERESULTS option that
> return the information as a result set. You can insert into a temp table
> using:
> create table #dbcc_test (tran_item nvarchar(128), tran_val nvarchar(128))
> insert into #dbcc_test
> Exec ('dbcc opentran (''pubs'') with tableresults')
> Select * from #dbcc_test
> Drop table #dbcc_test
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Thanks. The real coincedence. I wanted to write the script to use
dbcc indexdefrag using dbcc showcontig utility by capturing the output and
selectively running for some tables where scan density is low. But I found
the exact same script in MS SQL 2000 doc. Thanks again.
"Ryan Stonecipher [MSFT]" wrote:

> I'm not sure which command you're trying to capture results for, but pleas
e
> note that the TABLERESULTS option is not supported for use with DBCC
> commands unless Books Online specifically indicates its use in the syntax
> diagram.
> Thanks,
> --
> Ryan Stonecipher
> Microsoft SQL Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:%23sso5TsIFHA.1860@.TK2MSFTNGP15.phx.gbl...
>
>

dbcc output capture

Hi
Is there a way to capture the dbcc output into a t-sql variable or in
table.
Thanks
Mangesh
Mangesh Deshpande wrote:
> Hi
> Is there a way to capture the dbcc output into a t-sql variable
> or in table.
>
> Thanks
> Mangesh
Many DBCC statements in SQL 2000 offer a WITH TABLERESULTS option that
return the information as a result set. You can insert into a temp table
using:
create table #dbcc_test (tran_item nvarchar(128), tran_val
nvarchar(128))
insert into #dbcc_test
Exec ('dbcc opentran (''pubs'') with tableresults')
Select * from #dbcc_test
Drop table #dbcc_test
David Gugick
Imceda Software
www.imceda.com
|||I'm not sure which command you're trying to capture results for, but please
note that the TABLERESULTS option is not supported for use with DBCC
commands unless Books Online specifically indicates its use in the syntax
diagram.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23sso5TsIFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Mangesh Deshpande wrote:
> Many DBCC statements in SQL 2000 offer a WITH TABLERESULTS option that
> return the information as a result set. You can insert into a temp table
> using:
> create table #dbcc_test (tran_item nvarchar(128), tran_val nvarchar(128))
> insert into #dbcc_test
> Exec ('dbcc opentran (''pubs'') with tableresults')
> Select * from #dbcc_test
> Drop table #dbcc_test
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Thanks. The real coincedence. I wanted to write the script to use
dbcc indexdefrag using dbcc showcontig utility by capturing the output and
selectively running for some tables where scan density is low. But I found
the exact same script in MS SQL 2000 doc. Thanks again.
"Ryan Stonecipher [MSFT]" wrote:

> I'm not sure which command you're trying to capture results for, but please
> note that the TABLERESULTS option is not supported for use with DBCC
> commands unless Books Online specifically indicates its use in the syntax
> diagram.
> Thanks,
> --
> Ryan Stonecipher
> Microsoft SQL Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:%23sso5TsIFHA.1860@.TK2MSFTNGP15.phx.gbl...
>
>

dbcc output capture

Hi
Is there a way to capture the dbcc output into a t-sql variable or in
table.
Thanks
MangeshMangesh Deshpande wrote:
> Hi
> Is there a way to capture the dbcc output into a t-sql variable
> or in table.
>
> Thanks
> Mangesh
Many DBCC statements in SQL 2000 offer a WITH TABLERESULTS option that
return the information as a result set. You can insert into a temp table
using:
create table #dbcc_test (tran_item nvarchar(128), tran_val
nvarchar(128))
insert into #dbcc_test
Exec ('dbcc opentran (''pubs'') with tableresults')
Select * from #dbcc_test
Drop table #dbcc_test
David Gugick
Imceda Software
www.imceda.com|||I'm not sure which command you're trying to capture results for, but please
note that the TABLERESULTS option is not supported for use with DBCC
commands unless Books Online specifically indicates its use in the syntax
diagram.
Thanks,
--
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23sso5TsIFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Mangesh Deshpande wrote:
>> Hi
>> Is there a way to capture the dbcc output into a t-sql variable
>> or in table.
>>
>> Thanks
>> Mangesh
> Many DBCC statements in SQL 2000 offer a WITH TABLERESULTS option that
> return the information as a result set. You can insert into a temp table
> using:
> create table #dbcc_test (tran_item nvarchar(128), tran_val nvarchar(128))
> insert into #dbcc_test
> Exec ('dbcc opentran (''pubs'') with tableresults')
> Select * from #dbcc_test
> Drop table #dbcc_test
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Thanks. The real coincedence. I wanted to write the script to use
dbcc indexdefrag using dbcc showcontig utility by capturing the output and
selectively running for some tables where scan density is low. But I found
the exact same script in MS SQL 2000 doc. Thanks again.
"Ryan Stonecipher [MSFT]" wrote:
> I'm not sure which command you're trying to capture results for, but please
> note that the TABLERESULTS option is not supported for use with DBCC
> commands unless Books Online specifically indicates its use in the syntax
> diagram.
> Thanks,
> --
> Ryan Stonecipher
> Microsoft SQL Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:%23sso5TsIFHA.1860@.TK2MSFTNGP15.phx.gbl...
> > Mangesh Deshpande wrote:
> >> Hi
> >>
> >> Is there a way to capture the dbcc output into a t-sql variable
> >> or in table.
> >>
> >>
> >> Thanks
> >> Mangesh
> >
> > Many DBCC statements in SQL 2000 offer a WITH TABLERESULTS option that
> > return the information as a result set. You can insert into a temp table
> > using:
> >
> > create table #dbcc_test (tran_item nvarchar(128), tran_val nvarchar(128))
> >
> > insert into #dbcc_test
> > Exec ('dbcc opentran (''pubs'') with tableresults')
> >
> > Select * from #dbcc_test
> >
> > Drop table #dbcc_test
> >
> >
> > --
> > David Gugick
> > Imceda Software
> > www.imceda.com
>
>sql

DBCC INDEXDEFRAG

I've automated defragging my indexes with a stored procedure, but can't
capture the results of the defrag into a table. How can I go about this?
I've tried:
Declare @.sql varchar(1000)
Select @.sql = 'DBCC INDEXDEFRAG(''SOSManager'',''tmpKeys'','
'PK_tmpKeys'')'
Insert Into maint_IXDEFRAG (PagesScanned, PagesMoved, PagesRemoved)
exec(@.sql)
but I get the following error:
Cannot perform a IndexDefrag operation inside a user transaction. Terminate
the transaction and reissue the statement.
Ideas?The error message is pretty clear on this. An INSERT is one transaction, and
one of the thing with
INDEXDEFRAG is that it isn't all in one transaction (otherwise it would have
to keep locks etc.).
How about instead of having EXEC('string') in your INSERT, you have xp_cmdsh
ell from there you use
OSQL.EXE to execute your DBCC command? You will only get one column back fro
m xp_cmdshell so do some
post processing.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
news:95EB0997-1324-4E22-A55D-97C6B9A258F4@.microsoft.com...
> I've automated defragging my indexes with a stored procedure, but can't
> capture the results of the defrag into a table. How can I go about this?
> I've tried:
> Declare @.sql varchar(1000)
> Select @.sql = 'DBCC INDEXDEFRAG(''SOSManager'',''tmpKeys'','
'PK_tmpKeys'')
'
> Insert Into maint_IXDEFRAG (PagesScanned, PagesMoved, PagesRemoved)
> exec(@.sql)
> but I get the following error:
> Cannot perform a IndexDefrag operation inside a user transaction. Terminat
e
> the transaction and reissue the statement.
> Ideas?

DBCC Daily commands

Can anyone tell me what are the daily DBCC commands they run and if they run it as a job and how you can capture the result of those commands for further analysis. I am switching from a development role to a DBA role and i can never find such a sugestion anywhere online or in books. So more than 1 openion would be greatly appreciated.

Hi,

you can read this article at http://www.sql-server-performance.com/dbcc_commands.asp,to learn more about DBCC commands and how it will help you when you are trying to optimize your SQL Servers.

You can run the DBCC commnads as a job by scheduling DBCC commands by using AT scheduler command.

Best Regards