Post reply

msdb backupset new index

  • September 28, 2012 at 2:32 am

    #263063

    hello,

    do you know why this index creation take for ever when I run it:

    CREATE NONCLUSTERED INDEX [IX_backupset_database_name_backup_set_id]

    ON [dbo].[backupset] ([database_name]) INCLUDE ([backup_set_id])

    thank you

  • October 1, 2012 at 3:55 pm

    #1544179

    It's probably because you have a lot of data in the table. If you haven't been cleaning the backup history and you have done a lot of backups since msdb was created that may be the source of the issue. I run a job once a week that runs this command to keep my backup history manageable:

    DECLARE @dt DATETIME ;

    SET @dt = DATEADD(day, -60, GETDATE())

    EXEC dbo.sp_delete_backuphistory

    @oldest_date = @dt ;

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 2 posts - 1 through 1 (of 1 total)

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