Author: Latheesh NK

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 Server. The solution was given by one of my good friend | guru Yitzhak Khabinsky (in), would like to share it for the benefit of community.

Few interesting ways:

–Delete the style attributes

Declare @tbl TABLE (ID INT PRIMARY KEY Identity(1,1), xhtmlData xml);

Insert into @tbl (xhtmlData) Values
('<p><span style="font-size: 18px;"><strong>Hello World</strong></span></p>'),
('<p> </p>'),
('<h2 style="font-size: 24px; text-align: center;">123456</h2>'),
('<p> </p>'),
('<p class="next-steps-intro" attr= "" style="margin-bottom: 0;"><span style="font-size: 18px;">
<strong>You are at home:</strong></span></p>')

Select * From @tbl
update @tbl set xhtmlData.modify('delete //@*'); 
Select * From @tbl

–Delete the style attributes ONLY for span Tag

Select * From @tbl
update @tbl SET xhtmlData.modify('delete //span/@style');
Select * From @tbl

–Delete multiple attributes for a Tag

Select * From @tbl
update @tbl SET xhtmlData.modify('delete /p/@*[local-name()=("class","attr")]');
Select * From @tbl

I had spend almost an hour with Yitzhak and shared many stuffs around xml/SQL Server etc. In fact, when I contacted , it was a mid night for him and he immediately came for a skype discussion. There are some really interesting things he shared and I requested Yitzhak to share all of them in a form of a blog which would really be a great asset to SQL Server/any tech communities.

With that to conclude this post, we would like to enhance xml support further in SQL Server future releases and to request you all to vote for the below feedback for the same.

https://feedback.azure.com/forums/908035-sql-server/suggestions/38142115-sql-server-vnext-post-2019-and-nosql-functionali

If you enjoyed this blog post, please feel free to share it with your friends!

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 the examples straightaway to understand better.

SELECT REPLACE('SQL Server is a database server', 'sql server', ' SQL SERVER');

Point to be noted is that REPLACE function replaces all occurrences of word to be replaced. If there is a requirement to replace first or any specific range, then REPLACE will not work. There is an old blog post that explains how to replace ONLY the first occurrence as https://sqlzealots.com/2017/05/20/how-to-replace-first-occurrence-of-a-word-in-a-sentence-in-sql-server/

REPLICATE in SQL Server

REPLICATE() function repeats a string to the number of times its specified in the second parameter.

SELECT REPLICATE('SQL | ',2)

REVERSE in SQL Server

REVERSE() function reverses a string passed.

SELECT REVERSE ('SQL Server')

Reverse is a very powerful SQL String function that uses many places. I used this function in one of my earlier post on checking palindrome in SQL Server as below.

https://sqlzealots.com/2020/10/04/palindrome-in-sql-server/

If you enjoyed this blog post, feel free to share it with your friends!

How to identify the CPU utilization in SQL Server

Using ring buffers

DECLARE @ts_now BIGINT = (SELECT cpu_ticks / ( cpu_ticks / ms_ticks )
   FROM   sys.dm_os_sys_info WITH (nolock));

SELECT TOP(256) sqlprocessutilization             AS [SQL Server Process CPU Utilization],
                systemidle                        AS [System Idle Process],
               100 - systemidle - sqlprocessutilization AS [Other Process CPU Utilization],
                Dateadd(ms, -1 * ( @ts_now - [timestamp] ), Getdate()) AS [Event Time]
FROM   
(SELECT 
       record.value('(./Record/@id)[1]', 'int') AS record_id,
       record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')         AS [SystemIdle],
       record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization],
       [timestamp]
        FROM   (SELECT [timestamp],
                       CONVERT(XML, record) AS [record]
                FROM   sys.dm_os_ring_buffers WITH (nolock)
                WHERE  ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
                       AND record LIKE N'%%') AS x
) AS y
ORDER  BY record_id DESC
OPTION (recompile); 

ring buffers is a good way to get the CPU utilization as above. Please note, the above code is being used heavily by many DBAs, I do not really know who is the author of the above script, however, would like to give full credit to the author who drafted it.

Using perfmon

Performance monitor (perfmon) is a built-in tool in windows server to track the system performance and other data points. We can configure perfmon to run on scheduled manner and collect the information as per the requirement. Most of the production servers will be enabled with perfmon to track the performance and it has no or less impact on the server.

Once the data is collected, you can even look at the data through graphana or kibana which would provide us a good data representation.

Using open source monitoring tools

There are many open source monitoring tool in the market which can be used to get the information from server. Few of the tools are explained in the link https://geekflare.com/best-open-source-monitoring-software/

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 sizes in your db server. Its a very handy procedure that helps at times.

Caveat:

  1. If you want to know the size of log and data separately, this procedure cannot be helpful. You may refer “https://sqlzealots.com/2015/01/29/find-the-database-size-logrowtotal-in-sql-server-using-t-sql/” for the same.
  2. If any of databases is having more than 2.15 TB size, then sp_databases may not provide the database_size value as this column is defined as a INT datatype which can hold value upto : 2,147,483,647

If you enjoyed this blog post, please feel free to share it with your friends!

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 that we can use to overcome the issue with this post.

The database could not be exclusively locked to perform the operation.

We were trying to rename a database in one of our lower environment as below and ended up with the error message.

ALTER DATABASE dbname MODIFY NAME = dbname_new

Steps to resolve

  1. Take the database to single_user mode
  2. Rename the database
  3. Take the renamed database to multi_user mode

Script

ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE dbname MODIFY NAME = dbname_new
GO
ALTER DATABASE dbname_new SET MULTI_USER WITH ROLLBACK IMMEDIATE

If you enjoyed this blog post, please share it with your friends!