Database backup time

I have database of 17GB. Avg. time taken for backup was around 40min on disk.
For last 2 days it is taking 2hrs. Nothing have changed.
Any ideas how to troubleshoot why backup is taking soo much time.
Thanks...Hi
Have you checked physical fragmentation on your discs?
Are these physical discs used for other things?
Are you on a SAN?
What other processes are being run at that time?
Have the file sizes for data/log files changed?
John
"Satwinder" wrote:
> I have database of 17GB. Avg. time taken for backup was around 40min on disk.
> For last 2 days it is taking 2hrs. Nothing have changed.
> Any ideas how to troubleshoot why backup is taking soo much time.
> Thanks...|||Hi John,
The database & log files are on SAN disk. Files size of neither one has
changed.
Only change i observed is one process which runs a SP is running for last 2
days.
Thanks..
"John Bell" wrote:
> Hi
> Have you checked physical fragmentation on your discs?
> Are these physical discs used for other things?
> Are you on a SAN?
> What other processes are being run at that time?
> Have the file sizes for data/log files changed?
> John
>
> "Satwinder" wrote:
> > I have database of 17GB. Avg. time taken for backup was around 40min on disk.
> > For last 2 days it is taking 2hrs. Nothing have changed.
> >
> > Any ideas how to troubleshoot why backup is taking soo much time.
> >
> > Thanks...|||Hi
Log files and data files should be separated even if they are on a SAN.You
may want to get the SAN engineer to look at the disc stats over the period. I
assume that the backup is to a different drive/LUN? Have you checked the LUNs
to make sure they are not conflicting? Also check out the perfmon counters.
You may want to find out what the SP that has been running for 2 days is
doing! Have you checked disc fragmentation?
John
"Satwinder" wrote:
> Hi John,
> The database & log files are on SAN disk. Files size of neither one has
> changed.
> Only change i observed is one process which runs a SP is running for last 2
> days.
> Thanks..
> "John Bell" wrote:
> > Hi
> >
> > Have you checked physical fragmentation on your discs?
> > Are these physical discs used for other things?
> > Are you on a SAN?
> > What other processes are being run at that time?
> > Have the file sizes for data/log files changed?
> >
> > John
> >
> >
> > "Satwinder" wrote:
> >
> > > I have database of 17GB. Avg. time taken for backup was around 40min on disk.
> > > For last 2 days it is taking 2hrs. Nothing have changed.
> > >
> > > Any ideas how to troubleshoot why backup is taking soo much time.
> > >
> > > Thanks...|||Hi
The SP was doing multiple Select one by one on a table having 67+million
rows. When i checked locks by that SP process i could see only 1 lock - Sch-S
lock on the Table on which the select query were being run. Select query had
nolock option specified.
After i killed the SP, backup completed in 45min.
Do you known if a Select can cause backup to take more time.
Do we have any blog by SQL Server backup team were i can post this issue and
get more information on this.
Thanks..
"John Bell" wrote:
> Hi
> Log files and data files should be separated even if they are on a SAN.You
> may want to get the SAN engineer to look at the disc stats over the period. I
> assume that the backup is to a different drive/LUN? Have you checked the LUNs
> to make sure they are not conflicting? Also check out the perfmon counters.
> You may want to find out what the SP that has been running for 2 days is
> doing! Have you checked disc fragmentation?
> John
>
> "Satwinder" wrote:
> > Hi John,
> >
> > The database & log files are on SAN disk. Files size of neither one has
> > changed.
> > Only change i observed is one process which runs a SP is running for last 2
> > days.
> >
> > Thanks..
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > Have you checked physical fragmentation on your discs?
> > > Are these physical discs used for other things?
> > > Are you on a SAN?
> > > What other processes are being run at that time?
> > > Have the file sizes for data/log files changed?
> > >
> > > John
> > >
> > >
> > > "Satwinder" wrote:
> > >
> > > > I have database of 17GB. Avg. time taken for backup was around 40min on disk.
> > > > For last 2 days it is taking 2hrs. Nothing have changed.
> > > >
> > > > Any ideas how to troubleshoot why backup is taking soo much time.
> > > >
> > > > Thanks...|||Hi
Your SP sounds like it is using a cursor so you may want to see if you can
improve it. Does this SP take a lot of resources? You may have conflicting
I/O between the SP reading data and your backups reading/writing.
For blogs you may want to look at:
http://searchsqlserver.techtarget.com/general/0,295582,sid87_gci1132175,00.html?track=NL-464
John
"John Bell" wrote:
> Hi
> Have you checked physical fragmentation on your discs?
> Are these physical discs used for other things?
> Are you on a SAN?
> What other processes are being run at that time?
> Have the file sizes for data/log files changed?
> John
>
> "Satwinder" wrote:
> > I have database of 17GB. Avg. time taken for backup was around 40min on disk.
> > For last 2 days it is taking 2hrs. Nothing have changed.
> >
> > Any ideas how to troubleshoot why backup is taking soo much time.
> >
> > Thanks...|||Satwinder wrote:
> Hi
> The SP was doing multiple Select one by one on a table having 67+million
> rows. When i checked locks by that SP process i could see only 1 lock - Sch-S
> lock on the Table on which the select query were being run. Select query had
> nolock option specified.
> After i killed the SP, backup completed in 45min.
> Do you known if a Select can cause backup to take more time.
>
Well of course it can! Running a backup produces load on the server.
Running a query produces load on the server. The greater the load, the
slower things run.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks everyone for there useful inputs.
"Satwinder" wrote:
> I have database of 17GB. Avg. time taken for backup was around 40min on disk.
> For last 2 days it is taking 2hrs. Nothing have changed.
> Any ideas how to troubleshoot why backup is taking soo much time.
> Thanks...