Re: DB Backup problem
From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 10/19/04
- Next message: GYK: "Re: Transaction log question"
- Previous message: SJohnson: "RE: SQL2k backups not deleted per maintenance plan"
- In reply to: Scott Elgram: "Re: DB Backup problem"
- Next in thread: Tibor Karaszi: "Re: DB Backup problem"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 19 Oct 2004 23:14:00 +0200
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 >> > > > >> > > > >> > > >> > > >> > >> > >> >> > >
- Next message: GYK: "Re: Transaction log question"
- Previous message: SJohnson: "RE: SQL2k backups not deleted per maintenance plan"
- In reply to: Scott Elgram: "Re: DB Backup problem"
- Next in thread: Tibor Karaszi: "Re: DB Backup problem"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
