SQL Server Performance

how to read a log file

Discussion in 'General DBA Questions' started by dhamu294, Apr 8, 2009.

  1. dhamu294 New Member

    Hi,
    I have few doubts on sql server.
    1. How to see the SQL Log files.
    2. if we split table has one filegroup and index has another file group, will it improve the performance.
  2. techbabu303 New Member

    - If you are in SQL 2000 you can use SQL enterprise manager to read the log files.
    - File gorups are tricky affair would not recommend to those not familiar with it. Since it is hard to mainitain them and your backup and restore strategies need some twicking.
    -Sat
  3. satya Moderator

    On your Q1 I need further clarification, are you referring to SQL Server error log or transaction log?
    With regard to Q2, refer to http://msdn.microsoft.com/en-us/library/ms179316.aspx link which explains the architecture and defintions you are looking. We cannot guarantee that seperating data and index into seperate filegroups will improve the performance, it always depends on the SQL query execution and database design.
  4. Sandy New Member

    Dhamu, Please find the Sql Script for Database Reading file.
    1 - dbcc log(testdb_sandy,4)
    1.1 - dbcc log (mydatabaseid,-1) with tableresults
    Or
    2- USE testdb_sandy
    select *
    from ::fn_dblog(null, null)
    order by [Current LSN] desc
    where Operation = N'LOP_DELETE_SPLIT'
    Output
    ======
    (replace the mydatabaseid with the database id of the database you need (select db_id())
    Find the rows where the Operation is LOP_DELETE_ROWS and the AllocUnitName is sys.sysschobjs.clst
    These are object drop operations.
    look at the Transaction ID column, and find the previous LOP_BEGIN_XACT (operation is LOP_BEGIN_XACT) row,
    and see the UID column. If the column is -1, then the uid is not recorded, if it is not -1, then you have the person who did this.
    As DDL command can't be Logged, you can do by Trace file in SQL Server 2005 by this..
    SQL 2005 has a default trace running which you can use.
    An even easier option is the "Schema changes History" report in SSMS.
    In Sql Server 2000, how to trace who drop my table is:
    On 2000 there is no with tableresult, so to get the log you need to:
    /*
    dbcc traceon(3604)
    dbcc log (databaseid, -1)
    dbcc traceoff(3604)
    */
    (replace databaseid with the relevant databas id)
    Look for LOP_DELETE_ROWS for the dbo.sysobjects table.
    Please Let me know , If you have any query.
    Thanks,
    Sandy.

Share This Page