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

2005: sp_helpdevice returns 0 results

On a new install of SQL Server2000, sp_helpdevice returns at least one result
(for master) and you can call
sp_helpdevice 'master'
to always get that row.
On SQL Server2005, sp_helpdevice on a new install returns no results, and
sp_helpdevice 'master' generates an error.
Anyone know how to find out the file path where the master database is stored?sp_helpdevice was used to return the name/location of backup devices, so it
seems an odd choice to use just to return the location of master.
Anyway, in SQL Server 2005, you want to use system catalog views. For
example, the following statement returns the physical location of the master
database files.
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('master')
For additional information about catalog views, see the topic "Catalog Views
(Transact-SQL)" in Books Online.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"michael pryor" <michael pryor@.discussions.microsoft.com> wrote in message
news:78862BE2-74CB-446E-B119-3B2518596A60@.microsoft.com...
> On a new install of SQL Server2000, sp_helpdevice returns at least one
> result
> (for master) and you can call
> sp_helpdevice 'master'
> to always get that row.
> On SQL Server2005, sp_helpdevice on a new install returns no results, and
> sp_helpdevice 'master' generates an error.
> Anyone know how to find out the file path where the master database is
> stored?|||Is there a problem with using sp_helpfile 'master' and looking at the
filename?

2005 Failover Cluster SAN drives best practices

> What is the best practice to move the logs onto
> another spindle? Do I have to just create another LUN?
You should at least put yout transaction log files on a separate LUN from
your data LUN. This doesn't guarantee that your data and log would be placed
on separate spindles inside SAN because both LUNs can be carved from the sam
e
set of disks, but at least it gives you some separation.

> I am about to add a few more instances.
> Does each one need to have it's own LUN?
Each clustered instance will be implemented a separate cluster resource
group with its own disk resource(s). Since you can't have the same read/writ
e
disk as two separate disk resources in different resource groups, each
clustered instance needs its own LUN.

> There is a limited amount of drive letters that I can create. Is there
> a way to share a LUN between instances?
You can't really share drive letters. But SQL2005 fully supports mounted
volumes in a cluster. These volumes don't have to be assigned separate drive
letters. As long as they are made disk resources, and are mounted under a
root drive letter, you can have multiple volumes in a resource group (or an
instance) with as few as one drive letter per instance.
Linchi
"Karl" wrote:

> I have a MS SQL 2005 default installation on a Windows 2003 cluster. I
> currently have two nodes and I am planning on adding more instances to
> run on the other node.
> First, I am not sure I have setup my existing default instance the best
> way for my cluster. I currently only have one 'Physical Disk' in the
> cluster group which is a LUN on our SAN. Both the data files and log
> files are stored there. What is the best practice to move the logs onto
> another spindle? Do I have to just create another LUN?
> I am about to add a few more instances. Does each one need to have it's
> own LUN?
> There is a limited amount of drive letters that I can create. Is there
> a way to share a LUN between instances? I seem to remember reading a
> feature of 2005 that was related to this but, for the life of me, I
> cannot find it again.
> Any help would be appreciated!
> Thank you
> Karl
>I have a MS SQL 2005 default installation on a Windows 2003 cluster. I
currently have two nodes and I am planning on adding more instances to
run on the other node.
First, I am not sure I have setup my existing default instance the best
way for my cluster. I currently only have one 'Physical Disk' in the
cluster group which is a LUN on our SAN. Both the data files and log
files are stored there. What is the best practice to move the logs onto
another spindle? Do I have to just create another LUN?
I am about to add a few more instances. Does each one need to have it's
own LUN?
There is a limited amount of drive letters that I can create. Is there
a way to share a LUN between instances? I seem to remember reading a
feature of 2005 that was related to this but, for the life of me, I
cannot find it again.
Any help would be appreciated!
Thank you
Karl|||> What is the best practice to move the logs onto
> another spindle? Do I have to just create another LUN?
You should at least put yout transaction log files on a separate LUN from
your data LUN. This doesn't guarantee that your data and log would be placed
on separate spindles inside SAN because both LUNs can be carved from the sam
e
set of disks, but at least it gives you some separation.

> I am about to add a few more instances.
> Does each one need to have it's own LUN?
Each clustered instance will be implemented a separate cluster resource
group with its own disk resource(s). Since you can't have the same read/writ
e
disk as two separate disk resources in different resource groups, each
clustered instance needs its own LUN.

> There is a limited amount of drive letters that I can create. Is there
> a way to share a LUN between instances?
You can't really share drive letters. But SQL2005 fully supports mounted
volumes in a cluster. These volumes don't have to be assigned separate drive
letters. As long as they are made disk resources, and are mounted under a
root drive letter, you can have multiple volumes in a resource group (or an
instance) with as few as one drive letter per instance.
Linchi
"Karl" wrote:

> I have a MS SQL 2005 default installation on a Windows 2003 cluster. I
> currently have two nodes and I am planning on adding more instances to
> run on the other node.
> First, I am not sure I have setup my existing default instance the best
> way for my cluster. I currently only have one 'Physical Disk' in the
> cluster group which is a LUN on our SAN. Both the data files and log
> files are stored there. What is the best practice to move the logs onto
> another spindle? Do I have to just create another LUN?
> I am about to add a few more instances. Does each one need to have it's
> own LUN?
> There is a limited amount of drive letters that I can create. Is there
> a way to share a LUN between instances? I seem to remember reading a
> feature of 2005 that was related to this but, for the life of me, I
> cannot find it again.
> Any help would be appreciated!
> Thank you
> Karl
>