SQL Server Consulting, SQL Server Experts, SQL Server Migrations, SQL Server Performance, SQL Server High Availability, SQL Server Operations

info@techdevops.com | 437-991-3573 | Data Engineering Services

TechDevOps.com

Resources	Tools

Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance

< Back

October 21, 2024

Distributed Always On Group Scripts

by BF (Principal Consultant; Architecture; Engineering)

sys.availability_groups, sys.availability_replicas, sys.dm_hadr_availability_replica_states

Tags SQLServer

June 16, 2024

Automated Log Shipping Setup for Numerous User Databases

by BF (Principal Consultant; Architecture; Engineering)

Invoke-DbaDbLogShipping

Tags SQLServer

February 3, 2024

Microsoft SQL Server Fleet Backup Report Using PowerShell

by BF (Principal Consultant; Architecture; Engineering)

Invoke-Sqlcmd

Tags SQLServer

October 24, 2023

Get all Tables, Primary Keys & Column(s)

by BF (Principal Consultant; Architecture; Engineering)

sys.tables, sys.indexes, sys.index_columns, sys.columns

Tags SQLServer

October 5, 2023

Check Log Sequence Numbers match for your Log Backups

by BF (Principal Consultant; Architecture; Engineering)

sysdatabases, backupset, backupmediaset, backupmediafamily

Tags SQLServer

October 1, 2023

SQL Server OPTION (FAST N)

by BF (Principal Consultant; Architecture; Engineering)

Query Optimizer

Tags SQLServer

July 23, 2023

Microsoft SQL Server Database Files Space Report

by BF (Principal Consultant; Architecture; Engineering)

sys.database_files, sys.filegroups

Tags SQLServer

April 23, 2023

Designing a Microsoft SQL Server Always On High Availability Stacked w/ Distributed Availability Groups

by BF (Principal Consultant; Architecture; Engineering)

AOAG

Tags SQLServer

January 10, 2023

Get all Forced Plans from the Query Store

by BF (Principal Consultant; Architecture; Engineering)

sys.query_store_plan, sys.query_store_query, sys.query_store_query_text, sys.query_store_runtime_stats

Tags SQLServer

December 4, 2022

Get Disk Database Files Mount Points

by BF (Principal Consultant; Architecture; Engineering)

sys.dm_os_volume_stats

Tags SQLServer

November 13, 2022

Compare SQL Agent Job Runtime for a certain day of the week

by BF (Principal Consultant; Architecture; Engineering)

A Migration Performance Check Item

Tags SQLServer

October 8, 2022

High CPU Pressure

by BF (Principal Consultant; Architecture; Engineering)

Signal Wait Time

Tags SQLServer

September 12, 2022

Transactional Replication Distribution Delivery Status

by BF (Principal Consultant; Architecture; Engineering)

MSdistribution_status, MSarticles, MSreplication_monitordata

Tags SQLServer

July 6, 2022

Check TDE progress

by BF (Principal Consultant; Architecture; Engineering)

sys.dm_database_encryption_keys

Tags SQLServer

June 20, 2022

SQL Agent Jobs - Enable, Disable, Stop - Commands

by BF (Principal Consultant; Architecture; Engineering)

sysjobs, sysjobhistory, sysjobschedules, sysoperators

Tags SQLServer

May 20, 2022

Always On Synch Lag

by BF (Principal Consultant; Architecture; Engineering)

sys.dm_hadr_database_replica_states, sys.availability_replicas, sys.dm_hadr_availability_replica_states

Tags SQLServer

May 10, 2022

Get Table Information

by BF (Principal Consultant; Architecture; Engineering)

sys.tables, sys.columns, sys.types, sys.partitions

Tags SQLServer

May 8, 2022

Get CPU Usage Information

by BF (Principal Consultant; Architecture; Engineering)

sys.dm_os_ring_buffers, sys.dm_exec_requests, sys.dm_exec_query_stats

Tags SQLServer

March 10, 2022

Get The TEQ (Top Expensive Queries)

by BF (Principal Consultant; Architecture; Engineering)

sys.dm_exec_query_stats

Tags SQLServer

February 16, 2022

Statistics Objects Information

by BF (Principal Consultant; Architecture; Engineering)

Column Statistics and Index Statistics

Tags SQLServer

February 8, 2022

File Usage Statistics

by BF (Principal Consultant; Architecture; Engineering)

fn_virtualfilestats

Tags SQLServer

January 3, 2022

Index Usage Statistics

by BF (Principal Consultant; Architecture; Engineering)

sys.indexes, sys.dm_db_index_usage_stats, sys.dm_db_partition_stats

Tags SQLServer

October 11, 2021

SQL Server Automated Database Migration using Backup & Restore Method (v2)

by BF (Principal Consultant; Architecture; Engineering)

FULL, DIFFERENTIAL, LOG

Tags SQLServer

September 5, 2021

Microsoft SQL Server Always On Availability Groups Architecture Design

by BF (Principal Consultant; Architecture; Engineering)

High Availability & Disaster Recovery

Tags SQLServer

July 17, 2021

List all Databases on an Instance with user created Service Broker Queues

by BF (Principal Consultant; Architecture; Engineering)

Service Broker

Tags SQLServer

April 4, 2021

Get all Ports configured in Always On Availability Groups Environment

by BF (Principal Consultant; Architecture; Engineering)

Enter Short Description Here

Tags SQLServer

January 23, 2021

Always On Health Dashboard

by BF (Principal Consultant; Architecture; Engineering)

availability_replicas, dm_hadr_database_replica_cluster_states, dm_hadr_database_replica_states, dm_hadr_availability_replica_states

Tags SQLServer

November 28, 2020

Determine if your current UDFs are inlineable to benefit from SQL Server 2019 performance boost

by BF (Principal Consultant; Architecture; Engineering)

sys.sql_modules, sys.objects

Tags SQLServer

October 31, 2020

Automate Enable or Disable SQL Jobs in Always On Nodes

by BF (Principal Consultant; Architecture; Engineering)

sys.availability_groups_cluster, sys.dm_hadr_availability_replica_cluster_states, sys.dm_hadr_availability_replica_states, sys.availability_group_listeners, sysjobs

Tags SQLServer

September 1, 2020

Get number of Pending IO's

by BF (Principal Consultant; Architecture; Engineering)

sys.dm_io_pending_io_requests

Tags SQLServer

June 20, 2020

Windows Failover Cluster - Using Paxos Tag to monitor cluster configuration synchronization across the system

by BF (Principal Consultant; Architecture; Engineering)

WSFC, Always On Availability Groups

Tags SQLServer

May 12, 2020

Always On - Check if Primary Replica

by BF (Principal Consultant; Architecture; Engineering)

sys.availability_groups_cluster, sys.dm_hadr_availability_replica_cluster_states, sys.dm_hadr_availability_replica_states, sys.availability_group_listeners

Tags SQLServer

January 10, 2020

miniDBA.com - a great tool for Microsoft SQL Server Instance monitoring, performance analysis & much more

by BF (Principal Consultant; Architecture; Engineering)

A game changer in terms of features + price from the other tools in the marketplace.

Tags SQLServer

December 28, 2019

Windocks.com develops a new option for SQL Server Disaster Recovery using Database Image & Clones

by BF (Principal Consultant; Architecture; Engineering)

Automated refresh with Transaction Log backups and deliver current Production Databases for D.R. requirements.

Tags SQLServer

October 7, 2019

Powershell - Disk Space Alert

by BF (Principal Consultant; Architecture; Engineering)

Get-WmiObject

Tags SQLServer

August 26, 2019

Microsoft SQL Server - Database Compression Estimator (PAGE and ROW)

by BF (Principal Consultant; Architecture; Engineering)

sp_estimate_data_compression_savings

Tags SQLServer

July 14, 2019

Get SQL Agent Job Last Run Status

by BF (Principal Consultant; Architecture; Engineering)

msdb.dbo.sysjobservers, msdb.dbo.sysjobs

