Groups > comp.databases.ms-sqlserver > #1657

Re: Transaction Log Backup Failing as Deadlock Victim

Newsgroups	comp.databases.ms-sqlserver
Date	2014-01-30 13:07 -0800
References	<a226b43d-9e59-4686-b21c-b818fed76ef8@googlegroups.com> <XnsA2C3E928BBD6BYazorman@127.0.0.1> <06522420-91a7-4905-8d4b-9d397a3eed68@googlegroups.com> <XnsA2C46177C41FDYazorman@127.0.0.1> <8af3ac60-ef8f-4f84-95bc-16f8645cd3b5@googlegroups.com>
Message-ID	<d6dc7b6c-6513-494c-9ca2-d3620451ea00@googlegroups.com> (permalink)
Subject	Re: Transaction Log Backup Failing as Deadlock Victim
From	Mark D Powell <Mark.Powell2@hp.com>

Show all headers | View raw

On Thursday, January 30, 2014 3:34:40 PM UTC-5, Mark D Powell wrote:
> On Wednesday, January 29, 2014 3:34:53 AM UTC-5, Erland Sommarskog wrote:
> 
> > (rja.carnegie@gmail.com) writes: 
> 
> > 
> 
> > > Is this what you meant to link to, again - "MSDB Performance Tuning"? 
> 
> > 
> 
> > > 
> 
> > 
> 
> > ><http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tun
> 
> > 
> 
> > >ing.aspx> 
> 
> > 
> 
> > > 
> 
> > 
> 
> > > There is plenty to think about, there, anyway. 
> 
> > 
> 
> > 
> 
> > 
> 
> > Yes, thanks for adding the link, when I forgot to paste it into my post. 
> 
> > 
> 
> > 
> 
> > 
> 
> > -- 
> 
> > 
> 
> > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> 
> 
> Thanks to both of you.  I have opened the article and will give the indexes a shot.  Fortunately I do have a backup history purge which is the second step in my daily error log switch job.
> 
> 
> -- Mark --

If would appear that SQL Server 2008 R2 RTM comes with some of the recommended indexes already defined:

/****** Object:  Index [PK__restoreh__FDC4B0311C873BEC]    Script Date: 01/30/2014 16:00:11 ******/
ALTER TABLE [dbo].[restorehistory] ADD PRIMARY KEY CLUSTERED 
(
	[restore_history_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


/****** Object:  Index [restorehistorybackupset]    Script Date: 01/30/2014 16:00:35 ******/
CREATE NONCLUSTERED INDEX [restorehistorybackupset] ON [dbo].[restorehistory] 
(
	[backup_set_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

I have not checked all the tables yet. -- Mark --

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

Transaction Log Backup Failing as Deadlock Victim Mark D Powell <Mark.Powell2@hp.com> - 2014-01-28 12:58 -0800
  Re: Transaction Log Backup Failing as Deadlock Victim Erland Sommarskog <esquel@sommarskog.se> - 2014-01-28 22:55 +0100
    Re: Transaction Log Backup Failing as Deadlock Victim rja.carnegie@gmail.com - 2014-01-28 16:58 -0800
      Re: Transaction Log Backup Failing as Deadlock Victim Erland Sommarskog <esquel@sommarskog.se> - 2014-01-29 08:34 +0000
        Re: Transaction Log Backup Failing as Deadlock Victim Mark D Powell <Mark.Powell2@hp.com> - 2014-01-30 12:34 -0800
          Re: Transaction Log Backup Failing as Deadlock Victim Mark D Powell <Mark.Powell2@hp.com> - 2014-01-30 13:07 -0800

csiph-web