Disk defrag

Is ok to run a disk defrag using the native windows tool on a local array
that houses SQL databases?It should be fine; but take a full database backup of all databases before
defrag. Stop SQL Server service, otherwise the data files will be locked and
won't be accessible to defrag. DisKeeper will be a good option.
But Physical disk defragmentation quite often is not needed, but
defragmentation within databases (REINDEXING) is good.
Thanks
Hari
"Tim" <Tim@.discussions.microsoft.com> wrote in message
news:3336DC2D-0FE2-4513-A002-BD24B4AA018D@.microsoft.com...
> Is ok to run a disk defrag using the native windows tool on a local array
> that houses SQL databases?|||See this article as well..
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Thanks
Hari
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:udhRWnxpHHA.3312@.TK2MSFTNGP05.phx.gbl...
> It should be fine; but take a full database backup of all databases before
> defrag. Stop SQL Server service, otherwise the data files will be locked
> and
> won't be accessible to defrag. DisKeeper will be a good option.
> But Physical disk defragmentation quite often is not needed, but
> defragmentation within databases (REINDEXING) is good.
> Thanks
> Hari
>
> "Tim" <Tim@.discussions.microsoft.com> wrote in message
> news:3336DC2D-0FE2-4513-A002-BD24B4AA018D@.microsoft.com...
>|||Thanks for the infosql

Disk defrag

Is ok to run a disk defrag using the native windows tool on a local array
that houses SQL databases?
It should be fine; but take a full database backup of all databases before
defrag. Stop SQL Server service, otherwise the data files will be locked and
won't be accessible to defrag. DisKeeper will be a good option.
But Physical disk defragmentation quite often is not needed, but
defragmentation within databases (REINDEXING) is good.
Thanks
Hari
"Tim" <Tim@.discussions.microsoft.com> wrote in message
news:3336DC2D-0FE2-4513-A002-BD24B4AA018D@.microsoft.com...
> Is ok to run a disk defrag using the native windows tool on a local array
> that houses SQL databases?
|||See this article as well..
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Thanks
Hari
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:udhRWnxpHHA.3312@.TK2MSFTNGP05.phx.gbl...
> It should be fine; but take a full database backup of all databases before
> defrag. Stop SQL Server service, otherwise the data files will be locked
> and
> won't be accessible to defrag. DisKeeper will be a good option.
> But Physical disk defragmentation quite often is not needed, but
> defragmentation within databases (REINDEXING) is good.
> Thanks
> Hari
>
> "Tim" <Tim@.discussions.microsoft.com> wrote in message
> news:3336DC2D-0FE2-4513-A002-BD24B4AA018D@.microsoft.com...
>
|||Thanks for the info

Disk defrag

Is ok to run a disk defrag using the native windows tool on a local array
that houses SQL databases?It should be fine; but take a full database backup of all databases before
defrag. Stop SQL Server service, otherwise the data files will be locked and
won't be accessible to defrag. DisKeeper will be a good option.
But Physical disk defragmentation quite often is not needed, but
defragmentation within databases (REINDEXING) is good.
Thanks
Hari
"Tim" <Tim@.discussions.microsoft.com> wrote in message
news:3336DC2D-0FE2-4513-A002-BD24B4AA018D@.microsoft.com...
> Is ok to run a disk defrag using the native windows tool on a local array
> that houses SQL databases?|||See this article as well..
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Thanks
Hari
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:udhRWnxpHHA.3312@.TK2MSFTNGP05.phx.gbl...
> It should be fine; but take a full database backup of all databases before
> defrag. Stop SQL Server service, otherwise the data files will be locked
> and
> won't be accessible to defrag. DisKeeper will be a good option.
> But Physical disk defragmentation quite often is not needed, but
> defragmentation within databases (REINDEXING) is good.
> Thanks
> Hari
>
> "Tim" <Tim@.discussions.microsoft.com> wrote in message
> news:3336DC2D-0FE2-4513-A002-BD24B4AA018D@.microsoft.com...
>> Is ok to run a disk defrag using the native windows tool on a local array
>> that houses SQL databases?
>|||Thanks for the info

Disk Block Size