Tags SQLServer

May 24, 2019

Get SQL Server Modules loaded

by BF (Principal Consultant; Architecture; Engineering)

SQL Server VAS (Virtual Address Space)

Tags SQLServer

May 3, 2019

View SQL Job Last Status in Microsoft System Center

by BF (Principal Consultant; Architecture; Engineering)

OperationsManager

Tags SQLServer

April 16, 2019

Powershell - Log CPU - per Process - to a Table & Output File

by BF (Principal Consultant; Architecture; Engineering)

Get-Counter, Invoke-sqlcmd

Tags SQLServer

April 2, 2019

Azure Powershell - Delete all files in Storage Files Share

by BF (Principal Consultant; Architecture; Engineering)

Remove-AzureStorageFile

Tags SQLServer

February 28, 2019

Server-Level Trigger to notify on any Drop Database

by BF (Principal Consultant; Architecture; Engineering)

ALTER TRIGGER [DropDatabaseNotifier] ON ALL SERVER FOR DROP_DATABASE

Tags SQLServer

February 15, 2019

SQL Server Transactional Replication - Get all Publication Article Columns, Get all Publication Article Filtered Rows

by BF (Principal Consultant; Architecture; Engineering)

Run in each Published Database

Tags SQLServer

January 30, 2019

SQL Jobs Monitor

by BF (Principal Consultant; Architecture; Engineering)

Receive alerts on any SQL Jobs Failed

Tags SQLServer

January 21, 2019

Log Shipping Real-Time Processing Engine v2

by BF (Principal Consultant; Architecture; Engineering)

msdb.dbo.restorehistory, msdb.dbo.backupset, msdb.dbo.backupmediaset, msdb.dbo.backupmediafamily

Tags SQLServer

January 15, 2019

T-SQL - Using Bitwise Operators to store multiple values in one column

by BF (Principal Consultant; Architecture; Engineering)

Bitwise operators perform bit manipulations between two expressions of any of the data types of the integer data type category...

Tags SQLServer

January 10, 2019

Restore a SQL Server Database Faster

by BF (Principal Consultant; Architecture; Engineering)

MAXTRANSFERSIZE, BUFFERCOUNT

Tags SQLServer

December 18, 2018

T-SQL - Determine the last DBCC CHECKDB date

by BF (Principal Consultant; Architecture; Engineering)

DBCC DBINFO

Tags SQLServer

December 6, 2018

T-SQL Monitor TempDB Usage

by BF (Principal Consultant; Architecture; Engineering)

sys.database_files, sys.dm_os_performance_counters, sys.dm_db_file_space_usage

Tags SQLServer

December 4, 2018

Extracting Metrics from MSlogreader_history - "Approximately 45000000 log records have been scanned..."

by BF (Principal Consultant; Architecture; Engineering)

Distribution.dbo.MSlogreader_history, Distribution.dbo.MSlogreader_agents

Tags SQLServer

December 3, 2018

T-SQL - Dynamically Re-Size TempDB Files

by BF (Principal Consultant; Architecture; Engineering)

Alter Database tempdb Modify File

Tags SQLServer

December 3, 2018

Distribution Database in Recovery Pending State

by BF (Principal Consultant; Architecture; Engineering)

Alter Database Modify File

Tags SQLServer

November 23, 2018

Use Extended Events to monitor SQL Server Transactional Replication

by BF (Principal Consultant; Architecture; Engineering)

ring_buffer

Tags SQLServer

November 23, 2018

SQL Server Reporting Services - Report Usage Stats

by BF (Principal Consultant; Architecture; Engineering)

ExecutionLog2

Tags SQLServer

November 23, 2018

T-SQL List Permissions for an Object

by BF (Principal Consultant; Architecture; Engineering)

sys.objects, sys.database_permissions, sys.database_principals

Tags SQLServer

November 22, 2018

SQL Server Reporting Services - File Share Account

by BF (Principal Consultant; Architecture; Engineering)

Reporting Services Configuration Manager

Tags SQLServer

November 20, 2018

Migrating a Giant to the Cloud

by BF (Principal Consultant; Architecture; Engineering)

Microsoft Azure Cloud Computing

Tags SQLServer

November 18, 2018

T-SQL - Get all Distribution Agents, UndelivCmdsInDistDB & DelivCmdsInDistDB

by BF (Principal Consultant; Architecture; Engineering)

MSdistribution_agents, MSdistribution_status, MSARTICLES, SYS.Servers, MSagent_profiles

Tags SQLServer

November 18, 2018

T-SQL - Get All Publications & Articles

by BF (Principal Consultant; Architecture; Engineering)

MSPUBLICATIONS, MSARTICLES

Tags SQLServer

November 2, 2018

Log Shipping Real-Time Processing Engine

by BF (Principal Consultant; Architecture; Engineering)

msdb.dbo.restorehistory, msdb.dbo.backupset, msdb.dbo.backupmediaset, msdb.dbo.backupmediafamily

Tags SQLServer

November 1, 2018

T-SQL Delete Extra TempDB Data Files

by BF (Principal Consultant; Architecture; Engineering)

Alter Database Remove File

Tags SQLServer

October 31, 2018

T-SQL - Get Transaction Log Restore History on the Secondary Database Server for any Log Shipped Database(s)

by BF (Principal Consultant; Architecture; Engineering)

msdb.dbo.backupset, msdb.dbo.backupmediaset, msdb.dbo.backupmediafamily

Tags SQLServer

October 27, 2018

Use Powershell to monitor S2D(Storage Spaces Direct) Performance

by BF (Principal Consultant; Architecture; Engineering)

Get-StorageSubSystem, Get-StorageHealthReport

Tags SQLServer

October 19, 2018

Use Azure Function with Timer Trigger to call an Azure SQL Database Stored Procedure

by BF (Principal Consultant; Architecture; Engineering)

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud...

Tags SQLServer

October 17, 2018

Cycle Error Logs

by BF (Principal Consultant; Architecture; Engineering)

sp_cycle_errorlog, sp_cycle_agent_errorlog

Tags SQLServer

October 12, 2018

T-SQL - Get Unused Indexes

by BF (Principal Consultant; Architecture; Engineering)

sys.dm_db_index_usage_stats

Tags SQLServer

October 6, 2018

T-SQL - Backup All Databases in an Instance and Return Backup History

by BF (Principal Consultant; Architecture; Engineering)

Enter Short Description Here

Tags SQLServer

September 26, 2018

T-SQL - Get Currently Running Jobs

by BF (Principal Consultant; Architecture; Engineering)

msdb.dbo.sysjobs, msdb.dbo.sysjobactivity

Tags SQLServer

September 15, 2018

T-SQL Compare sys.database_files with sys.master_files

by BF (Principal Consultant; Architecture; Engineering)

Output any differences with System Catalog Views

Tags SQLServer

September 4, 2018

Query Cache Analysis

by BF (Principal Consultant; Architecture; Engineering)

SQL_Handle, Plan_Handle, Query_Hash, Query_Plan_Hash, Query Cache Statistics, Plan Attributes

Tags SQLServer

August 22, 2018

T-SQL - Check Always On Transactional Replication re-directed Publisher to Listener Name

by BF (Principal Consultant; Architecture; Engineering)

dbo.sp_validate_redirected_publisher, dbo.sp_Validate_Replica_Hosts_As_Publishers, dbo.MSRedirected_Publishers

Tags SQLServer

August 14, 2018

T-SQL Encryption Status of a database

by BF (Principal Consultant; Architecture; Engineering)

sys.dm_database_encryption_keys (Transact-SQL)

Tags SQLServer

July 5, 2018

File share witness resource failed to arbitrate for the file share. Please ensure that file share exists and is accessible by the cluster.

by BF (Principal Consultant; Architecture; Engineering)

Logon failed for user SQLCluster01

Tags SQLServer

June 21, 2018

Use Powershell to Stop & Start SQL Server Service

by BF (Principal Consultant; Architecture; Engineering)

