I have recently started using the dba_indexDefrag_sp stored procedure from SQL Fool (http://sqlfool.com/2011/06/index-defrag-script-v4-1/) against my production databases running on SQL Server 2012 (Standard 64-bit). When I execute the procedure I use all of the default settings, the only parameter I provide is the @database, which I use to pick out the active databases which need maintenance - for example:
USE MASTER GO EXEC dba_indexDefrag_sp @DATABASE='MyDb'
If I run this from SQL Management Studio, on the server, the procedure executes to completion, doing its work and recording its activity, taking about an hour and a half. If, however, I create a SQL Agent job for it, using the same login account (my own) to run the job then I see the job execute successfully BUT I see that the job completed in 0 seconds and the job history indicates an issue:
Job Name Weekly Index Defragment Step Name Defragment MyDb Db Duration 00:00:00 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message Executed as user: dbo. The user does not have permission to perform this action. (Line Number: 442) [SQLSTATE 01000] (Message 0). The step succeeded.
The problematic line 442 is an INSERT, as follows:
INSERT INTO dbo.dba_indexDefragStatus ( databaseID , databaseName , objectID , indexID , partitionNumber , fragmentation , page_count , range_scan_count , scanDate ) SELECT ps.database_id AS 'databaseID' , QUOTENAME(DB_NAME(ps.database_id)) AS 'databaseName' , ps.[object_id] AS 'objectID' , ps.index_id AS 'indexID' , ps.partition_number AS 'partitionNumber' , SUM(ps.avg_fragmentation_in_percent) AS 'fragmentation' , SUM(ps.page_count) AS 'page_count' , os.range_scan_count , GETDATE() AS 'scanDate' FROM sys.dm_db_index_physical_stats(@databaseID, OBJECT_ID(@tableName), NULL , NULL, @scanMode) AS ps JOIN sys.dm_db_index_operational_stats(@databaseID, OBJECT_ID(@tableName), NULL , NULL) AS os ON ps.database_id = os.database_id AND ps.[object_id] = os.[object_id] AND ps.index_id = os.index_id AND ps.partition_number = os.partition_number WHERE avg_fragmentation_in_percent >= @minFragmentation AND ps.index_id > 0 -- ignore heaps AND ps.page_count > @minPageCount AND ps.index_level = 0 -- leaf-level nodes only, supports @scanMode GROUP BY ps.database_id , QUOTENAME(DB_NAME(ps.database_id)) , ps.[object_id] , ps.index_id , ps.partition_number , os.range_scan_count OPTION (MAXDOP 2);
I don't understand why it fails as a SQL Agent job yet succeeds 'interactively'. Is there something about that INSERT statement which needs special attention? I would appreciate some help to allow me to resolve this problem. Thanks.