Have a deadlock situation for almost every two weeks, and application is moving but very slow, but could not verify.
No access to the box. At this time, the transaction log was filled up, it was now set as 1 GB in size but increasing the space to 2GB will not resolve the situation which I feel.
The sybase recover by itself after the peak hours. The system was set in default.
The only maitenance tasks are
1. Update statistics followed by sp_recompile on all tables in apps database
2. DBCC Checkstorage done on SSR database
3. Normal DBCCs done on other databases e.g. master
4. reorg compact run on all DOL tables that meet criteria (1% or 1000 rows can be reclaimed)
5. As of next weekend reorg reindex will be run on specified DOL tables E.G. tables etc.
The database has no scheduled restarts.
The Sybase configuration for deadlocks is the default configuration that you can see below.
Configuration option is not unique.
Parameter Name Default Memory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
deadlock checking period 500 0 500 500
deadlock pipe active 0 0 0 0
deadlock pipe max messages 0 0 0 0
deadlock retries 5 0 5 5
print deadlock information 0 0 0 0
(1 row affected)
(return status = 1)
CPU was average at 20-30% but at peak hours 30-40%
Number of 'open databases' configured: 12
Actual number of databases in server: 9
'OpenDbRemaining' parameter's average: 9.98
Average connections used: 49.236486 (9%)
Average connections free: 550.763514 (91%)
Total 'number of user connections' configured: 600
Maximum connections remaining: 596
Minimum connections remaining: 0
During that the transaction logs was fill and in the end it was crashed due to db option "abort on log full
Anything which I might have missed?? Any suggestion are welcome.
The last option which I have not tried is to turn on the sp_sysmon? The only time it happens was every two weeks no fix day, so if when the problem occur, turning the sp_sysmon will it help? For 3 incidents, the deadlock encounter and my application hang.