Disk Layout from h/w perspective

We usually have our databases comprise of 2 data files and one log file
Is it best to have these files on their own array controller or even on
different channels on the same array controller ?
Whats ideal ?
How do organizations lay out there databases i.e. data and log files along
with the tempdb and backup files,etc.
Would love to hear in terms of array controllers,etc..
You could tell me in terms of an HP or a Dell server..Doesnt matter.. Just
seeking for advices.Ideally you would have your own controller for each with different raid
arrays -raid 10 for the logs and raid 5 for the data. If your work load is
write intensive (over 20% writes) I would make both arrays raid 10.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:Odg69QpPGHA.3984@.TK2MSFTNGP14.phx.gbl...
> We usually have our databases comprise of 2 data files and one log file
> Is it best to have these files on their own array controller or even on
> different channels on the same array controller ?
> Whats ideal ?
> How do organizations lay out there databases i.e. data and log files along
> with the tempdb and backup files,etc.
> Would love to hear in terms of array controllers,etc..
> You could tell me in terms of an HP or a Dell server..Doesnt matter.. Just
> seeking for advices.
>|||So even if a controller has 2-4 channels, you would not prefer to have
arrays hooked to different channels on the same array controller ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O8TYu0qPGHA.3728@.tk2msftngp13.phx.gbl...
> Ideally you would have your own controller for each with different raid
> arrays -raid 10 for the logs and raid 5 for the data. If your work load is
> write intensive (over 20% writes) I would make both arrays raid 10.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:Odg69QpPGHA.3984@.TK2MSFTNGP14.phx.gbl...
>

Disk Layout from h/w perspective

We usually have our databases comprise of 2 data files and one log file
Is it best to have these files on their own array controller or even on
different channels on the same array controller ?
Whats ideal ?
How do organizations lay out there databases i.e. data and log files along
with the tempdb and backup files,etc.
Would love to hear in terms of array controllers,etc..
You could tell me in terms of an HP or a Dell server..Doesnt matter.. Just
seeking for advices.
Ideally you would have your own controller for each with different raid
arrays -raid 10 for the logs and raid 5 for the data. If your work load is
write intensive (over 20% writes) I would make both arrays raid 10.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:Odg69QpPGHA.3984@.TK2MSFTNGP14.phx.gbl...
> We usually have our databases comprise of 2 data files and one log file
> Is it best to have these files on their own array controller or even on
> different channels on the same array controller ?
> Whats ideal ?
> How do organizations lay out there databases i.e. data and log files along
> with the tempdb and backup files,etc.
> Would love to hear in terms of array controllers,etc..
> You could tell me in terms of an HP or a Dell server..Doesnt matter.. Just
> seeking for advices.
>
|||So even if a controller has 2-4 channels, you would not prefer to have
arrays hooked to different channels on the same array controller ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O8TYu0qPGHA.3728@.tk2msftngp13.phx.gbl...
> Ideally you would have your own controller for each with different raid
> arrays -raid 10 for the logs and raid 5 for the data. If your work load is
> write intensive (over 20% writes) I would make both arrays raid 10.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:Odg69QpPGHA.3984@.TK2MSFTNGP14.phx.gbl...
>

Disk Layout from h/w perspective

We usually have our databases comprise of 2 data files and one log file
Is it best to have these files on their own array controller or even on
different channels on the same array controller ?
Whats ideal ?
How do organizations lay out there databases i.e. data and log files along
with the tempdb and backup files,etc.
Would love to hear in terms of array controllers,etc..
You could tell me in terms of an HP or a Dell server..Doesnt matter.. Just
seeking for advices.Ideally you would have your own controller for each with different raid
arrays -raid 10 for the logs and raid 5 for the data. If your work load is
write intensive (over 20% writes) I would make both arrays raid 10.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:Odg69QpPGHA.3984@.TK2MSFTNGP14.phx.gbl...
> We usually have our databases comprise of 2 data files and one log file
> Is it best to have these files on their own array controller or even on
> different channels on the same array controller ?
> Whats ideal ?
> How do organizations lay out there databases i.e. data and log files along
> with the tempdb and backup files,etc.
> Would love to hear in terms of array controllers,etc..
> You could tell me in terms of an HP or a Dell server..Doesnt matter.. Just
> seeking for advices.
>|||So even if a controller has 2-4 channels, you would not prefer to have
arrays hooked to different channels on the same array controller ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O8TYu0qPGHA.3728@.tk2msftngp13.phx.gbl...
> Ideally you would have your own controller for each with different raid
> arrays -raid 10 for the logs and raid 5 for the data. If your work load is
> write intensive (over 20% writes) I would make both arrays raid 10.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:Odg69QpPGHA.3984@.TK2MSFTNGP14.phx.gbl...
>> We usually have our databases comprise of 2 data files and one log file
>> Is it best to have these files on their own array controller or even on
>> different channels on the same array controller ?
>> Whats ideal ?
>> How do organizations lay out there databases i.e. data and log files
>> along with the tempdb and backup files,etc.
>> Would love to hear in terms of array controllers,etc..
>> You could tell me in terms of an HP or a Dell server..Doesnt matter..
>> Just seeking for advices.
>

