[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 160
  • Last Modified:

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
0
quasar_ee
Asked:
quasar_ee
  • 3
  • 2
1 Solution
 
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now