CODEWITHSUNDEEP
sql-serverssistransactions
JimmyG
JimmyG

Reputation: 619

T-SQL Transaction Logging Issue

It's a long story, but I work for the research department of a school system, and we have beef with our IT department. A while back the IT dept. issued me a server with SQL installed on it and basically told me that I was on my own - they will not help with maintenance of the server or the behind-the-scenes functioning of the SQL database. For the most part this suits me fine, but I am not a DB Admin and I've never been able to solve setting up database backups and transaction logging.

The issue I'm trying to solve for now is transaction logging. Each night I use SSIS to pull in a large amount of records from our master database into my database. I've chunked this process into 18 individual steps, and step 9 & 15 are to do transaction log backups. Unfortunately my process still crashes regularly due to the transaction log filling up -- sometimes even on Step 10 just after a backup. I was going to disable logging during my process but have been reading and see that you cannot turn off logging. I'm hoping there is a simple solution that I just don't know about...

I'm running:

Microsoft SQL Server 2012 (SP3-GDR) (KB3194721) - 11.0.6248.0 (X64)   
Sep 23 2016 15:49:43   
Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) 
on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor).

Honestly I don't even know what type of transaction logging I'm doing or how to check and find out.

Any help would be greatly appreciated

Upvotes: 1

Views: 223

Answers (1)

Mark Wojciechowicz
Mark Wojciechowicz

Reputation: 4477

First, when you say,

Each night I use SSIS to pull in a large amount of records from our master database into my database

I presume this does not literally mean the system master database. If it does, there should not be user objects in there and it should not be processing or storing any user data there. If you are, migrate this to a user database.

Second, the short answer to the logging problem is this: The default recovery model for a database is Full. As noted in the comments, this means that no logs are overwritten until a back up occurs. This model works for point in time recovery and makes sense for a transactional system. So there are a few options:

  • If this is a reporting system, it usually makes sense to put this into simple recovery model and do nightly backups. Especially if the data is only changing once per day.
  • If you do require up to the minute point in time recovery, log backups should be performed every 15 min. I'm guessing that the management and retention of these backups will make no sense for you and you should use option 1.
  • When processing data in an SSIS dataflow, each buffer is committed one at a time. If you have not fiddled with the defaults, this means you are committing 10k rows at a maximum. So everything is already getting nicely chunked up. The problem then, is not that the batch sizes are too great, it is that you are in the wrong recovery model or that you are not backing up your logs often enough.

Upvotes: 1

Related Questions