DBCC INDEXDEFRAG

[Kevin Bowker]

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?

[Tibor Karaszi]

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_cmdshell from there you use
OSQL.EXE to execute your DBCC command? You will only get one column back from 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/

Post by Kevin Bowker
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?
Insert Into maint_IXDEFRAG (PagesScanned, PagesMoved, PagesRemoved)
Cannot perform a IndexDefrag operation inside a user transaction. Terminate
the transaction and reissue the statement.
Ideas?