Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
agiotti
Starting Member
37 Posts |
 Posted - 2011-04-06 : 10:54:08
|
| Summary information: Exclusive, Update, and Share Page Latch events were identified on specific temporary tables within TEMPDB during the analyzed period. A SQL Server background process was being blocked on a temporary table owned by the suspect process. This contention caused the LOGON TRIGGER to continually fail preventing other sessions from logging into the instance. During the analyzed period, the suspect process is performing a large number of inserts into the temporary table in question.Enviroment:WINDOWS 2003 Enterprise (Cluster Active/Passive)SQL Server 2005 Enterprise (SP3 CU6)Problem Statement:Time window of occurrence: 5 minutes.This Instance utilizes a logon trigger to prevent unauthorized access via SSQL Server Accounts. The trigger has been in place for two years without any issues. Recently we had been experiencing failed login attempts with many backend process accounts.“Logon failed due to trigger execution”Upon further research we noticed excessive buffer latch contention (PAGELATCH_UP) referenicng the MASTER database during this specific timeframe. Compared to our baseline buffer latch waits have increased by 1000%. Also, CPU consumption increased by 36% compared to the baseline. Overall workload increased from 7 active sessions to 55 active sessions during the analyzed period. Additional Information:Server has plenty of memory to accommodate the additional sessions. 64GB RAM allocated to SQL Server. Page Life 18K. Has anyone run into similar issues with the use of Login Triggers |
|
|
agiotti
Starting Member
37 Posts |
Posted - 2011-04-22 : 09:30:52
|
| Still no takers? this is a very challenging. More information…1. We’ve found that there is a new process running inserting massive amounts of data into a temporary table. This process loops through 1,000’s of records calculating financial returns. Once we’ve killed this particular thread the latch contention within MASTER subsided. Note: this process runs against an user defined database, not master directly. However, the logon trigger is associated with MASTER, which is my opinion.2. The duration of today's incident lasted for 1 hour. |
 |
|
|
agiotti
Starting Member
37 Posts |
Posted - 2011-04-22 : 13:09:51
|
| More information: During the time we are experiencing heavy PAGELATCH contention within the MASTER database a SQL Server background process, running out of the MASTER database, is being blocked. The background process, command=TASK MANAGER, is attempting to acquire a LCK_M_Sch_S on a temporary table in TEMPDB. This temporary table is locked by the process mentioned earlier. I need to understand what this background process is doing and why does it require a LCK_M_Sch_S on this processes temporary table. Finding what the background process is may shed some light on why the logon trigger is failing. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
agiotti
Starting Member
37 Posts |
Posted - 2011-04-22 : 14:20:39
|
| Thanks Tara, I appreciate it. I have also posted this on SQLServer Central. http://www.sqlservercentral.com/Forums/Topic1097538-146-1.aspx?Update=1 |
|
|
|
|
|
|
|
|
PAGELATCH_UP