March 9, 2015 at 1:27 pm
Very bad idea to shrink log files. This will eventually cause heavy fragmentation to the underlining disks.
March 9, 2015 at 1:54 pm
I would suggest making sure you are using bulk inserts with batch sizes and max commits (e.g. smaller transactions => less transaction space needed for the operations). In SSIS, these are (often overlooked) properties of the oledb destination (assuming destination is simple recovery mode).
There can be more causes for large tran log usage but that is definitely the lowest-hanging fruit.
March 10, 2015 at 2:54 am
PHYData DBA (3/9/2015)
cookie SQL (3/9/2015)
I would look at the active portions of the log 'DBCC LogInfo' , check that its viable to shrink or it will just fail.The other thing not mentioned or in the script is VLF's, I have a similar script but check that its shrunk as expected and the VLF count at the end.
My other concern would be drive fragmentation for spinning disks.
Remember growing a log file is a blocking operation so be careful how much you shrink because your slowing the other processes down. Maybe you should manage the growth before your import and directly shrink it back after with some checks in place.
+1 to everything said above.
I understand the need to shrink any SQL Transaction Log file that has experienced ONE Temporary Over Growth.
I see the need to do something automated when disk space for the fast and near storage that log files should be deployed on is a premium, but only when multiple databases have to use this same storage area.
And only if there was something that would grow each log file back to where it was before these data imports start.
Your solution that runs every 15 minutes outside of the SSIS packages were this task should be seem illogical and dangerous.
At best I would imagine it would shrink a log file right before that log file would auto grow and slow down anything using that log file.
A log file that becomes a certain size every night does so because it needs to be that size to do the task at hand.
You are ALWAYS going to need that much disc space available and a Transaction Log of that size .
Creating something that runs every 15 minutes to Shrink any Tran Log because there are no active connections to the database is at best performance robbing not enhancing.
If you have a SSIS package that has a need for a large Transaction log when it runs then add tasks to that package or the SQL Agent Job that executes it to Grow and then Shrink the Tran log files that are affected.
I do admit this is an interesting temporary solution for your specific problem.
Just would feel better if the permanent problems that it creates and glosses over were detailed better in your article.
I agree with all the above, but it works in this case. I would also say that it isn't a general solution for all servers, certainly won't work for any sort of OLTP server. However, I have a case where:
- disc space is extremely hard to come by, and we would need an additional 500GB-1TB to not shrink the transaction logs
- the databases are used in turn during our overnight load, so that when the connections to one database is dropped, it will not be written to again that night (might be read from some time later)
- we have large fixed size increments set for the transaction logs, generally 1-4GB per log file, which tends to keep the number of VLFs down.
- we operate in a SAN so disc platters aren't really a consideration
- since implementation, there's been no performance hit, possibly the opposite, although there have been other factors which are too complex to go into here and out of the remit of this discussion.
- more to the point, where we were running out of t-log space previously, we don't now and the system is more reliable, which is the acid test after all.
Not advocating that everyone should go and implement this at all, but in this instance, it is an optimal solution.
March 10, 2015 at 3:49 am
While I have an appreciation for a limited budget, it is much more expensive to have to come up with these types of workarounds than it is to buy more harddisk space (provided there's a rack with an open slot in it). A terabyte of disk space really doesn't cost that much an will make life a whole lot easier and code a whole lot faster if you have enough.
Jeff totally hits the nail on the head here. Having worked in UK NHS I know the culture only too well. Money is allocated from different pots - quite often the pot that needs to finance something that's actually crucial is simply empty, but there's plenty in the pot for that poorly conceived, poorly run project that's eating all the resources. On a recent contract at a UK NHS hospital I was given an ancient laptop to use that reduced my productivity down to maybe 75%. In other words they were squandering 5 days of my output per month, whereas a decent workstation would only have cost half a day's pay.
I know Mark (the OP), worked with him a few years ago and know he's a good guy, the sort who will find a way to provide a service to the users however his hands are tied. Sometimes the workaround is the only option circumstances allow.
March 10, 2015 at 4:05 am
You say that you want a load to either succeed or roll back to the previous day. Wouldn't it be a work-around to:
1. create a new database
2. load your data (using SIMPLE recovery model)
3. if successful, delete old database and rename the new
Of course, this only works if you can kick people of the database (maintenance window somewhere?). But it would probably cut down on the needed logspace.
Also, for your DWH, you could run it in READ-ONLY mode during the day (no more locking!), but I assume you are already doing this or it isn't possible (logging and other concerns).
September 29, 2016 at 12:34 am
Hi Mark
I was very impressed with this article ... maybe because i'm rather "young" user of SQL. I do have one question, The procedure is written to [LIB] database. May I assume this is a database that is used in the Datawarehouse database and not an "Active" or production database. Is this a correct assumption?
Regards
Aubrey
December 16, 2016 at 1:35 am
Something I would like to add to my previous remarks is this:
If the transaction log really is that big, then now is definately the right time to figure out why. On top of the things I mentioned earlier in the discussion, you should try to identify large transactions and break them down into smaller ones so that after each commit the space is returned to the instance.
December 16, 2016 at 7:34 am
I typically just setup a couple of maintenance plans. One of which is setup to run every 10min and commits the log files and removes them. This makes smaller intermittent log files which are significantly smaller in size and the plan automatically commits and releases (deletes) any log files older than 10min. This is made my life easier in that I do not need to create custom Jobs or SSIS packages.
December 16, 2016 at 8:58 am
Don't shrink your log files, apart from the fact it will very likely introduce massive file system fragmentation it's also very bad for perfomance.
Each time the log is grown the new space has to be zeroed out. Also you will likely find the log does not consume contiguous space on the disk because of the file system fragmentation.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 16, 2016 at 9:22 am
Perry Whittle (12/16/2016)
Don't shrink your log files, apart from the fact it will very likely introduce massive file system fragmentation it's also very bad for perfomance.Each time the log is grown the new space has to be zeroed out. Also you will likely find the log does not consume contiguous space on the disk because of the file system fragmentation.
Except for when your Log files are actually causing this exact thing to happen... Which they do...
Then take the word of someone that has been paid twenty years to use DBCC (and other things).
DBCCC SHRINK LOG is a real and necessary thing but they are like an AXE.
You can really hurt yourself if you do it wrong.
December 16, 2016 at 11:40 pm
Pre size your log and don't shrink it
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 17, 2016 at 12:56 am
Perry Whittle (12/16/2016)
Pre size your log and don't shrink it
I prefer simply letting the log grow naturally and leaving it there........after all, how often does an SQL Server really Need to be restarted?
A couple of databases have the added touch of having a Job that tells me proactively when the log grows so I can get an idea of why it needed to resize.
December 17, 2016 at 10:43 am
I think this is all an "It depends" thing. In today's world, many of us are on on all flash arrays. I have tested autogrowth of 8GB on the log and it is just as fast as 512MB. So, I see no issue in setting my autogrowth at 8GB, since that is how I initialized my log from the get go. Now, if it does grow, I can shrink it without any penalty since all of my VLFs are already the same size. Of course, the fact that it grew in the first place indicates an issue, but we can't always dismiss the one offs that grow the log because of some bad code that leaves an open transaction or something. The only penalty for shrinking a log comes when your autogrowth is really small and you get many small VLFs. If all of your VLFs are the same size, it really doesn't matter...
Jared
CE - Microsoft
December 17, 2016 at 11:25 am
Great article Mark,
I was going to implement a manual "shrink file" solution for a client next year but this looks much a better solution for me. But we will modify the "shrink"-target as well as if we should shrink and also look at the database file sizes. 🙂
This saves real money in hard disk space used at the SAN of our hosting provider at the end of the day.
Johan
December 19, 2016 at 7:15 am
This can be useful, thanks.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply