hidden maintenance plan

Question

hidden maintenance plan

Philip Tao

SSC Veteran

Points: 252
More actions
November 3, 2005 at 3:34 pm

#169466

Hi all,

I have SQL Server 2000 running on Windows 2003 Enterprise Edition.
I have two database maintenance plans running into each other.   The problem is one of the plan is not displayed in the maintenance plan window in EM.
After i deleted the one displayed in EM, the other hidden plan is still running and generating transaction log backup files. All of the transaction log backups are 14KB.   I checked SQL Server logs and it didn't report that it has backup any transaction logs.
I checked the SQL Agent, SQL Jobs, i can't find any job display there.   I checked msdb database, all the sysmaint.. tables and all of them don't show any entries EXCEPT sysdbmaintplans where it has one plan called All Ad-hoc plans
What else should i check?

Thanks in advance

Philip

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

One can get sqlmaint.exe or xp_sqlmaint to write to the maintenance history table by including "-WriteHistory" as an argument. When no maintenance plan is specified, the results appear under "All Ad-hoc plans".

Look in Windows Scheduler first and then for a startup stored procedures.

Last but not least, turn on profilier to capture the host name , program name , etc.

SQL = Scarcely Qualifies as a Language

Philip Tao SSC Veteran Points: 252 More actions · Answer 2

Hi Carl,

I ran SQL profiler but apart from user queries i can't see any SQL Statements that execute sqlmaint job.

I used SQLProfiler Standard template and added extra data columns such as DBUsername, Hostname and application name.

In the windows scheduler there is no schedule.

Where can i find the startup stored procedure?

Thanks

Philip

Ebong SSC-Addicted Points: 419 More actions · Answer 3

You could check in Select * from msdb.dbo.sysjobschedules

Pay attention to freq_type... 64 = When SQL Server Agent Starts.

OR if you are looking for a specific Procedure...

Select all procedures in a cursor then loop through

use master

go

IF OBJECTPROPERTY ( object_id('proc_name'),'ExecIsStartup') = 1

print 'proc_name will run at startup'

Else

print 'proc_name No'

Hope this helps..

Carl Federl One Orange Chip Points: 25384 More actions · Answer 4

For reference on the trace events and trace columns, see in BOL sp_trace_setevent.

As sqlmaint.exe is a program, not a SQL statement, so "sqlmaint" might be in the "ApplicationName" field but would not be in the TextData field. Search in the field TextData for "backup" which might then indicate the login name, host and application.

Also check for a Multiserver Administration Setup where a job on another server is initiating the backup.

SQL = Scarcely Qualifies as a Language

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

have you checked for stored procedures marked to execute on start-up ?

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

SQLBill SSC Guru Points: 51440 More actions · Answer 6

Is this a database created by an application? Some vendors create maintenance plans within their application. We have two that are that way, but the interface allows them to be turned off (no, I can't tell you what the vendors are - it falls under the proprietary information rules).

Talking to the companies, I found out that in their view - MS SQL Server is so easy to administer, that some organizations don't have a dedicated DBA. So they built the maintenance functions into their applications so anyone could do it.

Our vendor applications delete x days of data, reindex the tables, shrink the files and do a couple of other things. But I have them turned off since I do the maintenance work myself. But in the jobs, they show up as different things (whatever the vendor names them).

-SQLBill

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 7

Even MS has 'builtin' this undocumented feature (maintenance with no listed Jobs) in some products ... in particular SMS and MOM (to a limited extent) ... as for showing up, well some of the MOM 'stuff' does show up, but not all of it !

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Philip Tao SSC Veteran Points: 252 More actions · Answer 8

Hi,

Is this T-SQL works to check for startup stored procedure?

declare @name1 char(50)

DECLARE all_stored_proc CURSOR FOR

SELECT name from sysobjects where xtype = 'P'

OPEN all_stored_proc

FETCH NEXT FROM all_stored_proc into @name1

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH next from all_stored_proc into @name1

IF OBJECTPROPERTY ( object_id(@name1),'ExecIsStartup') = 1

print @name1 + ' will run at startup'

END

CLOSE all_stored_proc

DEALLOCATE all_stored_proc

Thanks

Philip

Carl Federl One Orange Chip Points: 25384 More actions · Answer 9

To complicated for me.

select name

from sysobjects

where OBJECTPROPERTY (id,'ExecIsStartup') = 1

To run against all databases:

exec master.dbo.sp_msforeachdb @cmd1 = '

select name

from sysobjects

where OBJECTPROPERTY (id,''ExecIsStartup'') = 1'

SQL = Scarcely Qualifies as a Language

Philip Tao SSC Veteran Points: 252 More actions · Answer 10

Hi guys,

I can't find any startup procedure at all.

I have also rebooted the server but it didn't fix anything, the hidden plan still runs.

In SQL profiling i can't see the job runs.

I am really stuck with this hidden plan problem. Will rebuilding msdb helps?