How to Analyze SQL Server Log File

Now analyze SQL Server transaction log details in the simplest manner via SQL Log Analyzer Tool. You just need to follow this "How it Works" section to understand the working process of the tool.

STEP 1

Open & launch the SQL Log Analyzer tool.

Click Start » All Programs » SysTools SQL Log Analyzer » SysTools SQL Log Analyzer

sql log analyzer
STEP 2

Click on Open for adding .ldf/.mdf file to the application.

click on open
STEP 3

Here user will get two options to add file to the software:

  • Online DB Option
  • Offline DB Option

Online Database Option

If user select Online DB Option, Software allow you to select the Server Name from available list. If the Server Name not available in the list then you can enter it manually, then choose Authentication and click on drop down arrow to Select Database.

online db option
STEP 4

Now click on Ok button.

click ok
STEP 5

Click OK once the scanning of LDF file finishes.

scanning finish
STEP 6

You can view the complete scanning details such as; No. of Records, No. of Inserts, No. of Updates, & No. of Deletes. Click Ok to proceed.

scanning details
STEP 7

You can preview the select log file data for each table.

preview
STEP 8

Select any of the listed Tables in order to preview & analyze the corresponding log entries of operations performed on the database.

selected table preview
STEP 9

Click on the Transaction field name to sort the list of transactions in ascending or descending order.

sort according to transaction
STEP 10

You can sort the information of transactions by Login Name.

sort according to login name
STEP 11

Similarly, you can sort the table fields according to the Time, when a particular transaction was executed.

sort according to time
STEP 12

Table Name can also be sorted in upside-down or vice versa order by clicking on the field.

sort according to table name
STEP 13

Same way, you can click on the Transaction Name field to sort it.

sort according to transaction name
STEP 14

Click on any transaction of a particular table to preview its details.

preview details of table
STEP 15

Export Log Files:

Select the Tables using checkboxes provided against each, to export their transactions.

selected table export
STEP 16

Click on Export.

click on export
STEP 17

Check the transaction record type checkbox that you want to export: INSERT, UPDATE, and DELETE.

apply filter option
STEP 18

Check the Date Filter option to export transaction records of a selected date range.

date filter
STEP 19

Choose Export Option to Save Records:

1. SQL Server Database: For SQL Server Database you need to fill the credentials.

2. SQL Server Compatible SQL Scripts: Select a location on your local machine for exporting the log files in .sql format.

3. CSV: Similarly, you can export the Log File into CSV (Comma Separated Values) format.

multiple export option
STEP 20

Enter Database credentials: Server Name & Authentication type. If you are using SQL Server Authentication then fill the appropriate User Name & Password.

enter database credentials
STEP 21

Select the destination database to export On: Create New Database or Export to Existing Database

enter database credentials
STEP 22

Provide the Database Name, If you want to save as Create New Database

enter database credentials
STEP 23

Click on Export to begin the export procedure.

export procedure
STEP 24

Click OK on the window that appears on screen prompting you about the successful completion of export.

click ok
STEP 25

View the Complete Status Report and click on Ok button.

complete status report
STEP 26

A prompt up window will open that will ask Do you want to Export the Details to a .csv file? Click Yes if you want.

export to csv file
STEP 27

Select a folder to save the .csv file

select destination location
STEP 28

Now, open .csv files to view the saved report on your system.

select destination location
STEP 1

Open & launch the SQL Log Analyzer tool.

Click Start » All Programs » SysTools SQL Log Analyzer » SysTools SQL Log Analyzer

sql log analyzer
STEP 2

Click on Open for adding .ldf/.mdf file to the application.

click on open
STEP 3

Here user will get two options to add file to the software:

  • Online DB Option
  • Offline DB Option

Offline Database Option

If users select Offline DB Option, click on the LDF File Path Browse button to load the Log file.

browse ldf file
STEP 4

Select the LDF file & click on Open.

Note:The Software easily reads multiple .ldf files of a database. For this, you just need to add primary .ldf file of the database and it will auto fetch the transaction logs of all associated .ldf files.
select log file
STEP 5

If database .mdf file is corrupted, the software enables this option to select the version of SQL database file.

select mdf file version
STEP 6

User can Check Get all Login\User names checkbox if you want to identify who has done the transaction (Optional). For this you need to provide the Login Credentials (Server Name, User Name and Password).

get all user name
STEP 7

Click on Fetch User to retrieve all the users of Server.

fetch user
STEP 8

A prompt up window will open saying Scanning completed successfully.

scanning completed successfully
STEP 9

Now view scanning details and click on Ok button.

view scanning details
STEP 10

You can save scanned file into .str file format.

save scanned file
STEP 11

Save the the .str file into desired location.

save .str file to desired location
STEP 12

Once the file saved successfully, click on OK button to view the transactional information.

view transactional information
STEP 13

Preview of log file data.

preview log data
STEP 14

Select tables which you want to export.

selected table export
STEP 15

Now click on Export button.

click on export
STEP 16

Export data according to transaction: INSERT, UPDATE, DELETE.

click on export
STEP 17

You can also export transaction records of selected date using Date Filters.

date filter
STEP 18

Select Export option:

1) SQL Server Database 2) SQL Server Compatible SQL Script 3) CSV

export options
STEP 19

Now browse the saving location using the Destination field Browse button.

browse location
STEP 20

Select a preferred destination location and click Ok.

select destination
STEP 21

Click on Export button.

export
STEP 22

A window will appear prompting Export completed successfully. Click on Ok button.

export completed
STEP 23

Now you can see the complete export report and click on Ok.

export report
STEP 24

The software will ask you to export the details to a CSV file. If you want to save then click on Yes.

export details to csv
STEP 25

Select the folder where you want to save. Click on Ok

select folder to save
STEP 26

Now, you can open & preview the CSV file on your system.

select folder to save
STEP 27

After Exporting Log File:

Navigate to the destination location chosen to save the output to check the results.

navigate to the destination location
STEP 28

Preview the .sql file with SQL Server Database.

preview sql file