How to uninstall a SQL Server 2016 Cumulative Update

Question: A server experienced a few problems after the installation of a SQL Server 2016 Cumulative Update. As part of the troubleshooting process, I'd like to rollback the cumulative update and rule out any issues related to the Cumulative update. How can I uninstall the cumulative update? Answer: Uninstalling the cumulative update in SQL Server 2016 is straightforward. I've copied an pasted these 3 steps straight from the Microsoft website In Control Panel, select View installed updates under Programs and Features. Locate the entry that corresponds to this cumulative update package under Microsoft SQL Server 2016. Press and hold (or... Read more →

Recovery is writing a checkpoint in database

Question: In daily scans of the SQL Server message logs there is an informational message Recovery is writing a checkpoint in database. What does this mean? Answer: There is normally an added text "No user action is required" - meaning the DBA doesn't need to take any action. But it's useful to understand the checkpoint process. The Checkpoint process writes sql dirty pages (in memory) and transaction logs to the physical disk. Checkpoints are useful as they assist in decreasing the recovery time in a system outage. The checkpoint process is regularly occuring . Different events can trigger a database... Read more →

How to check Enforce password expiration is set for SQL Login

Question: We had an unexpected application outage due to a SQL Login which had Enforce password expiration set . Unfortunately this was not being monitored, and led to a situation where the password had expired and the application was able to logon onto the database. It's the sql server team security policy to not have SQL Logins set with MUST_CHANGE ON. Part of enforcing and monitoring the policy includes incorporating certain checks in daily sql server security violations reports. What is the sql code to execute this check and to identify if any SQL Logins have Enforce Password Policy and... Read more →

How to fix Powershell Execution Timeout Expired with CommandTimeout

Question: I'm getting this Execution timeout error when executing a Powershell script using the $sql.ExecuteReader() method. How can I fix this issue? Exception calling "ExecuteReader" with "0" argument(s): "Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding." Answer: To overcome this issue - use the CommandTimeout parameter and set to a value of 0. This will make it infinite. So it would be something like : $cn = new-object System.Data.SqlClient.SqlConnection "server=myInstance;database=master;Integrated Security=sspi" $cn.Open() $cn.CreateCommand() $sql.CommandText $sql.CommandTimeout=0 $sql.CommandTimeout=0 $sql.ExecuteReader() Read More on Powerhsell and connecting to SQL Server Powershell - run... Read more →

Powershell script - Get the total table count of all databases in multiple SQL Server Instances

To get the total table count of all databases in either one or more SQL Server Instances use this powershell script. This basic powershell script will give you the basic framework - which you adapt for your own purposes. For example - you may want to store this in a report or capture and place in some sort of datawarehouse. This script - similar to How to get SQL Server database object count using Powershell Measure-Object - iterates through a list of SQL Servers - listed in the file referenced in the $instancepath. The results of the command executed are... Read more →