SQL Server 2016 simplifies database maintenance with the DROP IF EXISTS T-SQL enhancement. This feature eliminates the need for legacy object existence checks using IF IF EXISTS or sys.objects. Learn how to drop tables...
Category - SQL Server
Welcome to the ultimate resource for Microsoft SQL Server Database Administration and Development. Our library of expert-led SQL Server tutorials is designed to help you navigate the complexities of enterprise data management, from local development to high-availability production clusters.
Advanced T-SQL Development & Performance Tuning
Master the art of query optimization with in-depth guides on T-SQL enhancements in SQL Server 2016, 2019, and 2022. Learn how to implement high-performance STRING_SPLIT and STRING_ESCAPE functions, utilize DROP IF EXISTS logic for clean deployment scripts, and leverage TRUNCATE TABLE WITH PARTITIONS to manage massive datasets without bloating your transaction logs.
We go beyond basic syntax to show you how to reduce CPU pressure, resolve SQL Server deadlocks, and optimize TempDB contention for maximum throughput.
Mission-Critical DBA Best Practices
Secure your data infrastructure with our proven SQL Server DBA checklists. Our tutorials cover the ‘nuts and bolts’ of database health, including step-by-step SQL Server backup and recovery model configurations, automating SQL Server Agent Jobs, and troubleshooting SQL Server error log 15151 or 18456.
Whether you are looking for SQL Server row count history scripts, configuring Always On Availability Groups, or managing SQL Server Analysis Services (SSAS) backup and restore procedures, MyTechMantra provides the technical clarity needed to maintain 99.9% uptime. Stay updated with the latest SSMS tips, database migration strategies, and SQL Server security hardening techniques to protect your organization’s most valuable asset—its data.
Ashish Kumar MehtaDROP IF EXISTS – Simplifying Database Cleanup Scripts in SQL Server 2016
SQL Server 2016 simplifies database maintenance with the DROP IF EXISTS T-SQL enhancement. This feature eliminates the need for legacy object existence checks using IF EXISTS or sys.objects. Learn how to drop tables...
How to Truncate Specific Partitions in SQL Server (Fast & Efficiently)
Learn how to use TRUNCATE TABLE WITH PARTITIONS in SQL Server 2016 to clear specific data ranges instantly with minimal logging. Faster than DELETE and SWITCH
T-SQL Enhancements in SQL Server 2016 for Developers and DBAs
SQL Server 2016 introduces many new T-SQL features for Developers and Database Administrators. Let us take a look at some of the new T-SQL enhancements in SQL Server 2016 along with relevant examples. This is Part 1 of...
How to Enable an Index in SQL Server
In this article we will take a look at how to enable an index in SQL Server. This is a very useful feature which will help you enable an index which was disabled earlier to check whether the index was really useful or...
How to Disable an Index in SQL Server
In this article we will take a look at how to disable an index in SQL Server. This is a very useful feature which will help you identify whether the index is really useful or not without actually dropping the index...
How to Change SQL Server Login Properties to Enforce Password Policies and Expiration Settings
How to set SQL Server Password Policy | Set SQL Password Policy This article demonstrates the steps to create an SQL Server Login which enforces password policies and password expiration policies. Let us start by...
Troubleshooting The remote procedure call failed. [0x800706be] WMI Error in SQL Server
You may end up getting “The remote procedure call failed. [0x800706be] WMI Error“ when you click SQL Server Services in SQL Server Configuration Manager as shown in the snippet below. This article explains the steps to...
TAIL-LOG Backup in SQL Server Step by Step Tutorial with Examples
A tail-log backup captures any log records which has not yet been backed up i.e., the tail of the transaction log to prevent any work loss and to keep the transaction log chain intact. Before you can recover a SQL...
Chetna BhallaPARTIAL Backup in SQL Server Step by Step Tutorial with Examples
As per MSDN “PARTIAL Backups are designed for use under SIMPLE recovery model; thereby to improve flexibility to backup very large database which contain one or more read-only Filegroups. They are useful whenever...
FILEGROUP Backup in SQL Server Step by Step Tutorial with Examples
Using FILEGROUP backup one can backup all the data files within the SQL Server FILEGROUP individually. While backing up the database you can specify whole of FILEGROUP (i.e., PRIMARY or SECONDARY) instead of specifying...
FILE Backup in SQL Server Step by Step Tutorial with Examples
Using FILE backup one can backup SQL Server Data File individually. While backing up the database you can specify whole of FILEGROUP instead of specifying each database file individually within the FILEGROUP. In case if...
MIRRORED Backup in SQL Server Step by Step Tutorial with Examples
Using MIRRORED Backup feature a DBA can create up to 3 identical copies of a database backup. This feature is available in SQL Server 2005 Enterprise Edition and later versions. This is Part 12 of 16 Part SQL Server...
COPY_ONLY Backup in SQL Server Step by Step Tutorial with Examples
COPY_ONLY backup is a special type of SQL Server Backup which is independent of the sequence of conventional SQL Server backups. Normally whenever you take a backup it will affect how later backups are restored...
TRANSACTION LOG Backups in SQL Server Step by Step Tutorial with Examples
Transaction log backup is only possible when your database is in FULL or BULK-LOGGED recovery model. With the help of Transaction Log backup one can achieve Point in Time recovery for the database in case of any...
DIFFERENTIAL Database Backups in SQL Server Step by Step Tutorial with Examples
What is a Differential Backup SQL Server? Differential Backup SQL Server will only record the data which has changes since the last successful full database backup. Differential Backup SQL Server | Differential Database...
FULL Database Backups in SQL Server Step by Step Tutorial with Examples
FULL Database Backup is used to back up the whole database in SQL Server. A database full backup will include parts of transaction log so that if the need arises a database can be recovered completely by restoring full...
Permissions Required to Take Database Backup in SQL Server
For a user to take database backups in SQL Server a user must be a member of DBCREATOR Server Role and DB_OWNER Database Role. Else you will receive the below mentioned error while performing backups...
Different Types of SQL Server Backups
SQL Server supports different types of backups for databases. However, which type of a database backup can be performed totally depends upon the recovery model of the database one choose. If you are unclear of the...
SQL Server BULK-LOGGED Recovery Model Step by Step Tutorial with Examples
When a database is configured to use a BULK-LOGGED Recovery Model then SQL Server will log minimal amount of information for operations such as SELECT INTO, BULK INSERT, BCP, CREATE INDEX, ALTER INDEX, and REBUILD INDEX...
SQL Server FULL Recovery Model Step by Step Tutorial with Examples
In Full Recovery Model Point in Time recovery of the database is possible as long as you have all the valid database backups along with the transaction log tail backup file. In Full Recovery model all the transactions...
SQL Server SIMPLE Recovery Model Step by Step Tutorial with Examples
SIMPLE Recovery Model in SQL Server SIMPLE Recovery Model as the name suggests it is the most basic recovery model which is available in SQL Server. In this recovery model every transaction is written to the...
Different SQL Server Recovery Models Step by Step Tutorial with Examples
Recovery Models in SQL Server are basically designed to control the transaction log maintenance and to help you recover your data from a disaster. Based on the choice of Recovery Model, SQL Server decides which data it...
SQL Server Database Backup Tutorial with Examples
Introduction It is very critical for any organization to protect its data within the database. Hence it is must for an organization to perform database backups at regular intervals. In this tutorial, you will learn...
How to Start SQL Server in Single User Mode?
There can be certain scenarios when one needs to connect to an SQL Server Instance in a Single User Mode by using the Startup Option -m. For example, the need could be to recover a damaged system database such as...
SQL Server: How to Start SQL Server with Minimal Configuration
Introduction There can be certain scenarios when SQL Server is not starting because of configuration problems. During such scenarios one need to start an instance of SQL Server by using minimal configuration...
Download SQL Server 2016 Developer Edition for Free
SQL Server 2016 Download Developer Edition for Free Microsoft announced the release of the Release to Manufacturing (RTM) version of SQL Server 2016 on June 1, 2016. The good news is that you can download SQL Server...
How to Export records from SQL Server to Text File using BCP Command
Introduction Is there a quick way to export records from SQL Server to text file? In this tip we will take a look at an example to export records from SQL Server to text file using BCP. In this tip we will learn how to...
Configure Network Drive Visible for SQL Server During Backup and Restore Using SSMS
Introduction Most of the Development and Test Database Servers will not have enough disk space to store both the database and backup files in order to perform the periodic database refreshes. In such scenarios, the best...
Troubleshooting SQL Server blocked access to procedure sp_send_dbmail
Use sp_send_dbmail to send email from SQL Server | How to Use Database Email SQL Server Feature Troubleshooting SQL Server blocked access to procedure ‘dbo.sp_send_dbmail’ of component ‘Database Mail...
Download SQL Server 2014 Developer Edition Free
Introduction Download Microsoft SQL Server 2014 Developer Edition for Free for Visual Studio Dev Essentials Members Microsoft has announced that the SQL Server 2014 Developer Edition will available for free to Visual...
Convert Seconds to Minutes, Hours and Days in SQL Server
As a developer there are times when yo need to convert seconds to minutes, hours and days. The TSQL script mentioned in this article can be used to easily Convert Seconds to Minutes, Hours and Days. Learn How to FORMAT...
Different Ways to Find Default Trace Location in SQL Server
How to Find SQL Server Default Trace Location Starting SQL Server 2005, Microsoft introduced a light weight trace which is always running by default on every SQL Server Instance. The trace will give very valuable...
SQL Server: How to Find which user deleted the database in SQL Server
Introduction In this article we will take a look at the steps which you can follow to quickly identify the user who deleted the user database in SQL Server. There are two different methods by which one can easily find...
How to Change SQL Server Database Auto Growth Settings
Introduction In this article we will go through the steps to change SQL Server Database Auto Growth Settings. It is always a best practice to set an appropriate auto growth setting for all Production database to a...
How to Repair Suspect Database in SQL Server
There can be a possibility that when you connect to an SQL Server Instance you will find the database being marked as SUSPECT. During such scenarios, you will not be able to connect to the database. Steps to Fix...
Fix Cannot open user default database. Login failed. Login failed for user SQL Server Error
In this article we will take a look at the steps which you need to follow when you receive “Cannot open user default database. Login failed. Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)“ SQL...
Identify Deadlocks in SQL Server Using Trace Flag 1222 and 1204
This article outlines the steps which one can follow to enable Trace Flag 1222 on SQL Server to capture deadlock information. You can also use Trace Flag 1204 in conjunction with Trace Flag 1222. Difference between...
SQL Server Best Practice Auto Close Database Option Should Remain OFF
Introduction In this article, we will take a look at why it is essential to leave AUTO CLOSE database option turned OFF for a Production or a Non-Production SQL Server Database across all versions and editions of SQL...
How to Change Select Top 1000 Rows and Edit Top 200 Rows Default Value in SQL Server Management Studio
How to Select Top 1000 Rows SQL Server Starting SQL Server 2008, SSMS allows you to Select Top 1000 rows and Edit Top 200 rows. However, in the previous version of SSMS 2005, the only option was to open the entire table...
How to Verify and Register SPN for SQL Server Authentication with Kerberos Connections
Introduction This article explains how to verify and register Service Principal Names (SPN) for SQL Server Authentication with Kerberos Connections. Kerberos Authentication is a widely accepted network authentication...
Drop Database in SQL Server by Killing Existing Connections
DROP Database SQL Server Let’s learn how to Drop Database in SQL Server when the users are connected to the SQL Server Database. You may find the need to close all the existing database connections in a database...
Display Line Numbers in SQL Server Management Studio (SSMS)
SSMS Show Line Numbers | SSMS Line Numbers This article explains how to enable SSMS show line numbers. SHOW Line Numbers in SQL Server Management Studio As a developer you may have often encounter errors while debugging...
How to Move TempDB to New Drive in SQL Server
There are times when as a DBA you find the need to move TempDB Data and Log Files to a new Drive. This article explains all the steps you need to follow to move TempDB files.
FORMAT SQL Server Dates Using FORMAT Function in SQL Server
FORMAT Date in SQL Server FORMAT Function was introduced in SQL Server 2012, and it is available in all the later versions of SQL Server. This article will show different examples of using the new FORMAT function in SQL...
