February 1, 2017 at 4:31 am
Hi,
Is it possible to execute a query on a database which is in full Recovery Model without write to ldf/log file?
Thanks
February 1, 2017 at 4:37 am
amns - Wednesday, February 1, 2017 4:31 AMHi,
Is it possible to execute a query on a database which is in full Recovery Model without write to ldf/log file?Thanks
I just have to ask why you want to try to do this?
😎
SQL Server uses Write ahead log for acid compliance, no way around the log being written regardless of the recovery model used.
February 1, 2017 at 4:41 am
As Eirikur says, all changes are logged. But if your query doesn't make any changes, for example a SELECT query, the answer to your question is yes.
John
February 1, 2017 at 4:48 am
Want to delete several rows from a table (several gigabytes) (not truncate) and avoid that to be logged to ldf file, to avoid its growing.
February 1, 2017 at 4:55 am
amns - Wednesday, February 1, 2017 4:48 AMWant to delete several rows from a table (several gigabytes) (not truncate) and avoid that to be logged to ldf file, to avoid its growing.
Not possible. But what you can do is break the delete into small batches, and increase the frequency of the log backups while it runs.
John
February 1, 2017 at 7:14 am
Ok, thanks.
February 1, 2017 at 8:16 am
If the amount to data you want to keep is smaller that the data you want to delete, there is a trick that uses minimally logged transactions to do the bulk of the heavy lifting.
Simply create an identical table structure (including indexes) and insert the data you want to keep there... This will of course be a verbosely logged transaction but if the amount you're keeping is small compared to the amount your deleting, you end up with less logging in total.
Then you can simply truncate the original table and do a partition switch (both minimally logged transactions) to get the "keep" data back to the original table.
Since the truncate & switch operations happen nearly instantly, your down time for the table wold only be a few milliseconds.
The only real down side is that you'll have to have enough disk space to temporarily hold both the full amount of original data and the copy of the keep data...
Here is an example...
USE tempdb;
GO
--======================================================================
-- create test data
--======================================================================
CREATE TABLE dbo.OriginalData (
ID INT NOT NULL,
Value VARCHAR(36) NOT NULL,
CONSTRAINT pk_OriginalData PRIMARY KEY CLUSTERED (ID)
);
WITH
cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),
cte_Tally (n) AS (
SELECT TOP 1000000
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
cte_n3 a CROSS JOIN cte_n3 b
)
INSERT dbo.OriginalData (ID,Value)
SELECT
ID = t.n,
Value = NEWID()
FROM
cte_Tally t;
--======================================================================
-- solution
--======================================================================
BEGIN TRANSACTION;
-- insert the data you want to keep into a new table that has an identical structure (including indexes)
CREATE TABLE dbo.DataToKeep (
ID INT NOT NULL,
Value VARCHAR(36) NOT NULL,
CONSTRAINT pk_DataToKeep PRIMARY KEY CLUSTERED (ID)
);
INSERT dbo.DataToKeep (ID,Value)
SELECT
od.ID,
od.Value
FROM
dbo.OriginalData od
WHERE
od.ID >= 150254
AND od.ID <= 357842;
-- truncate the original table
TRUNCATE TABLE dbo.OriginalData;
-- use a partition switch to but the "keep" data back into the original table.
ALTER TABLE dbo.DataToKeep SWITCH TO dbo.OriginalData;
-- drop the keep table once the switch has completed.
DROP TABLE dbo.DataToKeep;
COMMIT TRANSACTION;
SELECT * FROM dbo.OriginalData od;
February 1, 2017 at 8:27 am
Seems a good solution. Thanks!
February 1, 2017 at 8:37 am
amns - Wednesday, February 1, 2017 8:27 AMSeems a good solution. Thanks!
No problem. Let us know how it goes. 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply