Disk Fragmentation on W2K Server/SQL Server 2000

Hi, I'm hoping one of you SQL gurus out there will help me with a debate bet
ween myself, a simple Network Admin. and my colleague, an SQL DBA. The W2K/
SQL 2000 box is having performance problems so I was asked to check on it.
Now, in most environments I
have worked in, I took care of the physical server and OS and the DBA/Develo
per types did their own thing. In my current work environment, everyone has
been accustomed to taking care of their own boxes, the DBA included, so the
re are some issues with me
doing my job at all. Anyway, I did a performance analysis of the OS/Hardwar
e and configuration. I also researched the Best Practices for SQL 2000 Serve
r and after a week of collecting metrics, gave the information to the DBA.
There did not appear to be
any disk, memory, paging etc. problems but the one thing that really stood o
ut to me was that the disks were 49% fragmented and the files on those disks
93 and 99% fragmented. Primarily database and log files. Again, I just pr
ovided the info as I do no
t want to step on the DBAs toes. The DBA however, reacted defensively and s
aid that she has *never* defragged an SQL server and in searching through MS
DN, found *no* evidence that there is ever a need to defrag one. Further, t
hat if SQL server could now
find the files in their fragmented state, why was there a problem? If the d
rives were defragged, how would SQL server be able to find the files? She w
as serious. Now, I'm being frowned on as not knowing what I'm talking about.
I'm the newest member of
the team, but not a newbie in the field. I have worked with Microsoft produc
ts now for many years and I know that every Windows box PC or Server needs t
o be defragged and occasionally rebooted as a part of routine maintenace. T
here needs to be a mainten
ance windows for doing so. Having a 99.9% uptime record will not matter when
those drives grind to a halt. What I keep searching for is some reference
from Microsoft or other known expert or publication that will state this as
it pertains to SQL server.
Most of the SQL manuals leave out any OS administration information. Anyone
have any suggestions or comments? Microsoft, are you out there? Please He
lp! TIAmy two cents:
Databases if set up as auto grow and auto shrink, will definitely fragment
disk drives over time. Defragmentation the disks will help improve
performance.
Now, inside a database, there is a page split problem that causes
fragmentation too, It's the DBA's job to reindex tables on a regular basis
to fix index fragmentation. I think you should first get an idea of how the
DBA maintains the index defragmentation, then work on the physical disk
defragmentation. Also there are a lot of other factors affecting database
performance.
Richard
"cdub" <cdub@.newsguy.com> wrote in message
news:29C67933-B2CB-43F3-A498-E8EABD2DB936@.microsoft.com...
> Hi, I'm hoping one of you SQL gurus out there will help me with a debate
between myself, a simple Network Admin. and my colleague, an SQL DBA. The
W2K/SQL 2000 box is having performance problems so I was asked to check on
it. Now, in most environments I have worked in, I took care of the physical
server and OS and the DBA/Developer types did their own thing. In my
current work environment, everyone has been accustomed to taking care of
their own boxes, the DBA included, so there are some issues with me doing my
job at all. Anyway, I did a performance analysis of the OS/Hardware and
configuration. I also researched the Best Practices for SQL 2000 Server and
after a week of collecting metrics, gave the information to the DBA. There
did not appear to be any disk, memory, paging etc. problems but the one
thing that really stood out to me was that the disks were 49% fragmented and
the files on those disks 93 and 99% fragmented. Primarily database and log
files. Again, I just provided the info as I do not want to step on the DBAs
toes. The DBA however, reacted defensively and said that she has *never*
defragged an SQL server and in searching through MSDN, found *no* evidence
that there is ever a need to defrag one. Further, that if SQL server could
now find the files in their fragmented state, why was there a problem? If
the drives were defragged, how would SQL server be able to find the files?
She was serious. Now, I'm being frowned on as not knowing what I'm talking
about. I'm the newest member of the team, but not a newbie in the field. I
have worked with Microsoft products now for many years and I know that every
Windows box PC or Server needs to be defragged and occasionally rebooted as
a part of routine maintenace. There needs to be a maintenance windows for
doing so. Having a 99.9% uptime record will not matter when those drives
grind to a halt. What I keep searching for is some reference from Microsoft
or other known expert or publication that will state this as it pertains to
SQL server. Most of the SQL manuals leave out any OS administration
information. Anyone have any suggestions or comments? Microsoft, are you
out there? Please Help! TIA|||I agree with Richard. I've seen SQL performance affected by fragmentation
and auto-grow. The best way to handle it is usually to plan the size of the
database you need, defrag first, and then allocate the space that you need.
Christian Smith
"Richard Ding" <dingr@.cleanharbors.com> wrote in message
news:uEp2zCo8DHA.2656@.TK2MSFTNGP11.phx.gbl...
> my two cents:
> Databases if set up as auto grow and auto shrink, will definitely fragment
> disk drives over time. Defragmentation the disks will help improve
> performance.
> Now, inside a database, there is a page split problem that causes
> fragmentation too, It's the DBA's job to reindex tables on a regular basis
> to fix index fragmentation. I think you should first get an idea of how
the
> DBA maintains the index defragmentation, then work on the physical disk
> defragmentation. Also there are a lot of other factors affecting database
> performance.
>
> Richard
> "cdub" <cdub@.newsguy.com> wrote in message
> news:29C67933-B2CB-43F3-A498-E8EABD2DB936@.microsoft.com...
> between myself, a simple Network Admin. and my colleague, an SQL DBA. The
> W2K/SQL 2000 box is having performance problems so I was asked to check on
> it. Now, in most environments I have worked in, I took care of the
physical
> server and OS and the DBA/Developer types did their own thing. In my
> current work environment, everyone has been accustomed to taking care of
> their own boxes, the DBA included, so there are some issues with me doing
my
> job at all. Anyway, I did a performance analysis of the OS/Hardware and
> configuration. I also researched the Best Practices for SQL 2000 Server
and
> after a week of collecting metrics, gave the information to the DBA.
There
> did not appear to be any disk, memory, paging etc. problems but the one
> thing that really stood out to me was that the disks were 49% fragmented
and
> the files on those disks 93 and 99% fragmented. Primarily database and
log
> files. Again, I just provided the info as I do not want to step on the
DBAs
> toes. The DBA however, reacted defensively and said that she has *never*
> defragged an SQL server and in searching through MSDN, found *no* evidence
> that there is ever a need to defrag one. Further, that if SQL server
could
> now find the files in their fragmented state, why was there a problem? If
> the drives were defragged, how would SQL server be able to find the files?
> She was serious. Now, I'm being frowned on as not knowing what I'm talking
> about. I'm the newest member of the team, but not a newbie in the field.
I
> have worked with Microsoft products now for many years and I know that
every
> Windows box PC or Server needs to be defragged and occasionally rebooted
as
> a part of routine maintenace. There needs to be a maintenance windows for
> doing so. Having a 99.9% uptime record will not matter when those drives
> grind to a halt. What I keep searching for is some reference from
Microsoft
> or other known expert or publication that will state this as it pertains
to
> SQL server. Most of the SQL manuals leave out any OS administration
> information. Anyone have any suggestions or comments? Microsoft, are you
> out there? Please Help! TIA
>sql

Disk Defragmenter & SQL Svr

I'm looking for info on running Windows 2000 disk
defragmenter on a W2k server running 2 instances on SQL
Server 2000. Does anyone know of issues or problems
running disk defrag while the SQL Server(s) are up and
running? Please reply with any info on this subject.
Thanks.while it is possible to defragment the sql server data
files while the service is running, it is very strongly
not recommended, as per andrew's advice
i do it on my development system, but production is
another matter
2nd) within a sql server data, there is no guarantee a
given table occupies contiguous space
3rd) sql server has a very clever scatter gather
capability that hides any negative consequences of a
fragmented file, along with providing other benefits
>--Original Message--
>I'm looking for info on running Windows 2000 disk
>defragmenter on a W2k server running 2 instances on SQL
>Server 2000. Does anyone know of issues or problems
>running disk defrag while the SQL Server(s) are up and
>running? Please reply with any info on this subject.
>Thanks.
>.
>|||Hello Andrew. on this point about running a defrag while
SQL Server is started... say you detached the databases
first, but SQL Server was still running (and not touching
any files on a drive you are defragging), would this still
be a case when you shouldn't defrag that drive? Is there
any place you can point me to that references this issue
of defragging while SQL is started? THanks, Bruce
>--Original Message--
>You should never run a defragmentor while sql server is
in use. Why do you
>think you need to defrag it?
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"B Wood" <brad.wood@.stclair.org> wrote in message
>news:2cb601c373c1$eba3f8a0$a401280a@.phx.gbl...
>> I'm looking for info on running Windows 2000 disk
>> defragmenter on a W2k server running 2 instances on SQL
>> Server 2000. Does anyone know of issues or problems
>> running disk defrag while the SQL Server(s) are up and
>> running? Please reply with any info on this subject.
>> Thanks.
>
>.
>|||Bruce,
I don't know if there are any actual articles per say. It's mostly just
good practice or common sense approach. If you defragging a drive that
doesn't have any sql server files on it then I guess you shouldn't have to
worry about it damaging sql server files then. But hopefully you don't
actually have users attempting to do anything on the server (otherwise why
keep sql running?) as a defrag utility usually monopolizes the cpu's and
hard drives.
--
Andrew J. Kelly
SQL Server MVP
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:3d5801c37622$ec1ff9b0$a101280a@.phx.gbl...
> Hello Andrew. on this point about running a defrag while
> SQL Server is started... say you detached the databases
> first, but SQL Server was still running (and not touching
> any files on a drive you are defragging), would this still
> be a case when you shouldn't defrag that drive? Is there
> any place you can point me to that references this issue
> of defragging while SQL is started? THanks, Bruce
>
>
> >--Original Message--
> >You should never run a defragmentor while sql server is
> in use. Why do you
> >think you need to defrag it?
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"B Wood" <brad.wood@.stclair.org> wrote in message
> >news:2cb601c373c1$eba3f8a0$a401280a@.phx.gbl...
> >> I'm looking for info on running Windows 2000 disk
> >> defragmenter on a W2k server running 2 instances on SQL
> >> Server 2000. Does anyone know of issues or problems
> >> running disk defrag while the SQL Server(s) are up and
> >> running? Please reply with any info on this subject.
> >> Thanks.
> >
> >
> >.
> >|||Good article on this topic...
http://www.sql-server-performance.com/sql_fragmentation.asp
Use at your own risk...

