How to disable transaction logs in SQL Server 2008

Question

In SQL Server 2008 is there any way to disable transaction log or clear log file?

When I execute one query in my project (very large in terms of transaction) a that time this log file's size will goring to increase (2 to 3 GB).

Please suggest me some good option.

Your question has already been asked and answered on SO. here's the link http://stackoverflow.com/questions/996403/disable-transaction-log — GuZzie, Jan 04 '12 at 08:23
@GuZzie yeah but the link is only for recovery model - and the OP asked also for clearing it ..... so dont just put un-full realated links. — Royi Namir, Jan 04 '12 at 08:24
@RoyiNamir Question was asked 2 years ago and still hasn't been cleared? still same resolution in that situation should answer Arun's question. Sorry for helping lol — GuZzie, Jan 04 '12 at 08:28

score 8 · Answer 1 · answered Jan 04 '12 at 08:23

You cannot disable the transaction log in SQL Server. No way.

You can switch to simple recovery model

ALTER DATABASE YourDatabase SET RECOVERY SIMPLE

which will log less - but the transaction log is such a fundamental, core concept in SQL Server, you cannot just turn that off.

Oleg Dok · Accepted Answer · 2012-01-04T08:40:20.547

3

You may shrink it

DECLARE @sql NVARCHAR(MAX) = ''
SELECT @sql = @sql + N'DBCC SHRINKFILE('+CAST(file_id AS NVARCHAR)+N', 0);' 
FROM sys.database_files
WHERE type = 1

EXEC(@sql)

BUT it only works with Simple recovery model, instead you have to back the log up and shrink it afterwards

You cannot drop the log files at all even if your database is in read-only mode.

AND try to redesign and review your approach when dealing with sql server's DB. It is not the right option or best practice to shrink files - especially on regular basis!

edited Jan 04 '12 at 08:40

answered Jan 04 '12 at 08:26

Oleg Dok

21,109
4
45
54

thanks for your effort , is it right option to create job for this so at particular interval it will execute and shrink that file? – Arun Rana Jan 04 '12 at 08:36
yep - just in your case may be, but try to redesign and review your approach when dealing with sql server's DB. It is not the right option or best practice to shrink files - especially on regular basis! – Oleg Dok Jan 04 '12 at 08:39
This works with FULL recovery but it breaks your log backup chain. You need a full backup to reset the log backup chain. This isn't the answer either, really. It just fixes a symptom and creates further problems. I won't downvote because you have qualified the shrink. The question gets one though – gbn Jan 04 '12 at 08:51
@gbn - I assumed, that if the question is about "clearing log" or even "throwing it out" - then there is no any option to talk backup chains or full recovery model about 8-) – Oleg Dok Jan 04 '12 at 08:56

score 2 · Answer 3 · edited May 23 '17 at 12:13

You can't disable the transaction log.

If your log needs to be 3GB larger, then so be it. If you shrink it, then it will just grow again and cause other problems.

If it's one off, you can mitigate growth by

batching the transaction: example: Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?). The 3GB growth is there to allow rollback of that transaction
Switch to SIMPLE recovery, change back to FULL (if needed): note, you'll need a full backup afterwards. The log will still grow of course to allow rollback

Royi Namir · Answer 4 · 2012-01-04T08:28:35.700

1

change the recovery model to :simple

ALTER DATABASE myDB SET RECOVERY SIMPLE

and run

DBCC SHRINKFILE (MyLog, 1); // to 1 MB

But from where do you get the log name ?

sp_helpdb MyDb

edited Jan 04 '12 at 08:28

answered Jan 04 '12 at 08:23

Royi Namir

144,742
138
468
792

3

and then it grows again, causing physical fragmentations and VLF issues. – gbn Jan 04 '12 at 08:25
@gbn the command is there not for nothing. Ive build a hash tool which takes a bak - recovering it , Hasha data , and creating a new bak.Now , truncating the Log file , helped me a lot. – Royi Namir Jan 04 '12 at 08:27
The command is there for specific usages. I know what you did: you asked on dba.se. But that doesn't mean it is suitable for use adhoc. And -1 – gbn Jan 04 '12 at 08:37
@gbn p.s. I dont see any difference between my answer to others. but nevermind. – Royi Namir Jan 04 '12 at 08:47
@gbn what is VLF Issue ? can you explain ? – Royi Namir Jan 04 '12 at 08:50
http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx and http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-More-on-the-circular-nature-of-the-log.aspx Paul Randal *wrote* DBCC for SQL Server... – gbn Jan 04 '12 at 08:53

How to disable transaction logs in SQL Server 2008

4 Answers4