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
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
Microsoft SQL Server
Last Comment
Vitor Montalvão
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.
ASKER
not a solution on production
Thanks
Thanks
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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.
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 sizeYou already told that. We can't change the way SQL Server engine works. Well, maybe Microsoft Engineers can :)
Can you post the changed View?
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
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.
ASKER
exactly
So, IMHO, this question should be closed by accepting my comment ID: 42073090
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.
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.