April 13, 2010 at 8:34 am
Ivanna Noh (4/12/2010)
I've posted about this before, but it's relevant to this discussion too...We have a mirrored database in production which is reindexed once a week - the reindexing causes the log file to grow from 2GB to about 21GB. The reindexing is done by a vendor-provided stored proc and can't be changed (warranties etc).
There is insufficient disk space to just let the log file remain at 21GB, so the morning after the reindexing I execute a DBCC Shrinkfile against it and shrink it back down to 2GB - this is usually run manually which is a pain. Running the shrink as a sch. job has never been successful as it usually requires one or more log backups to be taken before the log will shrink. Changing recovery models is not an option because of the mirroring...
I have a smililiar situation except without the mirror part so I luickily have more flexability with changing Recovery Models to accomidate Bulk Load operations.
I feel your pain about being stuck with the vendor provided code or process (in this case an Index Management process) because I have a similiar scenario. Thank God I can use my Index management method (the vendor doesn't provde one) because if the vendor did they'd find some way to do it using all cursors. I do however have other vendor-provided 'features' (I'm typing that word sarcastically) that I can't alter else risk breaking maintenance/warranty contract/service.
Kindest Regards,
Just say No to Facebook!April 13, 2010 at 8:39 am
Schadenfreude-Mei (4/13/2010)
Ivanna Noh (4/12/2010)
I've posted about this before, but it's relevant to this discussion too...We have a mirrored database in production which is reindexed once a week - the reindexing causes the log file to grow from 2GB to about 21GB. The reindexing is done by a vendor-provided stored proc and can't be changed (warranties etc).
There is insufficient disk space to just let the log file remain at 21GB, so the morning after the reindexing I execute a DBCC Shrinkfile against it and shrink it back down to 2GB - this is usually run manually which is a pain. Running the shrink as a sch. job has never been successful as it usually requires one or more log backups to be taken before the log will shrink. Changing recovery models is not an option because of the mirroring...
How long have you been following this routine? Apart fromm the fact that you are most likely fragmenting your disk (which in time will cause io slows) but what happens if someone is late, or ill or forgets?
Surely your company must realise that if they 'choose' not to have the re-index sp optimised, they need to accept the requirement for additional storage.
I can't speak to Mr or Mrs 5000's speccific scenario but I can tell you in my case, management will embrace a farr less then effecient method for something provided by the software application vendor before even considering allowing for a custom one even if it is far superior. You may be lucky enough to not have that kind of setup but for those of us in that scenario you can bet your bottom dollar it doesn't mater if you can do it better. The priority is to not do anything that could cause the vendor to take a hands off attitude and so sorry but you went against our recomendations.
Steve [Jones] - I bet in your position, as lead at SQLServerCentral combined with your working history, that you've heard more then a few stories from DB/DBA folks like this, where they are stuck with something a procedural coder slapped together all in teh anme of being align with the software vednrs recomendations or requirements.
Kindest Regards,
Just say No to Facebook!April 13, 2010 at 9:04 am
YSL,
Completely agree with you and believe me, my position and the hoops I have to jump through are far from ideal; thats why i put in the bit about more storage.
Any company needs to understand the need for growing storage space as their data (and company) grows.
If it is explained what the pros and (mosty) cons are, then a CEO would be bonkers to allows io slow down and multiple manual (and potentially risky) procedures for the sake of saving a few $$$ on some disk.
Or maybe I'm wrong and they enjoy timeouts and DR scenarios.
Either way dont bite my hand off, its just an opinion.
Adam Zacks-------------------------------------------Be Nice, Or Leave
April 13, 2010 at 10:09 am
Schadenfreude-Mei (4/13/2010)
YSL,Completely agree with you and believe me, my position and the hoops I have to jump through are far from ideal; thats why i put in the bit about more storage.
Any company needs to understand the need for growing storage space as their data (and company) grows.
If it is explained what the pros and (mosty) cons are, then a CEO would be bonkers to allows io slow down and multiple manual (and potentially risky) procedures for the sake of saving a few $$$ on some disk.
Or maybe I'm wrong and they enjoy timeouts and DR scenarios.
Either way dont bite my hand off, its just an opinion.
Your hand was not bitten; I did not even snarl. You read too much into my reply.
Kindest Regards,
Just say No to Facebook!April 13, 2010 at 10:18 am
Yep, I've seen lots of questions like that. So often management doesn't want a better solution. They just want one working, and having you move on to something else.
It is a fair point that you can be introducing lots of fragmentation. I saw a note from Paul Randal recently in one of his classes he found someone with thousands of virtual log files. Defragmenting those increased performance by some crazy number, like 25%. I'd use examples like that to push for more disk space.
On the other hand, if you have the space every week, why shrink back down? Is the low space causing some alert? You need it to grow again, correct?
Also FYI, Mr or Mrs. 500 is a level, not a name. The name is above that in bold.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 13, 2010 at 1:28 pm
Thanks to all for the replies 🙂
How long have you been following this routine? Apart fromm the fact that you are most likely fragmenting your disk (which in time will cause io slows) but what happens if someone is late, or ill or forgets?
Its been about 18 months now, if I'm off work there is one other person who can do the shrink - but if it isn't done it is only a matter of time until lack of disk space becomes tight
Make sure the t-log is growing in very large chunks (or do a deliberate resize in perhaps 8GB chunks right before the reindexing)
Yes I've set the log to grow at 1GB - but there are still over 200 virtual log files before the shrink (50 after the shrink). I will also try some alternate configurations in the sch. job as you suggest
if the vendor did they'd find some way to do it using all cursors...I can't alter else risk breaking maintenance/warranty contract/service
yep the vendor code is cursors all the way down, and can't be altered :w00t:
The priority is to not do anything that could cause the vendor to take a hands off attitude and so sorry but you went against our recomendations
yes indeed
So often management doesn't want a better solution. They just want one working
ditto
On the other hand, if you have the space every week, why shrink back down? Is the low space causing some alert? You need it to grow again, correct?
Yes that is true - there is space for the log file to grow, but it doesn't leave much space for unforseen events like unexpected log growth in other DBs and if not rectified will eventually cause an alert...It's a fixed drive too, so not just a matter of allocating more SAN space...
anyway thanks for everyone's contributions:-)
April 13, 2010 at 2:08 pm
Ivanna Noh (4/13/2010)
Thanks to all for the replies 🙂...It's a fixed drive too, so not just a matter of allocating more SAN space...
anyway thanks for everyone's contributions:-)
Did you know that 600GB 3.5" 15k SAS drives are available? If the fixed drives are older, chances are you can buy a replacement that is both larger and faster.
April 13, 2010 at 2:20 pm
Did you know that 600GB 3.5" 15k SAS drives are available? If the fixed drives are older, chances are you can buy a replacement that is both larger and faster.
I have to profess my ignorance here, I just maintain the databases, write reports etc but I don't have any involvement with hardware or responsibility for it so my focus is elsewhere - and I probably wouldn't get far if I made hardware suggestions as that side of things is the domain of the networking team - but thanks for the info, I will read up about it so that I can at least make informed comment at the appropriate time 🙂
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply