[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

How to minimise Transaction Log

I have a database which is fed daily from parsed log files. The MSSQL transaction logs from this are huge and I don't actually need them because if there was a database error I would restore from parsing the original log files.

Is there any way I can turn off the transaction logging from within the stored procedures that are called during the parsing so that those stored procedures do not produce transaction logs ? Alternatively is there a way to minimise or shrink or truncate the transaction log before these stored procedures are called so that they will clear down before each run is made ?
0
Mamine
Asked:
Mamine
2 Solutions
 
graf0Commented:
You can not turn off transaction logging entirely, but you can swich the database into a Simple Recovery Model - that will truncate the transaction log automatically after the transaction is completed.
0
 
mcmonapCommented:
Hi

It is also worth assessing Bulk-logged recovery mode for your database:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_4ku1.asp

In order to remove data from your transaction logs you can run this command:
BACKUP LOG yourDBName WITH TRUNCATE_ONLY
More info here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp

To shrink the size of the transaction log file (the number represents the size in MB you wish to shrink to):
DBCC SHRINKFILE (yourDBName, 50)
More info here: http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_8b51.asp
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now