Vision: SQL Homeostasis

I should mention outright that this post applies to SQL Server version 2016 and up. Over the years I have relied on the backup history tables in msdb to check if backups are performed regularly and/or if they are falling behind the SLAs. Of course there are other, maybe better ways to monitor your backups too. But I don't take chances with database backups, not only for the DR purposes but also for the database availability reasons as well. If the transaction log back ups are getting skipped due to some oversight or failing for some reason, it may fill up the disks and lead to outage with not just that database but for other databases sharing the same disks. So I have a script that checks when was the last successful transaction log backup performed and if it exceeds a threshold, for example more than 24 hours, I get an automatic email alert. Things got little more interesting in high availability group clusters, SQL Server allows the log backup to be performed on any of the replic...

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.

This is a simple powershell script to query and display hardware and OS information from a remote computer. It uses CIM (Common Information Model) that is available since Powershell version 3 and is the recommended direction.  Please see the following article on why "we" should use CIM instead of the WMI. https://devblogs.microsoft.com/scripting/should-i-use-cim-or-wmi-with-windows-powershell/ # Specify the server name here $server = "server1" # pull all the information $hardware = Get-CimInstance -ClassName Win32_ComputerSystem -ComputerName $server $OS = Get-CimInstance -ClassName Win32_OperatingSystem -ComputerName $server $CPU = Get-CimInstance -ClassName Win32_Processor -ComputerName $server $PhysicalMemory = Get-CimInstance -ClassName CIM_PhysicalMemory -ComputerName $server $Bios = Get-CimInstance -ClassName Win32_BIOS -ComputerName $server $total_memory = ( $PhysicalMemory | measure-object...