RE: Timeout problem when move large number of files
From: Mike Epprecht (SQL MVP) (mike_at_epprecht.net)
Date: 01/13/05
- Next message: Mike Epprecht (SQL MVP): "Re: Help about the excute order of Logical Expressions"
- Previous message: Hugo Kornelis: "Re: Best way to implement this kind of a matching?"
- In reply to: David: "Timeout problem when move large number of files"
- Next in thread: Hugo Kornelis: "Re: Timeout problem when move large number of files"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 13 Jan 2005 00:45:04 -0800
Hi
In effect, you are moving more than 80% of your data that resides in the
table.
This has serious locking implications. During the delete, SQL Server
probably escalates the lock to a Table lock, resulting in any other access to
the table being blocked. Even if it is not a Table lock, just the index
maintenance that happens will cuase enough blocking so that nobody else can
work.
If you need high uptime on a server, and need to do these type of
operations, you have to do the deletes in small chunks. 10, 20 or 30 minute
chunks will probably be OK.
Moving data around so much does raise a red flag. DB design and indexing
sprint to mind.
Regards
Mike
"David" wrote:
> Hello, I have a transactions table that has a intensive use all day (24
> hours)
>
> For better performance at 5 AM, I move all transactions of previous day from
> the production table to a history table.
>
> The problem is that while moving (2 minutes aprox) all new transactions fall
> in a SQL timeout.
>
> I use trigger FOR DELETE to move
>
> in my production table I have this trigger
>
> CREATE TRIGGER [TGR_TRAN_DELETE] ON [dbo].[Transactions]
> FOR DELETE
> AS
> Insert into DbLog.dbo.his_Transactions select * from deleted
>
>
> So, my scheduled Job just do a Delete from Transactions where receivedtime <
> DateAdd(dd,-1, getdate())
>
>
> Do you know some way to avoid the timeout?
>
> Thanks
>
>
>
>
- Next message: Mike Epprecht (SQL MVP): "Re: Help about the excute order of Logical Expressions"
- Previous message: Hugo Kornelis: "Re: Best way to implement this kind of a matching?"
- In reply to: David: "Timeout problem when move large number of files"
- Next in thread: Hugo Kornelis: "Re: Timeout problem when move large number of files"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
