Sql Server Table Population Source

I have an Audit database(created by someone else). Something is polulating it, with table sizes data (which makes sense as it is Audit database). The SQL server has too many jobs.

Solution 1:

you could try running something like this:

SELECTDISTINCT
    o.name,o.type_desc
    FROM sys.sql_modules        m 
        INNER JOIN sys.objects  o ON m.object_id=o.object_id
    WHERE m.definition Like'%YourTableName%'ORDERBY2,1

EDIT after OP mentioned SQL Server 2000

this should work on SQl Server 2000:

--remove comments to see the actual text too
SELECTDISTINCT
    o.name --,c1.colid,c1.textFROM sysobjects                  o
        INNER JOIN syscomments      c1 ON o.id = c1.id
        --jointonext section of code incase search value is split over two rows
        LEFT OUTER JOIN syscomments c2 ON o.id = c2.id AND c2.colid=c1.colid+1WHERE c1.textLike'%YourTableName%'OR RIGHT(c1.text,100)+LEFT(c2.text,100) Like'%YourTableName%'ORDERBY1--,2

Solution 2:

Try looking at msdb..sysjobsteps in the command column for the destination table names; this will only work if they are using T-SQL to populate the tables. If they're using an SSIS (or DTS) package, this won't work.

Solution 3:

most likely it is being populated by triggers onteh the audited tables.

Solution 4:

If you know what causes data to go into the audit table, you can run a (very) brief Profiler session against the database, filtering specifically on that table, while triggering the action. That will give you further steps to back-trace the root action.