• How to start SQL Server with Minimal Configuration - Problem Statement: We recently migrated one of On-prem sql server to SQL server in Azure VM, As part of configuration…
• SOUNDEX() and DIFFERENCE() in SQL Server - SOUNDEX() and DIFFERENCE() functions are string functions in SQL Server. Honestly, I have not seen these functions used in development…
• How to find Table Name from Page ID in SQL Server - Recently, we had a performance issue in one of our testing environment and while we are troubleshooting the issue with…
• CHARINDEX() in SQL Server - CHARINDEX() function returns the position of the string to search in the string to be searched. Syntax: CHARINDEX ( search…
• sp_procoption in SQL Server - sp_procoption procedure set or clears a stored procedure for automatic execution that runs every time an instance of SQL Server…
• CURRENT_TIMESTAMP, GETDATE(), GETUTCDATE() and SYSDATETIME() in SQL Server - CURRENT_TIMESTAMP – Returns the current system date and time. GETDATE() – Returns the current system date and time. GETUTCDATE() –…
• LEFT() and RIGHT() functions in SQL Server - Extracts defined number of characters from the string provided, from left or right depending on LEFT() or RIGHT() function used.…
• LEN() and DATALENGTH() in SQL Server - LEN() function returns the number of characters in the string excluding the trailing spaces, however, DATALENGTH() returns the bytes in…
• IIF() in SQL Server - Today, we are going to see IIF function in SQL Server. Honestly speaking, I have never used this function in…
• QUOTENAME() in SQL Server - QUOTENAME function returns a unicode string with bracket by default. Let us look at a simple example as below. I…
• How to recover “Recover Pending” database in SQL Server - One of my colleague has reached out to me to recover a database which has a state as “Recover Pending”.…
• NULLIF() in SQL Server - NULLIF – (Definition by Microsoft) returns NULL if the two expressions are equal and if the expressions are different then…
• Database ‘dbname’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. - One of my colleagues reported an issue with accessing a database as below. Initially, I thought it was something related…
• The data type ntext cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable. - Exception Message With today’s post, we will deep dive into an error message that one of my friend encountered recently…
• Can NEWID() generate duplicate value in SQL Server? - Earlier, I blogged on updating table with random numbers in SQL Server, later one of my friend asked me how…
• Microsoft SQL Server – Beginners Guide - Introduction: This blog aims beginners on how easily they can learn SQL Server step by step. We are trying to…
• Differences between ISNULL and COALESCE functions in SQL Server - This blog post explains ISNULL and COALESCE functions in SQL Server in a very simple way with examples. We would…
• DENSE_RANK() window function in SQL Server - DENSE_RANK() is an another window function in SQL Server very similar to RANK() in SQL Server.The exception is, it does…
• RANK() window function in SQL Server - This blog explains the usage of RANK window function in SQL Server. RANK function puts a rank number for each…
• Converting Row values into String with delimiter in SQL Server - In this post we are going to see how we can convert the Row values into comma separated String. Different…
• Microsoft SQL Server – A bit of History & Introduction - Microsoft SQL Server is a relational database management system developed by Microsoft early 1980s. The first code was developed in…
• ROW_NUMBER() window function in SQL Server - This blog post explains few methods to generate a running number for a SELECT query. Different SQL versions have different…
• How to recover restoring database in SQL Server - At times, we end up with situations like databases suddenly goes into restoring state and becomes not accessible. Few cases…
• Whats new with TempDB in SQL Server 2016 - Its always quite interesting topic TempDB in SQL Server. Microsoft SQL Server core engine team continuously working on improving the…
• How to find last restart time of SQL Server - Why do we need this information? This is an important piece of information for a SQL Server engineer. I carefully…
• SQL Server – Invalid Urn filter on server level: filter must be empty, or server attribute must be equal with the true server name - Problem Statement: One of my colleague has reported an issue as below while he is trying to restore a database…
• Update() Trigger Function in Table - Update() function returns “True” in any case the Insert/update attempt is successful on the supplied column, the significance of this…
• xp_servicecontrol in SQL Server - Problem Statement: While I was automating a new server preparation step, one of my requirement is to make sure “MSDTC”…
• How to Repair Database in Suspect Mode in SQL Server - Today, one of my colleague reached out to me that one of her database has gone into SUSPECT mode. She…
• ISUNIQUEIDENTIFIER function in SQL Server - Today, I had a requirement to check a value can be converted as uniqueidentifier datatype or not, so quickly come…
• Calendar Table in SQL Server - What is Calendar Table in SQL Server ? Calendar Table is a custom/user table in SQL Server created for ease…
• Error: Changes to the state or options of database ‘dbname’ cannot be made at this time. The database is in single-user mode, and a user is currently connected to it. - One of my colleague had an issue in dropping a database in her testing environment. She was not able to…
• How to read and write backup directory in SQL Server - There is a requirement for me to change the backup directory of a SQL Server instance. Here are few tips…
• WITH RETURNS NULL ON NULL INPUT in SQL Server - While I was going through a review of a sql server function, it is observed there are many checks implemented…
• Palindrome in SQL Server - What is Palindrome? From Books OnLine, “A palindrome is a word, number, phrase, or other sequence of characters which reads…
• Fibonacci series in SQL Server - Today, I noticed that my elder kid was learning about fibonacci series. While observing her, I was just trying to…
• 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…