FIND THE DATABASE USED

Question

Post reply

FIND THE DATABASE USED

bubbly-1002401

SSC Journeyman

Points: 99
More actions
May 28, 2009 at 1:17 pm

#205368

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

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

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

SQL_DBA_3 SSCarpal Tunnel Points: 4772 More actions · Answer 1

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.

bubbly-1002401 SSC Journeyman Points: 99 More actions · Answer 2

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.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

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.

bubbly-1002401 SSC Journeyman Points: 99 More actions · Answer 4

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

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 5

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

bubbly-1002401 SSC Journeyman Points: 99 More actions · Answer 6

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

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 7

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

murthykalyani SSCarpal Tunnel Points: 4500 More actions · Answer 8

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?

bubbly-1002401 SSC Journeyman Points: 99 More actions · Answer 9

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

Ale Pelc SSCrazy Points: 2421 More actions · Answer 10

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