| Author |
Topic |
|
yhchan2005
Starting Member
26 Posts |
 Posted - 2011-07-11 : 05:06:41
|
| Hi,i have create a maintenance plan to do the daily full backup for my DB. i have set the Auto Shrink to true and Recovery Model for the DB is Full. All the while it is working find and the Trx LOg is auto shrink back to 1024k after the full backup.on last week, i have done a batch delete and also the batch insert record, which cause the Trx Log file growth until 11GB and start from that day, the auto shrink is stop working.i have manual do the shrink log file ( as attach in the picture ) but the file size still remain 11GB, please help !!!! |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-11 : 05:30:02
|
| Turn autoshrink off. It should NEVER be on on a DB. Remove the manual shrink. Shrinking of anything should be done after unusual operations, not on a regular basis.--Gail ShawSQL Server MVP |
 |
|
|
yhchan2005
Starting Member
26 Posts |
Posted - 2011-07-11 : 21:38:52
|
| thanks for your advise but may i know what is the reason we should not use the Auto Shrink ? Believe Microsoft provide this should have their own reason.and also if i don't have any harddisk constrain, you suggest me to let the Log file growth unlimited ? eg 500GB, 1TB, 2TB ? |
|
|
|
yhchan2005
Starting Member
26 Posts |
Posted - 2011-07-11 : 21:44:39
|
| i have another DB size around 150GB, and i have configure this DB to use Log Shipping to 'syn' the database back to my office every 15 minute. After the SQL Server backup the TrxLog ( every 15 minute ), the log file will become very small eg 1024K. So far, i don't face any performance issue regarding to the log file re-acquire back the hardisk space. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-07-11 : 22:43:32
|
| Here's why you should turn off autoshrink: http://www.sqlskills.com/blogs/paul/post/Auto-shrink-e28093-turn-it-OFF!.aspxKeep in mind Paul Randal was the leader of the storage engine team at Microsoft. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-12 : 04:33:17
|
| The fact that you don't currently see performance problems doesn't change that it's a bad practice.If the log is properly maintained it will not grow without bounds. 11GB is small for a log file, I've seen 300 GB before (and the log needed to be that size)Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]--Gail ShawSQL Server MVP |
|
|
|
yhchan2005
Starting Member
26 Posts |
Posted - 2011-07-13 : 22:25:34
|
| thanks for your comment and will turn the auto shrink off.but my question is, why previously the Log file can be shrink, but now cannot ? is that something wrong in the log file ? how to check it and fixed it ? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-14 : 04:43:45
|
| Probably nothing wrong. Probably just the active portion of the log is at the end of the file. Wait and try again or take 2 log backups (yes, 2) and then try the shrink.That would be a once-off shrink of the log back to it's normal size, then leave it alone.--Gail ShawSQL Server MVP |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-07-14 : 16:09:20
|
| Are you actually running transaction log backups on this database? If not, then either switch the recovery model to simple (not recommended) or implement frequent transaction log backups.The reason your other system is working is because log shipping performs transaction log backups, which you configured to be done every 15 minutes.Jeff |
|
|
|
yhchan2005
Starting Member
26 Posts |
Posted - 2011-07-14 : 22:51:18
|
| No, for this DB i don't do any Trx Backup. i have follow GilaMonster suggestion, do 2 time trx backup ( MUST 2 time, if you only do 1 time, the log file will not able to shrink ), now the log file become 1024K.additional question, since the log shipping will backup the Trx Log every 15 minute, and the log file size will reset back to 1024K, will it have performance issue ? i don't do any shrink process and will it have any impact to the table fragementation ? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-15 : 04:12:47
|
| If the DB is in full recovery, you MUST do log backups (either manually or via log shipping)Log backups do not shrink the log file size. Only a shrink operation does that and you shouldn't be running those and autoshrink should be off.Please read through this - http://www.sqlservercentral.com/articles/64582/--Gail ShawSQL Server MVP |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-07-15 : 17:19:25
|
quote:
Originally posted by yhchan2005
No, for this DB i don't do any Trx Backup. i have follow GilaMonster suggestion, do 2 time trx backup ( MUST 2 time, if you only do 1 time, the log file will not able to shrink ), now the log file become 1024K.additional question, since the log shipping will backup the Trx Log every 15 minute, and the log file size will reset back to 1024K, will it have performance issue ? i don't do any shrink process and will it have any impact to the table fragementation ?
So - for the database with the problem, you are not log shipping and you are not backing up the transaction log on a regular (frequent) basis? If that is true, either implement frequent transaction log backups or change the recovery model to simple.The log file should not change it's size just because a transaction log backup was run. You either have a step that is shrinking it or have turned on auto-shrink. Either of these is a bad idea - because all it does is cause performance issues (takes time to grow the log), causes fragmentation in the file and you probably don't have the right auto-growth settings.Like Gail said - STOP SHRINKING - the files. Read her article on how to manage transaction logs. |
|
|
|
|
Trx Log stop auto shrink ?