Vision: SQL Homeostasis

Recently I have had to troubleshoot quite a bit of SQL login issues and often times the issue was with the users active directory user account. I was aware the my organization has security policies that includes among other things, active directory password policy. Of course there is also SQL Server user security policy, which I know by heart. To better support my users, I thought it would be a good idea to familiarize myself with the active directory password policy that is in effect. Now, there is a document that outlines the policy but, I want to get what is actually implemented. Here, I am using powershell to get the password policy values. You will need to have the ActiveDirectory PowerShell module installed for the following cmdlets to work. Get-ADUserResultantPasswordPolicy -Identity aduser1 That returned nothing in my case. That most likely implies that the password policy is not assigned per user basis. So now I am going to check what is the default password policy in the...

If your organization uses a password policy (there are very good odds these days that it does) and, especially stricter password requirement for administrative users, your might have experienced instances where yours or your users Active Directory user might be locked out. How do you check if that is the case? Well, for one thing the Windows will tell you so when you try to login and/or failed login attempts are logged in to sql log, event logs etc.  What if user does not logout or have more than one user account, one for regular use and one for administrative tasks? There maybe other scenarios where you have a need to check status of a user account in the Active Directory. I don't have admin privileges and presumably you don't either.  However, I do have read permission on the AD so I could have used Active Directory Users and Groups snap-in. But, here I am going to show the powershell way. You will need to have the ActiveDirectory PowerShell module installed for the fol...

Event notifications are kinda like a trigger in the sense that they respond to specific event, specifically in response to DDL statements and SQL Trace events. The major difference between the triggers and event notifications is that the triggers are fired in synchronous mode to execute a specific code within the same session and transaction. Whereas, the event notification do not execute any code, it only sends information in asynchronous mode which then can be logged and acted upon later on. Here is a snippet from the Microsoft Documentation that explains the differences: Event Notifications vs. Triggers The following table compares and contrasts triggers and event notifications. Triggers Event Notifications DML triggers respond to data manipulation language (DML) events. DDL triggers respond to data definition language (DDL) events. Event noti...

I am in a situation where I have to incorporate SQL Server Agent alerts in my monitoring and alerting strategy. I needed a query (DMV) to get details on what alerts are setup on each server. And the result is the following query that I will run as a multi-server query. SELECT a.[id] [alert_id], a.[name] [alert_name], a.[enabled] [is_alert_enabled], o.[enabled] [is_operator_enabled], o.[email_address] [email_address], o.[pager_address] [pager_address], o.[netsend_address] [netsend_address], j.[name] [job_name], a.[event_source] [alert_event_source], a.[event_category_id] [alert_event_category_id], sc.[name] [alert_category_name], CASE sc.[category_class] WHEN 1 THEN 'JOB' WHEN 2 THEN 'ALERT' ...

While there are many ways and criteria to find out what database is the most used, under optimized, trouble maker etc....., here is one more to find out the database/s using the most buffer cache. SELECT CASE database_id WHEN 32767 THEN 'ResourceDb' ELSE db_name(database_id) END AS database_name, COUNT ( * ) AS cached_pages_count, COUNT ( * ) / 128 / 1024 AS cache_size_gb FROM sys.dm_os_buffer_descriptors GROUP BY DB_NAME(database_id) ,database_id ORDER BY cached_pages_count DESC ; Here is the results I got on one of the production SQL Servers.  Lucky for me, it turned out to be an extreme case. Now I knew where I should focus optimization efforts or even if that database belongs with rest of the databases on that same server. I have tested this query for SQL 2008 (SP3) and up. Hope you find this useful.