Failed Updates - Help

[jivee]

Hi
I am using SQL Server 6.5 as my backend for a VB6 Application.
This is a OLTP kind of package that I have developed with about 600 Users.
I use BeginTran and CommitTran between my Update/Insert Queries in my VB Application.
My database has grown considerably and all of a sudden, all
the CommitTran failed. None of the records were saved.

When I stopped and restarted my SQL Server, the operation became
normal all Update/Insert queries for the same Application succeeded.
(Note that I have not stopped my Server for the last 45 days).

WHY DOES THIS HAPPEN?
HOW DO I AVOID THIS IN FUTURE...
PLEASE HELP
Thanks
Jivee

[Jackie]

Hello Jeevie,
If you do not recycle your server for 45 days, then your tempdb might have been increasing and if the the log for the DB you are connecting to increased beyond a certain point your SQL server will stop. So the key is to dump the log at regular intervals.
Also as you are using a VB application to make connections to SQL server, it is a good idea to see how the connections are being handled. SO keep running sp_who and sp_who2 often to check and see that your application is not eating up your SQL connections.
You might want to read about transaction log and dumping the log at regular intervals.
keep running dbcc logspace(DBName) to check and see how much log space is being used.The lesser the better.
Thanks


------------
jivee at 9/29/00 11:11:22 AM

Hi
I am using SQL Server 6.5 as my backend for a VB6 Application.
This is a OLTP kind of package that I have developed with about 600 Users.
I use BeginTran and CommitTran between my Update/Insert Queries in my VB Application.
My database has grown considerably and all of a sudden, all
the CommitTran failed. None of the records were saved.

When I stopped and restarted my SQL Server, the operation became
normal all Update/Insert queries for the same Application succeeded.
(Note that I have not stopped my Server for the last 45 days).

WHY DOES THIS HAPPEN?
HOW DO I AVOID THIS IN FUTURE...
PLEASE HELP
Thanks
Jivee