Hi,
We use internal (local) disk for our sql server 2000, SP3a
databases. We have the OS (Win 2k Advanced Server), log
files, and data files on separate physical spindles. The
disk spins at 15K RPM and has proved to be much quicker
than our previous EMC solution. Lately, we have noticed
that the disk_queue_length numbers on our data spindle are
higher than normal. The physical block size of our disk
is now at 4K. I remember reading that SQL SERVER writes
and reads in 8K sections and it would be best to format
the data and log drives at an 8K block size. We have also
had some database contension issues along with the
disk_queue_length, so we believe our problem could be
either hardware or software related. We would like to get
the hardware as finely tuned before we challenge the
software developers and their queries.
Can anyone provide any insight on the above?
Thanks,
AndyWhere has the database contention been seen? What kind of workload is it
with how many clients?
Long disk queues are usually indicative of the IO system not being able to
handle the load placed upon it. Solutions are tuning/upgrading the IO system
or reducing the IO load - if you're convinced the load should not be too
high, you'll need to start doing query perf analysis.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:11eb01c4263e$a5e76650$a401280a@.phx.gbl...
> Hi,
> We use internal (local) disk for our sql server 2000, SP3a
> databases. We have the OS (Win 2k Advanced Server), log
> files, and data files on separate physical spindles. The
> disk spins at 15K RPM and has proved to be much quicker
> than our previous EMC solution. Lately, we have noticed
> that the disk_queue_length numbers on our data spindle are
> higher than normal. The physical block size of our disk
> is now at 4K. I remember reading that SQL SERVER writes
> and reads in 8K sections and it would be best to format
> the data and log drives at an 8K block size. We have also
> had some database contension issues along with the
> disk_queue_length, so we believe our problem could be
> either hardware or software related. We would like to get
> the hardware as finely tuned before we challenge the
> software developers and their queries.
> Can anyone provide any insight on the above?
> Thanks,
> Andy|||It is a shared SQL SERVER with 10 user databases on it.
Most of the transactions are "reading" or select in
nature. Up until recently everything was fine. I used
the Disk Defagmenter tool that comes with Windows to
analze the log and data drives and found that the ldf and
mdf files had up to 150 fragments within the file. We
have rebuilt the indexes with both dbcc indexdefrag and
dbcc dbreindex as well as looked and analyzed queries from
a server wide trace.
The applications connecting to this machine are using
connection pooling and we don't exactly know the number of
client connections, but we know that there are no more
than 100 concurrent connections at one time. Most of the
time there is no more than 5 active connections or
transactions unless there is locking or contension at the
time. We have thought about moving some databases to less
transactional sql servers, but wanted to rule out disk
fragmentation as a bottleneck first.
Thanks,
Andy
>--Original Message--
>Where has the database contention been seen? What kind of
workload is it
>with how many clients?
>Long disk queues are usually indicative of the IO system
not being able to
>handle the load placed upon it. Solutions are
tuning/upgrading the IO system
>or reducing the IO load - if you're convinced the load
should not be too
>high, you'll need to start doing query perf analysis.
>Regards
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Andy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:11eb01c4263e$a5e76650$a401280a@.phx.gbl...
SP3a[vbcol=seagreen]
The[vbcol=seagreen]
are[vbcol=seagreen]
also[vbcol=seagreen]
get[vbcol=seagreen]
>
>.
>

Disk Block Size

Hi,
We use internal (local) disk for our sql server 2000, SP3a
databases. We have the OS (Win 2k Advanced Server), log
files, and data files on separate physical spindles. The
disk spins at 15K RPM and has proved to be much quicker
than our previous EMC solution. Lately, we have noticed
that the disk_queue_length numbers on our data spindle are
higher than normal. The physical block size of our disk
is now at 4K. I remember reading that SQL SERVER writes
and reads in 8K sections and it would be best to format
the data and log drives at an 8K block size. We have also
had some database contension issues along with the
disk_queue_length, so we believe our problem could be
either hardware or software related. We would like to get
the hardware as finely tuned before we challenge the
software developers and their queries.
Can anyone provide any insight on the above?
Thanks,
Andy
Where has the database contention been seen? What kind of workload is it
with how many clients?
Long disk queues are usually indicative of the IO system not being able to
handle the load placed upon it. Solutions are tuning/upgrading the IO system
or reducing the IO load - if you're convinced the load should not be too
high, you'll need to start doing query perf analysis.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:11eb01c4263e$a5e76650$a401280a@.phx.gbl...
> Hi,
> We use internal (local) disk for our sql server 2000, SP3a
> databases. We have the OS (Win 2k Advanced Server), log
> files, and data files on separate physical spindles. The
> disk spins at 15K RPM and has proved to be much quicker
> than our previous EMC solution. Lately, we have noticed
> that the disk_queue_length numbers on our data spindle are
> higher than normal. The physical block size of our disk
> is now at 4K. I remember reading that SQL SERVER writes
> and reads in 8K sections and it would be best to format
> the data and log drives at an 8K block size. We have also
> had some database contension issues along with the
> disk_queue_length, so we believe our problem could be
> either hardware or software related. We would like to get
> the hardware as finely tuned before we challenge the
> software developers and their queries.
> Can anyone provide any insight on the above?
> Thanks,
> Andy
|||It is a shared SQL SERVER with 10 user databases on it.
Most of the transactions are "reading" or select in
nature. Up until recently everything was fine. I used
the Disk Defagmenter tool that comes with Windows to
analze the log and data drives and found that the ldf and
mdf files had up to 150 fragments within the file. We
have rebuilt the indexes with both dbcc indexdefrag and
dbcc dbreindex as well as looked and analyzed queries from
a server wide trace.
The applications connecting to this machine are using
connection pooling and we don't exactly know the number of
client connections, but we know that there are no more
than 100 concurrent connections at one time. Most of the
time there is no more than 5 active connections or
transactions unless there is locking or contension at the
time. We have thought about moving some databases to less
transactional sql servers, but wanted to rule out disk
fragmentation as a bottleneck first.
Thanks,
Andy
>--Original Message--
>Where has the database contention been seen? What kind of
workload is it
>with how many clients?
>Long disk queues are usually indicative of the IO system
not being able to
>handle the load placed upon it. Solutions are
tuning/upgrading the IO system
>or reducing the IO load - if you're convinced the load
should not be too
>high, you'll need to start doing query perf analysis.
>Regards
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Andy" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:11eb01c4263e$a5e76650$a401280a@.phx.gbl...
SP3a[vbcol=seagreen]
The[vbcol=seagreen]
are[vbcol=seagreen]
also[vbcol=seagreen]
get
>
>.
>

