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 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
>|||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...
> > 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
> >
> >
>|||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...
> > 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
> >
> >
>|||"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...
> > 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
> 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
> > >
> > >
> >
> >
>|||I fully agree with Geoff's points.
To read more specifically about shrink: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
And more about defragmentation:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/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 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...
>> > 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
>> >
>> >
>>
>|||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...
> > 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...
> > > 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
> > 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
> > > >
> > > >
> > >
> > >
> >
> >
>|||Some people don't like maintenance plans at all and prefer to do things
using scripts and SQL agent jobs. The Reorganize Data and Index pages uses
DBCC DBREINDEX under the covers. I prefer a combination of DBCC DBREINDEX
and DBCC INDEXDEFRAG driven by DBCC SHOWCONTIG, all running at low activity
times in SQL agent jobs.
As for free space, you need to pro-actively manage that. Don't try and
shrink active databases unless there is more than 30% empty space. You will
fill in the space soon enough and auto-grow has a huge performance penalty.
Finally, the Statistics update. I prefer to set up SQL agent jobs to
recalculate statistics at a known time. Sample rates depend on your data.
If you have a fairly normal or even data distribution, then a small
percentage like 10% is ok. If you have some unusual distributions (I.E.
many entries are unique and a few entries have hundreds or thousands of
matches, then you will likely need a higher percentage. If you end up with
bad query plans, then you will definitely have to increase the scan
percentage.
Maintenance plans are easy but not as flexible as writing your own code.
There are good examples in BOL and out on the web.
--
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:u6$30YhtEHA.2664@.TK2MSFTNGP12.phx.gbl...
> 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...
> > > 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...
> > > > 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
> > > 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Did you read the defrag article I referred to? It has *lots* of good info!
--
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:u6$30YhtEHA.2664@.TK2MSFTNGP12.phx.gbl...
> 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...
>> > 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...
>> > > 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
>> > 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
>> > > >
>> > > >
>> > >
>> > >
>> >
>> >
>>
>|||Thanks alot guys,
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eJaSOihtEHA.1452@.TK2MSFTNGP11.phx.gbl...
> Some people don't like maintenance plans at all and prefer to do things
> using scripts and SQL agent jobs. The Reorganize Data and Index pages
uses
> DBCC DBREINDEX under the covers. I prefer a combination of DBCC DBREINDEX
> and DBCC INDEXDEFRAG driven by DBCC SHOWCONTIG, all running at low
activity
> times in SQL agent jobs.
> As for free space, you need to pro-actively manage that. Don't try and
> shrink active databases unless there is more than 30% empty space. You
will
> fill in the space soon enough and auto-grow has a huge performance
penalty.
> Finally, the Statistics update. I prefer to set up SQL agent jobs to
> recalculate statistics at a known time. Sample rates depend on your data.
> If you have a fairly normal or even data distribution, then a small
> percentage like 10% is ok. If you have some unusual distributions (I.E.
> many entries are unique and a few entries have hundreds or thousands of
> matches, then you will likely need a higher percentage. If you end up
with
> bad query plans, then you will definitely have to increase the scan
> percentage.
> Maintenance plans are easy but not as flexible as writing your own code.
> There are good examples in BOL and out on the web.
> --
> 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:u6$30YhtEHA.2664@.TK2MSFTNGP12.phx.gbl...
> > 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...
> > > > 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...
> > > > > 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
> > > > 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>