DB Backup problem

Hello,
I have one particular SQL 7 database that is rather large, over 100
gigs. Most of this is in one table which is full of blobs. This database
is mission critical stuff and is backed up every night. However, as of
recent this backup is taking almost 15 hours to complete sometimes and when
it's running the DB can't be accessed which is causing problems. Can anyone
suggest a solution to keeping this backup time to a minimum?
-Scott ElgramThere are several things that you can do.. None of them are particularly
pretty.
One suggestion that comes to mind:
1. Create a new table for a 1-to-1 relationship with your big table and
move your BLOBs off the big table and into the new table.
2. Put that new table in it's own filegroup
3. Back up that filegroup separately.
Another suggestion..
If the BLOBs are not often modified or added, you may wish to enable
t-logging of your BLOB objects. Then the blobs are part of the transaction
log. You don't have to do a complete DB backup every night.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:%23EiS8cftEHA.1272@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have one particular SQL 7 database that is rather large, over 100
> gigs. Most of this is in one table which is full of blobs. This database
> is mission critical stuff and is backed up every night. However, as of
> recent this backup is taking almost 15 hours to complete sometimes and
when
> it's running the DB can't be accessed which is causing problems. Can
anyone
> suggest a solution to keeping this backup time to a minimum?
> --
> -Scott Elgram
>|||Are you certain that it really is backup that is blocking? Have you verified
using sp_who2 etc?
Backup shouldn't block, perhaps you in the same job is doing some shrink or
defrag?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:%23EiS8cftEHA.1272@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have one particular SQL 7 database that is rather large, over 100
> gigs. Most of this is in one table which is full of blobs. This database
> is mission critical stuff and is backed up every night. However, as of
> recent this backup is taking almost 15 hours to complete sometimes and whe
n
> it's running the DB can't be accessed which is causing problems. Can anyo
ne
> suggest a solution to keeping this backup time to a minimum?
> --
> -Scott Elgram
>|||Tibor Karaszi,
I looked into that and the Maintenance Plan does the following Monday -
Friday;
5:00pm - Optimization:
- Reorganize data and index pages, Change free space per page to 1%.
- Remove unused space from database files, shrink when it grows
beyond 50 MB, Amount of free space to remain after shrink: 1% of the data
space.
5:30pm - Integrity
- Check database integrity, include indexes, attempt to repair any
minor problems.
6:00pm - Transaction Log Backup
- Backup the transaction log of the database as part of the
maintenance plan
- Verify the integrity of the backup upon completion
7:00pm - Complete Backup
- back up the database as part of the maintenance plan
- verify the integrity of the backup upon completion
After reviewing this, which I did not create ;) , Would in help my cause
if I were to move the Optimization to some other plan that runs once a week
on perhaps a weekend when backups are not running? If backing up should not
lock the DB then could it be the Optimization that is taking 15 hours and
locking it?
-Scott
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O%23pUFIgtEHA.2536@.TK2MSFTNGP11.phx.gbl...
> Are you certain that it really is backup that is blocking? Have you
verified using sp_who2 etc?
> Backup shouldn't block, perhaps you in the same job is doing some shrink
or defrag?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:%23EiS8cftEHA.1272@.TK2MSFTNGP12.phx.gbl...
database[vbcol=seagreen]
when[vbcol=seagreen]
anyone[vbcol=seagreen]
>|||Rick Sawtell,
The information added to the BLOB table is daily....sometimes several
hundred megs are added each day. Does that leave me with only this second
option? Also, Someone by the name Tibor Karaszi has suggested that Backups
should not "lock" databases or tables and this lock might be caused by some
shrink or defrag occurring in the same job. Could Optimization in the
backup maintenance plan be the culprit and running for 15 hours, not the
actual backup?
-Scott
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:Osb3YGgtEHA.3572@.tk2msftngp13.phx.gbl...
> There are several things that you can do.. None of them are particularly
> pretty.
> One suggestion that comes to mind:
> 1. Create a new table for a 1-to-1 relationship with your big table and
> move your BLOBs off the big table and into the new table.
> 2. Put that new table in it's own filegroup
> 3. Back up that filegroup separately.
> Another suggestion..
> If the BLOBs are not often modified or added, you may wish to enable
> t-logging of your BLOB objects. Then the blobs are part of the
transaction
> log. You don't have to do a complete DB backup every night.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:%23EiS8cftEHA.1272@.TK2MSFTNGP12.phx.gbl...
database[vbcol=seagreen]
> when
> anyone
>|||"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:uTP9RmgtEHA.2956@.TK2MSFTNGP12.phx.gbl...
> Rick Sawtell,
> The information added to the BLOB table is daily....sometimes several
> hundred megs are added each day. Does that leave me with only this second
> option? Also, Someone by the name Tibor Karaszi has suggested that
Backups
> should not "lock" databases or tables and this lock might be caused by
some
> shrink or defrag occurring in the same job. Could Optimization in the
> backup maintenance plan be the culprit and running for 15 hours, not the
> actual backup?
> -Scott
A couple of hundred megs of changes are still better than 100 gigs of
nightly backups.
That said, Tibor is correct. Backups don't generally lock the tables. You
probably have something going on in the DBMaint that is locking rows and
slowing your backup process. An index defrag or similar.
Rick|||"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:uuQrgjgtEHA.2316@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi,
> I looked into that and the Maintenance Plan does the following
Monday -
> Friday;
> 5:00pm - Optimization:
> - Reorganize data and index pages, Change free space per page to
1%.
> - Remove unused space from database files, shrink when it grows
> beyond 50 MB, Amount of free space to remain after shrink: 1% of the data
> space.
> 5:30pm - Integrity
> - Check database integrity, include indexes, attempt to repair any
> minor problems.
> 6:00pm - Transaction Log Backup
> - Backup the transaction log of the database as part of the
> maintenance plan
> - Verify the integrity of the backup upon completion
> 7:00pm - Complete Backup
> - back up the database as part of the maintenance plan
> - verify the integrity of the backup upon completion
> After reviewing this, which I did not create ;) , Would in help my
cause
> if I were to move the Optimization to some other plan that runs once a
week
> on perhaps a weekend when backups are not running? If backing up should
not
> lock the DB then could it be the Optimization that is taking 15 hours and
> locking it?
>
Yes! You are only picking up 1% or so.. If you let it go until the
weekends, you should see very little performance degradation.
To be sure, run a baseline on the system as it operates currently. Then
make your changes and check it again. Compare to the baseline.
Rick|||Comments Inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:uuQrgjgtEHA.2316@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi,
> I looked into that and the Maintenance Plan does the following
Monday -
> Friday;
> 5:00pm - Optimization:
> - Reorganize data and index pages, Change free space per page to
1%.
> - Remove unused space from database files, shrink when it grows
> beyond 50 MB, Amount of free space to remain after shrink: 1% of the data
> space.
>
Ditch the Maintenance Plan Optimizations. Look in BOL under DBCC
SHOWCONTIG. There is a script that will allow you to defrag your tables
when they actually need it rather than all at once. Do not shrink the data
files. You will just have to grow them again and take the performance hit
when they auto-grow. And yes, it is the optimization step that is likely
locking the tables.

