December 10, 2010 at 11:13 am
CirquedeSQLeil (12/10/2010)
As a manual process for very specific reasons (such as Gail mentioned), shrink should remain. I think it needs some fixing done but it should stay in SQL Server. Go ahead and remove autoshrink though.
I have always said that autoshrink is the devil. I have only seen it be usefull once and that was before SQL 2000.
December 10, 2010 at 11:19 am
pjdiller (12/10/2010)
SanDroid (12/10/2010)
pjdiller (12/10/2010)
Yes? I didn't say it'll throw an error, just that it's ignored. It's the same as running
DBCC SHRINKFILE (N'my DB logfile' , 0)Ultimately, my question is whether that is evil.
I would say that 99.999% of the time shrinking a Transaction Log file is evil....
Is that the answer you seek?
I was expecting that chopping off the end of a logfile was not detrimental. No shuffling required, no index fragmentation. No problem.
:w00t: That is not something IMO you are giong to get an experianced professional DBA that has worked on large live production systems to say. :w00t:
It would feel to me like saying changing your engine Oil and not the Oil filter is fine, no problem. Which is true if you are the Mechanic.
:-PPerhaps we should encourage keeping Autoshrink and it's use for job security. :hehe:
December 10, 2010 at 11:23 am
Russell Shilling (12/10/2010)
I run shrink on our Dev server after running lots and lots of queries to reduce the transaction log size (on disk). It's easy to grow a log file to 50 GB when running lots of big queries, and I have filled a drive in the past.
Isn't your Dev servers perfomance worth doing some tran log and Database backups so this is not necessary?
Most of the Dev servers I maintain have/need a better maintenance plane that the produciton systems just so they perform at half speed.
December 10, 2010 at 11:50 am
SanDroid (12/10/2010)
pjdiller (12/10/2010)
SanDroid (12/10/2010)
pjdiller (12/10/2010)
Yes? I didn't say it'll throw an error, just that it's ignored. It's the same as running
DBCC SHRINKFILE (N'my DB logfile' , 0)Ultimately, my question is whether that is evil.
I would say that 99.999% of the time shrinking a Transaction Log file is evil....
Is that the answer you seek?
I was expecting that chopping off the end of a logfile was not detrimental. No shuffling required, no index fragmentation. No problem.
:w00t: That is not something IMO you are giong to get an experianced professional DBA that has worked on large live production systems to say. :w00t:
It would feel to me like saying changing your engine Oil and not the Oil filter is fine, no problem. Which is true if you are the Mechanic.
:-PPerhaps we should encourage keeping Autoshrink and it's use for job security. :hehe:
If I may... One man's experienced professional DBA is another man's noob :cool:. What is the impact of removing the end of the log?
December 10, 2010 at 12:26 pm
Representing the non-standard SQL Server folks if I may, SHRINKing is very needed and valuable in our situation (databases not attached to applications). Our data is for analysis, so I'm constantly shrinking our transaction logs after large projects, and sometimes the data files too. Shrinking is very needed for us, and part of our maintenance plans. I'd argue it should be defaulted OFF and have warnings associated with it, but it should be left in the wizard.
A couple points:
1. Keeping the transaction log large makes sense for most of you in an OLTP situation, since you're just going to grow it out again. But for something like a monthly/yearly process, it's silly.
2. Disk space can be cheap. SAN disk space is *not* cheap, especially when you max out your SAN space and need to upgrade to a new chassis.
3. The downside to Simple recovery model involves backup tools that want to read the transaction log and create diffs. Ours, for instance. Since I keep everything Simple, it ends up having to do a complete backup. This is a conscious tradeoff as tapes are cheaper than disk, but it's something to consider.
-Dom
December 10, 2010 at 12:34 pm
No, DBCC SHRINKDATABASE and DBCC SHRINKFILE should not be removed. However, even if they are fixed to prevent excessive fragmentation, it would probably be much slower than it already is and there would still be the temptation for a lot of junior to mid-level DBA's to run it whenever they want anyway. That has it's own set of performance problems, so not much would be gained by doing that without presenting more liabilities into the equation IMHO. I do agree that AUTO SHRINK should be removed and shrink should be removed from the Maintenance Plans since rookies tend to use them anyway and that is just too easy of an option to check, and while they are at it remove the AUTO CLOSE db setting as well. That way, at least I could get rid of all the PBM policies I have in place that prevent those database settings anyway. 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
December 10, 2010 at 12:45 pm
pjdiller (12/10/2010)
SanDroid (12/10/2010)
pjdiller (12/10/2010)
Yes? I didn't say it'll throw an error, just that it's ignored. It's the same as running
DBCC SHRINKFILE (N'my DB logfile' , 0)Ultimately, my question is whether that is evil.
I would say that 99.999% of the time shrinking a Transaction Log file is evil....
Is that the answer you seek?
I was expecting that chopping off the end of a logfile was not detrimental. No shuffling required, no index fragmentation. No problem.
Log files don't have indexes, they don't have pages that can get shuffled around, they don't have fragmentation. The architecture of the log file is quite different from the data files. The log is a circular file, SQL writes log records (inside VLFs) until it reaches the end of the file, then either starts again from the beginning or, if the log records at the beginning are still needed, grows the file.
Again, the concept of 'truncating the file', deallocating everything past the last allocated extent is for the data file only, not the log file. You say shrink the log file to 0, SQL will try and shrink it to 0.
The question is why are you shrinking to 0? Why are you telling the file to go as small as it can go? The next thing that's going to happen is that the log grows again, and a log file cannot be instant initialised, the newly allocated space will be overwritten with zeroes and the change that needs the log space sits and waits for that to happen.
There are valid reasons for shrinking the log eg log backups failed for some time, no one noticed and the log grew. But then you don't shrink it to 0, you shrink it back to the size that it is usually.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 10, 2010 at 12:50 pm
GilaMonster (12/10/2010)
Log files don't have indexes, they don't have pages that can get shuffled around, they don't have fragmentation. The architecture of the log file is quite different from the data files. The log is a circular file, SQL writes log records (inside VLFs) until it reaches the end of the file, then either starts again from the beginning or, if the log records at the beginning are still needed, grows the file.
Again, the concept of 'truncating the file', deallocating everything past the last allocated extent is for the data file only, not the log file. You say shrink the log file to 0, SQL will try and shrink it to 0.
The question is why are you shrinking to 0? Why are you telling the file to go as small as it can go? The next thing that's going to happen is that the log grows again, and a log file cannot be instant initialised, the newly allocated space will be overwritten with zeroes and the change that needs the log space sits and waits for that to happen.
There are valid reasons for shrinking the log eg log backups failed for some time, no one noticed and the log grew. But then you don't shrink it to 0, you shrink it back to the size that it is usually.
Thanks, Gail. That makes perfect sense. I really don't want it to go to zero, and what I've been doing is re-growing it immediately... which I'm not fond of. I was under the impression that this would clean out VLFs more effectively. If that's not the case, then it seems that I should be specifying size.
December 10, 2010 at 12:55 pm
If what you're trying to do is clean up too many VLFs, then you do (once and only once) want to shrink to 0 and then grow to the appropriate size, while the DB is not in use. That's an exception to the general rule (and is pretty much the only reason I can think of to shrink the only log file right down to 0)
If you have a log that has grown abnormally, you want a targeted shrink to the size the log is supposed to be/needs to be.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 10, 2010 at 12:58 pm
GilaMonster (12/10/2010)
If what you're trying to do is clean up too many VLFs, then you do (once and only once) want to shrink to 0 and then grow to the appropriate size, while the DB is not in use. That's an exception to the general rule (and is pretty much the only reason I can think of to shrink the only log file right down to 0)If you have a log that has grown abnormally, you want a targeted shrink to the size the log is supposed to be/needs to be.
Gail speaks the truth and a good resource on how this is done is located here:
http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx
December 10, 2010 at 1:03 pm
SanDroid (12/10/2010)
GilaMonster (12/10/2010)
If what you're trying to do is clean up too many VLFs, then you do (once and only once) want to shrink to 0 and then grow to the appropriate size, while the DB is not in use. That's an exception to the general rule (and is pretty much the only reason I can think of to shrink the only log file right down to 0)If you have a log that has grown abnormally, you want a targeted shrink to the size the log is supposed to be/needs to be.
Gail speaks the truth and a good resource on how this is done is located here:
http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx
Thanks all. Question asked and answered.
December 10, 2010 at 1:08 pm
I would also add that IF(and that's a BIG IF) you are going to do this knowing that the file will immediatley grow again considering change the growth factor from 10% (the default I believe) to either a larger fixed growth factor or a larger percent, so that it is not growing at such quick intervals when the file is small after doing this. The default growth factor was not meant to be a catchall for all database files, but you would be surprised how many databases I see out there where this has never been changed, whether its on a 1 mb database or a 100GB database.:-D
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
December 10, 2010 at 1:30 pm
This hole discussion reminds me of a time when I was asked to fix the custom log shipping scripts for a customer. The scripts stopped working sometime after it was determined that Full Recovery backup model was using to many resources and Simple was a better setting. :hehe: I miss consulting sometimes. 😛
The point is good idea or bad idea depends on the applications and solutions your database supports.
December 10, 2010 at 6:10 pm
I think shrink should be made a manual only process that requires you to type in a CAPTCHA phrase while reading a warning about what will happen to your database. 😛
December 10, 2010 at 8:40 pm
Shrinking should stay. Had an archival run on Oracle leaving 50% free. To shrink it I had to move all the objects to a new tablespace, "shrink" the original and place them all back.
Freed some costly SAN allocation and reduced the space need for restore on dev.
Changing the default recovery to SIMPLE is a good idea since as rookie dba you assume that backup data will also shrink logfiles.
Viewing 15 posts - 46 through 60 (of 71 total)
You must be logged in to reply to this topic. Login to reply