24 - 7 and the need for maintainence

Hi,
I hope someone can point me in the right direction at least on our
maintainence problem. As a 24x7 shop the databases are up all the time. We
are running enterprise edition 2000 on clustered servers.
The little mundane things like reindexing, torn page fix and other little
things that you would normally run in dead time are the problem.
How do you guys take care of these chores?
TIA
JohnJohn,
For reindexing, you might want to look at DBCC INDEXDEFRAG. Locking is totally different than
the other methods (essentially only locks the area where it currently "sweeps").
As for torn page fixing: This is not something you would normally have. Are you saying that this
is common in your installation?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"John Campbell" <jcampbell@.us-south.net> wrote in message
news:ejiBemtRDHA.304@.tk2msftngp13.phx.gbl...
> Hi,
> I hope someone can point me in the right direction at least on our
> maintainence problem. As a 24x7 shop the databases are up all the time. We
> are running enterprise edition 2000 on clustered servers.
> The little mundane things like reindexing, torn page fix and other little
> things that you would normally run in dead time are the problem.
> How do you guys take care of these chores?
> TIA
> John
>|||I currently use the indexdefrag command. I have seen more torn pages at
this job than I have ever seen in the past. In the past I would only see
them on non-production type servers. Here I see them about every 2 to 3
months on the same server, different databases.
The normal solution is to drop the indexes and then recreate them.
The general maintainence issue is that I went on vacation a few weeks back.
Before I left I was running the DBCC INDEXDEFRAG on the 10 largest tables
about 2 or 3 times a week depenting on my schedule and system load.
Since I have come back, I am unable to run any of my maintenance without
blocking on this particular production server......
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:ebQ8HztRDHA.3144@.tk2msftngp13.phx.gbl...
> John,
> For reindexing, you might want to look at DBCC INDEXDEFRAG. Locking is
totally different than
> the other methods (essentially only locks the area where it currently
"sweeps").
> As for torn page fixing: This is not something you would normally have.
Are you saying that this
> is common in your installation?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "John Campbell" <jcampbell@.us-south.net> wrote in message
> news:ejiBemtRDHA.304@.tk2msftngp13.phx.gbl...
> > Hi,
> > I hope someone can point me in the right direction at least on our
> > maintainence problem. As a 24x7 shop the databases are up all the time.
We
> > are running enterprise edition 2000 on clustered servers.
> > The little mundane things like reindexing, torn page fix and other
little
> > things that you would normally run in dead time are the problem.
> > How do you guys take care of these chores?
> >
> > TIA
> > John
> >
> >
>|||If your getting that many torn pages the I would take a good look at your
hardware, especially your disk subsystem to ensure it is working properly.
Torn pages usually occur when the write to the disk was not completed and
that is mainly caused by the hardware or power failures. As for your other
issue, are you saying that DBCC INDEXDEFRAG is now causing blocking at a
point it interferes with your app? It should only block pages at a time and
for brief periods. This should not adversely affect a properly designed
app. One thing it does do is take a shared lock on the table so if you have
any code in your app that takes out a table level lock this can be a
problem. What kind of blocking are you seeing?
Andrew J. Kelly
SQL Server MVP
"John Campbell" <jcampbell@.us-south.net> wrote in message
news:uBijd6tRDHA.3192@.TK2MSFTNGP10.phx.gbl...
> I currently use the indexdefrag command. I have seen more torn pages at
> this job than I have ever seen in the past. In the past I would only see
> them on non-production type servers. Here I see them about every 2 to 3
> months on the same server, different databases.
> The normal solution is to drop the indexes and then recreate them.
> The general maintainence issue is that I went on vacation a few weeks
back.
> Before I left I was running the DBCC INDEXDEFRAG on the 10 largest tables
> about 2 or 3 times a week depenting on my schedule and system load.
> Since I have come back, I am unable to run any of my maintenance without
> blocking on this particular production server......
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:ebQ8HztRDHA.3144@.tk2msftngp13.phx.gbl...
> > John,
> >
> > For reindexing, you might want to look at DBCC INDEXDEFRAG. Locking is
> totally different than
> > the other methods (essentially only locks the area where it currently
> "sweeps").
> >
> > As for torn page fixing: This is not something you would normally have.
> Are you saying that this
> > is common in your installation?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "John Campbell" <jcampbell@.us-south.net> wrote in message
> > news:ejiBemtRDHA.304@.tk2msftngp13.phx.gbl...
> > > Hi,
> > > I hope someone can point me in the right direction at least on our
> > > maintainence problem. As a 24x7 shop the databases are up all the
time.
> We
> > > are running enterprise edition 2000 on clustered servers.
> > > The little mundane things like reindexing, torn page fix and other
> little
> > > things that you would normally run in dead time are the problem.
> > > How do you guys take care of these chores?
> > >
> > > TIA
> > > John
> > >
> > >
> >
> >
>