Re-Establishing Maintenance Jobs?

I have a client that hasn't had a successful run of any of the four
"standard" maintenance jobs in over 4 months. (Integrity Check,
Optimization, DB Backup, and Transaction Log Backup).
I know what the problem is, but I'm not his DBA (in fact I'm a mere contract
software developer). Apparently my client doesn't have a DBA, or this
wouldn't be the case, and he may turn to me to fix the problem. (I suspect
that they renamed the server, and the job owner is listed with the old
server name).
I'm logging in remotely, so I'm somewhat apprehensive of simply turning the
four jobs on overnight. I can envision problems with temp space, disk
space, and possibly excessive time required for index rebuilds. I don't
know if a CHECKDB takes longer if it hasn't been done in a while.
Is there anything else I should be prepared for?
Is there a recommended approach to "softly" bring a potentially ailing (no
symptoms that I know of) database back into a verified and backed up state?
Should I do DBCC CHECKTABLE on a table at a time before doing CHECKDB, or is
it best to do DBCC CHECKDB to start with?
Would the following be a good sequence:
1: Verify disk space available compared to backup space required (how do I
estimate the backup space required?).
1: Manually perform backup of the database and Transaction log.
2: Manually perform DBCC CHECKDB (Estimate Only first to verify sufficient
space in tempdb). I assume I should do it as REPAIR_FAST?
3: Manually perform DBCC INDEXDEFRAG before or instead of DBCC REINDEX
4: Re-Establish the Agent Jobs.
TIA,
Tore.Thanks.
This is a 24/7 system (medium traffic web site), so my rationale for doing
the initial backup before anything else was to make sure that a copy of the
database would be available just in case something went very wrong during
the subsequent operations. I know the (a) backup really needs to be not
just on another drive, but offline and preferably off-site. However, the
initial backup would only be for the time it took to get everything back
into normal working order - at what point their overall server backup
procedures would (hopefully) address the proper security of the database
backups.
From your response, I guess I don't need to worry about problems during
checkdb or dbreindex or whether the database will remain in working order
should an error be found?
Should I specify FAST_REPAIR on the initial run of CHECKDB?
Tore.
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:uW6MFHCWDHA.1512@.TK2MSFTNGP11.phx.gbl...
> See inline
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it
community
> of SQL Server professionals.
> www.sqlpass.org
>
> "Tore Bostrup" <newspost_at_bostrup.us> wrote in message
> news:#KnN$7#VDHA.1620@.TK2MSFTNGP12.phx.gbl...
> > I have a client that hasn't had a successful run of any of the four
> > "standard" maintenance jobs in over 4 months. (Integrity Check,
> > Optimization, DB Backup, and Transaction Log Backup).
> >
> > I know what the problem is, but I'm not his DBA (in fact I'm a mere
> contract
> > software developer). Apparently my client doesn't have a DBA, or this
> > wouldn't be the case, and he may turn to me to fix the problem. (I
> suspect
> > that they renamed the server, and the job owner is listed with the old
> > server name).
> >
> > I'm logging in remotely, so I'm somewhat apprehensive of simply turning
> the
> > four jobs on overnight. I can envision problems with temp space, disk
> > space, and possibly excessive time required for index rebuilds. I don't
> > know if a CHECKDB takes longer if it hasn't been done in a while.
> The time checkdb takes is related to the amount of IO required to read the
> records.
> >
> > Is there anything else I should be prepared for?
> >
> > Is there a recommended approach to "softly" bring a potentially ailing
(no
> > symptoms that I know of) database back into a verified and backed up
> state?
> > Should I do DBCC CHECKTABLE on a table at a time before doing CHECKDB,
or
> is
> > it best to do DBCC CHECKDB to start with?
> I would IMMEDIATELY implement backups - period...
> THen add CHECKDB - regardless of how long it takes...
> >
> > Would the following be a good sequence:
> >
> > 1: Verify disk space available compared to backup space required (how
do
> I
> > estimate the backup space required?).
> Backup the database either to tape or to a remote hard drive, backing up
on
> the local box does not protect from local box burn ups...sp_spaceused
> should show about how big things will be..
> > 1: Manually perform backup of the database and Transaction log.
> > 2: Manually perform DBCC CHECKDB (Estimate Only first to verify
> sufficient
> > space in tempdb). I assume I should do it as REPAIR_FAST?
> > 3: Manually perform DBCC INDEXDEFRAG before or instead of DBCC REINDEX
> I would probably do dbcc dbreindex (at least the first time) since it has
> been forever since maintenance has been done... on highly fragmented
tables
> indexdefrag can take longer than dbreindex. (after the first time, do
> whichever of the two you prefer.)
> > 4: Re-Establish the Agent Jobs.
> When scheduling the jobs, run index maintenance BEFORE the db backups...
The
> backup will then be a copy of a well maintained database...so if you ever
> need to restore, you will NOT have to immediately do index maintenance
after
> the restore..
> >
> > TIA,
> > Tore.
> >
> >
>