March 8, 2015 at 5:49 am
Comments posted to this topic are about the item Managing Log Files Sizes the Easy Way
March 9, 2015 at 1:26 am
Good morning!
This is certainly an interesting topic because in our firm we have two DWHs in place to facilitate a cut-over from one system to the second. The new machine is a SQL Server 2014 machine. The storage for this DWH is on a NetApp storage appliance and for those out there that have one, you know how convincing admins to give up space on such an expensive piece of hardware is.
Because of this reason I had to come up with a plan to simplify transaction log management and reduce the total amount of space required by te database.
My solution was to run the DWH in SIMPLE Recovery Mode and increase the CHECKPOINTs over a given period.
Why?
Firstly it is a Data Warehouse. The information fed into it comes from systems running constantly in production. The DWH in it's own right is not responsible for data Manipulation but rather OLAP processes so that Management can make its decisions. On an hourly Basis this Information is compared with its productive source and the Delta imported to Keep the Information as up-to-date as it can be.
What if we Need to restore the whole machine?
Naturally, at first we will have to restore the DWH from the previous full backup. Because we are running in SIMPLE Recovery Mode we don't have the capability to restore to a Point-in-time. This though isnt a Problem because during the first Delta run, the Information that Needs to be imported is extracted and inserted into the relevant DWH tables. Within 24 hours it is back up and running.
Ensuring that the DWH developers understand the Need to regularly commit changed data and increasing the amount of checkpoints throughout the day has kept our Transaction Log size dowon to a Minimum.
Just my take on the subject......
March 9, 2015 at 2:40 am
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.
March 9, 2015 at 4:06 am
kevaburg (3/9/2015)
Good morning!This is certainly an interesting topic because in our firm we have two DWHs in place to facilitate a cut-over from one system to the second. The new machine is a SQL Server 2014 machine. The storage for this DWH is on a NetApp storage appliance and for those out there that have one, you know how convincing admins to give up space on such an expensive piece of hardware is.
Because of this reason I had to come up with a plan to simplify transaction log management and reduce the total amount of space required by te database.
My solution was to run the DWH in SIMPLE Recovery Mode and increase the CHECKPOINTs over a given period.
Why?
Firstly it is a Data Warehouse. The information fed into it comes from systems running constantly in production. The DWH in it's own right is not responsible for data Manipulation but rather OLAP processes so that Management can make its decisions. On an hourly Basis this Information is compared with its productive source and the Delta imported to Keep the Information as up-to-date as it can be.
What if we Need to restore the whole machine?
Naturally, at first we will have to restore the DWH from the previous full backup. Because we are running in SIMPLE Recovery Mode we don't have the capability to restore to a Point-in-time. This though isnt a Problem because during the first Delta run, the Information that Needs to be imported is extracted and inserted into the relevant DWH tables. Within 24 hours it is back up and running.
Ensuring that the DWH developers understand the Need to regularly commit changed data and increasing the amount of checkpoints throughout the day has kept our Transaction Log size dowon to a Minimum.
Just my take on the subject......
Agreed. All our source system databases, staged and clean are run in simple mode, as is our DW main database. Our whole overnight load process could be considered to be like a single transaction, we either want it to work, or to continue with the previous day's data. The only non system database that is in Full mode is the one that contains the reference data for loading and logging server operations. The approach I've used specifically wouldn't work for OLTP, only OLAP, as it only compacts log files for DBs where there are no current connections.
How I would love to have a second DW, so that I could just switch reports from one to the other once the load is successfully completed.
March 9, 2015 at 4:17 am
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.
Our server is in a SAN so file fragmentation is an issue I have no visibililty of (and becomes a beast of a different nature, I think). Being a developer foremost and less of a DBA, I've not paid a vast amount of attention to VLFs (other than making sure we don't have hundreds). The max number we have across the staging, clean and DW is 5, so personally not too worried. Strangely enough there is always at least one VLF of status=2, but presumably you would always have at least VLF in a log file.
March 9, 2015 at 4:41 am
roworthm (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.
Our server is in a SAN so file fragmentation is an issue I have no visibililty of (and becomes a beast of a different nature, I think). Being a developer foremost and less of a DBA, I've not paid a vast amount of attention to VLFs (other than making sure we don't have hundreds). The max number we have across the staging, clean and DW is 5, so personally not too worried. Strangely enough there is always at least one VLF of status=2, but presumably you would always have at least VLF in a log file.
Depending on where in the file the active log is will affect your shrink, to be honest with simple recovery its often at the beginning.
I would just check how you are growing the log, is it autogrowth % or Size ? is it small/large chunks ?
Insert, Update and Delete can all be affected by large numbers of VLF's and will definitely be affected by growing the log as its a blocking operation. If you know how large the log needs to get, grow it back before your import operation (I grow in 8GB chunks) so it doesn't have to wait for growth during your import operation.
How many VLF's did you have before the shrink ?
March 9, 2015 at 6:56 am
We also run a DWH with almost all the DBs in simple recovery mode. I run a script like this every night though I don't take the extra check of making sure no connections are in there presently. Due to active transactions, even in simple recovery DBCC SHRINKFILE doesn't always reclaim the space.
Ken
March 9, 2015 at 7:32 am
ken.trock (3/9/2015)
We also run a DWH with almost all the DBs in simple recovery mode. I run a script like this every night though I don't take the extra check of making sure no connections are in there presently. Due to active transactions, even in simple recovery DBCC SHRINKFILE doesn't always reclaim the space.Ken
That is the reason I increase the amount of CHECKPOINTs.
March 9, 2015 at 7:44 am
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.
March 9, 2015 at 7:51 am
first thing I would ask is what is the recovery model in use on the DW database?
I'm sure you've heard a hundred times "don't shrink your transaction logs".
If you haven't heard this then, "Don't shrink your transaction logs!!"
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 9, 2015 at 8:00 am
Perry Whittle (3/9/2015)
first thing I would ask is what is the recovery model in use on the DW database?I'm sure you've heard a hundred times "don't shrink your transaction logs".
If you haven't heard this then, "Don't shrink your transaction logs!!"
***smile***
The OP mentioned that the DHW runs mstly in SIMPLE. My solution to Keep the log small was to increase the frequency of CHECKPOINTs that Information would be written to disk more often and the space released.
March 9, 2015 at 8:00 am
Perry Whittle (3/9/2015)
first thing I would ask is what is the recovery model in use on the DW database?I'm sure you've heard a hundred times "don't shrink your transaction logs".
If you haven't heard this then, "Don't shrink your transaction logs!!"
Actually, I've always heard "Don't shrink your database files!!". I thought it was ok that we shrink the tranlogs. Actually, we have no choice sometimes :angry:
My issue and I suspect Mark's and others is that we cannot grow all our logs to their comfortable working size without running out of space. So we shrink when we must.
Ken
March 9, 2015 at 10:13 am
ken.trock (3/9/2015)
Perry Whittle (3/9/2015)
first thing I would ask is what is the recovery model in use on the DW database?I'm sure you've heard a hundred times "don't shrink your transaction logs".
If you haven't heard this then, "Don't shrink your transaction logs!!"
Actually, I've always heard "Don't shrink your database files!!". I thought it was ok that we shrink the tranlogs. Actually, we have no choice sometimes :angry:
My issue and I suspect Mark's and others is that we cannot grow all our logs to their comfortable working size without running out of space. So we shrink when we must.
Ken
In a perfect world, there are several ways to keep the logs smaller; i.e. Don't do everything in one large transaction. Batch it into smaller transactions. Your log shouldn't need to be 80GB on a DW that doesn't need point in time recovery.
Jared
CE - Microsoft
March 9, 2015 at 11:18 am
In a perfect world, there are several ways to keep the logs smaller; i.e. Don't do everything in one large transaction. Batch it into smaller transactions. Your log shouldn't need to be 80GB on a DW that doesn't need point in time recovery.
Even something like instead of updating a month's worth of data in one fell swoop, looping thru the month and updating a day at a time.
March 9, 2015 at 11:42 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.
I do appreciate the article, though. It's a great temporary work around (although such workarounds frequently give people the excuse to not do it right).
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply