We have a SQL 2000 Database in both a production and test environment.
There are numerous processes and procedures in this database that must
communicate cross-server with another SQL 2000 database. This other
server also has a corresponding production / test environment.
There are many references in my database to the linked server (dts &
sprocs). Add to this, when my database is being operated in the test
environment, the procedures and processes must reference the
corresponding test server for the other database. In other words, my
production database on my production server must reference their
production database on their production server. My database in the
testing server must reference their database in the testing server.
I'd love to keep both databases on the same server for test &
production, but for business reasons, it simply can't be done.
My problem is that when I move my production into testing (and back), I
must go through and meticulously change all the references in my
sprocs. Failure to change them all would be disasterous. Instead, I
wanted a dynamic way to do this so that the reference to the linked
server could be a variable which is set, depending on whether I'm
Production or Testing.
Unfortunately, table names in SQL statements cannot be variable so what
I end up with is a TON of dynamic SQL. Certainly there must be a
better way to do this!
Anybody have encountered this situation before?
You can use the SQL Client Network utility to create an alias for an
instance. This way you define the alias on both your test and production
servers with the same alias name but pointing at different instances. Thus
in your code you just refer to the alias name and it will work without
alteration in either environment.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143777464.390453.261130@.i39g2000cwa.googlegr oups.com...
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
>
|||Sounds like a good idea--never read up on these. So let's say you have
a SQL server somewhere out on the domain called 'FRED'. Do you define
an alias on each workstation called, for instance, 'WILMA' but it
really points to 'FRED'? If it has to be done at each Workstation, is
there a way to automate this for our 200 users?
|||bdtmike wrote:
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
Parameterize the names in the installation script. Never in the runtime
code. Create views (or synonyms in 2005) for each table outside the
current database, then reference the views in your procs. That way the
number of places that the name is specified is kept to a minimum. Don't
reference server names or database names directly in procs.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||These are linked servers so I assume they are only defined on the server. So
if you have a proc like
create proc linkcall
as
exec LINKEDSERVER.db.dbo.otherproc
and you have a linked server defined in your production environment that
points to server PROD and a linked server in your development environment
that points at DEV then rather than having the following code in PROD
create proc linkcall
as
exec PROD.db.dbo.otherproc
which won't work in dev, you create an alias on the server that this
procedure runs on e.g. LINK that points at the server PROD. You do the same
in dev but you point it at server DEV. Then your code becomes
create proc linkcall
as
exec LINK.db.dbo.otherproc
and this will work regardless of the environment. One of the points of
client aliases defined on a server are to allow you to use a logical
servername to refer to different physical server in different environments.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143829371.682086.238210@.u72g2000cwu.googlegr oups.com...
> Sounds like a good idea--never read up on these. So let's say you have
> a SQL server somewhere out on the domain called 'FRED'. Do you define
> an alias on each workstation called, for instance, 'WILMA' but it
> really points to 'FRED'? If it has to be done at each Workstation, is
> there a way to automate this for our 200 users?
>
Showing posts with label processes. Show all posts
Showing posts with label processes. Show all posts
Wednesday, March 28, 2012
Production vs. Test Environment, Linked Servers = Lots of Dynamic SQL!
We have a SQL 2000 Database in both a production and test environment.
There are numerous processes and procedures in this database that must
communicate cross-server with another SQL 2000 database. This other
server also has a corresponding production / test environment.
There are many references in my database to the linked server (dts &
sprocs). Add to this, when my database is being operated in the test
environment, the procedures and processes must reference the
corresponding test server for the other database. In other words, my
production database on my production server must reference their
production database on their production server. My database in the
testing server must reference their database in the testing server.
I'd love to keep both databases on the same server for test &
production, but for business reasons, it simply can't be done.
My problem is that when I move my production into testing (and back), I
must go through and meticulously change all the references in my
sprocs. Failure to change them all would be disasterous. Instead, I
wanted a dynamic way to do this so that the reference to the linked
server could be a variable which is set, depending on whether I'm
Production or Testing.
Unfortunately, table names in SQL statements cannot be variable so what
I end up with is a TON of dynamic SQL. Certainly there must be a
better way to do this!
Anybody have encountered this situation before?You can use the SQL Client Network utility to create an alias for an
instance. This way you define the alias on both your test and production
servers with the same alias name but pointing at different instances. Thus
in your code you just refer to the alias name and it will work without
alteration in either environment.
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143777464.390453.261130@.i39g2000cwa.googlegroups.com...
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
>|||Hi
I am not sure why you would need to change this unless the linked servers
had different names.
John
"bdtmike" wrote:
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
>|||Well, they do have separate names (they are separate servers).
John Bell wrote:
> Hi
> I am not sure why you would need to change this unless the linked servers
> had different names.
> John
> "bdtmike" wrote:
> > We have a SQL 2000 Database in both a production and test environment.
> > There are numerous processes and procedures in this database that must
> > communicate cross-server with another SQL 2000 database. This other
> > server also has a corresponding production / test environment.
> >
> > There are many references in my database to the linked server (dts &
> > sprocs). Add to this, when my database is being operated in the test
> > environment, the procedures and processes must reference the
> > corresponding test server for the other database. In other words, my
> > production database on my production server must reference their
> > production database on their production server. My database in the
> > testing server must reference their database in the testing server.
> >
> > I'd love to keep both databases on the same server for test &
> > production, but for business reasons, it simply can't be done.
> >
> > My problem is that when I move my production into testing (and back), I
> > must go through and meticulously change all the references in my
> > sprocs. Failure to change them all would be disasterous. Instead, I
> > wanted a dynamic way to do this so that the reference to the linked
> > server could be a variable which is set, depending on whether I'm
> > Production or Testing.
> >
> > Unfortunately, table names in SQL statements cannot be variable so what
> > I end up with is a TON of dynamic SQL. Certainly there must be a
> > better way to do this!
> >
> > Anybody have encountered this situation before?
> >
> >|||Sounds like a good idea--never read up on these. So let's say you have
a SQL server somewhere out on the domain called 'FRED'. Do you define
an alias on each workstation called, for instance, 'WILMA' but it
really points to 'FRED'? If it has to be done at each Workstation, is
there a way to automate this for our 200 users?|||bdtmike wrote:
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
Parameterize the names in the installation script. Never in the runtime
code. Create views (or synonyms in 2005) for each table outside the
current database, then reference the views in your procs. That way the
number of places that the name is specified is kept to a minimum. Don't
reference server names or database names directly in procs.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||These are linked servers so I assume they are only defined on the server. So
if you have a proc like
create proc linkcall
as
exec LINKEDSERVER.db.dbo.otherproc
and you have a linked server defined in your production environment that
points to server PROD and a linked server in your development environment
that points at DEV then rather than having the following code in PROD
create proc linkcall
as
exec PROD.db.dbo.otherproc
which won't work in dev, you create an alias on the server that this
procedure runs on e.g. LINK that points at the server PROD. You do the same
in dev but you point it at server DEV. Then your code becomes
create proc linkcall
as
exec LINK.db.dbo.otherproc
and this will work regardless of the environment. One of the points of
client aliases defined on a server are to allow you to use a logical
servername to refer to different physical server in different environments.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143829371.682086.238210@.u72g2000cwu.googlegroups.com...
> Sounds like a good idea--never read up on these. So let's say you have
> a SQL server somewhere out on the domain called 'FRED'. Do you define
> an alias on each workstation called, for instance, 'WILMA' but it
> really points to 'FRED'? If it has to be done at each Workstation, is
> there a way to automate this for our 200 users?
>|||But the linked server name can be the same!
Check out example 2 in the sp_addlinkedserver examples in BOL, specify
different data sources and the same linked server name.
John
"bdtmike" wrote:
> Well, they do have separate names (they are separate servers).
> John Bell wrote:
> > Hi
> >
> > I am not sure why you would need to change this unless the linked servers
> > had different names.
> >
> > John
> >
> > "bdtmike" wrote:
> >
> > > We have a SQL 2000 Database in both a production and test environment.
> > > There are numerous processes and procedures in this database that must
> > > communicate cross-server with another SQL 2000 database. This other
> > > server also has a corresponding production / test environment.
> > >
> > > There are many references in my database to the linked server (dts &
> > > sprocs). Add to this, when my database is being operated in the test
> > > environment, the procedures and processes must reference the
> > > corresponding test server for the other database. In other words, my
> > > production database on my production server must reference their
> > > production database on their production server. My database in the
> > > testing server must reference their database in the testing server.
> > >
> > > I'd love to keep both databases on the same server for test &
> > > production, but for business reasons, it simply can't be done.
> > >
> > > My problem is that when I move my production into testing (and back), I
> > > must go through and meticulously change all the references in my
> > > sprocs. Failure to change them all would be disasterous. Instead, I
> > > wanted a dynamic way to do this so that the reference to the linked
> > > server could be a variable which is set, depending on whether I'm
> > > Production or Testing.
> > >
> > > Unfortunately, table names in SQL statements cannot be variable so what
> > > I end up with is a TON of dynamic SQL. Certainly there must be a
> > > better way to do this!
> > >
> > > Anybody have encountered this situation before?
> > >
> > >
>|||Yes, in theory they could. But I'm saying that in my situation they
arent' the same name. And the chance that I'm going to get this
organization to change their server name just for me is...Zero.|||Any changes to code after it has been tested introduces a risk which they
should be made aware off. If this risk is removed by having a better
configuration then it should be acceptable.
John
"bdtmike" wrote:
> Yes, in theory they could. But I'm saying that in my situation they
> arent' the same name. And the chance that I'm going to get this
> organization to change their server name just for me is...Zero.
>
There are numerous processes and procedures in this database that must
communicate cross-server with another SQL 2000 database. This other
server also has a corresponding production / test environment.
There are many references in my database to the linked server (dts &
sprocs). Add to this, when my database is being operated in the test
environment, the procedures and processes must reference the
corresponding test server for the other database. In other words, my
production database on my production server must reference their
production database on their production server. My database in the
testing server must reference their database in the testing server.
I'd love to keep both databases on the same server for test &
production, but for business reasons, it simply can't be done.
My problem is that when I move my production into testing (and back), I
must go through and meticulously change all the references in my
sprocs. Failure to change them all would be disasterous. Instead, I
wanted a dynamic way to do this so that the reference to the linked
server could be a variable which is set, depending on whether I'm
Production or Testing.
Unfortunately, table names in SQL statements cannot be variable so what
I end up with is a TON of dynamic SQL. Certainly there must be a
better way to do this!
Anybody have encountered this situation before?You can use the SQL Client Network utility to create an alias for an
instance. This way you define the alias on both your test and production
servers with the same alias name but pointing at different instances. Thus
in your code you just refer to the alias name and it will work without
alteration in either environment.
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143777464.390453.261130@.i39g2000cwa.googlegroups.com...
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
>|||Hi
I am not sure why you would need to change this unless the linked servers
had different names.
John
"bdtmike" wrote:
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
>|||Well, they do have separate names (they are separate servers).
John Bell wrote:
> Hi
> I am not sure why you would need to change this unless the linked servers
> had different names.
> John
> "bdtmike" wrote:
> > We have a SQL 2000 Database in both a production and test environment.
> > There are numerous processes and procedures in this database that must
> > communicate cross-server with another SQL 2000 database. This other
> > server also has a corresponding production / test environment.
> >
> > There are many references in my database to the linked server (dts &
> > sprocs). Add to this, when my database is being operated in the test
> > environment, the procedures and processes must reference the
> > corresponding test server for the other database. In other words, my
> > production database on my production server must reference their
> > production database on their production server. My database in the
> > testing server must reference their database in the testing server.
> >
> > I'd love to keep both databases on the same server for test &
> > production, but for business reasons, it simply can't be done.
> >
> > My problem is that when I move my production into testing (and back), I
> > must go through and meticulously change all the references in my
> > sprocs. Failure to change them all would be disasterous. Instead, I
> > wanted a dynamic way to do this so that the reference to the linked
> > server could be a variable which is set, depending on whether I'm
> > Production or Testing.
> >
> > Unfortunately, table names in SQL statements cannot be variable so what
> > I end up with is a TON of dynamic SQL. Certainly there must be a
> > better way to do this!
> >
> > Anybody have encountered this situation before?
> >
> >|||Sounds like a good idea--never read up on these. So let's say you have
a SQL server somewhere out on the domain called 'FRED'. Do you define
an alias on each workstation called, for instance, 'WILMA' but it
really points to 'FRED'? If it has to be done at each Workstation, is
there a way to automate this for our 200 users?|||bdtmike wrote:
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
Parameterize the names in the installation script. Never in the runtime
code. Create views (or synonyms in 2005) for each table outside the
current database, then reference the views in your procs. That way the
number of places that the name is specified is kept to a minimum. Don't
reference server names or database names directly in procs.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||These are linked servers so I assume they are only defined on the server. So
if you have a proc like
create proc linkcall
as
exec LINKEDSERVER.db.dbo.otherproc
and you have a linked server defined in your production environment that
points to server PROD and a linked server in your development environment
that points at DEV then rather than having the following code in PROD
create proc linkcall
as
exec PROD.db.dbo.otherproc
which won't work in dev, you create an alias on the server that this
procedure runs on e.g. LINK that points at the server PROD. You do the same
in dev but you point it at server DEV. Then your code becomes
create proc linkcall
as
exec LINK.db.dbo.otherproc
and this will work regardless of the environment. One of the points of
client aliases defined on a server are to allow you to use a logical
servername to refer to different physical server in different environments.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143829371.682086.238210@.u72g2000cwu.googlegroups.com...
> Sounds like a good idea--never read up on these. So let's say you have
> a SQL server somewhere out on the domain called 'FRED'. Do you define
> an alias on each workstation called, for instance, 'WILMA' but it
> really points to 'FRED'? If it has to be done at each Workstation, is
> there a way to automate this for our 200 users?
>|||But the linked server name can be the same!
Check out example 2 in the sp_addlinkedserver examples in BOL, specify
different data sources and the same linked server name.
John
"bdtmike" wrote:
> Well, they do have separate names (they are separate servers).
> John Bell wrote:
> > Hi
> >
> > I am not sure why you would need to change this unless the linked servers
> > had different names.
> >
> > John
> >
> > "bdtmike" wrote:
> >
> > > We have a SQL 2000 Database in both a production and test environment.
> > > There are numerous processes and procedures in this database that must
> > > communicate cross-server with another SQL 2000 database. This other
> > > server also has a corresponding production / test environment.
> > >
> > > There are many references in my database to the linked server (dts &
> > > sprocs). Add to this, when my database is being operated in the test
> > > environment, the procedures and processes must reference the
> > > corresponding test server for the other database. In other words, my
> > > production database on my production server must reference their
> > > production database on their production server. My database in the
> > > testing server must reference their database in the testing server.
> > >
> > > I'd love to keep both databases on the same server for test &
> > > production, but for business reasons, it simply can't be done.
> > >
> > > My problem is that when I move my production into testing (and back), I
> > > must go through and meticulously change all the references in my
> > > sprocs. Failure to change them all would be disasterous. Instead, I
> > > wanted a dynamic way to do this so that the reference to the linked
> > > server could be a variable which is set, depending on whether I'm
> > > Production or Testing.
> > >
> > > Unfortunately, table names in SQL statements cannot be variable so what
> > > I end up with is a TON of dynamic SQL. Certainly there must be a
> > > better way to do this!
> > >
> > > Anybody have encountered this situation before?
> > >
> > >
>|||Yes, in theory they could. But I'm saying that in my situation they
arent' the same name. And the chance that I'm going to get this
organization to change their server name just for me is...Zero.|||Any changes to code after it has been tested introduces a risk which they
should be made aware off. If this risk is removed by having a better
configuration then it should be acceptable.
John
"bdtmike" wrote:
> Yes, in theory they could. But I'm saying that in my situation they
> arent' the same name. And the chance that I'm going to get this
> organization to change their server name just for me is...Zero.
>
Production vs. Test Environment, Linked Servers = Lots of Dynamic SQL!
We have a SQL 2000 Database in both a production and test environment.
There are numerous processes and procedures in this database that must
communicate cross-server with another SQL 2000 database. This other
server also has a corresponding production / test environment.
There are many references in my database to the linked server (dts &
sprocs). Add to this, when my database is being operated in the test
environment, the procedures and processes must reference the
corresponding test server for the other database. In other words, my
production database on my production server must reference their
production database on their production server. My database in the
testing server must reference their database in the testing server.
I'd love to keep both databases on the same server for test &
production, but for business reasons, it simply can't be done.
My problem is that when I move my production into testing (and back), I
must go through and meticulously change all the references in my
sprocs. Failure to change them all would be disasterous. Instead, I
wanted a dynamic way to do this so that the reference to the linked
server could be a variable which is set, depending on whether I'm
Production or Testing.
Unfortunately, table names in SQL statements cannot be variable so what
I end up with is a TON of dynamic SQL. Certainly there must be a
better way to do this!
Anybody have encountered this situation before?You can use the SQL Client Network utility to create an alias for an
instance. This way you define the alias on both your test and production
servers with the same alias name but pointing at different instances. Thus
in your code you just refer to the alias name and it will work without
alteration in either environment.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143777464.390453.261130@.i39g2000cwa.googlegroups.com...
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
>|||Sounds like a good idea--never read up on these. So let's say you have
a SQL server somewhere out on the domain called 'FRED'. Do you define
an alias on each workstation called, for instance, 'WILMA' but it
really points to 'FRED'? If it has to be done at each Workstation, is
there a way to automate this for our 200 users?|||bdtmike wrote:
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
Parameterize the names in the installation script. Never in the runtime
code. Create views (or synonyms in 2005) for each table outside the
current database, then reference the views in your procs. That way the
number of places that the name is specified is kept to a minimum. Don't
reference server names or database names directly in procs.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||These are linked servers so I assume they are only defined on the server. So
if you have a proc like
create proc linkcall
as
exec LINKEDSERVER.db.dbo.otherproc
and you have a linked server defined in your production environment that
points to server PROD and a linked server in your development environment
that points at DEV then rather than having the following code in PROD
create proc linkcall
as
exec PROD.db.dbo.otherproc
which won't work in dev, you create an alias on the server that this
procedure runs on e.g. LINK that points at the server PROD. You do the same
in dev but you point it at server DEV. Then your code becomes
create proc linkcall
as
exec LINK.db.dbo.otherproc
and this will work regardless of the environment. One of the points of
client aliases defined on a server are to allow you to use a logical
servername to refer to different physical server in different environments.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143829371.682086.238210@.u72g2000cwu.googlegroups.com...
> Sounds like a good idea--never read up on these. So let's say you have
> a SQL server somewhere out on the domain called 'FRED'. Do you define
> an alias on each workstation called, for instance, 'WILMA' but it
> really points to 'FRED'? If it has to be done at each Workstation, is
> there a way to automate this for our 200 users?
>
There are numerous processes and procedures in this database that must
communicate cross-server with another SQL 2000 database. This other
server also has a corresponding production / test environment.
There are many references in my database to the linked server (dts &
sprocs). Add to this, when my database is being operated in the test
environment, the procedures and processes must reference the
corresponding test server for the other database. In other words, my
production database on my production server must reference their
production database on their production server. My database in the
testing server must reference their database in the testing server.
I'd love to keep both databases on the same server for test &
production, but for business reasons, it simply can't be done.
My problem is that when I move my production into testing (and back), I
must go through and meticulously change all the references in my
sprocs. Failure to change them all would be disasterous. Instead, I
wanted a dynamic way to do this so that the reference to the linked
server could be a variable which is set, depending on whether I'm
Production or Testing.
Unfortunately, table names in SQL statements cannot be variable so what
I end up with is a TON of dynamic SQL. Certainly there must be a
better way to do this!
Anybody have encountered this situation before?You can use the SQL Client Network utility to create an alias for an
instance. This way you define the alias on both your test and production
servers with the same alias name but pointing at different instances. Thus
in your code you just refer to the alias name and it will work without
alteration in either environment.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143777464.390453.261130@.i39g2000cwa.googlegroups.com...
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
>|||Sounds like a good idea--never read up on these. So let's say you have
a SQL server somewhere out on the domain called 'FRED'. Do you define
an alias on each workstation called, for instance, 'WILMA' but it
really points to 'FRED'? If it has to be done at each Workstation, is
there a way to automate this for our 200 users?|||bdtmike wrote:
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
Parameterize the names in the installation script. Never in the runtime
code. Create views (or synonyms in 2005) for each table outside the
current database, then reference the views in your procs. That way the
number of places that the name is specified is kept to a minimum. Don't
reference server names or database names directly in procs.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||These are linked servers so I assume they are only defined on the server. So
if you have a proc like
create proc linkcall
as
exec LINKEDSERVER.db.dbo.otherproc
and you have a linked server defined in your production environment that
points to server PROD and a linked server in your development environment
that points at DEV then rather than having the following code in PROD
create proc linkcall
as
exec PROD.db.dbo.otherproc
which won't work in dev, you create an alias on the server that this
procedure runs on e.g. LINK that points at the server PROD. You do the same
in dev but you point it at server DEV. Then your code becomes
create proc linkcall
as
exec LINK.db.dbo.otherproc
and this will work regardless of the environment. One of the points of
client aliases defined on a server are to allow you to use a logical
servername to refer to different physical server in different environments.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143829371.682086.238210@.u72g2000cwu.googlegroups.com...
> Sounds like a good idea--never read up on these. So let's say you have
> a SQL server somewhere out on the domain called 'FRED'. Do you define
> an alias on each workstation called, for instance, 'WILMA' but it
> really points to 'FRED'? If it has to be done at each Workstation, is
> there a way to automate this for our 200 users?
>
Monday, March 12, 2012
Processing Cubes via a SQL Server 2000 DTS
Hi,
I have a DTS which processes a set of AS 2000 cubes via a set of AS Processing Tasks.
I want to know how I can accurately trap and report errors once a cube fails to process?
Thanks in advance
Jon Derbyshire
The logging of processing errors in DTS ( SQL Server 2000 ) is bit limited.
You should try and look at migrating your application to SQL Server 2005 instead. I know it is bit a far fetched suggestion to migrate your whole solution to SQL Server 2005 just because of a single problem with accurately tracking processing errors. But the problem is: your will find increasingly harder to implement any new functionality with old version of the product. You will find it harder to find answers to your questions in the community that has moved to new ( different) technologies. At some point you should look at developing new solution using SQL Server 2005.
Hope that helps.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Processes seen in enterprise manager
Hi,
We are using a new third party application that has SQL Server 2000 as the
database. It is an ASP page front end and uses ODBC to connect. There are
about 20 people who use it during the day. When I look in EM at the
processes there are well over 100. Even in the morning after everyone has
logged out the night before. All the processes are sleeping so they aren't
using any resources. I feel kind of dumb here but is there a server
property setting where I can set a value for these to expire? Looked in BOL
and in my other books but this doesn't seem to be available. Only timeout
settings when waiting for a connection or running a query. I wasn't worried
about these thinking SQL Server was managing them but then I noticed that
our in house application that uses the same type of setup doesn't have this
problem.
Thanks,
Wayne
I have worked with one third party application whose idea of connection
pooling was to open up 100 connections on start-up, even though it never
used more than 2 during the time we used it. Your third party application
might have been designed by a similarly brilliant and knowledgeable
developer.
You can't set a timeout for the connections, but you can schedule a job to
run the following script on a regular basis. This example kills all
connections that have not been used for 6 hours:
DECLARE @.sql varchar(4000)
WHILE 1=1
BEGIN
SET @.sql = (SELECT TOP 1 'KILL ' + CAST(spid AS VARCHAR(10))
FROM master.dbo.sysprocesses WHERE DATEDIFF(hh, last_batch,
GETDATE()) >= 6
AND spid <> @.@.spid AND spid >= 50)
IF @.sql IS NULL BREAK
EXEC (@.sql)
END
Jacco Schalkwijk
SQL Server MVP
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:OPecmwqLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi,
> We are using a new third party application that has SQL Server 2000 as the
> database. It is an ASP page front end and uses ODBC to connect. There
are
> about 20 people who use it during the day. When I look in EM at the
> processes there are well over 100. Even in the morning after everyone has
> logged out the night before. All the processes are sleeping so they
aren't
> using any resources. I feel kind of dumb here but is there a server
> property setting where I can set a value for these to expire? Looked in
BOL
> and in my other books but this doesn't seem to be available. Only timeout
> settings when waiting for a connection or running a query. I wasn't
worried
> about these thinking SQL Server was managing them but then I noticed that
> our in house application that uses the same type of setup doesn't have
this
> problem.
> Thanks,
> Wayne
>
|||Thanks Jacco,
I'll give the script a try. Based on other things I've seen with this app I
would not be surprised if they are doing something similar.
Thanks again,
Wayne
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23m6mO%23qLEHA.3516@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> I have worked with one third party application whose idea of connection
> pooling was to open up 100 connections on start-up, even though it never
> used more than 2 during the time we used it. Your third party application
> might have been designed by a similarly brilliant and knowledgeable
> developer.
> You can't set a timeout for the connections, but you can schedule a job to
> run the following script on a regular basis. This example kills all
> connections that have not been used for 6 hours:
> DECLARE @.sql varchar(4000)
> WHILE 1=1
> BEGIN
> SET @.sql = (SELECT TOP 1 'KILL ' + CAST(spid AS VARCHAR(10))
> FROM master.dbo.sysprocesses WHERE DATEDIFF(hh, last_batch,
> GETDATE()) >= 6
> AND spid <> @.@.spid AND spid >= 50)
> IF @.sql IS NULL BREAK
> EXEC (@.sql)
> END
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> news:OPecmwqLEHA.1340@.TK2MSFTNGP12.phx.gbl...
the[vbcol=seagreen]
> are
has[vbcol=seagreen]
> aren't
> BOL
timeout[vbcol=seagreen]
> worried
that
> this
>
|||Ok, don't be surprised though if the application either fails to work when
it's 100 or so connections aren't there, or just recreates them on a regular
basis after you have killed them....
Jacco Schalkwijk
SQL Server MVP
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:uJQkvJrLEHA.3904@.TK2MSFTNGP09.phx.gbl...
> Thanks Jacco,
> I'll give the script a try. Based on other things I've seen with this app
I[vbcol=seagreen]
> would not be surprised if they are doing something similar.
> Thanks again,
> Wayne
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:%23m6mO%23qLEHA.3516@.TK2MSFTNGP11.phx.gbl...
application[vbcol=seagreen]
to[vbcol=seagreen]
> the
There[vbcol=seagreen]
> has
in
> timeout
> that
>
We are using a new third party application that has SQL Server 2000 as the
database. It is an ASP page front end and uses ODBC to connect. There are
about 20 people who use it during the day. When I look in EM at the
processes there are well over 100. Even in the morning after everyone has
logged out the night before. All the processes are sleeping so they aren't
using any resources. I feel kind of dumb here but is there a server
property setting where I can set a value for these to expire? Looked in BOL
and in my other books but this doesn't seem to be available. Only timeout
settings when waiting for a connection or running a query. I wasn't worried
about these thinking SQL Server was managing them but then I noticed that
our in house application that uses the same type of setup doesn't have this
problem.
Thanks,
Wayne
I have worked with one third party application whose idea of connection
pooling was to open up 100 connections on start-up, even though it never
used more than 2 during the time we used it. Your third party application
might have been designed by a similarly brilliant and knowledgeable
developer.
You can't set a timeout for the connections, but you can schedule a job to
run the following script on a regular basis. This example kills all
connections that have not been used for 6 hours:
DECLARE @.sql varchar(4000)
WHILE 1=1
BEGIN
SET @.sql = (SELECT TOP 1 'KILL ' + CAST(spid AS VARCHAR(10))
FROM master.dbo.sysprocesses WHERE DATEDIFF(hh, last_batch,
GETDATE()) >= 6
AND spid <> @.@.spid AND spid >= 50)
IF @.sql IS NULL BREAK
EXEC (@.sql)
END
Jacco Schalkwijk
SQL Server MVP
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:OPecmwqLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi,
> We are using a new third party application that has SQL Server 2000 as the
> database. It is an ASP page front end and uses ODBC to connect. There
are
> about 20 people who use it during the day. When I look in EM at the
> processes there are well over 100. Even in the morning after everyone has
> logged out the night before. All the processes are sleeping so they
aren't
> using any resources. I feel kind of dumb here but is there a server
> property setting where I can set a value for these to expire? Looked in
BOL
> and in my other books but this doesn't seem to be available. Only timeout
> settings when waiting for a connection or running a query. I wasn't
worried
> about these thinking SQL Server was managing them but then I noticed that
> our in house application that uses the same type of setup doesn't have
this
> problem.
> Thanks,
> Wayne
>
|||Thanks Jacco,
I'll give the script a try. Based on other things I've seen with this app I
would not be surprised if they are doing something similar.
Thanks again,
Wayne
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23m6mO%23qLEHA.3516@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> I have worked with one third party application whose idea of connection
> pooling was to open up 100 connections on start-up, even though it never
> used more than 2 during the time we used it. Your third party application
> might have been designed by a similarly brilliant and knowledgeable
> developer.
> You can't set a timeout for the connections, but you can schedule a job to
> run the following script on a regular basis. This example kills all
> connections that have not been used for 6 hours:
> DECLARE @.sql varchar(4000)
> WHILE 1=1
> BEGIN
> SET @.sql = (SELECT TOP 1 'KILL ' + CAST(spid AS VARCHAR(10))
> FROM master.dbo.sysprocesses WHERE DATEDIFF(hh, last_batch,
> GETDATE()) >= 6
> AND spid <> @.@.spid AND spid >= 50)
> IF @.sql IS NULL BREAK
> EXEC (@.sql)
> END
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> news:OPecmwqLEHA.1340@.TK2MSFTNGP12.phx.gbl...
the[vbcol=seagreen]
> are
has[vbcol=seagreen]
> aren't
> BOL
timeout[vbcol=seagreen]
> worried
that
> this
>
|||Ok, don't be surprised though if the application either fails to work when
it's 100 or so connections aren't there, or just recreates them on a regular
basis after you have killed them....
Jacco Schalkwijk
SQL Server MVP
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:uJQkvJrLEHA.3904@.TK2MSFTNGP09.phx.gbl...
> Thanks Jacco,
> I'll give the script a try. Based on other things I've seen with this app
I[vbcol=seagreen]
> would not be surprised if they are doing something similar.
> Thanks again,
> Wayne
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:%23m6mO%23qLEHA.3516@.TK2MSFTNGP11.phx.gbl...
application[vbcol=seagreen]
to[vbcol=seagreen]
> the
There[vbcol=seagreen]
> has
in
> timeout
> that
>
Friday, March 9, 2012
Processes seen in enterprise manager
Hi,
We are using a new third party application that has SQL Server 2000 as the
database. It is an ASP page front end and uses ODBC to connect. There are
about 20 people who use it during the day. When I look in EM at the
processes there are well over 100. Even in the morning after everyone has
logged out the night before. All the processes are sleeping so they aren't
using any resources. I feel kind of dumb here but is there a server
property setting where I can set a value for these to expire? Looked in BOL
and in my other books but this doesn't seem to be available. Only timeout
settings when waiting for a connection or running a query. I wasn't worried
about these thinking SQL Server was managing them but then I noticed that
our in house application that uses the same type of setup doesn't have this
problem.
Thanks,
WayneI have worked with one third party application whose idea of connection
pooling was to open up 100 connections on start-up, even though it never
used more than 2 during the time we used it. Your third party application
might have been designed by a similarly brilliant and knowledgeable
developer.
You can't set a timeout for the connections, but you can schedule a job to
run the following script on a regular basis. This example kills all
connections that have not been used for 6 hours:
DECLARE @.sql varchar(4000)
WHILE 1=1
BEGIN
SET @.sql = (SELECT TOP 1 'KILL ' + CAST(spid AS VARCHAR(10))
FROM master.dbo.sysprocesses WHERE DATEDIFF(hh, last_batch,
GETDATE()) >= 6
AND spid <> @.@.spid AND spid >= 50)
IF @.sql IS NULL BREAK
EXEC (@.sql)
END
Jacco Schalkwijk
SQL Server MVP
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:OPecmwqLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi,
> We are using a new third party application that has SQL Server 2000 as the
> database. It is an ASP page front end and uses ODBC to connect. There
are
> about 20 people who use it during the day. When I look in EM at the
> processes there are well over 100. Even in the morning after everyone has
> logged out the night before. All the processes are sleeping so they
aren't
> using any resources. I feel kind of dumb here but is there a server
> property setting where I can set a value for these to expire? Looked in
BOL
> and in my other books but this doesn't seem to be available. Only timeout
> settings when waiting for a connection or running a query. I wasn't
worried
> about these thinking SQL Server was managing them but then I noticed that
> our in house application that uses the same type of setup doesn't have
this
> problem.
> Thanks,
> Wayne
>|||Thanks Jacco,
I'll give the script a try. Based on other things I've seen with this app I
would not be surprised if they are doing something similar.
Thanks again,
Wayne
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23m6mO%23qLEHA.3516@.TK2MSFTNGP11.phx.gbl...
> I have worked with one third party application whose idea of connection
> pooling was to open up 100 connections on start-up, even though it never
> used more than 2 during the time we used it. Your third party application
> might have been designed by a similarly brilliant and knowledgeable
> developer.
> You can't set a timeout for the connections, but you can schedule a job to
> run the following script on a regular basis. This example kills all
> connections that have not been used for 6 hours:
> DECLARE @.sql varchar(4000)
> WHILE 1=1
> BEGIN
> SET @.sql = (SELECT TOP 1 'KILL ' + CAST(spid AS VARCHAR(10))
> FROM master.dbo.sysprocesses WHERE DATEDIFF(hh, last_batch,
> GETDATE()) >= 6
> AND spid <> @.@.spid AND spid >= 50)
> IF @.sql IS NULL BREAK
> EXEC (@.sql)
> END
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> news:OPecmwqLEHA.1340@.TK2MSFTNGP12.phx.gbl...
the[vbcol=seagreen]
> are
has[vbcol=seagreen]
> aren't
> BOL
timeout[vbcol=seagreen]
> worried
that[vbcol=seagreen]
> this
>|||Ok, don't be surprised though if the application either fails to work when
it's 100 or so connections aren't there, or just recreates them on a regular
basis after you have killed them....
Jacco Schalkwijk
SQL Server MVP
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:uJQkvJrLEHA.3904@.TK2MSFTNGP09.phx.gbl...
> Thanks Jacco,
> I'll give the script a try. Based on other things I've seen with this app
I
> would not be surprised if they are doing something similar.
> Thanks again,
> Wayne
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:%23m6mO%23qLEHA.3516@.TK2MSFTNGP11.phx.gbl...
application[vbcol=seagreen]
to[vbcol=seagreen]
> the
There[vbcol=seagreen]
> has
in[vbcol=seagreen]
> timeout
> that
>
We are using a new third party application that has SQL Server 2000 as the
database. It is an ASP page front end and uses ODBC to connect. There are
about 20 people who use it during the day. When I look in EM at the
processes there are well over 100. Even in the morning after everyone has
logged out the night before. All the processes are sleeping so they aren't
using any resources. I feel kind of dumb here but is there a server
property setting where I can set a value for these to expire? Looked in BOL
and in my other books but this doesn't seem to be available. Only timeout
settings when waiting for a connection or running a query. I wasn't worried
about these thinking SQL Server was managing them but then I noticed that
our in house application that uses the same type of setup doesn't have this
problem.
Thanks,
WayneI have worked with one third party application whose idea of connection
pooling was to open up 100 connections on start-up, even though it never
used more than 2 during the time we used it. Your third party application
might have been designed by a similarly brilliant and knowledgeable
developer.
You can't set a timeout for the connections, but you can schedule a job to
run the following script on a regular basis. This example kills all
connections that have not been used for 6 hours:
DECLARE @.sql varchar(4000)
WHILE 1=1
BEGIN
SET @.sql = (SELECT TOP 1 'KILL ' + CAST(spid AS VARCHAR(10))
FROM master.dbo.sysprocesses WHERE DATEDIFF(hh, last_batch,
GETDATE()) >= 6
AND spid <> @.@.spid AND spid >= 50)
IF @.sql IS NULL BREAK
EXEC (@.sql)
END
Jacco Schalkwijk
SQL Server MVP
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:OPecmwqLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi,
> We are using a new third party application that has SQL Server 2000 as the
> database. It is an ASP page front end and uses ODBC to connect. There
are
> about 20 people who use it during the day. When I look in EM at the
> processes there are well over 100. Even in the morning after everyone has
> logged out the night before. All the processes are sleeping so they
aren't
> using any resources. I feel kind of dumb here but is there a server
> property setting where I can set a value for these to expire? Looked in
BOL
> and in my other books but this doesn't seem to be available. Only timeout
> settings when waiting for a connection or running a query. I wasn't
worried
> about these thinking SQL Server was managing them but then I noticed that
> our in house application that uses the same type of setup doesn't have
this
> problem.
> Thanks,
> Wayne
>|||Thanks Jacco,
I'll give the script a try. Based on other things I've seen with this app I
would not be surprised if they are doing something similar.
Thanks again,
Wayne
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23m6mO%23qLEHA.3516@.TK2MSFTNGP11.phx.gbl...
> I have worked with one third party application whose idea of connection
> pooling was to open up 100 connections on start-up, even though it never
> used more than 2 during the time we used it. Your third party application
> might have been designed by a similarly brilliant and knowledgeable
> developer.
> You can't set a timeout for the connections, but you can schedule a job to
> run the following script on a regular basis. This example kills all
> connections that have not been used for 6 hours:
> DECLARE @.sql varchar(4000)
> WHILE 1=1
> BEGIN
> SET @.sql = (SELECT TOP 1 'KILL ' + CAST(spid AS VARCHAR(10))
> FROM master.dbo.sysprocesses WHERE DATEDIFF(hh, last_batch,
> GETDATE()) >= 6
> AND spid <> @.@.spid AND spid >= 50)
> IF @.sql IS NULL BREAK
> EXEC (@.sql)
> END
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> news:OPecmwqLEHA.1340@.TK2MSFTNGP12.phx.gbl...
the[vbcol=seagreen]
> are
has[vbcol=seagreen]
> aren't
> BOL
timeout[vbcol=seagreen]
> worried
that[vbcol=seagreen]
> this
>|||Ok, don't be surprised though if the application either fails to work when
it's 100 or so connections aren't there, or just recreates them on a regular
basis after you have killed them....
Jacco Schalkwijk
SQL Server MVP
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:uJQkvJrLEHA.3904@.TK2MSFTNGP09.phx.gbl...
> Thanks Jacco,
> I'll give the script a try. Based on other things I've seen with this app
I
> would not be surprised if they are doing something similar.
> Thanks again,
> Wayne
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:%23m6mO%23qLEHA.3516@.TK2MSFTNGP11.phx.gbl...
application[vbcol=seagreen]
to[vbcol=seagreen]
> the
There[vbcol=seagreen]
> has
in[vbcol=seagreen]
> timeout
> that
>
Processes seen in enterprise manager
Hi,
We are using a new third party application that has SQL Server 2000 as the
database. It is an ASP page front end and uses ODBC to connect. There are
about 20 people who use it during the day. When I look in EM at the
processes there are well over 100. Even in the morning after everyone has
logged out the night before. All the processes are sleeping so they aren't
using any resources. I feel kind of dumb here but is there a server
property setting where I can set a value for these to expire? Looked in BOL
and in my other books but this doesn't seem to be available. Only timeout
settings when waiting for a connection or running a query. I wasn't worried
about these thinking SQL Server was managing them but then I noticed that
our in house application that uses the same type of setup doesn't have this
problem.
Thanks,
WayneI have worked with one third party application whose idea of connection
pooling was to open up 100 connections on start-up, even though it never
used more than 2 during the time we used it. Your third party application
might have been designed by a similarly brilliant and knowledgeable
developer.
You can't set a timeout for the connections, but you can schedule a job to
run the following script on a regular basis. This example kills all
connections that have not been used for 6 hours:
DECLARE @.sql varchar(4000)
WHILE 1=1
BEGIN
SET @.sql = (SELECT TOP 1 'KILL ' + CAST(spid AS VARCHAR(10))
FROM master.dbo.sysprocesses WHERE DATEDIFF(hh, last_batch,
GETDATE()) >= 6
AND spid <> @.@.spid AND spid >= 50)
IF @.sql IS NULL BREAK
EXEC (@.sql)
END
Jacco Schalkwijk
SQL Server MVP
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:OPecmwqLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi,
> We are using a new third party application that has SQL Server 2000 as the
> database. It is an ASP page front end and uses ODBC to connect. There
are
> about 20 people who use it during the day. When I look in EM at the
> processes there are well over 100. Even in the morning after everyone has
> logged out the night before. All the processes are sleeping so they
aren't
> using any resources. I feel kind of dumb here but is there a server
> property setting where I can set a value for these to expire? Looked in
BOL
> and in my other books but this doesn't seem to be available. Only timeout
> settings when waiting for a connection or running a query. I wasn't
worried
> about these thinking SQL Server was managing them but then I noticed that
> our in house application that uses the same type of setup doesn't have
this
> problem.
> Thanks,
> Wayne
>|||Thanks Jacco,
I'll give the script a try. Based on other things I've seen with this app I
would not be surprised if they are doing something similar.
Thanks again,
Wayne
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23m6mO%23qLEHA.3516@.TK2MSFTNGP11.phx.gbl...
> I have worked with one third party application whose idea of connection
> pooling was to open up 100 connections on start-up, even though it never
> used more than 2 during the time we used it. Your third party application
> might have been designed by a similarly brilliant and knowledgeable
> developer.
> You can't set a timeout for the connections, but you can schedule a job to
> run the following script on a regular basis. This example kills all
> connections that have not been used for 6 hours:
> DECLARE @.sql varchar(4000)
> WHILE 1=1
> BEGIN
> SET @.sql = (SELECT TOP 1 'KILL ' + CAST(spid AS VARCHAR(10))
> FROM master.dbo.sysprocesses WHERE DATEDIFF(hh, last_batch,
> GETDATE()) >= 6
> AND spid <> @.@.spid AND spid >= 50)
> IF @.sql IS NULL BREAK
> EXEC (@.sql)
> END
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> news:OPecmwqLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> > We are using a new third party application that has SQL Server 2000 as
the
> > database. It is an ASP page front end and uses ODBC to connect. There
> are
> > about 20 people who use it during the day. When I look in EM at the
> > processes there are well over 100. Even in the morning after everyone
has
> > logged out the night before. All the processes are sleeping so they
> aren't
> > using any resources. I feel kind of dumb here but is there a server
> > property setting where I can set a value for these to expire? Looked in
> BOL
> > and in my other books but this doesn't seem to be available. Only
timeout
> > settings when waiting for a connection or running a query. I wasn't
> worried
> > about these thinking SQL Server was managing them but then I noticed
that
> > our in house application that uses the same type of setup doesn't have
> this
> > problem.
> >
> > Thanks,
> > Wayne
> >
> >
>|||Ok, don't be surprised though if the application either fails to work when
it's 100 or so connections aren't there, or just recreates them on a regular
basis after you have killed them....
--
Jacco Schalkwijk
SQL Server MVP
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:uJQkvJrLEHA.3904@.TK2MSFTNGP09.phx.gbl...
> Thanks Jacco,
> I'll give the script a try. Based on other things I've seen with this app
I
> would not be surprised if they are doing something similar.
> Thanks again,
> Wayne
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:%23m6mO%23qLEHA.3516@.TK2MSFTNGP11.phx.gbl...
> > I have worked with one third party application whose idea of connection
> > pooling was to open up 100 connections on start-up, even though it never
> > used more than 2 during the time we used it. Your third party
application
> > might have been designed by a similarly brilliant and knowledgeable
> > developer.
> >
> > You can't set a timeout for the connections, but you can schedule a job
to
> > run the following script on a regular basis. This example kills all
> > connections that have not been used for 6 hours:
> >
> > DECLARE @.sql varchar(4000)
> > WHILE 1=1
> > BEGIN
> > SET @.sql = (SELECT TOP 1 'KILL ' + CAST(spid AS VARCHAR(10))
> > FROM master.dbo.sysprocesses WHERE DATEDIFF(hh, last_batch,
> > GETDATE()) >= 6
> > AND spid <> @.@.spid AND spid >= 50)
> > IF @.sql IS NULL BREAK
> > EXEC (@.sql)
> > END
> >
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> >
> > "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> > news:OPecmwqLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> > > Hi,
> > > We are using a new third party application that has SQL Server 2000 as
> the
> > > database. It is an ASP page front end and uses ODBC to connect.
There
> > are
> > > about 20 people who use it during the day. When I look in EM at the
> > > processes there are well over 100. Even in the morning after everyone
> has
> > > logged out the night before. All the processes are sleeping so they
> > aren't
> > > using any resources. I feel kind of dumb here but is there a server
> > > property setting where I can set a value for these to expire? Looked
in
> > BOL
> > > and in my other books but this doesn't seem to be available. Only
> timeout
> > > settings when waiting for a connection or running a query. I wasn't
> > worried
> > > about these thinking SQL Server was managing them but then I noticed
> that
> > > our in house application that uses the same type of setup doesn't have
> > this
> > > problem.
> > >
> > > Thanks,
> > > Wayne
> > >
> > >
> >
> >
>
We are using a new third party application that has SQL Server 2000 as the
database. It is an ASP page front end and uses ODBC to connect. There are
about 20 people who use it during the day. When I look in EM at the
processes there are well over 100. Even in the morning after everyone has
logged out the night before. All the processes are sleeping so they aren't
using any resources. I feel kind of dumb here but is there a server
property setting where I can set a value for these to expire? Looked in BOL
and in my other books but this doesn't seem to be available. Only timeout
settings when waiting for a connection or running a query. I wasn't worried
about these thinking SQL Server was managing them but then I noticed that
our in house application that uses the same type of setup doesn't have this
problem.
Thanks,
WayneI have worked with one third party application whose idea of connection
pooling was to open up 100 connections on start-up, even though it never
used more than 2 during the time we used it. Your third party application
might have been designed by a similarly brilliant and knowledgeable
developer.
You can't set a timeout for the connections, but you can schedule a job to
run the following script on a regular basis. This example kills all
connections that have not been used for 6 hours:
DECLARE @.sql varchar(4000)
WHILE 1=1
BEGIN
SET @.sql = (SELECT TOP 1 'KILL ' + CAST(spid AS VARCHAR(10))
FROM master.dbo.sysprocesses WHERE DATEDIFF(hh, last_batch,
GETDATE()) >= 6
AND spid <> @.@.spid AND spid >= 50)
IF @.sql IS NULL BREAK
EXEC (@.sql)
END
Jacco Schalkwijk
SQL Server MVP
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:OPecmwqLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi,
> We are using a new third party application that has SQL Server 2000 as the
> database. It is an ASP page front end and uses ODBC to connect. There
are
> about 20 people who use it during the day. When I look in EM at the
> processes there are well over 100. Even in the morning after everyone has
> logged out the night before. All the processes are sleeping so they
aren't
> using any resources. I feel kind of dumb here but is there a server
> property setting where I can set a value for these to expire? Looked in
BOL
> and in my other books but this doesn't seem to be available. Only timeout
> settings when waiting for a connection or running a query. I wasn't
worried
> about these thinking SQL Server was managing them but then I noticed that
> our in house application that uses the same type of setup doesn't have
this
> problem.
> Thanks,
> Wayne
>|||Thanks Jacco,
I'll give the script a try. Based on other things I've seen with this app I
would not be surprised if they are doing something similar.
Thanks again,
Wayne
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23m6mO%23qLEHA.3516@.TK2MSFTNGP11.phx.gbl...
> I have worked with one third party application whose idea of connection
> pooling was to open up 100 connections on start-up, even though it never
> used more than 2 during the time we used it. Your third party application
> might have been designed by a similarly brilliant and knowledgeable
> developer.
> You can't set a timeout for the connections, but you can schedule a job to
> run the following script on a regular basis. This example kills all
> connections that have not been used for 6 hours:
> DECLARE @.sql varchar(4000)
> WHILE 1=1
> BEGIN
> SET @.sql = (SELECT TOP 1 'KILL ' + CAST(spid AS VARCHAR(10))
> FROM master.dbo.sysprocesses WHERE DATEDIFF(hh, last_batch,
> GETDATE()) >= 6
> AND spid <> @.@.spid AND spid >= 50)
> IF @.sql IS NULL BREAK
> EXEC (@.sql)
> END
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> news:OPecmwqLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> > We are using a new third party application that has SQL Server 2000 as
the
> > database. It is an ASP page front end and uses ODBC to connect. There
> are
> > about 20 people who use it during the day. When I look in EM at the
> > processes there are well over 100. Even in the morning after everyone
has
> > logged out the night before. All the processes are sleeping so they
> aren't
> > using any resources. I feel kind of dumb here but is there a server
> > property setting where I can set a value for these to expire? Looked in
> BOL
> > and in my other books but this doesn't seem to be available. Only
timeout
> > settings when waiting for a connection or running a query. I wasn't
> worried
> > about these thinking SQL Server was managing them but then I noticed
that
> > our in house application that uses the same type of setup doesn't have
> this
> > problem.
> >
> > Thanks,
> > Wayne
> >
> >
>|||Ok, don't be surprised though if the application either fails to work when
it's 100 or so connections aren't there, or just recreates them on a regular
basis after you have killed them....
--
Jacco Schalkwijk
SQL Server MVP
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:uJQkvJrLEHA.3904@.TK2MSFTNGP09.phx.gbl...
> Thanks Jacco,
> I'll give the script a try. Based on other things I've seen with this app
I
> would not be surprised if they are doing something similar.
> Thanks again,
> Wayne
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:%23m6mO%23qLEHA.3516@.TK2MSFTNGP11.phx.gbl...
> > I have worked with one third party application whose idea of connection
> > pooling was to open up 100 connections on start-up, even though it never
> > used more than 2 during the time we used it. Your third party
application
> > might have been designed by a similarly brilliant and knowledgeable
> > developer.
> >
> > You can't set a timeout for the connections, but you can schedule a job
to
> > run the following script on a regular basis. This example kills all
> > connections that have not been used for 6 hours:
> >
> > DECLARE @.sql varchar(4000)
> > WHILE 1=1
> > BEGIN
> > SET @.sql = (SELECT TOP 1 'KILL ' + CAST(spid AS VARCHAR(10))
> > FROM master.dbo.sysprocesses WHERE DATEDIFF(hh, last_batch,
> > GETDATE()) >= 6
> > AND spid <> @.@.spid AND spid >= 50)
> > IF @.sql IS NULL BREAK
> > EXEC (@.sql)
> > END
> >
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> >
> > "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> > news:OPecmwqLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> > > Hi,
> > > We are using a new third party application that has SQL Server 2000 as
> the
> > > database. It is an ASP page front end and uses ODBC to connect.
There
> > are
> > > about 20 people who use it during the day. When I look in EM at the
> > > processes there are well over 100. Even in the morning after everyone
> has
> > > logged out the night before. All the processes are sleeping so they
> > aren't
> > > using any resources. I feel kind of dumb here but is there a server
> > > property setting where I can set a value for these to expire? Looked
in
> > BOL
> > > and in my other books but this doesn't seem to be available. Only
> timeout
> > > settings when waiting for a connection or running a query. I wasn't
> > worried
> > > about these thinking SQL Server was managing them but then I noticed
> that
> > > our in house application that uses the same type of setup doesn't have
> > this
> > > problem.
> > >
> > > Thanks,
> > > Wayne
> > >
> > >
> >
> >
>
processes problem
I have a client using my application written in Delphi with SQL Server. My
log indicates Maximum number of DBPROCESSes already
allocated and I can see that I have 250 sleeping processes in their
SQL Server process information screen. My application is set to
connect 1 times to the database and maintains these connection
persistently while it is running which is basically all the time, so I
cannot explain how it has 250 processes.
Thank you for your help!
VirgilWhat version of SQL Server are you using. SELECT @.@.VERSION
--
HTH
Ryan Waight, MCDBA, MCSE
"Virgil Trasca" <vtrasca@.totalsoft.ro> wrote in message
news:ug0JTnRfDHA.3324@.TK2MSFTNGP11.phx.gbl...
> I have a client using my application written in Delphi with SQL Server. My
> log indicates Maximum number of DBPROCESSes already
> allocated and I can see that I have 250 sleeping processes in their
> SQL Server process information screen. My application is set to
> connect 1 times to the database and maintains these connection
> persistently while it is running which is basically all the time, so I
> cannot explain how it has 250 processes.
> Thank you for your help!
> Virgil
>
log indicates Maximum number of DBPROCESSes already
allocated and I can see that I have 250 sleeping processes in their
SQL Server process information screen. My application is set to
connect 1 times to the database and maintains these connection
persistently while it is running which is basically all the time, so I
cannot explain how it has 250 processes.
Thank you for your help!
VirgilWhat version of SQL Server are you using. SELECT @.@.VERSION
--
HTH
Ryan Waight, MCDBA, MCSE
"Virgil Trasca" <vtrasca@.totalsoft.ro> wrote in message
news:ug0JTnRfDHA.3324@.TK2MSFTNGP11.phx.gbl...
> I have a client using my application written in Delphi with SQL Server. My
> log indicates Maximum number of DBPROCESSes already
> allocated and I can see that I have 250 sleeping processes in their
> SQL Server process information screen. My application is set to
> connect 1 times to the database and maintains these connection
> persistently while it is running which is basically all the time, so I
> cannot explain how it has 250 processes.
> Thank you for your help!
> Virgil
>
Processes locking themselves after SP4
Hi All,
We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
200 SP4. Users are complaining that the system is running slowly, looking at
the processes, the active processes appear to be blocking themselves. The
configuration of SQL is identical to before and the hardware is the same,
just the OS and the SP version of SQL has changed.
Any ideas what might be causing the blocking? I've checked for lock
escalation (using profiler) and we're not experiencing it!
Dave Wall
Are you using the very latest SP4 for SQL2K? Does your system use RAM
above 4GB and is AWE-enabled?
This may not be the issue I know but better to check rather than
assume. I know the first SP4 release problems didn't necessarily
manifest in the way your system is but you can never be sure ...
ALI
|||We're using the patch on SP4 to allow us to run AWE with RAM of 5GB
(restricted from 8GB)
Dave
Dave Wall
"zashah@.gmail.com" wrote:
> Are you using the very latest SP4 for SQL2K? Does your system use RAM
> above 4GB and is AWE-enabled?
> This may not be the issue I know but better to check rather than
> assume. I know the first SP4 release problems didn't necessarily
> manifest in the way your system is but you can never be sure ...
> ALI
>
|||Processes appearing to lock themselves is a change in reporting in SP4, not an actual issue. Basically the process reports itself as a blocking process when it goes to grab disk allocation (as I understand it). The Wait Type should display as PAGELATCH IO if I recall correctly.
Took me by surprise too but it isn't an actual blocked process.
As far as performance goes, I haven't seen any performance hits on databases up to 400 GB in size.
Originally posted by Dave Wall
Hi All,
We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
200 SP4. Users are complaining that the system is running slowly, looking at
the processes, the active processes appear to be blocking themselves. The
configuration of SQL is identical to before and the hardware is the same,
just the OS and the SP version of SQL has changed.
Any ideas what might be causing the blocking? I've checked for lock
escalation (using profiler) and we're not experiencing it!
Dave Wall
Have a look at this explanation by Santeri Voutilainen from MSFT
http://groups-beta.google.com/group/...513ab281?hl=en
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dave Wall" <DaveWall@.discussions.microsoft.com> wrote in message
news:B746F00D-1908-4D9B-9934-F35F24304D54@.microsoft.com...
> Hi All,
> We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
> 200 SP4. Users are complaining that the system is running slowly, looking
> at
> the processes, the active processes appear to be blocking themselves. The
> configuration of SQL is identical to before and the hardware is the same,
> just the OS and the SP version of SQL has changed.
> Any ideas what might be causing the blocking? I've checked for lock
> escalation (using profiler) and we're not experiencing it!
> --
> Dave Wall
|||Thanks Kyle,
and thanks to everyone else for your input. Turns out that while our
networking team assured me that the server had been set up to use PAE it
wasn't entriely true. Once it was set and the server rebooted, SQL started to
use the additional memory, IO dropped to nothing (as it can now hold around
1/3 of the DB in memory) and the blocking spids disappeared.
Thanks again for everyones input!
Dave
Dave Wall
"Kyle Quinby" wrote:
> Processes appearing to lock themselves is a change in reporting in SP4,
> not an actual issue. Basically the process reports itself as a blocking
> process when it goes to grab disk allocation (as I understand it). The
> Wait Type should display as PAGELATCH IO if I recall correctly.
> Took me by surprise too but it isn't an actual blocked process.
> As far as performance goes, I haven't seen any performance hits on
> databases up to 400 GB in size.
> Dave Wall wrote:
>
> --
> Kyle Quinby
> Posted via http://www.mcse.ms
> View this thread: http://www.mcse.ms/message1756427.html
>
We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
200 SP4. Users are complaining that the system is running slowly, looking at
the processes, the active processes appear to be blocking themselves. The
configuration of SQL is identical to before and the hardware is the same,
just the OS and the SP version of SQL has changed.
Any ideas what might be causing the blocking? I've checked for lock
escalation (using profiler) and we're not experiencing it!
Dave Wall
Are you using the very latest SP4 for SQL2K? Does your system use RAM
above 4GB and is AWE-enabled?
This may not be the issue I know but better to check rather than
assume. I know the first SP4 release problems didn't necessarily
manifest in the way your system is but you can never be sure ...
ALI
|||We're using the patch on SP4 to allow us to run AWE with RAM of 5GB
(restricted from 8GB)
Dave
Dave Wall
"zashah@.gmail.com" wrote:
> Are you using the very latest SP4 for SQL2K? Does your system use RAM
> above 4GB and is AWE-enabled?
> This may not be the issue I know but better to check rather than
> assume. I know the first SP4 release problems didn't necessarily
> manifest in the way your system is but you can never be sure ...
> ALI
>
|||Processes appearing to lock themselves is a change in reporting in SP4, not an actual issue. Basically the process reports itself as a blocking process when it goes to grab disk allocation (as I understand it). The Wait Type should display as PAGELATCH IO if I recall correctly.
Took me by surprise too but it isn't an actual blocked process.
As far as performance goes, I haven't seen any performance hits on databases up to 400 GB in size.
Quote:
Hi All,
We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
200 SP4. Users are complaining that the system is running slowly, looking at
the processes, the active processes appear to be blocking themselves. The
configuration of SQL is identical to before and the hardware is the same,
just the OS and the SP version of SQL has changed.
Any ideas what might be causing the blocking? I've checked for lock
escalation (using profiler) and we're not experiencing it!
Dave Wall
Have a look at this explanation by Santeri Voutilainen from MSFT
http://groups-beta.google.com/group/...513ab281?hl=en
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dave Wall" <DaveWall@.discussions.microsoft.com> wrote in message
news:B746F00D-1908-4D9B-9934-F35F24304D54@.microsoft.com...
> Hi All,
> We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
> 200 SP4. Users are complaining that the system is running slowly, looking
> at
> the processes, the active processes appear to be blocking themselves. The
> configuration of SQL is identical to before and the hardware is the same,
> just the OS and the SP version of SQL has changed.
> Any ideas what might be causing the blocking? I've checked for lock
> escalation (using profiler) and we're not experiencing it!
> --
> Dave Wall
|||Thanks Kyle,
and thanks to everyone else for your input. Turns out that while our
networking team assured me that the server had been set up to use PAE it
wasn't entriely true. Once it was set and the server rebooted, SQL started to
use the additional memory, IO dropped to nothing (as it can now hold around
1/3 of the DB in memory) and the blocking spids disappeared.
Thanks again for everyones input!
Dave
Dave Wall
"Kyle Quinby" wrote:
> Processes appearing to lock themselves is a change in reporting in SP4,
> not an actual issue. Basically the process reports itself as a blocking
> process when it goes to grab disk allocation (as I understand it). The
> Wait Type should display as PAGELATCH IO if I recall correctly.
> Took me by surprise too but it isn't an actual blocked process.
> As far as performance goes, I haven't seen any performance hits on
> databases up to 400 GB in size.
> Dave Wall wrote:
>
> --
> Kyle Quinby
> Posted via http://www.mcse.ms
> View this thread: http://www.mcse.ms/message1756427.html
>
Processes locking themselves after SP4
Hi All,
We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
200 SP4. Users are complaining that the system is running slowly, looking at
the processes, the active processes appear to be blocking themselves. The
configuration of SQL is identical to before and the hardware is the same,
just the OS and the SP version of SQL has changed.
Any ideas what might be causing the blocking? I've checked for lock
escalation (using profiler) and we're not experiencing it!
--
Dave WallAre you using the very latest SP4 for SQL2K? Does your system use RAM
above 4GB and is AWE-enabled?
This may not be the issue I know but better to check rather than
assume. I know the first SP4 release problems didn't necessarily
manifest in the way your system is but you can never be sure ...
ALI|||We're using the patch on SP4 to allow us to run AWE with RAM of 5GB
(restricted from 8GB)
Dave
--
Dave Wall
"zashah@.gmail.com" wrote:
> Are you using the very latest SP4 for SQL2K? Does your system use RAM
> above 4GB and is AWE-enabled?
> This may not be the issue I know but better to check rather than
> assume. I know the first SP4 release problems didn't necessarily
> manifest in the way your system is but you can never be sure ...
> ALI
>|||Hi
Have a look at this explanation by Santeri Voutilainen from MSFT
http://groups-beta.google.com/group/microsoft.public.sqlserver.server/msg/b86e343e513ab281?hl=en
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dave Wall" <DaveWall@.discussions.microsoft.com> wrote in message
news:B746F00D-1908-4D9B-9934-F35F24304D54@.microsoft.com...
> Hi All,
> We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
> 200 SP4. Users are complaining that the system is running slowly, looking
> at
> the processes, the active processes appear to be blocking themselves. The
> configuration of SQL is identical to before and the hardware is the same,
> just the OS and the SP version of SQL has changed.
> Any ideas what might be causing the blocking? I've checked for lock
> escalation (using profiler) and we're not experiencing it!
> --
> Dave Wall|||Processes appearing to lock themselves is a change in reporting in SP4,
not an actual issue. Basically the process reports itself as a blocking
process when it goes to grab disk allocation (as I understand it). The
Wait Type should display as PAGELATCH IO if I recall correctly.
Took me by surprise too but it isn't an actual blocked process.
As far as performance goes, I haven't seen any performance hits on
databases up to 400 GB in size.
Dave Wall wrote:
> *Hi All,
> We've recently rebuilt our failover cluster to Windows 2003 and SQL
> Server
> 200 SP4. Users are complaining that the system is running slowly,
> looking at
> the processes, the active processes appear to be blocking themselves.
> The
> configuration of SQL is identical to before and the hardware is the
> same,
> just the OS and the SP version of SQL has changed.
> Any ideas what might be causing the blocking? I've checked for lock
> escalation (using profiler) and we're not experiencing it!
> --
> Dave Wall *
Kyle Quinby
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1756427.html|||Thanks Kyle,
and thanks to everyone else for your input. Turns out that while our
networking team assured me that the server had been set up to use PAE it
wasn't entriely true. Once it was set and the server rebooted, SQL started to
use the additional memory, IO dropped to nothing (as it can now hold around
1/3 of the DB in memory) and the blocking spids disappeared.
Thanks again for everyones input!
Dave
--
Dave Wall
"Kyle Quinby" wrote:
> Processes appearing to lock themselves is a change in reporting in SP4,
> not an actual issue. Basically the process reports itself as a blocking
> process when it goes to grab disk allocation (as I understand it). The
> Wait Type should display as PAGELATCH IO if I recall correctly.
> Took me by surprise too but it isn't an actual blocked process.
> As far as performance goes, I haven't seen any performance hits on
> databases up to 400 GB in size.
> Dave Wall wrote:
> > *Hi All,
> >
> > We've recently rebuilt our failover cluster to Windows 2003 and SQL
> > Server
> > 200 SP4. Users are complaining that the system is running slowly,
> > looking at
> > the processes, the active processes appear to be blocking themselves.
> > The
> > configuration of SQL is identical to before and the hardware is the
> > same,
> > just the OS and the SP version of SQL has changed.
> >
> > Any ideas what might be causing the blocking? I've checked for lock
> > escalation (using profiler) and we're not experiencing it!
> >
> > --
> > Dave Wall *
>
> --
> Kyle Quinby
> ---
> Posted via http://www.mcse.ms
> ---
> View this thread: http://www.mcse.ms/message1756427.html
>
We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
200 SP4. Users are complaining that the system is running slowly, looking at
the processes, the active processes appear to be blocking themselves. The
configuration of SQL is identical to before and the hardware is the same,
just the OS and the SP version of SQL has changed.
Any ideas what might be causing the blocking? I've checked for lock
escalation (using profiler) and we're not experiencing it!
--
Dave WallAre you using the very latest SP4 for SQL2K? Does your system use RAM
above 4GB and is AWE-enabled?
This may not be the issue I know but better to check rather than
assume. I know the first SP4 release problems didn't necessarily
manifest in the way your system is but you can never be sure ...
ALI|||We're using the patch on SP4 to allow us to run AWE with RAM of 5GB
(restricted from 8GB)
Dave
--
Dave Wall
"zashah@.gmail.com" wrote:
> Are you using the very latest SP4 for SQL2K? Does your system use RAM
> above 4GB and is AWE-enabled?
> This may not be the issue I know but better to check rather than
> assume. I know the first SP4 release problems didn't necessarily
> manifest in the way your system is but you can never be sure ...
> ALI
>|||Hi
Have a look at this explanation by Santeri Voutilainen from MSFT
http://groups-beta.google.com/group/microsoft.public.sqlserver.server/msg/b86e343e513ab281?hl=en
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dave Wall" <DaveWall@.discussions.microsoft.com> wrote in message
news:B746F00D-1908-4D9B-9934-F35F24304D54@.microsoft.com...
> Hi All,
> We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
> 200 SP4. Users are complaining that the system is running slowly, looking
> at
> the processes, the active processes appear to be blocking themselves. The
> configuration of SQL is identical to before and the hardware is the same,
> just the OS and the SP version of SQL has changed.
> Any ideas what might be causing the blocking? I've checked for lock
> escalation (using profiler) and we're not experiencing it!
> --
> Dave Wall|||Processes appearing to lock themselves is a change in reporting in SP4,
not an actual issue. Basically the process reports itself as a blocking
process when it goes to grab disk allocation (as I understand it). The
Wait Type should display as PAGELATCH IO if I recall correctly.
Took me by surprise too but it isn't an actual blocked process.
As far as performance goes, I haven't seen any performance hits on
databases up to 400 GB in size.
Dave Wall wrote:
> *Hi All,
> We've recently rebuilt our failover cluster to Windows 2003 and SQL
> Server
> 200 SP4. Users are complaining that the system is running slowly,
> looking at
> the processes, the active processes appear to be blocking themselves.
> The
> configuration of SQL is identical to before and the hardware is the
> same,
> just the OS and the SP version of SQL has changed.
> Any ideas what might be causing the blocking? I've checked for lock
> escalation (using profiler) and we're not experiencing it!
> --
> Dave Wall *
Kyle Quinby
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1756427.html|||Thanks Kyle,
and thanks to everyone else for your input. Turns out that while our
networking team assured me that the server had been set up to use PAE it
wasn't entriely true. Once it was set and the server rebooted, SQL started to
use the additional memory, IO dropped to nothing (as it can now hold around
1/3 of the DB in memory) and the blocking spids disappeared.
Thanks again for everyones input!
Dave
--
Dave Wall
"Kyle Quinby" wrote:
> Processes appearing to lock themselves is a change in reporting in SP4,
> not an actual issue. Basically the process reports itself as a blocking
> process when it goes to grab disk allocation (as I understand it). The
> Wait Type should display as PAGELATCH IO if I recall correctly.
> Took me by surprise too but it isn't an actual blocked process.
> As far as performance goes, I haven't seen any performance hits on
> databases up to 400 GB in size.
> Dave Wall wrote:
> > *Hi All,
> >
> > We've recently rebuilt our failover cluster to Windows 2003 and SQL
> > Server
> > 200 SP4. Users are complaining that the system is running slowly,
> > looking at
> > the processes, the active processes appear to be blocking themselves.
> > The
> > configuration of SQL is identical to before and the hardware is the
> > same,
> > just the OS and the SP version of SQL has changed.
> >
> > Any ideas what might be causing the blocking? I've checked for lock
> > escalation (using profiler) and we're not experiencing it!
> >
> > --
> > Dave Wall *
>
> --
> Kyle Quinby
> ---
> Posted via http://www.mcse.ms
> ---
> View this thread: http://www.mcse.ms/message1756427.html
>
Processes locking themselves after SP4
Hi All,
We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
200 SP4. Users are complaining that the system is running slowly, looking a
t
the processes, the active processes appear to be blocking themselves. The
configuration of SQL is identical to before and the hardware is the same,
just the OS and the SP version of SQL has changed.
Any ideas what might be causing the blocking? I've checked for lock
escalation (using profiler) and we're not experiencing it!
Dave WallAre you using the very latest SP4 for SQL2K? Does your system use RAM
above 4GB and is AWE-enabled?
This may not be the issue I know but better to check rather than
assume. I know the first SP4 release problems didn't necessarily
manifest in the way your system is but you can never be sure ...
ALI|||We're using the patch on SP4 to allow us to run AWE with RAM of 5GB
(restricted from 8GB)
Dave
Dave Wall
"zashah@.gmail.com" wrote:
> Are you using the very latest SP4 for SQL2K? Does your system use RAM
> above 4GB and is AWE-enabled?
> This may not be the issue I know but better to check rather than
> assume. I know the first SP4 release problems didn't necessarily
> manifest in the way your system is but you can never be sure ...
> ALI
>|||Hi
Have a look at this explanation by Santeri Voutilainen from MSFT
http://groups-beta.google.com/group...
513ab281?hl=en
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dave Wall" <DaveWall@.discussions.microsoft.com> wrote in message
news:B746F00D-1908-4D9B-9934-F35F24304D54@.microsoft.com...
> Hi All,
> We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
> 200 SP4. Users are complaining that the system is running slowly, looking
> at
> the processes, the active processes appear to be blocking themselves. The
> configuration of SQL is identical to before and the hardware is the same,
> just the OS and the SP version of SQL has changed.
> Any ideas what might be causing the blocking? I've checked for lock
> escalation (using profiler) and we're not experiencing it!
> --
> Dave Wall|||Processes appearing to lock themselves is a change in reporting in SP4,
not an actual issue. Basically the process reports itself as a blocking
process when it goes to grab disk allocation (as I understand it). The
Wait Type should display as PAGELATCH IO if I recall correctly.
Took me by surprise too but it isn't an actual blocked process.
As far as performance goes, I haven't seen any performance hits on
databases up to 400 GB in size.
Dave Wall wrote:
> *Hi All,
> We've recently rebuilt our failover cluster to Windows 2003 and SQL
> Server
> 200 SP4. Users are complaining that the system is running slowly,
> looking at
> the processes, the active processes appear to be blocking themselves.
> The
> configuration of SQL is identical to before and the hardware is the
> same,
> just the OS and the SP version of SQL has changed.
> Any ideas what might be causing the blocking? I've checked for lock
> escalation (using profiler) and we're not experiencing it!
> --
> Dave Wall *
Kyle Quinby
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1756427.html|||Thanks Kyle,
and thanks to everyone else for your input. Turns out that while our
networking team assured me that the server had been set up to use PAE it
wasn't entriely true. Once it was set and the server rebooted, SQL started t
o
use the additional memory, IO dropped to nothing (as it can now hold around
1/3 of the DB in memory) and the blocking spids disappeared.
Thanks again for everyones input!
Dave
--
Dave Wall
"Kyle Quinby" wrote:
> Processes appearing to lock themselves is a change in reporting in SP4,
> not an actual issue. Basically the process reports itself as a blocking
> process when it goes to grab disk allocation (as I understand it). The
> Wait Type should display as PAGELATCH IO if I recall correctly.
> Took me by surprise too but it isn't an actual blocked process.
> As far as performance goes, I haven't seen any performance hits on
> databases up to 400 GB in size.
> Dave Wall wrote:
>
> --
> Kyle Quinby
> ---
> Posted via http://www.mcse.ms
> ---
> View this thread: http://www.mcse.ms/message1756427.html
>
We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
200 SP4. Users are complaining that the system is running slowly, looking a
t
the processes, the active processes appear to be blocking themselves. The
configuration of SQL is identical to before and the hardware is the same,
just the OS and the SP version of SQL has changed.
Any ideas what might be causing the blocking? I've checked for lock
escalation (using profiler) and we're not experiencing it!
Dave WallAre you using the very latest SP4 for SQL2K? Does your system use RAM
above 4GB and is AWE-enabled?
This may not be the issue I know but better to check rather than
assume. I know the first SP4 release problems didn't necessarily
manifest in the way your system is but you can never be sure ...
ALI|||We're using the patch on SP4 to allow us to run AWE with RAM of 5GB
(restricted from 8GB)
Dave
Dave Wall
"zashah@.gmail.com" wrote:
> Are you using the very latest SP4 for SQL2K? Does your system use RAM
> above 4GB and is AWE-enabled?
> This may not be the issue I know but better to check rather than
> assume. I know the first SP4 release problems didn't necessarily
> manifest in the way your system is but you can never be sure ...
> ALI
>|||Hi
Have a look at this explanation by Santeri Voutilainen from MSFT
http://groups-beta.google.com/group...
513ab281?hl=en
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dave Wall" <DaveWall@.discussions.microsoft.com> wrote in message
news:B746F00D-1908-4D9B-9934-F35F24304D54@.microsoft.com...
> Hi All,
> We've recently rebuilt our failover cluster to Windows 2003 and SQL Server
> 200 SP4. Users are complaining that the system is running slowly, looking
> at
> the processes, the active processes appear to be blocking themselves. The
> configuration of SQL is identical to before and the hardware is the same,
> just the OS and the SP version of SQL has changed.
> Any ideas what might be causing the blocking? I've checked for lock
> escalation (using profiler) and we're not experiencing it!
> --
> Dave Wall|||Processes appearing to lock themselves is a change in reporting in SP4,
not an actual issue. Basically the process reports itself as a blocking
process when it goes to grab disk allocation (as I understand it). The
Wait Type should display as PAGELATCH IO if I recall correctly.
Took me by surprise too but it isn't an actual blocked process.
As far as performance goes, I haven't seen any performance hits on
databases up to 400 GB in size.
Dave Wall wrote:
> *Hi All,
> We've recently rebuilt our failover cluster to Windows 2003 and SQL
> Server
> 200 SP4. Users are complaining that the system is running slowly,
> looking at
> the processes, the active processes appear to be blocking themselves.
> The
> configuration of SQL is identical to before and the hardware is the
> same,
> just the OS and the SP version of SQL has changed.
> Any ideas what might be causing the blocking? I've checked for lock
> escalation (using profiler) and we're not experiencing it!
> --
> Dave Wall *
Kyle Quinby
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1756427.html|||Thanks Kyle,
and thanks to everyone else for your input. Turns out that while our
networking team assured me that the server had been set up to use PAE it
wasn't entriely true. Once it was set and the server rebooted, SQL started t
o
use the additional memory, IO dropped to nothing (as it can now hold around
1/3 of the DB in memory) and the blocking spids disappeared.
Thanks again for everyones input!
Dave
--
Dave Wall
"Kyle Quinby" wrote:
> Processes appearing to lock themselves is a change in reporting in SP4,
> not an actual issue. Basically the process reports itself as a blocking
> process when it goes to grab disk allocation (as I understand it). The
> Wait Type should display as PAGELATCH IO if I recall correctly.
> Took me by surprise too but it isn't an actual blocked process.
> As far as performance goes, I haven't seen any performance hits on
> databases up to 400 GB in size.
> Dave Wall wrote:
>
> --
> Kyle Quinby
> ---
> Posted via http://www.mcse.ms
> ---
> View this thread: http://www.mcse.ms/message1756427.html
>
Processes in Sysprocesses
Hi
When using sybase EA server I can utilise the following code to inform me of
what a particular Spid is executing
select s.stmtnum, s.linenum, o.name
from sysobjects o, master..sysprocesses s
where s.id = o.id
and s.spid = 77
is there anything I can use in sqlserver to give me the same kind of
details? ie, what line of a SP is running?
however, in sql server the columns linenum1) DBCC INPUTBUFFER(spid)
2) DECLARE @.Handle BINARY(30)
SELECT @.Handle = sql_handle
FROM master..sysprocesses
WHERE spid = @.@.spid
SELECT * FROM ::fn_get_sql(@.Handle)
For more details please refer to BOL
"almightygav" <almightygav@.discussions.microsoft.com> wrote in message
news:18CAF9A4-4F51-40F7-BC31-DF36FDE1B114@.microsoft.com...
> Hi
> When using sybase EA server I can utilise the following code to inform me
> of
> what a particular Spid is executing
> select s.stmtnum, s.linenum, o.name
> from sysobjects o, master..sysprocesses s
> where s.id = o.id
> and s.spid = 77
> is there anything I can use in sqlserver to give me the same kind of
> details? ie, what line of a SP is running?
> however, in sql server the columns linenum|||You might want to check this ... http://vyaskn.tripod.com/fn_get_sql.htm
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"almightygav" wrote:
> Hi
> When using sybase EA server I can utilise the following code to inform me
of
> what a particular Spid is executing
> select s.stmtnum, s.linenum, o.name
> from sysobjects o, master..sysprocesses s
> where s.id = o.id
> and s.spid = 77
> is there anything I can use in sqlserver to give me the same kind of
> details? ie, what line of a SP is running?
> however, in sql server the columns linenum|||Thanks for that Uri, every day is a lesson eh!
However, is there any way to drill down to a more granular level so that I
can identify what line of the proc it is running?
The problem we are encountering is that on occasions a couple of large
procedures grind to a standstill and we are trying to identify where they ar
e
slow.
Any more assistance would be gratefully received.
Gavin
"Uri Dimant" wrote:
> 1) DBCC INPUTBUFFER(spid)
> 2) DECLARE @.Handle BINARY(30)
> SELECT @.Handle = sql_handle
> FROM master..sysprocesses
> WHERE spid = @.@.spid
> SELECT * FROM ::fn_get_sql(@.Handle)
> For more details please refer to BOL
>
> "almightygav" <almightygav@.discussions.microsoft.com> wrote in message
> news:18CAF9A4-4F51-40F7-BC31-DF36FDE1B114@.microsoft.com...
>
>|||I've got to hand it to you Vadivel, that was good!
I'd like to think I could have written it if the help files were updated
inline with the Service Packs but hey ho, top job!
When do the help files get updated to show changes made by the Service
Packs? The two columns added to sysprocesses don't seem to be documented
anywhere?
Thank you very much, perfect!
"Vadivel" wrote:
> You might want to check this ... http://vyaskn.tripod.com/fn_get_sql.htm
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
> http://thinkingms.com/vadivel
> "almightygav" wrote:
>|||> When do the help files get updated to show changes made by the Service
> Packs?
You need to download the update for Books Online. BOL update is not in the s
ervice pack files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"almightygav" <almightygav@.discussions.microsoft.com> wrote in message
news:FF370F16-5824-4E66-909F-2FFF2A1FB160@.microsoft.com...
> I've got to hand it to you Vadivel, that was good!
> I'd like to think I could have written it if the help files were updated
> inline with the Service Packs but hey ho, top job!
> When do the help files get updated to show changes made by the Service
> Packs? The two columns added to sysprocesses don't seem to be documented
> anywhere?
> Thank you very much, perfect!
> "Vadivel" wrote:
>|||Thanks almighty! I am glad i was of some help to you.
Btw as Tibor said, BOL updates are not part of Service pack files.
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"almightygav" wrote:
> I've got to hand it to you Vadivel, that was good!
> I'd like to think I could have written it if the help files were updated
> inline with the Service Packs but hey ho, top job!
> When do the help files get updated to show changes made by the Service
> Packs? The two columns added to sysprocesses don't seem to be documented
> anywhere?
> Thank you very much, perfect!
> "Vadivel" wrote:
>|||On Thu, 10 Nov 2005 05:22:04 -0800, "almightygav"
<almightygav@.discussions.microsoft.com> wrote:
>Thanks for that Uri, every day is a lesson eh!
>However, is there any way to drill down to a more granular level so that I
>can identify what line of the proc it is running?
>The problem we are encountering is that on occasions a couple of large
>procedures grind to a standstill and we are trying to identify where they a
re
>slow.
>Any more assistance would be gratefully received.
You can get that level of detail by running the profiler.
There's probably some voodoo way to get it via queries, but I don't
know it.
Well, maybe I do, "set statistics profile on" and then run your fat SP
and see if that helps, if that's not too much information!
J.
When using sybase EA server I can utilise the following code to inform me of
what a particular Spid is executing
select s.stmtnum, s.linenum, o.name
from sysobjects o, master..sysprocesses s
where s.id = o.id
and s.spid = 77
is there anything I can use in sqlserver to give me the same kind of
details? ie, what line of a SP is running?
however, in sql server the columns linenum1) DBCC INPUTBUFFER(spid)
2) DECLARE @.Handle BINARY(30)
SELECT @.Handle = sql_handle
FROM master..sysprocesses
WHERE spid = @.@.spid
SELECT * FROM ::fn_get_sql(@.Handle)
For more details please refer to BOL
"almightygav" <almightygav@.discussions.microsoft.com> wrote in message
news:18CAF9A4-4F51-40F7-BC31-DF36FDE1B114@.microsoft.com...
> Hi
> When using sybase EA server I can utilise the following code to inform me
> of
> what a particular Spid is executing
> select s.stmtnum, s.linenum, o.name
> from sysobjects o, master..sysprocesses s
> where s.id = o.id
> and s.spid = 77
> is there anything I can use in sqlserver to give me the same kind of
> details? ie, what line of a SP is running?
> however, in sql server the columns linenum|||You might want to check this ... http://vyaskn.tripod.com/fn_get_sql.htm
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"almightygav" wrote:
> Hi
> When using sybase EA server I can utilise the following code to inform me
of
> what a particular Spid is executing
> select s.stmtnum, s.linenum, o.name
> from sysobjects o, master..sysprocesses s
> where s.id = o.id
> and s.spid = 77
> is there anything I can use in sqlserver to give me the same kind of
> details? ie, what line of a SP is running?
> however, in sql server the columns linenum|||Thanks for that Uri, every day is a lesson eh!
However, is there any way to drill down to a more granular level so that I
can identify what line of the proc it is running?
The problem we are encountering is that on occasions a couple of large
procedures grind to a standstill and we are trying to identify where they ar
e
slow.
Any more assistance would be gratefully received.
Gavin
"Uri Dimant" wrote:
> 1) DBCC INPUTBUFFER(spid)
> 2) DECLARE @.Handle BINARY(30)
> SELECT @.Handle = sql_handle
> FROM master..sysprocesses
> WHERE spid = @.@.spid
> SELECT * FROM ::fn_get_sql(@.Handle)
> For more details please refer to BOL
>
> "almightygav" <almightygav@.discussions.microsoft.com> wrote in message
> news:18CAF9A4-4F51-40F7-BC31-DF36FDE1B114@.microsoft.com...
>
>|||I've got to hand it to you Vadivel, that was good!
I'd like to think I could have written it if the help files were updated
inline with the Service Packs but hey ho, top job!
When do the help files get updated to show changes made by the Service
Packs? The two columns added to sysprocesses don't seem to be documented
anywhere?
Thank you very much, perfect!
"Vadivel" wrote:
> You might want to check this ... http://vyaskn.tripod.com/fn_get_sql.htm
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
> http://thinkingms.com/vadivel
> "almightygav" wrote:
>|||> When do the help files get updated to show changes made by the Service
> Packs?
You need to download the update for Books Online. BOL update is not in the s
ervice pack files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"almightygav" <almightygav@.discussions.microsoft.com> wrote in message
news:FF370F16-5824-4E66-909F-2FFF2A1FB160@.microsoft.com...
> I've got to hand it to you Vadivel, that was good!
> I'd like to think I could have written it if the help files were updated
> inline with the Service Packs but hey ho, top job!
> When do the help files get updated to show changes made by the Service
> Packs? The two columns added to sysprocesses don't seem to be documented
> anywhere?
> Thank you very much, perfect!
> "Vadivel" wrote:
>|||Thanks almighty! I am glad i was of some help to you.
Btw as Tibor said, BOL updates are not part of Service pack files.
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"almightygav" wrote:
> I've got to hand it to you Vadivel, that was good!
> I'd like to think I could have written it if the help files were updated
> inline with the Service Packs but hey ho, top job!
> When do the help files get updated to show changes made by the Service
> Packs? The two columns added to sysprocesses don't seem to be documented
> anywhere?
> Thank you very much, perfect!
> "Vadivel" wrote:
>|||On Thu, 10 Nov 2005 05:22:04 -0800, "almightygav"
<almightygav@.discussions.microsoft.com> wrote:
>Thanks for that Uri, every day is a lesson eh!
>However, is there any way to drill down to a more granular level so that I
>can identify what line of the proc it is running?
>The problem we are encountering is that on occasions a couple of large
>procedures grind to a standstill and we are trying to identify where they a
re
>slow.
>Any more assistance would be gratefully received.
You can get that level of detail by running the profiler.
There's probably some voodoo way to get it via queries, but I don't
know it.
Well, maybe I do, "set statistics profile on" and then run your fat SP
and see if that helps, if that's not too much information!
J.
Processes blocking RESOURCE MONITOR, normal behaviour?
Today I ended up in a situation where I had a process with total six "subthreads" (identified by different execution context) (seen in Activity Monitor). All of these had blocking=1. The server didn't function properly, I don't know the details of these problems, since I was not present at that time. We had to kill the processes. What is the process id 1, "RESOURCE MONITOR" in SQL Server 2005, seen in Activity Monitor? Is it fatal if some processes are blocking RESOURCE MONITOR? How can one end up in such situation, is it normal or a bug somewhere?
The server is a 64-bit Windows server having SQL Server 2005 SP1.
Yesterday I had a CLR stored procedure running on another server. The procedure uses System.Data.SqlClient.SqlConnection to access this server. The procedure started about 11.4.2007 22:22. The procedure created a connection to the SQL Server and created a select that should return 1,5 million rows. During fetching the rows (about after 800 000 rows) the procedure crashes to an error:"".NET Framework execution was aborted by escalation policy because of out of memory. " Naturally the procedure couldn't close the SQL Server connections, since it was forced to end.
The details if the processes as seen from ActÃvity Monitor (I only have screenshots so I can't copy-paste...):
The main process:
Process id: 69
status: suspended
open transactions: 1
command: SELECT
Application: .NET SqlClient Data Provider
Wait time: 578
Wait type: ASYNC_NETWORK_ID
CPU: 1375
Physical IO: 22
Memory usage: 2
Login time: 11.4.2007 22:22:05
Last batch: 11.4.2007 22:22:05
Blocked by: 0
Blocking: 1
Execution context: 0
Two "subthreads", there are five similar.
Process id: 69
status: suspended
open transactions: 0
command: SELECT
Application: .NET SqlClient Data Provider
Wait time: 35293046
Wait type: CXPACKET
CPU: 4875
Physical IO: 2214
Memory usage: 2
Login time: 11.4.2007 22:22:05
Last batch: 11.4.2007 22:22:05
Blocked by: 0
Blocking: 1
Execution context: 1
Process id: 69
status: suspended
open transactions: 0
command: SELECT
Application: .NET SqlClient Data Provider
Wait time: 35293031
Wait type: CXPACKET
CPU: 4875
Physical IO: 2210
Memory usage: 2
Login time: 11.4.2007 22:22:05
Last batch: 11.4.2007 22:22:05
Blocked by: 0
Blocking: 1
Execution context: 2
The rest three subthreads differ from the above by having different wait time, CPU, physical IO and execution context.
Alright Chap,
You need to look at the "BLOCKED BY" rather than "BLOCKING" column.
The BLOCKING=1 means that this process is blocking another process.
Looking at the info you provided, the SPID 69 is not being blocked by any process.
Hope that helps.
Jag
|||Hi
First off are you aware of the issue http://support.microsoft.com/kb/928083. I ask because one of its symptoms is the .NET framework message you mention and I just wanted to make certain you could eliminate it as a cause of that problem.
On the wait/blocking issue you may want to check out http://msdn2.microsoft.com/en-us/library/ms179984.aspx which describes the various wait states and a dynamic management view to look at them. The wait type that you are seeing (CXPACKET) is particularly associated with parallellisation of queries. There is a recommendation of trying reducing the degree of parallelism if you see a problem with this type of contention.
|||
Jag Sandhu wrote:
Alright Chap,
You need to look at the "BLOCKED BY" rather than "BLOCKING" column.
The BLOCKING=1 means that this process is blocking another process.
Looking at the info you provided, the SPID 69 is not being blocked by any process.
You are right, 69 is not blocking anything. I'm not interested in what 69 is blocking.
The problem is that 69 IS blocking SPID 1. SPID 1 is a system process, whose significance I don't know. During the problem 69 had been blocking 1 for a long time and the SQL server had been quite jammed. I was suspecting that the jamming was because the system process 1 couldn't do anythin being blocked by 69.
|||
First off are you aware of the issue http://support.microsoft.com/kb/928083. I ask because one of its symptoms is the .NET framework message you mention and I just wanted to make certain you could eliminate it as a cause of that problem.
Thanks for the information, I actually was unaware of the issue. This time I am not using a context connection, so the KB-entry is not valid in my case? I have two SQL Server instances and the CLR stored procedures run on instance A and use a "normal" SQL Server connection (instead of context connection) to connect to the server B.
On the wait/blocking issue you may want to check out http://msdn2.microsoft.com/en-us/library/ms179984.aspx which describes the various wait states and a dynamic management view to look at them. The wait type that you are seeing (CXPACKET) is particularly associated with parallellisation of queries. There is a recommendation of trying reducing the degree of parallelism if you see a problem with this type of contention.
OK, thanks for this information too. I find this CXPACKET issue also very strange since our database is in practise idle most of the time. And when this issue I reported happened the connection 69 had been in CXPACKET state for a while (don't know details, but maybe at least minutes). Shuoldn't the CXPACKET state change to something else after a while? Can this have something to do with the RESOURCE MONITOR process being blocked by process 69?
|||Hi JM_F,
When BLOCKING column is set to 1 which means IT IS blocking another process. Other possible value for BLOCKING column is 0 which means it is not blocking any processes.
The values are 1 or 0 for Yes or NO respectively.
You have to use SP_who2 or DMV - sys.dm_exec_requests
and look for spid 69 in the BLOCKED by column.
regards
Jag
|||
When BLOCKING column is set to 1 which means IT IS blocking another process. Other possible value for BLOCKING column is 0 which means it is not blocking any processes.
The values are 1 or 0 for Yes or NO respectively.
Hello Jag,
If I open Activity Monitor and click help, the following comes:
Blocked By
Process ID (SPID) of a blocking process.
Blocking
Process ID (SPID) of processes that are blocked.
This very clearly states that the value of blocking column contains the process ID. You say it contains 0 or 1. Are you sure of this?
JM
|||Hi JM,
To confirm, please see the books online topic - Activity Monitor (Process Info Page).
- Process ID
SQL Server Process ID.
- User
ID of the user who executed the command.
- Database
Database currently being used by the process.
- Status
Status of the process (for example, running, sleeping, runnable, and background).
- Open Transactions
Number of open transactions for the process.
- Command
Command currently being executed.
- Application
Name of the application program being used by the process.
- Wait Time
Current wait time in milliseconds. When the process is not waiting, the wait time is zero.
- Wait Type
Indicates the name of the last or current wait type.
- Resource
Textual representation of a lock resource.
- CPU
Cumulative CPU time for the process. The entry is updated only for processes performed on behalf of Transact-SQL statements executed when SET STATISTICS TIME ON has been activated in the same session. The CPU column is updated when a query has been executed with SET STATISTICS TIME ON. When zero is returned, SET STATISTICS TIME is OFF.
- Physical IO
Cumulative disk reads and writes for the process.
- Memory Usage
Number of pages in the procedure cache that are currently allocated to this process. A negative number indicates that the process is freeing memory allocated by another process.
- Login Time
Time at which a client process logged into the server. For system processes, the time at which SQL Server startup occurred is displayed.
- Last Batch
Last time a client process executed a remote stored procedure call or an EXECUTE statement. For system processes, the displayed time is that at which SQL Server startup occurred.
- Host
Name of the workstation.
- Net Library
Column in which the client's network library is stored. Every client process comes in on a network connection. Network connections have a network library associated with them that allows them to make the connection. .
- Net Address
Assigned unique identifier for the network interface card on each user's workstation. When the user logs in, this identifier is inserted in the Network Address column.
- Blocked By
Process ID (SPID) of a blocking process.
- Blocking
Indicates whether this process is blocking others. 1 = yes; 0 = no.
- Execution Context
Execution context ID used to uniquely identify the subthreads operating on behalf of a single process.
To confirm, please see the books online topic - Activity Monitor (Process Info Page).
Blocking
Indicates whether this process is blocking others. 1 = yes; 0 = no.
Interesting... I found the entry you pointed in MSDN (http://msdn2.microsoft.com/en-us/library/ms178520.aspx). However the help page in my local installation (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/12f87b09-bf20-4a69-8333-e67419472337.htm) contains the description I sent before. So these descriptions don't match.
Can I assume this a bug in SQL Server local documentation?
I have SQL Server 2005 SP 1 on Windows XP.
regards,
JM
|||MS does a very good job of keeping sql bol on msdn2 current. You should download the latest one from http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx|||Hi JM,
Please get the latest bol.
By the way, BLOCKING thing is even same for SQL 2000. So its always been like that.
regards
Jag
process which lock himself??
Hi,
I have some strange behaviors.
Sometimes my processes are locked... by themself!
I have executed a shrink command through a DBCC command, and the process is
locked by himself. (sp_who2 result)
this behavior appeared not only during DBCC commands but also during select
or other simple commands.
any idea?
thanks
Jerome
Have you intalled sp4?
We are seeing this too.
Maybe nothing to worry about. It appears to be part of the new "stuck I/O"
reporting.
It makes it hard to get a quick view of what is really going on.
See what wait type is shown for the spid
http://www.sqldev.net/articles/WaitTypes.htm
http://msdn.microsoft.com/sql/defaul...v_04222005.asp
Paul
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have some strange behaviors.
> Sometimes my processes are locked... by themself!
> I have executed a shrink command through a DBCC command, and the process
> is locked by himself. (sp_who2 result)
> this behavior appeared not only during DBCC commands but also during
> select or other simple commands.
> any idea?
> thanks
> Jerome
>
|||Hi
At a guess you are locking another process by the same user and not locking
the process that is doing the shrink, in that respect the locked process is
no different to any other!
If you posted your SQL Server version and the output of your sp_who2 it
would have been more informative.
John
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have some strange behaviors.
> Sometimes my processes are locked... by themself!
> I have executed a shrink command through a DBCC command, and the process
> is locked by himself. (sp_who2 result)
> this behavior appeared not only during DBCC commands but also during
> select or other simple commands.
> any idea?
> thanks
> Jerome
>
|||SP4 is installed
I don't have the result of the SP_who2 command, but next time the problem
appear, I'll send it to you.
its the second server with the problem and only after SP4 applied.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23t0y4zQdFHA.1684@.TK2MSFTNGP09.phx.gbl...
> Hi
> At a guess you are locking another process by the same user and not
> locking the process that is doing the shrink, in that respect the locked
> process is no different to any other!
> If you posted your SQL Server version and the output of your sp_who2 it
> would have been more informative.
> John
>
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
>
|||I had a google and others are reporting this too.
eg "Blocked by own spid" etc.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23b7vM8QdFHA.3828@.TK2MSFTNGP10.phx.gbl...
> SP4 is installed
> I don't have the result of the SP_who2 command, but next time the problem
> appear, I'll send it to you.
> its the second server with the problem and only after SP4 applied.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23t0y4zQdFHA.1684@.TK2MSFTNGP09.phx.gbl...
>
|||have you checked to ensure that you do not have a disk bottleneck?
(disk fragmentation /cache conroller problem etc . .)
appears to be the same spid with different execution context..
blocks itself during the execution of a query using a parallel QEP.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23b7vM8QdFHA.3828@.TK2MSFTNGP10.phx.gbl...
> SP4 is installed
> I don't have the result of the SP_who2 command, but next time the problem
> appear, I'll send it to you.
> its the second server with the problem and only after SP4 applied.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23t0y4zQdFHA.1684@.TK2MSFTNGP09.phx.gbl...
>
I have some strange behaviors.
Sometimes my processes are locked... by themself!
I have executed a shrink command through a DBCC command, and the process is
locked by himself. (sp_who2 result)
this behavior appeared not only during DBCC commands but also during select
or other simple commands.
any idea?
thanks
Jerome
Have you intalled sp4?
We are seeing this too.
Maybe nothing to worry about. It appears to be part of the new "stuck I/O"
reporting.
It makes it hard to get a quick view of what is really going on.
See what wait type is shown for the spid
http://www.sqldev.net/articles/WaitTypes.htm
http://msdn.microsoft.com/sql/defaul...v_04222005.asp
Paul
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have some strange behaviors.
> Sometimes my processes are locked... by themself!
> I have executed a shrink command through a DBCC command, and the process
> is locked by himself. (sp_who2 result)
> this behavior appeared not only during DBCC commands but also during
> select or other simple commands.
> any idea?
> thanks
> Jerome
>
|||Hi
At a guess you are locking another process by the same user and not locking
the process that is doing the shrink, in that respect the locked process is
no different to any other!
If you posted your SQL Server version and the output of your sp_who2 it
would have been more informative.
John
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have some strange behaviors.
> Sometimes my processes are locked... by themself!
> I have executed a shrink command through a DBCC command, and the process
> is locked by himself. (sp_who2 result)
> this behavior appeared not only during DBCC commands but also during
> select or other simple commands.
> any idea?
> thanks
> Jerome
>
|||SP4 is installed
I don't have the result of the SP_who2 command, but next time the problem
appear, I'll send it to you.
its the second server with the problem and only after SP4 applied.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23t0y4zQdFHA.1684@.TK2MSFTNGP09.phx.gbl...
> Hi
> At a guess you are locking another process by the same user and not
> locking the process that is doing the shrink, in that respect the locked
> process is no different to any other!
> If you posted your SQL Server version and the output of your sp_who2 it
> would have been more informative.
> John
>
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
>
|||I had a google and others are reporting this too.
eg "Blocked by own spid" etc.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23b7vM8QdFHA.3828@.TK2MSFTNGP10.phx.gbl...
> SP4 is installed
> I don't have the result of the SP_who2 command, but next time the problem
> appear, I'll send it to you.
> its the second server with the problem and only after SP4 applied.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23t0y4zQdFHA.1684@.TK2MSFTNGP09.phx.gbl...
>
|||have you checked to ensure that you do not have a disk bottleneck?
(disk fragmentation /cache conroller problem etc . .)
appears to be the same spid with different execution context..
blocks itself during the execution of a query using a parallel QEP.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23b7vM8QdFHA.3828@.TK2MSFTNGP10.phx.gbl...
> SP4 is installed
> I don't have the result of the SP_who2 command, but next time the problem
> appear, I'll send it to you.
> its the second server with the problem and only after SP4 applied.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23t0y4zQdFHA.1684@.TK2MSFTNGP09.phx.gbl...
>
process which lock himself??
Hi,
I have some strange behaviors.
Sometimes my processes are locked... by themself!
I have executed a shrink command through a DBCC command, and the process is
locked by himself. (sp_who2 result)
this behavior appeared not only during DBCC commands but also during select
or other simple commands.
any idea?
thanks
JeromeHave you intalled sp4?
We are seeing this too.
Maybe nothing to worry about. It appears to be part of the new "stuck I/O"
reporting.
It makes it hard to get a quick view of what is really going on.
See what wait type is shown for the spid
http://www.sqldev.net/articles/WaitTypes.htm
http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsqldev/html/sqldev_04222005.asp
Paul
"Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have some strange behaviors.
> Sometimes my processes are locked... by themself!
> I have executed a shrink command through a DBCC command, and the process
> is locked by himself. (sp_who2 result)
> this behavior appeared not only during DBCC commands but also during
> select or other simple commands.
> any idea?
> thanks
> Jerome
>|||Hi
At a guess you are locking another process by the same user and not locking
the process that is doing the shrink, in that respect the locked process is
no different to any other!
If you posted your SQL Server version and the output of your sp_who2 it
would have been more informative.
John
"Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have some strange behaviors.
> Sometimes my processes are locked... by themself!
> I have executed a shrink command through a DBCC command, and the process
> is locked by himself. (sp_who2 result)
> this behavior appeared not only during DBCC commands but also during
> select or other simple commands.
> any idea?
> thanks
> Jerome
>|||SP4 is installed
I don't have the result of the SP_who2 command, but next time the problem
appear, I'll send it to you.
its the second server with the problem and only after SP4 applied.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23t0y4zQdFHA.1684@.TK2MSFTNGP09.phx.gbl...
> Hi
> At a guess you are locking another process by the same user and not
> locking the process that is doing the shrink, in that respect the locked
> process is no different to any other!
> If you posted your SQL Server version and the output of your sp_who2 it
> would have been more informative.
> John
>
> "Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
> news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> I have some strange behaviors.
>> Sometimes my processes are locked... by themself!
>> I have executed a shrink command through a DBCC command, and the process
>> is locked by himself. (sp_who2 result)
>> this behavior appeared not only during DBCC commands but also during
>> select or other simple commands.
>> any idea?
>> thanks
>> Jerome
>>
>|||I had a google and others are reporting this too.
eg "Blocked by own spid" etc.
"Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23b7vM8QdFHA.3828@.TK2MSFTNGP10.phx.gbl...
> SP4 is installed
> I don't have the result of the SP_who2 command, but next time the problem
> appear, I'll send it to you.
> its the second server with the problem and only after SP4 applied.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23t0y4zQdFHA.1684@.TK2MSFTNGP09.phx.gbl...
>> Hi
>> At a guess you are locking another process by the same user and not
>> locking the process that is doing the shrink, in that respect the locked
>> process is no different to any other!
>> If you posted your SQL Server version and the output of your sp_who2 it
>> would have been more informative.
>> John
>>
>> "Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
>> news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> I have some strange behaviors.
>> Sometimes my processes are locked... by themself!
>> I have executed a shrink command through a DBCC command, and the process
>> is locked by himself. (sp_who2 result)
>> this behavior appeared not only during DBCC commands but also during
>> select or other simple commands.
>> any idea?
>> thanks
>> Jerome
>>
>>
>|||have you checked to ensure that you do not have a disk bottleneck?
(disk fragmentation /cache conroller problem etc . .)
appears to be the same spid with different execution context..
blocks itself during the execution of a query using a parallel QEP.
"Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23b7vM8QdFHA.3828@.TK2MSFTNGP10.phx.gbl...
> SP4 is installed
> I don't have the result of the SP_who2 command, but next time the problem
> appear, I'll send it to you.
> its the second server with the problem and only after SP4 applied.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23t0y4zQdFHA.1684@.TK2MSFTNGP09.phx.gbl...
>> Hi
>> At a guess you are locking another process by the same user and not
>> locking the process that is doing the shrink, in that respect the locked
>> process is no different to any other!
>> If you posted your SQL Server version and the output of your sp_who2 it
>> would have been more informative.
>> John
>>
>> "Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
>> news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> I have some strange behaviors.
>> Sometimes my processes are locked... by themself!
>> I have executed a shrink command through a DBCC command, and the process
>> is locked by himself. (sp_who2 result)
>> this behavior appeared not only during DBCC commands but also during
>> select or other simple commands.
>> any idea?
>> thanks
>> Jerome
>>
>>
>
I have some strange behaviors.
Sometimes my processes are locked... by themself!
I have executed a shrink command through a DBCC command, and the process is
locked by himself. (sp_who2 result)
this behavior appeared not only during DBCC commands but also during select
or other simple commands.
any idea?
thanks
JeromeHave you intalled sp4?
We are seeing this too.
Maybe nothing to worry about. It appears to be part of the new "stuck I/O"
reporting.
It makes it hard to get a quick view of what is really going on.
See what wait type is shown for the spid
http://www.sqldev.net/articles/WaitTypes.htm
http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsqldev/html/sqldev_04222005.asp
Paul
"Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have some strange behaviors.
> Sometimes my processes are locked... by themself!
> I have executed a shrink command through a DBCC command, and the process
> is locked by himself. (sp_who2 result)
> this behavior appeared not only during DBCC commands but also during
> select or other simple commands.
> any idea?
> thanks
> Jerome
>|||Hi
At a guess you are locking another process by the same user and not locking
the process that is doing the shrink, in that respect the locked process is
no different to any other!
If you posted your SQL Server version and the output of your sp_who2 it
would have been more informative.
John
"Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have some strange behaviors.
> Sometimes my processes are locked... by themself!
> I have executed a shrink command through a DBCC command, and the process
> is locked by himself. (sp_who2 result)
> this behavior appeared not only during DBCC commands but also during
> select or other simple commands.
> any idea?
> thanks
> Jerome
>|||SP4 is installed
I don't have the result of the SP_who2 command, but next time the problem
appear, I'll send it to you.
its the second server with the problem and only after SP4 applied.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23t0y4zQdFHA.1684@.TK2MSFTNGP09.phx.gbl...
> Hi
> At a guess you are locking another process by the same user and not
> locking the process that is doing the shrink, in that respect the locked
> process is no different to any other!
> If you posted your SQL Server version and the output of your sp_who2 it
> would have been more informative.
> John
>
> "Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
> news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> I have some strange behaviors.
>> Sometimes my processes are locked... by themself!
>> I have executed a shrink command through a DBCC command, and the process
>> is locked by himself. (sp_who2 result)
>> this behavior appeared not only during DBCC commands but also during
>> select or other simple commands.
>> any idea?
>> thanks
>> Jerome
>>
>|||I had a google and others are reporting this too.
eg "Blocked by own spid" etc.
"Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23b7vM8QdFHA.3828@.TK2MSFTNGP10.phx.gbl...
> SP4 is installed
> I don't have the result of the SP_who2 command, but next time the problem
> appear, I'll send it to you.
> its the second server with the problem and only after SP4 applied.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23t0y4zQdFHA.1684@.TK2MSFTNGP09.phx.gbl...
>> Hi
>> At a guess you are locking another process by the same user and not
>> locking the process that is doing the shrink, in that respect the locked
>> process is no different to any other!
>> If you posted your SQL Server version and the output of your sp_who2 it
>> would have been more informative.
>> John
>>
>> "Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
>> news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> I have some strange behaviors.
>> Sometimes my processes are locked... by themself!
>> I have executed a shrink command through a DBCC command, and the process
>> is locked by himself. (sp_who2 result)
>> this behavior appeared not only during DBCC commands but also during
>> select or other simple commands.
>> any idea?
>> thanks
>> Jerome
>>
>>
>|||have you checked to ensure that you do not have a disk bottleneck?
(disk fragmentation /cache conroller problem etc . .)
appears to be the same spid with different execution context..
blocks itself during the execution of a query using a parallel QEP.
"Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23b7vM8QdFHA.3828@.TK2MSFTNGP10.phx.gbl...
> SP4 is installed
> I don't have the result of the SP_who2 command, but next time the problem
> appear, I'll send it to you.
> its the second server with the problem and only after SP4 applied.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23t0y4zQdFHA.1684@.TK2MSFTNGP09.phx.gbl...
>> Hi
>> At a guess you are locking another process by the same user and not
>> locking the process that is doing the shrink, in that respect the locked
>> process is no different to any other!
>> If you posted your SQL Server version and the output of your sp_who2 it
>> would have been more informative.
>> John
>>
>> "Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
>> news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> I have some strange behaviors.
>> Sometimes my processes are locked... by themself!
>> I have executed a shrink command through a DBCC command, and the process
>> is locked by himself. (sp_who2 result)
>> this behavior appeared not only during DBCC commands but also during
>> select or other simple commands.
>> any idea?
>> thanks
>> Jerome
>>
>>
>
process which lock himself??
Hi,
I have some strange behaviors.
Sometimes my processes are locked... by themself!
I have executed a shrink command through a DBCC command, and the process is
locked by himself. (sp_who2 result)
this behavior appeared not only during DBCC commands but also during select
or other simple commands.
any idea?
thanks
JeromeHave you intalled sp4?
We are seeing this too.
Maybe nothing to worry about. It appears to be part of the new "stuck I/O"
reporting.
It makes it hard to get a quick view of what is really going on.
See what wait type is shown for the spid
http://www.sqldev.net/articles/WaitTypes.htm
http://msdn.microsoft.com/sql/defau...
v_04222005.asp
Paul
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have some strange behaviors.
> Sometimes my processes are locked... by themself!
> I have executed a shrink command through a DBCC command, and the process
> is locked by himself. (sp_who2 result)
> this behavior appeared not only during DBCC commands but also during
> select or other simple commands.
> any idea?
> thanks
> Jerome
>|||Hi
At a guess you are locking another process by the same user and not locking
the process that is doing the shrink, in that respect the locked process is
no different to any other!
If you posted your SQL Server version and the output of your sp_who2 it
would have been more informative.
John
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have some strange behaviors.
> Sometimes my processes are locked... by themself!
> I have executed a shrink command through a DBCC command, and the process
> is locked by himself. (sp_who2 result)
> this behavior appeared not only during DBCC commands but also during
> select or other simple commands.
> any idea?
> thanks
> Jerome
>|||SP4 is installed
I don't have the result of the SP_who2 command, but next time the problem
appear, I'll send it to you.
its the second server with the problem and only after SP4 applied.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23t0y4zQdFHA.1684@.TK2MSFTNGP09.phx.gbl...
> Hi
> At a guess you are locking another process by the same user and not
> locking the process that is doing the shrink, in that respect the locked
> process is no different to any other!
> If you posted your SQL Server version and the output of your sp_who2 it
> would have been more informative.
> John
>
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
>|||I had a google and others are reporting this too.
eg "Blocked by own spid" etc.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23b7vM8QdFHA.3828@.TK2MSFTNGP10.phx.gbl...
> SP4 is installed
> I don't have the result of the SP_who2 command, but next time the problem
> appear, I'll send it to you.
> its the second server with the problem and only after SP4 applied.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23t0y4zQdFHA.1684@.TK2MSFTNGP09.phx.gbl...
>|||have you checked to ensure that you do not have a disk bottleneck?
(disk fragmentation /cache conroller problem etc . .)
appears to be the same spid with different execution context..
blocks itself during the execution of a query using a parallel QEP.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23b7vM8QdFHA.3828@.TK2MSFTNGP10.phx.gbl...
> SP4 is installed
> I don't have the result of the SP_who2 command, but next time the problem
> appear, I'll send it to you.
> its the second server with the problem and only after SP4 applied.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23t0y4zQdFHA.1684@.TK2MSFTNGP09.phx.gbl...
>
I have some strange behaviors.
Sometimes my processes are locked... by themself!
I have executed a shrink command through a DBCC command, and the process is
locked by himself. (sp_who2 result)
this behavior appeared not only during DBCC commands but also during select
or other simple commands.
any idea?
thanks
JeromeHave you intalled sp4?
We are seeing this too.
Maybe nothing to worry about. It appears to be part of the new "stuck I/O"
reporting.
It makes it hard to get a quick view of what is really going on.
See what wait type is shown for the spid
http://www.sqldev.net/articles/WaitTypes.htm
http://msdn.microsoft.com/sql/defau...
v_04222005.asp
Paul
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have some strange behaviors.
> Sometimes my processes are locked... by themself!
> I have executed a shrink command through a DBCC command, and the process
> is locked by himself. (sp_who2 result)
> this behavior appeared not only during DBCC commands but also during
> select or other simple commands.
> any idea?
> thanks
> Jerome
>|||Hi
At a guess you are locking another process by the same user and not locking
the process that is doing the shrink, in that respect the locked process is
no different to any other!
If you posted your SQL Server version and the output of your sp_who2 it
would have been more informative.
John
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have some strange behaviors.
> Sometimes my processes are locked... by themself!
> I have executed a shrink command through a DBCC command, and the process
> is locked by himself. (sp_who2 result)
> this behavior appeared not only during DBCC commands but also during
> select or other simple commands.
> any idea?
> thanks
> Jerome
>|||SP4 is installed
I don't have the result of the SP_who2 command, but next time the problem
appear, I'll send it to you.
its the second server with the problem and only after SP4 applied.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23t0y4zQdFHA.1684@.TK2MSFTNGP09.phx.gbl...
> Hi
> At a guess you are locking another process by the same user and not
> locking the process that is doing the shrink, in that respect the locked
> process is no different to any other!
> If you posted your SQL Server version and the output of your sp_who2 it
> would have been more informative.
> John
>
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:OiryhqQdFHA.3808@.TK2MSFTNGP14.phx.gbl...
>|||I had a google and others are reporting this too.
eg "Blocked by own spid" etc.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23b7vM8QdFHA.3828@.TK2MSFTNGP10.phx.gbl...
> SP4 is installed
> I don't have the result of the SP_who2 command, but next time the problem
> appear, I'll send it to you.
> its the second server with the problem and only after SP4 applied.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23t0y4zQdFHA.1684@.TK2MSFTNGP09.phx.gbl...
>|||have you checked to ensure that you do not have a disk bottleneck?
(disk fragmentation /cache conroller problem etc . .)
appears to be the same spid with different execution context..
blocks itself during the execution of a query using a parallel QEP.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23b7vM8QdFHA.3828@.TK2MSFTNGP10.phx.gbl...
> SP4 is installed
> I don't have the result of the SP_who2 command, but next time the problem
> appear, I'll send it to you.
> its the second server with the problem and only after SP4 applied.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23t0y4zQdFHA.1684@.TK2MSFTNGP09.phx.gbl...
>
Subscribe to:
Posts (Atom)