Post reply

hidden maintenance plan

  • 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

  • November 3, 2005 at 4:08 pm

    #602267

    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

  • November 3, 2005 at 5:12 pm

    #602271

    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

  • November 4, 2005 at 4:55 am

    #602304

    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..

  • November 4, 2005 at 5:17 am

    #602307

    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

  • November 4, 2005 at 10:54 am

    #602406

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

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

  • November 4, 2005 at 1:55 pm

    #602470

    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

  • November 4, 2005 at 2:59 pm

    #602483

    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."

  • November 7, 2005 at 6:08 pm

    #602710

    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

  • November 7, 2005 at 7:38 pm

    #602715

    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

  • November 13, 2005 at 3:24 pm

    #603582

    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?

     

     

     

     

     

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

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