• The transaction log for database ‘database name’ is full due to ‘REPLICATION’ - Today, we are going to see an exception from SQL Server “The transaction log for database ‘dbname’ is full due…
• How to capture deadlock occurrences in SQL Server using sys.dm_os_performance_counters - Today, let us quickly see how to monitor deadlocks in your SQL Server. There are multiple ways to get the…
• When was the database taken offline/online in SQL Server - Here is a script to identify when was the database taken offline/online and few other information like login name, host…
• How do we list the available instances from SQL Server - Here is a script to list the available instances of a SQL Server. Script I’d like to grow my readership.…
• A way to Shrink Data File in SQL Server - Problem Statement We recently wanted to drop few obsolete tables those are really big ones in my SQL Server databases.…
• How to identify Prime numbers in SQL Server - While on HackerRank puzzles, I came through a question to find out prime numbers in SQL Server. Here is my…
• How to re-queue an email in SQL that has been successfully sent at first time? - Let’s say you send an email from inside a SQL instance using something like the following: And recipient DOES successfully…
• How to get FQDN (fully qualified domain name) from SQL Server? - A fully qualified domain name (FQDN) is referred to as an absolute domain name, is a domain name that specifies…
• How to get Disk space information in SQL Server - Problem Statement: While troubleshooting an issue in SQL Server, I wanted to know the space information of the server. Since…
• Compress and Decompress in SQL Server 2016 - Recently, to enforce a very small level of masking of data, I was thinking of different ways and finally used…
• Handy Troubleshooting T-SQL queries for Always On Availability Groups in SQL Server - Today, as part of a database migration from (one server to another server), wanted to check few things related to…
• Service Broker enabled by default while creating a database through T-SQL in SQL Server - Recently, we decided to disable Service Broker for all databases as part of our deployment. While doing a preliminary check,…
• Fun with SQL – Find out numbers where adjacent digit differs by 1 - Today, I have come through a post by one of my good friends and one of the most famous SQL…
• Database Properties – Difference between GUI and sys.master_files in SQL Server - Few days back, One of my colleagues was asking why there is a discrepancy in GUI and system table for…
• Amazon RDS for SQL Server versus SQL Server On Amazon EC2 - Its an era of Cloud computing and anywhere you go, people talk about cloud computing and its usages, migration and…
• SQL Server Inventory Queries – SQL Server Databases - Here is a T-SQL script used for inventory purpose on your SQL Server Databases. Tested in: SQL Server 2016/SQL Server…
• SQL Server Inventory Queries – SQL Server Instances - Here is a T-SQL script used for inventory purpose on your SQL Server Instances. Tested in: SQL Server 2016/SQL Server…
• How to enable Side by Side Query window in SQL Server Management Studio - Side by Side Query window is a very useful feature in SSMS. When we are comparing queries or its data,…
• Stairway to SQL Server Table Partitioning – Whats is Table Partitioning in SQL Server - There are two types of partitioning in SQL Server – Vertical & Horizontal. Today, we are quickly going to see…
• Understanding PIVOT operator in SQL Server with Examples - Today, we will quickly see PIVOT operator in SQL Server and why do we need this and its various forms…
• Error Message in SQL Server: Conflicting locking hints are specified for table “tablename”. This may be caused by a conflicting hint specified for a view. - Recently, One of my colleague has reported an issue as below from production environment. Msg 4138, Level 16, State 1,…
• How to identify redundant/duplicate indexes in SQL Server - What are redundant indexes in SQL Server? Identifying redundant indexes is a very important task for a DBA. I personally…
• How to identify Missing Indexes in SQL Server -
• The enlist operation failed (reason: SQLServerAgent Error: The target server cannot establish an encrypted connection to the master server ‘Servername’. Make sure that the MsxEncryptChannelOptions registry subkey is set correctly on the target server.) (Microsoft SQL Server, Error: 22026) - While configuring SQL Server Multi Server Administration: Master and Target Servers in SQL Agent, we encountered an error as below:…
• Stairway to SQL Server Table Partitioning – How do we partition an existing table - This post explains how to implement table partitioning for an existing table in SQL Server. Let us create a table…
• Search a value in your database in SQL Server - Here is a script to identify a value in your database. The script will identify the presence of the search…
• How to build comma separated string in SQL Server - Building a comma separated string is a very frequent&common requirement for SQL Server developers. Developers find different ways to achieve…
• How to remove special characters like TAB, Carriage Return, and Line Feed characters from string in SQL Server - Today, we are going to see a scenario where we need to remove special characters from a string in SQL…
• Curious case of varchar to uniqueidentifier in SQL Server - Converting a datatype to another datatype is a very common requirement in real world and we usually do it with…
• Generate Random characters in SQL Server - Here is a script to generate randon characters in SQL Server It also allows you to generate the characters in…
• How to change mdf and ldf files path to instance default path in SQL Server - Here is a code snippet to change the mdf and ldf file path of an existing database to instance default…
• Dark theme in Sql Server Management Studio - Many people prefer dark theme for computer displays. It actually enhances the visual ergonomics by reducing eye strain and providing…
• Cleanup of Load Runner Test data from SQL Server - Here is a script to purge the Load test data from your load runner database. This would be handy at…
• How to identify Scans (Table/index) from cached plan in SQL Server - Today, we will quickly see how to identify the scans happening on SQL Server. I had to analyse a test…
• XACT_ABORT in SQL Server - Today’s post will explain XACT_ABORT in SQL Server. An efficient way of error handling before SQL Server 2005.Post SQL Server…
• CONTAINSTABLE is not returning records from full text enabled table in SQL Server - Recently, we have an issue, FULL TEXT search was not working with CONTIANSTABLE. My friend was searching for a word…
• “The database is encrypted by database master key, you need to provide valid password when adding to the availability group.” - Recently, one of my colleague has encountered an issue an error while adding a database to availability group as below:…
• How do we identify LOB datatype columns in SQL Server - Large OBject (LOB) data types are datatypes that can store large data. These datatypes can hold values larger than 8K…
• Length of LOB data to be replicated exceeds configured maximum 65536 in SQL Server - Problem Statement: Sometimes When we do a DML operation in SQL Server, we end up with the below error –…
• CDC Jobs in SQL Server – Capture and Cleanup - Earlier, we found how do we set up Change Data Capture for a Database. Today, lets us quickly look at…
• Could not update the metadata that indicates database <> is enabled for Change Data Capture. The failure occurred when executing the command ‘SetCDCTracked(Value = 1)’. The error returned was 15517: ‘Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission.’. Use the action and error to determine the cause of the failure and resubmit the request. - While configuring Change Data Capture on a database that is restored from different environment, we might end up with an…
• Decoding hexa error code to windows error message in SQL Server - Problem Statement: Sometimes, the error messages generated are not friendly to understand the underlined issues. This is going to be…
• How to Replay a SQL Server Profiler trace in SQL Server - It is one of the most interesting topic in SQL Server “How to Replay profiler”. But, I have not seen…
• How to identify parallel plans in SQL Server - Few years back, on one of my engagements, identified lots of performance issues with an application. As part of a…
• How do we provide xp_cmdshell access to a SQL login who is not a member of sysadmin in SQL Server - This post is to answer a question by Mr. Q (I am masking the name of the reader as it…
• How do we move or restore a database with CDC enabled in SQL Server - This post explains how efficiently we can move a CDC enabled database to different environment. To move a database from…
• How do we set up Change Data Capture in SQL Server - This may be an old topic for many who worked with SQL Server, but, As I was doing a proof…
• Configure BlockedProcessReport in SQL Server - In this blog we will discuss on how to configure Blocked Process report on a particular database. This will be…
• String_Split function in SQL Server 2016 - Introduction: SQL Server 2016 has introduced a built-in table valued function string_split to split a string into rows based on…
• SQL Server 2016 Database Scoped Configuration to force The Legacy CE - Problem Statement: Recently, we identified a performance issue with a query once we upgraded the database server to SQL Server…
• WITH Encryption clause in SQL Server - This is quite an old feature, however, a very useful one in SQL Server. Applies to: SQL Server 2005 and…
• How to identify table rows and size in a database in SQL Server - Problem Statement: At times, we may need to understand the size of the tables in a database for various reasons.…
• Find Max and Min value of dates in a row in SQL Server - Problem Statement: How to identify the Max and Min date value in a row in SQL Server? Code: Here is…
• How to identify Process Name of a Process Id in SQL Server - We had an immediate requirement to identify process names of process ids in SQL Server. The first reaction to the…
• SQL Server Configuration Manager – The remote procedure call failed. [0x800706be] - Problem Statement: Recently, I encountered an issue as below with SQL Server Configuration Manager which is a very common issue…
• More about MSDTC - What is MSDTC? MSDTC is a acronynm for Microsoft Distributed Transaction Coordinator which is a windows service to maintain the…
• Error Message: The operation could not be performed because OLE DB provider “SQLNCLI11” for linked server “” was unable to begin a distributed transaction. - Problem Statement: We had an issue with Linked Server in one of our environment while executing a functionality. The error…
• Internal usage of TEMPDB by SQL Server DB engine - This post is actually a continuation post for TEMPDB – the most important system database in SQL Server which trying…
• Table Variables in SQL Server - Table variables are another types of temporary objects to store transient data. Please refer Temporary Table objects for more details…
• Linked Server in SQL Server – Best Practices - Linked Server is one of the easiest way of communicating between multiple servers/instances in SQL Server. By linking the servers,…
• How to create a lock on a table that prevents other requests in SQL Server - Problem Statement: Today, I got a very peculiar request from one of my colleague that he wants to put a…
• Temporary objects in TEMPDB database - With this blog post, a continuation of earlier post on “TEMPDB – the most important system database in SQL Server“,…
• TEMPDB – the most important system database in SQL Server - Introduction If you had anytime worked in SQL Server, you would be knowing about a system database – TEMPDB. TEMPDB…
• A quick look at SQL Server Logs - Logs are always important for anyone who works with any application. SQL Server has its own Logs that can be…
• Task failed due to following error: Cannot save package to file. Error SQL71561: Error validating element in Always Encrypted Implementation - Problem Summary While I am trying to configure Always Encrypted for one of my databases, I received an error as…
• CURRENT_TIMESTAMP in SQL Server - CURRENT_TIMESTAMP is a system function to retrieve the current system date and time. CURRENT_TIMESTAMP is an ANSI SQL function whereas…
• Could not open file E:\TempDB\tempdb.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.) - Recently we faced an issue that SQL Server was not coming up online in one of our environment. We found…
• Memory optimized Temporary table in SQL Server - Today, we will quickly see how do we create memory optimized temporary table in SQL Server and how to use…
• SQL Server detected a logical consistency-based I/O error: incorrect checksum - Recently, we encountered an issue with a restored database stating the below error message: Msg 824, Level 24, State 2,…
• How to remove space in a column names of table in SQL Server - Here is a script to rename the column names to avoid the space(s) in the column name. create table test_table([column…
• ALTER DATABASE failed because a lock could not be placed on database ‘dbname’. Try again later. - Problem statement As a DBA, you will be facing the error message “ALTER DATABASE failed because a lock could not…
• How to get last running query based on SPID in SQL Server - We often need to identify the SQL text that was executed last time for a spid in SQL Server. Recently…
• DBMIRROR_DBM_EVENT wait type causing blocking in SQL Server - Recently, we observed lots of blocking in our production server showing the wait type as DBMIRROR_DBM_EVENT. To brief, the system…
• How do we recover unsaved query window in SQL Server Management Studio? - How do we recover the unsaved query window in SQL Server Management Studio? This is a simple tip to recover…
• Error Message – The index ” is dependent on column ”.ALTER TABLE ALTER COLUMN ” failed because one or more objects access this column. - It is always annoying when we get a request to change the datatype of an existing column for many reason.…
• Msg 22841, Level 16, State 1, Procedure sp_cdc_vupgrade, Line 323 – While restoring from a cdc enabled database backup - We encountered an issue as below while restoring from a CDC enabled database backup. Though Management Studio has shown the…
• DBCC UPDATEUSAGE – Is it important post SQL Server version upgrade? - As part of migration from SQL server 2008 R2 to SQL server 2014, we listed out a list of items…
• The multi-part identifier “” could not be bound. with insert statement to get non insert values into OUTPUT variable in SQL Server - Today, one of my colleague was asking how to get the non inserted values from the insert statement to output…
• How to track SQL Server Database connections using T-SQL - One of my friend was asking to get him information from SQL Server to troubleshoot some performance issues. My initial…
• How to identify non-indexed foreign keys in SQL Server - Here is a script to identify foreign keys without any supporting index in SQL Server. The script is developed on…
• New enhancement in SSMS in SQL Server - SSMS in SQL 2016 has come up with lots of new features. One of the features is enhanced scrollbar in…
• How to identify the referenced objects of an object in SQL Server - Here is a very small script that helps you to identify the referenced objects of an object in SQL Server.…
• Cycle Clipboard ring in SQL Server Management Studio - “Cycle Clipboard” or “CTRL + SHIFT + V” is a short cut operation for pasting the items in the clipboard.…
• HASHBYTES for a large string in SQL Server - HASHBYTES function in SQL Server is used to hash the input using the algorithm specified. This is a very efficient…
• Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32). - Hello friends, I don’t know what made me to write a blog, either it could be because i sit next…
• Its always important to look at the License type of your SQL Server - Its always important to look at the License type of your SQL Server!!! Problem Statement: We recently had an issue…
• Extending sp_helpindex to get more useful information about index in SQL Server - This post is to help people who had tough time to get information about index and its included columns in…
• How to extract ONLY numbers from a string in SQL Server - Here is a script to extract *ONLY* numbers from a string. DECLARE @Sample TABLE(string VARCHAR(2000)); INSERT INTO @Sample Values( 'hhjjj12345hhhkk'),('-7655'),('asd5-5dffgdfg105'),('hhjjj12345hhhkk'),('a1b2c3d4ee55fff666gggg7777h'),('Al1')…
• How to replace first occurrence of a word in a sentence in SQL Server - Have you ever thought of How REPLACE is working? It just replaces all the occurrences of a word in the…
• The identifier ‘source column name’ cannot be bound. Only target columns and columns in the clause scope are allowed in the ‘WHEN NOT MATCHED BY SOURCE’ clause of a MERGE statement. - Problem Statement: When you want to have a condition in a MERGE with source and target for WHEN NOT MATCHED…
• Temporary table caching in SQL Server - Caching of Temporary table is an interesting topic but not appreciated/recognized one in SQL Server due to several reasons. First…
• ALTER DATABASE failed because a lock could not be placed on database ‘DBNAME’. Try again later. in SQL Server - At times, we may need to take a database offline. And there is a chance that we encounter an error…
• The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values. - One of my colleague was facing an issue while inserting 2000 values to a table variable as below: DECLARE @EMPLOYEEDETAILS…
• Could not load assembly ‘Microsoft.SqlServer.Connectioninfo, Version=10.0.0.0, Culture=netural, PublicKeyToken=89845dcd8080cc91’ or one Of its dependencies. The system cannot find the file specified. - Problem Statement: When trying to migrate SQL Server 2008 R2 to SQL Server 2016, we identified an issue with the…
• SQL Server – Count table rows for all databases using sp_MSforeachdb and sp_MSforeachtable - This post is inspired from one of other post in SQL Server Geeks by Ahmad Osama. http://www.sqlservergeeks.com/sql-server-sp_spaceused-returns-wrong-count/ I would like…
• CREATE OR ALTER in SQL Server - One of my colleague was asking a follow up question on my earlier post DROP IF EXISTS – A handy…
• How do we cleanup plan cache in SQL Server? - At times, we may need to cleanup plan cache in SQL Server. I have come across situations where I need…
• Query Store in SQL Server 2016 – Overview - QueryStore feature in SQL Server 2016 is very helpful for any SQL Server resource to identify and troubleshoot the performance…
• DROP IF EXISTS – A handy feature in SQL Server 2016 - Microsoft has introduced a handy cool feature in SQL Server 2016 called DROP IF EXISTS for objects. Applies to :…
• How to identify tables without Primary Key in SQL Server - To identify tables without primary key in SQL Server. SELECT OBJECT_SCHEMA_NAME( OBJECT_ID ) AS SCHEMANAME, NAME AS TABLENAME FROM SYS.TABLES…