Help! I have -2, -3, or -4 Session ID!

We can kill a session by using KILL command. However, KILL command requires a positive number; executing KILL with negative number returns an error: Msg 6101, Level 16, State 1, Line 1 Session ID -4 is not valid. In order to kill the session ID, you need to find the unit of work (UOW) guid….

0

Dissecting SQL Server Execution Plans

I remember my days before, Microsoft SQL Server PFE.  I wanted to learn everything and know everything about SQL Server.  However, getting hold of good resources was tough, as I didn’t have any mentor when I started down my journey to becoming a SQL Server Database Administrator. Along the way I did pick up lots…

0

How to get orphaned logins for all databases?

Following script gives you all user database users, that do not have a server login mapped or where database user name does not match server login name. This is follow up post to How get all users and their role mappings from all databases? I posted few days ago. IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE…

0

Changing @@SERVERNAME causes SQL Server Backups to Fail for AlwaysOn Availability Group

One of customers changed the value returned from @@SERVERNAME.  SQL Server works no problem, however an unexpected behavior appeared.  Changing the value for @@SERVERNAME, caused the backups to fail. Looking at the maintenance jobs, found all jobs completed successfully and without issues.  However, upon looking at the database’s statics it states no backups completed. Because…

0

sys.foreign_keys does not have matching row in sys.indexes

Running DBCC CHECKDB you are getting following error message: Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=194099732,key_index_id=7) of row (object_id=2040565179) in sys.foreign_keys does not have a matching row (object_id=194099732,index_id=7) in sys.indexes This error means, that Unique key constraint (index_id 7) in the primary table (object_id 194099732) is missing, which was referenced by child table’s FK…

0

Index Creation Date

Often when troubleshooting performance related issues, it is beneficial to know when new indexes were introduced.  However currently there is no easy way to keep track of this information or attain this information.  That’s why one of my colleagues and a good friend, Mohamed Sharaf (Blog | Twitter), has submitted a request on Microsoft Connect,…

0

SQL Server 2012 NUMA Node Imbalance–Conclusion

Final post in the series, I hope to summarize my findings for everyone.  Thanks for all the comments, feedback and comments I have received to-date on these posts.  Before providing the summary of all the testing, research and reading, I want to provide some internals for folks. NUAM Memory Allocation If a server has NUMA…

0

SQL Server 2012 NUMA Node Imbalance – Cont’d

Yesterday, I had posted an issue I been troubleshooting with memory allocation.  That was causing CPU to pin, you can read more about the issue at SQL Server 2012 NUMA Node Imbalance.  Even though I found what was causing it, I didn’t understand the internals well enough to identify the root cause. Additional testing I…

2

SQL Server 2012 NUMA Node Imbalance

One of my client has been fighting a very strange problem and there is not much literature on the topic, NUMA Node Imbalance. Server Configuration 8 NUMA nodes with 6 cores each; totaling 48 cores. 256GB memory. 12 Instances of SQL Server; mix of SQL Sever 2012 Latest Build and SQL Server 2008 R2 SP2….

0

Service Principal Names for SQL Server

Service Principal Names (SPN) get used for Kerberos authentication.  SQL Server require that all instances have SPNs configured, if not Kerberos authentication fails.  By default starting with Windows 2008 all communication between clients and SQL Server is first attempted on Kerberos.  If it fails, it falls back to NTLM.  Often if you have Kerberos issues…

0