👀 Views: 123 💬 Answers: 1 📅 Created: 2025-06-03

sql-server sql-agent indexing sql

I'm trying to configure I tried several approaches but none seem to work. I'm stuck on something that should probably be simple. I'm working with an scenario with my index maintenance script running on SQL Server 2019. The script is designed to rebuild indexes on a specific table, but when it runs as a SQL Agent job, it throws the behavior: `want to drop the index 'IX_ColumnName' because it doesn't exist or you do not have permission.` This scenario does not occur when I run the script directly from SQL Server Management Studio (SSMS). Here’s a snippet of the script: ```sql SET NOCOUNT ON; DECLARE @sql NVARCHAR(MAX); SET @sql = N''; SELECT @sql += ''ALTER INDEX [IX_ColumnName] ON [dbo].[YourTableName] REBUILD;'' + CHAR(10) FROM sys.indexes WHERE name = 'IX_ColumnName'; EXEC sp_executesql @sql; ``` I have verified that the index exists at the time the job is executing, and the account running the SQL Agent job has the necessary permissions. I've also enabled logging for the SQL Agent job, and I see that the script executes without issues; however, it fails at the index dropping stage. I tried adding `IF EXISTS` checks before the `ALTER INDEX` command, but the job still fails with the same behavior. I suspect it might be related to the job's execution context or permissions. Is there a different way to structure this script to ensure it works correctly in the SQL Agent job context? Any advice on troubleshooting this would be greatly appreciated! I'm working with Sql in a Docker container on Debian. Is this even possible? This issue appeared after updating to Sql LTS. I'm open to any suggestions. Could this be a known issue?