Introduction

Lumigent Log explorer is a tool used with Microsoft SQL Server 7.0 or 2000 to analyze the transaction logs of the database for maintaining the databases and data recovery management.

Uses:

• It helps in recovering selected rows
• Undo or redo transactions
• Recover dropped Data or truncated data from the backup
• Real Time Monitoring
• Analyze the performance of the database. For example, analyze for long running transactions.
• Viewing the transaction logs to which the Log Explorer is attached to.
• Restore the required tables from the backup.
• The user can filter the log on the basis of user id, table, activity performed etc for in depth analysis.

Downloading Lumigent Log Explorer

The user can download the trial version at http://www.lumigent.com/downloads/ . A user has to contact the sales team (sales@Lumigent.com) to purchase the registered version.

Components of Log Explorer

It has client component and server component. The server component resides on the machine where the SQL Server is installed. The client component can reside on the same machine as the server component or any other machine in the network. Using the client component, the user can attach to the SQL Server database on which the Server component is installed. The server component fetches the logs from the SQL Server to the client component. The client component can be used for filtering the log data, generating SQL scripts, exporting the log data etc.

Database Management:

For analysis of the transaction log, the recovery model of the database has to be "Full".

Real Time Monitoring

1. Go to Start --> All Programs --> Lumigent --> Log Explorer for SQL Server --> Log Explorer
2. Click "Run" to install the Log Explorer. (This dialog box appears every time the Log explorer tool is run)
   
3. Here is a shot of the main Log Explorer screen.

   

4. Click "Connect"
   
5. Click "OK"
   

6. Now, attach the Log explorer to the Northwind Database.The evaluation version permits the user to attach to only "Pubs" or "Northwind" sample databases.
   