Disk I/O

Is there a way to monitor disk I/O by database, log or even better by table
without splitting out onto separate arrays?
DanDan,
Check out:
fn_virtualfilestats
http://msdn.microsoft.com/library/d...br />
61ih.asp
HTH
Jerry
"DanR" <DanR@.discussions.microsoft.com> wrote in message
news:19FF5DFB-A29E-43B2-B7AF-E7A3B5E844E5@.microsoft.com...
> Is there a way to monitor disk I/O by database, log or even better by
> table
> without splitting out onto separate arrays?
> Dan|||Best granularity you get is by file. Look up fn_virtualfilestats in BOL.
Note: You will need the 2004 refresh of BOL, available here:
http://www.microsoft.com/sql/techin...2000/books.mspx
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"DanR" <DanR@.discussions.microsoft.com> wrote in message
news:19FF5DFB-A29E-43B2-B7AF-E7A3B5E844E5@.microsoft.com...
> Is there a way to monitor disk I/O by database, log or even better by
> table
> without splitting out onto separate arrays?
> Dan

Disk I/O

Is there a way to monitor disk I/O by database, log or even better by table
without splitting out onto separate arrays?
Dan
Dan,
Check out:
fn_virtualfilestats
http://msdn.microsoft.com/library/de...fa-fz_61ih.asp
HTH
Jerry
"DanR" <DanR@.discussions.microsoft.com> wrote in message
news:19FF5DFB-A29E-43B2-B7AF-E7A3B5E844E5@.microsoft.com...
> Is there a way to monitor disk I/O by database, log or even better by
> table
> without splitting out onto separate arrays?
> Dan
|||Best granularity you get is by file. Look up fn_virtualfilestats in BOL.
Note: You will need the 2004 refresh of BOL, available here:
http://www.microsoft.com/sql/techinf...000/books.mspx
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"DanR" <DanR@.discussions.microsoft.com> wrote in message
news:19FF5DFB-A29E-43B2-B7AF-E7A3B5E844E5@.microsoft.com...
> Is there a way to monitor disk I/O by database, log or even better by
> table
> without splitting out onto separate arrays?
> Dan

Disk I/O

Is there a way to monitor disk I/O by database, log or even better by table
without splitting out onto separate arrays?
DanDan,
Check out:
fn_virtualfilestats
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_61ih.asp
HTH
Jerry
"DanR" <DanR@.discussions.microsoft.com> wrote in message
news:19FF5DFB-A29E-43B2-B7AF-E7A3B5E844E5@.microsoft.com...
> Is there a way to monitor disk I/O by database, log or even better by
> table
> without splitting out onto separate arrays?
> Dan|||Best granularity you get is by file. Look up fn_virtualfilestats in BOL.
Note: You will need the 2004 refresh of BOL, available here:
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.mspx
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"DanR" <DanR@.discussions.microsoft.com> wrote in message
news:19FF5DFB-A29E-43B2-B7AF-E7A3B5E844E5@.microsoft.com...
> Is there a way to monitor disk I/O by database, log or even better by
> table
> without splitting out onto separate arrays?
> Dan

Disk full.

Hi all,
I have a problem...
I use SQL server 2000,all the disk on computer is used to store data file and transaction log file, and now they are full so data can be insert or update because the data file and transaction log file can be add more or increase, please show me.
Best regard,Need to backup your transaction logs so they shirnk or truncate|||Originally posted by Turismon
Need to backup your transaction logs so they shirnk or truncate

i mean the data file not enough space to store data and size of the data file can not be increase or add more because the disk is full.
Best regard,|||Originally posted by thanhtung2003
i mean the data file not enough space to store data and size of the data file can not be increase or add more because the disk is full.
Best regard,
u need to clean up the disk and give some space to the datafile to grow.
or add a new disk and increase the space.|||Originally posted by harshal_in
u need to clean up the disk and give some space to the datafile to grow.
or add a new disk and increase the space.
thanks .

Disk Full of transactions log's

Hi,
One of my app servers has filled the c:\ drive with transactions log's.
Someone moved the log's to another disk, SQL now reports the database as
being suspect. We moved the log's back, it still reports the db as suspect?
Any help would be greatly appreciated
Rich
Hi,
take a look here:
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||reset should resolve the problem (order in sequences, try the next step
if the first one doesn't work).
1) restart the MSSQL services (starts the auto recovery)
2) detach and re-attach the database
3) restore the latest database backup
Mel

Disk Full of transactions log's

