Post reply

Jobs in SQL Server 2005

  • August 8, 2011 at 5:48 am

    #242724

    Hi,

    I have lots of jobs on SQL Server 2005. There are 30+ databases on this instance. I want to check which job belongs to which database. If I go and open the jobs and check, I think its not good idea. Is there any other way to find out that particular job belongs to particular database.

    Thanks in advance.

  • August 8, 2011 at 6:01 am

    #1365123

    I've done this in 2 ways.

    1 - Script out every job and then do a windows file search.

    2 -

    SELECT * FROM msdb.dbo.sysjobsteps WHERE command LIKE '%dbname%' OR database_name = 'dbname'

  • August 8, 2011 at 6:52 am

    #1365174

    Thanks for reply Ninja..

  • August 8, 2011 at 7:00 am

    #1365180

    HTH.

    Just thought that you might also want to search for sp_MSforeachdb and also sysdatabases. That way you trap all the looping processes which you'd be missing right now.

    Let us know if you did other cases.

    TIA.

  • August 8, 2011 at 7:42 am

    #1365222

    Also, depending on how the jobs are configured, you might have a single job doing things for multiple databases. Just to be aware in case whatever search mechanism you put together doesn't take that into account.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 5 posts - 1 through 4 (of 4 total)

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