Hello-
We were on SQL 7.0 / Windows NT and have moved to SQL 2000 sp4a / Windows
2003. Since upgrade, we have seen some strange behavior.
Following are some of the observations so far:
1. We have witnessed exceedingly large database restoration timings. With
SQL 7.0, it used to take around 1.5-2.0 hours to restore database sized 125+
GB (with around 70 GB of data... we had some fragmentation problems!). Now,
the whole process takes from 4 to 9 hours. The restoration times are very
large when restored the first time (true, I am trying to create a database as
well).
2. We are using a vertical solution that used to have high level of
fragmentation. The fragmentation used to be so high that it used to increase
data device to 122+ GB with actual data of ~ 65+ GB. This forces me to
rebuild indexes and run the maintenance DBCC on every weekend. Since
upgrading to SQL 2000, this problem has reduced a lot. But, the maintenance
job timings since upgrade have also increased by 50% (it used to take ~ 8
hours and now the job is taking ~ 12 hours).
3. On at least two occasions (the latest on last Friday), the database has
generated exceedingly large transaction log backups. True, the application
system administrator has told me there was a runaway process that might have
created exceedingly large logs. But, the backups were so huge (+61 GB) that
it filled up the whole backup drive resulting in failures for other
transaction log backups till I cleaned the drive and took a full backup. The
normal hourly transaction log backups have total of size of 600+ MB (624 MB
as of now).
My question is: Am I seeing some kind of structural problems and has anyone
encountered similar problems?
Please share your experiences and expertise.
--
Regards,
MZeeshanHello Mzeeshan,
Before going any further, I'd like to confirm if the current transaction
log backup is larger than before and if you are restoring from the
transaction log backup. If yes, it is normal that it take more time to
restore.
The reason why the log backup is larger than before is that you run DBCC
command to rebuild indexes every week. Reindexing can cause the transaction
log grow much.
Because of the changes in the recovery model in SQL Server 2000, when you
use the Full recovery mode and you run DBCC DBREINDEX, the transaction log
may expand significantly more compared to that of SQL Server 7.0 in an
equivalent recovery mode with the use of SELECT INTO or BULK COPY and with
"Trunc. Log on chkpt." off.
Although the size of the transaction log after the DBREINDEX operation
might be an issue, this approach provides better log restore performance.
The following article has addressed the similar issue:
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/?id=317375
If you only rely on full database backups (not transaction log backups),
the recovery model can be changed to Simple to have a smaller transaction
log file. To do this, please follow these steps:
1. Start SQL Server Enterprise Manager (SEM).
2. Expand a server group, and then expand a server.
3. Expand Databases, right-click the database to modify, and then click
Properties.
4. Click the Options tab.
5. Select "Simple" from the Model list under the Recovery section.
6. Click OK.
You can also run the following command to set the recovery model to Simple:
ALTER DATABASE database_name SET RECOVERY Simple
Note: Replace database_name with your database's name
Note: If you set the recovery option of the database to SIMPLE, the
transaction log is set to be truncated at every checkpoint and thus
prevents the log from filling up. However, with the Simple Recovery model,
the database can be recovered only to the point of the last database backup
and not to the last transaction and you cannot backup the transaction log
since the sequence of transaction logs is not being maintained. Also, you
cannot restore the database to the point of failure or to a specific point
in time. To do that, you need to use the Full Recovery or Bulk-Logged
Recovery model.
After that, run the "dbcc shrinkfile" command against the transaction log
to shrink the log file.
For more information on shrinking the transaction Log, please refer to the
following article:
272318 INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
<http://support.microsoft.com/?id=272318>
Please understand that the latest SQL Server 2000 Service Pack is Microsoft
SQL Server 2000 Service Pack 3a instead of SQL 2000 SP4a. Please clarify
the Service Pack you installed.
In addition, I want to let you know that performance issues can be caused
by various factors, and it is difficult to locate the root cause in a
newsgroup thread. If the issue still exists after you have used the
troubleshooting steps above, to efficiently troubleshoot a performance
issue, we recommend that you contact Microsoft Product Support Services and
open a support incident and work with a dedicated Support Professional.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
I hope above information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
Showing posts with label strange. Show all posts
Showing posts with label strange. Show all posts
Wednesday, March 28, 2012
Friday, March 23, 2012
Is this even possible?
Hi,
I have a very strange problem and just wanted to run something with
you guys to see if this scanario is possible.
Is it possible that data inserted into a table using an insert query
within a stored procedure and called from an ASP page could be
selected by a diferent ASP page calling a stored procedure. And for
that whole data to suddenly disappear without a trace?
We have a site which has been running fine for a year, and recently it
has been hit quite badly in terms of traffic. When a user comes to
the site, data is eneterd into a user table and the Identity
requested. Then extra info is taken from that user and entered into a
second table using the identity (two different stored procdures. Then
an audit (this identity, and one other piece of info) is written to
the hard drive using a flat file, this happens for reasons I dont
really want to go into, but nonetheless this happens.
We have had a number of users coming back to us stating that they have
used the site and quoted a reference (the identity) this is completely
missing, including the data associated with it in the second table
(The flat file is there however, which means a select must have taken
place and data must have been there at some point). In one case,
there was only 10 minutes between the user entering data and us
finding it didnt exist. The identity in the flat file DOES exist.
Where could this data go? The SP uses a commit at the end and this is
happenening (After all, we would be able to select from this data
unless it was commited) The sequential number is missing from the
identity field in the first table.
We have performed a backup and rollforward using the transaction logs
and NONE of these SQL transactuions appear to have taken place, and do
not appear in the transaction log at all.
We have been looking into lots of things regarding torn pages but our
understanding is that if this had happened, the data should still be
in the transaction log for that day?
the server was under considerable strain with a queue index of over 10
so could this affect it? We have used SQL server (7 and 2000) for a
number of years now and have never experienced anything like this.
Logic would state that the data was in there but has subsequently been
deleted, but the data doesnt exist in the transaction logs at all so
how did it disappear (theres no INSERT or DELETE)?
Any thoughts would be very much appreciated.
Craig
---
Play my Pot Noodle Game
http://www.dvdhacks.co.uk/games.aspAre both stored procedures and the process that write the identity into the
file wrapped into a transaction?, If not, then there is not a guarantee that
the value being in the file means it was inserted into the other tables.
AMB
"Craig" wrote:
> Hi,
> I have a very strange problem and just wanted to run something with
> you guys to see if this scanario is possible.
> Is it possible that data inserted into a table using an insert query
> within a stored procedure and called from an ASP page could be
> selected by a diferent ASP page calling a stored procedure. And for
> that whole data to suddenly disappear without a trace?
> We have a site which has been running fine for a year, and recently it
> has been hit quite badly in terms of traffic. When a user comes to
> the site, data is eneterd into a user table and the Identity
> requested. Then extra info is taken from that user and entered into a
> second table using the identity (two different stored procdures. Then
> an audit (this identity, and one other piece of info) is written to
> the hard drive using a flat file, this happens for reasons I dont
> really want to go into, but nonetheless this happens.
> We have had a number of users coming back to us stating that they have
> used the site and quoted a reference (the identity) this is completely
> missing, including the data associated with it in the second table
> (The flat file is there however, which means a select must have taken
> place and data must have been there at some point). In one case,
> there was only 10 minutes between the user entering data and us
> finding it didnt exist. The identity in the flat file DOES exist.
> Where could this data go? The SP uses a commit at the end and this is
> happenening (After all, we would be able to select from this data
> unless it was commited) The sequential number is missing from the
> identity field in the first table.
> We have performed a backup and rollforward using the transaction logs
> and NONE of these SQL transactuions appear to have taken place, and do
> not appear in the transaction log at all.
> We have been looking into lots of things regarding torn pages but our
> understanding is that if this had happened, the data should still be
> in the transaction log for that day?
> the server was under considerable strain with a queue index of over 10
> so could this affect it? We have used SQL server (7 and 2000) for a
> number of years now and have never experienced anything like this.
> Logic would state that the data was in there but has subsequently been
> deleted, but the data doesnt exist in the transaction logs at all so
> how did it disappear (theres no INSERT or DELETE)?
> Any thoughts would be very much appreciated.
> Craig
>
> ---
> Play my Pot Noodle Game
> http://www.dvdhacks.co.uk/games.asp
>|||On Thu, 3 Feb 2005 08:43:04 -0800, "Alejandro Mesa"
<AlejandroMesa@.discussions.microsoft.com> wrote:
>Are both stored procedures and the process that write the identity into the
>file wrapped into a transaction?, If not, then there is not a guarantee tha
t
>the value being in the file means it was inserted into the other tables.
Hi thanks for the reply,
In the stored procedure, the data is inserted, then transaction is
commited witha rollback clause. I would have through that if it had
rolledback (Throuch not being able to commit) then it wouldnt be
selectable using a statement after?
its is two completely different stored procedures called from
different ASP pages, why is there no guarantee? Surely a commit means
that the data should be there, and the subsequent select means that
the data should have commited?
Or am I wrong here? Sorry for asking you to explain what you mean, but
I was always under the impression that you couldnt select non
committed data because that should be locked until commited? If your
saying I can insert data using a stored procedure and commit it, for
the data to subsequently not commit (even after it had been selected)
then thats scary?
Thanks again
Craig
---
Play my Pot Noodle Game
http://www.dvdhacks.co.uk/games.asp|||What I meant was that inserting the order header, details and writing the ne
w
order number in a file that is in the file system, should be in a
transaction, if something was wrong then everything is rolled back (I am
using order headerand detail as an example). You can accomplish this using
diff sp, but it does not mean that both sp should run in a diff transaction.
AMB
"Craig" wrote:
> On Thu, 3 Feb 2005 08:43:04 -0800, "Alejandro Mesa"
> <AlejandroMesa@.discussions.microsoft.com> wrote:
>
> Hi thanks for the reply,
> In the stored procedure, the data is inserted, then transaction is
> commited witha rollback clause. I would have through that if it had
> rolledback (Throuch not being able to commit) then it wouldnt be
> selectable using a statement after?
> its is two completely different stored procedures called from
> different ASP pages, why is there no guarantee? Surely a commit means
> that the data should be there, and the subsequent select means that
> the data should have commited?
> Or am I wrong here? Sorry for asking you to explain what you mean, but
> I was always under the impression that you couldnt select non
> committed data because that should be locked until commited? If your
> saying I can insert data using a stored procedure and commit it, for
> the data to subsequently not commit (even after it had been selected)
> then thats scary?
> Thanks again
> Craig
> ---
> Play my Pot Noodle Game
> http://www.dvdhacks.co.uk/games.asp
>|||On Thu, 3 Feb 2005 10:05:05 -0800, "Alejandro Mesa"
<AlejandroMesa@.discussions.microsoft.com> wrote:
>What I meant was that inserting the order header, details and writing the n
ew
>order number in a file that is in the file system, should be in a
>transaction, if something was wrong then everything is rolled back (I am
>using order headerand detail as an example). You can accomplish this using
>diff sp, but it does not mean that both sp should run in a diff transaction.[/color
]
Hi,
Thanks again. But I dont quite understand how data inserted in one SP
can be queried using another at a slightly later time if it couldnt
commit. I understand that there might have been a problem inserting
the data in the first place, and understand rollbacks. But I dont
understand how the data could be queried (Selected) in a different SP
without the data being commited.
The application process flow works like this yet the transaction logs
dont even know that this data existed?
Cheers
Craig
>
>AMB
>"Craig" wrote:
>
---
Play my Pot Noodle Game
http://www.dvdhacks.co.uk/games.asp|||Craig wrote:
> Thanks again. But I dont quite understand how data inserted in one SP
> can be queried using another at a slightly later time if it couldnt
> commit. I understand that there might have been a problem inserting
> the data in the first place, and understand rollbacks. But I dont
> understand how the data could be queried (Selected) in a different SP
> without the data being commited.
> The application process flow works like this yet the transaction logs
> dont even know that this data existed?
That could occur if the SELECTing transaction used a NOLOCK or READ
UNCOMMITTED query hint. In essence, it can select uncommitted data
(dirty reads), and if the other transaction rolled back, those rows
would be gone.
David Gugick
Imceda Software
www.imceda.com|||
>That could occur if the SELECTing transaction used a NOLOCK or READ
>UNCOMMITTED query hint. In essence, it can select uncommitted data
>(dirty reads), and if the other transaction rolled back, those rows
>would be gone.
Okay, thats a point, however no lock was specified during any of these
read or writes, so could this be set as default on the server? (This
is not our SQL server)
Cheers
Craig
---
Play my Pot Noodle Game
http://www.dvdhacks.co.uk/games.asp|||Craig wrote:
> Okay, thats a point, however no lock was specified during any of these
> read or writes, so could this be set as default on the server? (This
> is not our SQL server)
This is usually done on a per-query basis or at the client level. You'll
have to check the ADO or ADO.Net connection and query information in the
ASP code and also check the stored procedure code for SET TRANSACTION
ISOLATION LEVEL READ UNCOMMITTED and NOLOCK table hints.
How are you pulling the identitiy value? Are you using SCOPE_IDENTITY().
If not, you should be.
David Gugick
Imceda Software
www.imceda.com
I have a very strange problem and just wanted to run something with
you guys to see if this scanario is possible.
Is it possible that data inserted into a table using an insert query
within a stored procedure and called from an ASP page could be
selected by a diferent ASP page calling a stored procedure. And for
that whole data to suddenly disappear without a trace?
We have a site which has been running fine for a year, and recently it
has been hit quite badly in terms of traffic. When a user comes to
the site, data is eneterd into a user table and the Identity
requested. Then extra info is taken from that user and entered into a
second table using the identity (two different stored procdures. Then
an audit (this identity, and one other piece of info) is written to
the hard drive using a flat file, this happens for reasons I dont
really want to go into, but nonetheless this happens.
We have had a number of users coming back to us stating that they have
used the site and quoted a reference (the identity) this is completely
missing, including the data associated with it in the second table
(The flat file is there however, which means a select must have taken
place and data must have been there at some point). In one case,
there was only 10 minutes between the user entering data and us
finding it didnt exist. The identity in the flat file DOES exist.
Where could this data go? The SP uses a commit at the end and this is
happenening (After all, we would be able to select from this data
unless it was commited) The sequential number is missing from the
identity field in the first table.
We have performed a backup and rollforward using the transaction logs
and NONE of these SQL transactuions appear to have taken place, and do
not appear in the transaction log at all.
We have been looking into lots of things regarding torn pages but our
understanding is that if this had happened, the data should still be
in the transaction log for that day?
the server was under considerable strain with a queue index of over 10
so could this affect it? We have used SQL server (7 and 2000) for a
number of years now and have never experienced anything like this.
Logic would state that the data was in there but has subsequently been
deleted, but the data doesnt exist in the transaction logs at all so
how did it disappear (theres no INSERT or DELETE)?
Any thoughts would be very much appreciated.
Craig
---
Play my Pot Noodle Game
http://www.dvdhacks.co.uk/games.aspAre both stored procedures and the process that write the identity into the
file wrapped into a transaction?, If not, then there is not a guarantee that
the value being in the file means it was inserted into the other tables.
AMB
"Craig" wrote:
> Hi,
> I have a very strange problem and just wanted to run something with
> you guys to see if this scanario is possible.
> Is it possible that data inserted into a table using an insert query
> within a stored procedure and called from an ASP page could be
> selected by a diferent ASP page calling a stored procedure. And for
> that whole data to suddenly disappear without a trace?
> We have a site which has been running fine for a year, and recently it
> has been hit quite badly in terms of traffic. When a user comes to
> the site, data is eneterd into a user table and the Identity
> requested. Then extra info is taken from that user and entered into a
> second table using the identity (two different stored procdures. Then
> an audit (this identity, and one other piece of info) is written to
> the hard drive using a flat file, this happens for reasons I dont
> really want to go into, but nonetheless this happens.
> We have had a number of users coming back to us stating that they have
> used the site and quoted a reference (the identity) this is completely
> missing, including the data associated with it in the second table
> (The flat file is there however, which means a select must have taken
> place and data must have been there at some point). In one case,
> there was only 10 minutes between the user entering data and us
> finding it didnt exist. The identity in the flat file DOES exist.
> Where could this data go? The SP uses a commit at the end and this is
> happenening (After all, we would be able to select from this data
> unless it was commited) The sequential number is missing from the
> identity field in the first table.
> We have performed a backup and rollforward using the transaction logs
> and NONE of these SQL transactuions appear to have taken place, and do
> not appear in the transaction log at all.
> We have been looking into lots of things regarding torn pages but our
> understanding is that if this had happened, the data should still be
> in the transaction log for that day?
> the server was under considerable strain with a queue index of over 10
> so could this affect it? We have used SQL server (7 and 2000) for a
> number of years now and have never experienced anything like this.
> Logic would state that the data was in there but has subsequently been
> deleted, but the data doesnt exist in the transaction logs at all so
> how did it disappear (theres no INSERT or DELETE)?
> Any thoughts would be very much appreciated.
> Craig
>
> ---
> Play my Pot Noodle Game
> http://www.dvdhacks.co.uk/games.asp
>|||On Thu, 3 Feb 2005 08:43:04 -0800, "Alejandro Mesa"
<AlejandroMesa@.discussions.microsoft.com> wrote:
>Are both stored procedures and the process that write the identity into the
>file wrapped into a transaction?, If not, then there is not a guarantee tha
t
>the value being in the file means it was inserted into the other tables.
Hi thanks for the reply,
In the stored procedure, the data is inserted, then transaction is
commited witha rollback clause. I would have through that if it had
rolledback (Throuch not being able to commit) then it wouldnt be
selectable using a statement after?
its is two completely different stored procedures called from
different ASP pages, why is there no guarantee? Surely a commit means
that the data should be there, and the subsequent select means that
the data should have commited?
Or am I wrong here? Sorry for asking you to explain what you mean, but
I was always under the impression that you couldnt select non
committed data because that should be locked until commited? If your
saying I can insert data using a stored procedure and commit it, for
the data to subsequently not commit (even after it had been selected)
then thats scary?
Thanks again
Craig
---
Play my Pot Noodle Game
http://www.dvdhacks.co.uk/games.asp|||What I meant was that inserting the order header, details and writing the ne
w
order number in a file that is in the file system, should be in a
transaction, if something was wrong then everything is rolled back (I am
using order headerand detail as an example). You can accomplish this using
diff sp, but it does not mean that both sp should run in a diff transaction.
AMB
"Craig" wrote:
> On Thu, 3 Feb 2005 08:43:04 -0800, "Alejandro Mesa"
> <AlejandroMesa@.discussions.microsoft.com> wrote:
>
> Hi thanks for the reply,
> In the stored procedure, the data is inserted, then transaction is
> commited witha rollback clause. I would have through that if it had
> rolledback (Throuch not being able to commit) then it wouldnt be
> selectable using a statement after?
> its is two completely different stored procedures called from
> different ASP pages, why is there no guarantee? Surely a commit means
> that the data should be there, and the subsequent select means that
> the data should have commited?
> Or am I wrong here? Sorry for asking you to explain what you mean, but
> I was always under the impression that you couldnt select non
> committed data because that should be locked until commited? If your
> saying I can insert data using a stored procedure and commit it, for
> the data to subsequently not commit (even after it had been selected)
> then thats scary?
> Thanks again
> Craig
> ---
> Play my Pot Noodle Game
> http://www.dvdhacks.co.uk/games.asp
>|||On Thu, 3 Feb 2005 10:05:05 -0800, "Alejandro Mesa"
<AlejandroMesa@.discussions.microsoft.com> wrote:
>What I meant was that inserting the order header, details and writing the n
ew
>order number in a file that is in the file system, should be in a
>transaction, if something was wrong then everything is rolled back (I am
>using order headerand detail as an example). You can accomplish this using
>diff sp, but it does not mean that both sp should run in a diff transaction.[/color
]
Hi,
Thanks again. But I dont quite understand how data inserted in one SP
can be queried using another at a slightly later time if it couldnt
commit. I understand that there might have been a problem inserting
the data in the first place, and understand rollbacks. But I dont
understand how the data could be queried (Selected) in a different SP
without the data being commited.
The application process flow works like this yet the transaction logs
dont even know that this data existed?
Cheers
Craig
>
>AMB
>"Craig" wrote:
>
---
Play my Pot Noodle Game
http://www.dvdhacks.co.uk/games.asp|||Craig wrote:
> Thanks again. But I dont quite understand how data inserted in one SP
> can be queried using another at a slightly later time if it couldnt
> commit. I understand that there might have been a problem inserting
> the data in the first place, and understand rollbacks. But I dont
> understand how the data could be queried (Selected) in a different SP
> without the data being commited.
> The application process flow works like this yet the transaction logs
> dont even know that this data existed?
That could occur if the SELECTing transaction used a NOLOCK or READ
UNCOMMITTED query hint. In essence, it can select uncommitted data
(dirty reads), and if the other transaction rolled back, those rows
would be gone.
David Gugick
Imceda Software
www.imceda.com|||
>That could occur if the SELECTing transaction used a NOLOCK or READ
>UNCOMMITTED query hint. In essence, it can select uncommitted data
>(dirty reads), and if the other transaction rolled back, those rows
>would be gone.
Okay, thats a point, however no lock was specified during any of these
read or writes, so could this be set as default on the server? (This
is not our SQL server)
Cheers
Craig
---
Play my Pot Noodle Game
http://www.dvdhacks.co.uk/games.asp|||Craig wrote:
> Okay, thats a point, however no lock was specified during any of these
> read or writes, so could this be set as default on the server? (This
> is not our SQL server)
This is usually done on a per-query basis or at the client level. You'll
have to check the ADO or ADO.Net connection and query information in the
ASP code and also check the stored procedure code for SET TRANSACTION
ISOLATION LEVEL READ UNCOMMITTED and NOLOCK table hints.
How are you pulling the identitiy value? Are you using SCOPE_IDENTITY().
If not, you should be.
David Gugick
Imceda Software
www.imceda.com
Is this even Possible?
Hi,
I have a very strange problem and just wanted to run something with
you guys to see if this scanario is possible.
Is it possible that data inserted into a table using an insert query
within a stored procedure and called from an ASP page could be
selected by a diferent ASP page calling a stored procedure. And for
that whole data to suddenly disappear without a trace?
We have a site which has been running fine for a year, and recently it
has been hit quite badly in terms of traffic. When a user comes to
the site, data is eneterd into a user table and the Identity
requested. Then extra info is taken from that user and entered into a
second table using the identity (two different stored procdures. Then
an audit (this identity, and one other piece of info) is written to
the hard drive using a flat file, this happens for reasons I dont
really want to go into, but nonetheless this happens.
We have had a number of users coming back to us stating that they have
used the site and quoted a reference (the identity) this is completely
missing, including the data associated with it in the second table
(The flat file is there however, which means a select must have taken
place and data must have been there at some point). In one case,
there was only 10 minutes between the user entering data and us
finding it didnt exist. The identity in the flat file DOES exist.
Where could this data go? The SP uses a commit at the end and this is
happenening (After all, we would be able to select from this data
unless it was commited) The sequential number is missing from the
identity field in the first table.
We have performed a backup and rollforward using the transaction logs
and NONE of these SQL transactuions appear to have taken place, and do
not appear in the transaction log at all.
We have been looking into lots of things regarding torn pages but our
understanding is that if this had happened, the data should still be
in the transaction log for that day?
the server was under considerable strain with a queue index of over 10
so could this affect it? We have used SQL server (7 and 2000) for a
number of years now and have never experienced anything like this.
Logic would state that the data was in there but has subsequently been
deleted, but the data doesnt exist in the transaction logs at all so
how did it disappear (theres no INSERT or DELETE)?
Any thoughts would be very much appreciated.
Craig
---
Play my Pot Noodle Game
http://www.dvdhacks.co.uk/games.aspA quick idea: maybe some transactions get rolled back because of a deadlock?
Try to catch error 1205.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Craig" <reply2craig@.hotmail.com> wrote in message
news:flk40115pt56mnvrtqf0ol3076186pefm6@.
4ax.com...
> Hi,
> I have a very strange problem and just wanted to run something with
> you guys to see if this scanario is possible.
> Is it possible that data inserted into a table using an insert query
> within a stored procedure and called from an ASP page could be
> selected by a diferent ASP page calling a stored procedure. And for
> that whole data to suddenly disappear without a trace?
> We have a site which has been running fine for a year, and recently it
> has been hit quite badly in terms of traffic. When a user comes to
> the site, data is eneterd into a user table and the Identity
> requested. Then extra info is taken from that user and entered into a
> second table using the identity (two different stored procdures. Then
> an audit (this identity, and one other piece of info) is written to
> the hard drive using a flat file, this happens for reasons I dont
> really want to go into, but nonetheless this happens.
> We have had a number of users coming back to us stating that they have
> used the site and quoted a reference (the identity) this is completely
> missing, including the data associated with it in the second table
> (The flat file is there however, which means a select must have taken>
place and data must have been there at some point). In one case,
> there was only 10 minutes between the user entering data and us
> finding it didnt exist. The identity in the flat file DOES exist.
> Where could this data go? The SP uses a commit at the end and this is
> happenening (After all, we would be able to select from this data
> unless it was commited) The sequential number is missing from the
> identity field in the first table.
> We have performed a backup and rollforward using the transaction logs
> and NONE of these SQL transactuions appear to have taken place, and do
> not appear in the transaction log at all.
> We have been looking into lots of things regarding torn pages but our
> understanding is that if this had happened, the data should still be
> in the transaction log for that day?
> the server was under considerable strain with a queue index of over 10
> so could this affect it? We have used SQL server (7 and 2000) for a
> number of years now and have never experienced anything like this.
> Logic would state that the data was in there but has subsequently been
> deleted, but the data doesnt exist in the transaction logs at all so
> how did it disappear (theres no INSERT or DELETE)?
> Any thoughts would be very much appreciated.
> Craig
>
> ---
> Play my Pot Noodle Game
> http://www.dvdhacks.co.uk/games.asp|||On Thu, 3 Feb 2005 20:07:12 +0100, "Dejan Sarka"
<dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote:
>A quick idea: maybe some transactions get rolled back because of a deadlock
?
>Try to catch error 1205.
Okay, thanks for that...... looking back though, would error 1205
show up in any logs?
Cheers
Craig
---
Play my Pot Noodle Game
http://www.dvdhacks.co.uk/games.asp|||Craig wrote:
> Hi,
> I have a very strange problem and just wanted to run something with
> you guys to see if this scanario is possible.
> Is it possible that data inserted into a table using an insert query
> within a stored procedure and called from an ASP page could be
> selected by a diferent ASP page calling a stored procedure. And for
> that whole data to suddenly disappear without a trace?
> We have a site which has been running fine for a year, and recently it
> has been hit quite badly in terms of traffic. When a user comes to
> the site, data is eneterd into a user table and the Identity
> requested. Then extra info is taken from that user and entered into a
> second table using the identity (two different stored procdures. Then
> an audit (this identity, and one other piece of info) is written to
> the hard drive using a flat file, this happens for reasons I dont
> really want to go into, but nonetheless this happens.
> We have had a number of users coming back to us stating that they have
> used the site and quoted a reference (the identity) this is completely
> missing, including the data associated with it in the second table
> (The flat file is there however, which means a select must have taken
> place and data must have been there at some point). In one case,
> there was only 10 minutes between the user entering data and us
> finding it didnt exist. The identity in the flat file DOES exist.
> Where could this data go? The SP uses a commit at the end and this is
> happenening (After all, we would be able to select from this data
> unless it was commited) The sequential number is missing from the
> identity field in the first table.
> We have performed a backup and rollforward using the transaction logs
> and NONE of these SQL transactuions appear to have taken place, and do
> not appear in the transaction log at all.
> We have been looking into lots of things regarding torn pages but our
> understanding is that if this had happened, the data should still be
> in the transaction log for that day?
> the server was under considerable strain with a queue index of over 10
> so could this affect it? We have used SQL server (7 and 2000) for a
> number of years now and have never experienced anything like this.
> Logic would state that the data was in there but has subsequently been
> deleted, but the data doesnt exist in the transaction logs at all so
> how did it disappear (theres no INSERT or DELETE)?
> Any thoughts would be very much appreciated.
> Craig
>
> ---
> Play my Pot Noodle Game
> http://www.dvdhacks.co.uk/games.asp
Just a thought,
You are doing the inserts and stuff with SPs, there is a possibility the
SPs arent functioning properly, if in any of the porgram steps inside
the SP gets and warning message it just dies, NO error is returned as
the @.@.ERROR does not contain warning messages but the SP will exit
and rollback will occur.
this just might be the cause .
I have a very strange problem and just wanted to run something with
you guys to see if this scanario is possible.
Is it possible that data inserted into a table using an insert query
within a stored procedure and called from an ASP page could be
selected by a diferent ASP page calling a stored procedure. And for
that whole data to suddenly disappear without a trace?
We have a site which has been running fine for a year, and recently it
has been hit quite badly in terms of traffic. When a user comes to
the site, data is eneterd into a user table and the Identity
requested. Then extra info is taken from that user and entered into a
second table using the identity (two different stored procdures. Then
an audit (this identity, and one other piece of info) is written to
the hard drive using a flat file, this happens for reasons I dont
really want to go into, but nonetheless this happens.
We have had a number of users coming back to us stating that they have
used the site and quoted a reference (the identity) this is completely
missing, including the data associated with it in the second table
(The flat file is there however, which means a select must have taken
place and data must have been there at some point). In one case,
there was only 10 minutes between the user entering data and us
finding it didnt exist. The identity in the flat file DOES exist.
Where could this data go? The SP uses a commit at the end and this is
happenening (After all, we would be able to select from this data
unless it was commited) The sequential number is missing from the
identity field in the first table.
We have performed a backup and rollforward using the transaction logs
and NONE of these SQL transactuions appear to have taken place, and do
not appear in the transaction log at all.
We have been looking into lots of things regarding torn pages but our
understanding is that if this had happened, the data should still be
in the transaction log for that day?
the server was under considerable strain with a queue index of over 10
so could this affect it? We have used SQL server (7 and 2000) for a
number of years now and have never experienced anything like this.
Logic would state that the data was in there but has subsequently been
deleted, but the data doesnt exist in the transaction logs at all so
how did it disappear (theres no INSERT or DELETE)?
Any thoughts would be very much appreciated.
Craig
---
Play my Pot Noodle Game
http://www.dvdhacks.co.uk/games.aspA quick idea: maybe some transactions get rolled back because of a deadlock?
Try to catch error 1205.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Craig" <reply2craig@.hotmail.com> wrote in message
news:flk40115pt56mnvrtqf0ol3076186pefm6@.
4ax.com...
> Hi,
> I have a very strange problem and just wanted to run something with
> you guys to see if this scanario is possible.
> Is it possible that data inserted into a table using an insert query
> within a stored procedure and called from an ASP page could be
> selected by a diferent ASP page calling a stored procedure. And for
> that whole data to suddenly disappear without a trace?
> We have a site which has been running fine for a year, and recently it
> has been hit quite badly in terms of traffic. When a user comes to
> the site, data is eneterd into a user table and the Identity
> requested. Then extra info is taken from that user and entered into a
> second table using the identity (two different stored procdures. Then
> an audit (this identity, and one other piece of info) is written to
> the hard drive using a flat file, this happens for reasons I dont
> really want to go into, but nonetheless this happens.
> We have had a number of users coming back to us stating that they have
> used the site and quoted a reference (the identity) this is completely
> missing, including the data associated with it in the second table
> (The flat file is there however, which means a select must have taken>
place and data must have been there at some point). In one case,
> there was only 10 minutes between the user entering data and us
> finding it didnt exist. The identity in the flat file DOES exist.
> Where could this data go? The SP uses a commit at the end and this is
> happenening (After all, we would be able to select from this data
> unless it was commited) The sequential number is missing from the
> identity field in the first table.
> We have performed a backup and rollforward using the transaction logs
> and NONE of these SQL transactuions appear to have taken place, and do
> not appear in the transaction log at all.
> We have been looking into lots of things regarding torn pages but our
> understanding is that if this had happened, the data should still be
> in the transaction log for that day?
> the server was under considerable strain with a queue index of over 10
> so could this affect it? We have used SQL server (7 and 2000) for a
> number of years now and have never experienced anything like this.
> Logic would state that the data was in there but has subsequently been
> deleted, but the data doesnt exist in the transaction logs at all so
> how did it disappear (theres no INSERT or DELETE)?
> Any thoughts would be very much appreciated.
> Craig
>
> ---
> Play my Pot Noodle Game
> http://www.dvdhacks.co.uk/games.asp|||On Thu, 3 Feb 2005 20:07:12 +0100, "Dejan Sarka"
<dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote:
>A quick idea: maybe some transactions get rolled back because of a deadlock
?
>Try to catch error 1205.
Okay, thanks for that...... looking back though, would error 1205
show up in any logs?
Cheers
Craig
---
Play my Pot Noodle Game
http://www.dvdhacks.co.uk/games.asp|||Craig wrote:
> Hi,
> I have a very strange problem and just wanted to run something with
> you guys to see if this scanario is possible.
> Is it possible that data inserted into a table using an insert query
> within a stored procedure and called from an ASP page could be
> selected by a diferent ASP page calling a stored procedure. And for
> that whole data to suddenly disappear without a trace?
> We have a site which has been running fine for a year, and recently it
> has been hit quite badly in terms of traffic. When a user comes to
> the site, data is eneterd into a user table and the Identity
> requested. Then extra info is taken from that user and entered into a
> second table using the identity (two different stored procdures. Then
> an audit (this identity, and one other piece of info) is written to
> the hard drive using a flat file, this happens for reasons I dont
> really want to go into, but nonetheless this happens.
> We have had a number of users coming back to us stating that they have
> used the site and quoted a reference (the identity) this is completely
> missing, including the data associated with it in the second table
> (The flat file is there however, which means a select must have taken
> place and data must have been there at some point). In one case,
> there was only 10 minutes between the user entering data and us
> finding it didnt exist. The identity in the flat file DOES exist.
> Where could this data go? The SP uses a commit at the end and this is
> happenening (After all, we would be able to select from this data
> unless it was commited) The sequential number is missing from the
> identity field in the first table.
> We have performed a backup and rollforward using the transaction logs
> and NONE of these SQL transactuions appear to have taken place, and do
> not appear in the transaction log at all.
> We have been looking into lots of things regarding torn pages but our
> understanding is that if this had happened, the data should still be
> in the transaction log for that day?
> the server was under considerable strain with a queue index of over 10
> so could this affect it? We have used SQL server (7 and 2000) for a
> number of years now and have never experienced anything like this.
> Logic would state that the data was in there but has subsequently been
> deleted, but the data doesnt exist in the transaction logs at all so
> how did it disappear (theres no INSERT or DELETE)?
> Any thoughts would be very much appreciated.
> Craig
>
> ---
> Play my Pot Noodle Game
> http://www.dvdhacks.co.uk/games.asp
Just a thought,
You are doing the inserts and stuff with SPs, there is a possibility the
SPs arent functioning properly, if in any of the porgram steps inside
the SP gets and warning message it just dies, NO error is returned as
the @.@.ERROR does not contain warning messages but the SP will exit
and rollback will occur.
this just might be the cause .
Is this even Possible?
Hi,
I have a very strange problem and just wanted to run something with
you guys to see if this scanario is possible.
Is it possible that data inserted into a table using an insert query
within a stored procedure and called from an ASP page could be
selected by a diferent ASP page calling a stored procedure. And for
that whole data to suddenly disappear without a trace?
We have a site which has been running fine for a year, and recently it
has been hit quite badly in terms of traffic. When a user comes to
the site, data is eneterd into a user table and the Identity
requested. Then extra info is taken from that user and entered into a
second table using the identity (two different stored procdures. Then
an audit (this identity, and one other piece of info) is written to
the hard drive using a flat file, this happens for reasons I dont
really want to go into, but nonetheless this happens.
We have had a number of users coming back to us stating that they have
used the site and quoted a reference (the identity) this is completely
missing, including the data associated with it in the second table
(The flat file is there however, which means a select must have taken
place and data must have been there at some point). In one case,
there was only 10 minutes between the user entering data and us
finding it didnt exist. The identity in the flat file DOES exist.
Where could this data go? The SP uses a commit at the end and this is
happenening (After all, we would be able to select from this data
unless it was commited) The sequential number is missing from the
identity field in the first table.
We have performed a backup and rollforward using the transaction logs
and NONE of these SQL transactuions appear to have taken place, and do
not appear in the transaction log at all.
We have been looking into lots of things regarding torn pages but our
understanding is that if this had happened, the data should still be
in the transaction log for that day?
the server was under considerable strain with a queue index of over 10
so could this affect it? We have used SQL server (7 and 2000) for a
number of years now and have never experienced anything like this.
Logic would state that the data was in there but has subsequently been
deleted, but the data doesnt exist in the transaction logs at all so
how did it disappear (theres no INSERT or DELETE)?
Any thoughts would be very much appreciated.
Craig
Play my Pot Noodle Game
http://www.dvdhacks.co.uk/games.asp
A quick idea: maybe some transactions get rolled back because of a deadlock?
Try to catch error 1205.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Craig" <reply2craig@.hotmail.com> wrote in message
news:flk40115pt56mnvrtqf0ol3076186pefm6@.4ax.com...
> Hi,
> I have a very strange problem and just wanted to run something with
> you guys to see if this scanario is possible.
> Is it possible that data inserted into a table using an insert query
> within a stored procedure and called from an ASP page could be
> selected by a diferent ASP page calling a stored procedure. And for
> that whole data to suddenly disappear without a trace?
> We have a site which has been running fine for a year, and recently it
> has been hit quite badly in terms of traffic. When a user comes to
> the site, data is eneterd into a user table and the Identity
> requested. Then extra info is taken from that user and entered into a
> second table using the identity (two different stored procdures. Then
> an audit (this identity, and one other piece of info) is written to
> the hard drive using a flat file, this happens for reasons I dont
> really want to go into, but nonetheless this happens.
> We have had a number of users coming back to us stating that they have
> used the site and quoted a reference (the identity) this is completely
> missing, including the data associated with it in the second table
> (The flat file is there however, which means a select must have taken>
place and data must have been there at some point). In one case,
> there was only 10 minutes between the user entering data and us
> finding it didnt exist. The identity in the flat file DOES exist.
> Where could this data go? The SP uses a commit at the end and this is
> happenening (After all, we would be able to select from this data
> unless it was commited) The sequential number is missing from the
> identity field in the first table.
> We have performed a backup and rollforward using the transaction logs
> and NONE of these SQL transactuions appear to have taken place, and do
> not appear in the transaction log at all.
> We have been looking into lots of things regarding torn pages but our
> understanding is that if this had happened, the data should still be
> in the transaction log for that day?
> the server was under considerable strain with a queue index of over 10
> so could this affect it? We have used SQL server (7 and 2000) for a
> number of years now and have never experienced anything like this.
> Logic would state that the data was in there but has subsequently been
> deleted, but the data doesnt exist in the transaction logs at all so
> how did it disappear (theres no INSERT or DELETE)?
> Any thoughts would be very much appreciated.
> Craig
>
> Play my Pot Noodle Game
> http://www.dvdhacks.co.uk/games.asp
|||On Thu, 3 Feb 2005 20:07:12 +0100, "Dejan Sarka"
<dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote:
>A quick idea: maybe some transactions get rolled back because of a deadlock?
>Try to catch error 1205.
Okay, thanks for that...... looking back though, would error 1205
show up in any logs?
Cheers
Craig
Play my Pot Noodle Game
http://www.dvdhacks.co.uk/games.asp
|||Craig wrote:
> Hi,
> I have a very strange problem and just wanted to run something with
> you guys to see if this scanario is possible.
> Is it possible that data inserted into a table using an insert query
> within a stored procedure and called from an ASP page could be
> selected by a diferent ASP page calling a stored procedure. And for
> that whole data to suddenly disappear without a trace?
> We have a site which has been running fine for a year, and recently it
> has been hit quite badly in terms of traffic. When a user comes to
> the site, data is eneterd into a user table and the Identity
> requested. Then extra info is taken from that user and entered into a
> second table using the identity (two different stored procdures. Then
> an audit (this identity, and one other piece of info) is written to
> the hard drive using a flat file, this happens for reasons I dont
> really want to go into, but nonetheless this happens.
> We have had a number of users coming back to us stating that they have
> used the site and quoted a reference (the identity) this is completely
> missing, including the data associated with it in the second table
> (The flat file is there however, which means a select must have taken
> place and data must have been there at some point). In one case,
> there was only 10 minutes between the user entering data and us
> finding it didnt exist. The identity in the flat file DOES exist.
> Where could this data go? The SP uses a commit at the end and this is
> happenening (After all, we would be able to select from this data
> unless it was commited) The sequential number is missing from the
> identity field in the first table.
> We have performed a backup and rollforward using the transaction logs
> and NONE of these SQL transactuions appear to have taken place, and do
> not appear in the transaction log at all.
> We have been looking into lots of things regarding torn pages but our
> understanding is that if this had happened, the data should still be
> in the transaction log for that day?
> the server was under considerable strain with a queue index of over 10
> so could this affect it? We have used SQL server (7 and 2000) for a
> number of years now and have never experienced anything like this.
> Logic would state that the data was in there but has subsequently been
> deleted, but the data doesnt exist in the transaction logs at all so
> how did it disappear (theres no INSERT or DELETE)?
> Any thoughts would be very much appreciated.
> Craig
>
> Play my Pot Noodle Game
> http://www.dvdhacks.co.uk/games.asp
Just a thought,
You are doing the inserts and stuff with SPs, there is a possibility the
SPs arent functioning properly, if in any of the porgram steps inside
the SP gets and warning message it just dies, NO error is returned as
the @.@.ERROR does not contain warning messages but the SP will exit
and rollback will occur.
this just might be the cause .
sql
I have a very strange problem and just wanted to run something with
you guys to see if this scanario is possible.
Is it possible that data inserted into a table using an insert query
within a stored procedure and called from an ASP page could be
selected by a diferent ASP page calling a stored procedure. And for
that whole data to suddenly disappear without a trace?
We have a site which has been running fine for a year, and recently it
has been hit quite badly in terms of traffic. When a user comes to
the site, data is eneterd into a user table and the Identity
requested. Then extra info is taken from that user and entered into a
second table using the identity (two different stored procdures. Then
an audit (this identity, and one other piece of info) is written to
the hard drive using a flat file, this happens for reasons I dont
really want to go into, but nonetheless this happens.
We have had a number of users coming back to us stating that they have
used the site and quoted a reference (the identity) this is completely
missing, including the data associated with it in the second table
(The flat file is there however, which means a select must have taken
place and data must have been there at some point). In one case,
there was only 10 minutes between the user entering data and us
finding it didnt exist. The identity in the flat file DOES exist.
Where could this data go? The SP uses a commit at the end and this is
happenening (After all, we would be able to select from this data
unless it was commited) The sequential number is missing from the
identity field in the first table.
We have performed a backup and rollforward using the transaction logs
and NONE of these SQL transactuions appear to have taken place, and do
not appear in the transaction log at all.
We have been looking into lots of things regarding torn pages but our
understanding is that if this had happened, the data should still be
in the transaction log for that day?
the server was under considerable strain with a queue index of over 10
so could this affect it? We have used SQL server (7 and 2000) for a
number of years now and have never experienced anything like this.
Logic would state that the data was in there but has subsequently been
deleted, but the data doesnt exist in the transaction logs at all so
how did it disappear (theres no INSERT or DELETE)?
Any thoughts would be very much appreciated.
Craig
Play my Pot Noodle Game
http://www.dvdhacks.co.uk/games.asp
A quick idea: maybe some transactions get rolled back because of a deadlock?
Try to catch error 1205.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Craig" <reply2craig@.hotmail.com> wrote in message
news:flk40115pt56mnvrtqf0ol3076186pefm6@.4ax.com...
> Hi,
> I have a very strange problem and just wanted to run something with
> you guys to see if this scanario is possible.
> Is it possible that data inserted into a table using an insert query
> within a stored procedure and called from an ASP page could be
> selected by a diferent ASP page calling a stored procedure. And for
> that whole data to suddenly disappear without a trace?
> We have a site which has been running fine for a year, and recently it
> has been hit quite badly in terms of traffic. When a user comes to
> the site, data is eneterd into a user table and the Identity
> requested. Then extra info is taken from that user and entered into a
> second table using the identity (two different stored procdures. Then
> an audit (this identity, and one other piece of info) is written to
> the hard drive using a flat file, this happens for reasons I dont
> really want to go into, but nonetheless this happens.
> We have had a number of users coming back to us stating that they have
> used the site and quoted a reference (the identity) this is completely
> missing, including the data associated with it in the second table
> (The flat file is there however, which means a select must have taken>
place and data must have been there at some point). In one case,
> there was only 10 minutes between the user entering data and us
> finding it didnt exist. The identity in the flat file DOES exist.
> Where could this data go? The SP uses a commit at the end and this is
> happenening (After all, we would be able to select from this data
> unless it was commited) The sequential number is missing from the
> identity field in the first table.
> We have performed a backup and rollforward using the transaction logs
> and NONE of these SQL transactuions appear to have taken place, and do
> not appear in the transaction log at all.
> We have been looking into lots of things regarding torn pages but our
> understanding is that if this had happened, the data should still be
> in the transaction log for that day?
> the server was under considerable strain with a queue index of over 10
> so could this affect it? We have used SQL server (7 and 2000) for a
> number of years now and have never experienced anything like this.
> Logic would state that the data was in there but has subsequently been
> deleted, but the data doesnt exist in the transaction logs at all so
> how did it disappear (theres no INSERT or DELETE)?
> Any thoughts would be very much appreciated.
> Craig
>
> Play my Pot Noodle Game
> http://www.dvdhacks.co.uk/games.asp
|||On Thu, 3 Feb 2005 20:07:12 +0100, "Dejan Sarka"
<dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote:
>A quick idea: maybe some transactions get rolled back because of a deadlock?
>Try to catch error 1205.
Okay, thanks for that...... looking back though, would error 1205
show up in any logs?
Cheers
Craig
Play my Pot Noodle Game
http://www.dvdhacks.co.uk/games.asp
|||Craig wrote:
> Hi,
> I have a very strange problem and just wanted to run something with
> you guys to see if this scanario is possible.
> Is it possible that data inserted into a table using an insert query
> within a stored procedure and called from an ASP page could be
> selected by a diferent ASP page calling a stored procedure. And for
> that whole data to suddenly disappear without a trace?
> We have a site which has been running fine for a year, and recently it
> has been hit quite badly in terms of traffic. When a user comes to
> the site, data is eneterd into a user table and the Identity
> requested. Then extra info is taken from that user and entered into a
> second table using the identity (two different stored procdures. Then
> an audit (this identity, and one other piece of info) is written to
> the hard drive using a flat file, this happens for reasons I dont
> really want to go into, but nonetheless this happens.
> We have had a number of users coming back to us stating that they have
> used the site and quoted a reference (the identity) this is completely
> missing, including the data associated with it in the second table
> (The flat file is there however, which means a select must have taken
> place and data must have been there at some point). In one case,
> there was only 10 minutes between the user entering data and us
> finding it didnt exist. The identity in the flat file DOES exist.
> Where could this data go? The SP uses a commit at the end and this is
> happenening (After all, we would be able to select from this data
> unless it was commited) The sequential number is missing from the
> identity field in the first table.
> We have performed a backup and rollforward using the transaction logs
> and NONE of these SQL transactuions appear to have taken place, and do
> not appear in the transaction log at all.
> We have been looking into lots of things regarding torn pages but our
> understanding is that if this had happened, the data should still be
> in the transaction log for that day?
> the server was under considerable strain with a queue index of over 10
> so could this affect it? We have used SQL server (7 and 2000) for a
> number of years now and have never experienced anything like this.
> Logic would state that the data was in there but has subsequently been
> deleted, but the data doesnt exist in the transaction logs at all so
> how did it disappear (theres no INSERT or DELETE)?
> Any thoughts would be very much appreciated.
> Craig
>
> Play my Pot Noodle Game
> http://www.dvdhacks.co.uk/games.asp
Just a thought,
You are doing the inserts and stuff with SPs, there is a possibility the
SPs arent functioning properly, if in any of the porgram steps inside
the SP gets and warning message it just dies, NO error is returned as
the @.@.ERROR does not contain warning messages but the SP will exit
and rollback will occur.
this just might be the cause .
sql
Is this even Possible?
Hi,
I have a very strange problem and just wanted to run something with
you guys to see if this scanario is possible.
Is it possible that data inserted into a table using an insert query
within a stored procedure and called from an ASP page could be
selected by a diferent ASP page calling a stored procedure. And for
that whole data to suddenly disappear without a trace?
We have a site which has been running fine for a year, and recently it
has been hit quite badly in terms of traffic. When a user comes to
the site, data is eneterd into a user table and the Identity
requested. Then extra info is taken from that user and entered into a
second table using the identity (two different stored procdures. Then
an audit (this identity, and one other piece of info) is written to
the hard drive using a flat file, this happens for reasons I dont
really want to go into, but nonetheless this happens.
We have had a number of users coming back to us stating that they have
used the site and quoted a reference (the identity) this is completely
missing, including the data associated with it in the second table
(The flat file is there however, which means a select must have taken
place and data must have been there at some point). In one case,
there was only 10 minutes between the user entering data and us
finding it didnt exist. The identity in the flat file DOES exist.
Where could this data go? The SP uses a commit at the end and this is
happenening (After all, we would be able to select from this data
unless it was commited) The sequential number is missing from the
identity field in the first table.
We have performed a backup and rollforward using the transaction logs
and NONE of these SQL transactuions appear to have taken place, and do
not appear in the transaction log at all.
We have been looking into lots of things regarding torn pages but our
understanding is that if this had happened, the data should still be
in the transaction log for that day?
the server was under considerable strain with a queue index of over 10
so could this affect it? We have used SQL server (7 and 2000) for a
number of years now and have never experienced anything like this.
Logic would state that the data was in there but has subsequently been
deleted, but the data doesnt exist in the transaction logs at all so
how did it disappear (theres no INSERT or DELETE)?
Any thoughts would be very much appreciated.
Craig
---
Play my Pot Noodle Game
http://www.dvdhacks.co.uk/games.aspA quick idea: maybe some transactions get rolled back because of a deadlock?
Try to catch error 1205.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Craig" <reply2craig@.hotmail.com> wrote in message
news:flk40115pt56mnvrtqf0ol3076186pefm6@.4ax.com...
> Hi,
> I have a very strange problem and just wanted to run something with
> you guys to see if this scanario is possible.
> Is it possible that data inserted into a table using an insert query
> within a stored procedure and called from an ASP page could be
> selected by a diferent ASP page calling a stored procedure. And for
> that whole data to suddenly disappear without a trace?
> We have a site which has been running fine for a year, and recently it
> has been hit quite badly in terms of traffic. When a user comes to
> the site, data is eneterd into a user table and the Identity
> requested. Then extra info is taken from that user and entered into a
> second table using the identity (two different stored procdures. Then
> an audit (this identity, and one other piece of info) is written to
> the hard drive using a flat file, this happens for reasons I dont
> really want to go into, but nonetheless this happens.
> We have had a number of users coming back to us stating that they have
> used the site and quoted a reference (the identity) this is completely
> missing, including the data associated with it in the second table
> (The flat file is there however, which means a select must have taken>
place and data must have been there at some point). In one case,
> there was only 10 minutes between the user entering data and us
> finding it didnt exist. The identity in the flat file DOES exist.
> Where could this data go? The SP uses a commit at the end and this is
> happenening (After all, we would be able to select from this data
> unless it was commited) The sequential number is missing from the
> identity field in the first table.
> We have performed a backup and rollforward using the transaction logs
> and NONE of these SQL transactuions appear to have taken place, and do
> not appear in the transaction log at all.
> We have been looking into lots of things regarding torn pages but our
> understanding is that if this had happened, the data should still be
> in the transaction log for that day?
> the server was under considerable strain with a queue index of over 10
> so could this affect it? We have used SQL server (7 and 2000) for a
> number of years now and have never experienced anything like this.
> Logic would state that the data was in there but has subsequently been
> deleted, but the data doesnt exist in the transaction logs at all so
> how did it disappear (theres no INSERT or DELETE)?
> Any thoughts would be very much appreciated.
> Craig
>
> ---
> Play my Pot Noodle Game
> http://www.dvdhacks.co.uk/games.asp|||On Thu, 3 Feb 2005 20:07:12 +0100, "Dejan Sarka"
<dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote:
>A quick idea: maybe some transactions get rolled back because of a deadlock?
>Try to catch error 1205.
Okay, thanks for that...... looking back though, would error 1205
show up in any logs?
Cheers
Craig
---
Play my Pot Noodle Game
http://www.dvdhacks.co.uk/games.asp|||Craig wrote:
> Hi,
> I have a very strange problem and just wanted to run something with
> you guys to see if this scanario is possible.
> Is it possible that data inserted into a table using an insert query
> within a stored procedure and called from an ASP page could be
> selected by a diferent ASP page calling a stored procedure. And for
> that whole data to suddenly disappear without a trace?
> We have a site which has been running fine for a year, and recently it
> has been hit quite badly in terms of traffic. When a user comes to
> the site, data is eneterd into a user table and the Identity
> requested. Then extra info is taken from that user and entered into a
> second table using the identity (two different stored procdures. Then
> an audit (this identity, and one other piece of info) is written to
> the hard drive using a flat file, this happens for reasons I dont
> really want to go into, but nonetheless this happens.
> We have had a number of users coming back to us stating that they have
> used the site and quoted a reference (the identity) this is completely
> missing, including the data associated with it in the second table
> (The flat file is there however, which means a select must have taken
> place and data must have been there at some point). In one case,
> there was only 10 minutes between the user entering data and us
> finding it didnt exist. The identity in the flat file DOES exist.
> Where could this data go? The SP uses a commit at the end and this is
> happenening (After all, we would be able to select from this data
> unless it was commited) The sequential number is missing from the
> identity field in the first table.
> We have performed a backup and rollforward using the transaction logs
> and NONE of these SQL transactuions appear to have taken place, and do
> not appear in the transaction log at all.
> We have been looking into lots of things regarding torn pages but our
> understanding is that if this had happened, the data should still be
> in the transaction log for that day?
> the server was under considerable strain with a queue index of over 10
> so could this affect it? We have used SQL server (7 and 2000) for a
> number of years now and have never experienced anything like this.
> Logic would state that the data was in there but has subsequently been
> deleted, but the data doesnt exist in the transaction logs at all so
> how did it disappear (theres no INSERT or DELETE)?
> Any thoughts would be very much appreciated.
> Craig
>
> ---
> Play my Pot Noodle Game
> http://www.dvdhacks.co.uk/games.asp
Just a thought,
You are doing the inserts and stuff with SPs, there is a possibility the
SPs arent functioning properly, if in any of the porgram steps inside
the SP gets and warning message it just dies, NO error is returned as
the @.@.ERROR does not contain warning messages but the SP will exit
and rollback will occur.
this just might be the cause .
I have a very strange problem and just wanted to run something with
you guys to see if this scanario is possible.
Is it possible that data inserted into a table using an insert query
within a stored procedure and called from an ASP page could be
selected by a diferent ASP page calling a stored procedure. And for
that whole data to suddenly disappear without a trace?
We have a site which has been running fine for a year, and recently it
has been hit quite badly in terms of traffic. When a user comes to
the site, data is eneterd into a user table and the Identity
requested. Then extra info is taken from that user and entered into a
second table using the identity (two different stored procdures. Then
an audit (this identity, and one other piece of info) is written to
the hard drive using a flat file, this happens for reasons I dont
really want to go into, but nonetheless this happens.
We have had a number of users coming back to us stating that they have
used the site and quoted a reference (the identity) this is completely
missing, including the data associated with it in the second table
(The flat file is there however, which means a select must have taken
place and data must have been there at some point). In one case,
there was only 10 minutes between the user entering data and us
finding it didnt exist. The identity in the flat file DOES exist.
Where could this data go? The SP uses a commit at the end and this is
happenening (After all, we would be able to select from this data
unless it was commited) The sequential number is missing from the
identity field in the first table.
We have performed a backup and rollforward using the transaction logs
and NONE of these SQL transactuions appear to have taken place, and do
not appear in the transaction log at all.
We have been looking into lots of things regarding torn pages but our
understanding is that if this had happened, the data should still be
in the transaction log for that day?
the server was under considerable strain with a queue index of over 10
so could this affect it? We have used SQL server (7 and 2000) for a
number of years now and have never experienced anything like this.
Logic would state that the data was in there but has subsequently been
deleted, but the data doesnt exist in the transaction logs at all so
how did it disappear (theres no INSERT or DELETE)?
Any thoughts would be very much appreciated.
Craig
---
Play my Pot Noodle Game
http://www.dvdhacks.co.uk/games.aspA quick idea: maybe some transactions get rolled back because of a deadlock?
Try to catch error 1205.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Craig" <reply2craig@.hotmail.com> wrote in message
news:flk40115pt56mnvrtqf0ol3076186pefm6@.4ax.com...
> Hi,
> I have a very strange problem and just wanted to run something with
> you guys to see if this scanario is possible.
> Is it possible that data inserted into a table using an insert query
> within a stored procedure and called from an ASP page could be
> selected by a diferent ASP page calling a stored procedure. And for
> that whole data to suddenly disappear without a trace?
> We have a site which has been running fine for a year, and recently it
> has been hit quite badly in terms of traffic. When a user comes to
> the site, data is eneterd into a user table and the Identity
> requested. Then extra info is taken from that user and entered into a
> second table using the identity (two different stored procdures. Then
> an audit (this identity, and one other piece of info) is written to
> the hard drive using a flat file, this happens for reasons I dont
> really want to go into, but nonetheless this happens.
> We have had a number of users coming back to us stating that they have
> used the site and quoted a reference (the identity) this is completely
> missing, including the data associated with it in the second table
> (The flat file is there however, which means a select must have taken>
place and data must have been there at some point). In one case,
> there was only 10 minutes between the user entering data and us
> finding it didnt exist. The identity in the flat file DOES exist.
> Where could this data go? The SP uses a commit at the end and this is
> happenening (After all, we would be able to select from this data
> unless it was commited) The sequential number is missing from the
> identity field in the first table.
> We have performed a backup and rollforward using the transaction logs
> and NONE of these SQL transactuions appear to have taken place, and do
> not appear in the transaction log at all.
> We have been looking into lots of things regarding torn pages but our
> understanding is that if this had happened, the data should still be
> in the transaction log for that day?
> the server was under considerable strain with a queue index of over 10
> so could this affect it? We have used SQL server (7 and 2000) for a
> number of years now and have never experienced anything like this.
> Logic would state that the data was in there but has subsequently been
> deleted, but the data doesnt exist in the transaction logs at all so
> how did it disappear (theres no INSERT or DELETE)?
> Any thoughts would be very much appreciated.
> Craig
>
> ---
> Play my Pot Noodle Game
> http://www.dvdhacks.co.uk/games.asp|||On Thu, 3 Feb 2005 20:07:12 +0100, "Dejan Sarka"
<dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote:
>A quick idea: maybe some transactions get rolled back because of a deadlock?
>Try to catch error 1205.
Okay, thanks for that...... looking back though, would error 1205
show up in any logs?
Cheers
Craig
---
Play my Pot Noodle Game
http://www.dvdhacks.co.uk/games.asp|||Craig wrote:
> Hi,
> I have a very strange problem and just wanted to run something with
> you guys to see if this scanario is possible.
> Is it possible that data inserted into a table using an insert query
> within a stored procedure and called from an ASP page could be
> selected by a diferent ASP page calling a stored procedure. And for
> that whole data to suddenly disappear without a trace?
> We have a site which has been running fine for a year, and recently it
> has been hit quite badly in terms of traffic. When a user comes to
> the site, data is eneterd into a user table and the Identity
> requested. Then extra info is taken from that user and entered into a
> second table using the identity (two different stored procdures. Then
> an audit (this identity, and one other piece of info) is written to
> the hard drive using a flat file, this happens for reasons I dont
> really want to go into, but nonetheless this happens.
> We have had a number of users coming back to us stating that they have
> used the site and quoted a reference (the identity) this is completely
> missing, including the data associated with it in the second table
> (The flat file is there however, which means a select must have taken
> place and data must have been there at some point). In one case,
> there was only 10 minutes between the user entering data and us
> finding it didnt exist. The identity in the flat file DOES exist.
> Where could this data go? The SP uses a commit at the end and this is
> happenening (After all, we would be able to select from this data
> unless it was commited) The sequential number is missing from the
> identity field in the first table.
> We have performed a backup and rollforward using the transaction logs
> and NONE of these SQL transactuions appear to have taken place, and do
> not appear in the transaction log at all.
> We have been looking into lots of things regarding torn pages but our
> understanding is that if this had happened, the data should still be
> in the transaction log for that day?
> the server was under considerable strain with a queue index of over 10
> so could this affect it? We have used SQL server (7 and 2000) for a
> number of years now and have never experienced anything like this.
> Logic would state that the data was in there but has subsequently been
> deleted, but the data doesnt exist in the transaction logs at all so
> how did it disappear (theres no INSERT or DELETE)?
> Any thoughts would be very much appreciated.
> Craig
>
> ---
> Play my Pot Noodle Game
> http://www.dvdhacks.co.uk/games.asp
Just a thought,
You are doing the inserts and stuff with SPs, there is a possibility the
SPs arent functioning properly, if in any of the porgram steps inside
the SP gets and warning message it just dies, NO error is returned as
the @.@.ERROR does not contain warning messages but the SP will exit
and rollback will occur.
this just might be the cause .
Friday, February 24, 2012
Is there any REGEXP library for TSQL?
Hi guys,
Sounds a bit strange, however, if we could put some calculation in
stored procedure it would be quite convenient, just... where can I find
a REGEXP library for matching checking? thanks.
yours,
athos"athos" <athos.liu@.gmail.com> wrote in message
news:1130877928.278288.62290@.g44g2000cwa.googlegro ups.com...
> Hi guys,
> Sounds a bit strange, however, if we could put some calculation in
> stored procedure it would be quite convenient, just... where can I find
> a REGEXP library for matching checking? thanks.
> yours,
> athos
Take a look at the LIKE topic in Books Online to see if it meets your
requirements. Not regex but it does support some simple pattern matching.
--
David Portas
SQL Server MVP
--|||LIKE is not powerful enough. btw, COM is prohibited. thanks.|||athos (athos.liu@.gmail.com) writes:
> Sounds a bit strange, however, if we could put some calculation in
> stored procedure it would be quite convenient, just... where can I find
> a REGEXP library for matching checking? thanks.
It does not sound strange at all. Some DB Engines have SIMILAR TO, and
this might even be in ANSI. I believe this uses some form of regexps.
I've been longing for it myself at times.
But for SQL2000 there is only LIKE which is far from whole covering.
You can use patindex or charindex for some stuff, but in essence it's
all very primitive.
In SQL 2005, there is no better support in T-SQL, but you can call a CLR
routine that uses the RegEx classes in .Net.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> In SQL 2005, there is no better support in T-SQL, but you can call a CLR
> routine that uses the RegEx classes in .Net.
I guess for SQL 2000, you could use a non-COM library as an "extended
procedure"?
--
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||Martijn Tonies (m.tonies@.upscene-removethis.nospam.com) writes:
>> In SQL 2005, there is no better support in T-SQL, but you can call a CLR
>> routine that uses the RegEx classes in .Net.
> I guess for SQL 2000, you could use a non-COM library as an "extended
> procedure"?
But performance would be awful and the code would be messy.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> >> In SQL 2005, there is no better support in T-SQL, but you can call a
CLR
> >> routine that uses the RegEx classes in .Net.
> > I guess for SQL 2000, you could use a non-COM library as an "extended
> > procedure"?
> But performance would be awful and the code would be messy.
I've never written any extended procedures, so perhaps you could
explain why this would give awful performance?
I imagine the call could be as:
select ...
from ...
where myregexp_match(mycolumn, myexpression, myvalue)
Why would this be any slower than COM or .NET? Isn't this partly
what extended procedures were meant for?
--
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:11mhdiekuhe26e9@.corp.supernews.com...
> > >> In SQL 2005, there is no better support in T-SQL, but you can call a
> CLR
> > >> routine that uses the RegEx classes in .Net.
> > > > I guess for SQL 2000, you could use a non-COM library as an "extended
> > > procedure"?
> > But performance would be awful and the code would be messy.
> I've never written any extended procedures, so perhaps you could
> explain why this would give awful performance?
> I imagine the call could be as:
> select ...
> from ...
> where myregexp_match(mycolumn, myexpression, myvalue)
> Why would this be any slower than COM or .NET? Isn't this partly
> what extended procedures were meant for?
I'm guessing the main reason is that in SQL 2000, it executes outside of SQL
Server, which means for every call there's delay as it has to call out of
its address space. SQL 2005 CLR code executes within the same memory space
as SQL Server.
> --
> With regards,
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com|||Martijn Tonies (m.tonies@.upscene-removethis.nospam.com) writes:
> I've never written any extended procedures, so perhaps you could
> explain why this would give awful performance?
> I imagine the call could be as:
> select ...
> from ...
> where myregexp_match(mycolumn, myexpression, myvalue)
That's not really how you call extended stored procedure. But you could
encapsulate the XP in a user-defined function to get this syntax. However,
there is a big overhead for calling a UDF in a WHERE clause in SQL 2000
(this overhead has been reduced in SQL 2005). If you then add a call to
extended stored procedure that gives you context switches and all, it's
getting really bad.
Then add to this that if you have a bug in your XP that causes an
access violation or similar, it's not only the XP that crashes. You
blow away the entire SQL Server.
> Why would this be any slower than COM or .NET? Isn't this partly
> what extended procedures were meant for?
The CLR stuff in SQL 2005 is a lot more integrated in SQL Server and there
is far less overhead for invoking CLR. In fact, say that you have a decently
complex operation like some string manipulation that you can perform in
T-SQL, it is very likely to perform better in a CLR UDF. (But if you
start do data access from the CLR, it's a different picture.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> > I've never written any extended procedures, so perhaps you could
> > explain why this would give awful performance?
> > I imagine the call could be as:
> > select ...
> > from ...
> > where myregexp_match(mycolumn, myexpression, myvalue)
> That's not really how you call extended stored procedure. But you could
> encapsulate the XP in a user-defined function to get this syntax. However,
> there is a big overhead for calling a UDF in a WHERE clause in SQL 2000
> (this overhead has been reduced in SQL 2005). If you then add a call to
> extended stored procedure that gives you context switches and all, it's
> getting really bad.
Then when are XPs actually useful?
> Then add to this that if you have a bug in your XP that causes an
> access violation or similar, it's not only the XP that crashes. You
> blow away the entire SQL Server.
I understand this part, seems to be the case with pretty much all
extending to DB engines (unless managed or Java or whatever).|||Martijn Tonies (m.tonies@.upscene-removethis.nospam.com) writes:
>> That's not really how you call extended stored procedure. But you could
>> encapsulate the XP in a user-defined function to get this syntax.
>> However, there is a big overhead for calling a UDF in a WHERE clause in
>> SQL 2000 (this overhead has been reduced in SQL 2005). If you then add
>> a call to extended stored procedure that gives you context switches and
>> all, it's getting really bad.
> Then when are XPs actually useful?
When the stuff you want to do with them are not used to evaluate queries.
For instance, we have an extended stored procedure that performs a loopback
and writes messages to a log table when an error is detected. (The point
with the loopback is that we want the log records to persist even if there
is a rollback.)
Another possible application is some sort of signaling, to inform some
external process "Hey, I've just inserted 10000 rows, you might be
interested in those".
But it is correct that XP:s, as well as sp_OAcreate & co for calling
OLE objects, have limited use, and something you only use for special
cases.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> But it is correct that XP:s, as well as sp_OAcreate & co for calling
> OLE objects, have limited use, and something you only use for special
> cases.
Thanks for the explanation.
--
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
Subscribe to:
Comments (Atom)