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.
> >
> >
>

Redundant Servers?

I have to setup a redundant server system. I want to
preferably use Windows Server 2003 Standard but I can use
Windows Server 2000 as well. The system will be running
SQL Server 2000. I am writing some custom software to
run on the system and will be storing data to the SQL
database. If the primary server fails then the secondary
server will start handling the functions of the primary
server. What is the best way to accomplish this? I need
the data in each SQL database to be identical, is there a
way to sync this data? I have thought about using Legato
Co-Standby Server AAdvanced, is this a good product?
WIll it do what I need? Any ideas are greatly
appreciated. I am a software engineer not a sys admin so
this is kinda new to me. Thanks!!!Look into Microsoft Cluster Services and Microsoft SQL Clustering.
--
Geoff N. Hiten
Senior Database Administrator
Careerbuilder.com
"Kris" <grindaz@.hotmail.com> wrote in message
news:022f01c34181$83b10930$a101280a@.phx.gbl...
> I have to setup a redundant server system. I want to
> preferably use Windows Server 2003 Standard but I can use
> Windows Server 2000 as well. The system will be running
> SQL Server 2000. I am writing some custom software to
> run on the system and will be storing data to the SQL
> database. If the primary server fails then the secondary
> server will start handling the functions of the primary
> server. What is the best way to accomplish this? I need
> the data in each SQL database to be identical, is there a
> way to sync this data? I have thought about using Legato
> Co-Standby Server AAdvanced, is this a good product?
> WIll it do what I need? Any ideas are greatly
> appreciated. I am a software engineer not a sys admin so
> this is kinda new to me. Thanks!!!
>|||Kris, the answer depends on what you need exactly. When
you ask for the "best way" to approach, it also depends on
how much $$$ you'd like to spend, and how much down time
your users could deal with in the event of a system
crash. Yes, clustering is a nice option, but it's more
complex until you've done it a while, and you need shared
disk storage between the nodes, Advanced Server and
Enterprise Ed of SQL Server...
Then you could also look at something like shipping your
transaction logs over to the offline server, and if a
crash happens, maybe get that last LOG over, then changing
server names and bringing that server online...
It all depends on the budget and the length of down time
that's bearable... Bruce
>--Original Message--
>I have to setup a redundant server system. I want to
>preferably use Windows Server 2003 Standard but I can use
>Windows Server 2000 as well. The system will be running
>SQL Server 2000. I am writing some custom software to
>run on the system and will be storing data to the SQL
>database. If the primary server fails then the secondary
>server will start handling the functions of the primary
>server. What is the best way to accomplish this? I need
>the data in each SQL database to be identical, is there a
>way to sync this data? I have thought about using Legato
>Co-Standby Server AAdvanced, is this a good product?
>WIll it do what I need? Any ideas are greatly
>appreciated. I am a software engineer not a sys admin so
>this is kinda new to me. Thanks!!!
>.
>|||Thanks for the reply Bruce. I cannot spend lots of money
on this like $11,000 for SQL Ent. ED. But I will be
using Windows Server 20003 Enterprise. I have been
reading about clustering and it sounds like the way to go
except for as you metion the shared storage. How exactly
do you do this? What type of hardware? Also, will
clustering keep data in the SQL database on each machine
synced together or do you have to keep the DB on the
shared storage? I look forward to your comments and
thanks again.
Kris
>--Original Message--
>Kris, the answer depends on what you need exactly. When
>you ask for the "best way" to approach, it also depends
on
>how much $$$ you'd like to spend, and how much down time
>your users could deal with in the event of a system
>crash. Yes, clustering is a nice option, but it's more
>complex until you've done it a while, and you need
shared
>disk storage between the nodes, Advanced Server and
>Enterprise Ed of SQL Server...
>Then you could also look at something like shipping your
>transaction logs over to the offline server, and if a
>crash happens, maybe get that last LOG over, then
changing
>server names and bringing that server online...
>It all depends on the budget and the length of down time
>that's bearable... Bruce
>>--Original Message--
>>I have to setup a redundant server system. I want to
>>preferably use Windows Server 2003 Standard but I can
use
>>Windows Server 2000 as well. The system will be
running
>>SQL Server 2000. I am writing some custom software to
>>run on the system and will be storing data to the SQL
>>database. If the primary server fails then the
secondary
>>server will start handling the functions of the primary
>>server. What is the best way to accomplish this? I
need
>>the data in each SQL database to be identical, is there
a
>>way to sync this data? I have thought about using
Legato
>>Co-Standby Server AAdvanced, is this a good product?
>>WIll it do what I need? Any ideas are greatly
>>appreciated. I am a software engineer not a sys admin
so
>>this is kinda new to me. Thanks!!!
>>.
>.
>|||In your situation, you have two servers (nodes), and also
a shared disk array, a separate unit, sitting between and
cbaled to each node. Yes, the database files sit on the
shared drives, there's only one copy of them (well, not
counting whatever you do for disaster planning!)... After
teh 2 node cluster is built, then you install SQL Server
and it's "cluster-aware", so you should generally only
have to install it once, and it actually installs SQL
Server software on BOTH nodes C: drives, but anything
shared goes on the disk array. I'm not a hardware guy, to
talk about disk array brands exactly, and which is
better... But in a cluster you need some type of shared
disk between the nodes. Also, since you're dealing with 2
machines, keep in mind that you have TWO registries, so
anything not cluster-aware, needs to MAYBE be done on each
box... Bruce
>--Original Message--
>Thanks for the reply Bruce. I cannot spend lots of money
>on this like $11,000 for SQL Ent. ED. But I will be
>using Windows Server 20003 Enterprise. I have been
>reading about clustering and it sounds like the way to go
>except for as you metion the shared storage. How exactly
>do you do this? What type of hardware? Also, will
>clustering keep data in the SQL database on each machine
>synced together or do you have to keep the DB on the
>shared storage? I look forward to your comments and
>thanks again.
>Kris
>>--Original Message--
>>Kris, the answer depends on what you need exactly. When
>>you ask for the "best way" to approach, it also depends
>on
>>how much $$$ you'd like to spend, and how much down time
>>your users could deal with in the event of a system
>>crash. Yes, clustering is a nice option, but it's more
>>complex until you've done it a while, and you need
>shared
>>disk storage between the nodes, Advanced Server and
>>Enterprise Ed of SQL Server...
>>Then you could also look at something like shipping your
>>transaction logs over to the offline server, and if a
>>crash happens, maybe get that last LOG over, then
>changing
>>server names and bringing that server online...
>>It all depends on the budget and the length of down time
>>that's bearable... Bruce
>>--Original Message--
>>I have to setup a redundant server system. I want to
>>preferably use Windows Server 2003 Standard but I can
>use
>>Windows Server 2000 as well. The system will be
>running
>>SQL Server 2000. I am writing some custom software to
>>run on the system and will be storing data to the SQL
>>database. If the primary server fails then the
>secondary
>>server will start handling the functions of the primary
>>server. What is the best way to accomplish this? I
>need
>>the data in each SQL database to be identical, is there
>a
>>way to sync this data? I have thought about using
>Legato
>>Co-Standby Server AAdvanced, is this a good product?
>>WIll it do what I need? Any ideas are greatly
>>appreciated. I am a software engineer not a sys admin
>so
>>this is kinda new to me. Thanks!!!
>>.
>>.
>.
>|||probably the cheapest solution is log shipping, which can be home grown or
purchased (enterprise edition). Failover is manual, then.
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:08f801c34193$8b57c950$a001280a@.phx.gbl...
> In your situation, you have two servers (nodes), and also
> a shared disk array, a separate unit, sitting between and
> cbaled to each node. Yes, the database files sit on the
> shared drives, there's only one copy of them (well, not
> counting whatever you do for disaster planning!)... After
> teh 2 node cluster is built, then you install SQL Server
> and it's "cluster-aware", so you should generally only
> have to install it once, and it actually installs SQL
> Server software on BOTH nodes C: drives, but anything
> shared goes on the disk array. I'm not a hardware guy, to
> talk about disk array brands exactly, and which is
> better... But in a cluster you need some type of shared
> disk between the nodes. Also, since you're dealing with 2
> machines, keep in mind that you have TWO registries, so
> anything not cluster-aware, needs to MAYBE be done on each
> box... Bruce
>
> >--Original Message--
> >Thanks for the reply Bruce. I cannot spend lots of money
> >on this like $11,000 for SQL Ent. ED. But I will be
> >using Windows Server 20003 Enterprise. I have been
> >reading about clustering and it sounds like the way to go
> >except for as you metion the shared storage. How exactly
> >do you do this? What type of hardware? Also, will
> >clustering keep data in the SQL database on each machine
> >synced together or do you have to keep the DB on the
> >shared storage? I look forward to your comments and
> >thanks again.
> >
> >Kris
> >
> >>--Original Message--
> >>Kris, the answer depends on what you need exactly. When
> >>you ask for the "best way" to approach, it also depends
> >on
> >>how much $$$ you'd like to spend, and how much down time
> >>your users could deal with in the event of a system
> >>crash. Yes, clustering is a nice option, but it's more
> >>complex until you've done it a while, and you need
> >shared
> >>disk storage between the nodes, Advanced Server and
> >>Enterprise Ed of SQL Server...
> >>
> >>Then you could also look at something like shipping your
> >>transaction logs over to the offline server, and if a
> >>crash happens, maybe get that last LOG over, then
> >changing
> >>server names and bringing that server online...
> >>
> >>It all depends on the budget and the length of down time
> >>that's bearable... Bruce
> >>
> >>--Original Message--
> >>I have to setup a redundant server system. I want to
> >>preferably use Windows Server 2003 Standard but I can
> >use
> >>Windows Server 2000 as well. The system will be
> >running
> >>SQL Server 2000. I am writing some custom software to
> >>run on the system and will be storing data to the SQL
> >>database. If the primary server fails then the
> >secondary
> >>server will start handling the functions of the primary
> >>server. What is the best way to accomplish this? I
> >need
> >>the data in each SQL database to be identical, is there
> >a
> >>way to sync this data? I have thought about using
> >Legato
> >>Co-Standby Server AAdvanced, is this a good product?
> >>WIll it do what I need? Any ideas are greatly
> >>appreciated. I am a software engineer not a sys admin
> >so
> >>this is kinda new to me. Thanks!!!
> >>
> >>.
> >>
> >>.
> >>
> >.
> >