Get-Service, Stop-Service, Start-Service

Tags SQLServer

June 19, 2018

T-SQL Check if a Table has a ColumnStore Index

by BF (Principal Consultant; Architecture; Engineering)

sys.indexes

Tags SQLServer

June 17, 2018

Fing Queries using Parallelism

by BF (Principal Consultant; Architecture; Engineering)

sys.dm_exec_query_stats, sys.dm_exec_sql_text, sys.dm_exec_cached_plans, sys.dm_exec_query_plan, sys.dm_os_tasks, sys.dm_os_workers, sys.dm_os_waiting_tasks

Tags SQLServer

June 13, 2018

Find the root cause of a TEMPDB LOG growth issue

by BF (Principal Consultant; Architecture; Engineering)

Extended Events, DBCC OPENTRAN, INPUTBUFFER & SYSPROCESSES

Tags SQLServer

June 6, 2018

Use SQLCMD Mode to connect to numerous SQL Instances & loop through all databases & execute query

by BF (Principal Consultant; Architecture; Engineering)

SSMS..SQLCMD Mode

Tags SQLServer

June 5, 2018

Get Azure API Versions for Web and Azure SQL Database

by BF (Principal Consultant; Architecture; Engineering)

Get-AzureRmResourceProvider

Tags SQLServer

June 4, 2018

Execute SSRS Report Subscriptions manually / on-demand

by BF (Principal Consultant; Architecture; Engineering)

EXEC ReportServer.dbo.AddEvent @EventType='TimedSubscription',@EventData=''

Tags SQLServer

May 22, 2018

T-SQL - Log Linked Servers

by BF (Principal Consultant; Architecture; Engineering)

[MASTER].sys.Servers, [MASTER].sys.linked_logins, [MASTER].sys.server_principals

Tags SQLServer

May 20, 2018

SQL Server Audit

by BF (Principal Consultant; Architecture; Engineering)

Create Server Audit, Create Server Audit Specification

Tags SQLServer

May 18, 2018

Automate DBCC UpdateUsage per select list of databases

by BF (Principal Consultant; Architecture; Engineering)

DBCC UPDATEUSAGE

Tags SQLServer

May 18, 2018

Automate DBCC UpdateStats per select list of databases

by BF (Principal Consultant; Architecture; Engineering)

Exec sp_updatestats

Tags SQLServer

May 14, 2018

T-SQL - List Databases, Logins & SQL Jobs

by BF (Principal Consultant; Architecture; Engineering)

master.sys.databases, master.sys.server_principals, msdb.dbo.sysjobs

Tags SQLServer

May 6, 2018

T-SQL Generate Logins for a specific list of databases for Migration purposes

by BF (Principal Consultant; Architecture; Engineering)

Use a Driver table for Database List

Tags SQLServer

April 29, 2018

Running SQL Server Post-Installation Checks

by BF (Principal Consultant; Architecture; Engineering)

Builds

Tags SQLServer

April 28, 2018

Automate SQL Server Database Data & Log File Growth Sizes

by BF (Principal Consultant; Architecture; Engineering)

sys.master_files, sys.databases, sysaltfiles

Tags SQLServer

April 28, 2018

Check Allocation Unit Size for Disks in a SQL Server System

by BF (Principal Consultant; Architecture; Engineering)

DISKPART

Tags SQLServer

April 28, 2018

SQL Server Automated Database Migration using Backup & Restore Method (v1)

by BF (Principal Consultant; Architecture; Engineering)

FULL, DIFFERENTIAL, LOG

Tags SQLServer

April 21, 2018

Monitor real-time query progress using sys.dm_exec_query_profiles DMV

by BF (Principal Consultant; Architecture; Engineering)

Use this DMV to determine which part of the query is running slow.

Tags SQLServer

April 16, 2018

Find the Authentication scheme(SQL Server or Windows Authentication) used with all connections.

by BF (Principal Consultant; Architecture; Engineering)

sys.dm_exec_connections

Tags SQLServer

April 13, 2018

Automate XCOPY of Database Data & Log Files

by BF (Principal Consultant; Architecture; Engineering)

Useful for SQL Server Database Migrations that use DETACH & ATTACH process

Tags SQLServer

April 10, 2018

The term 'New-AzureStorageContext' is not recognized as the name of a cmdlet, function, script file, or operable program.

by BF (Principal Consultant; Architecture; Engineering)

SQLPS.exe, New-AzureStorageContext, Get-AzureStorageBlob, Remove-AzureStorageBlob

Tags SQLServer

March 23, 2018

Using Microsoft Data Migration Assistant and Extended Events to capture any Deprecated Features

by BF (Principal Consultant; Architecture; Engineering)

sqlserver.deprecation_announcement, sqlserver.deprecation_final_support

Tags SQLServer

March 22, 2018

Using Extended Events to capture failed logins

by BF (Principal Consultant; Architecture; Engineering)

Enter Short Description Here

Tags SQLServer

March 10, 2018

Using Extended Events to capture Stored Procedure calls, Log & Extract Duration Buckets of % and/or #

by BF (Principal Consultant; Architecture; Engineering)

CREATE EVENT SESSION, ADD EVENT, ADD TARGET

Tags SQLServer

March 7, 2018

Find out which Connections are using the highest network bandwidth from SQL Server

by BF (Principal Consultant; Architecture; Engineering)

DMVs

Tags SQLServer

March 3, 2018

Automated Report for any Database Restores occurring on an SQL Server Instance

by BF (Principal Consultant; Architecture; Engineering)

HTML Report, Database Mail

Tags SQLServer

February 24, 2018

Using PowerShell to spin up Windock's Docker Containers running SQL Server with Cloned Databases

by BF (Principal Consultant; Architecture; Engineering)

Clone Terabyte data in seconds

Tags SQLServer

February 13, 2018

Extract Blocking Chains from your SQL Servers

by BF (Principal Consultant; Architecture; Engineering)

Find the Head / Lead Blockers to eliminate/lower your rate of Blocking

Tags SQLServer

February 11, 2018

Hitting the 16 Terabytes Data File Limit

by BF (Principal Consultant; Architecture; Engineering)

Maximum Capacity Specifications for SQL Server

Tags SQLServer

February 2, 2018

Always On: The availability replica manager is going offline because the local Windows Server Failover Clustering (WSFC) node has lost quorum.

by BF (Principal Consultant; Architecture; Engineering)

SQL Server Error Log, Failover Cluster Cluster Events, Cluster.log

Tags SQLServer

January 28, 2018

Using Windock's Docker SQL Containers to deliver multiple SQL Server Environment(s) in seconds on a single machine

by BF (Principal Consultant; Architecture; Engineering)

Automation, Continuous Integration

Tags SQLServer

January 23, 2018

Using Profiler to view SQL:StmtRecompiles and the EventSubClass to see the reason for a recompile (w/ Stored Procedure using Dynamic SQL)

by BF (Principal Consultant; Architecture; Engineering)

sys.trace_events, sys.trace_subclass_values

Tags SQLServer

January 22, 2018

The request limit for the database is 400 and has been reached

by BF (Principal Consultant; Architecture; Engineering)

Azure SQL Database resource limits

Tags SQLServer

January 19, 2018

Powershell Functions to check if TLS 1.2 updates are required

by BF (Principal Consultant; Architecture; Engineering)

Check if SQL Server Native Client can support TLS 1.2, Check if Microsoft ODBC Driver for SQL Server can support TLS 1.2

Tags SQLServer

January 9, 2018

SQL Always On Monitor

by BF (Principal Consultant; Architecture; Engineering)

Captures, Logs, Alerts & Reports for Microsoft SQL Server Always On Availability Groups Metrics.

Tags SQLServer

December 18, 2017

Automate Database Attach or Detach of all User Databases for a Migration

by BF (Principal Consultant; Architecture; Engineering)

sp_detach_db, sp_attach_db

Tags SQLServer

December 12, 2017

