Post reply

How can I find the source of data for a DB

  • November 25, 2015 at 6:00 am

    #322809

    Hi all, total newbie here 🙂

    Is there a way to find out which program, application or source is feeding the database?

    Server is 2008 R2.

    Thanks

  • November 25, 2015 at 6:40 am

    #1842293

    You could monitor sys.dm_exec_sessions or set a trace or extended events session.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • November 25, 2015 at 7:43 am

    #1842313

    Thanks Luis. Total newbie. How would I do that?

  • November 25, 2015 at 8:04 am

    #1842320

    I don't have much experience on any of those and don't have much spare time to play around and set something. Maybe someone else can give you something more specific.

    To use sys.dm_exec_sessions, you need to be querying the view to know what's going on at that point in time, as it will only show current sessions. An alternative is to use procedures like sp_who, sp_who2 or others that can be downloaded like Erland Sommarskog's beta_lockinfo or Adam Machanic's sp_whoisactive. All this will just give you the current state of the server.

    To monitor constantly, there are the other 2 options.

    To run a trace, use SQL Server Profiler. Be sure to save the trace to a file to minimize the needed resources and choose the appropriate events and columns.

    Extended events are a muchly improved version of Profiler (which are actually available in 2008, but GUI was included until 2012) which have lower impact on the performance of your server. You can find lots of articles in Google.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • November 25, 2015 at 10:06 am

    #1842358

    Thanks again Luis. I will work on it 🙂

  • November 25, 2015 at 11:45 am

    #1842379

    Additionally I would also look at the scheduled jobs on the server itself; there's a good chance that Extract-Transform-Load(ETL) jobs that populate your databases exists on the same server as the databases; not guaranteed, but very common.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 25, 2015 at 12:32 pm

    #1842392

    Thanks Lowell. I really appreciate.

  • November 25, 2015 at 3:01 pm

    #1842419

    I'd also ask people in the company. It's possible someone runs SSIS or performs manual imports from their workstation.

  • November 25, 2015 at 4:40 pm

    #1842431

    Thanks Steve Jones

  • November 25, 2015 at 9:21 pm

    #1842443

    Most probably is an SSIS job which would be loading the table and you could lookup the source connection in the project file to find the source data system, or sql server profiler. Honestly i haven't used profiler myself but have seen others do it.

  • November 26, 2015 at 3:48 am

    #1842481

    Thanks Devilsid

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

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