[Solved] How can I extract T-SQL command from LDF file in SQL Server?

EverSQL Database Performance Knowledge Base

How can I extract T-SQL command from LDF file in SQL Server?

Database type:

I use SQL Server 2008 R2 and SSAS and SSIS.

I create a Stage database that fill every day from OLTPs databases.

and then after data cleansing and integrated data (ETL) i most transfer my data to DataWarehouse.

Now what I need to do is how i can change tracking in my OLTPs DataBase.

I know about solution Trigger ( I can fire a trigger on all table in OLTP Database and log data Inserted/ Deleted/ Updated from Inserted and Deleted table inside trigger)

but my OLTP database is very big (about 80,000,000 record) and create trigger make my business slow.

i find some query from this site like this :

SELECT 
*
FROM 
sys.fn_dblog(NULL,NULL)

that show all record from LDF file.

and i find some 3rd parties software that it can read LDF file and then extract command (Insert/Update/Delete) command like ApexSQL

and i find one question in this site How to view transaction logs in SQL Server 2008

and finally i think that if one 3rd parties can extract this command from LDF file why we can not extract that?!?

and in the other hand i need find DDL Command like Alter Table and Alter Field in OLTPs Database for change my Stage Database.

and i find This Link that can revers deleted row from LDF file.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
The optimized query:
SELECT
        * 
    FROM
        sys.fn_dblog(NULL,
        NULL)

Related Articles



* original question posted on StackOverflow here.