Disaster..

Scenario:
w2k server sp4
sql server 2k sp3a
I had on USB external disk (160gb from maxtor) 3,34 GB MDF file and LDF
of some size.
Yesterday night I encountered some read errors on it.
W2k suggests me to run CHKDSK /F, that I obviously do.
When system restarts that 3,34 GB MDF file is now size 0kb!!!
Now...
I know that I should have a backup but these data aren't so
important...but If I can retrieve them is better than nothing !!!!!
I'm trying to restore some files with lots of recovery tools but I
notice that all of them find thousands of files...
How can I do, if possible, to obtain my MDF back "full" of data?
Any help MUCH MUCH appreciated...
Best Regards.
--
F.Fabri,
You really shouldn't put SQL Server data files on an exernal USB drive.
Anyway, there's not much you can do in this instance unless you send the
drive off to a specialist for repair (expensive). I have heard that
putting it in the freezer for a few hours can revive it. However, if
your CHKDSK has overwritten or "fixed" sectors on the disk, it is
unlikely you will get the data back without a corrupt MDF file.
I think your only choices here are to restore from backup, or start again.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Fabri wrote:
> Scenario:
> w2k server sp4
> sql server 2k sp3a
>
> I had on USB external disk (160gb from maxtor) 3,34 GB MDF file and LDF
> of some size.
> Yesterday night I encountered some read errors on it.
> W2k suggests me to run CHKDSK /F, that I obviously do.
> When system restarts that 3,34 GB MDF file is now size 0kb!!!
> Now...
> I know that I should have a backup but these data aren't so
> important...but If I can retrieve them is better than nothing !!!!!
> I'm trying to restore some files with lots of recovery tools but I
> notice that all of them find thousands of files...
> How can I do, if possible, to obtain my MDF back "full" of data?
> Any help MUCH MUCH appreciated...
> Best Regards.
>

Disable named pipes on W2k/SQL2k cluster?

I have a cluster running on Windows 2000 with SQL 2000. In SQL Server Network Utility I can highlight named pipes but I cannot disable it. Does anyone know why and better yet should this not be done?You can no longer disable Named Pipes on SQL clusters as of SQL SP3. MS PSS has acknowledged this change although they have yet to document it anywhere.



The reasons given for this change:



1) If other protocols failed, they wanted at least one protocol to be available. For example if TCP/IP is enabled yet SQL Server fails to bind on 1433, Named Pipes should still be able to respond.

2) Prior to SQL SP3, if you disabled Named Pipes and re-enabled it you would an encounter an issue where the local machine name would be populated rather than the virtual server name. For example: \\.\pipe\$$\local machine name\sql\query instead of \\.\pipe\$$\virtual server name\sql\query