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...

You may have situations where the CPU % usage is well below the alert threshold but still queries are running slow because they are waiting for CPU to be available. This script creates an alert to send out email if the number of queries waiting for CPU exceeds the threshold.  Please update the value for the  @ operator  variable to whatever is the email operator you have setup in the SQL Server Agent. And since I am testing I am using the threshold value of 10. You may want to lower that after testing in your environment. Lastly, Since I did not want to get bombarded with emails, I am using the 900 seconds (15 minutes) delay of between alert emails. Please feel free to adjust it to your needs. USE [msdb] GO declare @ operator varchar ( 500 ) -- email operator name declare @ threshold int -- number of queries waiting for the CPU declare @ delay_between_email_alerts int -- this value is in seconds declare @ drop_alert_if_exists bit ...

Most of the times I use the CSV files whenever I need to import or export SQL Server data. And then if I need to do further analysis on the data or simply beautify the results to share with the users or colleagues, I simply open the CSV file in Excel and do the rest of the work manually. But what if I could just skip the CSV and export data directly to Excel format? Wouldn't that save me time and efforts and also help me to automate if I wanted to? No surprise that there is indeed a powershell module for Excel at the Powershell Gallary site. https://www.powershellgallery.com/packages/ImportExcel/5.2.0 You can import the module directly from there or do the manual download. I decided to use the import method. For that I would need to have the PSGallary as one of the registered repositories in the PowerShell If you don't already have registered the Powershell Gallary as one of the repository, there are couple methods depending on the PowerShell version you have. I have the 5.x ve...

If you are new at your job or a new client you would like to gather and review the sql server meta data to understand the environment and get up to speed as quickly as possible. Most of us are already familiar with the DMVs, system functions, procedures etc. to gather the SQL server metadata. And if you want to gather the information for all your SQL servers, you could run a multi-server query against all through the Central Management Server. In fact, in newer versions of SSMS you don't even need the CMS, you just register all your sql instances in the Local Server Groups. So from that perspective this post is not adding much values except maybe that it is another alternative to SSIS or other ETL tools to capture the meta data on regular basis. If nothing else I hope you find this educational regarding how to use powershell to interact with sql servers. <# Export SQL Server properties, settings and configuration values to CSV files #> # name of the sql server instance you wo...