- 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,…
- Windows Power Shell Script to run SQL Files in Folder against SQL Server Database - Problem Statement:- At times, we may need to run SQL files present in a folder against SQL Server Database. Since…
- 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…
- On-Demand Performance Test Rig with JMeter - These days it has become necessary to execute the performance test at low cost. This blog details about the how…
- 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…
- Windows Power Shell Script to Find Full File Path Length for all files in Directory - In some cases, we may need to identify the maximum length of full file path in a directory such that…
- 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…
- Windows Power Shell script to Purge/Cleanup Backup & Transaction files - At times, we need to purge backup files (.bak, *.trn) which are older than some x days from the server…
- How to get list of filenames in a folder – Command Shell Script - There may be some situation where you want to find the list of all filenames whose patterns are like “*.txt”,…
- Using Windows Power Shell to get the Server Disk Space Information - To find Drive Names, Total Space, Free Space and Free Space Percentage for the list of Servers specified in computers.txt…
- 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:…
- Toulouse Chapter - This post has nothing to do with SQL Server, but sharing my experiences at Toulouse in France. I stayed near…
- 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…
- Python Dictionaries - A dictionary is a set of unordered key, value pairs. In a dictionary, the keys must be unique and they…
- 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…
- Tuple in Python - Tuples in Python are immutable sequences of random objects. Once created, the objects within them cannot be replaced or removed,and…
- List in python - Python lists such as those returned by the string’s split method are sequences of objects. Unlike strings, lists are mutable…
- Format Strings in Python - We have very powerful method str.format() in python through which you can do variable substitutions and value formatting. Let’s understand…
- Python in SQL Server - Python is an interpreted programming language. Python is a very strong programming language for multi purposes like machine learning/complex computing/…
- Stairway to Python - Topics: Introduction to Python First Program with Python Working with Strings Format Strings in Python Python in SQL Server List…
- Working with Strings in Python - Strings in Python are identified as a continuous set of characters represented in the quotation marks.We can access subset of…
- 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…
- First Program with Python: - Lets begin our first program with some of String operations.Python has a built in String class named “str” with many…
- More about MSDTC - What is MSDTC? MSDTC is a acronynm for Microsoft Distributed Transaction Coordinator which is a windows service to maintain the…
- Introduction to Python - From today, we will start learning the most powerful and dynamic programming language popular for web development,big data, data science,and…
- How to identify NetBIOS Name of a computer? - Its not a SQL Server related, but I found it difficult to identify a way to get the NetBIOS name…
- 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…
- WalkIn Drive at ADP For .Net -
- 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…
- Always Encrypted – A new column level security feature in SQL Server 2016 - Always Encrypted (AE) is a new feature introduced in SQL Server 2016 to secure your data in SQL Server at…
- 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…
- - Hi Everyone, Here is an opportunity to work with a world class organization – ADP. Interested people can mail their…
- 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.…
- Microsoft Free E-Books - Today evening, While I was going through MSDN blog sites, I happen to see such a great collection of Free…
- 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…
