HI There,
a client i work with has a 3 cluster setup with biztalk databases on each cluster. one instance continually uses its full allocation of 40GB max server memory. i can view reports that show me the I/O and CPU and the memory might be related just to the amount of data coming back but is there a report or sql out there that would show me what SQL is using the most memory?
also, on the same environment the clusters randomly fail over to the second nodes, this occurs for all 3 clusters at the same time. no one has put their hand up to say they are failing over and the logs are showing very little, i have a suspicion that the biztalk front end might have something to do with it but do not know enough about biztalk to go looking for a setting that would perform this action. the only errors i can see across all 3 clusters is access to data files seems to be an issue:
Cluster1:
SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [F:\MSSQL10.Cluster1\MSSQL\DATA\BAMPrimaryImport.mdf] in database [BAMPrimaryImport] (5). The OS file handle is 0x000000000000070C. The offset of the latest long I/O is: 0x00000b51cfe000
SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [G:\MSSQL10.Cluster1\MSSQL\Logs\templog.ldf] in database [tempdb] (2). The OS file handle is 0x00000000000005C8. The offset of the latest long I/O is: 0x0000000041c000
SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [G:\MSSQL10.Cluster1\MSSQL\Logs\BAMAlertsNSMain_log.LDF] in database [BAMAlertsNSMain] (8). The OS file handle is 0x0000000000000760. The offset of the latest long I/O is: 0x00000038b27000
Cluster2:
The log for database 'BizTalkMsgBoxDb' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
Cluster3:
The log for database 'BizTalkMgmtDb' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
again i think it is the large data being returned that is locking up the files but would that be enough to cause a failover?
the sysadmin says the SAN is ok at these times and the biztlkadmin reckons they were not working on the system at the time so i was wondering if anyone has any ides how i could capture the cause??
thanks in advance.
a client i work with has a 3 cluster setup with biztalk databases on each cluster. one instance continually uses its full allocation of 40GB max server memory. i can view reports that show me the I/O and CPU and the memory might be related just to the amount of data coming back but is there a report or sql out there that would show me what SQL is using the most memory?
also, on the same environment the clusters randomly fail over to the second nodes, this occurs for all 3 clusters at the same time. no one has put their hand up to say they are failing over and the logs are showing very little, i have a suspicion that the biztalk front end might have something to do with it but do not know enough about biztalk to go looking for a setting that would perform this action. the only errors i can see across all 3 clusters is access to data files seems to be an issue:
Cluster1:
SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [F:\MSSQL10.Cluster1\MSSQL\DATA\BAMPrimaryImport.mdf] in database [BAMPrimaryImport] (5). The OS file handle is 0x000000000000070C. The offset of the latest long I/O is: 0x00000b51cfe000
SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [G:\MSSQL10.Cluster1\MSSQL\Logs\templog.ldf] in database [tempdb] (2). The OS file handle is 0x00000000000005C8. The offset of the latest long I/O is: 0x0000000041c000
SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [G:\MSSQL10.Cluster1\MSSQL\Logs\BAMAlertsNSMain_log.LDF] in database [BAMAlertsNSMain] (8). The OS file handle is 0x0000000000000760. The offset of the latest long I/O is: 0x00000038b27000
Cluster2:
The log for database 'BizTalkMsgBoxDb' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
Cluster3:
The log for database 'BizTalkMgmtDb' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
again i think it is the large data being returned that is locking up the files but would that be enough to cause a failover?
the sysadmin says the SAN is ok at these times and the biztlkadmin reckons they were not working on the system at the time so i was wondering if anyone has any ides how i could capture the cause??
thanks in advance.