Hi,
One of my app servers has filled the c:\ drive with transactions log's.
Someone moved the log's to another disk, SQL now reports the database as
being suspect. We moved the log's back, it still reports the db as suspect?
Any help would be greatly appreciated
RichHi,
take a look here:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||reset should resolve the problem (order in sequences, try the next step
if the first one doesn't work).
1) restart the MSSQL services (starts the auto recovery)
2) detach and re-attach the database
3) restore the latest database backup
Mel

Disk Full of transactions log's

Hi,
One of my app servers has filled the c:\ drive with transactions log's.
Someone moved the log's to another disk, SQL now reports the database as
being suspect. We moved the log's back, it still reports the db as suspect?
Any help would be greatly appreciated
RichHi,
take a look here:
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||reset should resolve the problem (order in sequences, try the next step
if the first one doesn't work).
1) restart the MSSQL services (starts the auto recovery)
2) detach and re-attach the database
3) restore the latest database backup
Mel

Disk Error?

Help, I'm getting the following error that is preventing
full backups or transaction log backups of this db...
Event ID: 17052
Error:823, Severity: 24, State:3
I/O error 2(The system cannot find the file specified.)
detected during write at offset 0x000002ca6d8000 in the
file 'D:\...db.mdf'.
I ran dbcc and it did fine against this database.
However, transaction log backups continue to fail with
this error. All other databases on this server aren't
having problems. This is a standalone environment, sql
server 2000 EE. This server has been running fine for
sometime. I suspect a disk problem but would have to
present a good case for it.Are you running backups in EM? Have you tried QA.
>--Original Message--
>Help, I'm getting the following error that is preventing
>full backups or transaction log backups of this db...
>Event ID: 17052
>Error:823, Severity: 24, State:3
>I/O error 2(The system cannot find the file specified.)
>detected during write at offset 0x000002ca6d8000 in the
>file 'D:\...db.mdf'.
>I ran dbcc and it did fine against this database.
>However, transaction log backups continue to fail with
>this error. All other databases on this server aren't
>having problems. This is a standalone environment, sql
>server 2000 EE. This server has been running fine for
>sometime. I suspect a disk problem but would have to
>present a good case for it.
>.
>|||You might want to give MS PSS a call:
http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
--
Andrew J. Kelly
SQL Server MVP
"Tim Bridges" <bridgt01@.hotmail.com> wrote in message
news:03c101c3ca33$ca2ac7d0$a001280a@.phx.gbl...
> Help, I'm getting the following error that is preventing
> full backups or transaction log backups of this db...
> Event ID: 17052
> Error:823, Severity: 24, State:3
> I/O error 2(The system cannot find the file specified.)
> detected during write at offset 0x000002ca6d8000 in the
> file 'D:\...db.mdf'.
> I ran dbcc and it did fine against this database.
> However, transaction log backups continue to fail with
> this error. All other databases on this server aren't
> having problems. This is a standalone environment, sql
> server 2000 EE. This server has been running fine for
> sometime. I suspect a disk problem but would have to
> present a good case for it.|||You may want to chek the disk controllers and device drivers. There is a utility available to stress the disks. http://support.microsoft.com/default.aspx?kbid=231619. The following artcile talks about a similar problem. http://support.microsoft.com/default.aspx?kbid=826433
-- Andrew J. Kelly wrote: --
You might want to give MS PSS a call:
http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
--
Andrew J. Kelly
SQL Server MVP
"Tim Bridges" <bridgt01@.hotmail.com> wrote in message
news:03c101c3ca33$ca2ac7d0$a001280a@.phx.gbl...
> Help, I'm getting the following error that is preventing
> full backups or transaction log backups of this db...
>> Event ID: 17052
> Error:823, Severity: 24, State:3
> I/O error 2(The system cannot find the file specified.)
> detected during write at offset 0x000002ca6d8000 in the
> file 'D:\...db.mdf'.
>> I ran dbcc and it did fine against this database.
> However, transaction log backups continue to fail with
> this error. All other databases on this server aren't
> having problems. This is a standalone environment, sql
> server 2000 EE. This server has been running fine for
> sometime. I suspect a disk problem but would have to
> present a good case for it.|||Thanks for the link. We've used this utility before but
with the holidays here and this problem being on a test
server it may not be until next week before we dig into
it further. Thanks for the info.
>--Original Message--
>You may want to chek the disk controllers and device
drivers. There is a utility available to stress the
disks. http://support.microsoft.com/default.aspx?
kbid=231619. The following artcile talks about a similar
problem. http://support.microsoft.com/default.aspx?
kbid=826433
>
> -- Andrew J. Kelly wrote: --
> You might want to give MS PSS a call:
> http://support.microsoft.com/default.aspx?
scid=fh;EN-US;sql SQL Support
> http://www.mssqlserver.com/faq/general-pss.asp MS
PSS
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Tim Bridges" <bridgt01@.hotmail.com> wrote in
message
> news:03c101c3ca33$ca2ac7d0$a001280a@.phx.gbl...
> > Help, I'm getting the following error that is
preventing
> > full backups or transaction log backups of this
db...
> >> Event ID: 17052
> > Error:823, Severity: 24, State:3
> > I/O error 2(The system cannot find the file
specified.)
> > detected during write at offset 0x000002ca6d8000
in the
> > file 'D:\...db.mdf'.
> >> I ran dbcc and it did fine against this database.
> > However, transaction log backups continue to fail
with
> > this error. All other databases on this server
aren't
> > having problems. This is a standalone
environment, sql
> > server 2000 EE. This server has been running
fine for
> > sometime. I suspect a disk problem but would
have to
> > present a good case for it.
>
>.
>

