January 12, 2011 at 7:00 am
Hi,
I need to execute a script which implies many writes on the transaction log. In the truth I don't think I need to keep such information, since the query reorganizes and rebuilds indexes (I'm I wrong thinking it is not useful to register the activity on t-log? I'd schedule the script just after the schedule of backup full of the database).
The matter is that, scheduling the sql at night time, there is the possibility that the transaction log gets full. Also, I could run a backup of transaction log before and after the query, and configure an appropriate max size for the transaction log file, or is there the possibility to execute the sql without writing only its activity on transaction log (keeping the full option)?
Thanks in advance for your reply.
sb
January 12, 2011 at 7:18 am
Nope, everything is written to the transaction log. Even index rebuilds. This is so if they fail in the middle, they can be rolled back and maintain an intact index.
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
January 12, 2011 at 8:03 am
Unless you change the ricovery model and then run your reindex (buts it is not advisable).
Why is this an issue? Do you not have enough disk space?
Adam Zacks-------------------------------------------Be Nice, Or Leave
January 12, 2011 at 8:08 am
Schadenfreude-Mei (1/12/2011)
Unless you change the ricovery model and then run your reindex (buts it is not advisable).
It's logged regardless of the recovery model. Minimally logged in recovery models other than full, but still logged.
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
January 12, 2011 at 8:11 am
True but from the posters first post it seems they are worried about log filling (and presumably subsiquent growths) during the night.
Adam Zacks-------------------------------------------Be Nice, Or Leave
January 12, 2011 at 8:39 am
If it is subsequent actions, you would schedule a few log backups to clean it up. The reindex will be one large logged operation.
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
January 12, 2011 at 9:32 am
Schadenfreude-Mei (1/12/2011)
True but from the posters first post it seems they are worried about log filling (and presumably subsiquent growths) during the night.
Index rebuilds can and do grow the log in simple recovery.
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
January 12, 2011 at 1:50 pm
but u can shrink it without getting back up !
GilaMonster (1/12/2011)
Schadenfreude-Mei (1/12/2011)
True but from the posters first post it seems they are worried about log filling (and presumably subsiquent growths) during the night.Index rebuilds can and do grow the log in simple recovery.
January 13, 2011 at 12:04 am
after ur rebuild index/reorganize, you scheduled shrink database log then after take a full database backup
January 13, 2011 at 12:25 am
naresh.talla (1/13/2011)
after ur rebuild index/reorganize, you scheduled shrink database log then after take a full database backup
I have a few question here..
1. will shrinking always shrink your log file even if it is in use?
2. Why to run a full backup after the shrinking log process?
So far i know,You need to take a full\Differential backup only if you Truncate your log file but shrinking never truncates your log file it only tries to reclaim the unused portion of your log file for Os.
And log file can only be shrunk if it is not is use ..
use dbcc sqlperf('logspace') command to know how much % of your log file is in use.
January 13, 2011 at 2:37 am
sachnam (1/13/2011)
1. will shrinking always shrink your log file even if it is in use?
No, and repeatedly shrinking logs is not recommended.
2. Why to run a full backup after the shrinking log process?
Because too many people think that shrink log breaks the log chain. It does not. Hence no backup is required.
Hmmm... maybe another blog post topic here...
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
January 13, 2011 at 5:48 am
Thanks for replies,
as Adam says, the only thing I was 'worried' about is the possibility to fill the transaction log during night, and cause disases to users (Hospital databases).
The test I made yesterday took about 5 Gb of transaction log, for a 4 Gb database. Usually I configure max size of autogrowth to 2 Gb for transaction logs, since during the day more backups are scheduled to keep the files empty.
I'm analyzing db per db to opportunely plan the job for rebuild and reorganize the indexes. After the first run of the job (which rebuilds if fragmentation > 60%, else reorganizes, if frag >10% and <60%), next executions reguard a less number of indexes, and only reorganizes.
So, the problem would be only at the first execution, or in particular cases. I can plan it at morning and monitor.
To reciprocate your replies, I post the sql for managament of indexes, which I found on a blog and seems to be appropriate.
Best regards, sb
January 13, 2011 at 10:37 am
First, I'd recommend you use this maintenance script. Lots of expert DBAs have reviewed it and they like it: http://sqlfool.com/2009/06/index-defrag-script-v30/
Second: This is not necessarily good advice.
after ur rebuild index/reorganize, you scheduled shrink database log then after take a full database backup
If you need 5GB of space in the log for an index rebuild, then if you shrink it after the rebuild, what happens to that space? You can use it, but not permanently since you'll then reuse it when you rebuild again. So what's the point of shrinking? The log file needs what it needs for your system, and you should not try to micromanage that to the minimum it needs at any one time. Set it to the level it needs and monitor to see if needs change.
You want your log to run well, so consider this advice: http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx
Third, don't schedule log backups to manage the size of the log. Schedule log backups based on your needs for data loss and recovery.
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
January 14, 2011 at 2:49 am
Steve Jones - SSC Editor (1/13/2011)
First, I'd recommend you use this maintenance script. Lots of expert DBAs have reviewed it and they like it: http://sqlfool.com/2009/06/index-defrag-script-v30/
Steve (and all),
There's a newer version of that here http://sqlfool.com/2010/04/index-defrag-script-v4-0/
Nigel
How to post questions
January 14, 2011 at 3:09 am
Many thank for the scripts and replies.
Steve you're perfectly right, saying 'Schedule log backups based on your needs for data loss and recovery.'
I was considering only space aspects while writing, as you say I use to plan backups for data loss needs.
Regarding the space of transaction log, only the first execution of the sql takes a large amount of space, since once it's regularly schedulded, there would be non more need to rebuild and reorganize as many indexes as the first time.
all times I read about transaction log managing I have some doubts, can you telle me please the exact difference between truncating log and shrinking? It has happened that a big transaction log, emptied by the backup, didn't reduce its space after shrinking. Was it because the tail contained transactions not jet committed?
Still thanks, sb
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply