Performance Tuning And Infra Automation

SQL Server - Index Maintenance for Replication Databases

SQL Server – Doing Index Maintenance when HADR solutions are in Place

When I joined my current organization 2 years back, the biggest problem I faced as DBA was whenever there was Replication, log shipping, Mirroring, or AlwaysOn, the Index Maintenance jobs…

Ajay Dwivedi February 22, 2020

Error - Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server. (.Net SqlClient Data Provider)

Errors

Error/Fix: Cannot initialize the data source object of OLE DB provider “OraOLEDB.Oracle” for linked server. (.Net SqlClient Data Provider)

Recently I was working on one user request to create a linked server for Oracle database in SQL Server 2014 instance. After installing the correct Oracle ODBC drivers, when I…

Ajay Dwivedi February 20, 2020

ScriptOut-LinkedServers-With-Actual-Password

Powershell

ScriptOut Linked Server with Actual Password

Within my organization, we work in a controlled environment. That means, with each SQL Instance migration, we have strict rules to script out everything including System database user objects, operators,…

Ajay Dwivedi October 25, 2019

TSQL

Some useful Scripts

As a DBA/Developer, there have been many occasions where I had to write my own scripts/automation to fulfill specific business requirements. Below are some of those useful scripts that I…

Ajay Dwivedi September 8, 2019

Baseline-SQL-Server SQL Alerts TSQL

Setup Long-Running Jobs Alert

In my organization, we have many SQL Agent jobs that are expected to execute within a particular time window. But there are times when some of them take longer than…

Ajay Dwivedi September 7, 2019

Performance Tuning TSQL

Get Rid of High VLF Easily

Remove High Virtual Log File (VLF) (@OptimizeLogFiles) This generates TSQL code for removing huge virtual log files (VLFs) from SQL Server database log files. By default, @vlfCountThreshold is 500. So any log file with VLF count greater than @vlfCountThreshold will be considered for SHRINKFILE operation, Regrow to original Size in appropriate size chunk, and finally reset the AutoGrowth settings. Space-Capacity-Automation url:- https://github.com/imajaydwivedi/Space-Capacity-Automation

Ajay Dwivedi March 14, 2019

Baseline-SQL-Server Health Check

Baseline SQL Server

Hi DBA friends! Like me, if you are responsible for multiple SQL Servers and don't have any third-party monitoring tool, then I guess instead of writing your own in-house tool,…

Ajay Dwivedi March 5, 2019

SQL Alerts TSQL

How to Setup Blocking Alerts on SQL Server

It's very common for Database professionals to experience blocking. Even though blocking is a usual activity, it can convert into an issue anytime. And becomes pain point when DBAs are…

Ajay Dwivedi August 18, 2018

Database Administration

Indexing Maintenance Solution for Replication

Like any other DBA, I fell into the trap of using straight maintenance solution using Reorganize operation for Indexes with avg fragmentation with 30% or less with Index Rebuild for…

Ajay Dwivedi July 10, 2018

Errors

Error/Fix: There is insufficient system memory in resource pool ‘default’ to run this query.

Recently I was reading about Temporal Tables feature that has been introduced with SQL Server 2016. So, as usual, I thought to get the knowledge in practice with my virtual…

Ajay Dwivedi July 7, 2018