Disk configuration and optimization

Hi,
If I have a RAID 1 configuration and a RAID 5 configuration, what is the
optimal way to setup all my paging, data files, log files, temp files, etc.
for optimal SQL Server Performance?
DoOptimal means different things depending on what you are optimizing for, =but some suggestions:
Lose the raid 5 and make it 0+1 if you want best combination of speed =and resilience.
If performance matters more than resilience go for raid 0 for sql server =data files (don't panic the log is going elsewhere)
If resilience matters more than performance go for raid 1 for data =files.
Then put log on raid 1 (always)
System drive on raid 1
SQL server data files on 0+ 1, 0 or 1 depending on your meaning of =optimal.
How far you go with this then depends on many other factors - how many =spindles, controllers etc
Mike John
"Do" <doduong12141214@.hotmail.com> wrote in message =news:OEa$vSi1DHA.3220@.tk2msftngp13.phx.gbl...
> Hi,
> > If I have a RAID 1 configuration and a RAID 5 configuration, what is =the
> optimal way to setup all my paging, data files, log files, temp files, =etc.
> for optimal SQL Server Performance?
> > Do
> >|||So if I switch that RAID 5 to RAID 1 then I'll have two RAID 1
configurations on two separate controllers.
On my system RAID 1, I'll have my paging file and log files?
And on my other RAID 1, I'll have the data files?
So this means that I should keep my data and log files on separate disk
configurations?
Is it okay to put paging and log files on the same disk configuration?
Do
"Mike John" <Mike.John@.knowledgepool.spamtrap.com> wrote in message
news:eYC2yli1DHA.2528@.TK2MSFTNGP10.phx.gbl...
Optimal means different things depending on what you are optimizing for, but
some suggestions:
Lose the raid 5 and make it 0+1 if you want best combination of speed and
resilience.
If performance matters more than resilience go for raid 0 for sql server
data files (don't panic the log is going elsewhere)
If resilience matters more than performance go for raid 1 for data files.
Then put log on raid 1 (always)
System drive on raid 1
SQL server data files on 0+ 1, 0 or 1 depending on your meaning of optimal.
How far you go with this then depends on many other factors - how many
spindles, controllers etc
Mike John
"Do" <doduong12141214@.hotmail.com> wrote in message
news:OEa$vSi1DHA.3220@.tk2msftngp13.phx.gbl...
> Hi,
> If I have a RAID 1 configuration and a RAID 5 configuration, what is the
> optimal way to setup all my paging, data files, log files, temp files,
etc.
> for optimal SQL Server Performance?
> Do
>|||I would always want to keep log and data on different discs both for =recovery and performance. You don't want to be paging much anyway so =access to pagefile should not be much. If tempdb use is very high then =moving that to yet another set of drives can help as well, but you need =to know a lot more about the workload to judge that.
Mike John
"Do" <doduong12141214@.hotmail.com> wrote in message =news:egC2cvi1DHA.2528@.TK2MSFTNGP09.phx.gbl...
> So if I switch that RAID 5 to RAID 1 then I'll have two RAID 1
> configurations on two separate controllers.
> > On my system RAID 1, I'll have my paging file and log files?
> And on my other RAID 1, I'll have the data files?
> > So this means that I should keep my data and log files on separate =disk
> configurations?
> Is it okay to put paging and log files on the same disk configuration?
> > Do
> > > "Mike John" <Mike.John@.knowledgepool.spamtrap.com> wrote in message
> news:eYC2yli1DHA.2528@.TK2MSFTNGP10.phx.gbl...
> Optimal means different things depending on what you are optimizing =for, but
> some suggestions:
> > Lose the raid 5 and make it 0+1 if you want best combination of speed =and
> resilience.
> > If performance matters more than resilience go for raid 0 for sql =server
> data files (don't panic the log is going elsewhere)
> If resilience matters more than performance go for raid 1 for data =files.
> > Then put log on raid 1 (always)
> System drive on raid 1
> SQL server data files on 0+ 1, 0 or 1 depending on your meaning of =optimal.
> > How far you go with this then depends on many other factors - how many
> spindles, controllers etc
> > Mike John
> > > "Do" <doduong12141214@.hotmail.com> wrote in message
> news:OEa$vSi1DHA.3220@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > If I have a RAID 1 configuration and a RAID 5 configuration, what =is the
> > optimal way to setup all my paging, data files, log files, temp =files,
> etc.
> > for optimal SQL Server Performance?
> >
> > Do
> >
> >
> >|||As mike says, using raid 10 gives the best performance, but you have to have
more hard drives... Most (non extrememe performance) folks I see simply Raid
5 the data and Raid 1 the log, and system drives.
--
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's
community of SQL Server professionals.
www.sqlpass.org
"Do" <doduong12141214@.hotmail.com> wrote in message
news:OEa$vSi1DHA.3220@.tk2msftngp13.phx.gbl...
> Hi,
> If I have a RAID 1 configuration and a RAID 5 configuration, what is the
> optimal way to setup all my paging, data files, log files, temp files,
etc.
> for optimal SQL Server Performance?
> Do
>

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

Discount-calculation

Hello everybody,

I'm busy with a website with a MySQL-database, and I want to build in a discount-calculation. Customers who log in at the have their own general discount, but there can be an exception for a few products.
I have the following tables and fields (only the ones I use in the script):

- discount
- discount_id
- product_id
- company_id
- productdiscount

- companies
- company_id
- companydiscount

- users
- user_id
- company_id

- products
- product_id
- name
- price

The first thing that has to be calculated is the companydiscount.
And for the products that have a alternative discount, I of course want to show that alternatvie discount.

I wrote the following SQL-script:

select products.product_id, products.name, products.price, discount.discount_id, discount.product_id, discount.company_id, discount.productdiscount, companies.company_id, companies.companiediscount, users.user_id, users.company_id,

(price)-(price/100*productdiscount) as alternative_discount,
(price)-(price/100*companydiscount) as general_discount

from products, companies, users, discount

where companies.company_id = users.company_id and
products.product_id = discount.producte_id

The only thing that happens now is that only the product that have an anlternative discount are showed. How can I make a script that show all the products with the general discount, but the products with the alternative discount with the right calculated discount?

I hope anyone can help me!

Many thanks,

Michael RoosWell, you should be posting this in the MySQL forum, but I can see right off that you haven't linked your Companies and Discounts tables. You are essentially running a crossjoin query, and I don't think that is your intention.

blindman

Disconnect All Ussers But Me

I have setup a small SSIS package that will regularly delete my log files by detaching the database, removing the file and then reattaching it.

Problem I'm having is that there is almost always at least one other connection to the database and I have to kill it for this to work.

Is there any TSQL command that allows me to shut down all open connections except the one the command is coming from?

There is no command like that. But you could use ALTER DATABASE to set the database in single user or restricted user mode. This may or may not work depending on the permissions of the login used by the SSIS package and how often the clients reconnect etc. Check out the ALTER DATABASE topic and see.

Btw, why are you recreating the log file periodically? What is the purpose?

|||

just to add what Umachander has already told.. read about Alter Database and Singler_User in BOL first

ALTER DATABASE SomeDatabase SET SiNGLE_USER WITH ROLLBACK IMMEDIATE;

and ofcourse answer the abovementioned questions also... very risky and unethical method of truncating and shrinking log.. not atall recommended and you should not do this. there are another /professional approach to control the growth and size of log file.

Refer this thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1272445&SiteID=1

Madhu

|||Thank you, I'll check those links out.

As for the reason, right now I inherited a database that is running some heavy SSIS packages, every time these run they do a lot of copying and deleting, in the end they inflate the log files drastically. However we don't really need the log files at all, we have all the data ready to be loaded in backup tapes should anything drastic happen.

I bet the SSIS packages can use a lot of optimization but for the time being I'm not in control of these and was instructed to take care of the logs ASAP. I already did this process manually and it worked so I figured it may be an OK temporary solution to run weekly until I got a hold of what is truly going on in this database and how to optimize it.

Heck, for the purposes of this project, if it was possible to disable logging as a permanent option, I'd do it.
|||If you aren't worried about he log files, then set the database recovery model to "simple". That will keep the logs much smaller and increase the speed of updates to the tables.

The way to shrink the log files is to use a maintenance plan. You can back it up and shrink the logs at night, which is what you are trying to do anyway.

Disconnect All Ussers But Me

I have setup a small SSIS package that will regularly delete my log files by detaching the database, removing the file and then reattaching it.

Problem I'm having is that there is almost always at least one other connection to the database and I have to kill it for this to work.

Is there any TSQL command that allows me to shut down all open connections except the one the command is coming from?

There is no command like that. But you could use ALTER DATABASE to set the database in single user or restricted user mode. This may or may not work depending on the permissions of the login used by the SSIS package and how often the clients reconnect etc. Check out the ALTER DATABASE topic and see.

Btw, why are you recreating the log file periodically? What is the purpose?

|||

just to add what Umachander has already told.. read about Alter Database and Singler_User in BOL first

ALTER DATABASE SomeDatabase SET SiNGLE_USER WITH ROLLBACK IMMEDIATE;

and ofcourse answer the abovementioned questions also... very risky and unethical method of truncating and shrinking log.. not atall recommended and you should not do this. there are another /professional approach to control the growth and size of log file.

Refer this thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1272445&SiteID=1

Madhu

|||Thank you, I'll check those links out.

As for the reason, right now I inherited a database that is running some heavy SSIS packages, every time these run they do a lot of copying and deleting, in the end they inflate the log files drastically. However we don't really need the log files at all, we have all the data ready to be loaded in backup tapes should anything drastic happen.

I bet the SSIS packages can use a lot of optimization but for the time being I'm not in control of these and was instructed to take care of the logs ASAP. I already did this process manually and it worked so I figured it may be an OK temporary solution to run weekly until I got a hold of what is truly going on in this database and how to optimize it.

Heck, for the purposes of this project, if it was possible to disable logging as a permanent option, I'd do it.|||If you aren't worried about he log files, then set the database recovery model to "simple". That will keep the logs much smaller and increase the speed of updates to the tables.

The way to shrink the log files is to use a maintenance plan. You can back it up and shrink the logs at night, which is what you are trying to do anyway.

Disaster recovery through log file alone?

A collegue (really not me ;) has accidentally emptied a table in one of his
databases by making a simple typing error in a query, only to notice after
it happened that he had forgotten to create a backup schedule for that
database.
He has full recovery enabled the log has never been shrunk, so all his data
should still be present there.
Is there any way to use that log file to get his data back up to the point
where he emptied the table?
Automatic recovery would restore everything including the deletion, so what
we need is something like the 'STOPAT' option from RESTORE LOG, except that
there's no backup so RESTORE LOG is out of the question.
Or isn't it?
hi,
"Lucvdv" <replace_name@.null.net> ha scritto nel messaggio
news:v8ads0pkece7kjeelaalribkgt6dtec1b2@.4ax.com
> A collegue (really not me ;) has accidentally emptied a table in one
> of his databases by making a simple typing error in a query, only to
> notice after it happened that he had forgotten to create a backup
> schedule for that database.
> He has full recovery enabled the log has never been shrunk, so all
> his data should still be present there.
> Is there any way to use that log file to get his data back up to the
> point where he emptied the table?
> Automatic recovery would restore everything including the deletion,
> so what we need is something like the 'STOPAT' option from RESTORE
> LOG, except that there's no backup so RESTORE LOG is out of the
> question.
> Or isn't it?
as you do not have a backup to restore from, you are aout of luck with
standard SQL Server tools... but..
but Lumigent has a tool, Log Explorer,
http://www.lumigent.com/products/le_sql.html, which can be of interest..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||On Mon, 20 Dec 2004 13:23:50 +0100, "Andrea Montanari"
<andrea.sqlDMO@.virgilio.it> wrote:

> as you do not have a backup to restore from, you are aout of luck with
> standard SQL Server tools... but..
> but Lumigent has a tool, Log Explorer,
> http://www.lumigent.com/products/le_sql.html, which can be of interest..
Thanks.
I passed it on, but I wonder if his data are going to be worth the $1850
price tag of the product ;)
|||"Lucvdv" <replace_name@.null.net> ha scritto nel messaggio
news:bsnds0to23e1rm8p2lndi1nepcq97idvej@.4ax.com
> On Mon, 20 Dec 2004 13:23:50 +0100, "Andrea Montanari"
> <andrea.sqlDMO@.virgilio.it> wrote:
>
> Thanks.
> I passed it on, but I wonder if his data are going to be worth the
> $1850 price tag of the product ;)
:D
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Disaster Recovery Tests

Currently we use Log shipping between our active site and our DR site. The
question I have is when we do DR testing at the DR site to test
recoverability, we have to recover all those Log shipped databases which
means we are exposed until our DR tests are completed.
How does one go about ensuring we do not get exposed and continue to keep
data current and yet be able to test. Does that mean I have to log ship to 2
instances ? We have so many databases with TBs of storage and now I have to
double the storage and maintenance..
Anyways, are there other ways of doing so than Log shipping ?
We are on the SAN.When you say "exposed," what do you mean? What we do is we have DNS
settings that we manually change (IP address changes, that is) during DR
tests. This in itself will prevent anybody from accessing the production
servers, unless they know the server and instance names and access the
servers directly. After DR tests, we revert to the original IP addresses of
the production servers configured on our DNS settings.
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23TgJzy4AIHA.5160@.TK2MSFTNGP05.phx.gbl...
> Currently we use Log shipping between our active site and our DR site. The
> question I have is when we do DR testing at the DR site to test
> recoverability, we have to recover all those Log shipped databases which
> means we are exposed until our DR tests are completed.
> How does one go about ensuring we do not get exposed and continue to keep
> data current and yet be able to test. Does that mean I have to log ship to
> 2 instances ? We have so many databases with TBs of storage and now I have
> to double the storage and maintenance..
> Anyways, are there other ways of doing so than Log shipping ?
> We are on the SAN.
>|||I meant exposed at the DR site by not receiving logs and being current as
the log ship databases would be recovered for DR testing..
"bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
news:u7im1C7AIHA.4476@.TK2MSFTNGP06.phx.gbl...
> When you say "exposed," what do you mean? What we do is we have DNS
> settings that we manually change (IP address changes, that is) during DR
> tests. This in itself will prevent anybody from accessing the production
> servers, unless they know the server and instance names and access the
> servers directly. After DR tests, we revert to the original IP addresses
> of the production servers configured on our DNS settings.
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:%23TgJzy4AIHA.5160@.TK2MSFTNGP05.phx.gbl...
>> Currently we use Log shipping between our active site and our DR site.
>> The question I have is when we do DR testing at the DR site to test
>> recoverability, we have to recover all those Log shipped databases which
>> means we are exposed until our DR tests are completed.
>> How does one go about ensuring we do not get exposed and continue to keep
>> data current and yet be able to test. Does that mean I have to log ship
>> to 2 instances ? We have so many databases with TBs of storage and now I
>> have to double the storage and maintenance..
>> Anyways, are there other ways of doing so than Log shipping ?
>> We are on the SAN.
>|||Hassan
RESTORE DATABASE database_name WITH RECOVERY
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23MvD1r7AIHA.2268@.TK2MSFTNGP04.phx.gbl...
>I meant exposed at the DR site by not receiving logs and being current as
>the log ship databases would be recovered for DR testing..
> "bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
> news:u7im1C7AIHA.4476@.TK2MSFTNGP06.phx.gbl...
>> When you say "exposed," what do you mean? What we do is we have DNS
>> settings that we manually change (IP address changes, that is) during DR
>> tests. This in itself will prevent anybody from accessing the production
>> servers, unless they know the server and instance names and access the
>> servers directly. After DR tests, we revert to the original IP addresses
>> of the production servers configured on our DNS settings.
>>
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:%23TgJzy4AIHA.5160@.TK2MSFTNGP05.phx.gbl...
>> Currently we use Log shipping between our active site and our DR site.
>> The question I have is when we do DR testing at the DR site to test
>> recoverability, we have to recover all those Log shipped databases which
>> means we are exposed until our DR tests are completed.
>> How does one go about ensuring we do not get exposed and continue to
>> keep data current and yet be able to test. Does that mean I have to log
>> ship to 2 instances ? We have so many databases with TBs of storage and
>> now I have to double the storage and maintenance..
>> Anyways, are there other ways of doing so than Log shipping ?
>> We are on the SAN.
>>
>

Disaster recovery - Use Replication or Log shipping

Hi,
I have applications using Sql Server 2000 database at a primary location. As
a disaster recovery i.e if Sql server at the primary location fails , i have
setup another setup with Sqlserver 2000 and applications at secondary
location.
I want to know what is best option for disaster recovery ?, is it
transaction replication or Log shipping.
Suppose i use log shipping , when the primary server goes down, manually i
switch the applications to secondary server . Now the seconday server will
have some trasnactions . When the primary server comes back how do i
propogate the transaction chnages of secondary server to primary server?
Thanks.
gdc
That depends on your particular requirements - here are some of the pros and
cons of each
Pros log shipping
all objects and permission are copied
simple to set up
there is a log shipping wizard that ships with SQL 2000 EE, and SQL 7
Resource kit has some scripts
conceptually is very simple to understand
cons
standby server database is in read only mode
standby server database must go off line as you apply the tlogs
latency can be as low as 1 minutes, but practical limit is 5 minutes
certain db operations break the log shipping chain
requires more space
can't partition - its all objects and all data or nothing
not feasible for large databases
Pros trepl
can partition to a single object and a subset of rows
short latency as low as 4 s or less.
only new transactions go across the wire
cons
each table needs a pk
can be complex to troubleshoot and restart, but all in all is almost as
reliable as log shipping
"rajani" <rajaninandula@.hotmail.com> wrote in message
news:eiHeEbbFEHA.3064@.tk2msftngp13.phx.gbl...
> Hi,
> I have applications using Sql Server 2000 database at a primary location.
As
> a disaster recovery i.e if Sql server at the primary location fails , i
have
> setup another setup with Sqlserver 2000 and applications at secondary
> location.
> I want to know what is best option for disaster recovery ?, is it
> transaction replication or Log shipping.
> Suppose i use log shipping , when the primary server goes down, manually i
> switch the applications to secondary server . Now the seconday server will
> have some trasnactions . When the primary server comes back how do i
> propogate the transaction chnages of secondary server to primary server?
> Thanks.
> gdc
>
|||I missed part of your question.
The only ways to reconcile changes which occur when the primary goes down is
to use merge replication, bi-directional transactional, or manually to
stitch together a consistent database.
It is possible to resync both databases manually, but you have to kick
everyone off both systems and then do binary checksums on all tables to
figure out which rows are out of sync.
I've had to do it and it is a chore.
Merge and bi-di trans replication makes life a lot easier.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:u$FubBdFEHA.3372@.TK2MSFTNGP10.phx.gbl...
> That depends on your particular requirements - here are some of the pros
and
> cons of each
> Pros log shipping
> all objects and permission are copied
> simple to set up
> there is a log shipping wizard that ships with SQL 2000 EE, and SQL 7
> Resource kit has some scripts
> conceptually is very simple to understand
> cons
> standby server database is in read only mode
> standby server database must go off line as you apply the tlogs
> latency can be as low as 1 minutes, but practical limit is 5 minutes
> certain db operations break the log shipping chain
> requires more space
> can't partition - its all objects and all data or nothing
> not feasible for large databases
> Pros trepl
> can partition to a single object and a subset of rows
> short latency as low as 4 s or less.
> only new transactions go across the wire
> cons
> each table needs a pk
> can be complex to troubleshoot and restart, but all in all is almost as
> reliable as log shipping
>
> "rajani" <rajaninandula@.hotmail.com> wrote in message
> news:eiHeEbbFEHA.3064@.tk2msftngp13.phx.gbl...
location.
> As
> have
i
will
server?
>
|||Although Hilary implies this, it's worth noting that there are a couple of extra points in the list of cons for transactional replication for disaster recovery:
permissions, user-defined datatypes and users not transferred
defaults removed - this may cause code to break or return unexpected results after DR.
Regards,
Paul Ibison
|||Still not clear. Suppose i transaction replication, and the primary server
fails. I disable the replication jobs on the secondary server and point my
applications to secondary server and now my secondary server is primary
server.
The applications creates some transactions on the new primary server. When
the old primary server comes back , suppose i want to revert to old primary
server .
How do i reflect the changes done on the new primary server to old primary
server ?
Suppose by some means i reflect the changes, is there any problem in
enabling the replication which was setup earlier?
thanks
gdc
"rajani" <rajaninandula@.hotmail.com> wrote in message
news:eiHeEbbFEHA.3064@.tk2msftngp13.phx.gbl...
> Hi,
> I have applications using Sql Server 2000 database at a primary location.
As
> a disaster recovery i.e if Sql server at the primary location fails , i
have
> setup another setup with Sqlserver 2000 and applications at secondary
> location.
> I want to know what is best option for disaster recovery ?, is it
> transaction replication or Log shipping.
> Suppose i use log shipping , when the primary server goes down, manually i
> switch the applications to secondary server . Now the seconday server will
> have some trasnactions . When the primary server comes back how do i
> propogate the transaction chnages of secondary server to primary server?
> Thanks.
> gdc
>

Disaster Recovery - Failing over and back - Losing publications

Hello,
I am wondering what I am doing wrong or if what I am seeing is normal
behavior.
I have manual log shipping in place (via SQL LiteSpeed).
We have a replicated database log shipped to a DR server in another
location. When I fail over to the DR site, I can still see the publications
on both sites (however the DR site is not set up for replication). When I
fail back to the main site, I lose replication for the database and all of
my agents fail upon restarting them.
Has anyone run into this and come up with a solution?
We have to fail over to the DR site next weekend for building maintenance
and I don't want to have to rebuild replication.
Thanks so much.
~lb
You might want to restore your databases with the keep replication switch.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lonnye Blake Bower" <lbower@.excellerx.com> wrote in message
news:eZclO86MFHA.4092@.tk2msftngp13.phx.gbl...
> Hello,
> I am wondering what I am doing wrong or if what I am seeing is normal
> behavior.
> I have manual log shipping in place (via SQL LiteSpeed).
> We have a replicated database log shipped to a DR server in another
> location. When I fail over to the DR site, I can still see the
publications
> on both sites (however the DR site is not set up for replication). When I
> fail back to the main site, I lose replication for the database and all of
> my agents fail upon restarting them.
> Has anyone run into this and come up with a solution?
> We have to fail over to the DR site next weekend for building maintenance
> and I don't want to have to rebuild replication.
> Thanks so much.
> ~lb
>