Blog – My SQL Library

Availability Group, DBA

How To Remove Database From Availability Group In SQL Server

Step 1. Open SSMS Step 2. Connect to the Primary Instance of the SQL Server Step 3. Go to the Always On High Availability folder and expand it (click the + sign) Step 4. Expand Availability Groups Step 5. Expand…

Ernie
January 12, 2021

DBA, Monitoring

How To Monitor SQL Server Database Backup and Restore Progress Status

This is very useful when you want to monitor the progress of your Database backup or restore progress status especially on large databases where it normally takes a longer time to finish than usual. Use the script below and run…

Ernie
January 5, 2021

DBA, SQL

SSPI handshake failed with error code 0x80090304, (state 14) while establishing a connection with integrated security. The Local Security Authority cannot be contacted.

It started off when some (not all are affected) of our sql server instances notified us for Instance Offline state. I checked the instances and all looking good, when I checked the sql logs found this SSPI handshake failed issue…

Ernie
December 28, 2020

DBA, TempDB

How To Shrink TempDB Without Restarting SQL Server

–DBCC DROPCLEANBUFFERS/*Clears the clean buffers. This will flush cached indexes and data pages. You may want to run a CHECKPOINT command first, in order to flush everything to disk.*/CHECKPOINT;GODBCC DROPCLEANBUFFERS;GO –DBCC FREEPROCCACHE/*Clears the procedure cache, which may free up some…

Ernie
December 28, 2020

Availability Group, DBA, Uncategorized

How To Check The History Of Availability Group State Change?

This script is quite helpful when you want to know the change history of your Availability Group state. Excerpt from source Huge credit to this post.

Ernie
December 22, 2020

Availability Group, DBA

How To Configure Log Backup for Always ON Availability Group

When your backup starting to mess due to the replica’s priority backup is not configured properly or somehow the primary AG has failed over to secondary or vice versa and the backup priority is not pointing to the right replica,…

Ernie
December 22, 2020

Availability Group, DBA

How To Manual Failover Availability Group In SQL Server

In this issue, it shows an example of how to manual failover an AlwaysOn availability group in SQL Server. The scenario is when you need to do a force failover because your replicas are not synchronized with each other. Steps…