June 12, 2012 at 1:03 pm
Hi all,
Does SQL Server rebuild/alter index of a table on its own? There was a session locking the table and we saw that the T sql in the session was 'alter index on the locked table'. But none of us ran the alter index command on that table.. So wondering if it was automated? Also, is there a way to find out if the rebuild index was automated?
Thanks a lot
June 12, 2012 at 1:06 pm
sounds to me like its an automated SQL job or maintenance plan have you tried to look at the current sql jobs and maintenance plans
***The first step is always the hardest *******
June 12, 2012 at 1:20 pm
newbieuser (6/12/2012)
Does SQL Server rebuild/alter index of a table on its own?
No, never.
Has to be a manual execution or a job or other scheduled task that someone created.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2012 at 2:13 pm
Scan your databases code for a reference to the table or the alter command
SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, o.type_desc, sm.definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE sm.definition like '%tablename%'
ORDER BY o.type;
GO
To look at the code in your SQL Agent Jobs
SELECT [JOB].[name] AS [JobName]
, [JSTEP].[step_id] AS [StepNo]
, [JSTEP].[step_name] AS [StepName]
, CASE [JSTEP].[subsystem]
WHEN 'ActiveScripting' THEN 'ActiveX Script'
WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
WHEN 'PowerShell' THEN 'PowerShell'
WHEN 'Distribution' THEN 'Replication Distributor'
WHEN 'Merge' THEN 'Replication Merge'
WHEN 'QueueReader' THEN 'Replication Queue Reader'
WHEN 'Snapshot' THEN 'Replication Snapshot'
WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
ELSE [JSTEP].subsystem
END AS [StepType]
, [JSTEP].[database_name] AS [Database]
, [JSTEP].[command] AS [ExecutableCommand]
FROM [msdb].[dbo].[sysjobsteps] AS [JSTEP]
INNER JOIN [msdb].[dbo].[sysjobs] AS [JOB] ON [JSTEP].[job_id] = [JOB].[job_id]
WHERE [JSTEP].[command] like '%looking for value %'
ORDER BY [JobName], [StepNo]
June 12, 2012 at 2:15 pm
Thanks everyone. It was scheduled job..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply