• How to identify the list of folders and files which has been modified on given date using SQL Server - In some situations you may face scenarios like “you need to know all the files and folders modified recently or…
• @@ERROR & @@TOTAL_ERRORS in SQL Server - @@ERROR This variable is used for a basic error handling mechanism in SQL Server earlier days (before SQL 2005). This…
• @@CONNECTIONS & @@MAX_CONNECTIONS in SQL Server - @@CONNECTIONS It returns the total number of connections that includes both successful and failed made to SQL Server since it…
• How to script out ALL or FEW SQL Server Jobs using SSMS - Today, I have come across a requirement to generate the sql script for SQL Server Jobs (only few out of…
• CONCAT_WS in SQL Server - CONCAT_WS is a new function introduced from SQL Server 2017. CONCAT_WS denotes concatenate the strings with a separator. The syntax…
• How to remove all attributes from (X)HTML tags in SQL Server - Requirement Statement This post is to explain a quick and brilliant way of removing attributes from (X)HTML tags in SQL…
• REPLACE, REPLICATE and REVERSE functions in SQL Server - REPLACE in SQL Server Replace function replaces a substring with another substring in a string. Let us quickly look at…
• How to identify the CPU utilization in SQL Server - Using ring buffers ring buffers is a good way to get the CPU utilization as above. Please note, the above…
• sp_databases in SQL Server - Today’s post will introduce a new system procedure sp_databases that helps If you want to know the databases and its…
• Error Message: “The database could not be exclusively locked to perform the operation.” in SQL Server - Recently we encountered an error message as below while doing a rename database. So, let us look at the steps…
• Warning Message in SQL Server: “Database name ‘tempdb’ ignored, referencing object in tempdb.” - Today, let us quickly try to understand an error message as below. “Database name ‘tempdb’ ignored, referencing object in tempdb.”.…
• Dark Theme in Azure Data Studio (ADS) - I always prefer Dark Theme possibly for almost all cases. This post simply explains the way to make ADS to…
• The tipping point and covering indexes - So you’re tuning a slow query and you create a new index to eliminate a table or clustered index scan. …
• COUNT and COUNT_BIG functions in SQL Server - Todays post, we will quickly see the two important functions count and count_big in SQL Server. Both functions return the…
• Error Message : The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. - Error Message: The query processor ran out of internal resources and could not produce a query plan. This is a…
• UPPER and LOWER functions in SQL Server - UPPER and LOWER are strings functions in SQL Server. Both function accepts character expression to be converted. UPPER converts all…
• STR() function in SQL Server - Today, we are going to see the function – STR in SQL Server. Syntax: STR ( float_expression [ , length…
• sp_settriggerorder in SQL Server - Today, we are going to see a very interesting system procedure in SQL Server – sp_settriggerorder. At times, we will…
• 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:  …
• 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…