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 →

How to get SQL Server database object count using Powershell Measure-Object

Question: How can I iterate through a list of SQL Servers , extract the count of the databases , place into a powershell data table and get the database count sum for all the servers? I'm having some trouble thinking about how to add up the counts from the individual servers into a total count , using powershell functions. Answer: This basic powershell script will give you the basic framework. This script iterates through a list of SQL Servers - listed in the file referenced in the $instancepath. The results of the command executed are added to the DataTable .... Read more →

Powershell script for unattended install of SQL Server standard patches

Question: Do you have a powershell script allowing me to dynamically create a drive - pointing to a network share , and then start an unattended install of SQL Server patching? I'll afdapt the script to drop onto a server and execute according to schedule . Answer: Here is an example of powershell script that applies SQL Server 2016 SP2 CU7 , although you can apply the same framework for other patching . Some key points to note: 1) Replace "my_path" with the network path . The drive is added by use of the PS-Drive cmdlet. If you want to... Read more →

How to check the SQL sa Login is disabled

Often organisations have a SQL server security policy dictating the 'sa' login is disabled. There is some sound reasoning behind this policy. The primary reason is to decreases the surface area available to attack - and is a common principle of security. When this is a requirement - I'll add some code to disable 'sa' as part of the build and certification process. But , from time to time - the 'sa' login will be re enabled, so it's important to build an alerting or reporting mechanism identifying the exception. The sql statement to check if 'sa' is disabled. SELECT... Read more →