Offer Offer

News We Recently Launched AD Migrator and AD Reporter.

How to Analyze SQL Server Log File

Analyze SQL Server Transaction Log details in the simplest manner Via SQL Log Analyzer Tool. Just go through the Step-by-Step instructions to understand the working process of software.

Online DB Option

STEP 1

Open & launch the SQL Log Analyzer tool.

Click Start » All Programs » SysTools SQL Log Analyzer

sql log analyzer

STEP 2

Click on Open to add .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. Afterward, choose Authentication and click on Select Database option drop down arrow to opt for desired 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 data of each table by selecting it from left-side panel.

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 by checking the corresponding checkboxes to export their transactions.

selected table export

STEP 16

Click on Export button.

click on export

STEP 17

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

sort according to table name

STEP 18

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

sort according to table name

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.

scanning finish

Offline DB Option

STEP 1

Open & launch the SQL Log Analyzer tool.

Click Start » All Programs » 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

Note: If you are facing this error (software unable to fetch MDF file with LDF), then the reason is that some SQL Server versions store the default file with an uppercase file name. Thus, users need to make sure to change the log name in lower case. Otherwise, the software will not browse the MDF file.

uppercase error

Here, users can see that the software fetched both the LDF and MDF files easily after changing the file name from uppercase to the required lowercase format.

lowercase correct

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 .str file into desired location on the local system.

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

Get the Preview of log file data on software dashboard.

preview log data

STEP 14

Select tables which you want to export from the left-side panel.

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:

  • SQL Server Database
  • SQL Server Compatible SQL Script
  • 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

Free Download SQL Log Analyzer Download Now