Stack multiple tables using UNION ALL
Avoid conversion errors when using UNION to stack rows from multiple tables.
2020-06-19 (first published: 2020-06-16)
3,991 reads
Scripts
How to register SPN for SQL service account
Check if the SPN is already registered: setspn -l domain\xxxxx If not, run below commands: setspn -A MSSQLSvc/abc.xx.companyname.com:1433 domain\xxxxx setspn -A MSSQLSvc/abc.xx.companyname.com domain\xxxxx setspn -A MSSQLSvc/abc:1433 domain\xxxxx setspn -A MSSQLSvc/ abc domain\xxxxx Verify again, setspn -l domain\xxxxx
2020-06-17 (first published: 2020-06-15)
8,197 reads
SQL Service account status with PowerShell
One of my client has the requirement to have SQL Service account running with domain account and should not be with local account. You may receive SSPI error after changing it to domain account. In order to fix that issue check my previous blog "How to register SPN for SQL service account" Output:
2020-06-17
1,162 reads
Disk space used by the SQL Server instance
I have found this query valuable. Sometimes you need to see just overall consumption without any detailed information. It is also useful in overall trend analysis and disk space planning.
2020-06-16 (first published: 2020-06-12)
2,238 reads
Monitor basic Database parameters like Recovery Model, Page Verify, Auto Close, Auto Shrink, DB Owner, Auto Create Statistics Enabled, DB Create Date etc with PowerShell command.
I have created this script for one of our client, the requirement was all the databases should be in full recovery model and should be monitored. Also, I added other important parameters like AutoShrink, AutoClose etc. Output:
2020-06-16
1,185 reads
Getting more info about who or what connect to your SQL Server
A couple of days ago I was playing with my small SQL Server enviroment (test) and auditting login events. I went ahead and created the audit for FAILED_LOGIN_GROUP and SUCCESSFULL_LOGIN_GROUP (let it run for a while). USE [master] GO CREATE SERVER AUDIT [Audit_LoginEvents] TO FILE ( FILEPATH = N'<PathToStoreData>' ,MAXSIZE = 100 MB ,MAX_FILES = […]
2020-06-03 (first published: 2020-05-27)
1,982 reads
SQL Server Database engine patching
This script can install Service pack, security patch and Cumulative update on SQL instance(Database Engine).
2020-05-26 (first published: 2020-05-19)
1,869 reads
Always on AG Dashboard report
This script can be used to generate Dashboard report for Always-on AG databases. Script can be placed in SQL Agent to generate report.
2020-05-22 (first published: 2020-05-12)
3,240 reads
Migrate and Upgrade SQL Instance (2014/2016) to 2019 version with 1 click
The script includes these steps: STEP 1: CREATE EMPTY Databases STEP 2 - CREATE Logins WITH SERVER ROLES\PERMISSIONS STEP 3 - COPY LINKED SERVERS STEP 4 - COPY SERVER OPTIONS STEP 5 - COPY CREDENTIALS STEP 6 - COPY AGENT JOBS STEP 7 - COPY DB Mail STEP 8 - COPY CERTIFICATES STEP 9 […]
2020-05-22 (first published: 2020-05-12)
2,548 reads
Migrate and Upgrade SQL Instance (2014/2016) to 2019 version with 1 click
The script includes these steps: STEP 1: CREATE EMPTY Databases STEP 2 - CREATE Logins WITH SERVER ROLESPERMISSIONS STEP 3 - COPY LINKED SERVERS STEP 4 - COPY SERVER OPTIONS STEP 5 - COPY CREDENTIALS STEP 6 - COPY AGENT JOBS STEP 7 - COPY DB Mail STEP 8 - COPY CERTIFICATES STEP 9 […]
2020-05-22 (first published: 2020-05-13)
1,189 reads
Blogs
The SUM of Nothing: #SQLNewBlogger
By Steve Jones
I caught this interesting item over on Pinal Dave’s blog: Eleven Interview Questions that...
How to Move the SSMS Status Bar to the Top and Color-Code SQL Server Connections
By Hemantgiri
I use color-coded connections in SSMS to distinguish Production, Pre-Production, UAT, and Development. But...
Forums
Split Large Queries in Athena with a Simple Modulo Trick
Comments posted to this topic are about the item Split Large Queries in Athena...
Finding Trailing Spaces
Comments posted to this topic are about the item Finding Trailing Spaces
Question of the Day
Finding Trailing Spaces
I have some data in a SQL Server 2025 database. It looks like this for the dbo.Customer table:
CustomerID CustomerName PreferredName 1 Steve Steve 2 Andy Andy 3 Brian Brian 4 Allan Allan 5 Devin Devin 6 Steve Steve 7 Sally SallyI want to detect which names have a single trailing space. The CustomerName is a varchar() and the PreferredName is a CHAR(). Does this query detect the problem rows?
SELECT
CustomerID,
CustomerName,
PreferredName
FROM Customer
WHERE CustomerName <> RTRIM(CustomerName)
OR PreferredName <> RTRIM(PreferredName); See possible answers