Link to home
Create AccountLog in
Avatar of bibi92
bibi92Flag for France

asked on

optimize view to prevent the transaction log from being filled

Hello,

How can I optimize the following view to prevent the transaction log from being filled

CREATE VIEW ZONE_QTE
AS
SELECT *  FROM US.QTE
Union ALL
SELECT * from  EU.QTE
Union ALL
SELECT * from  ASI.QTE


INSERT INTO WORLD.QTE with (tablock)
            SELECT [QTE_QRL_LB_EBELN]
                    
              FROM ZONE_QTE
              WHERE [QTE_QRL_LB_EBELN] like '55%' and [QTE_QRL_LB_BUS_TRAN] = 'GR'

Thanks

regards
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

The only way to not fill the transaction log is to change the database recovery model to Simple.
By the way, the tablock hint is useless in an INSERT. It's the default behavior so no need for the hint.
Avatar of bibi92

ASKER

not a solution on  production
Thanks
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of bibi92

ASKER

Maybe a solution is to modify the view, each table has 8GB size. What do you mean?
What's logged is the INSERT command and not the SELECT.
Having a filter set in your WHERE clause it's already reducing the number of rows to be returned/inserted. Anyway, without backing up the transaction log you won't be able to stop it to grow.
Avatar of bibi92

ASKER

yes but the insert select * from three tables and each table has 8GB size. Thanks
the insert select * from three tables and each table has 8GB size
You already told that. We can't change the way SQL Server engine works. Well, maybe Microsoft Engineers can :)
Can you post the changed View?
Avatar of bibi92

ASKER

CREATE VIEW ZONE_QTE_US
AS
SELECT *  FROM US.QTE
CREATE VIEW ZONE_QTE_EU
AS
SELECT * from EU.QTE
CREATE VIEW ZONE_QTE_ASI
AS
SELECT * from  ASI.QTE
How that solved your issue? You just created 3 Views and each one is a full select from the table.
Avatar of bibi92

ASKER

exactly
So, IMHO, this question should be closed by accepting my comment ID: 42073090
Avatar of bibi92

ASKER

42073090 Then no solution at all. You can't control what's logged when the Full Recovery model is set for a database. If you facing disk space issues you should provide more space for transaction logs to grow. Or, you can backup logs more often so they can be truncated more often too.
Just because you can't apply it or you can't do it, it doesn't mean no solution.
You just want to delete this question because the answer doesn't fits you and that's wrong. See this as a KB that somebody in the future with the same issue can use my comment to solve their problem.