FIND THE DATABASE USED

  • I have a list of jobs in application server which are being used by the application team..the jobs access the databases from the production server(only system databases are present in the application server).

    i wish to know which of the jobs in the application server are using the databases in production server.

    Please could someone suggest a way to find this out...???

    If it can be done from scripting the job, please let me know how it can be found out fromt he script?

    thankyou!!

  • I did'nt understand your question completely...

    But you can know what are the jobs which are using particular databases.

    Use msdb

    select * from sysjobsteps

    you have a column database_name in this table.

  • Thanks..I checked the query but i get the database names to be either NULL or master database, i dont get the name of the user databases.

    I want to know the database that a particular job uses.

  • Are jobs running T-SQL scripts? look at the code, specifically "use" statements.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • Hi ,

    few jobs run tsql while few others are cmdexec or SSIS...i cheked the job scripts. the use statement only says use msdb...

    I found few scripts which has the name of the production server+database name mentioned for updatning the databases but in others it hasnt been mentioned...

    Please help!!

  • A bit more detail is needed.

    You say you have reviewed some of the job scripts or jobs themselves. What type of job is it (SQL Agent Job, maintenance plan, SSIS package)?

    Have you tried talking to the person that created the job?

    What types of jobs have you found the production server and database names? What types of jobs don't have it?

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • They are agent jobs.

    Well i gave the command,

    use msdb;

    select * from sysjobsteps.

    The subsystem column contained tsql for some jobs,cmdexec for some and SSIS for others.I was able to know that few jobs( with tsql subsytem) uses databases from the produciton server, as ther were commands in the script that mentioned the name of the server and database used,

    eg: update prod_server_name.db_name.schemaname.tablename

    But im not able to find such commands in other jobs.So is there a way to find out which database is being used by the other jobs ? The one who works on the application server claims there are many jobs that uses the user databases from the other server(production).

  • If they are SQL Agent jobs then you should be able to go through SSMS and view the properties of each job to determine what they are doing.

    I am not to familiar with using cmdexec commands but this is a link on how you actually create a job step with one that might start you in the direction of reading through one already created: http://msdn.microsoft.com/en-us/library/aa259594(SQL.80).aspx

    With SSIS you would have to open up [SQL Server Business Intelligence Development Studio] and create a test project, then go through the Import and Export wizard to import the SSIS package down to your computer or project. Depending on how detailed the package contents are you should be able to come across what the package does and connections it will make. There should be a 'Connection Manager' tab show up at the bottom of the window once you have the package open.

    The 'SSIS Tutorial: Creatingin A Package' shows a screenshot of what it looks like and the types of connections you can make: http://www.accelebrate.com/sql_training/ssis_tutorial.htm

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • bubbly (5/28/2009)


    I have a list of jobs in application server which are being used by the application team..the jobs access the databases from the production server(only system databases are present in the application server).

    Are jobs are sql server jobs or jobs originated from app server which access databases?

  • They are sql server agent jobs that run in application server .they extract data or update data in the production server.So i want to know which databases of the production server are used by these jobs

  • few jobs run tsql while few others are cmdexec or SSIS.

    Take a look at the SSIS packages to see what are they doing. To do that, create a new BI solution and add the dtsx package. You'll see all the steps in a graphical way and what does each step executes.

    cheers

    Alejandro Pelc

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

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