7. Click "Real Time monitor" present on the left hand side toolbar.

   Run the following script in the Query Analyzer. The script runs for about 22 secs.

   Begin tran t1
   INSERT into Employees
     (LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,City,Region,Country) 
    values('Sri','Murthy','SE','Ms.','1981-12-14','2004-02-23','Mumbai','MAH','India')
   INSERT into Employees
     (LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,City,Region,Country) 
    values('a','b','SE','Ms.','1981-12-15','2004-02-24','Mumbai','MAH','India')
   WAITFOR DELAY '000:00:02'
   UPDATE Employees set Lastname='Drew' where employeeID=1
   SELECT * from employees
   COMMIT
   Begin tran t2
   INSERT into Employees
     (LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,City,Region,Country) 
    values('aa','bb','SE','Ms.','1981-12-16','2004-02-27','Mumbai','MAH','India')
   INSERT into Employees
     (LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,City,Region,Country) 
    values('cc','bc','SE','Ms.','1981-12-27','2004-02-08','Mumbai','MAH','India')
   INSERT into Employees
     (LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,City,Region,Country) 
    values('cd','bd','SE','Ms.','1981-12-28','2004-02-09','Mumbai','MAH','India')
   INSERT into Employees
     (LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,City,Region,Country) 
    values('ce','be','SE','Ms.','1981-12-29','2004-02-10','Mumbai','MAH','India')
   INSERT into Employees
     (LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,City,Region,Country) 
    values('cf','bf','SE','Ms.','1981-12-30','2004-02-11','Jalgaon','MAH','India')
   UPDATE Employees set City='Nagpur' where employeeID=9
   UPDATE Employees set Region='California' where employeeID=10
   UPDATE Employees set Title='Software Engineer' where employeeID=11
   UPDATE Employees set Title='Project Manager' where employeeID=1
   UPDATE Employees set Title='Delivery Manager' where employeeID=2
   SELECT * from Employees
   WAITFOR DELAY '000:00:04'
   COMMIT
   BEGIN TRAN T3
   UPDATE Customers SET Address='714 Haight Street' WHERE CustomerID='ALFKI' 
   WAITFOR DELAY '000:00:02'
   UPDATE Employees SET Address='710 Haight Street' WHERE LastName='King' 
   UPDATE Customers SET ContactName='Angelina Jolie' where CustomerID='ANATR'
   SELECT * from Customers
   COMMIT
   BEGIN TRAN T4
   INSERT Customers 
     (CustomerID, CompanyName, ContactName) 
    VALUES ('AL1','Alfreds Futter1','Maria') 
   UPDATE Customers SET Address='714 Haight Street' WHERE CustomerID='ALFKI' 
   UPDATE Employees SET Address='710 Haight Street' WHERE LastName='King' 
   INSERT Customers 
     (CustomerID, CompanyName) 
    VALUES ('HAL','Hindustan Aeronautics Limited') 
   UPDATE Customers SET ContactName='Ang' where CustomerID='AL1'
   INSERT Customers 
     (CustomerID, CompanyName, ContactName) 
   VALUES ('AL2','Alfreds Futterkiste2','Maria Anders2') 
   UPDATE Customers SET ContactName='Jessica' where CustomerID='AL2'
   WAITFOR DELAY '000:00:03'
   COMMIT
   BEGIN TRAN T5
   INSERT Customers 
     (CustomerID,CompanyName,ContactName) 
    VALUES('AL3','Alfreds Futterkiste3','Maria Anders3') 
   UPDATE Customers SET Address='714 Haight Street' WHERE CustomerID='ALFKI' 
   UPDATE Customers SET Address='710 Haight Street' WHERE CustomerID='ALFKI' 
   UPDATE Employees SET Address='710 Haight Street' WHERE LastName='King' 
   INSERT Customers 
     (CustomerID,CompanyName) 
    VALUES('HIN','HINDU') 
   INSERT Customers 
     (CustomerID,CompanyName,ContactName) 
    VALUES('AL4','Alfreds Futterkiste4','Maria Anders4') 
   WAITFOR DELAY '000:00:02'
   COMMIT
   BEGIN TRAN T6
   INSERT Customers 
     (CustomerID, CompanyName, ContactName) 
    VALUES ('AL5','Alfreds Futterkiste5','Maria Anders') 
   UPDATE Customers SET Address='714 Haight Street' WHERE CustomerID='ALFKI' 
   UPDATE Customers SET Address='710 Haight Street' WHERE CustomerID='ALFKI' 
   UPDATE Employees SET Address='710 Haight Street' WHERE LastName='King' 
   INSERT Customers 
     (CustomerID, CompanyName) 
    VALUES ('TOI','Times of India') 
   INSERT Customers 
     (CustomerID, CompanyName, ContactName) 
    VALUES ('AL6','Alfreds Futterkiste6','Maria Anders') 
   WAITFOR DELAY '000:00:04'
   COMMIT
   BEGIN TRAN T7
   INSERT Customers 
     (CustomerID, CompanyName, ContactName) 
    VALUES ('AL7','Alfreds Futterkiste7','Maria Anders') 
   UPDATE Customers SET Address='714 Haight Street' WHERE CustomerID='ALFKI' 
   UPDATE Customers SET Address='710 Haight Street' WHERE CustomerID='ALFKI' 
   UPDATE Employees SET Address='710 Haight Street' WHERE LastName='King' 
   INSERT Customers 
     (CustomerID, CompanyName) 
    VALUES ('DEC','DECCANHERALD') 
   INSERT Customers 
     (CustomerID, CompanyName, ContactName) 
    VALUES ('AL8','Alfreds Futterkiste8','Maria Anders') 
   WAITFOR DELAY '000:00:04'
   COMMIT
   BEGIN TRAN
   INSERT Employees 
     (LastName,FirstName,Address,City,Region,PostalCode,Country) 
    VALUES('Jo','Janis','645 Fourth Street','Port Arthur','TX','77641','USA') 
   COMMIT
   BEGIN TRAN
   INSERT Employees 
     (LastName,FirstName,Address,City,Region,PostalCode,Country) 
    VALUES('Marley','Bob','Manley Boulevard','Kingston','Jamaica','W64 15S','JWI') 
   COMMIT
   

   

   This is the screen shot of the activities which happened in the 22 seconds.

   

Functionality of various buttons in the "Browse Log" Screen:-

The user can filter the log records on the basis of various factors like Time Range,

Type of Activity Performed, Table, user ID etc.

The pointer is now at the "t1" transaction.

On clicking the "Previous Transaction" button, the pointer jumps to the INSERT_ROWS of the "EmployeeTerritories" table.

The user can use all the functionalities in the "View Log" screen to audit the activities performed during that period. The user can filter the log records, know the transaction type, can traverse between the records in a single transaction and also multiple transactions.

Load Analysis

"Load Analysis" can be used to analyze the heavily used tables, long running transactions etc.

Click "Load Analysis" on the left hand side of the tool bar.To find the longest running transactions click the "User Load" tab.

It displays the four longest run transactions.

Click the "Table Load" to find the heavily used tables. The user can find out the number of inserts, deletes and updates performed against the table.

Solving Problems

Due to user or application errors, some unexpected changes can occur into the database. The problem can be solved by using the "Row Revision History" function of the log explorer. It shows all the modifications done to a specified row.

Click "Row Revision History" on the left side of the toolbar. Select the table as "dbo.customers".The user wants to find out the row revisions related to customer Id "AL1".

Click "View" button.

This view shows the changes done to the row before it was committed.

Click on the row which has contact name as "Maria" and operation as "Modify_columns".

Click the "Browse" button. This brings the screen to the "View Log" corresponding to this transaction.

The "View Log" screen shows three panes.