Reduce Table Size without deleting data

We are using SQL Server 2000 Standard ed, sp4 on Server 2000 Advanced.

We have one table that is three times as large as the rest of the database.
Most of the data is static after approximately 3-6 months, but we are
required to keep it for 8 years. I would like to archive this table (A), but
there are complications.

1. the only way to access the data is through the application (they are
images produced by the application-built on Power-Builder)
2. there are multiple tables refrencing this table and vise-versa
3. we restore the entire db to two other servers for testing and training
regularly
4. there might be more complications that have not been thought of

Currently, our only plan is to setup a seperate server with a copy of this db
on it and the application. Leave only the tables necessary to access the data,
and if this 'archive' works, remove from production the data from the table A
and all references to the table A from rows on the other tables.

I mentioned #3 because someone mentioned a third party tool that may be able
to pull the data from the table, archive it elsewhere, and at the same time,
place a 'pointer' in the table to the new storage location. The tool they
mentioned only works on Oracle and we have not explored beyond that yet.

I am ready to explore ideas and suggestions; I am still new to the DBA world,
I am out of ideas.

Thank you!

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200607/1On Thu, 20 Jul 2006 14:23:50 GMT, iamset via SQLMonster.com wrote:

(snip)

Quote:

Originally Posted by

>I am ready to explore ideas and suggestions; I am still new to the DBA world,
>I am out of ideas.

Hi iamset,

You might wish to explore distributed partitioned views. Looks like
they're the exact right tool for this job.

Books Online has good information on them.

--
Hugo Kornelis, SQL Server MVP