Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rockmoose
SQL Natt Alfen
3279 Posts |
 Posted - 2005-09-30 : 02:49:54
|
| A client of mine told me they were experiencing locking in the databasewhen they were running:- normal load of oltp transactions- reporting queries- and backupsAt the same time !I suggested making sure that the reporting was done with NOLOCK.My question is, how much can the backups (I guess both full & log)contribute to the locking that occurs in the oltp ?Can they be a (major/minor/no) factor in this ? |
|
|
ACALVETT
Starting Member
34 Posts |
Posted - 2005-09-30 : 05:45:40
|
| Did you make sure it was SQL backups and not NT backups? Also are they using any third party backup tools? |
 |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-09-30 : 06:02:58
|
quote:
Originally posted by ACALVETT
Did you make sure it was SQL backups and not NT backups? Also are they using any third party backup tools?
Running regular SQL Backups. |
|
|
|
ACALVETT
Starting Member
34 Posts |
Posted - 2005-09-30 : 06:20:54
|
| Hmm,They may be a minor factor in that there will be extra stress on the disk subsystem which would then slow reporting and loads. Also if its a full backup, consider the files as you only get 1 thread per file so this would have to be shared.A |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-09-30 : 06:34:48
|
quote:
Originally posted by ACALVETT
Hmm,They may be a minor factor in that there will be extra stress on the disk subsystem which would then slow reporting and loads. Also if its a full backup, consider the files as you only get 1 thread per file so this would have to be shared.A
Thanks,A minor factor since the system will have less resources and be running overall slower when the backups are taken makes sense. |
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2005-09-30 : 07:07:34
|
| ... but no locking should be produced by backup process. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-30 : 07:33:24
|
| "Running regular SQL Backups"Directly to tape?Same SCSI channel as database?Those would be the two I would be anxious about.Are we talking TLog backups? They should be pretty quick, no? If not run them more frequently?If its Full backups - presumably running during a Quiet Period then consider DIFFs daily and FULLs on the quietest period in the week (e.g. Sunday early morning)Kristen |
|
|
|
|
|
|
Locking and backups