> 5:30pm - Integrity
> - Check database integrity, include indexes, attempt to repair any
> minor problems
Big no-no. Go ahead and run the checks, but don't attempt to repair. All
you will do is mask the early stages of a problem until it gets too big for
DBCC to fix.

> 6:00pm - Transaction Log Backup
> - Backup the transaction log of the database as part of the
> maintenance plan
> - Verify the integrity of the backup upon completion
Yes.
> 7:00pm - Complete Backup
> - back up the database as part of the maintenance plan
> - verify the integrity of the backup upon completion
Yes.
> After reviewing this, which I did not create ;) , Would in help my
cause
> if I were to move the Optimization to some other plan that runs once a
week
> on perhaps a weekend when backups are not running? If backing up should
not
> lock the DB then could it be the Optimization that is taking 15 hours and
> locking it?
> -Scott
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:O%23pUFIgtEHA.2536@.TK2MSFTNGP11.phx.gbl...
> verified using sp_who2 etc?
> or defrag?
> database
of[vbcol=seagreen]
> when
> anyone
>|||I fully agree with Geoff's points.
To read more specifically about shrink: http://www.karaszi.com/SQLServer/in.../>
_shrink.asp
And more about defragmentation:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:uuQrgjgtEHA.2316@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi,
> I looked into that and the Maintenance Plan does the following Monday -
> Friday;
> 5:00pm - Optimization:
> - Reorganize data and index pages, Change free space per page to 1%
.
> - Remove unused space from database files, shrink when it grows
> beyond 50 MB, Amount of free space to remain after shrink: 1% of the data
> space.
> 5:30pm - Integrity
> - Check database integrity, include indexes, attempt to repair any
> minor problems.
> 6:00pm - Transaction Log Backup
> - Backup the transaction log of the database as part of the
> maintenance plan
> - Verify the integrity of the backup upon completion
> 7:00pm - Complete Backup
> - back up the database as part of the maintenance plan
> - verify the integrity of the backup upon completion
> After reviewing this, which I did not create ;) , Would in help my caus
e
> if I were to move the Optimization to some other plan that runs once a wee
k
> on perhaps a weekend when backups are not running? If backing up should n
ot
> lock the DB then could it be the Optimization that is taking 15 hours and
> locking it?
> -Scott
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:O%23pUFIgtEHA.2536@.TK2MSFTNGP11.phx.gbl...
> verified using sp_who2 etc?
> or defrag?
> database
> when
> anyone
>|||Geoff N. Hiten,
What about checking the "Reorganize data and index pages, Reorganize
with the original amount of free space" and "Update the statistics used by
the query optimizer, Percentage of database to sample 10%" to run on a
weekend? Those seem like good things to have done but because of the cost on
performance might be better run during a time of little or no usage like the
weekend. Or is it just a bad idea over all to set Optimization in the
maintenance Plan?
-Scott
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:%23CT8aygtEHA.1464@.TK2MSFTNGP15.phx.gbl...
> Comments Inline
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:uuQrgjgtEHA.2316@.TK2MSFTNGP12.phx.gbl...
> Monday -
> 1%.
data[vbcol=seagreen]
> Ditch the Maintenance Plan Optimizations. Look in BOL under DBCC
> SHOWCONTIG. There is a script that will allow you to defrag your tables
> when they actually need it rather than all at once. Do not shrink the
data
> files. You will just have to grow them again and take the performance hit
> when they auto-grow. And yes, it is the optimization step that is likely
> locking the tables.
>
any[vbcol=seagreen]
> Big no-no. Go ahead and run the checks, but don't attempt to repair. All
> you will do is mask the early stages of a problem until it gets too big
for
> DBCC to fix.
>
> Yes.
> Yes.
> cause
> week
> not
and[vbcol=seagreen]
> in
shrink[vbcol=seagreen]
100[vbcol=seagreen]
> of
and[vbcol=seagreen]
Can[vbcol=seagreen]
>