Logs

  • Hello all,

    In SQL SERVER 2008, if any DML activity or any import export or any Backup restore etc is been done... then where does this information gets stored?????

    How will i able to know where to find & what would be the best approach??

    IF i have to set this in oredr to track work.. how should i set this task???

    Please help me

    ************************************
    Every Dog has a Tail !!!!! :-D

  • Backup restore is stored in MSDB, in the restore history and backupset tables and many more.

    Some DML (Insert, Update, Delete) is stored in the transaction log, assuming you have bulk or full recover set on your database and then you need to ensure that you do regular transaction log backups.

    Import Export is not logged unless you create some custom logging and build it into the import export routines.

  • But what if the databse doesnot start due to some reason.. how to find the reason & where to fin the information :w00t:

    ************************************
    Every Dog has a Tail !!!!! :-D

  • look in the sql and windows logs

  • That will be in the SQL Server error log.

    Just to clarify something Anthony said. Data modifications (insert, update, delete and DDL) are stored in the tran log regardless of what recovery model you have, but the transaction log is not an audit log, it's not there to tell you who did what. It's there to allow rollbacks, database recovery and to guarantee durability of changes.

    If you want an audit trail, you have to set one up.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks Gila, but reagrding SQL SERVER Error Log, will this will a file stored on any location???

    coz if DB wont start how to check Error logs then??

    More over coming to Audit, can u provide links where in i would read, understand & start implementing it?

    🙂

    ************************************
    Every Dog has a Tail !!!!! :-D

  • runal_jagtap (11/1/2012)


    Thanks Gila, but reagrding SQL SERVER Error Log, will this will a file stored on any location???

    coz if DB wont start how to check Error logs then??

    More over coming to Audit, can u provide links where in i would read, understand & start implementing it?

    🙂

    Typically, the error logs are stored in C:\Program Files\Microsoft SQL Server\MSSQL10_50.instname\MSSQL\Log but obviously this may have been changed

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • If SQL doesn't start it will tell you why in the Windows event viewer, if a DB doesn't start it will be in the SQL error log.

    As Perry has already detailed the logs are typically stored where the binaries are but can be changed.

    As for auditing it all depends on what you want to audit, maybe here will be a good place to start reading up http://msdn.microsoft.com/en-us/library/cc280386%28v=sql.105%29.aspx or do a search on a search engine of your choice on the topic

  • i opened Event Viewer.. but exactly where do i find the Logs stating SQL server

    ************************************
    Every Dog has a Tail !!!!! :-D

  • Not event viewer. The SQL Server error log. (Those log entries are in the application event log too, often harder to find among all events from all the other apps)

    It's a text file on disk (location depends where you installed SQL), latest one is simply called ERRORLOG

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Ok fine.. willl see to it 🙂

    thank you everyone for quick response n help 🙂

    I will like to add one more thing

    We have one Rack Space server & other normal local server..

    Now Rackspace servers have some databases running...

    Whenever i need to copy a file from Rackspace to local i have to FTP it..

    It means that i can not apply Database mirroring or Log Shipping :w00t:

    that day i copied one file of 1gb.. it almost took an hour to complete :w00t:

    that day i wanted to import/export some tables from rack space to local one for that i have to take backup of one DB from rack space & then FTP it to local drive then restore n then import/export :w00t:

    Do any one have any document or link or idea who can help me with this type of scenario????

    ************************************
    Every Dog has a Tail !!!!! :-D

  • runal_jagtap (11/1/2012)


    Ok fine.. willl see to it 🙂

    thank you everyone for quick response n help 🙂

    I will like to add one more thing

    We have one Rack Space server & other normal local server..

    Now Rackspace servers have some databases running...

    Whenever i need to copy a file from Rackspace to local i have to FTP it..

    It means that i can not apply Database mirroring or Log Shipping :w00t:

    that day i copied one file of 1gb.. it almost took an hour to complete :w00t:

    that day i wanted to import/export some tables from rack space to local one for that i have to take backup of one DB from rack space & then FTP it to local drive then restore n then import/export :w00t:

    Do any one have any document or link or idea who can help me with this type of scenario????

    have you consulted your network\domain admins for help communicating between servers?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • I spoke with them,... they said they are using a FTP via which the files are transffered.. but if i want to make a database backup restore or data import/export then this will be a mess to work... i we have to forget abt Database Mirroring.. coz Mirroring would fail or slow down the network :w00t::crazy:

    ************************************
    Every Dog has a Tail !!!!! :-D

Viewing 13 posts - 1 through 12 (of 12 total)

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