Disk Block Size

Hi,
We use internal (local) disk for our sql server 2000, SP3a
databases. We have the OS (Win 2k Advanced Server), log
files, and data files on separate physical spindles. The
disk spins at 15K RPM and has proved to be much quicker
than our previous EMC solution. Lately, we have noticed
that the disk_queue_length numbers on our data spindle are
higher than normal. The physical block size of our disk
is now at 4K. I remember reading that SQL SERVER writes
and reads in 8K sections and it would be best to format
the data and log drives at an 8K block size. We have also
had some database contension issues along with the
disk_queue_length, so we believe our problem could be
either hardware or software related. We would like to get
the hardware as finely tuned before we challenge the
software developers and their queries.
Can anyone provide any insight on the above?
Thanks,
AndyWhere has the database contention been seen? What kind of workload is it
with how many clients?
Long disk queues are usually indicative of the IO system not being able to
handle the load placed upon it. Solutions are tuning/upgrading the IO system
or reducing the IO load - if you're convinced the load should not be too
high, you'll need to start doing query perf analysis.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:11eb01c4263e$a5e76650$a401280a@.phx.gbl...
> Hi,
> We use internal (local) disk for our sql server 2000, SP3a
> databases. We have the OS (Win 2k Advanced Server), log
> files, and data files on separate physical spindles. The
> disk spins at 15K RPM and has proved to be much quicker
> than our previous EMC solution. Lately, we have noticed
> that the disk_queue_length numbers on our data spindle are
> higher than normal. The physical block size of our disk
> is now at 4K. I remember reading that SQL SERVER writes
> and reads in 8K sections and it would be best to format
> the data and log drives at an 8K block size. We have also
> had some database contension issues along with the
> disk_queue_length, so we believe our problem could be
> either hardware or software related. We would like to get
> the hardware as finely tuned before we challenge the
> software developers and their queries.
> Can anyone provide any insight on the above?
> Thanks,
> Andy|||It is a shared SQL SERVER with 10 user databases on it.
Most of the transactions are "reading" or select in
nature. Up until recently everything was fine. I used
the Disk Defagmenter tool that comes with Windows to
analze the log and data drives and found that the ldf and
mdf files had up to 150 fragments within the file. We
have rebuilt the indexes with both dbcc indexdefrag and
dbcc dbreindex as well as looked and analyzed queries from
a server wide trace.
The applications connecting to this machine are using
connection pooling and we don't exactly know the number of
client connections, but we know that there are no more
than 100 concurrent connections at one time. Most of the
time there is no more than 5 active connections or
transactions unless there is locking or contension at the
time. We have thought about moving some databases to less
transactional sql servers, but wanted to rule out disk
fragmentation as a bottleneck first.
Thanks,
Andy
>--Original Message--
>Where has the database contention been seen? What kind of
workload is it
>with how many clients?
>Long disk queues are usually indicative of the IO system
not being able to
>handle the load placed upon it. Solutions are
tuning/upgrading the IO system
>or reducing the IO load - if you're convinced the load
should not be too
>high, you'll need to start doing query perf analysis.
>Regards
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Andy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:11eb01c4263e$a5e76650$a401280a@.phx.gbl...
>> Hi,
>> We use internal (local) disk for our sql server 2000,
SP3a
>> databases. We have the OS (Win 2k Advanced Server), log
>> files, and data files on separate physical spindles.
The
>> disk spins at 15K RPM and has proved to be much quicker
>> than our previous EMC solution. Lately, we have noticed
>> that the disk_queue_length numbers on our data spindle
are
>> higher than normal. The physical block size of our disk
>> is now at 4K. I remember reading that SQL SERVER writes
>> and reads in 8K sections and it would be best to format
>> the data and log drives at an 8K block size. We have
also
>> had some database contension issues along with the
>> disk_queue_length, so we believe our problem could be
>> either hardware or software related. We would like to
get
>> the hardware as finely tuned before we challenge the
>> software developers and their queries.
>> Can anyone provide any insight on the above?
>> Thanks,
>> Andy
>
>.
>

