Last night one of my SQL Servers stopped and restarted for no reason. When
it came back up all of the files (.mdf) on one of my volumes were missing
(as were the directories/folders).
We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
XIOTech Magnitude SAN.
Has this happened to any of you? Do you have any ideas on what may have
caused this? I am currently restoring a 100gig database and feeling rather
scared about the IO subsystem.
Mike
It seems strange.Looks like you have been attacked by virus.
"Mike Johnson" <mj@.microsoft.com> wrote in message
news:OdqRhz61EHA.3576@.TK2MSFTNGP12.phx.gbl...
> Last night one of my SQL Servers stopped and restarted for no reason.
When
> it came back up all of the files (.mdf) on one of my volumes were missing
> (as were the directories/folders).
> We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
> XIOTech Magnitude SAN.
> Has this happened to any of you? Do you have any ideas on what may have
> caused this? I am currently restoring a 100gig database and feeling
rather
> scared about the IO subsystem.
>
|||I disagree. Xiotech is notorious for spurious losses of data. We use to
have that in our data center but they proved to be too unreliable. We now
use EMC Symmetrix systems and reserve a few remaining Xiotech systems for
our developement environments.
Sincerely,
Anthony Thomas
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%233YG$461EHA.3468@.TK2MSFTNGP14.phx.gbl...
Mike
It seems strange.Looks like you have been attacked by virus.
"Mike Johnson" <mj@.microsoft.com> wrote in message
news:OdqRhz61EHA.3576@.TK2MSFTNGP12.phx.gbl...
> Last night one of my SQL Servers stopped and restarted for no reason.
When
> it came back up all of the files (.mdf) on one of my volumes were missing
> (as were the directories/folders).
> We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
> XIOTech Magnitude SAN.
> Has this happened to any of you? Do you have any ideas on what may have
> caused this? I am currently restoring a 100gig database and feeling
rather
> scared about the IO subsystem.
>
Showing posts with label servers. Show all posts
Showing posts with label servers. Show all posts
Tuesday, March 27, 2012
Data file missing
Last night one of my SQL Servers stopped and restarted for no reason. When
it came back up all of the files (.mdf) on one of my volumes were missing
(as were the directories/folders).
We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
XIOTech Magnitude SAN.
Has this happened to any of you? Do you have any ideas on what may have
caused this? I am currently restoring a 100gig database and feeling rather
scared about the IO subsystem.Mike
It seems strange.Looks like you have been attacked by virus.
"Mike Johnson" <mj@.microsoft.com> wrote in message
news:OdqRhz61EHA.3576@.TK2MSFTNGP12.phx.gbl...
> Last night one of my SQL Servers stopped and restarted for no reason.
When
> it came back up all of the files (.mdf) on one of my volumes were missing
> (as were the directories/folders).
> We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
> XIOTech Magnitude SAN.
> Has this happened to any of you? Do you have any ideas on what may have
> caused this? I am currently restoring a 100gig database and feeling
rather
> scared about the IO subsystem.
>|||I disagree. Xiotech is notorious for spurious losses of data. We use to
have that in our data center but they proved to be too unreliable. We now
use EMC Symmetrix systems and reserve a few remaining Xiotech systems for
our developement environments.
Sincerely,
Anthony Thomas
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%233YG$461EHA.3468@.TK2MSFTNGP14.phx.gbl...
Mike
It seems strange.Looks like you have been attacked by virus.
"Mike Johnson" <mj@.microsoft.com> wrote in message
news:OdqRhz61EHA.3576@.TK2MSFTNGP12.phx.gbl...
> Last night one of my SQL Servers stopped and restarted for no reason.
When
> it came back up all of the files (.mdf) on one of my volumes were missing
> (as were the directories/folders).
> We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
> XIOTech Magnitude SAN.
> Has this happened to any of you? Do you have any ideas on what may have
> caused this? I am currently restoring a 100gig database and feeling
rather
> scared about the IO subsystem.
>
it came back up all of the files (.mdf) on one of my volumes were missing
(as were the directories/folders).
We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
XIOTech Magnitude SAN.
Has this happened to any of you? Do you have any ideas on what may have
caused this? I am currently restoring a 100gig database and feeling rather
scared about the IO subsystem.Mike
It seems strange.Looks like you have been attacked by virus.
"Mike Johnson" <mj@.microsoft.com> wrote in message
news:OdqRhz61EHA.3576@.TK2MSFTNGP12.phx.gbl...
> Last night one of my SQL Servers stopped and restarted for no reason.
When
> it came back up all of the files (.mdf) on one of my volumes were missing
> (as were the directories/folders).
> We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
> XIOTech Magnitude SAN.
> Has this happened to any of you? Do you have any ideas on what may have
> caused this? I am currently restoring a 100gig database and feeling
rather
> scared about the IO subsystem.
>|||I disagree. Xiotech is notorious for spurious losses of data. We use to
have that in our data center but they proved to be too unreliable. We now
use EMC Symmetrix systems and reserve a few remaining Xiotech systems for
our developement environments.
Sincerely,
Anthony Thomas
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%233YG$461EHA.3468@.TK2MSFTNGP14.phx.gbl...
Mike
It seems strange.Looks like you have been attacked by virus.
"Mike Johnson" <mj@.microsoft.com> wrote in message
news:OdqRhz61EHA.3576@.TK2MSFTNGP12.phx.gbl...
> Last night one of my SQL Servers stopped and restarted for no reason.
When
> it came back up all of the files (.mdf) on one of my volumes were missing
> (as were the directories/folders).
> We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
> XIOTech Magnitude SAN.
> Has this happened to any of you? Do you have any ideas on what may have
> caused this? I am currently restoring a 100gig database and feeling
rather
> scared about the IO subsystem.
>
Data file missing
Last night one of my SQL Servers stopped and restarted for no reason. When
it came back up all of the files (.mdf) on one of my volumes were missing
(as were the directories/folders).
We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
XIOTech Magnitude SAN.
Has this happened to any of you? Do you have any ideas on what may have
caused this? I am currently restoring a 100gig database and feeling rather
scared about the IO subsystem.Mike
It seems strange.Looks like you have been attacked by virus.
"Mike Johnson" <mj@.microsoft.com> wrote in message
news:OdqRhz61EHA.3576@.TK2MSFTNGP12.phx.gbl...
> Last night one of my SQL Servers stopped and restarted for no reason.
When
> it came back up all of the files (.mdf) on one of my volumes were missing
> (as were the directories/folders).
> We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
> XIOTech Magnitude SAN.
> Has this happened to any of you? Do you have any ideas on what may have
> caused this? I am currently restoring a 100gig database and feeling
rather
> scared about the IO subsystem.
>|||I disagree. Xiotech is notorious for spurious losses of data. We use to
have that in our data center but they proved to be too unreliable. We now
use EMC Symmetrix systems and reserve a few remaining Xiotech systems for
our developement environments.
Sincerely,
Anthony Thomas
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%233YG$461EHA.3468@.TK2MSFTNGP14.phx.gbl...
Mike
It seems strange.Looks like you have been attacked by virus.
"Mike Johnson" <mj@.microsoft.com> wrote in message
news:OdqRhz61EHA.3576@.TK2MSFTNGP12.phx.gbl...
> Last night one of my SQL Servers stopped and restarted for no reason.
When
> it came back up all of the files (.mdf) on one of my volumes were missing
> (as were the directories/folders).
> We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
> XIOTech Magnitude SAN.
> Has this happened to any of you? Do you have any ideas on what may have
> caused this? I am currently restoring a 100gig database and feeling
rather
> scared about the IO subsystem.
>
it came back up all of the files (.mdf) on one of my volumes were missing
(as were the directories/folders).
We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
XIOTech Magnitude SAN.
Has this happened to any of you? Do you have any ideas on what may have
caused this? I am currently restoring a 100gig database and feeling rather
scared about the IO subsystem.Mike
It seems strange.Looks like you have been attacked by virus.
"Mike Johnson" <mj@.microsoft.com> wrote in message
news:OdqRhz61EHA.3576@.TK2MSFTNGP12.phx.gbl...
> Last night one of my SQL Servers stopped and restarted for no reason.
When
> it came back up all of the files (.mdf) on one of my volumes were missing
> (as were the directories/folders).
> We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
> XIOTech Magnitude SAN.
> Has this happened to any of you? Do you have any ideas on what may have
> caused this? I am currently restoring a 100gig database and feeling
rather
> scared about the IO subsystem.
>|||I disagree. Xiotech is notorious for spurious losses of data. We use to
have that in our data center but they proved to be too unreliable. We now
use EMC Symmetrix systems and reserve a few remaining Xiotech systems for
our developement environments.
Sincerely,
Anthony Thomas
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%233YG$461EHA.3468@.TK2MSFTNGP14.phx.gbl...
Mike
It seems strange.Looks like you have been attacked by virus.
"Mike Johnson" <mj@.microsoft.com> wrote in message
news:OdqRhz61EHA.3576@.TK2MSFTNGP12.phx.gbl...
> Last night one of my SQL Servers stopped and restarted for no reason.
When
> it came back up all of the files (.mdf) on one of my volumes were missing
> (as were the directories/folders).
> We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
> XIOTech Magnitude SAN.
> Has this happened to any of you? Do you have any ideas on what may have
> caused this? I am currently restoring a 100gig database and feeling
rather
> scared about the IO subsystem.
>
Sunday, March 11, 2012
data content comparison
Hello:
I'm looking for a free tool or procedures
to compare data content of two tables
which belongs to different servers.
Each table does not have any indexes or primary key. I need this as
verification procedures for my
log shipping process.
Thanks,
GBTake a look at Red Gate SQL Data Compare
(http://www.red-gate.com/products/SQ...mpare/index.htm)
http://sqlservercode.blogspot.com/|||I ask it for FREE.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1135192364.052961.258020@.z14g2000cwz.googlegroups.com...
> Take a look at Red Gate SQL Data Compare
> (http://www.red-gate.com/products/SQ...mpare/index.htm)
> http://sqlservercode.blogspot.com/
>|||when you say "each table does not have any indexes or primary key" do
you mean:
-- No tables have indexes/pks or
-- Some tables do, but not all of them
and
-- They don't have a pk [or unique?] constraint defined, but they do
have a logical pk or
-- They don't even have a logical pk defined
if it's that they don't even have a logical pk defined, then how would
you know what you're comparing? you'd have to be able to determine what
data constitutes being the same between the two similar tables on
different servers. [an identity/guid is most likely irrelevant in this
situation, as the chances that two tables in different servers have the
same identity value for the same data is extremely small]
even if it's that the pk (or unique?) constraint is not defined, then
you still might have duplicates within a singe table.
most comparison tools i've seen pull the pk dynamically, so if it's not
defined, you'll also need a tool that lets you specify the pk column(s).
GB wrote:
> Hello:
> I'm looking for a free tool or procedures
> to compare data content of two tables
> which belongs to different servers.
> Each table does not have any indexes or primary key. I need this as
> verification procedures for my
> log shipping process.
> Thanks,
> GB
>
>|||Some tables do, but not all of them
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:up2tgcmBGHA.2040@.TK2MSFTNGP14.phx.gbl...
> when you say "each table does not have any indexes or primary key" do
> you mean:
> -- No tables have indexes/pks or
> -- Some tables do, but not all of them
> and
> -- They don't have a pk [or unique?] constraint defined, but they do
> have a logical pk or
> -- They don't even have a logical pk defined
> if it's that they don't even have a logical pk defined, then how would
> you know what you're comparing? you'd have to be able to determine what
> data constitutes being the same between the two similar tables on
> different servers. [an identity/guid is most likely irrelevant in this
> situation, as the chances that two tables in different servers have the
> same identity value for the same data is extremely small]
> even if it's that the pk (or unique?) constraint is not defined, then
> you still might have duplicates within a singe table.
> most comparison tools i've seen pull the pk dynamically, so if it's not
> defined, you'll also need a tool that lets you specify the pk column(s).
>
> GB wrote:|||>I ask it for FREE.
There is no FREE, one-click, easy button -- not even Staples has one of
those. So, you can invest the time in writing queries tailored to your
schema, or you can invest the money in a product that does the work for you.
I'm looking for a free tool or procedures
to compare data content of two tables
which belongs to different servers.
Each table does not have any indexes or primary key. I need this as
verification procedures for my
log shipping process.
Thanks,
GBTake a look at Red Gate SQL Data Compare
(http://www.red-gate.com/products/SQ...mpare/index.htm)
http://sqlservercode.blogspot.com/|||I ask it for FREE.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1135192364.052961.258020@.z14g2000cwz.googlegroups.com...
> Take a look at Red Gate SQL Data Compare
> (http://www.red-gate.com/products/SQ...mpare/index.htm)
> http://sqlservercode.blogspot.com/
>|||when you say "each table does not have any indexes or primary key" do
you mean:
-- No tables have indexes/pks or
-- Some tables do, but not all of them
and
-- They don't have a pk [or unique?] constraint defined, but they do
have a logical pk or
-- They don't even have a logical pk defined
if it's that they don't even have a logical pk defined, then how would
you know what you're comparing? you'd have to be able to determine what
data constitutes being the same between the two similar tables on
different servers. [an identity/guid is most likely irrelevant in this
situation, as the chances that two tables in different servers have the
same identity value for the same data is extremely small]
even if it's that the pk (or unique?) constraint is not defined, then
you still might have duplicates within a singe table.
most comparison tools i've seen pull the pk dynamically, so if it's not
defined, you'll also need a tool that lets you specify the pk column(s).
GB wrote:
> Hello:
> I'm looking for a free tool or procedures
> to compare data content of two tables
> which belongs to different servers.
> Each table does not have any indexes or primary key. I need this as
> verification procedures for my
> log shipping process.
> Thanks,
> GB
>
>|||Some tables do, but not all of them
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:up2tgcmBGHA.2040@.TK2MSFTNGP14.phx.gbl...
> when you say "each table does not have any indexes or primary key" do
> you mean:
> -- No tables have indexes/pks or
> -- Some tables do, but not all of them
> and
> -- They don't have a pk [or unique?] constraint defined, but they do
> have a logical pk or
> -- They don't even have a logical pk defined
> if it's that they don't even have a logical pk defined, then how would
> you know what you're comparing? you'd have to be able to determine what
> data constitutes being the same between the two similar tables on
> different servers. [an identity/guid is most likely irrelevant in this
> situation, as the chances that two tables in different servers have the
> same identity value for the same data is extremely small]
> even if it's that the pk (or unique?) constraint is not defined, then
> you still might have duplicates within a singe table.
> most comparison tools i've seen pull the pk dynamically, so if it's not
> defined, you'll also need a tool that lets you specify the pk column(s).
>
> GB wrote:|||>I ask it for FREE.
There is no FREE, one-click, easy button -- not even Staples has one of
those. So, you can invest the time in writing queries tailored to your
schema, or you can invest the money in a product that does the work for you.
Saturday, February 25, 2012
Data and log file naming conventions
I have several user databases across several servers.
Some of the physical data files are stored as databasename.mdf,
databasename_data.mdf...
Some of the physical log files are stored as databasename.ldf,
databasename_log.ldf...
Does any one has any naming convention suggestion on this?
Currently SQL2K. Planning to move SQL2005 when it is available.
Thanks,
HarryUse whatever convention is logical and meets your needs; the main thing
is to be consistent.
Personally, I tend to stick with the GUI default <dbname>_data.mdf &
<dbname>_log.ldf for everything I can (using default values for things,
if they're sensible & reasonable, tends to avoid the pain of having to
memorise different conventions). For secondary data files I tend to use
<dbname>n_data.ndf where n is just a simple incrementing integer (1, 2,
3...), eg. mydb_Data.mdf, mydb_Log.ldf, mydb1_Data.ndf, mydb2_Data.ndf, etc.
CREATE DATABASE <dbname>, with no extra parameters, will create the
database with <dbname>.mdf & <dbname>_log.ldf but I've always thought
that to be inconsistent (with "_log" but not "_data") so I've always
gone with the recommendation of the GUI (with the _data & _log postfixes).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
HarrySmith wrote:
>I have several user databases across several servers.
>Some of the physical data files are stored as databasename.mdf,
>databasename_data.mdf...
>Some of the physical log files are stored as databasename.ldf,
>databasename_log.ldf...
>Does any one has any naming convention suggestion on this?
>Currently SQL2K. Planning to move SQL2005 when it is available.
>Thanks,
>Harry
>
>
Some of the physical data files are stored as databasename.mdf,
databasename_data.mdf...
Some of the physical log files are stored as databasename.ldf,
databasename_log.ldf...
Does any one has any naming convention suggestion on this?
Currently SQL2K. Planning to move SQL2005 when it is available.
Thanks,
HarryUse whatever convention is logical and meets your needs; the main thing
is to be consistent.
Personally, I tend to stick with the GUI default <dbname>_data.mdf &
<dbname>_log.ldf for everything I can (using default values for things,
if they're sensible & reasonable, tends to avoid the pain of having to
memorise different conventions). For secondary data files I tend to use
<dbname>n_data.ndf where n is just a simple incrementing integer (1, 2,
3...), eg. mydb_Data.mdf, mydb_Log.ldf, mydb1_Data.ndf, mydb2_Data.ndf, etc.
CREATE DATABASE <dbname>, with no extra parameters, will create the
database with <dbname>.mdf & <dbname>_log.ldf but I've always thought
that to be inconsistent (with "_log" but not "_data") so I've always
gone with the recommendation of the GUI (with the _data & _log postfixes).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
HarrySmith wrote:
>I have several user databases across several servers.
>Some of the physical data files are stored as databasename.mdf,
>databasename_data.mdf...
>Some of the physical log files are stored as databasename.ldf,
>databasename_log.ldf...
>Does any one has any naming convention suggestion on this?
>Currently SQL2K. Planning to move SQL2005 when it is available.
>Thanks,
>Harry
>
>
Data and log file naming conventions
I have several user databases across several servers.
Some of the physical data files are stored as databasename.mdf,
databasename_data.mdf...
Some of the physical log files are stored as databasename.ldf,
databasename_log.ldf...
Does any one has any naming convention suggestion on this?
Currently SQL2K. Planning to move SQL2005 when it is available.
Thanks,
Harry
Use whatever convention is logical and meets your needs; the main thing
is to be consistent.
Personally, I tend to stick with the GUI default <dbname>_data.mdf &
<dbname>_log.ldf for everything I can (using default values for things,
if they're sensible & reasonable, tends to avoid the pain of having to
memorise different conventions). For secondary data files I tend to use
<dbname>n_data.ndf where n is just a simple incrementing integer (1, 2,
3...), eg. mydb_Data.mdf, mydb_Log.ldf, mydb1_Data.ndf, mydb2_Data.ndf, etc.
CREATE DATABASE <dbname>, with no extra parameters, will create the
database with <dbname>.mdf & <dbname>_log.ldf but I've always thought
that to be inconsistent (with "_log" but not "_data") so I've always
gone with the recommendation of the GUI (with the _data & _log postfixes).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
HarrySmith wrote:
>I have several user databases across several servers.
>Some of the physical data files are stored as databasename.mdf,
>databasename_data.mdf...
>Some of the physical log files are stored as databasename.ldf,
>databasename_log.ldf...
>Does any one has any naming convention suggestion on this?
>Currently SQL2K. Planning to move SQL2005 when it is available.
>Thanks,
>Harry
>
>
Some of the physical data files are stored as databasename.mdf,
databasename_data.mdf...
Some of the physical log files are stored as databasename.ldf,
databasename_log.ldf...
Does any one has any naming convention suggestion on this?
Currently SQL2K. Planning to move SQL2005 when it is available.
Thanks,
Harry
Use whatever convention is logical and meets your needs; the main thing
is to be consistent.
Personally, I tend to stick with the GUI default <dbname>_data.mdf &
<dbname>_log.ldf for everything I can (using default values for things,
if they're sensible & reasonable, tends to avoid the pain of having to
memorise different conventions). For secondary data files I tend to use
<dbname>n_data.ndf where n is just a simple incrementing integer (1, 2,
3...), eg. mydb_Data.mdf, mydb_Log.ldf, mydb1_Data.ndf, mydb2_Data.ndf, etc.
CREATE DATABASE <dbname>, with no extra parameters, will create the
database with <dbname>.mdf & <dbname>_log.ldf but I've always thought
that to be inconsistent (with "_log" but not "_data") so I've always
gone with the recommendation of the GUI (with the _data & _log postfixes).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
HarrySmith wrote:
>I have several user databases across several servers.
>Some of the physical data files are stored as databasename.mdf,
>databasename_data.mdf...
>Some of the physical log files are stored as databasename.ldf,
>databasename_log.ldf...
>Does any one has any naming convention suggestion on this?
>Currently SQL2K. Planning to move SQL2005 when it is available.
>Thanks,
>Harry
>
>
Data and log file naming conventions
I have several user databases across several servers.
Some of the physical data files are stored as databasename.mdf,
databasename_data.mdf...
Some of the physical log files are stored as databasename.ldf,
databasename_log.ldf...
Does any one has any naming convention suggestion on this?
Currently SQL2K. Planning to move SQL2005 when it is available.
Thanks,
HarryThis is a multi-part message in MIME format.
--020803060805070804090702
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Use whatever convention is logical and meets your needs; the main thing
is to be consistent.
Personally, I tend to stick with the GUI default <dbname>_data.mdf &
<dbname>_log.ldf for everything I can (using default values for things,
if they're sensible & reasonable, tends to avoid the pain of having to
memorise different conventions). For secondary data files I tend to use
<dbname>n_data.ndf where n is just a simple incrementing integer (1, 2,
3...), eg. mydb_Data.mdf, mydb_Log.ldf, mydb1_Data.ndf, mydb2_Data.ndf, etc.
CREATE DATABASE <dbname>, with no extra parameters, will create the
database with <dbname>.mdf & <dbname>_log.ldf but I've always thought
that to be inconsistent (with "_log" but not "_data") so I've always
gone with the recommendation of the GUI (with the _data & _log postfixes).
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
HarrySmith wrote:
>I have several user databases across several servers.
>Some of the physical data files are stored as databasename.mdf,
>databasename_data.mdf...
>Some of the physical log files are stored as databasename.ldf,
>databasename_log.ldf...
>Does any one has any naming convention suggestion on this?
>Currently SQL2K. Planning to move SQL2005 when it is available.
>Thanks,
>Harry
>
>
--020803060805070804090702
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Use whatever convention is logical and meets your needs; the main
thing is to be consistent.<br>
<br>
Personally, I tend to stick with the GUI default
<dbname>_data.mdf & <dbname>_log.ldf for everything I
can (using default values for things, if they're sensible &
reasonable, tends to avoid the pain of having to memorise different
conventions). For secondary data files I tend to use
<dbname>n_data.ndf where n is just a simple incrementing integer
(1, 2, 3...), eg. mydb_Data.mdf, mydb_Log.ldf, mydb1_Data.ndf,
mydb2_Data.ndf, etc.<br>
<br>
CREATE DATABASE <dbname>, with no extra parameters, will create
the database with <dbname>.mdf & <dbname>_log.ldf but
I've always thought that to be inconsistent (with "_log" but not
"_data") so I've always gone with the recommendation of the GUI (with
the _data & _log postfixes).<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
HarrySmith wrote:
<blockquote cite="midONj5bFAtFHA.3604@.tk2msftngp13.phx.gbl" type="cite">
<pre wrap="">I have several user databases across several servers.
Some of the physical data files are stored as databasename.mdf,
databasename_data.mdf...
Some of the physical log files are stored as databasename.ldf,
databasename_log.ldf...
Does any one has any naming convention suggestion on this?
Currently SQL2K. Planning to move SQL2005 when it is available.
Thanks,
Harry
</pre>
</blockquote>
</body>
</html>
--020803060805070804090702--
Some of the physical data files are stored as databasename.mdf,
databasename_data.mdf...
Some of the physical log files are stored as databasename.ldf,
databasename_log.ldf...
Does any one has any naming convention suggestion on this?
Currently SQL2K. Planning to move SQL2005 when it is available.
Thanks,
HarryThis is a multi-part message in MIME format.
--020803060805070804090702
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Use whatever convention is logical and meets your needs; the main thing
is to be consistent.
Personally, I tend to stick with the GUI default <dbname>_data.mdf &
<dbname>_log.ldf for everything I can (using default values for things,
if they're sensible & reasonable, tends to avoid the pain of having to
memorise different conventions). For secondary data files I tend to use
<dbname>n_data.ndf where n is just a simple incrementing integer (1, 2,
3...), eg. mydb_Data.mdf, mydb_Log.ldf, mydb1_Data.ndf, mydb2_Data.ndf, etc.
CREATE DATABASE <dbname>, with no extra parameters, will create the
database with <dbname>.mdf & <dbname>_log.ldf but I've always thought
that to be inconsistent (with "_log" but not "_data") so I've always
gone with the recommendation of the GUI (with the _data & _log postfixes).
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
HarrySmith wrote:
>I have several user databases across several servers.
>Some of the physical data files are stored as databasename.mdf,
>databasename_data.mdf...
>Some of the physical log files are stored as databasename.ldf,
>databasename_log.ldf...
>Does any one has any naming convention suggestion on this?
>Currently SQL2K. Planning to move SQL2005 when it is available.
>Thanks,
>Harry
>
>
--020803060805070804090702
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Use whatever convention is logical and meets your needs; the main
thing is to be consistent.<br>
<br>
Personally, I tend to stick with the GUI default
<dbname>_data.mdf & <dbname>_log.ldf for everything I
can (using default values for things, if they're sensible &
reasonable, tends to avoid the pain of having to memorise different
conventions). For secondary data files I tend to use
<dbname>n_data.ndf where n is just a simple incrementing integer
(1, 2, 3...), eg. mydb_Data.mdf, mydb_Log.ldf, mydb1_Data.ndf,
mydb2_Data.ndf, etc.<br>
<br>
CREATE DATABASE <dbname>, with no extra parameters, will create
the database with <dbname>.mdf & <dbname>_log.ldf but
I've always thought that to be inconsistent (with "_log" but not
"_data") so I've always gone with the recommendation of the GUI (with
the _data & _log postfixes).<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
HarrySmith wrote:
<blockquote cite="midONj5bFAtFHA.3604@.tk2msftngp13.phx.gbl" type="cite">
<pre wrap="">I have several user databases across several servers.
Some of the physical data files are stored as databasename.mdf,
databasename_data.mdf...
Some of the physical log files are stored as databasename.ldf,
databasename_log.ldf...
Does any one has any naming convention suggestion on this?
Currently SQL2K. Planning to move SQL2005 when it is available.
Thanks,
Harry
</pre>
</blockquote>
</body>
</html>
--020803060805070804090702--
Sunday, February 19, 2012
Daily backup problem
My SQL 2000 server's daily (full) backup started failing due to a lack of
disk space. This daily backup has been running for quite a while so im
surprised. The file system was full but it seems the old backup are not
being deleted even though the maintenance plan is set to ""Remove files older
than 1 day". Any ideas why this would happen? Solutions?
Paul
Old fulls, or old t-log backups? If t-log make sure every db in the plan is
set to full recovery model. If you have databases in Simple recovery, make
a separate plan for them without the t-log backup step
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
>
> My SQL 2000 server's daily (full) backup started failing due to a lack of
> disk space. This daily backup has been running for quite a while so im
> surprised. The file system was full but it seems the old backup are not
> being deleted even though the maintenance plan is set to ""Remove files
> older
> than 1 day". Any ideas why this would happen? Solutions?
>
> Paul
>
|||Old fulls.
I only do full backups.
"Kevin3NF" wrote:
> Old fulls, or old t-log backups? If t-log make sure every db in the plan is
> set to full recovery model. If you have databases in Simple recovery, make
> a separate plan for them without the t-log backup step
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
>
>
|||Paul,
The delete happens after the backup is taken. If you do not have enough
space for the backup it will not delete the old backup. Could this be your
problem?
Chris
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...[vbcol=seagreen]
> Old fulls.
> I only do full backups.
> "Kevin3NF" wrote:
|||Check to see if the account running the SQL Server Agent has permissions to
delete those fulls. Look for the maintenance plan logs in the \LOG
directory (same location as the ERRORLOG file(s) )
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...[vbcol=seagreen]
> Old fulls.
> I only do full backups.
> "Kevin3NF" wrote:
|||No there is enough space for 3 days of backups
"Chris Wood" wrote:
> Paul,
> The delete happens after the backup is taken. If you do not have enough
> space for the backup it will not delete the old backup. Could this be your
> problem?
> Chris
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
>
>
|||The account has full admin permission so that not a problem. Like I said this
has not been a problem for months. I don't see a log specifacally for
maintenance logs.
"Kevin3NF" wrote:
> Check to see if the account running the SQL Server Agent has permissions to
> delete those fulls. Look for the maintenance plan logs in the \LOG
> directory (same location as the ERRORLOG file(s) )
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
>
>
|||The account have the permissions to delete these file. No problem there. I
don't see a maintenance plan specific log file.
Paul
"Kevin3NF" wrote:
> Check to see if the account running the SQL Server Agent has permissions to
> delete those fulls. Look for the maintenance plan logs in the \LOG
> directory (same location as the ERRORLOG file(s) )
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
>
>
|||Add a "text file" option in the Reporting tab of the Database maintenance
GUI (SQL 2000)
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:8670769E-BF6B-4C1F-BE67-84C830DBF144@.microsoft.com...[vbcol=seagreen]
> The account have the permissions to delete these file. No problem there. I
> don't see a maintenance plan specific log file.
> Paul
> "Kevin3NF" wrote:
disk space. This daily backup has been running for quite a while so im
surprised. The file system was full but it seems the old backup are not
being deleted even though the maintenance plan is set to ""Remove files older
than 1 day". Any ideas why this would happen? Solutions?
Paul
Old fulls, or old t-log backups? If t-log make sure every db in the plan is
set to full recovery model. If you have databases in Simple recovery, make
a separate plan for them without the t-log backup step
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
>
> My SQL 2000 server's daily (full) backup started failing due to a lack of
> disk space. This daily backup has been running for quite a while so im
> surprised. The file system was full but it seems the old backup are not
> being deleted even though the maintenance plan is set to ""Remove files
> older
> than 1 day". Any ideas why this would happen? Solutions?
>
> Paul
>
|||Old fulls.
I only do full backups.
"Kevin3NF" wrote:
> Old fulls, or old t-log backups? If t-log make sure every db in the plan is
> set to full recovery model. If you have databases in Simple recovery, make
> a separate plan for them without the t-log backup step
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
>
>
|||Paul,
The delete happens after the backup is taken. If you do not have enough
space for the backup it will not delete the old backup. Could this be your
problem?
Chris
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...[vbcol=seagreen]
> Old fulls.
> I only do full backups.
> "Kevin3NF" wrote:
|||Check to see if the account running the SQL Server Agent has permissions to
delete those fulls. Look for the maintenance plan logs in the \LOG
directory (same location as the ERRORLOG file(s) )
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...[vbcol=seagreen]
> Old fulls.
> I only do full backups.
> "Kevin3NF" wrote:
|||No there is enough space for 3 days of backups
"Chris Wood" wrote:
> Paul,
> The delete happens after the backup is taken. If you do not have enough
> space for the backup it will not delete the old backup. Could this be your
> problem?
> Chris
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
>
>
|||The account has full admin permission so that not a problem. Like I said this
has not been a problem for months. I don't see a log specifacally for
maintenance logs.
"Kevin3NF" wrote:
> Check to see if the account running the SQL Server Agent has permissions to
> delete those fulls. Look for the maintenance plan logs in the \LOG
> directory (same location as the ERRORLOG file(s) )
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
>
>
|||The account have the permissions to delete these file. No problem there. I
don't see a maintenance plan specific log file.
Paul
"Kevin3NF" wrote:
> Check to see if the account running the SQL Server Agent has permissions to
> delete those fulls. Look for the maintenance plan logs in the \LOG
> directory (same location as the ERRORLOG file(s) )
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
>
>
|||Add a "text file" option in the Reporting tab of the Database maintenance
GUI (SQL 2000)
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:8670769E-BF6B-4C1F-BE67-84C830DBF144@.microsoft.com...[vbcol=seagreen]
> The account have the permissions to delete these file. No problem there. I
> don't see a maintenance plan specific log file.
> Paul
> "Kevin3NF" wrote:
daily backup and log shipping
Hi,
I am in the process of setting up log shipping for a large database ~ 1GB over VPN and slow WAN link.
I intend to setup and sync the servers on main office and ship after that, the warm standby db to remote site.
My question is how the daily full backups of database will afect my log shipping.
To keep this servers in sync I intend to use only daily transaction logs backups/restores. I do not want to copy a full backup of 1GB daily over the WAN. However at local site I still want to perform a daily full backup.
As I know when a full backup run it will also truncate the transaction logs, so if these large daily full backups will not be copied and restored over the WAN, the warm standby server will run out of sync. This is because a part of transaction log that will be truncated when full backup is done will not be restored to remote site.
Is there any way to do full backups after initial sync without truncating the transaction logs? Has anyone an answer to my problem?
Thank you,
Zorba
Zorba,
A full database backup does not truncate the transaction log. When you start log shipping, you can make as many full backups of the primary database as you want - it will not affect log shipping. However, you cannot make both a full database backup and a transaction log backup of the same database at the same time.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Zorba" <nospam@.nonexistent> wrote in message news:OX0Z6LemEHA.3336@.TK2MSFTNGP10.phx.gbl...
Hi,
I am in the process of setting up log shipping for a large database ~ 1GB over VPN and slow WAN link.
I intend to setup and sync the servers on main office and ship after that, the warm standby db to remote site.
My question is how the daily full backups of database will afect my log shipping.
To keep this servers in sync I intend to use only daily transaction logs backups/restores. I do not want to copy a full backup of 1GB daily over the WAN. However at local site I still want to perform a daily full backup.
As I know when a full backup run it will also truncate the transaction logs, so if these large daily full backups will not be copied and restored over the WAN, the warm standby server will run out of sync. This is because a part of transaction log that will be truncated when full backup is done will not be restored to remote site.
Is there any way to do full backups after initial sync without truncating the transaction logs? Has anyone an answer to my problem?
Thank you,
Zorba
I am in the process of setting up log shipping for a large database ~ 1GB over VPN and slow WAN link.
I intend to setup and sync the servers on main office and ship after that, the warm standby db to remote site.
My question is how the daily full backups of database will afect my log shipping.
To keep this servers in sync I intend to use only daily transaction logs backups/restores. I do not want to copy a full backup of 1GB daily over the WAN. However at local site I still want to perform a daily full backup.
As I know when a full backup run it will also truncate the transaction logs, so if these large daily full backups will not be copied and restored over the WAN, the warm standby server will run out of sync. This is because a part of transaction log that will be truncated when full backup is done will not be restored to remote site.
Is there any way to do full backups after initial sync without truncating the transaction logs? Has anyone an answer to my problem?
Thank you,
Zorba
Zorba,
A full database backup does not truncate the transaction log. When you start log shipping, you can make as many full backups of the primary database as you want - it will not affect log shipping. However, you cannot make both a full database backup and a transaction log backup of the same database at the same time.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Zorba" <nospam@.nonexistent> wrote in message news:OX0Z6LemEHA.3336@.TK2MSFTNGP10.phx.gbl...
Hi,
I am in the process of setting up log shipping for a large database ~ 1GB over VPN and slow WAN link.
I intend to setup and sync the servers on main office and ship after that, the warm standby db to remote site.
My question is how the daily full backups of database will afect my log shipping.
To keep this servers in sync I intend to use only daily transaction logs backups/restores. I do not want to copy a full backup of 1GB daily over the WAN. However at local site I still want to perform a daily full backup.
As I know when a full backup run it will also truncate the transaction logs, so if these large daily full backups will not be copied and restored over the WAN, the warm standby server will run out of sync. This is because a part of transaction log that will be truncated when full backup is done will not be restored to remote site.
Is there any way to do full backups after initial sync without truncating the transaction logs? Has anyone an answer to my problem?
Thank you,
Zorba
Friday, February 17, 2012
CXPACKET and max degree of parallelism
Hello
I have a two node cluster with two SQL Server 2000 instances. The cluster is
made of two HP RX4640 servers with 4 x Itanium2 64bits processors and
32Gbytes of RAM . The servers are running Windows 2003 Enterprise Edition 64
bits and SQL Server 2000 64 bits. Each SQL 2000 instance has about 100
databases.
I am using the sp_waitstats procedure
(http://www.sqldev.net/misc/sp_waitstats.htm) from Gert Drapers website to
analyse to waittypes from both servers.
Using the storeprocedure I notice that 20% of the total wait type is made of
CXPACKET.
Reading the SQL Server 2000 Wait Types document
(http://sqldev.net/misc/waittypes.htm) I also notice that this wait type
happens because of parallel process waits. The document also says that one
possible way to solve that problema is to reduce the number of available
processor for parallel execution.
Now my questions (since I am not an expert on parallel execution)
1) Why the CXPACKET wait type happens?
2) If I reduce the number of processor or even turn off the parallel
execution, the queries will not take longer to complete?
3) What is the best way to reduce this wait type?
Regards.
Carlos Eduardo Selonke de Souza
http://carlos.geekbunker.orgBasically, parallel threads are used to speed up the execution of queries.
The idea is that on a multi-cpu system you can get more work done in
parallel then serially. Likewise, reducing the 'max degree of parallelism'
will probably slow down your queries and maintenance tasks. You would need
to test your system thoroughly to determine if there is a performance
impact.
The way parallelism works is that during the execution of some complex query
SQL might decide that the 'cost threshold for parallelism' will be exceeded
and it will spawn multiple threads (i.e. streams) to service each query
task. It will then gather streams, and if one is waiting on a resource other
threads will be blocked as well. You will see CXPACKET waittype at that
point.
To answer your third question, you won't be able to see anything beyond
CXPACKET to determine why a particular thread is waiting. If you set maxdop
to 1 you will see a different waittype at that point that might provide clue
as to where the bottleneck is.
If you are seeing a lot of CXPACKET waittypes you should try tuning queries
(excessive parallelism is frequently caused by poorly optimized queries),
and restrict parallelism to a smaller value but greater than 1.
Adrian
"Carlos Eduardo Selonke de Souza"
<CarlosEduardoSelonkedeSouza@.discussions.microsoft.com> wrote in message
news:64E1BFEB-4D00-4A35-BE53-1864804AD70E@.microsoft.com...
> Hello
> I have a two node cluster with two SQL Server 2000 instances. The cluster
> is
> made of two HP RX4640 servers with 4 x Itanium2 64bits processors and
> 32Gbytes of RAM . The servers are running Windows 2003 Enterprise Edition
> 64
> bits and SQL Server 2000 64 bits. Each SQL 2000 instance has about 100
> databases.
> I am using the sp_waitstats procedure
> (http://www.sqldev.net/misc/sp_waitstats.htm) from Gert Drapers website to
> analyse to waittypes from both servers.
> Using the storeprocedure I notice that 20% of the total wait type is made
> of
> CXPACKET.
> Reading the SQL Server 2000 Wait Types document
> (http://sqldev.net/misc/waittypes.htm) I also notice that this wait type
> happens because of parallel process waits. The document also says that one
> possible way to solve that problema is to reduce the number of available
> processor for parallel execution.
> Now my questions (since I am not an expert on parallel execution)
> 1) Why the CXPACKET wait type happens?
> 2) If I reduce the number of processor or even turn off the parallel
> execution, the queries will not take longer to complete?
> 3) What is the best way to reduce this wait type?
> Regards.
> --
> Carlos Eduardo Selonke de Souza
> http://carlos.geekbunker.org
I have a two node cluster with two SQL Server 2000 instances. The cluster is
made of two HP RX4640 servers with 4 x Itanium2 64bits processors and
32Gbytes of RAM . The servers are running Windows 2003 Enterprise Edition 64
bits and SQL Server 2000 64 bits. Each SQL 2000 instance has about 100
databases.
I am using the sp_waitstats procedure
(http://www.sqldev.net/misc/sp_waitstats.htm) from Gert Drapers website to
analyse to waittypes from both servers.
Using the storeprocedure I notice that 20% of the total wait type is made of
CXPACKET.
Reading the SQL Server 2000 Wait Types document
(http://sqldev.net/misc/waittypes.htm) I also notice that this wait type
happens because of parallel process waits. The document also says that one
possible way to solve that problema is to reduce the number of available
processor for parallel execution.
Now my questions (since I am not an expert on parallel execution)
1) Why the CXPACKET wait type happens?
2) If I reduce the number of processor or even turn off the parallel
execution, the queries will not take longer to complete?
3) What is the best way to reduce this wait type?
Regards.
Carlos Eduardo Selonke de Souza
http://carlos.geekbunker.orgBasically, parallel threads are used to speed up the execution of queries.
The idea is that on a multi-cpu system you can get more work done in
parallel then serially. Likewise, reducing the 'max degree of parallelism'
will probably slow down your queries and maintenance tasks. You would need
to test your system thoroughly to determine if there is a performance
impact.
The way parallelism works is that during the execution of some complex query
SQL might decide that the 'cost threshold for parallelism' will be exceeded
and it will spawn multiple threads (i.e. streams) to service each query
task. It will then gather streams, and if one is waiting on a resource other
threads will be blocked as well. You will see CXPACKET waittype at that
point.
To answer your third question, you won't be able to see anything beyond
CXPACKET to determine why a particular thread is waiting. If you set maxdop
to 1 you will see a different waittype at that point that might provide clue
as to where the bottleneck is.
If you are seeing a lot of CXPACKET waittypes you should try tuning queries
(excessive parallelism is frequently caused by poorly optimized queries),
and restrict parallelism to a smaller value but greater than 1.
Adrian
"Carlos Eduardo Selonke de Souza"
<CarlosEduardoSelonkedeSouza@.discussions.microsoft.com> wrote in message
news:64E1BFEB-4D00-4A35-BE53-1864804AD70E@.microsoft.com...
> Hello
> I have a two node cluster with two SQL Server 2000 instances. The cluster
> is
> made of two HP RX4640 servers with 4 x Itanium2 64bits processors and
> 32Gbytes of RAM . The servers are running Windows 2003 Enterprise Edition
> 64
> bits and SQL Server 2000 64 bits. Each SQL 2000 instance has about 100
> databases.
> I am using the sp_waitstats procedure
> (http://www.sqldev.net/misc/sp_waitstats.htm) from Gert Drapers website to
> analyse to waittypes from both servers.
> Using the storeprocedure I notice that 20% of the total wait type is made
> of
> CXPACKET.
> Reading the SQL Server 2000 Wait Types document
> (http://sqldev.net/misc/waittypes.htm) I also notice that this wait type
> happens because of parallel process waits. The document also says that one
> possible way to solve that problema is to reduce the number of available
> processor for parallel execution.
> Now my questions (since I am not an expert on parallel execution)
> 1) Why the CXPACKET wait type happens?
> 2) If I reduce the number of processor or even turn off the parallel
> execution, the queries will not take longer to complete?
> 3) What is the best way to reduce this wait type?
> Regards.
> --
> Carlos Eduardo Selonke de Souza
> http://carlos.geekbunker.org
CXPACKET and max degree of parallelism
Hello
I have a two node cluster with two SQL Server 2000 instances. The cluster is
made of two HP RX4640 servers with 4 x Itanium2 64bits processors and
32Gbytes of RAM . The servers are running Windows 2003 Enterprise Edition 64
bits and SQL Server 2000 64 bits. Each SQL 2000 instance has about 100
databases.
I am using the sp_waitstats procedure
(http://www.sqldev.net/misc/sp_waitstats.htm) from Gert Drapers website to
analyse to waittypes from both servers.
Using the storeprocedure I notice that 20% of the total wait type is made of
CXPACKET.
Reading the SQL Server 2000 Wait Types document
(http://sqldev.net/misc/waittypes.htm) I also notice that this wait type
happens because of parallel process waits. The document also says that one
possible way to solve that problema is to reduce the number of available
processor for parallel execution.
Now my questions (since I am not an expert on parallel execution)
1) Why the CXPACKET wait type happens?
2) If I reduce the number of processor or even turn off the parallel
execution, the queries will not take longer to complete?
3) What is the best way to reduce this wait type?
Regards.
Carlos Eduardo Selonke de Souza
http://carlos.geekbunker.org
Basically, parallel threads are used to speed up the execution of queries.
The idea is that on a multi-cpu system you can get more work done in
parallel then serially. Likewise, reducing the 'max degree of parallelism'
will probably slow down your queries and maintenance tasks. You would need
to test your system thoroughly to determine if there is a performance
impact.
The way parallelism works is that during the execution of some complex query
SQL might decide that the 'cost threshold for parallelism' will be exceeded
and it will spawn multiple threads (i.e. streams) to service each query
task. It will then gather streams, and if one is waiting on a resource other
threads will be blocked as well. You will see CXPACKET waittype at that
point.
To answer your third question, you won't be able to see anything beyond
CXPACKET to determine why a particular thread is waiting. If you set maxdop
to 1 you will see a different waittype at that point that might provide clue
as to where the bottleneck is.
If you are seeing a lot of CXPACKET waittypes you should try tuning queries
(excessive parallelism is frequently caused by poorly optimized queries),
and restrict parallelism to a smaller value but greater than 1.
Adrian
"Carlos Eduardo Selonke de Souza"
<CarlosEduardoSelonkedeSouza@.discussions.microsoft .com> wrote in message
news:64E1BFEB-4D00-4A35-BE53-1864804AD70E@.microsoft.com...
> Hello
> I have a two node cluster with two SQL Server 2000 instances. The cluster
> is
> made of two HP RX4640 servers with 4 x Itanium2 64bits processors and
> 32Gbytes of RAM . The servers are running Windows 2003 Enterprise Edition
> 64
> bits and SQL Server 2000 64 bits. Each SQL 2000 instance has about 100
> databases.
> I am using the sp_waitstats procedure
> (http://www.sqldev.net/misc/sp_waitstats.htm) from Gert Drapers website to
> analyse to waittypes from both servers.
> Using the storeprocedure I notice that 20% of the total wait type is made
> of
> CXPACKET.
> Reading the SQL Server 2000 Wait Types document
> (http://sqldev.net/misc/waittypes.htm) I also notice that this wait type
> happens because of parallel process waits. The document also says that one
> possible way to solve that problema is to reduce the number of available
> processor for parallel execution.
> Now my questions (since I am not an expert on parallel execution)
> 1) Why the CXPACKET wait type happens?
> 2) If I reduce the number of processor or even turn off the parallel
> execution, the queries will not take longer to complete?
> 3) What is the best way to reduce this wait type?
> Regards.
> --
> Carlos Eduardo Selonke de Souza
> http://carlos.geekbunker.org
I have a two node cluster with two SQL Server 2000 instances. The cluster is
made of two HP RX4640 servers with 4 x Itanium2 64bits processors and
32Gbytes of RAM . The servers are running Windows 2003 Enterprise Edition 64
bits and SQL Server 2000 64 bits. Each SQL 2000 instance has about 100
databases.
I am using the sp_waitstats procedure
(http://www.sqldev.net/misc/sp_waitstats.htm) from Gert Drapers website to
analyse to waittypes from both servers.
Using the storeprocedure I notice that 20% of the total wait type is made of
CXPACKET.
Reading the SQL Server 2000 Wait Types document
(http://sqldev.net/misc/waittypes.htm) I also notice that this wait type
happens because of parallel process waits. The document also says that one
possible way to solve that problema is to reduce the number of available
processor for parallel execution.
Now my questions (since I am not an expert on parallel execution)
1) Why the CXPACKET wait type happens?
2) If I reduce the number of processor or even turn off the parallel
execution, the queries will not take longer to complete?
3) What is the best way to reduce this wait type?
Regards.
Carlos Eduardo Selonke de Souza
http://carlos.geekbunker.org
Basically, parallel threads are used to speed up the execution of queries.
The idea is that on a multi-cpu system you can get more work done in
parallel then serially. Likewise, reducing the 'max degree of parallelism'
will probably slow down your queries and maintenance tasks. You would need
to test your system thoroughly to determine if there is a performance
impact.
The way parallelism works is that during the execution of some complex query
SQL might decide that the 'cost threshold for parallelism' will be exceeded
and it will spawn multiple threads (i.e. streams) to service each query
task. It will then gather streams, and if one is waiting on a resource other
threads will be blocked as well. You will see CXPACKET waittype at that
point.
To answer your third question, you won't be able to see anything beyond
CXPACKET to determine why a particular thread is waiting. If you set maxdop
to 1 you will see a different waittype at that point that might provide clue
as to where the bottleneck is.
If you are seeing a lot of CXPACKET waittypes you should try tuning queries
(excessive parallelism is frequently caused by poorly optimized queries),
and restrict parallelism to a smaller value but greater than 1.
Adrian
"Carlos Eduardo Selonke de Souza"
<CarlosEduardoSelonkedeSouza@.discussions.microsoft .com> wrote in message
news:64E1BFEB-4D00-4A35-BE53-1864804AD70E@.microsoft.com...
> Hello
> I have a two node cluster with two SQL Server 2000 instances. The cluster
> is
> made of two HP RX4640 servers with 4 x Itanium2 64bits processors and
> 32Gbytes of RAM . The servers are running Windows 2003 Enterprise Edition
> 64
> bits and SQL Server 2000 64 bits. Each SQL 2000 instance has about 100
> databases.
> I am using the sp_waitstats procedure
> (http://www.sqldev.net/misc/sp_waitstats.htm) from Gert Drapers website to
> analyse to waittypes from both servers.
> Using the storeprocedure I notice that 20% of the total wait type is made
> of
> CXPACKET.
> Reading the SQL Server 2000 Wait Types document
> (http://sqldev.net/misc/waittypes.htm) I also notice that this wait type
> happens because of parallel process waits. The document also says that one
> possible way to solve that problema is to reduce the number of available
> processor for parallel execution.
> Now my questions (since I am not an expert on parallel execution)
> 1) Why the CXPACKET wait type happens?
> 2) If I reduce the number of processor or even turn off the parallel
> execution, the queries will not take longer to complete?
> 3) What is the best way to reduce this wait type?
> Regards.
> --
> Carlos Eduardo Selonke de Souza
> http://carlos.geekbunker.org
CXPACKET and max degree of parallelism
Hello
I have a two node cluster with two SQL Server 2000 instances. The cluster is
made of two HP RX4640 servers with 4 x Itanium2 64bits processors and
32Gbytes of RAM . The servers are running Windows 2003 Enterprise Edition 64
bits and SQL Server 2000 64 bits. Each SQL 2000 instance has about 100
databases.
I am using the sp_waitstats procedure
(http://www.sqldev.net/misc/sp_waitstats.htm) from Gert Drapers website to
analyse to waittypes from both servers.
Using the storeprocedure I notice that 20% of the total wait type is made of
CXPACKET.
Reading the SQL Server 2000 Wait Types document
(http://sqldev.net/misc/waittypes.htm) I also notice that this wait type
happens because of parallel process waits. The document also says that one
possible way to solve that problema is to reduce the number of available
processor for parallel execution.
Now my questions (since I am not an expert on parallel execution)
1) Why the CXPACKET wait type happens?
2) If I reduce the number of processor or even turn off the parallel
execution, the queries will not take longer to complete?
3) What is the best way to reduce this wait type?
Regards.
--
Carlos Eduardo Selonke de Souza
http://carlos.geekbunker.orgBasically, parallel threads are used to speed up the execution of queries.
The idea is that on a multi-cpu system you can get more work done in
parallel then serially. Likewise, reducing the 'max degree of parallelism'
will probably slow down your queries and maintenance tasks. You would need
to test your system thoroughly to determine if there is a performance
impact.
The way parallelism works is that during the execution of some complex query
SQL might decide that the 'cost threshold for parallelism' will be exceeded
and it will spawn multiple threads (i.e. streams) to service each query
task. It will then gather streams, and if one is waiting on a resource other
threads will be blocked as well. You will see CXPACKET waittype at that
point.
To answer your third question, you won't be able to see anything beyond
CXPACKET to determine why a particular thread is waiting. If you set maxdop
to 1 you will see a different waittype at that point that might provide clue
as to where the bottleneck is.
If you are seeing a lot of CXPACKET waittypes you should try tuning queries
(excessive parallelism is frequently caused by poorly optimized queries),
and restrict parallelism to a smaller value but greater than 1.
Adrian
"Carlos Eduardo Selonke de Souza"
<CarlosEduardoSelonkedeSouza@.discussions.microsoft.com> wrote in message
news:64E1BFEB-4D00-4A35-BE53-1864804AD70E@.microsoft.com...
> Hello
> I have a two node cluster with two SQL Server 2000 instances. The cluster
> is
> made of two HP RX4640 servers with 4 x Itanium2 64bits processors and
> 32Gbytes of RAM . The servers are running Windows 2003 Enterprise Edition
> 64
> bits and SQL Server 2000 64 bits. Each SQL 2000 instance has about 100
> databases.
> I am using the sp_waitstats procedure
> (http://www.sqldev.net/misc/sp_waitstats.htm) from Gert Drapers website to
> analyse to waittypes from both servers.
> Using the storeprocedure I notice that 20% of the total wait type is made
> of
> CXPACKET.
> Reading the SQL Server 2000 Wait Types document
> (http://sqldev.net/misc/waittypes.htm) I also notice that this wait type
> happens because of parallel process waits. The document also says that one
> possible way to solve that problema is to reduce the number of available
> processor for parallel execution.
> Now my questions (since I am not an expert on parallel execution)
> 1) Why the CXPACKET wait type happens?
> 2) If I reduce the number of processor or even turn off the parallel
> execution, the queries will not take longer to complete?
> 3) What is the best way to reduce this wait type?
> Regards.
> --
> Carlos Eduardo Selonke de Souza
> http://carlos.geekbunker.org
I have a two node cluster with two SQL Server 2000 instances. The cluster is
made of two HP RX4640 servers with 4 x Itanium2 64bits processors and
32Gbytes of RAM . The servers are running Windows 2003 Enterprise Edition 64
bits and SQL Server 2000 64 bits. Each SQL 2000 instance has about 100
databases.
I am using the sp_waitstats procedure
(http://www.sqldev.net/misc/sp_waitstats.htm) from Gert Drapers website to
analyse to waittypes from both servers.
Using the storeprocedure I notice that 20% of the total wait type is made of
CXPACKET.
Reading the SQL Server 2000 Wait Types document
(http://sqldev.net/misc/waittypes.htm) I also notice that this wait type
happens because of parallel process waits. The document also says that one
possible way to solve that problema is to reduce the number of available
processor for parallel execution.
Now my questions (since I am not an expert on parallel execution)
1) Why the CXPACKET wait type happens?
2) If I reduce the number of processor or even turn off the parallel
execution, the queries will not take longer to complete?
3) What is the best way to reduce this wait type?
Regards.
--
Carlos Eduardo Selonke de Souza
http://carlos.geekbunker.orgBasically, parallel threads are used to speed up the execution of queries.
The idea is that on a multi-cpu system you can get more work done in
parallel then serially. Likewise, reducing the 'max degree of parallelism'
will probably slow down your queries and maintenance tasks. You would need
to test your system thoroughly to determine if there is a performance
impact.
The way parallelism works is that during the execution of some complex query
SQL might decide that the 'cost threshold for parallelism' will be exceeded
and it will spawn multiple threads (i.e. streams) to service each query
task. It will then gather streams, and if one is waiting on a resource other
threads will be blocked as well. You will see CXPACKET waittype at that
point.
To answer your third question, you won't be able to see anything beyond
CXPACKET to determine why a particular thread is waiting. If you set maxdop
to 1 you will see a different waittype at that point that might provide clue
as to where the bottleneck is.
If you are seeing a lot of CXPACKET waittypes you should try tuning queries
(excessive parallelism is frequently caused by poorly optimized queries),
and restrict parallelism to a smaller value but greater than 1.
Adrian
"Carlos Eduardo Selonke de Souza"
<CarlosEduardoSelonkedeSouza@.discussions.microsoft.com> wrote in message
news:64E1BFEB-4D00-4A35-BE53-1864804AD70E@.microsoft.com...
> Hello
> I have a two node cluster with two SQL Server 2000 instances. The cluster
> is
> made of two HP RX4640 servers with 4 x Itanium2 64bits processors and
> 32Gbytes of RAM . The servers are running Windows 2003 Enterprise Edition
> 64
> bits and SQL Server 2000 64 bits. Each SQL 2000 instance has about 100
> databases.
> I am using the sp_waitstats procedure
> (http://www.sqldev.net/misc/sp_waitstats.htm) from Gert Drapers website to
> analyse to waittypes from both servers.
> Using the storeprocedure I notice that 20% of the total wait type is made
> of
> CXPACKET.
> Reading the SQL Server 2000 Wait Types document
> (http://sqldev.net/misc/waittypes.htm) I also notice that this wait type
> happens because of parallel process waits. The document also says that one
> possible way to solve that problema is to reduce the number of available
> processor for parallel execution.
> Now my questions (since I am not an expert on parallel execution)
> 1) Why the CXPACKET wait type happens?
> 2) If I reduce the number of processor or even turn off the parallel
> execution, the queries will not take longer to complete?
> 3) What is the best way to reduce this wait type?
> Regards.
> --
> Carlos Eduardo Selonke de Souza
> http://carlos.geekbunker.org
Subscribe to:
Posts (Atom)