Find Long Running Queries in SQL Server, Queries with High Execution Counts, Query Cache Analysis

by BF (Principal Consultant; Architecture; Engineering)

Max Worker Time, Max Elapsed Time, Execution Counts, Last Elapsed Time

Tags SQLServer

December 12, 2017

Reporting Services 2016 Subscriptions Error - "An error has occurred. Something went wrong. Please try again later."

by BF (Principal Consultant; Architecture; Engineering)

No Subscriptions Available

Tags SQLServer

December 12, 2017

Replacement of Remote Transactional Replication Distributor in an Always On Setup

by BF (Principal Consultant; Architecture; Engineering)

Enter Short Description Here

Tags SQLServer

December 11, 2017

Huge Transaction Log File with Always-ON Availability Group Database in SQL Server 2016 STD

by BF (Principal Consultant; Architecture; Engineering)

log_reuse_wait_desc, DBCC sqlperf(logspace), DBCC LogInfo

Tags SQLServer

December 9, 2017

"The specified network name is no longer available" error while connecting On Premise Applications to Azure Always On Listener.

by BF (Principal Consultant; Architecture; Engineering)

MTU Setting

Tags SQLServer

December 3, 2017

DatabaseMail - Queuing

by BF (Principal Consultant; Architecture; Engineering)

sysmail_help_queue_sp, msdb.dbo.sysmail_allitems

Tags SQLServer

December 2, 2017

Troubleshooting Azure VM SQL Server Database Mail using SmtpClient class, PowerShell and SendGrid

by BF (Principal Consultant; Architecture; Engineering)

System.Net.Mail.MailAddress, System.net.mail.smtpclient, System.Net.NetworkCredential

Tags SQLServer

November 30, 2017

Automate DBCC CHECKDB Per Instance

by BF (Principal Consultant; Architecture; Engineering)

Execute against each Database in the Instance + Log Results + Check for any errors + RaiseError(Alert)

Tags SQLServer

November 29, 2017

SQL Server Transactional Replication Error: Count not find stored procedure sp_MSdel_TableName

by BF (Principal Consultant; Architecture; Engineering)

sp_scriptpublicationcustomprocs (Transact-SQL)

Tags SQLServer

November 29, 2017

SQL Server 2014 Service Pack 2 (SP2) installation failed

by BF (Principal Consultant; Architecture; Engineering)

Exit Code: -2061893565

Tags SQLServer

November 24, 2017

Troubleshooting SSIS Package Execution using dtexec Utility (SSIS Tool)

by BF (Principal Consultant; Architecture; Engineering)

SQL Server Execute Package Utility

Tags SQLServer

November 23, 2017

Replication Monitor - Agent Profile - HistroryVerboseLevel

by BF (Principal Consultant; Architecture; Engineering)

HistoryVerboseLevel 2

Tags SQLServer

November 18, 2017

Enable or Disable All SQL Jobs

by BF (Principal Consultant; Architecture; Engineering)

MSDB..sysjobs

Tags SQLServer

November 17, 2017

Monitor the progress of an Azure SQL Database Tier Scaling Operation

by BF (Principal Consultant; Architecture; Engineering)

sys.dm_operation_status - Returns information about operations performed on databases in a Azure SQL Database server.

Tags SQLServer

November 9, 2017

T-SQL - Query all SQL Server Error Log Files

by BF (Principal Consultant; Architecture; Engineering)

xp_enumerrorlogs

Tags SQLServer

November 8, 2017

T-SQL - Check Statistics Last Updated and Update All Statistics

by BF (Principal Consultant; Architecture; Engineering)

sys.stats, sys.stats_columns, sys.columns, sys.partitions, sys.objects

Tags SQLServer

November 8, 2017

T-SQL - Find Missing Indexes - incl. Improvement Measure and Avg User Impact

by BF (Principal Consultant; Architecture; Engineering)

sys.dm_db_missing_index_group, sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_details

Tags SQLServer

October 4, 2017

Automate Azure SQL Database (As-A-Service) Copy From a Logical Server to another Logical Server

by BF (Principal Consultant; Architecture; Engineering)

Import-AzureRmContext, Get-AzureRMResource, New-azurermsqldatabasecopy

Tags SQLServer

September 26, 2017

T-SQL Index & Heap Space plus Rolled Up Table Space

by BF (Principal Consultant; Architecture; Engineering)

sys.tables, sys.schemas, sys.indexes, sys.partition, sys.allocation_units

Tags SQLServer

September 9, 2017

SQL Replication Monitor

by BF (Principal Consultant; Architecture; Engineering)

Captures, Logs, Alerts & Reports for Microsoft SQL Server Transaction Replication Distribution Metrics.

Tags SQLServer

August 30, 2017

Get Number of Microsoft Systems Center (SCOM) Events Per ComputerName

by BF (Principal Consultant; Architecture; Engineering)

EventallView

Tags SQLServer

August 29, 2017

Speeding up SQL Server Database Restores

by BF (Principal Consultant; Architecture; Engineering)

Multi-Threaded Approach, Database Instant File Initialization, Azure VM SSDs

Tags SQLServer

August 28, 2017

List SQL Server Non-Default Configuration Options

by BF (Principal Consultant; Architecture; Engineering)

Server Dashboard Report

Tags SQLServer

August 25, 2017

T-SQL Get SQL Server Transaction Log Backup Size (or Database Backup Size)

by BF (Principal Consultant; Architecture; Engineering)

MSDB..backupset

Tags SQLServer

August 24, 2017

Automate Azure SQL Database Backup to Azure Virtual Machine running SQL Server

by BF (Principal Consultant; Architecture; Engineering)

SqlPackage.exe - /export, /import

Tags SQLServer

August 22, 2017

Transaction Log Large File Size

by BF (Principal Consultant; Architecture; Engineering)

800+GB Transaction Log File

Tags SQLServer

August 18, 2017

SQL Server Database Transaction Log Backups - Activity-based (vs Time-based)

by BF (Principal Consultant; Architecture; Engineering)

sys.dm_db_log_stats

Tags SQLServer

August 18, 2017

T-SQL - Get Stored Procedure Created and Modified Dates

by BF (Principal Consultant; Architecture; Engineering)

sys.objects, INFORMATION_SCHEMA.ROUTINES

Tags SQLServer

August 17, 2017

Get Number of Microsoft Systems Center (SCOM) Monitor's State changes

by BF (Principal Consultant; Architecture; Engineering)

StateChangeEvent, State, MonitorView, ManagedType

Tags SQLServer

July 31, 2017

Create a Microsoft SQL Server Audit Report using Powershell

by BF (Principal Consultant; Architecture; Engineering)

Microsoft.SqlServer.SMO, HTML Report, Email Delivery

Tags SQLServer

July 30, 2017

T-SQL - Get Logins Connect Permissions

by BF (Principal Consultant; Architecture; Engineering)

sys.server_principals, sys.server_permissions, sys.sql_logins

Tags SQLServer

July 29, 2017

T-SQL - Get SQL Server Up-Time - Services, SysProcesses & TempDB

by BF (Principal Consultant; Architecture; Engineering)

sys.dm_server_services, sysprocesses, sys.databases

Tags SQLServer

July 26, 2017