Disaster recovery - local account question

Hi Lance,
SQL Server Disaster Recovery Plan Examples at
http://sql-server-
performance.com/disaster_recover_examples.asp
Also If you are interested you can download and read the
MS SQL Server 2000 Operations guide from
http://www.microsoft.com/downloads/details.aspx?
displaylang=en&familyid=b91ce500-4ab7-4e1d-ac57-
03b5d0e1ab8a
There can be many disasters which are out of scope of this
topic. Disaster can be a serious non-recoverable Database
corruption due to HD failures or a non-recoverable system
crash which needs reinstallation from scratch. In both
cases you have to restore a copy of recent backup of your
database. Your Backup plan also varies from how important
is the data for you.
I did not undertand exactly what u mean by local accounts.
May be someone can answer this in a better way. Assuming
that you are taking about SQL Server logins, here are few
pointers to resolve such issues...
HOW TO: Transfer Logins and Passwords Between Instances of
SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-
us;246133
HOW TO: Resolve Permission Issues When You Move a Database
Between Servers That Are Running SQL Server
http://support.microsoft.com/default.aspx?kbid=240872
PRB: "Troubleshooting Orphaned Users" Topic in Books
Online is Incomplete
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;274188
Fixing Broken Logins and Transferring Passwords
http://www.databasejournal.com/feat...l/article.php/1
438491
HTH
Regards
Thirumal
>--Original Message--
>We have sql server 7.0 and 2000 servers that run
databases used for web front-ends in asp. I have been
given the task of coming up with disaster recovery
procedures. It looks like the databases are backed up
properly off site, however, each database has a number of
local accounts used by the asp developers to access the
stored procedures. My concern is if a disaster would
occur, how could the local accounts be migrated to the new
servers.
>In other words, can local accounts be backed up and
restored or is there a better way of doing this.
>Thanks in advance.
>Lance
>.
>Thanks for all the information, that's just what I was looking for. By the way, I also
found a nice script for transfering the accounts at www.winnetmag.com/sqlserver/a...090/16090.html.

Disable local report cache

Does anyone know how to disable caching of local reports with
Reporting Services 2005?On Dec 9, 4:12 pm, lachlan.h...@.gmail.com wrote:
> Does anyone know how to disable caching of local reports with
> Reporting Services 2005?
This article should be helpful.
http://www.databasejournal.com/features/mssql/article.php/3695721
Regards,
Enrique Martinez
Sr. Software Consultant

disable local admin rights in sql server

Can I disable local admin right in SQL Server?
What is the affect if I could do that?
Your response would be greatly appreaciated,
CulamYou can remove the builtin\administrators group from SQL
Server. However, under some scenarios, this can cause
problems. Whether you get problems or not depends. The
following article has a more information section with links
to some issues that could come up:
INF: How to impede Windows NT administrators from
administering a clustered instance of SQL Server
http://support.microsoft.com/?id=263712
-Sue
On Thu, 18 Nov 2004 14:49:06 -0800, "culam"
<culam@.discussions.microsoft.com> wrote:

>Can I disable local admin right in SQL Server?
>What is the affect if I could do that?
>Your response would be greatly appreaciated,
>Culam|||Thank you Sue!
"Sue Hoegemeier" wrote:

> You can remove the builtin\administrators group from SQL
> Server. However, under some scenarios, this can cause
> problems. Whether you get problems or not depends. The
> following article has a more information section with links
> to some issues that could come up:
> INF: How to impede Windows NT administrators from
> administering a clustered instance of SQL Server
> http://support.microsoft.com/?id=263712
> -Sue
> On Thu, 18 Nov 2004 14:49:06 -0800, "culam"
> <culam@.discussions.microsoft.com> wrote:
>
>|||Is it also possible to limit local admin rights to an MSDE SQL SERVER instan
ce?
"Sue Hoegemeier" wrote:

> You can remove the builtin\administrators group from SQL
> Server. However, under some scenarios, this can cause
> problems. Whether you get problems or not depends. The
> following article has a more information section with links
> to some issues that could come up:
> INF: How to impede Windows NT administrators from
> administering a clustered instance of SQL Server
> http://support.microsoft.com/?id=263712
> -Sue
> On Thu, 18 Nov 2004 14:49:06 -0800, "culam"
> <culam@.discussions.microsoft.com> wrote:
>
>