I have two views in a production database that I did not create and I cannot account for. From the names used and the syntax of the views, it seems that the system (SQL Server) generated them.
Name: _hypmv_0_5771
Name: _hypmv_0
The syntax crushes the entire SELECT statement (4357 characters) onto a single line of T-SQL (not something I'm usually in the habit of doing).
I have read on kbAlertz that there was a bug in the original release of SQL server where the Index Tuning Wizard did not always remove hypothetical indices used to calculate performance improvements. I have not found anything related to hypothetical views. I do have indexed views, but none that reference these views.
I have checked dependencies; nothing depends on these views and they, in turn, do not depend on anything else.
Has anyone else encountered this problem? Aside from saving the definitions and then dropping them (just to see what may break), are there any suggestions for how to deal with this?
The database is medium-large. About 25GB.
Thanks,
Hugh ScottHere is the article on hypothetical indexes...
http://support.microsoft.com/?id=290414
Showing posts with label mysterious. Show all posts
Showing posts with label mysterious. Show all posts
Monday, March 19, 2012
Mysterious truncation
I have a SP that retrives several fields on a table containing cariage
returns in the data (IE Office Address) Im storing the address data in a
temo table and then returning the resuls of the temp table.
The issue is that many of the addresses seem to get truncated even though
the field in the temp table is defined as varchar(1000) - more then enough
space to hold the data. The fields in the table where the data is coming
from are defined as text(16). I cant use text data type in my SP temp table
b/c I need the DISTINCT ability. However, Im not convined the problem lies
with the table being text type and the SP temp table being varchar(1000). An
y
Ideas as to the problem anyone. Most of the addresses are probably not even
500 characters long. Does the SP npt like cariage returns in the data? I
didnt design the orginal table. Im trying to work around it.
--
JP
.NET Software DevelperAre you basing your analysis on ouput from Query Analyzer? If so, there's a
trick in Query Analyzer - It defaults to truncate all column output to a max
of 256 characters. To change that, go to menu option
Tools, Options, Results, and in middle vertically, you will see
Maximum Characters per column set to 256... Change it to 8000
"JP" wrote:
> I have a SP that retrives several fields on a table containing cariage
> returns in the data (IE Office Address) Im storing the address data in a
> temo table and then returning the resuls of the temp table.
> The issue is that many of the addresses seem to get truncated even though
> the field in the temp table is defined as varchar(1000) - more then enough
> space to hold the data. The fields in the table where the data is coming
> from are defined as text(16). I cant use text data type in my SP temp tabl
e
> b/c I need the DISTINCT ability. However, Im not convined the problem lies
> with the table being text type and the SP temp table being varchar(1000).
Any
> Ideas as to the problem anyone. Most of the addresses are probably not eve
n
> 500 characters long. Does the SP npt like cariage returns in the data? I
> didnt design the orginal table. Im trying to work around it.
> --
> JP
> .NET Software Develper|||This did not solve the problem. It still resulting the truncated data even
though its in the table. If the SP results would have been truncated SQL
would have resulted in an error code b/c data would have been truncated.
"CBretana" wrote:
> Are you basing your analysis on ouput from Query Analyzer? If so, there's
a
> trick in Query Analyzer - It defaults to truncate all column output to a m
ax
> of 256 characters. To change that, go to menu option
> Tools, Options, Results, and in middle vertically, you will see
> Maximum Characters per column set to 256... Change it to 8000
> "JP" wrote:
>|||Then is there some pattern to the truncations? Is the length always the
same? i.e., is it always truncating a tthe same character position? Or is it
always truncating just before the Carriage return Line feed? Look at the
ouput, and try to discern some pattern. That will help steer you in the
right direction to debug this...
"JP" wrote:
> This did not solve the problem. It still resulting the truncated data even
> though its in the table. If the SP results would have been truncated SQL
> would have resulted in an error code b/c data would have been truncated.
> "CBretana" wrote:
>|||First step in an operation like this, run profiler and see what commands are
being sent to the server. This will likely show you your problem.
Otherwise post more information like the DDL of your tables and some data.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"JP" <JP@.discussions.microsoft.com> wrote in message
news:1503FA3A-0FFC-48EB-ADBD-D8CF183C93A3@.microsoft.com...
>I have a SP that retrives several fields on a table containing cariage
> returns in the data (IE Office Address) Im storing the address data in a
> temo table and then returning the resuls of the temp table.
> The issue is that many of the addresses seem to get truncated even though
> the field in the temp table is defined as varchar(1000) - more then enough
> space to hold the data. The fields in the table where the data is coming
> from are defined as text(16). I cant use text data type in my SP temp
> table
> b/c I need the DISTINCT ability. However, Im not convined the problem lies
> with the table being text type and the SP temp table being varchar(1000).
> Any
> Ideas as to the problem anyone. Most of the addresses are probably not
> even
> 500 characters long. Does the SP npt like cariage returns in the data? I
> didnt design the orginal table. Im trying to work around it.
> --
> JP
> .NET Software Develper
returns in the data (IE Office Address) Im storing the address data in a
temo table and then returning the resuls of the temp table.
The issue is that many of the addresses seem to get truncated even though
the field in the temp table is defined as varchar(1000) - more then enough
space to hold the data. The fields in the table where the data is coming
from are defined as text(16). I cant use text data type in my SP temp table
b/c I need the DISTINCT ability. However, Im not convined the problem lies
with the table being text type and the SP temp table being varchar(1000). An
y
Ideas as to the problem anyone. Most of the addresses are probably not even
500 characters long. Does the SP npt like cariage returns in the data? I
didnt design the orginal table. Im trying to work around it.
--
JP
.NET Software DevelperAre you basing your analysis on ouput from Query Analyzer? If so, there's a
trick in Query Analyzer - It defaults to truncate all column output to a max
of 256 characters. To change that, go to menu option
Tools, Options, Results, and in middle vertically, you will see
Maximum Characters per column set to 256... Change it to 8000
"JP" wrote:
> I have a SP that retrives several fields on a table containing cariage
> returns in the data (IE Office Address) Im storing the address data in a
> temo table and then returning the resuls of the temp table.
> The issue is that many of the addresses seem to get truncated even though
> the field in the temp table is defined as varchar(1000) - more then enough
> space to hold the data. The fields in the table where the data is coming
> from are defined as text(16). I cant use text data type in my SP temp tabl
e
> b/c I need the DISTINCT ability. However, Im not convined the problem lies
> with the table being text type and the SP temp table being varchar(1000).
Any
> Ideas as to the problem anyone. Most of the addresses are probably not eve
n
> 500 characters long. Does the SP npt like cariage returns in the data? I
> didnt design the orginal table. Im trying to work around it.
> --
> JP
> .NET Software Develper|||This did not solve the problem. It still resulting the truncated data even
though its in the table. If the SP results would have been truncated SQL
would have resulted in an error code b/c data would have been truncated.
"CBretana" wrote:
> Are you basing your analysis on ouput from Query Analyzer? If so, there's
a
> trick in Query Analyzer - It defaults to truncate all column output to a m
ax
> of 256 characters. To change that, go to menu option
> Tools, Options, Results, and in middle vertically, you will see
> Maximum Characters per column set to 256... Change it to 8000
> "JP" wrote:
>|||Then is there some pattern to the truncations? Is the length always the
same? i.e., is it always truncating a tthe same character position? Or is it
always truncating just before the Carriage return Line feed? Look at the
ouput, and try to discern some pattern. That will help steer you in the
right direction to debug this...
"JP" wrote:
> This did not solve the problem. It still resulting the truncated data even
> though its in the table. If the SP results would have been truncated SQL
> would have resulted in an error code b/c data would have been truncated.
> "CBretana" wrote:
>|||First step in an operation like this, run profiler and see what commands are
being sent to the server. This will likely show you your problem.
Otherwise post more information like the DDL of your tables and some data.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"JP" <JP@.discussions.microsoft.com> wrote in message
news:1503FA3A-0FFC-48EB-ADBD-D8CF183C93A3@.microsoft.com...
>I have a SP that retrives several fields on a table containing cariage
> returns in the data (IE Office Address) Im storing the address data in a
> temo table and then returning the resuls of the temp table.
> The issue is that many of the addresses seem to get truncated even though
> the field in the temp table is defined as varchar(1000) - more then enough
> space to hold the data. The fields in the table where the data is coming
> from are defined as text(16). I cant use text data type in my SP temp
> table
> b/c I need the DISTINCT ability. However, Im not convined the problem lies
> with the table being text type and the SP temp table being varchar(1000).
> Any
> Ideas as to the problem anyone. Most of the addresses are probably not
> even
> 500 characters long. Does the SP npt like cariage returns in the data? I
> didnt design the orginal table. Im trying to work around it.
> --
> JP
> .NET Software Develper
Mysterious text in query result
Hi,
I have a database with a column defined as varchar(100) called Surname.
When I use Query Analyzer to retrieve data from the column and display as
text, I get strange text appearing for one of the records. Thus, the field
contains "Wilson" but when I run the following:
SELECT '''' + Surname + '''', Surnname FROM tblManagers
WHERE FirstName = 'Glenn'
I get the following result:
'Wilson d g e d w i t h t h e a ' Wilson
Where has the "d ged with the a" come from. If I show the results in a grid
the final apostrophe is missing from the first part of the query:
'Wilson
If I run a query using ADO and show the result on a web page, the extra text
is not shown.
Any ideas what is going on here?
Glenn
[Reposted, as posts from outside msnews.microsoft.com does not seem to make
it in.]
BigMan2001 (BigMan2001@.discussions.microsoft.com) writes:
> I have a database with a column defined as varchar(100) called Surname.
> When I use Query Analyzer to retrieve data from the column and display
> as text, I get strange text appearing for one of the records. Thus, the
> field contains "Wilson" but when I run the following:
> SELECT '''' + Surname + '''', Surnname FROM tblManagers
> WHERE FirstName = 'Glenn'
> I get the following result:
> 'Wilson d g e d w i t h t h e a ' Wilson
> Where has the "d ged with the a" come from. If I show the results in a
> grid the final apostrophe is missing from the first part of the query:
> 'Wilson
> If I run a query using ADO and show the result on a web page, the extra
> text is not shown.
Looks like some junk data slipped in, and there is a NUL character hiding
there. See what
SELECT convert(varbinary(100), Surname) FROM tblManagers
WHERE FisttName = 'Glenn'
returns.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
I have a database with a column defined as varchar(100) called Surname.
When I use Query Analyzer to retrieve data from the column and display as
text, I get strange text appearing for one of the records. Thus, the field
contains "Wilson" but when I run the following:
SELECT '''' + Surname + '''', Surnname FROM tblManagers
WHERE FirstName = 'Glenn'
I get the following result:
'Wilson d g e d w i t h t h e a ' Wilson
Where has the "d ged with the a" come from. If I show the results in a grid
the final apostrophe is missing from the first part of the query:
'Wilson
If I run a query using ADO and show the result on a web page, the extra text
is not shown.
Any ideas what is going on here?
Glenn
[Reposted, as posts from outside msnews.microsoft.com does not seem to make
it in.]
BigMan2001 (BigMan2001@.discussions.microsoft.com) writes:
> I have a database with a column defined as varchar(100) called Surname.
> When I use Query Analyzer to retrieve data from the column and display
> as text, I get strange text appearing for one of the records. Thus, the
> field contains "Wilson" but when I run the following:
> SELECT '''' + Surname + '''', Surnname FROM tblManagers
> WHERE FirstName = 'Glenn'
> I get the following result:
> 'Wilson d g e d w i t h t h e a ' Wilson
> Where has the "d ged with the a" come from. If I show the results in a
> grid the final apostrophe is missing from the first part of the query:
> 'Wilson
> If I run a query using ADO and show the result on a web page, the extra
> text is not shown.
Looks like some junk data slipped in, and there is a NUL character hiding
there. See what
SELECT convert(varbinary(100), Surname) FROM tblManagers
WHERE FisttName = 'Glenn'
returns.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Mysterious Stored Procedures
SQL Server 2005 sp1
Maybe you can answer this.
Are the Stored Procedures dt_CheckOutObject and dt_CheckInObject used for some type of built-in source control?
Is source control for SQL Server built-in to the Management Studio, or is it purchased separately?
Thanks!
Frank
These are created when you connect using some Visual Tools of Microsoft, this is the list of procedures which will be created during the process:dt_addtosourcecontrol
dt_addtosourcecontrol_u
dt_adduserobject
dt_adduserobject_vcs
dt_checkinobject
dt_checkinobject_u
dt_checkoutobject
dt_checkoutobject_u
dt_displayoaerror
dt_displayoaerror_u
dt_droppropertiesbyid
dt_dropuserobjectbyid
dt_generateansiname
dt_getobjwithprop
dt_getobjwithprop_u
dt_getpropertiesbyid
dt_getpropertiesbyid_u
dt_getpropertiesbyid_vcs
dt_getpropertiesbyid_vcs_u
dt_isundersourcecontrol
dt_isundersourcecontrol_u
dt_removefromsourcecontrol
dt_setpropertybyid
dt_setpropertybyid_u
dt_validateloginparams
dt_validateloginparams_u
dt_vcsenabled
dt_verstamp006
dt_verstamp007
dt_whocheckedout
dt_whocheckedout_u
dtproperties
HTH, jens Suessmeyer.
http://www.sqlserver2005.de|||
Good morning, Jens.
Can you tell me if we can drop these sp? For example, say, for a clean distribuition of a database, with table-only content?
Thanks a lot.
Fernando M.
|||Yes, if you need created database diagrams you should leave the dbpoperties.HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Mysterious Stored Procedures
SQL Server 2005 sp1
Maybe you can answer this.
Are the Stored Procedures dt_CheckOutObject and dt_CheckInObject used for some type of built-in source control?
Is source control for SQL Server built-in to the Management Studio, or is it purchased separately?
Thanks!
Frank
These are created when you connect using some Visual Tools of Microsoft, this is the list of procedures which will be created during the process:dt_addtosourcecontrol
dt_addtosourcecontrol_u
dt_adduserobject
dt_adduserobject_vcs
dt_checkinobject
dt_checkinobject_u
dt_checkoutobject
dt_checkoutobject_u
dt_displayoaerror
dt_displayoaerror_u
dt_droppropertiesbyid
dt_dropuserobjectbyid
dt_generateansiname
dt_getobjwithprop
dt_getobjwithprop_u
dt_getpropertiesbyid
dt_getpropertiesbyid_u
dt_getpropertiesbyid_vcs
dt_getpropertiesbyid_vcs_u
dt_isundersourcecontrol
dt_isundersourcecontrol_u
dt_removefromsourcecontrol
dt_setpropertybyid
dt_setpropertybyid_u
dt_validateloginparams
dt_validateloginparams_u
dt_vcsenabled
dt_verstamp006
dt_verstamp007
dt_whocheckedout
dt_whocheckedout_u
dtproperties
HTH, jens Suessmeyer.
http://www.sqlserver2005.de|||
Good morning, Jens.
Can you tell me if we can drop these sp? For example, say, for a clean distribuition of a database, with table-only content?
Thanks a lot.
Fernando M.
|||Yes, if you need created database diagrams you should leave the dbpoperties.HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Mysterious Sql2000 process blocking
I have made several posts regarding my sql2000 cpu mysteriously staying at
100% for 10 minutes at a time. Using a performance monitor, I finally
noticed that during these times there is a sql process that is blocking the
others and causing some kind of "race" condition. The blocked processes
time out, but the blocking process seems to stay blocked often until the
service is restarted. It seems like sql should not allow this. I have not
been able to find out the cause or statements that produced the problem.
The only application using the sql server is an ASP.NET application that
uses 100% stored procedures (no dynamic sql). Does anyone have any
suggestions for finding the cause of process blocking like this? Thanks.
Dan,
You'd have to examine the code to determine this.
These may help:
http://www.sql-server-performance.co...prevention.asp
and
http://www.support.microsoft.com/?id=224453
and
http://www.support.microsoft.com/?id=271509
and
http://support.microsoft.com/?id=224587
and
http://support.microsoft.com/?id=243589
and
http://support.microsoft.com/?id=243588
HTH
Jerry
"Dan English" <dan_english2@.cox.net> wrote in message
news:eUAAmR4zFHA.2960@.tk2msftngp13.phx.gbl...
>I have made several posts regarding my sql2000 cpu mysteriously staying at
>100% for 10 minutes at a time. Using a performance monitor, I finally
>noticed that during these times there is a sql process that is blocking the
>others and causing some kind of "race" condition. The blocked processes
>time out, but the blocking process seems to stay blocked often until the
>service is restarted. It seems like sql should not allow this. I have
>not been able to find out the cause or statements that produced the
>problem. The only application using the sql server is an ASP.NET
>application that uses 100% stored procedures (no dynamic sql). Does anyone
>have any suggestions for finding the cause of process blocking like this?
>Thanks.
>
100% for 10 minutes at a time. Using a performance monitor, I finally
noticed that during these times there is a sql process that is blocking the
others and causing some kind of "race" condition. The blocked processes
time out, but the blocking process seems to stay blocked often until the
service is restarted. It seems like sql should not allow this. I have not
been able to find out the cause or statements that produced the problem.
The only application using the sql server is an ASP.NET application that
uses 100% stored procedures (no dynamic sql). Does anyone have any
suggestions for finding the cause of process blocking like this? Thanks.
Dan,
You'd have to examine the code to determine this.
These may help:
http://www.sql-server-performance.co...prevention.asp
and
http://www.support.microsoft.com/?id=224453
and
http://www.support.microsoft.com/?id=271509
and
http://support.microsoft.com/?id=224587
and
http://support.microsoft.com/?id=243589
and
http://support.microsoft.com/?id=243588
HTH
Jerry
"Dan English" <dan_english2@.cox.net> wrote in message
news:eUAAmR4zFHA.2960@.tk2msftngp13.phx.gbl...
>I have made several posts regarding my sql2000 cpu mysteriously staying at
>100% for 10 minutes at a time. Using a performance monitor, I finally
>noticed that during these times there is a sql process that is blocking the
>others and causing some kind of "race" condition. The blocked processes
>time out, but the blocking process seems to stay blocked often until the
>service is restarted. It seems like sql should not allow this. I have
>not been able to find out the cause or statements that produced the
>problem. The only application using the sql server is an ASP.NET
>application that uses 100% stored procedures (no dynamic sql). Does anyone
>have any suggestions for finding the cause of process blocking like this?
>Thanks.
>
Mysterious Sql2000 process blocking
I have made several posts regarding my sql2000 cpu mysteriously staying at
100% for 10 minutes at a time. Using a performance monitor, I finally
noticed that during these times there is a sql process that is blocking the
others and causing some kind of "race" condition. The blocked processes
time out, but the blocking process seems to stay blocked often until the
service is restarted. It seems like sql should not allow this. I have not
been able to find out the cause or statements that produced the problem.
The only application using the sql server is an ASP.NET application that
uses 100% stored procedures (no dynamic sql). Does anyone have any
suggestions for finding the cause of process blocking like this? Thanks.Dan,
You'd have to examine the code to determine this.
These may help:
http://www.sql-server-performance.com/sf_block_prevention.asp
and
http://www.support.microsoft.com/?id=224453
and
http://www.support.microsoft.com/?id=271509
and
http://support.microsoft.com/?id=224587
and
http://support.microsoft.com/?id=243589
and
http://support.microsoft.com/?id=243588
HTH
Jerry
"Dan English" <dan_english2@.cox.net> wrote in message
news:eUAAmR4zFHA.2960@.tk2msftngp13.phx.gbl...
>I have made several posts regarding my sql2000 cpu mysteriously staying at
>100% for 10 minutes at a time. Using a performance monitor, I finally
>noticed that during these times there is a sql process that is blocking the
>others and causing some kind of "race" condition. The blocked processes
>time out, but the blocking process seems to stay blocked often until the
>service is restarted. It seems like sql should not allow this. I have
>not been able to find out the cause or statements that produced the
>problem. The only application using the sql server is an ASP.NET
>application that uses 100% stored procedures (no dynamic sql). Does anyone
>have any suggestions for finding the cause of process blocking like this?
>Thanks.
>
100% for 10 minutes at a time. Using a performance monitor, I finally
noticed that during these times there is a sql process that is blocking the
others and causing some kind of "race" condition. The blocked processes
time out, but the blocking process seems to stay blocked often until the
service is restarted. It seems like sql should not allow this. I have not
been able to find out the cause or statements that produced the problem.
The only application using the sql server is an ASP.NET application that
uses 100% stored procedures (no dynamic sql). Does anyone have any
suggestions for finding the cause of process blocking like this? Thanks.Dan,
You'd have to examine the code to determine this.
These may help:
http://www.sql-server-performance.com/sf_block_prevention.asp
and
http://www.support.microsoft.com/?id=224453
and
http://www.support.microsoft.com/?id=271509
and
http://support.microsoft.com/?id=224587
and
http://support.microsoft.com/?id=243589
and
http://support.microsoft.com/?id=243588
HTH
Jerry
"Dan English" <dan_english2@.cox.net> wrote in message
news:eUAAmR4zFHA.2960@.tk2msftngp13.phx.gbl...
>I have made several posts regarding my sql2000 cpu mysteriously staying at
>100% for 10 minutes at a time. Using a performance monitor, I finally
>noticed that during these times there is a sql process that is blocking the
>others and causing some kind of "race" condition. The blocked processes
>time out, but the blocking process seems to stay blocked often until the
>service is restarted. It seems like sql should not allow this. I have
>not been able to find out the cause or statements that produced the
>problem. The only application using the sql server is an ASP.NET
>application that uses 100% stored procedures (no dynamic sql). Does anyone
>have any suggestions for finding the cause of process blocking like this?
>Thanks.
>
Mysterious Sql2000 process blocking
I have made several posts regarding my sql2000 cpu mysteriously staying at
100% for 10 minutes at a time. Using a performance monitor, I finally
noticed that during these times there is a sql process that is blocking the
others and causing some kind of "race" condition. The blocked processes
time out, but the blocking process seems to stay blocked often until the
service is restarted. It seems like sql should not allow this. I have not
been able to find out the cause or statements that produced the problem.
The only application using the sql server is an ASP.NET application that
uses 100% stored procedures (no dynamic sql). Does anyone have any
suggestions for finding the cause of process blocking like this? Thanks.Dan,
You'd have to examine the code to determine this.
These may help:
http://www.sql-server-performance.c..._prevention.asp
and
http://www.support.microsoft.com/?id=224453
and
http://www.support.microsoft.com/?id=271509
and
http://support.microsoft.com/?id=224587
and
http://support.microsoft.com/?id=243589
and
http://support.microsoft.com/?id=243588
HTH
Jerry
"Dan English" <dan_english2@.cox.net> wrote in message
news:eUAAmR4zFHA.2960@.tk2msftngp13.phx.gbl...
>I have made several posts regarding my sql2000 cpu mysteriously staying at
>100% for 10 minutes at a time. Using a performance monitor, I finally
>noticed that during these times there is a sql process that is blocking the
>others and causing some kind of "race" condition. The blocked processes
>time out, but the blocking process seems to stay blocked often until the
>service is restarted. It seems like sql should not allow this. I have
>not been able to find out the cause or statements that produced the
>problem. The only application using the sql server is an ASP.NET
>application that uses 100% stored procedures (no dynamic sql). Does anyone
>have any suggestions for finding the cause of process blocking like this?
>Thanks.
>
100% for 10 minutes at a time. Using a performance monitor, I finally
noticed that during these times there is a sql process that is blocking the
others and causing some kind of "race" condition. The blocked processes
time out, but the blocking process seems to stay blocked often until the
service is restarted. It seems like sql should not allow this. I have not
been able to find out the cause or statements that produced the problem.
The only application using the sql server is an ASP.NET application that
uses 100% stored procedures (no dynamic sql). Does anyone have any
suggestions for finding the cause of process blocking like this? Thanks.Dan,
You'd have to examine the code to determine this.
These may help:
http://www.sql-server-performance.c..._prevention.asp
and
http://www.support.microsoft.com/?id=224453
and
http://www.support.microsoft.com/?id=271509
and
http://support.microsoft.com/?id=224587
and
http://support.microsoft.com/?id=243589
and
http://support.microsoft.com/?id=243588
HTH
Jerry
"Dan English" <dan_english2@.cox.net> wrote in message
news:eUAAmR4zFHA.2960@.tk2msftngp13.phx.gbl...
>I have made several posts regarding my sql2000 cpu mysteriously staying at
>100% for 10 minutes at a time. Using a performance monitor, I finally
>noticed that during these times there is a sql process that is blocking the
>others and causing some kind of "race" condition. The blocked processes
>time out, but the blocking process seems to stay blocked often until the
>service is restarted. It seems like sql should not allow this. I have
>not been able to find out the cause or statements that produced the
>problem. The only application using the sql server is an ASP.NET
>application that uses 100% stored procedures (no dynamic sql). Does anyone
>have any suggestions for finding the cause of process blocking like this?
>Thanks.
>
Mysterious SQL Server Dropping Tables
We had just setup MS SQL 2000 (SP3), running on Windows 2000 (SP4). Both SQL
and Windows2000 Server has been patched with all security updates as per
Microsoft websites.
On the server PC, it is also running Norton Antivirus Corporate Edition 7.6
with the latest AV definition. I've also lost count of how many times we
scan the server for virus but none were found.
This is a newly setup server but we're observing the tables in the SQL
server dropping out of no apparent reason. A check on the actual data
directory we found that the database .LDF is missing and the EM marks it as
'suspect'.
What kind of information should I provide in order to further trouble shoot
this problem?
Since this is a test server we're only running it on 2 x 80GB IDE harddisk
and as far as I can tell there is no bad sectors found. Has anyone encounter
anything of such? Please help. TQ.
--
Steven Ung
"The source of all greatness lies within you" - Anonymous> This is a newly setup server but we're observing the tables in the SQL
> server dropping out of no apparent reason. A check on the actual data
> directory we found that the database .LDF is missing and the EM marks it
> as
> 'suspect'.
The tables, or the log file? Sounds like both are happening. Once you get
the system back where it should be, you might want to use a program like
filemon (http://www.sysinternals.com/ntw2k/utilities.shtml) to see what
process is accessing the LDF file.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:ulucZdfHEHA.2876@.TK2MSFTNGP09.phx.gbl...
> The tables, or the log file? Sounds like both are happening.
Both, but the tables are the ones dropping/missing first.
Does drive caching has anything to do with it? The Windows Event Log
complaints that drive cache is disabled. I've temporary enable it and still
checking the results. But since no one has had this problem before, I'm not
certain of whether this is the solution or what is causing the tables to
drop or go missing.
> Once you get
> the system back where it should be, you might want to use a program like
> filemon (http://www.sysinternals.com/ntw2k/utilities.shtml) to see what
> process is accessing the LDF file.
I've downloaded the utility but could not find any process out of the
extraordinary.
Steven Ung
"The source of all greatness lies within you" - Anonymous
and Windows2000 Server has been patched with all security updates as per
Microsoft websites.
On the server PC, it is also running Norton Antivirus Corporate Edition 7.6
with the latest AV definition. I've also lost count of how many times we
scan the server for virus but none were found.
This is a newly setup server but we're observing the tables in the SQL
server dropping out of no apparent reason. A check on the actual data
directory we found that the database .LDF is missing and the EM marks it as
'suspect'.
What kind of information should I provide in order to further trouble shoot
this problem?
Since this is a test server we're only running it on 2 x 80GB IDE harddisk
and as far as I can tell there is no bad sectors found. Has anyone encounter
anything of such? Please help. TQ.
--
Steven Ung
"The source of all greatness lies within you" - Anonymous> This is a newly setup server but we're observing the tables in the SQL
> server dropping out of no apparent reason. A check on the actual data
> directory we found that the database .LDF is missing and the EM marks it
> as
> 'suspect'.
The tables, or the log file? Sounds like both are happening. Once you get
the system back where it should be, you might want to use a program like
filemon (http://www.sysinternals.com/ntw2k/utilities.shtml) to see what
process is accessing the LDF file.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:ulucZdfHEHA.2876@.TK2MSFTNGP09.phx.gbl...
> The tables, or the log file? Sounds like both are happening.
Both, but the tables are the ones dropping/missing first.
Does drive caching has anything to do with it? The Windows Event Log
complaints that drive cache is disabled. I've temporary enable it and still
checking the results. But since no one has had this problem before, I'm not
certain of whether this is the solution or what is causing the tables to
drop or go missing.
> Once you get
> the system back where it should be, you might want to use a program like
> filemon (http://www.sysinternals.com/ntw2k/utilities.shtml) to see what
> process is accessing the LDF file.
I've downloaded the utility but could not find any process out of the
extraordinary.
Steven Ung
"The source of all greatness lies within you" - Anonymous
Mysterious SQL Server Dropping Tables
We had just setup MS SQL 2000 (SP3), running on Windows 2000 (SP4). Both SQL
and Windows2000 Server has been patched with all security updates as per
Microsoft websites.
On the server PC, it is also running Norton Antivirus Corporate Edition 7.6
with the latest AV definition. I've also lost count of how many times we
scan the server for virus but none were found.
This is a newly setup server but we're observing the tables in the SQL
server dropping out of no apparent reason. A check on the actual data
directory we found that the database .LDF is missing and the EM marks it as
'suspect'.
What kind of information should I provide in order to further trouble shoot
this problem?
Since this is a test server we're only running it on 2 x 80GB IDE harddisk
and as far as I can tell there is no bad sectors found. Has anyone encounter
anything of such? Please help. TQ.
--
Steven Ung
"The source of all greatness lies within you" - Anonymous> This is a newly setup server but we're observing the tables in the SQL
> server dropping out of no apparent reason. A check on the actual data
> directory we found that the database .LDF is missing and the EM marks it
> as
> 'suspect'.
The tables, or the log file? Sounds like both are happening. Once you get
the system back where it should be, you might want to use a program like
filemon (http://www.sysinternals.com/ntw2k/utilities.shtml) to see what
process is accessing the LDF file.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:ulucZdfHEHA.2876@.TK2MSFTNGP09.phx.gbl...
> > This is a newly setup server but we're observing the tables in the SQL
> > server dropping out of no apparent reason. A check on the actual data
> > directory we found that the database .LDF is missing and the EM marks it
> > as
> > 'suspect'.
> The tables, or the log file? Sounds like both are happening.
Both, but the tables are the ones dropping/missing first.
Does drive caching has anything to do with it? The Windows Event Log
complaints that drive cache is disabled. I've temporary enable it and still
checking the results. But since no one has had this problem before, I'm not
certain of whether this is the solution or what is causing the tables to
drop or go missing.
> Once you get
> the system back where it should be, you might want to use a program like
> filemon (http://www.sysinternals.com/ntw2k/utilities.shtml) to see what
> process is accessing the LDF file.
I've downloaded the utility but could not find any process out of the
extraordinary.
--
Steven Ung
"The source of all greatness lies within you" - Anonymous
and Windows2000 Server has been patched with all security updates as per
Microsoft websites.
On the server PC, it is also running Norton Antivirus Corporate Edition 7.6
with the latest AV definition. I've also lost count of how many times we
scan the server for virus but none were found.
This is a newly setup server but we're observing the tables in the SQL
server dropping out of no apparent reason. A check on the actual data
directory we found that the database .LDF is missing and the EM marks it as
'suspect'.
What kind of information should I provide in order to further trouble shoot
this problem?
Since this is a test server we're only running it on 2 x 80GB IDE harddisk
and as far as I can tell there is no bad sectors found. Has anyone encounter
anything of such? Please help. TQ.
--
Steven Ung
"The source of all greatness lies within you" - Anonymous> This is a newly setup server but we're observing the tables in the SQL
> server dropping out of no apparent reason. A check on the actual data
> directory we found that the database .LDF is missing and the EM marks it
> as
> 'suspect'.
The tables, or the log file? Sounds like both are happening. Once you get
the system back where it should be, you might want to use a program like
filemon (http://www.sysinternals.com/ntw2k/utilities.shtml) to see what
process is accessing the LDF file.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:ulucZdfHEHA.2876@.TK2MSFTNGP09.phx.gbl...
> > This is a newly setup server but we're observing the tables in the SQL
> > server dropping out of no apparent reason. A check on the actual data
> > directory we found that the database .LDF is missing and the EM marks it
> > as
> > 'suspect'.
> The tables, or the log file? Sounds like both are happening.
Both, but the tables are the ones dropping/missing first.
Does drive caching has anything to do with it? The Windows Event Log
complaints that drive cache is disabled. I've temporary enable it and still
checking the results. But since no one has had this problem before, I'm not
certain of whether this is the solution or what is causing the tables to
drop or go missing.
> Once you get
> the system back where it should be, you might want to use a program like
> filemon (http://www.sysinternals.com/ntw2k/utilities.shtml) to see what
> process is accessing the LDF file.
I've downloaded the utility but could not find any process out of the
extraordinary.
--
Steven Ung
"The source of all greatness lies within you" - Anonymous
Mysterious SQL Server Dropping Tables
We had just setup MS SQL 2000 (SP3), running on Windows 2000 (SP4). Both SQL
and Windows2000 Server has been patched with all security updates as per
Microsoft websites.
On the server PC, it is also running Norton Antivirus Corporate Edition 7.6
with the latest AV definition. I've also lost count of how many times we
scan the server for virus but none were found.
This is a newly setup server but we're observing the tables in the SQL
server dropping out of no apparent reason. A check on the actual data
directory we found that the database .LDF is missing and the EM marks it as
'suspect'.
What kind of information should I provide in order to further trouble shoot
this problem?
Since this is a test server we're only running it on 2 x 80GB IDE harddisk
and as far as I can tell there is no bad sectors found. Has anyone encounter
anything of such? Please help. TQ.
Steven Ung
"The source of all greatness lies within you" - Anonymous
> This is a newly setup server but we're observing the tables in the SQL
> server dropping out of no apparent reason. A check on the actual data
> directory we found that the database .LDF is missing and the EM marks it
> as
> 'suspect'.
The tables, or the log file? Sounds like both are happening. Once you get
the system back where it should be, you might want to use a program like
filemon (http://www.sysinternals.com/ntw2k/utilities.shtml) to see what
process is accessing the LDF file.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:ulucZdfHEHA.2876@.TK2MSFTNGP09.phx.gbl...
> The tables, or the log file? Sounds like both are happening.
Both, but the tables are the ones dropping/missing first.
Does drive caching has anything to do with it? The Windows Event Log
complaints that drive cache is disabled. I've temporary enable it and still
checking the results. But since no one has had this problem before, I'm not
certain of whether this is the solution or what is causing the tables to
drop or go missing.
> Once you get
> the system back where it should be, you might want to use a program like
> filemon (http://www.sysinternals.com/ntw2k/utilities.shtml) to see what
> process is accessing the LDF file.
I've downloaded the utility but could not find any process out of the
extraordinary.
Steven Ung
"The source of all greatness lies within you" - Anonymous
and Windows2000 Server has been patched with all security updates as per
Microsoft websites.
On the server PC, it is also running Norton Antivirus Corporate Edition 7.6
with the latest AV definition. I've also lost count of how many times we
scan the server for virus but none were found.
This is a newly setup server but we're observing the tables in the SQL
server dropping out of no apparent reason. A check on the actual data
directory we found that the database .LDF is missing and the EM marks it as
'suspect'.
What kind of information should I provide in order to further trouble shoot
this problem?
Since this is a test server we're only running it on 2 x 80GB IDE harddisk
and as far as I can tell there is no bad sectors found. Has anyone encounter
anything of such? Please help. TQ.
Steven Ung
"The source of all greatness lies within you" - Anonymous
> This is a newly setup server but we're observing the tables in the SQL
> server dropping out of no apparent reason. A check on the actual data
> directory we found that the database .LDF is missing and the EM marks it
> as
> 'suspect'.
The tables, or the log file? Sounds like both are happening. Once you get
the system back where it should be, you might want to use a program like
filemon (http://www.sysinternals.com/ntw2k/utilities.shtml) to see what
process is accessing the LDF file.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:ulucZdfHEHA.2876@.TK2MSFTNGP09.phx.gbl...
> The tables, or the log file? Sounds like both are happening.
Both, but the tables are the ones dropping/missing first.
Does drive caching has anything to do with it? The Windows Event Log
complaints that drive cache is disabled. I've temporary enable it and still
checking the results. But since no one has had this problem before, I'm not
certain of whether this is the solution or what is causing the tables to
drop or go missing.
> Once you get
> the system back where it should be, you might want to use a program like
> filemon (http://www.sysinternals.com/ntw2k/utilities.shtml) to see what
> process is accessing the LDF file.
I've downloaded the utility but could not find any process out of the
extraordinary.
Steven Ung
"The source of all greatness lies within you" - Anonymous
mysterious restore
I am a support rep for an app running on SQL Server 7 sp3.
The customer had a table mysteriously restored along with the dependencies [sp.s, indexes, etc] for that table.
Is there any logging in sql server that will show when tables or objects have been dropped, recreated or restored?
thanksLook up the msdb..restorehistory table.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"tlr" <anonymous@.discussions.microsoft.com> wrote in message
news:86C067AA-72E8-49CD-A34A-1E79E4E03315@.microsoft.com...
I am a support rep for an app running on SQL Server 7 sp3.
The customer had a table mysteriously restored along with the dependencies
[sp.s, indexes, etc] for that table.
Is there any logging in sql server that will show when tables or objects
have been dropped, recreated or restored?
thanks
The customer had a table mysteriously restored along with the dependencies [sp.s, indexes, etc] for that table.
Is there any logging in sql server that will show when tables or objects have been dropped, recreated or restored?
thanksLook up the msdb..restorehistory table.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"tlr" <anonymous@.discussions.microsoft.com> wrote in message
news:86C067AA-72E8-49CD-A34A-1E79E4E03315@.microsoft.com...
I am a support rep for an app running on SQL Server 7 sp3.
The customer had a table mysteriously restored along with the dependencies
[sp.s, indexes, etc] for that table.
Is there any logging in sql server that will show when tables or objects
have been dropped, recreated or restored?
thanks
Mysterious query problem?
Hey all,
strange problem here... query #1 displays 357 records correctly and all
is well. However, when placed within query #2 as a subquery, it updates
every single record in the lta table, what's going on here? any
thoughts?
strange problem here... query #1 displays 357 records correctly and all
is well. However, when placed within query #2 as a subquery, it updates
every single record in the lta table, what's going on here? any
thoughts?
1.) select *
from LTA INNER JOIN new_list
ON lta.voy = new_list.voy AND
lta.poe = new_list.poe
2.)
update lta
set lta.LL_RCVD = 'N'
where exists (select *
from LTA INNER JOIN new_list
ON lta.voy = new_list.voy AND
lta.poe = new_list.poe)Gah! What am I doing wrong? The query below is a modification of query
#2 above, yet updates 32 records (all of which are NOT located in table
new_list)??!
Please help!
update lta
set LL_RCVD = 'j'
from lta a, new_list b
where a.voy = b.voy AND
a.poe = b.poe|||On 10 Jan 2005 07:25:14 -0800, Roy wrote:
>Hey all,
>strange problem here... query #1 displays 357 records correctly and all
>is well. However, when placed within query #2 as a subquery, it updates
>every single record in the lta table, what's going on here? any
>thoughts?
>
>1.) select *
>from LTA INNER JOIN new_list
>ON lta.voy = new_list.voy AND
>lta.poe = new_list.poe
>
>2.)
>update lta
>set lta.LL_RCVD = 'N'
>where exists (select *
>from LTA INNER JOIN new_list
>ON lta.voy = new_list.voy AND
>lta.poe = new_list.poe)
Hi Roy,
The subquery on #2 doesn't reference the outer query. Hence, it returns
the same 357 rows for each row in LTA (from the outer query), so the
EXISTS predicate is always true.
You probably need
UPDATE lta
SET LL_RCVD = 'N'
WHERE EXISTS (SELECT *
FROM new_list
WHERE new_list.voy = lta.voy
AND new_list.poe = lta.poe)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 10 Jan 2005 07:42:14 -0800, Roy wrote:
>Gah! What am I doing wrong? The query below is a modification of query
>#2 above, yet updates 32 records (all of which are NOT located in table
>new_list)??!
>Please help!
>update lta
>set LL_RCVD = 'j'
>from lta a, new_list b
>where a.voy = b.voy AND
>a.poe = b.poe
Hi Roy,
This should affect the same rows as the query I suggested in my reply to
your other message (but this one sets LL_RVCD to 'j'; the other one sets
it to 'N').
An important difference occurs if one row in lta matches more than one row
in new_list (and, since the select returns more rows than are affected by
the update statement, this appears to be the case with your data). The
above query will repeatedly change the values for any lta row that matches
more than one new_list row (with the results being undefined, though in
you case, where the new value is a constant, the results will be as
expected); my version will simply update the rows exactly once.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I love you forever, thanks Hugo!
Mysterious Performance Degradation
Hi,
A batch job which updates a table in a SQL Server database took three hours
to run last week. Nothing changed except perhaps the database grew by 1%.
Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
log file to match the production database server - this same job runs in
about 2 hours on that server. I monitored the buffer cache hit ratio,
%Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
Time, and Processor Queue Length counters - they all fall well within
recommended thresholds. The difference between the two servers: the problem
server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
know the job on my server won'f finish as fast as the same job on the Prod
server. But why this sudden change from 3 to 30 hours?
index maintenance being done ?
Greg Jackson
PDX, Oregon
|||The database had been restored using a production database backup. The
indexes are regularly defragmented on the production database.
"pdxJaxon" wrote:
> index maintenance being done ?
>
> Greg Jackson
> PDX, Oregon
>
>
|||need to perform update statistics on the dev server.
"Simi B." wrote:
[vbcol=seagreen]
> The database had been restored using a production database backup. The
> indexes are regularly defragmented on the production database.
> "pdxJaxon" wrote:
|||Dis you check the execution plan of the job on both the servers. That might
give you insight into if some statistics are missing etc.
"Simi B." wrote:
> Hi,
> A batch job which updates a table in a SQL Server database took three hours
> to run last week. Nothing changed except perhaps the database grew by 1%.
> Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> log file to match the production database server - this same job runs in
> about 2 hours on that server. I monitored the buffer cache hit ratio,
> %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> Time, and Processor Queue Length counters - they all fall well within
> recommended thresholds. The difference between the two servers: the problem
> server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> know the job on my server won'f finish as fast as the same job on the Prod
> server. But why this sudden change from 3 to 30 hours?
|||Thanks all for your help. I was able to get it down from 41 hours to 7
hours...will see how I can tune it some more... =)
"harvinder" wrote:
[vbcol=seagreen]
> Dis you check the execution plan of the job on both the servers. That might
> give you insight into if some statistics are missing etc.
> "Simi B." wrote:
|||Simi B,
I am facing a similar unexpected performance with one of my production
database servers. What was it that reduced your execution time?
I'm similarly stuck.
Jeremiah
"Simi B." wrote:
[vbcol=seagreen]
> Thanks all for your help. I was able to get it down from 41 hours to 7
> hours...will see how I can tune it some more... =)
> "harvinder" wrote:
|||I reindexed all the tables, ran dbcc checkdb, updated statistics,
defragmented the hard drive on which the database resided.
"Jeremiah Traxler" wrote:
[vbcol=seagreen]
> Simi B,
> I am facing a similar unexpected performance with one of my production
> database servers. What was it that reduced your execution time?
> I'm similarly stuck.
> Jeremiah
> "Simi B." wrote:
A batch job which updates a table in a SQL Server database took three hours
to run last week. Nothing changed except perhaps the database grew by 1%.
Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
log file to match the production database server - this same job runs in
about 2 hours on that server. I monitored the buffer cache hit ratio,
%Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
Time, and Processor Queue Length counters - they all fall well within
recommended thresholds. The difference between the two servers: the problem
server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
know the job on my server won'f finish as fast as the same job on the Prod
server. But why this sudden change from 3 to 30 hours?
index maintenance being done ?
Greg Jackson
PDX, Oregon
|||The database had been restored using a production database backup. The
indexes are regularly defragmented on the production database.
"pdxJaxon" wrote:
> index maintenance being done ?
>
> Greg Jackson
> PDX, Oregon
>
>
|||need to perform update statistics on the dev server.
"Simi B." wrote:
[vbcol=seagreen]
> The database had been restored using a production database backup. The
> indexes are regularly defragmented on the production database.
> "pdxJaxon" wrote:
|||Dis you check the execution plan of the job on both the servers. That might
give you insight into if some statistics are missing etc.
"Simi B." wrote:
> Hi,
> A batch job which updates a table in a SQL Server database took three hours
> to run last week. Nothing changed except perhaps the database grew by 1%.
> Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> log file to match the production database server - this same job runs in
> about 2 hours on that server. I monitored the buffer cache hit ratio,
> %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> Time, and Processor Queue Length counters - they all fall well within
> recommended thresholds. The difference between the two servers: the problem
> server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> know the job on my server won'f finish as fast as the same job on the Prod
> server. But why this sudden change from 3 to 30 hours?
|||Thanks all for your help. I was able to get it down from 41 hours to 7
hours...will see how I can tune it some more... =)
"harvinder" wrote:
[vbcol=seagreen]
> Dis you check the execution plan of the job on both the servers. That might
> give you insight into if some statistics are missing etc.
> "Simi B." wrote:
|||Simi B,
I am facing a similar unexpected performance with one of my production
database servers. What was it that reduced your execution time?
I'm similarly stuck.
Jeremiah
"Simi B." wrote:
[vbcol=seagreen]
> Thanks all for your help. I was able to get it down from 41 hours to 7
> hours...will see how I can tune it some more... =)
> "harvinder" wrote:
|||I reindexed all the tables, ran dbcc checkdb, updated statistics,
defragmented the hard drive on which the database resided.
"Jeremiah Traxler" wrote:
[vbcol=seagreen]
> Simi B,
> I am facing a similar unexpected performance with one of my production
> database servers. What was it that reduced your execution time?
> I'm similarly stuck.
> Jeremiah
> "Simi B." wrote:
Mysterious Performance Degradation
Hi,
A batch job which updates a table in a SQL Server database took three hours
to run last week. Nothing changed except perhaps the database grew by 1%.
Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
log file to match the production database server - this same job runs in
about 2 hours on that server. I monitored the buffer cache hit ratio,
%Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
Time, and Processor Queue Length counters - they all fall well within
recommended thresholds. The difference between the two servers: the problem
server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
know the job on my server won'f finish as fast as the same job on the Prod
server. But why this sudden change from 3 to 30 hours?index maintenance being done ?
Greg Jackson
PDX, Oregon|||The database had been restored using a production database backup. The
indexes are regularly defragmented on the production database.
"pdxJaxon" wrote:
> index maintenance being done ?
>
> Greg Jackson
> PDX, Oregon
>
>|||need to perform update statistics on the dev server.
"Simi B." wrote:
[vbcol=seagreen]
> The database had been restored using a production database backup. The
> indexes are regularly defragmented on the production database.
> "pdxJaxon" wrote:
>|||Dis you check the execution plan of the job on both the servers. That might
give you insight into if some statistics are missing etc.
"Simi B." wrote:
> Hi,
> A batch job which updates a table in a SQL Server database took three hour
s
> to run last week. Nothing changed except perhaps the database grew by 1%.
> Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> log file to match the production database server - this same job runs in
> about 2 hours on that server. I monitored the buffer cache hit ratio,
> %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> Time, and Processor Queue Length counters - they all fall well within
> recommended thresholds. The difference between the two servers: the probl
em
> server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> know the job on my server won'f finish as fast as the same job on the Prod
> server. But why this sudden change from 3 to 30 hours?|||Thanks all for your help. I was able to get it down from 41 hours to 7
hours...will see how I can tune it some more... =)
"harvinder" wrote:
[vbcol=seagreen]
> Dis you check the execution plan of the job on both the servers. That migh
t
> give you insight into if some statistics are missing etc.
> "Simi B." wrote:
>|||Simi B,
I am facing a similar unexpected performance with one of my production
database servers. What was it that reduced your execution time?
I'm similarly stuck.
Jeremiah
"Simi B." wrote:
[vbcol=seagreen]
> Thanks all for your help. I was able to get it down from 41 hours to 7
> hours...will see how I can tune it some more... =)
> "harvinder" wrote:
>|||I reindexed all the tables, ran dbcc checkdb, updated statistics,
defragmented the hard drive on which the database resided.
"Jeremiah Traxler" wrote:
[vbcol=seagreen]
> Simi B,
> I am facing a similar unexpected performance with one of my production
> database servers. What was it that reduced your execution time?
> I'm similarly stuck.
> Jeremiah
> "Simi B." wrote:
>
A batch job which updates a table in a SQL Server database took three hours
to run last week. Nothing changed except perhaps the database grew by 1%.
Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
log file to match the production database server - this same job runs in
about 2 hours on that server. I monitored the buffer cache hit ratio,
%Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
Time, and Processor Queue Length counters - they all fall well within
recommended thresholds. The difference between the two servers: the problem
server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
know the job on my server won'f finish as fast as the same job on the Prod
server. But why this sudden change from 3 to 30 hours?index maintenance being done ?
Greg Jackson
PDX, Oregon|||The database had been restored using a production database backup. The
indexes are regularly defragmented on the production database.
"pdxJaxon" wrote:
> index maintenance being done ?
>
> Greg Jackson
> PDX, Oregon
>
>|||need to perform update statistics on the dev server.
"Simi B." wrote:
[vbcol=seagreen]
> The database had been restored using a production database backup. The
> indexes are regularly defragmented on the production database.
> "pdxJaxon" wrote:
>|||Dis you check the execution plan of the job on both the servers. That might
give you insight into if some statistics are missing etc.
"Simi B." wrote:
> Hi,
> A batch job which updates a table in a SQL Server database took three hour
s
> to run last week. Nothing changed except perhaps the database grew by 1%.
> Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> log file to match the production database server - this same job runs in
> about 2 hours on that server. I monitored the buffer cache hit ratio,
> %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> Time, and Processor Queue Length counters - they all fall well within
> recommended thresholds. The difference between the two servers: the probl
em
> server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> know the job on my server won'f finish as fast as the same job on the Prod
> server. But why this sudden change from 3 to 30 hours?|||Thanks all for your help. I was able to get it down from 41 hours to 7
hours...will see how I can tune it some more... =)
"harvinder" wrote:
[vbcol=seagreen]
> Dis you check the execution plan of the job on both the servers. That migh
t
> give you insight into if some statistics are missing etc.
> "Simi B." wrote:
>|||Simi B,
I am facing a similar unexpected performance with one of my production
database servers. What was it that reduced your execution time?
I'm similarly stuck.
Jeremiah
"Simi B." wrote:
[vbcol=seagreen]
> Thanks all for your help. I was able to get it down from 41 hours to 7
> hours...will see how I can tune it some more... =)
> "harvinder" wrote:
>|||I reindexed all the tables, ran dbcc checkdb, updated statistics,
defragmented the hard drive on which the database resided.
"Jeremiah Traxler" wrote:
[vbcol=seagreen]
> Simi B,
> I am facing a similar unexpected performance with one of my production
> database servers. What was it that reduced your execution time?
> I'm similarly stuck.
> Jeremiah
> "Simi B." wrote:
>
Mysterious Performance Degradation
Hi,
A batch job which updates a table in a SQL Server database took three hours
to run last week. Nothing changed except perhaps the database grew by 1%.
Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
log file to match the production database server - this same job runs in
about 2 hours on that server. I monitored the buffer cache hit ratio,
%Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
Time, and Processor Queue Length counters - they all fall well within
recommended thresholds. The difference between the two servers: the problem
server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
know the job on my server won'f finish as fast as the same job on the Prod
server. But why this sudden change from 3 to 30 hours?index maintenance being done ?
Greg Jackson
PDX, Oregon|||The database had been restored using a production database backup. The
indexes are regularly defragmented on the production database.
"pdxJaxon" wrote:
> index maintenance being done ?
>
> Greg Jackson
> PDX, Oregon
>
>|||need to perform update statistics on the dev server.
"Simi B." wrote:
> The database had been restored using a production database backup. The
> indexes are regularly defragmented on the production database.
> "pdxJaxon" wrote:
> > index maintenance being done ?
> >
> >
> > Greg Jackson
> > PDX, Oregon
> >
> >
> >|||Dis you check the execution plan of the job on both the servers. That might
give you insight into if some statistics are missing etc.
"Simi B." wrote:
> Hi,
> A batch job which updates a table in a SQL Server database took three hours
> to run last week. Nothing changed except perhaps the database grew by 1%.
> Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> log file to match the production database server - this same job runs in
> about 2 hours on that server. I monitored the buffer cache hit ratio,
> %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> Time, and Processor Queue Length counters - they all fall well within
> recommended thresholds. The difference between the two servers: the problem
> server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> know the job on my server won'f finish as fast as the same job on the Prod
> server. But why this sudden change from 3 to 30 hours?|||Thanks all for your help. I was able to get it down from 41 hours to 7
hours...will see how I can tune it some more... =)
"harvinder" wrote:
> Dis you check the execution plan of the job on both the servers. That might
> give you insight into if some statistics are missing etc.
> "Simi B." wrote:
> > Hi,
> > A batch job which updates a table in a SQL Server database took three hours
> > to run last week. Nothing changed except perhaps the database grew by 1%.
> > Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> > log file to match the production database server - this same job runs in
> > about 2 hours on that server. I monitored the buffer cache hit ratio,
> > %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> > Time, and Processor Queue Length counters - they all fall well within
> > recommended thresholds. The difference between the two servers: the problem
> > server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> > know the job on my server won'f finish as fast as the same job on the Prod
> > server. But why this sudden change from 3 to 30 hours?|||Simi B,
I am facing a similar unexpected performance with one of my production
database servers. What was it that reduced your execution time?
I'm similarly stuck.
Jeremiah
"Simi B." wrote:
> Thanks all for your help. I was able to get it down from 41 hours to 7
> hours...will see how I can tune it some more... =)
> "harvinder" wrote:
> > Dis you check the execution plan of the job on both the servers. That might
> > give you insight into if some statistics are missing etc.
> >
> > "Simi B." wrote:
> >
> > > Hi,
> > > A batch job which updates a table in a SQL Server database took three hours
> > > to run last week. Nothing changed except perhaps the database grew by 1%.
> > > Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> > > log file to match the production database server - this same job runs in
> > > about 2 hours on that server. I monitored the buffer cache hit ratio,
> > > %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> > > Time, and Processor Queue Length counters - they all fall well within
> > > recommended thresholds. The difference between the two servers: the problem
> > > server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> > > know the job on my server won'f finish as fast as the same job on the Prod
> > > server. But why this sudden change from 3 to 30 hours?|||I reindexed all the tables, ran dbcc checkdb, updated statistics,
defragmented the hard drive on which the database resided.
"Jeremiah Traxler" wrote:
> Simi B,
> I am facing a similar unexpected performance with one of my production
> database servers. What was it that reduced your execution time?
> I'm similarly stuck.
> Jeremiah
> "Simi B." wrote:
> > Thanks all for your help. I was able to get it down from 41 hours to 7
> > hours...will see how I can tune it some more... =)
> >
> > "harvinder" wrote:
> >
> > > Dis you check the execution plan of the job on both the servers. That might
> > > give you insight into if some statistics are missing etc.
> > >
> > > "Simi B." wrote:
> > >
> > > > Hi,
> > > > A batch job which updates a table in a SQL Server database took three hours
> > > > to run last week. Nothing changed except perhaps the database grew by 1%.
> > > > Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> > > > log file to match the production database server - this same job runs in
> > > > about 2 hours on that server. I monitored the buffer cache hit ratio,
> > > > %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> > > > Time, and Processor Queue Length counters - they all fall well within
> > > > recommended thresholds. The difference between the two servers: the problem
> > > > server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> > > > know the job on my server won'f finish as fast as the same job on the Prod
> > > > server. But why this sudden change from 3 to 30 hours?
A batch job which updates a table in a SQL Server database took three hours
to run last week. Nothing changed except perhaps the database grew by 1%.
Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
log file to match the production database server - this same job runs in
about 2 hours on that server. I monitored the buffer cache hit ratio,
%Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
Time, and Processor Queue Length counters - they all fall well within
recommended thresholds. The difference between the two servers: the problem
server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
know the job on my server won'f finish as fast as the same job on the Prod
server. But why this sudden change from 3 to 30 hours?index maintenance being done ?
Greg Jackson
PDX, Oregon|||The database had been restored using a production database backup. The
indexes are regularly defragmented on the production database.
"pdxJaxon" wrote:
> index maintenance being done ?
>
> Greg Jackson
> PDX, Oregon
>
>|||need to perform update statistics on the dev server.
"Simi B." wrote:
> The database had been restored using a production database backup. The
> indexes are regularly defragmented on the production database.
> "pdxJaxon" wrote:
> > index maintenance being done ?
> >
> >
> > Greg Jackson
> > PDX, Oregon
> >
> >
> >|||Dis you check the execution plan of the job on both the servers. That might
give you insight into if some statistics are missing etc.
"Simi B." wrote:
> Hi,
> A batch job which updates a table in a SQL Server database took three hours
> to run last week. Nothing changed except perhaps the database grew by 1%.
> Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> log file to match the production database server - this same job runs in
> about 2 hours on that server. I monitored the buffer cache hit ratio,
> %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> Time, and Processor Queue Length counters - they all fall well within
> recommended thresholds. The difference between the two servers: the problem
> server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> know the job on my server won'f finish as fast as the same job on the Prod
> server. But why this sudden change from 3 to 30 hours?|||Thanks all for your help. I was able to get it down from 41 hours to 7
hours...will see how I can tune it some more... =)
"harvinder" wrote:
> Dis you check the execution plan of the job on both the servers. That might
> give you insight into if some statistics are missing etc.
> "Simi B." wrote:
> > Hi,
> > A batch job which updates a table in a SQL Server database took three hours
> > to run last week. Nothing changed except perhaps the database grew by 1%.
> > Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> > log file to match the production database server - this same job runs in
> > about 2 hours on that server. I monitored the buffer cache hit ratio,
> > %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> > Time, and Processor Queue Length counters - they all fall well within
> > recommended thresholds. The difference between the two servers: the problem
> > server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> > know the job on my server won'f finish as fast as the same job on the Prod
> > server. But why this sudden change from 3 to 30 hours?|||Simi B,
I am facing a similar unexpected performance with one of my production
database servers. What was it that reduced your execution time?
I'm similarly stuck.
Jeremiah
"Simi B." wrote:
> Thanks all for your help. I was able to get it down from 41 hours to 7
> hours...will see how I can tune it some more... =)
> "harvinder" wrote:
> > Dis you check the execution plan of the job on both the servers. That might
> > give you insight into if some statistics are missing etc.
> >
> > "Simi B." wrote:
> >
> > > Hi,
> > > A batch job which updates a table in a SQL Server database took three hours
> > > to run last week. Nothing changed except perhaps the database grew by 1%.
> > > Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> > > log file to match the production database server - this same job runs in
> > > about 2 hours on that server. I monitored the buffer cache hit ratio,
> > > %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> > > Time, and Processor Queue Length counters - they all fall well within
> > > recommended thresholds. The difference between the two servers: the problem
> > > server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> > > know the job on my server won'f finish as fast as the same job on the Prod
> > > server. But why this sudden change from 3 to 30 hours?|||I reindexed all the tables, ran dbcc checkdb, updated statistics,
defragmented the hard drive on which the database resided.
"Jeremiah Traxler" wrote:
> Simi B,
> I am facing a similar unexpected performance with one of my production
> database servers. What was it that reduced your execution time?
> I'm similarly stuck.
> Jeremiah
> "Simi B." wrote:
> > Thanks all for your help. I was able to get it down from 41 hours to 7
> > hours...will see how I can tune it some more... =)
> >
> > "harvinder" wrote:
> >
> > > Dis you check the execution plan of the job on both the servers. That might
> > > give you insight into if some statistics are missing etc.
> > >
> > > "Simi B." wrote:
> > >
> > > > Hi,
> > > > A batch job which updates a table in a SQL Server database took three hours
> > > > to run last week. Nothing changed except perhaps the database grew by 1%.
> > > > Suddenly this job takes 30+ hours to finish. I increased tempDB datafile,
> > > > log file to match the production database server - this same job runs in
> > > > about 2 hours on that server. I monitored the buffer cache hit ratio,
> > > > %Processor Time, Pages/Sec, Avg Disk Queue Length, Available Bytes, %Disk
> > > > Time, and Processor Queue Length counters - they all fall well within
> > > > recommended thresholds. The difference between the two servers: the problem
> > > > server has 1 GB of RAM whereas the Production server has 2 GB of RAM. I
> > > > know the job on my server won'f finish as fast as the same job on the Prod
> > > > server. But why this sudden change from 3 to 30 hours?
Mysterious mystery -- 2002-11-08/2002-11-09
I have function that returns a table of information about properties. The
data comes from three different tables -- addresses (called PropertyID),
property characteristics, and events concerning those properties (sales,
appraisals, etc.), plus a table that maps one representation of property
types into another. The records are selected on the basis of location
(longitude & latitude), property type, event type, and a range of
event dates (upper and lower date specified). There are tens of millions
of records of all types, and almost any location, property type, event type
and date range will yield records.
data comes from three different tables -- addresses (called PropertyID),
property characteristics, and events concerning those properties (sales,
appraisals, etc.), plus a table that maps one representation of property
types into another. The records are selected on the basis of location
(longitude & latitude), property type, event type, and a range of
event dates (upper and lower date specified). There are tens of millions
of records of all types, and almost any location, property type, event type
and date range will yield records.
The heart of it is a cursor that selects records from joins on this basis:
SELECT <a bunch of fields>
FROM Property d
JOIN PropTypeMap ptm ON ptm.PropertyTypeID = d.PropertyTypeID
JOIN PropertyID a ON a.PropID = d.PropID
JOIN Event e1 ON e1.PropID = d.PropID
LEFT OUTER JOIN Event e2 ON e2.PropID = d.PropID
WHERE
d.LastSaleDate >= @.LoDate
AND a.GeoLongitude BETWEEN @.LowerLon AND @.UpperLon
AND a.GeoLatitude BETWEEN @.LowerLat AND @.UpperLat
AND ptm.PropCategory = @.PropType
AND a.GeoMatch <= @.MinGeoQuality
AND e1.EventTypeID = @.SaleEventType
AND e1.TransactionType = 'R'
AND e1.EventDt BETWEEN @.LoDate AND @.HiDate
AND e1.EventAmt > 0
AND e2.EventTypeID = @.AssessmentEventType
AND e2.EventDt <= @.HiDate
AND e2.EventAmt > 0
Each property has one PropertyID record, one Property record, and N Event
records (average perhaps five).
What is the mystery? If @.HiDate, which is the upper end of the time window,
is 2002-11-08 or earlier, nothing is returned. If it's 2002-11-09 or later,
oodles of records are found. I get the same query plan for either one, and
based on the content of the data, they should return almost exactly the same
set of records -- exactly the same set in almost all cases, in fact.
Is 2002-11-08/09 some sort of magic dividing point? I have replicated this
on the large database and on a smaller test version on another SQL Server.
(SQL Server 2000) I dropped the indexes and tried it, and
the same thing happened. This is driving me crazy![posted and mailed, please reply in news]
Jim Geissman (jim_geissman@.countrywide.com) writes:
> What is the mystery? If @.HiDate, which is the upper end of the time
> window, is 2002-11-08 or earlier, nothing is returned. If it's
> 2002-11-09 or later, oodles of records are found. I get the same query
> plan for either one, and based on the content of the data, they should
> return almost exactly the same set of records -- exactly the same set in
> almost all cases, in fact.
> Is 2002-11-08/09 some sort of magic dividing point? I have replicated
> this on the large database and on a smaller test version on another SQL
> Server. (SQL Server 2000) I dropped the indexes and tried it, and the
> same thing happened. This is driving me crazy!
Without access to the database, or a script that reproduces the problem,
there is much left for me, but to guess.
And the obvious guess is that the rows you find are from 2002-11-08, but
have a time porttion, and thus are > '2002-11-08 00:00:00'.
However, I notice one thing with your query which may not be correct:
LEFT OUTER JOIN Event e2 ON e2.PropID = d.PropID
WHERE ...
AND e2.EventTypeID = @.AssessmentEventType
AND e2.EventDt <= @.HiDate
AND e2.EventAmt > 0
The idea with a left outer join is that you include rows in the left
table, even if there is no matching rows in the right table. The
columns in the right table are then set to NULL.
Now, the FROM clause is (logically) evaluated before the WHERE clause,
which acts on the filter on the table defined by the FROM clause. This
means that you will filter away those rows with NULL in e2.EventTypeID
that comes from the outer join. Your join is now effectively an inner
join.
There are two ways to handle this:
1) Add conditions with OR clauses or coalesce to handle NULL in the WHERE
clause.
2) Move the conditions to the ON clause.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, Erland. You hit the nail on the head. I was naively assuming that
the right side of the Left Outer Join didn't matter, but when I removed the
second condition in the excerpt below, the records all appeared. As you
said, it was because the FROM was being evaluated before the WHERE. Moving
the condition to ON fixed it. Thanks. I wonder if I have any other
scripts that make this mistake...
> However, I notice one thing with your query which may not be correct:
> LEFT OUTER JOIN Event e2 ON e2.PropID = d.PropID
> WHERE ...
> AND e2.EventTypeID = @.AssessmentEventType
> AND e2.EventDt <= @.HiDate
> AND e2.EventAmt > 0
> The idea with a left outer join is that you include rows in the left
> table, even if there is no matching rows in the right table. The
> columns in the right table are then set to NULL.
> Now, the FROM clause is (logically) evaluated before the WHERE clause,
> which acts on the filter on the table defined by the FROM clause. This
> means that you will filter away those rows with NULL in e2.EventTypeID
> that comes from the outer join. Your join is now effectively an inner
> join.
> There are two ways to handle this:
> 1) Add conditions with OR clauses or coalesce to handle NULL in the WHERE
> clause.
> 2) Move the conditions to the ON clause.|||Jim Geissman (jim_geissman@.countrywide.com) writes:
> Thanks, Erland. You hit the nail on the head. I was naively assuming
> that the right side of the Left Outer Join didn't matter, but when I
> removed the second condition in the excerpt below, the records all
> appeared. As you said, it was because the FROM was being evaluated
> before the WHERE. Moving the condition to ON fixed it. Thanks. I
> wonder if I have any other scripts that make this mistake...
A very common mistake to make. I recall that I did it myself a few
times when I switched to the new syntax from the old *= crap.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Mysterious loss of data from mssql2k
Hi All,
I'm trying to track down a mysterious problem we're experiencing in
which updates and inserts to tables in our mssql2k server appear to be
'disappearing.'
To explain our situation:
We have a web page (written in ASP, if that's relevant) on which we
accept enrollment information.
When that page is submitted, the form data is passed to a stored
procedure on our mssql2k server, which performs several operations,
all of which are wrapped in a transaction.
In particular, the stored procedure performs an update operation on a
record in one table (i'll call it TableA) and an insert into another
table (TableB).
If the procedure encounters a problem (ie after each update / insert
operation in the procedure we test for IF @.@.Error<>0) it performs a
rollback, performs a select similar to the one immediately below, and
then RETURNs.
SELECT '1' as error, 'Unable to update TableA' as errormsg
If the procedure doesn't fail any of the @.@.Error tests, the
transaction is committed, and a membership number is SELECTed to be
returned.
SELECT '0' as error, @.memnum as membershipnumber
The @.memnum variable is populated within the transaction.
Back in the ASP page we test both for the proc returning an empty
recordset, or for it passing an explicit value in the error field, and
push the page to an error page if either of these conditions are met.
If, on the other hand, none of these conditions are met, and the
membershipnumber field in the recordset is populated with a valid
membership number, we push to a confirmation page.
This confirmation page receives the membership number in a session
variable, performs a SELECT against TableB (the table that received
the insert during the proc) using that membership number in the WHERE
clause, and the resultant recordset is used to populate the
confirmation details on that page. That recordset is also then used to
populate the details of a confirmation email, which is automatically
sent by the confirmation page.
And now here's our problem: we've become aware of a handfull of people
who have gone through the enrollment process, have received the
confirmation email containing the information they supplied as
expected, but the data appears to be entirely missing from our tables.
By that I mean that the record in TableA does not appear to have been
updated (under normal circumstances that record should have had
several flags set, and several other fields updated with information
supplied by the person enrolling), and the record in TableB does not
appear to have been inserted.
In essence, looking at our tables, it *feels* like the transaction in
the stored procedure for that particular enrollment hit a problem and
was rolled back. However, the evidence that we have in the form of the
confirmation email argues strongly that the data must have existed in
our tables (particularly in TableB), if only for an unknown period of
time.
We're kind of at our wit's end to work out what is going wrong with
these enrollments. From my understanding of transactions (and I could
well be wrong) any changes to data (ie updates, inserts etc) contained
within are essentially 'invisible' to any other operation (ie the
SELECT that happens in the confirmation page) until the transaction is
committed, implying that the effect of the update and insert should
have been 'permanently' successful if no error code is received and if
a valid membership number was returned. I ask, because someone in our
team has suggested that maybe the operations in the transaction
'lasted long enough' in the tables to have been visible for the SELECT
on the confirmation page to have worked, but were then subsequently
rolled back, explaining why the confirmation email is appropriately
populated and why the data then appears to be missing. However, as I
said, this doesn't match my understanding of how transactions behave.
Sorry for the length of this post, but I felt it was best to explain
this as best as I could.
Does anyone have any advice they can give us on this situation? ie,
are there any known problems with operations in transactions 'bleeding
over' into tables, but then being rolled back at some later point?
Does anyone have any thoughts or suggestions on how we can further
diagnose this issue?
Truly, any help will be immensely appreciated...
Thanks in advance,
M Wells"M Wells" <planetthoughtful@.gmail.com> wrote in message
news:hmba41p1gvtsmudtbus027ad3rapgo1ghd@.4ax.com...
> Hi All,
> I'm trying to track down a mysterious problem we're experiencing in
> which updates and inserts to tables in our mssql2k server appear to be
> 'disappearing.'
> To explain our situation:
> We have a web page (written in ASP, if that's relevant) on which we
> accept enrollment information.
<snip
First, to address your question about data inside a transaction being
visible to other connections, this would only happen if the other connection
explicitly sets its transaction level to READ UNCOMMITTED, which would allow
it to see data which has been inserted/updated but not committed. See SET
TRANSACTION ISOLATION LEVEL in Books Online for more details - I suppose
your ASP connections could be setting this isolation level, but it isn't the
default, so it would be somewhat unusual.
As for tracking down what's going on with the data, you can use Profiler to
run a trace, perhaps filtered on those specific tables and any relevant
stored procedures. If this problem happens fairly often, then running it
interactively may be possible, otherwise see the sp_trace_% procs in Books
Online for details of setting up a server-side trace.
You might also want to check for any triggers on the tables, as sometimes
they can be fired at times you don't expect. And if you have a middle tier
layer, you could also see if it's initiating a transaction before calling
the procedure - it could be that the procedure itself commits correctly, and
the email is sent, but there's an additional outer transaction started by
the middle tier which is then sometimes rolled back after sending the email.
Checking @.@.TRANCOUNT inside the procedure would give you a clue.
Simon|||M Wells (planetthoughtful@.gmail.com) writes:
> If the procedure doesn't fail any of the @.@.Error tests, the
> transaction is committed, and a membership number is SELECTed to be
> returned.
> SELECT '0' as error, @.memnum as membershipnumber
> The @.memnum variable is populated within the transaction.
> Back in the ASP page we test both for the proc returning an empty
> recordset, or for it passing an explicit value in the error field, and
> push the page to an error page if either of these conditions are met.
> If, on the other hand, none of these conditions are met, and the
> membershipnumber field in the recordset is populated with a valid
> membership number, we push to a confirmation page.
> This confirmation page receives the membership number in a session
> variable, performs a SELECT against TableB (the table that received
> the insert during the proc) using that membership number in the WHERE
> clause, and the resultant recordset is used to populate the
> confirmation details on that page. That recordset is also then used to
> populate the details of a confirmation email, which is automatically
> sent by the confirmation page.
> And now here's our problem: we've become aware of a handfull of people
> who have gone through the enrollment process, have received the
> confirmation email containing the information they supplied as
> expected, but the data appears to be entirely missing from our tables.
As I understand, this is an intermittent problem, and you don't have a
reproducible scenario. This make such a problem much more difficult
to track down. And if you make changes to address, you cannot really
be sure that you fixed the right thing.
One thing that is not clear to me is whether it is the same SQL Server
process that runs the stored procedure and the gets the data to the
confirmation page, or whether they are two different. (I should butt
in that I don't know ASP or IIS, so this talk about session variables
etc, tells me little.)
If it is the same SQL Server process, here is something that could
happen:
1) The stored procedure first run unsuccessfully, and a transaction
is started, but then neither committed nor rolled back.
2) The process then runs the procedure successfully, and then gets
the data to the confirmation page. This time a nested transaction
was started and committed. However, "commit" in the case of an
inner transaction just means that transaction count is decremented.
3) The process goes on and registers and confirms more enrollments.
4) Eventually the process is logged out, still with an open transaction.
All enrollments are now rolled back.
So why in step 1, would this happen? It can be a coding error in
the stored procedure, so that a rollback is not executed when it
should. But it could also be a client-side thing. Say that the
procedure is blocked for some reason, and the client gets a command
timeout. In this case the transaction started by the stored procedure
is *not* rolled back. This a really nasty gotcha.
If the confirmation page is really a separate SQL Server connection -
and you should really use the SQL Server Profiler to verify this - then
the data has been committed, and thus it has later been removed. Well,
if the confirmation reads with NOLOCK, are back to the previous
scenario.
One thing you should investigate in either case, is whether these missing
enrollments happened at different points in time, or if they are clustered.
That could give a clue of what may have happened.
I hope this has given you some more ideas of what to look for.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Mysterious login failure error message in log-shipping
Hi I posted this a few days ago in SQL Server Security forum and at Raul Garcia's suggestion (thanks Raul for your help), I post my question here again to see whether I can be lucky enough to find a super guru to help me out.
I get the following error messages in the sql server error log
Source Logon
Message
Error: 18456, Severity: 14, State: 11.
and
Source Logon
Message
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 185.23.11.33]
(note: 185.23.11.33 is the ip addr of the source server, i.e. node 1 in the two node clustered environment)
The scenario is: We set up log-shipping (LS) between a clustered sql server system (source server) and a stand-alone sql server box (target server). (SQL Server 2K5 EE + SP1 and OS is Win2K3 + SP1), and LS goes very well, but on the target server, we found the above-mentioned error messages. The messages seem in synchronization with Log shipping because we setup log shipping cycle at 10 minutes (from 12:00:00 am to 11:59:00pm), and I can see every 10 minutes, for example at 12:10:00am, the login failure errors occur in the target server's sql server error log, and then not appear until 12:20:00am.
BTW: the two servers are in the same domain.
Thanks in advance for your help ...
Jeff
Very much a case of delegation problem in linked server. Please take a look at Nan Tu's blog for an excellent info.http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx|||
Thanks oj, you are my hero...
I believe it is true as I can find there is a linked server on my source server that points to the target server, where the error message is logged. But just curious why SQL Server automatically set up a linked server for log shipping.
|||Logshipping is functional through linked server. It's by design.If you use built-in logshipping, the wizard will create the linked server for you. If you set up log shipping by hand (when you run on Standard edition), you will have to setup the linked server yourself.
Mysterious login failure error message in log-shipping
Hi I posted this a few days ago in SQL Server Security forum and at Raul Garcia's suggestion (thanks Raul for your help), I post my question here again to see whether I can be lucky enough to find a super guru to help me out.
I get the following error messages in the sql server error log
Source Logon
Message
Error: 18456, Severity: 14, State: 11.
and
Source Logon
Message
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 185.23.11.33]
(note: 185.23.11.33 is the ip addr of the source server, i.e. node 1 in the two node clustered environment)
The scenario is: We set up log-shipping (LS) between a clustered sql server system (source server) and a stand-alone sql server box (target server). (SQL Server 2K5 EE + SP1 and OS is Win2K3 + SP1), and LS goes very well, but on the target server, we found the above-mentioned error messages. The messages seem in synchronization with Log shipping because we setup log shipping cycle at 10 minutes (from 12:00:00 am to 11:59:00pm), and I can see every 10 minutes, for example at 12:10:00am, the login failure errors occur in the target server's sql server error log, and then not appear until 12:20:00am.
BTW: the two servers are in the same domain.
Thanks in advance for your help ...
Jeff
Very much a case of delegation problem in linked server. Please take a look at Nan Tu's blog for an excellent info.http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx|||
Thanks oj, you are my hero...
I believe it is true as I can find there is a linked server on my source server that points to the target server, where the error message is logged. But just curious why SQL Server automatically set up a linked server for log shipping.
|||Logshipping is functional through linked server. It's by design.If you use built-in logshipping, the wizard will create the linked server for you. If you set up log shipping by hand (when you run on Standard edition), you will have to setup the linked server yourself.
Subscribe to:
Posts (Atom)