The Importance of Log Files in SQL Server

On the SQL server every day we perform many transactions and as we know that all these transactions affect the log file. After all, the transactions are stored in the log file so every transaction contains some space in the log file so it is the responsibility of log file to manage all the transactions in SQL server. The other very important thing is that it is next to impossible to keep in mind all the transactions performed on the database. So SQL server provides such a facility that we do not need to remember the transactions rather, we give them space to store and retrieve from there. If necessary, we can know which transaction took place when we had performed a task, so that we can easily understand all the transactions.

But sometimes we have to face some difficult situations where the database becomes inaccessible due to technical failure or human errors. It is a major problem with SQL server, where we are performing a task on SQL server and sudden a power failure takes place. In such condition, there are so many chances where a log file can corrupt also affecting the transaction logs. The SQL server database becomes readable to these queries, but it depends on us as to which recovery model we have chosen.

Why We Use Log Files for Forensics?

As we know, the log files hold the transaction logs in SQL server so that if necessary we could use these transaction logs to get access to the corresponding data. Another important thing is that the size of a log file does matter a lot in SQL server. It is so because if a third person tries to misuse our database and the size of the log file has increased after this; and if we forget to remember the size of the log file and want to know what activities were performed by a third person, then we can take into account the activities logged into the log file to detect these unknown activities.

Another reason for using log files for forensics is that we can detect those records that we have deleted accidentally

When We Need the Log Forensics?

In some cases a log file is also needed for forensics as a log file is made up of the transaction logs. Thus, it is very important to focus on those transactions which make changes in the database. Such transactions are delete, update, insert or drop. These are DDL and DML statements and can change the database. So a third person can easily change our database if we have not applied any security to the database. In this case, it is very important for us to check the last performed actions. After the forensics, we will get to know where were the changes made in the database.

How to Detect Deleted Statements in SQL Server?

It happens many times that when you're dealing with your database and accidentally performed any task such as dropping a table, deleting a record or if a third person has accessed your database and performed some DML statements, In such case, there is no need to worry about it, the SQL Server uses some undocumented commands, through which you can detect all the transactions performed by you or by a third person.

Where to Begin the Detection?

To start the detection we will follow some basic steps:

1. At first, we will create a database

 ---Create database----
     create database readingdblog;
     GO

In the first step we have created a database named it ‘readingdblog’

2. After creating a database we will create a table in it.

 ---Create table---
       use readingdblog;
       GO
       create table person
       (
       sn int identity,
       name varchar (30),
       city varchar (50)
       );

In the second step we have created a table named person with two columns.

3. After creating a table, we will now insert a record/row into the table.

---insert rows---
        use readingdblog;
        GO
        insert into person values('alen','new york');
        GO

After inserting a record we have to delete this record for the detection.

---Delete row---
        use readingdblog;
        GO
        delete from person where sn=1;
        GO

4. After deleting the record, we will now find the info about deleted record. Run the below command to find info about deleted records.

     USE ReadingDBLog
     GO
 SELECT 
     [Transaction ID],
     Operation,
     Context,
     AllocUnitName
 FROM 
    fn_dblog(NULL, NULL) 
 WHERE 
    Operation = 'LOP_DELETE_ROWS'

Using fn_dblog function, we can see the deleted statement which we had performed. As we are looking for deleted records in person table; we can see the dbo.person in AllocUnitName column. The above output shows that a delete statement has been executed on a HEAP table ‘dbo.person’ under transaction ID 0000:0000031b. Now we will capture the transaction Id for our next step.

5. Now we will use this transaction ID to find out some more information about the deleted record(s).

    USE ReadingDBLog
    GO
 SELECT
    Operation,
    [Transaction ID],
    [Begin Time],
    [Transaction Name],
 FROM
    fn_dblog(NULL, NULL)
 WHERE
    [Transaction ID] = ‘0000:0000031b’
 AND
    [Operation] = 'LOP_BEGIN_XACT'

After executing the fifth step we can see the [Begin Time] of this transaction, by which we can easily detect the time at which a transaction has been performed and also we can see the name [Transaction Name] of this transaction.

Now we can see the delete statement began at 2015/01/10 05:20:28:623 under transaction ID 0000:0000031b

Conclusion:

The above detection method shows how we can detect accidentally deleted records and also if an unauthorized person misusing our database. So in that situation we can apply these above steps to detect all the transactions which he has performed. But the fact is that the procedure is so lengthy and time consuming and another thing is that if you don’t have the SQL server you cannot use this detection method. In such case you need to have a third party expert tool like SysTools SQL log analyzer for forensics purposes, which saves time and gives a satisfactory result to you.