create job help?

  • hi to all,

    How can i create a job ? for the rebuild index, best plan for this rebuild.

    🙂

  • 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.

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • 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

  • 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?

    🙂

  • 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

  • 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. 😉



    Pradeep Singh

  • 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.

    🙂

  • 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...



    Pradeep Singh

  • 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

  • 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

  • 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

  • 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.



    Pradeep Singh

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply