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)
4,027 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,242 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,182 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,248 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,200 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,997 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,891 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,288 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,567 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,215 reads
Blogs
SSMS Is Getting Smarter—and This Isn’t Just About Writing SQL
Continuing with my takeaways from SQLCon and FabCon Europe in Barcelona, let’s talk about...
Yippee-Kai-Yay 2027 Summit in Denver
By Steve Jones
Next year the PASS Data Community Summit is in DENVER! and London, but Denver...
PASS Data Community Summit 2026
By Steve Jones
Yesterday was the first day of sessions at the PASS Data Community Summit (after...
Forums
Pay for Performance
Comments posted to this topic are about the item Pay for Performance, which is...
BCA KCP Banjaran Adiwerna Hub/Wa. 0817-819-444
Jl. Raya Utara Adiwerna No.304, 306, Pejagan, Adiwerna, Kec. Adiwerna, Kabupaten Tegal, Jawa Tengah...
BCA KCP Ketanggungan Timur Hub/Wa. 0817-819-444
Jl. P. Diponegoro No.141, Dukuhtengah, Dukuh Tengah, Kec. Ketanggungan, Kabupaten Brebes, Jawa Tengah 52663
Question of the Day
Switching Schemas I
I run this query:
SELECT s.name AS schemaname , o.name AS objectname FROM sys.objects o INNER JOIN sys.schemas s ON s.schema_id = o.schema_id WHERE o.name = 'Beer'I get one row back for an object named dbo.beer. There is an ETL schema and both of these are nonpartitioned tables. I run this code:
ALTER TABLE dbo.beer switch TO etl.beerWhat happens? See possible answers