msdb backupset new index

• LightPerf

  Mr or Mrs. 500

  Points: 567

  More actions

  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

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  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