On the top pane, the user can obtain information such as table,uid,spid etc.The center pane provides information about the NT user name, login name and application name. The bottom pane gives the old and new values of the desired column.

Using the above information, the DBA can easily find out which user or application has caused the error.

Auditing the database

The user can audit the schema and permissions given to the database.

Click "View DDL commands" on the left hand side of the tool bar.

Click on the "Alter table" transaction. The user has added a column "Details" to the "Order Details" table.

The user can generate "Redo" and "Undo" scripts for many DDL commands.

"Salvage" button is used to recover the dropped tables from this view.

Click on the drop table transaction. The "Salvage" button is enabled. Click the "Salvage" button.

The log explorer creates the script. The user can specify the storage location.

This is the script generated by the Salvage function.

Audit Data Changes

Data changes can be audited by using the "Row Revision History" function.This functionality is explained above.

Audit User Activity

This can be done by filtering the log records according to the User ID.

Exporting Log Records

The log records can be exported to a database table or exported in XML, HTML or Excel format.

Recovery Management

Log explorer can be used in recovery management in the following ways:-

1. Undo select transactions
2. Restore single table from the backup.
3. Restore a particular column to a previous value
4. Salvage Dropped/Truncated Data

Undo Transactions

For example, if the user deletes all the order details in "dbo.order details" table with Employee ID as 9.The user does not have a backup.

DELETE From [Order Details] where OrderId in(SELECT orderid FROM orders where employeeId=9)

Go to "View Log" Screen. Click the Refresh button.

Right click on that row and select "Undo Transaction".

The log explorer creates the "Undo transaction" script.

Click "Run SQL Script" on the left hand side of the toolbar. Specify the file location of the undo script. Click "Run".

Restore Single table from backup

It's possible to restore only a single table from the backup. For example, if the user has dropped a single table, he does not have to go through the painful process of restoring the whole database from the backup.

This can be done as follows:

Click on "Restore table from backup" on the left hand side of the toolbar.

Select the backup file for Northwind Database. Specify the location of the file where the restore script has to be generated. Click the "Create" button.

The file created is as follows.

The user has to add the create table command in the restore script generated. If the script is run as it is, following error is generated.

Click "View/Modify Line". Add the create table script and

Save the file and close it. Click "Run the modified line and continue" button.

The table is restored from the backup.

Tip 1:- After the backup of the database is taken, the "View Log" doesn't show the backed up data. For seeing the backup data, go to "Filter Log Records". Select the tab "Recycled Data". Select "View Recycled Data" option. Now the user can view all the data.

Tip 2:- Select "Index Filtered Records" to build an index on the filtered records. This will improve the performance of browsing the log.

Restore a particular row to a value held by it in previous point of time

Click "View Log" on the left hand side of the toolbar. Suppose the user wants to see the modifications done to the "ContactName" column for "CustomerID=AL1".

Select a "Modify_Columns" opcode and right click to select "Revision History". The current value of the ContactName column for CustomerID=AL1 is "George Martin".

The user wants to rollback the value to "Maria Sunders". Click on the row which has the value "Maria Sunders". The "Rollback" button gets enabled. Click the "Rollback" button for generation of the rollback script.

Specify the location of the file. Click "Run SQL Script" and select the "RollbackRow.SQL" and click "Run".

Salvage Dropped/Truncated Data

Suppose the user has dropped the table "test1".The user does not have backup. It is still possible to recover the data.

Click "Salvage Dropped/Truncated Data" on the left hand side of the tool bar.

Type the table name if it does not appear in the list of tables. Input the approximate time.

If the user inputs time somewhere close to the actual time it was dropped, the user can retrieve all the data. In this example, the table was dropped at around 16:15.The time inputted is 16:00.The recovery script generates all the data. If the time inputted in the textbox is 10:00 then only partial script is generated.

Click on "Run SQL script" and mention the location of the recovery script and click "Run".

Issue with Log explorer:

Suppose the user creates a new table "test3".Click "View DDL Commands". The create table command is present in the DDL commands list.

Now insert some data. Click the "View Log" screen. The insert operations do not appear in the View Log screen.

Click the "Filter Log Records". The "test3" table which was created now is not included in the Table tab.

Now truncate the table "test3".Click "View DDL commands" .The truncate command appears in the list but the table name appears as some number.

Click "Salvage" button.This message appears on the screen.

The log explorer does not refresh the tables list when it is dropped or added.

Server Manager

Server Manager can be started by going to

Start --> All Programs --> Lumigent --> Log Explorer for SQL Server --> Server Manager

With the help of Server Manager, the user can configure alerts on the occurrence of certain database events.

Conclusion

Lumigent Log explorer, on the whole, is a great tool for Database Management and Recovery Operations. There are certain minor issues. But it does not affect the performance of the tool. It proves as very handy and useful tool to the DBAs.