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?