June 22, 2009 at 12:09 am
hi to all,
How can i create a job ? for the rebuild index, best plan for this rebuild.
🙂
June 22, 2009 at 12:39 am
If your database size is small you can have a maintenance plan to rebuild all index for the database.
If its a large database, you may have to write your custom maintenance proc, considering the fragmentation percent.
June 22, 2009 at 4:55 am
You can usee the following script which will reorganise or reindex the tables accordingly. You can create a job using this or use the maintenance plan.
-- Ensure a USE statement has been executed first.
use databasename
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
DECLARE @db_id smallint;
set @db_id=DB_ID(N'databasename');
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (@db_id, NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag = 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
EXEC (@command);
PRINT N'Executed: ' + @command;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
June 22, 2009 at 9:51 pm
hi,
thanks for you reply,
Is it correct that, when we rebuild the index log size will be increased. what can i do to prevent this growth?
🙂
June 23, 2009 at 6:51 am
Add a step before to change the recovery model to Bulk-logged.
ALTER DATABASE [dbname] SET RECOVERY BULK_LOGGED
then, add another step at the end to set it back to full recovery (if that what it was before).
ALTER DATABASE [BlueMaps] SET RECOVERY FULL
Tim White
June 23, 2009 at 6:59 am
Sarvesh, I did not run the script, if it's running fine, why not put it under scripts section. Will be useful for future use as well. 😉
June 24, 2009 at 12:02 am
i've regular backups full & transaction log backups. How it will be affect if i do rebuild with switching from bulk_logged to full.
recovery process:
daily fullbackup at 1:00 am
transaction log backups every one hour.
and i'm planning to rebuild job weekly once.
🙂
June 24, 2009 at 12:18 am
vrabhadram (6/24/2009)
i've regular backups full & transaction log backups. How it will be affect if i do rebuild with switching from bulk_logged to full.recovery process:
daily fullbackup at 1:00 am
transaction log backups every one hour.
and i'm planning to rebuild job weekly once.
Rebuilding indexes are resource intensive job and require heavy use of the log file. If you perform this activity in FULL recovery mode, your logs will grow heavily(depending in your db/index size).
If you perform in Bulk-Logged mode, the logs will grow minimally.
Keep in mind that in this case your Log backups will be huge even though log itself will grow less...
June 24, 2009 at 6:32 am
Switching to Bulk-logged and back to full again will not affect your log chain. Your Point In Time recovery using tran log backups is still in tact.
Tim White
June 24, 2009 at 3:57 pm
Restrictions for Point-in-time Recovery
If a log backup taken under the bulk-logged recovery model contains bulk-logged changes, point-in-time recovery is not allowed. Trying to perform point-in-time recovery on a log backup that contains bulk changes will cause the restore operation to fail.
http://msdn.microsoft.com/en-us/library/ms186229.aspx
MJ
June 24, 2009 at 8:36 pm
Nice catch Manu, thanks for correcting me. I was under the impression that the log chain is still good in Bulk Logged mode and during a restore you just had to reapply your bulk changes. My bad.
So, can you still restore the entire tran log if needed, just not to a point in time?
and, does any iteration of a single tran log backup since the last full backup count toward this? (ie. at any time after the full backup, switch to BL, perfrom a bulk log operation, then swith to full for the rest of the day until the next nightly full).
I use the method to switch models on some large indexes before a rebuild and of course switch it back to full as soon as the job is done. Maybe It's not doing what I think it is.
Tim White
June 24, 2009 at 9:16 pm
2 Tim 3:16 (6/24/2009)
Nice catch Manu, thanks for correcting me. I was under the impression that the log chain is still good in Bulk Logged mode and during a restore you just had to reapply your bulk changes. My bad.So, can you still restore the entire tran log if needed, just not to a point in time?
and, does any iteration of a single tran log backup since the last full backup count toward this? (ie. at any time after the full backup, switch to BL, perfrom a bulk log operation, then swith to full for the rest of the day until the next nightly full).
I use the method to switch models on some large indexes before a rebuild and of course switch it back to full as soon as the job is done. Maybe It's not doing what I think it is.
The log chain is intact. In bulk logged recovery model, bulk operations are minimally logged. SQL Server marks that extents which have been changed due to bulk operation(BCP pages?). While taking log backup, all those marked extents are copied to the backup file. In a nutshell, your log doesn't grow but your backup file will be of huge size since all modified extents will be copied to it.
hence, you can still recover but not till point in time as transactions are not copied as it takes place(entire extents are copied)
I've never tried this but once u've restored full backup, tran log backups in Full recovery, tran log backups in bulk recovery and again tran log backup in full recovery, i think u can do point in time restores only for transactions recorded in tran log backups in full recovery mode...
1 am - full
2 am - tran backup (full recovery)
2.10 - recovery model - Bulk logged
3 am - tran backup(bulk logged)
3.10 - recovery model - full
4 am - tran bacukp (full recovery)
Here, you can restore point in time if, say u want between 3.10 AM and 4 am but not between 2.10 am and 3 am.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply