Insert, updates not woring until SQL server restart.

Hi all,
I hope this is the right place to post my question/problem.
I have this problem with SQL server. All of a sudden the insert, updates doe
s
not work on SQL server based application written in ASP. The select commands
are returning fine, its only the inserts and updates. They dont appera to
make any changes in the database. I looked into the ErrorLog files on SQL
server and they look pretty normal. No error or query truncation messages.
I resrated the SQL server and things get to be working again. This happened
a couple of times. It can't be the memory issue, coz there is ample of space
and also the datalog is set to grow as required (not limited).
SQL server is upgraded to SP3. So it is up with the upgrades.
From the application side, either the asp page goes to a page not found
error or returns back to the page as it should, but no updates to database.
Any clues to the problem would be appreciated.
Thanks,
SamRun sp_who2 from query analyser to see if there is any blocking on the serve
r
when this occurs.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"sam" wrote:

> Hi all,
> I hope this is the right place to post my question/problem.
> I have this problem with SQL server. All of a sudden the insert, updates d
oes
> not work on SQL server based application written in ASP. The select comman
ds
> are returning fine, its only the inserts and updates. They dont appera to
> make any changes in the database. I looked into the ErrorLog files on SQL
> server and they look pretty normal. No error or query truncation messages.
> I resrated the SQL server and things get to be working again. This happene
d
> a couple of times. It can't be the memory issue, coz there is ample of spa
ce
> and also the datalog is set to grow as required (not limited).
> SQL server is upgraded to SP3. So it is up with the upgrades.
> From the application side, either the asp page goes to a page not found
> error or returns back to the page as it should, but no updates to database
.
> Any clues to the problem would be appreciated.
> Thanks,
> Sam|||Mike,
Thanks for the reply. I did run the sp_who2 stored procedure, and at the
time when the same problem situation arised. I didnt notice any blocking fro
mthe results. Nothing in the BlkBy column. I also created a small code that
runs the sp_who2 and file and put that in a batch process. Did'nt see any
information in BlkBy.
Any other way I could find out more information. or am I missing something
in urderstanding sp_who2 results?
"Mike Epprecht (SQL MVP)" wrote:
> Run sp_who2 from query analyser to see if there is any blocking on the ser
ver
> when this occurs.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "sam" wrote:
>

Insert, update does not work , until SQL server restart.

Hi,
I am not sure if this is the right place to post my problem. If not so, pls
do advise me of the right group.
Here is my problem:
I have this problem with SQL server. All of a sudden the insert, updates does
not work on SQL server based application written in ASP. The select commands
are returning fine, its only the inserts and updates. They dont appera to
make any changes in the database. I looked into the ErrorLog files on SQL
server and they look pretty normal. No error or query truncation messages.
I restarted the SQL server and things get to be working again. This happened
a couple of times. It can't be the memory issue, coz there is ample of space
and also the datalog is set to grow as required (not limited).
SQL server is upgraded to SP3. So it is up with the upgrades.
From the application side, either the asp page goes to a page not found
error or returns back to the page as it should, but no updates to database.
Any clues to the problem would be appreciated.
Thanks,
Sam
You may want to check for blocking, locking issue when you
can't do the inserts/updates. You can use query analyzer and
query sysprocesses, execute sp_who2, sp_lock to see what's
going on when the inserts, updates aren't successful.
-Sue
On Thu, 4 Aug 2005 10:15:11 -0700, sam
<sam@.discussions.microsoft.com> wrote:

>Hi,
>I am not sure if this is the right place to post my problem. If not so, pls
>do advise me of the right group.
>Here is my problem:
>I have this problem with SQL server. All of a sudden the insert, updates does
> not work on SQL server based application written in ASP. The select commands
> are returning fine, its only the inserts and updates. They dont appera to
>make any changes in the database. I looked into the ErrorLog files on SQL
> server and they look pretty normal. No error or query truncation messages.
> I restarted the SQL server and things get to be working again. This happened
> a couple of times. It can't be the memory issue, coz there is ample of space
> and also the datalog is set to grow as required (not limited).
> SQL server is upgraded to SP3. So it is up with the upgrades.
> From the application side, either the asp page goes to a page not found
> error or returns back to the page as it should, but no updates to database.
>Any clues to the problem would be appreciated.
>Thanks,
>Sam
|||Thanks for the reply Sue,
I checked the sp_who2 and the sp_lock results. Didnt find any clue there. I
didnt find any in the BlkBy field of the results nor any thing from sp_lock.
all the processes are Granted. And i checked it at the time the problem
arised. It looks like SQL server is sorting itself after a while. The Lock
Type for al lthe requests is DB. Does that mean anything and the Status shows
as GRANT. Mode is 'S'.
Is there anything else that I should look for?
"Sue Hoegemeier" wrote:

> You may want to check for blocking, locking issue when you
> can't do the inserts/updates. You can use query analyzer and
> query sysprocesses, execute sp_who2, sp_lock to see what's
> going on when the inserts, updates aren't successful.
> -Sue
> On Thu, 4 Aug 2005 10:15:11 -0700, sam
> <sam@.discussions.microsoft.com> wrote:
>
>
|||Then next you may want to try running profiler to monitor
what's going across when you do an insert, update and
monitor the server itself with Perf Mon.
When you trace the database activity, check for statements
starting with no corresponding statement completed. It could
be a timeout issue or could be related to resource
constraints on the server itself.
-Sue
On Tue, 9 Aug 2005 15:19:03 -0700, sam
<sam@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for the reply Sue,
>I checked the sp_who2 and the sp_lock results. Didnt find any clue there. I
>didnt find any in the BlkBy field of the results nor any thing from sp_lock.
>all the processes are Granted. And i checked it at the time the problem
>arised. It looks like SQL server is sorting itself after a while. The Lock
>Type for al lthe requests is DB. Does that mean anything and the Status shows
>as GRANT. Mode is 'S'.
>Is there anything else that I should look for?
>"Sue Hoegemeier" wrote:

Insert, update does not work , until SQL server restart.

Hi,
I am not sure if this is the right place to post my problem. If not so, pls
do advise me of the right group.
Here is my problem:
I have this problem with SQL server. All of a sudden the insert, updates doe
s
not work on SQL server based application written in ASP. The select commands
are returning fine, its only the inserts and updates. They dont appera to
make any changes in the database. I looked into the ErrorLog files on SQL
server and they look pretty normal. No error or query truncation messages.
I restarted the SQL server and things get to be working again. This happened
a couple of times. It can't be the memory issue, coz there is ample of space
and also the datalog is set to grow as required (not limited).
SQL server is upgraded to SP3. So it is up with the upgrades.
From the application side, either the asp page goes to a page not found
error or returns back to the page as it should, but no updates to database.
Any clues to the problem would be appreciated.
Thanks,
SamYou may want to check for blocking, locking issue when you
can't do the inserts/updates. You can use query analyzer and
query sysprocesses, execute sp_who2, sp_lock to see what's
going on when the inserts, updates aren't successful.
-Sue
On Thu, 4 Aug 2005 10:15:11 -0700, sam
<sam@.discussions.microsoft.com> wrote:

>Hi,
>I am not sure if this is the right place to post my problem. If not so, pls
>do advise me of the right group.
>Here is my problem:
>I have this problem with SQL server. All of a sudden the insert, updates do
es
> not work on SQL server based application written in ASP. The select comman
ds
> are returning fine, its only the inserts and updates. They dont appera to
>make any changes in the database. I looked into the ErrorLog files on SQL
> server and they look pretty normal. No error or query truncation messages.
> I restarted the SQL server and things get to be working again. This happen
ed
> a couple of times. It can't be the memory issue, coz there is ample of spa
ce
> and also the datalog is set to grow as required (not limited).
> SQL server is upgraded to SP3. So it is up with the upgrades.
> From the application side, either the asp page goes to a page not found
> error or returns back to the page as it should, but no updates to database
.
>Any clues to the problem would be appreciated.
>Thanks,
>Sam|||Thanks for the reply Sue,
I checked the sp_who2 and the sp_lock results. Didnt find any clue there. I
didnt find any in the BlkBy field of the results nor any thing from sp_lock.
all the processes are Granted. And i checked it at the time the problem
arised. It looks like SQL server is sorting itself after a while. The Lock
Type for al lthe requests is DB. Does that mean anything and the Status show
s
as GRANT. Mode is 'S'.
Is there anything else that I should look for?
"Sue Hoegemeier" wrote:

> You may want to check for blocking, locking issue when you
> can't do the inserts/updates. You can use query analyzer and
> query sysprocesses, execute sp_who2, sp_lock to see what's
> going on when the inserts, updates aren't successful.
> -Sue
> On Thu, 4 Aug 2005 10:15:11 -0700, sam
> <sam@.discussions.microsoft.com> wrote:
>
>|||Then next you may want to try running profiler to monitor
what's going across when you do an insert, update and
monitor the server itself with Perf Mon.
When you trace the database activity, check for statements
starting with no corresponding statement completed. It could
be a timeout issue or could be related to resource
constraints on the server itself.
-Sue
On Tue, 9 Aug 2005 15:19:03 -0700, sam
<sam@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for the reply Sue,
>I checked the sp_who2 and the sp_lock results. Didnt find any clue there. I
>didnt find any in the BlkBy field of the results nor any thing from sp_lock
.
>all the processes are Granted. And i checked it at the time the problem
>arised. It looks like SQL server is sorting itself after a while. The Lock
>Type for al lthe requests is DB. Does that mean anything and the Status sho
ws
>as GRANT. Mode is 'S'.
>Is there anything else that I should look for?
>"Sue Hoegemeier" wrote:
>