May 18, 2015 at 1:57 pm
We are using sql server 2008r2
We have transactional log backup set up for every 15 min.
We are running default reorg indexes for all tables in one of our production databases daily night.
Currently our log file size is greater than the data file size.
Why the transactional log backup is not reducing the log file size?
If we shrink the log everyday, is there any negative effects?
May 18, 2015 at 2:06 pm
ramana3327 (5/18/2015)
We are using sql server 2008r2We have transactional log backup set up for every 15 min.
We are running default reorg indexes for all tables in one of our production databases daily night.
Currently our log file size is greater than the data file size.
Why the transactional log backup is not reducing the log file size?
If we shrink the log everyday, is there any negative effects?
Transaction log backups does not shrink the physical size of the transaction log. It marks the VLF that are reusable. If you are rebuilding/reorganizing every index in the database, this is causing the growth of the t-log. If you are shrinking every day, it has to regrow every noght when you run your reorganization.
May 18, 2015 at 3:19 pm
So i.e. in my case the backups are not marking the VLF to reuse.
What could be the reason? Is this could be a bug?
Is rebuild Indexes also requires the same space?
May 18, 2015 at 3:22 pm
ramana3327 (5/18/2015)
So i.e. in my case the backups are not marking the VLF to reuse.What could be the reason? Is this could be a bug?
Is rebuild Indexes also requires the same space?
No, the t-log backups are most likely marking your VLFs for reuse. Your reorg/rebuild of every index in the database every night is probably causing the excessive growth of your t-log that you shrink every day. If the VLFs were not marked for reuse, you wouldn't be able to shrink your t-log file.
May 18, 2015 at 4:09 pm
Hi Lynn,
Thanks for your reply.
I am not clear about some questions
1) Does Reorganizing 5 indexes on a table and rebuilding the 5 indexes on the same table requires same log?
2) I shrink the log file only 2 times. If I want to shrink the log file everyday after the maintenance job it will impact any performance?
I read in some blog shrinking the logfile will increase VLFs and more fragmentation
3) Rebuilding the indexes is faster or not than reorg?
May 19, 2015 at 5:14 am
select * from sys.databases
run this query
it will tell you why the log files are not getting reuse
it will have reason in column log_reuse_wait
May 19, 2015 at 5:21 am
ramana3327 (5/18/2015)
2) I shrink the log file only 2 times. If I want to shrink the log file everyday after the maintenance job it will impact any performance?
Yes. Do not shrink your log regularly, it's very bad practice.
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
May 19, 2015 at 5:29 am
GilaMonster (5/19/2015)
ramana3327 (5/18/2015)
2) I shrink the log file only 2 times. If I want to shrink the log file everyday after the maintenance job it will impact any performance?Yes. Do not shrink your log regularly, it's very bad practice.
Gail Is there something called Non-destructive shrinking ??
i dont know but heard some one was saying that this thing exesist
May 19, 2015 at 5:44 am
Almighty (5/19/2015)
Gail Is there something called Non-destructive shrinking ??
Well shrinking's never destructive.... I said it's bad practice, not that it's going to destroy something.
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
May 19, 2015 at 9:32 am
Is it possible to use temp db for reorg index job like rebuild index instead of log?
May 19, 2015 at 9:33 am
No, and rebuild doesn't use TempDB instead of the log either. Rebuild uses TempDB for sort space, space that would otherwise come from the user database's data file. Reorganise doesn't sort, so it doesn't need sort space at all.
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
May 19, 2015 at 9:36 am
You should only rebuild or reorganize an index when it is fragmented enough to be significant. For some tables, this might be every day. For others, only every week or even every month. It's a huge waste of resources to rebuild/reorg every index every night. And you would never do both rebuild and reorg on the same index, just one of the two, as appropriate.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 19, 2015 at 4:01 pm
My Understanding is below
so either rebuild index or reorg index will use the transactional log and increase the physical log usage.
so if the log file initial size is 50MB and we started reorg indexes job, then it grows 160GB. So we can't get that initial physical log size (50 MB) even after the reorg job completes, until we shrink the log file manually after the backup
The regular transactional log backups will reduce only logical log size but to reduce the physical log only one option is we have to shrink log file
May 19, 2015 at 4:05 pm
ramana3327 (5/19/2015)
so either rebuild index or reorg index will use the transactional log and increase the physical log usage. We can't get that initial physical log size until we shrinkThe regular transactional log backups will reduce only logical log size but to reduce the physical log only one option is we have to shrink log file
Is there a reason you need to shrink the physical log file?
May 19, 2015 at 4:26 pm
ramana3327 (5/19/2015)
so if the log file initial size is 50MB and we started reorg indexes job, then it grows 160GB. So we can't get that initial physical log size (50 MB) even after the reorg job completes, until we shrink the log file manually after the backupThe regular transactional log backups will reduce only logical log size but to reduce the physical log only one option is we have to shrink log file
Why shrink it? What are you gaining other than extra work? If it need to be 160GB every night, then why waste time and effort shrinking it to 50GB just so that it can grow back to 160GB every night.
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
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply