CODEWITHSUNDEEP
sql-server-2008
vbNewbie
vbNewbie

Reputation: 3345

sql server - database with stored procedures using all of memory on server

I have a database with two tables that are filled with data from parallel processes running inserting data using stored procedures. The sql server is practically using all of the physical memory and I noticed that some tables were missing data and when I dbugged, I found that due to timeout errors data was not being inserted since I did not account for this. Someone told me to run exec who2 and looking at all the transactions associated with this application it looks as follows

spid   status   login    hostname    blkby  dbname   command ......

738    sleeping  sa      R120         .     FeedsRS   AWAITING COMMAND
739    sleeping  sa      R120         .     FeedsRS   AWAITING COMMAND
740    sleeping  sa      R120         .     FeedsRS   AWAITING COMMAND

Now there are about 800 of these and I am not sure what it means. If the transactions are locked, is there a way to end them without data problems, while I add code to handle the timeout errors. I am not even certain if this is the problem but should I also set the lock timeout to a reasonable value instead of the 0 default. Any ideas appreciated.

Upvotes: 0

Views: 246

Answers (1)

gbn
gbn

Reputation: 432471

probably one of:

  • You aren't closing connections
  • You have poor transaction handling

And SQL Server will use most memory by default: it's isn't a leak or a bug.

To check for open transactions

SELECT * FROM sys.sysprocesses WHERE open_tran > 0

Upvotes: 1

Related Questions