T-SQL - Get list of Indexes with Fillfactor less than 90 (incl: #Pages, #Rows, % Fillfactor, % Fragmentation)

by BF (Principal Consultant; Architecture; Engineering)

sys.dm_db_index_physical_stats, sys.indexes

Tags SQLServer

July 24, 2017

T-SQL - Get Tables, Fields & Data Types

by BF (Principal Consultant; Architecture; Engineering)

sys.columns, sys.objects, sys.types

Tags SQLServer

July 23, 2017

T-SQL - sp_clean_db_free_space

by BF (Principal Consultant; Architecture; Engineering)

Removes residual information left on database pages because of data modification routines in SQL Server.

Tags SQLServer

July 21, 2017

T-SQL Get Database Backups Throughput

by BF (Principal Consultant; Architecture; Engineering)

msdb.dbo.backupset, msdb.dbo.backupmediafamily

Tags SQLServer

July 19, 2017

T-SQL Display Operating System Version Information

by BF (Principal Consultant; Architecture; Engineering)

sys.dm_os_windows_info vs sys.dm_os_host_info

Tags SQLServer

July 12, 2017

SQL Space Monitor - Version 2

by BF (Principal Consultant; Architecture; Engineering)

sys.database, sys.master_files, sys.data_spaces, sys.dm_os_volume_stats, sp_send_dbmail

Tags SQLServer

July 8, 2017

SQL Space Monitor - Version 1

by BF (Principal Consultant; Architecture; Engineering)

sys.database, sys.master_files, sys.data_spaces, sys.dm_os_volume_stats

Tags SQLServer

July 5, 2017

Agent is retrying after an error. 65 retries attempted. See agent job history in the Jobs folder for more details.

by BF (Principal Consultant; Architecture; Engineering)

msdb.dbo.sysjobhistory

Tags SQLServer

June 30, 2017

High Memory Pressure

by BF (Principal Consultant; Architecture; Engineering)

sys.objects, sys.partitions, sys.allocation_units, sys.indexes, sys.dm_os_buffer_descriptors

Tags SQLServer

June 28, 2017

Auditing SQL Server Systems - Logins

by BF (Principal Consultant; Architecture; Engineering)

sp_readerrorlog, sp_helptext

Tags SQLServer

June 25, 2017

SQL Server 2017 - sys.dm_db_log_info

by BF (Principal Consultant; Architecture; Engineering)

A New DMF used to examine the structure of Transaction Log files and it is a replacement for DBCC LOGINFO. It returns VLF information of the Transaction Log Files.

Tags SQLServer

June 24, 2017

SQL Server 2017 - sys.dm_os_enumerate_fixed_drives

by BF (Principal Consultant; Architecture; Engineering)

A New DMV to identify free disk space. It is a replacement for XP_FIXEDDRIVES.

Tags SQLServer

June 23, 2017

SQL Server 2017 - sys.dm_os_file_exists

by BF (Principal Consultant; Architecture; Engineering)

A New inline TVF to check if a file exists or not. It is a replacement for XP_FILEEXIST.

Tags SQLServer

June 20, 2017

SQL Server 2017 - sys.query_store_wait_stats

by BF (Principal Consultant; Architecture; Engineering)

New in SQL Server 2017, it contains information about the Wait Stats for Queries in the Query Store. Query Store is a great feature to help with performances issues related to Plan changes.

Tags SQLServer

June 19, 2017

sp_configure - RECONFIGURE or RECONFIGURE WITH OVERRIDE

by BF (Principal Consultant; Architecture; Engineering)

SQL Server Configuration options are available to manage via SQL Server Management Studio Application (SSMS) or sp_configure stored procedure...

Tags SQLServer

June 18, 2017

SQL Server PBM - Policy, Condition, Facet

by BF (Principal Consultant; Architecture; Engineering)

A facet is a collection of properties that describe some functionality of SQL Server related to an area of Management. For example, some common facets are Database, Login & Server Security.

Tags SQLServer

June 18, 2017

SQL Server Management Studio - Web Browser

by BF (Principal Consultant; Architecture; Engineering)

Create an New Vertical Tab Group with Query Editor & Web Browser...

Tags SQLServer

June 14, 2017

T-SQL Get Permissions for a Role

by BF (Principal Consultant; Architecture; Engineering)

sys.database_principals, sys.database_permissions, sys.schemas, sys.objects

Tags SQLServer

June 7, 2017

sp_server_diagnostics

by BF (Principal Consultant; Architecture; Engineering)

Captures diagnostic data and health information about SQL Server to detect potential failures. The procedure runs in repeat mode and sends results periodically.

Tags SQLServer

June 7, 2017

Transactional Replication Monitoring - SQL Job Perspective - Last Run Time and Run Status

by BF (Principal Consultant; Architecture; Engineering)

This query returns all Replication SQL Jobs and reports on it's Last Run Time and Run Status...

Tags SQLServer

June 5, 2017

Capture Microsoft SQL Server Waits Stats

by BF (Principal Consultant; Architecture; Engineering)

sys.dm_os_wait_stats - Returns information about all the waits encountered by threads that executed. Use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches.

Tags SQLServer

June 3, 2017

T-SQL List Database or Log Backups

by BF (Principal Consultant; Architecture; Engineering)

Backupset - Contains a row for each backup set. A backup set contains the backup from a single, successful backup operation. Backupmediafamily - Contains one row for each media family.

Tags SQLServer

June 2, 2017

Use AzCopy to copy SQL Server Backup Files to Azure Blob Storage

by BF (Principal Consultant; Architecture; Engineering)

Within our Microsoft Cloud Environment we use a 3rd party product from Red-Gate - SQL Backup - to perform SQL Server backups for our both our System...

Tags SQLServer

May 31, 2017

How to Monitor Log Shipping in SQL Server

by BF (Principal Consultant; Architecture; Engineering)

Log shipping allows you to automatically send transaction log backups from a PRI DB on a PRI Server to one or more SEC DB(s) on separate SEC Server(s)...

Tags SQLServer

May 29, 2017

T-SQL Getdate as Integer

by BF (Principal Consultant; Architecture; Engineering)

With Time, With Uniqueidentifier

Tags SQLServer

April 11, 2017

T-SQL - Constants vs Parameters vs Local Variables - Execution Plans, Statistics, Assumptions

by BF (Principal Consultant; Architecture; Engineering)

Procedure Cache, Parameter Sniffing, SSMS Compare Execution Plans Tool, SSMS Live Execution Plan Tool

Tags SQLServer

March 30, 2017

SQL Server - File IO - Sizes, Usage, Stats - Write(Insert) vs Read(Select)

by BF (Principal Consultant; Architecture; Engineering)

SSMS, Process Monitor

Tags SQLServer

March 29, 2017

SQL Server Database Mail - Success, Failure, Logs, Config

by BF (Principal Consultant; Architecture; Engineering)

msdb.dbo.sysmail_sentitems, msdb.dbo.sysmail_faileditems, msdb.dbo.sysmail_event_log, msdb.dbo.sysmail_configuration

Tags SQLServer

March 16, 2017

Microsoft SQL Server Replication - Distribution - CmdsPerSec Delivery Rate Dropped

by BF (Principal Consultant; Architecture; Engineering)

Distribution.dbo.MSdistribution_history

Tags SQLServer

February 27, 2017

SQL Server High Availability - Monitor AlwaysON Availability Groups using Powershell, DMVs and TSQL

by BF (Principal Consultant; Architecture; Engineering)

Enter Short Description Here

Tags SQLServer

February 21, 2017

Use Microsoft Message Analyzer to capture TDS Packets

by BF (Principal Consultant; Architecture; Engineering)

The Tabular Data Stream (TDS) protocol is an application layer request/response protocol that...

Tags SQLServer

February 2, 2017

T-SQL Transform Row Data into Columns using PIVOT

by BF (Principal Consultant; Architecture; Engineering)

Example - Transactional Replication Pending Commands

Tags SQLServer

January 27, 2017

List all SQL Server Reporting Services Subscriptions

by BF (Principal Consultant; Architecture; Engineering)

SQL Server Reporting Services(SSRS) is a Microsoft solution that provides customers with the ability to create, publish and manage reports...

Tags SQLServer

January 27, 2017

SQL Server Get Latest Database or Log Backup History

by BF (Principal Consultant; Architecture; Engineering)

DB, Log, Diff

Tags SQLServer

January 18, 2017

Calculate RTO(Recovery Time Objective) in AlwaysON Availability Groups

by BF (Principal Consultant; Architecture; Engineering)

sys.dm_hadr_availability_replica_cluster_nodes, sys.dm_hadr_availability_replica_cluster_states, sys.dm_hadr_availability_replica_states, sys.dm_hadr_database_replica_states, sys.availability_replicas

Tags SQLServer

January 15, 2017

SQL Server on Linux

by BF (Principal Consultant; Architecture; Engineering)

Microsoft Azure, Linux VM, SQL Server vNext

Tags SQLServer

January 12, 2017

T-SQL Get Maximum Value from a list of Columns Values

by BF (Principal Consultant; Architecture; Engineering)

VALUES Clause...

Tags SQLServer

January 3, 2017

DBCC CLONEDATABASE

by BF (Principal Consultant; Architecture; Engineering)

Generates a Schema and Statistics only copy...

Tags SQLServer

January 1, 2017

SQL Server 2016 Compress() & Decompress()

by BF (Principal Consultant; Architecture; Engineering)

Built-in T-SQL functions in SQL Server 2016

Tags SQLServer

December 30, 2016

JSON in SQL Server 2016

by BF (Principal Consultant; Architecture; Engineering)

JSON_VALUE, JSON_QUERY, JSON_MODIFY, OPENJSON, FOR JSON

Tags SQLServer

December 27, 2016

Temporal Tables in SQL Server 2016

by BF (Principal Consultant; Architecture; Engineering)

A new type of user table in SQL Server 2016

Tags SQLServer

December 17, 2016

T-SQL Merge

by BF (Principal Consultant; Architecture; Engineering)

Inserting, Updating, and Deleting Data by Using MERGE

Tags SQLServer

December 17, 2016

T-SQL Pivot Data

by BF (Principal Consultant; Architecture; Engineering)

Pivoting the data based on a column values

Tags SQLServer

December 15, 2016

The User Data directory in the registry is not valid. Error code: 0x851A0043

by BF (Principal Consultant; Architecture; Engineering)

SQL Server Service Pack(s), Registry Key

Tags SQLServer

December 13, 2016

Reseed Identity Column in a SQL Server Table

by BF (Principal Consultant; Architecture; Engineering)

IDENTITY, DBCC CHECKIDENT

Tags SQLServer

December 13, 2016

SQL Server Schema Change History

by BF (Principal Consultant; Architecture; Engineering)

sys.traces, fn_trace_gettable, fn_dblog, SSMS Report

Tags SQLServer

December 9, 2016

T-SQL - Recursive CTE's

by BF (Principal Consultant; Architecture; Engineering)

Generate a List of Numbers, Generate a List of dates(DATEFROMPARTS, EOMONTH, MAXRECURSION), Navigate a Hierarchy

Tags SQLServer

December 8, 2016

Add a new Article to an existing Publication in Transactional Replication (Push Subscription) using T-SQL

by BF (Principal Consultant; Architecture; Engineering)

sp_helppublication, sp_helpsubscription, sp_addarticle, sp_addsubscription

Tags SQLServer

December 6, 2016

Retrieving the input buffer in SQL Server 2016 using the new Dynamic Management Function - sys.dm_exec_input_buffer

by BF (Principal Consultant; Architecture; Engineering)

Functionally equivalent to DBCC INPUTBUFFER

Tags SQLServer

December 6, 2016

T-SQL Delete Duplicate Records

by BF (Principal Consultant; Architecture; Engineering)

CTE, Row_Number(), Over(Partition by)

Tags SQLServer

December 5, 2016

T-SQL Create a Distinct Comma Separated List of Postal Codes

by BF (Principal Consultant; Architecture; Engineering)

COALESCE

Tags SQLServer

December 5, 2016

T-SQL - Extract out the Date within File Names

by BF (Principal Consultant; Architecture; Engineering)

SUBSTRING Function w/ PATINDEX(Pattern Index Function) and a REGEX(Regular Expression)

Tags SQLServer

December 1, 2016

Find out when an AlwaysOn Availability Group Failover occurred

by BF (Principal Consultant; Architecture; Engineering)

sys.dm_xe_sessions, sys.dm_xe_session_targets, sys.messages

Tags SQLServer

November 29, 2016

Microsoft SQL Server Replication - Log Reader Agent - History, Status & Configuration

by BF (Principal Consultant; Architecture; Engineering)

MSlogreader_history, MSlogreader_agents, sp_replcmds

Tags SQLServer

November 19, 2016

Using Extended Events to Monitor Microsoft SQL Server AlwaysOn Availability Groups

by BF (Principal Consultant; Architecture; Engineering)

Capture DDL, Capture Availability Replica State Changes, Capture Total Events Captured

Tags SQLServer

November 4, 2016

T-SQL Get Replication Distribution History Errors

by BF (Principal Consultant; Architecture; Engineering)

MSdistribution_history, MSdistribution_agents

Tags SQLServer

October 15, 2016

T-SQL Analytic Functions FIRST_VALUE(), LAST_VALUE(), LEAD(), LAG()

by BF (Principal Consultant; Architecture; Engineering)

Analytic functions compute an aggregate value based on a group of rows. However, unlike aggregate functions, they can return multiple rows for each group.

Tags SQLServer

October 15, 2016

T-SQL Ranking Functions ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()

by BF (Principal Consultant; Architecture; Engineering)

Ranking functions return a ranking value for each row in a partition

Tags SQLServer

October 13, 2016

T-SQL Get Total Database Size

by BF (Principal Consultant; Architecture; Engineering)

sys.databases, sys.master_files

Tags SQLServer

October 7, 2016

Objects exposing columns with CLR types are not allowed in distributed queries. Please use a pass-through query to access remote object...

by BF (Principal Consultant; Architecture; Engineering)

OPENQUERY

Tags SQLServer

October 6, 2016

Azure SQL Database Geo-Replication Logins and Users Configuration

by BF (Principal Consultant; Architecture; Engineering)

Create Login, Create User, sys.sql_logins , sys.database_principals

Tags SQLServer

October 3, 2016

Azure SQL Database Geo-Replication - Initiate a Failover using T-SQL

by BF (Principal Consultant; Architecture; Engineering)

Alter Database FAILOVER, sys.dm_geo_replication_link_status, sys.dm_operation_status

Tags SQLServer

October 3, 2016

Failed to update database because the database is read-only. Error Code 3096.

by BF (Principal Consultant; Architecture; Engineering)

Transactional Replication, Distribution Database, MSrepl_errors table, Set-AzureRmSqlDatabaseSecondary

Tags SQLServer

September 29, 2016

SQL Server Transactional Replication "The process could not connect to Subscriber" and "Cannot open server requested by the login"

by BF (Principal Consultant; Architecture; Engineering)

On-Premise Microsoft SQL Server, Microsoft Azure SQL Database

Tags SQLServer

September 9, 2016

SQL Server List all Tables with no Primary Key

by BF (Principal Consultant; Architecture; Engineering)

information_schema.tables, sys.tables, replication

Tags SQLServer

September 7, 2016

Text was truncated or one or more characters had no match in the target code page

by BF (Principal Consultant; Architecture; Engineering)

SSIS Package, SQL Job

Tags SQLServer

September 6, 2016

Powershell Azure Virtual Machine Alerts with Webhook & Logic App

by BF (Principal Consultant; Architecture; Engineering)

Loop all Resource Groups & Virtual Machines in a Subscription

Tags SQLServer

September 2, 2016

T-SQL SET PARSEONLY ON vs SET NOEXEC ON vs SET FMTONLY ON

by BF (Principal Consultant; Architecture; Engineering)

Parsing T-SQL Statements

Tags SQLServer

August 28, 2016

Use Extended Events to capture Deadlocks in SQL Server

by BF (Principal Consultant; Architecture; Engineering)

Histogram, XQuery

Tags SQLServer

August 24, 2016

Create Alert for Azure SQL Database using Powershell Code

by BF (Principal Consultant; Architecture; Engineering)

Add-AzureRmMetricAlertRule

Tags SQLServer

August 22, 2016

T-SQL Log Long Running Open Transactions

by BF (Principal Consultant; Architecture; Engineering)

Transaction Log Size, Number of VLF(Virtual Log Files), Connection Details

Tags SQLServer

August 10, 2016

Transactional Replication - Out of Synchronization

by BF (Principal Consultant; Architecture; Engineering)

SkipErrors, Validate Subscriptions

Tags SQLServer

July 27, 2016

How to Migrate an On-Premise SQL Server Database into Microsoft Azure As-A-Service (PAAS)

by BF (Principal Consultant; Architecture; Engineering)

Export Data-tier Application, AzCopy Tool, Microsoft Azure Portal Import Database

Tags SQLServer

July 20, 2016

SQL Server Replication Get Undistributed Commands

by BF (Principal Consultant; Architecture; Engineering)

Distribution.sys.sp_MSget_repl_commands

Tags SQLServer

July 17, 2016

SQL Server Distribution Agent is retrying after an error

by BF (Principal Consultant; Architecture; Engineering)

Transactional Replication, Distribution.dbo.MSdistribution_history, Distribution Retention Period

Tags SQLServer

July 14, 2016

View Microsoft SQL Server Replication Publication details

by BF (Principal Consultant; Architecture; Engineering)

Publication Name, Publication DB, Article Name, Distribution Name, Subscriber Name, Subscriber DB

Tags SQLServer

July 11, 2016

Microsoft Azure SQL Database Copy Operation

by BF (Principal Consultant; Architecture; Engineering)

Start-AzureSqlDatabaseCopy, Get-AzureSqlDatabaseOperation, Monitor Progress

Tags SQLServer

July 3, 2016

Create Microsoft Azure SQL Database using Powershell

by BF (Principal Consultant; Architecture; Engineering)

Module AzureRM.sql, New-AzureRmSqlServer, New-AzureRmSqlDatabase, SAAS

Tags SQLServer

June 30, 2016

How to prevent schema changes to tables configured for SQL Server Replication

by BF (Principal Consultant; Architecture; Engineering)

Database-level Trigger

Tags SQLServer

June 29, 2016

T-SQL Check the last X days for any SQL Agent Job steps that were retried

by BF (Principal Consultant; Architecture; Engineering)

MSDN function dbo.agent_datetime for Date & Time, Filter out Replication Jobs

Tags SQLServer

June 28, 2016

T-SQL Stuff() vs Replace()

by BF (Principal Consultant; Architecture; Engineering)

Transact-SQL Functions

Tags SQLServer

June 22, 2016

Capture Slow Stored Procedure Calls in Microsoft SQL Server using Extended Events

by BF (Principal Consultant; Architecture; Engineering)

sqlserver.rpc_completed

Tags SQLServer

June 20, 2016

Cross-Database Queries in Azure SQL Databases

by BF (Principal Consultant; Architecture; Engineering)

Extension in the DDL for external data sources and external tables

Tags SQLServer

June 15, 2016

MSrepl_errors - TCP Provider: The semaphore timeout period has expired & Communication link failure

by BF (Principal Consultant; Architecture; Engineering)

Distribution DB

Tags SQLServer

June 15, 2016

Delete Microsoft Azure Storage Blob(s) based on specified retention period

by BF (Principal Consultant; Architecture; Engineering)

Microsoft SQL Server .bak & log files

Tags SQLServer

June 10, 2016

TechDevOps.com shared a Session - "SQL Server Index Internals: A Deep Dive (Tim Chapman; Microsoft)"

by BF (Principal Consultant; Architecture; Engineering)

PASS

Tags SQLServer

June 1, 2016

SQL Server Management Studio - Include Client Statistics

by BF (Principal Consultant; Architecture; Engineering)

Measure Round-Trip Client-to-Server Time

Tags SQLServer

May 19, 2016

SQL Server Management Studio Multiserver Queries

by BF (Principal Consultant; Architecture; Engineering)

Groups, Status Bar, MultiServer Results

Tags SQLServer

May 16, 2016

SQL Server Deadlock Detection & Logging

by BF (Principal Consultant; Architecture; Engineering)

Trace Flag, DMV

Tags SQLServer

May 15, 2016

Procedure Cache Analysis Queries

by BF (Principal Consultant; Architecture; Engineering)

sys.procedures, sys.dm_exec_procedure_stats, sys.dm_exec_cached_plans

Tags SQLServer

May 9, 2016

T-SQL Cross Apply vs Derived Table vs Subquery

by BF (Principal Consultant; Architecture; Engineering)

Enter Short Description Here

Tags SQLServer

May 6, 2016

SQL Server Execution Plan Handle Changes - Monitoring, Logging & Alerting

by BF (Principal Consultant; Architecture; Engineering)

Alert if Plan_Handle changes

Tags SQLServer

April 13, 2016

Rebuild Indexes based on Fragmentation Percent

by BF (Principal Consultant; Architecture; Engineering)

T-SQL, Alter Index

Tags SQLServer

April 6, 2016

TechDevOps.com shared a Article - "How to choose antivirus software ..."

by BF (Principal Consultant; Architecture; Engineering)

KB Article

Tags SQLServer

March 18, 2016

Microsoft SQL Server Replication - Validate Subscriptions

by BF (Principal Consultant; Architecture; Engineering)

Replication Monitor, T-SQL

Tags SQLServer

March 18, 2016

Microsoft SQL Server Replication Monitor Queries

by BF (Principal Consultant; Architecture; Engineering)

sp_replmonitorhelppublication, sp_replmonitorhelpsubscription

Tags SQLServer

March 18, 2016

Microsoft SQL Server Replication - Distribution Agent - History, Status, Stats, Errors

by BF (Principal Consultant; Architecture; Engineering)

MSdistribution_history, MSdistribution_agents, MSrepl_commands

Tags SQLServer

March 6, 2016

Azure Extended Events session return ShowPlan XML

by BF (Principal Consultant; Architecture; Engineering)

query_post_execution_showplan

Tags SQLServer

February 8, 2016

T-SQL Column Data Values placed into Bucket Ranges and Aggregated Totals

by BF (Principal Consultant; Architecture; Engineering)

Used to measure Transactional Replication Distribution Agent Performance

Tags SQLServer

February 7, 2016

The definition of this report is not valid or supported by this version of Reporting Services

by BF (Principal Consultant; Architecture; Engineering)

Microsoft SQL Server Reporting Services

Tags SQLServer

February 6, 2016

TechDevOps.com shared a link - "Maximum Capacity Specifications for SQL Server"

by BF (Principal Consultant; Architecture; Engineering)

MSDN

Tags SQLServer

February 5, 2016

T-SQL Query Tuning - Statistics, Histogram, Density, Selectivity & Cardinality

by BF (Principal Consultant; Architecture; Engineering)

DBCC Show_Statistics, Set Statistics Profile, Set Statistics IO, Set Statistics Time, sp_updatestats, DBCC dropcleanbuffers, DBCC freeproccache

Tags SQLServer

January 27, 2016

Rebuild or Reorganize Index

by BF (Principal Consultant; Architecture; Engineering)

Recommendations, Information, Examples

Tags SQLServer

January 26, 2016

Distribution Agent Undistributed Commands value is not changing

by BF (Principal Consultant; Architecture; Engineering)

On-Prem Publisher & Distributor with Azure Subscriber

Tags SQLServer

January 20, 2016

How to Identify Performance Issues on Azure SQL Database

by BF (Principal Consultant; Architecture; Engineering)

Azure SQL Database (as-a-service), DMVs

Tags SQLServer

January 19, 2016

The row was not found at the Subscriber when applying the replicated command

by BF (Principal Consultant; Architecture; Engineering)

Error 20598

Tags SQLServer

January 4, 2016

RESTORE DATABASE is terminating abnormally using Azure Blog Storage

by BF (Principal Consultant; Architecture; Engineering)

services.msc

Tags SQLServer

December 23, 2015

T-SQL subtract current row value from previous row value

by BF (Principal Consultant; Architecture; Engineering)

LAG (Transact-SQL)

Tags SQLServer

December 14, 2015

Move Azure SQL Server and Azure SQL Database to a new Resource Group

by BF (Principal Consultant; Architecture; Engineering)

Powershell Script

Tags SQLServer

December 10, 2015

Azure BizTalk Hybrid Connections

by BF (Principal Consultant; Architecture; Engineering)

Hybrid Connection Manager, Listener, Ports

Tags SQLServer

November 26, 2015

Azure SQL Database Scale Operation failed

by BF (Principal Consultant; Architecture; Engineering)

Using Azure SQL Database Geo-Replication Two-Node Configuration

Tags SQLServer

November 23, 2015

View Microsoft SQL Server Reporting Services Report Server Logs

by BF (Principal Consultant; Architecture; Engineering)

Report Server Execution Log and the ExecutionLog3 View

Tags SQLServer

November 8, 2015

Determine the progress of Microsoft SQL Server Backup or Restore

by BF (Principal Consultant; Architecture; Engineering)

Using T-SQL get the Total Elapsed Time, Percentage Completed & Estimated Completion Time...

Tags SQLServer

November 7, 2015

Microsoft Azure Blob Storage Service as a backup destination

by BF (Principal Consultant; Architecture; Engineering)

Requires Storage Account, Container & Credential

Tags SQLServer

November 7, 2015

Find Microsoft SQL Server Agent Job History Retention Settings using T-SQL

by BF (Principal Consultant; Architecture; Engineering)

Max Records, Max Records Per Job, xp_instance_regread

Tags SQLServer

November 3, 2015

Transactional Replication to Azure SQL DB now in public preview

by BF (Principal Consultant; Architecture; Engineering)

"During my presentation at Pass Summit 2015, I announced that you can now configure Azure SQL DB as a subscriber of SQL Server Transactional Replication..."

Tags SQLServer

November 2, 2015

Create an Extended Events session - Azure SQL Database

by BF (Principal Consultant; Architecture; Engineering)

sqlserver.sql_batch_completed, ring_buffer

Tags SQLServer

November 2, 2015

Create an Extended Events session - On-Prem

by BF (Principal Consultant; Architecture; Engineering)

sqlserver.rpc_completed, sqlserver.sql_batch_completed, ring_buffer

Tags SQLServer

November 1, 2015

Microsoft SQL Server Query Testing Tool

by BF (Principal Consultant; Architecture; Engineering)

SQLQueryStress by http://www.datamanipulation.net/

Tags SQLServer

November 1, 2015

Move Distribution Database Files to other drives

by BF (Principal Consultant; Architecture; Engineering)

Using Alter Database commands

Tags SQLServer

October 15, 2015

SSIS Create Archive Folder based on current system date & time

by BF (Principal Consultant; Architecture; Engineering)

Using SSIS Package Variables & Expressions

Tags SQLServer

October 12, 2015

List your SQL Server Replication Topology using T-SQL

by BF (Principal Consultant; Architecture; Engineering)

Distributor, Publisher, Publications & Subscribers

Tags SQLServer

October 7, 2015

Find hidden characters in a SQL Server table data

by BF (Principal Consultant; Architecture; Engineering)

Using ASCII (Transact-SQL)

Tags SQLServer

September 27, 2015

Custom SQL Server Code Repository - "MSSQLCodeRepo" tool

by BF (Principal Consultant; Architecture; Engineering)

Includes Code Extractor & Viewer

Tags SQLServer

September 7, 2015

Move SQL Server TempDB Data & Log files

by BF (Principal Consultant; Architecture; Engineering)

T-SQL

Tags SQLServer

September 6, 2015

SQL Server Transactional Replication Threads

by BF (Principal Consultant; Architecture; Engineering)

Query Publisher, Distributor & Subscriber DB's

Tags SQLServer

August 30, 2015

Microsoft SQL Server Reporting Services - One Dataset, Many Charts

by BF (Principal Consultant; Architecture; Engineering)

Chart Filters

Tags SQLServer

August 30, 2015

Microsoft SQL Server Reporting Services - X-Axis - Date Data

by BF (Principal Consultant; Architecture; Engineering)

Solution for optimal number of axis labels

Tags SQLServer

August 14, 2015

View & Log Active TCP/IP Connections

by BF (Principal Consultant; Architecture; Engineering)

Netstat, Microsoft Message Analyzer, WireShark, CurrPorts

Tags SQLServer

August 12, 2015

View Microsoft SQL Server Replication Errors

by BF (Principal Consultant; Architecture; Engineering)

Replication System Tables

Tags SQLServer

August 6, 2015

List information about all SQL Server Databases & Files

by BF (Principal Consultant; Architecture; Engineering)

sys.master_files, sys.databases

Tags SQLServer

August 6, 2015

Use a SQL Server Trigger to capture calling object updating a table

by BF (Principal Consultant; Architecture; Engineering)

DBCC InputBuffer

Tags SQLServer

August 6, 2015

SSIS error Text was truncated or one or more characters had no match in the target code page

by BF (Principal Consultant; Architecture; Engineering)

Invalid Characters discovery

Tags SQLServer

August 5, 2015

Find all SQL Server Instances

by BF (Principal Consultant; Architecture; Engineering)

SQLCMD, OSQL, Powershell, TSQL

Tags SQLServer

August 3, 2015

Alerting on Deadlocks with SQL Server Management Pack in Microsoft Systems Center Operations Manager

by BF (Principal Consultant; Architecture; Engineering)

Configure sys.messages to log EventID 1205

Tags SQLServer

August 2, 2015

Java(Prepare-Execution Model) View call performance issue solved by SQL Server Plan Guides

by BF (Principal Consultant; Architecture; Engineering)

Parameter Sniffing lead to poor SQL Server Execution Plan

Tags SQLServer

August 2, 2015

How to check if SQL Server is running on a Physical or Virtual Machine

by BF (Principal Consultant; Architecture; Engineering)

Use T-SQL, CMD Prompt or Powershell

Tags SQLServer

August 2, 2015

Find a stored procedure call within a SQL Server Job

by BF (Principal Consultant; Architecture; Engineering)

Using T-SQL, find a stored procedure call within a SQL Server Job step(s)

Tags SQLServer

August 1, 2015

How to detect database version from a Hosting Provider backup file

by BF (Principal Consultant; Architecture; Engineering)

Using TSQL RESTORE HEADERONLY to obtain the database version & compatability level of the file...

Tags SQLServer

July 2, 2015

Creating Data Collector Sets

by BF (Principal Consultant; Architecture; Engineering)

MSDN - A Data Collector Set is the building block of performance monitoring and reporting in Windows Performance Monitor....

Tags SQLServer

June 28, 2015

New EMC Storage LUN Visibility to SQL Server Management Studio(SSMS)

by BF (Principal Consultant; Architecture; Engineering)

SSMS LUN Visibility requires Failover Cluster Manager dependency added

Tags SQLServer

June 28, 2015

Custom SQL Server Transactional Replication Publication(s) Validation Automation

by BF (Principal Consultant; Architecture; Engineering)

How to implement a complete solution for SQL Server Replication Publication(s) Validation, Error Logging & Notification

Tags SQLServer

June 28, 2015

Use T-SQL to log Active SPID Execution Duration

by BF (Principal Consultant; Architecture; Engineering)

Use T-SQL to capture, filter & log Active SPID Execution Duration for all active requests inside the Microsoft SQL Server Engine.

Tags SQLServer

June 21, 2015

Use Powershell to capture SQL Server Resources

by BF (Principal Consultant; Architecture; Engineering)

Use Powershell to capture SQL Server Resources - Test Connection, CPU Usage, MEM Usage & Disk Usage.

Tags SQLServer

June 21, 2015

Building Your Own SQL Server Monitoring Tool

by BF (Principal Consultant; Architecture; Engineering)

A few years ago I decided to create a Custom Performance Monitoring, Alerting and Diagnostics Web Application Tool for SQL Server product. At the time I was a Senior Microsoft SQL Server DBA managing a large farm installation of Microsoft SQL Server...