Have a situation where my app loses connectivity to db between the hours of 12 am and 4am every night. How should I be troubleshooting this ?
Every night between the hours of 12 am to 4 am the app runs out of connections. We use c3p0 to manage the connection pool. Currently have a maxpoolsize of 20. Its very intriguing that this happens only in the night. Would appreciate if someone could guide me on how can i troubleshoot this
I would guess that your problem could be related to database maintenance because of the times you mention. Probably integrity checks, index or statistics maintenance that could cause your server to be overloaded and that in turn could cause delays for your app and ultimately time outs and connectivity loss. Backups usually do not cause problems for apps, unless you are dumping backups to the same disk where you have data/tlog files which would cause delays since the disk is working hard. - First I would look in SQL error log/Event viewer (correlate with app server error logs) to get some clues, when does the problem Occur a specific night. - Then I would check your database maintenance schedule and try to correlate it with the times of errors above. - Also if you have performance counters (cpu/mem/disk) that you can go back to and look at, they could also give you a clue at what time your problem is occuring. (If you are using vmware, have a look in virtual center and the performance tab for that server) **For example:** Maybe your errors always occur when Index Maintenance is running. And maybe you run Index Maintenance with the built in Maintenace Plans in SQL server. Since built Index Maintenace is pretty brutal and rebuilds/reorganizes all indexes every time, the best solution could be to implement a script that only rebuilds indexes that are heavily fragmented. Have a look if you can get any clues and post them here and then we can try to help you to narrow down the problem, if it is SQL related =)
Resolved ten weeks ago. Update, the transaction management was through Spring framework's TransactionManager. For some reason in this deployment the Spring's transactionManager was not commiting. We made our said code nonTransactional and handled transactionManagement ourselves and that resolved this issue
looks like spid51 may be the cause of the whole thing this process seems to be blocking everyone, I have given the customer few more directions and a store procedure to find out what code this process is executing. Unfortunately I am an application person and not DB expert not sure what 'Awaiting Command' means, any guesses. full result set of running sp_who2, maybe someone can help. thanks [link text] : /storage/temp/
one thing which did not make sense to me is that the customer said that during that time period they have external scrips regularly testing to see if Db is online and they can verify that it is. I explained to them that test may not be perfect since these are short pings. While our java app is running continous jobs or reports with connections from connection pool that may stay alive longer and resource intensive, so we see them suffer more.