DB log info

Can I somehow investigate what was running in the database during the day or hourly?
Or maybe narrow it down to the info about dropping/ creating   tables/columns.
Thanks
quasar_eeAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
quasar_ee,
> Can I somehow investigate what was running in the database during the
> day or hourly?

u can use sql profiler to monitor this, there is another free tool 'sqlspy' which is also useful for identifying, what's going on in the sql server
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Otherwise you can use some log viewers, preferably Lumigen Log Explorer to do this
0
 
quasar_eeAuthor Commented:
Let's say the table was dropped in the database yesterday and I'd like to know who did it and when.
Where should I start and how hard it is?
Thanks
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
you can do it using the Log viwer,  Download a trial version and run it...
otherwise u can use
DBCC LOG

This command is used to view the transaction log for the specified database.

Syntax:

DBCC log ({dbid|dbname}, [, type={-1|0|1|2|3|4}])

where:

dbid or dbname - Enter either the dbid or the name of the database

type - is the type of output, and includes these options:

0 - minimum information (operation, context, transaction id)

1 - more information (plus flags, tags, row length, description)

2 - very detailed information (plus object name, index name, page id, slot id)

3 - full information about each operation

4 - full information about each operation plus hexadecimal dump of the current transaction log's row.

-1 - full information about each operation plus hexadecimal dump of the current transaction log's row, plus Checkpoint Begin, DB Version, Max XDESID

by default, type = 0

Then you need to interpret each displayed field.
0
 
quasar_eeAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.