July 6, 2011 at 1:53 am
I need your help in the below issue.
In my environment i have a 2 TB SQL 2005 Database and the DB is in SIMPLE recovery mode.
Now we are changing Simple to Full to achive the point in time by configuring log backups.
I just want to know from you is, while Rebuilding indexes of this 2TB Database, what extent My log file will increse. Based on your reply i will plan for the Log file Space & drive space in my environment to rebuild the indexes on my 2 TB DB.
And indexes are created multiple files & file groups.
Current DB Details:
DB Size = 2.2 TB
LOG file Size = 276 GB (Simple recovery & auto grow enabled)
And also i want to Know how to calculate the total Database index size. Individual table wise we can get by using sp_spaceused but is there any way to know the total Database index size.
Your input is very importent for me.
thanks
July 6, 2011 at 7:49 pm
Hi Mohan,
In my case log file is 516 gb of initial size with enable autogrowth. We have a maintenance plan to rebuild index but i noticed, after completion of rebuild index maintenance plan. There was no change in fragmentation level. So i just manually defragmented the indexes using DBCC command and it worked for me.
Hope this is informative for you.
July 7, 2011 at 12:32 am
1. Log file size=276 gb,
you can check how much it actually occupied.
If you backup transaction log, The data will clear from transaction log and you see how much free space in your transaction log
2. I have one database where database =around 500gb
we do rebuilt index every night, and backup transaction log hourly
each time index rebuild, it will create 100gb + transaction and we need to keep our eye on transaction log size and disk space.
After transaction log backup, the content of transaction log will be dump and the free space of transaction log increase to almost 100%
We change our rebuilt index strategy with reorganize and rebuilt index with the below
ALTER INDEX REORGANIZE - avg_fragmentation_in_percent value > 5% and < = 30%, need update statistics after reorganize.
ALTER INDEX REBUILD WITH (ONLINE = ON) - avg_fragmentation_in_percent value 30%
This help to reduce transaction log built up during rebuilt /reorganize
Reference:
Reorganizing and Rebuilding Indexes
Additional:
when I change database mode from simple recovery mode to full recovery mode
1. I will do full backup immediately when I change simple recovery mode to full recovery mode
2. I will start do backup transaction hourly
- it will help to clear the transaction log and prevent it grow unlimited
- it use for database recovery
3. I will also look for backup and recovery strategy if my database is 2TB.
- For 2 tb database, I will do full backup weekly, differential backup daily and transaction log backup hourly (this depend on database recovery strategy and how much data we can afford to lost) and I will also make sure I know how to recovery my database in other Server
Reference
July 7, 2011 at 7:55 am
Thanks for replies...
As my Database is in Simle recovery mode, i am not allowed to take the Log backup to clear the Log file.
And only 1% is in use in my 275 GB log file. But my question here is if I change my Database to Full recovery mode, till what extent my 2 TB database log file will grow? based on this i will add the disk space and if required i will add one more log in other drive. only one thing is i have to plan for the space.
July 7, 2011 at 8:00 am
mohan.pariveda 18256 (7/7/2011)
Thanks for replies...As my Database is in Simle recovery mode, i am not allowed to take the Log backup to clear the Log file.
And only 1% is in use in my 275 GB log file. But my question here is if I change my Database to Full recovery mode, till what extent my 2 TB database log file will grow? based on this i will add the disk space and if required i will add one more log in other drive. only one thing is i have to plan for the space.
#1 You don't need to rebuild every single 1 of your indexes.
#2 Yes the logs will grow. And easy estimate is 1.5X the space of each tables you rebuild.
#3 This script will allow you to pick only the indexes that really need to be worked on.
July 8, 2011 at 9:30 am
How much your tlog will grow cannot be known ahead of time unless you are doing a rebuild, which builds a completely new index in total. If doing a reorg the tlog size depends on the amount of pages that need to be swapped.
Use a script for this. Someone recommended sqlfool's, I prefer ola.hallengren.com.
VLDB operations and maintenance require some experience to handle properly/efficiently. I recommend you get some mentoring in place to keep you going in the right directions if you don't already have tha experience.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 8, 2011 at 9:52 am
TheSQLGuru (7/8/2011)
How much your tlog will grow cannot be known ahead of time unless you are doing a rebuild, which builds a completely new index in total. If doing a reorg the tlog size depends on the amount of pages that need to be swapped.Use a script for this. Someone recommended sqlfool's, I prefer ola.hallengren.com.
VLDB operations and maintenance require some experience to handle properly/efficiently. I recommend you get some mentoring in place to keep you going in the right directions if you don't already have tha experience.
WHy do you preffer ola's vs Michelle's?
July 8, 2011 at 10:08 am
Ola has an entire maintenance suite which does more than just index stuff. also very well documented and it can create sql agent jobs for you, etc.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply