• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 128
  • Last Modified:

how to extract a number from a MS SQL server string.

hi,

right now we have a tools that detect which MS SQL server queries take most of the tempdb space, however it will only return a string like 'Session id 89',

what is the script to let us extract exactly 89 from the string and how can we pass to the kill statement and email out an alert that spid 89 has been killed and what is the full statement of it ?
0
marrowyung
Asked:
marrowyung
  • 17
  • 11
  • 6
  • +2
3 Solutions
 
Pawan KumarDatabase ExpertCommented:
DECLARE @str AS VARCHAR(100) = 'Session id 89'

SELECT LTRIM(RTRIM(SUBSTRING(@str, CHARINDEX(@str,'Session id ',0) + LEN('Session id') + 1 , LEN(@str))))
GO

Output
---------------
89


DECLARE @str AS VARCHAR(100) = 'Session id 121'

SELECT LTRIM(RTRIM(SUBSTRING(@str, CHARINDEX(@str,'Session id ',0) + LEN('Session id') + 1 , LEN(@str))))

Output
---------------
121
0
 
Ryan ChongCommented:
or you can simply try Replace function, like:
select 'Session id 89' oldStr, ltrim(replace('Session id 89', 'Session id', '')) newStr

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, REPLACE function is much better option. You can even use it for the KILL command:
SELECT REPLACE('Session id 89', 'Session id', 'KILL')

Open in new window

But I would be very careful with killing the session. Sessions are recycled by SQL Server engine so you can even kill a session 89 that wasn't anymore what you thought. Once more, be very carefully with that and review if is this that you really want.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Pawan KumarDatabase ExpertCommented:
The complete syntax for KILL command is KILL 'session_id';   KILL can be used to terminate a connection, which internally terminates the transactions that are associated with the specified in that session ID.

Adding Kill for my comment..

DECLARE @str AS VARCHAR(100) = 'Session id 89'

SELECT 'KILL ' + LTRIM(RTRIM(SUBSTRING(@str, CHARINDEX(@str,'Session id ',0) + LEN('Session id') + 1 , LEN(@str))))
GO

Output
---------------
KILL 89


DECLARE @str AS VARCHAR(100) = 'Session id 121'

SELECT 'KILL ' + LTRIM(RTRIM(SUBSTRING(@str, CHARINDEX(@str,'Session id ',0) + LEN('Session id') + 1 , LEN(@str))))

Output
---------------
KILL 121
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
Victor,

The monitoring tools will report to us by emailing which queries use too much tempdb log space, report it and then kill it behind the scene.

all,

so if I have to execute the kill command, like kill 89, I have to add one more variable and exec @var?
0
 
Pawan KumarDatabase ExpertCommented:
Which variable?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
The monitoring tools will report to us by emailing which queries use too much tempdb log space, report it and then kill it behind the scene.
And how long it takes from the report until the kill command? Are you aware that in the meanwhile the query might complete and there's no process 89 anymore? Or worst, another query is being run by session 89 and has nothing to do with tempdb issue?

so if I have to execute the kill command, like kill 89, I have to add one more variable and exec @var?
I'm not good in guessing and my crystal ball just broke so you'll need to post your process so I know what are you talking about.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"And how long it takes from the report until the kill command? "

it should be how large the tempdb log a query takes/eat before it get kills, this is the whole concept.

"I'm not good in guessing and my crystal ball just broke "

ahaha, once I get the kill xx command, how can I execute it automatically ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
once I get the kill xx command, how can I execute it automatically ?
This depends on your process. We need to know what are you using. Some monitoring tools allows to fire a script when a threshold or condition happens.
0
 
ZberteocCommented:
This is definitely not the right path. There should NEVER be an automated process that kills other processes on the SQL server. This will result in very bad experience for the user if you kill user driven processes and will not solve your resource issues. You should deal with the reason why the process, be it a query or a stored procedure, takes so long and why does it need so much tempdb log. Any KILL will have to be rolled back and that alone could take significant time and resources on the SQL server.

The way to go with this is to prevent the resource consuming processes from happening.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
Victor,

I am using Idera DM for that as this is the only, suppose to be complete tools we purchased.

" Some monitoring tools allows to fire a script when a threshold or condition happens."

I set the tools to check for each queries and see if the tempdb storage it use is inside the alert and warning size range, and it will email us.

then once it is found the planning is email that out and kill it, the shrink the tempdb log accordingly ,then it is done.


Zberteoc,

"There should NEVER be an automated process that kills other processes on the SQL server. "

you are right but we have incident that use up all tempdb log and make SQL server stopped, we have to make this pattern stop EVEN developer forget what we told them to not do sth.

we still need a monitoring/control method to email that out on sth is not right at all. this is our job.

"This will result in very bad experience for the user if you kill user driven processes and will not solve your resource issues."

if I control it, only user experience is not good and it SHOULD NOT use too much tempdb log files per queries , right?

if we don't control it, both user experience and our annual review will be bad and tempdb log full from time to time. it should be better to email that out and someone follow that up.

it should not belongs to bad application design but  human mistake.

"You should deal with the reason why the process, be it a query or a stored procedure, takes so long and why does it need so much tempdb log. "

yeah, so email that out, kill it and then have developer fix it, easy to work that out, right?


the right process AFTER that is the same queries don't come up again as the high tempdb log consuming process, right?
0
 
Pawan KumarDatabase ExpertCommented:
Agree with other experts Killing a session is not a good approach. We should look for alternative approaches.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
yeah, but what is the other approach to control this kind of abnormal break down by some stupid queries ?
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
for resource governor, it only control RAM and CPU, which I considered before. But it don't control tempdb size of each queries.

agree?
0
 
Pawan KumarDatabase ExpertCommented:
I think you should find out queries which are taking large tempDB size and optimize them. Use query from the below URL and get those queries and find out what issues they were facing and fix them.

Which version of the SQL are you using? Do you have multiple tempDB data files ?

Here is a link - https://thesqldude.com/2012/05/15/monitoring-tempdb-space-usage-and-scripts-for-finding-queries-which-are-using-excessive-tempdb-space/
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why can't you just provide more space for tempdb to grow?
What's the size of your tempdb and what's the size of the databases in the respective SQL Server instance?
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
Pawan Kumar Khowal,

"Which version of the SQL are you using? Do you have multiple tempDB data files ?

SQL 2008 with SP4, yes I have many tempdb data file, but some time log file can be too large, but very less chance.

Victor:

"Why can't you just provide more space for tempdb to grow?"

log full because of some troublemaking queries.

e.g. open transaction without closing it.

"What's the size of your tempdb and what's the size of the databases in the respective SQL Server instance?"

is around 17GB with mdf and log.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
is around 17GB with mdf and log.
How big is the user/application database?
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"How big is the user/application database?"

8xxGB.

but the tempdb usage has been stabled for a year but just recently

once incident can bring DB down,
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
17GB tempdb shouldn't be enough to handle a 800GB database. I would say minimum 10% of that (80GB) even by experience 20%-30% should be a better number.
So IMHO you're dealing wrongly with the issue. Just increase the tempdb size and let it go. Databases aren't static and they tend to grow and some grow a lot so you should configure your system to be prepared for that otherwise you'll pass the rest of your life trying to fix things like this.
0
 
ZberteocCommented:
I will say again. By killing a process that doesn't "behave" good you will NOT solve the problem, only trying a workaround solution that is actually bad! What you need is to identify the query/stored procedure and optimize it! It is like trying to put pots around a overfilled draining tub instead of closing the tap. I agree that you need a to monitor parameters in the case some thresholds are surpassed but that should only be for you to identify problems to be SOLVED and not killed.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"that should only be for you to identify problems to be SOLVED and not killed."

ok, I will only monitoring it but not killing it. :):)
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
Victor:

"17GB tempdb shouldn't be enough to handle a 800GB database. "

but that 17GB of tempdb space has been used for a long time and it never grow beyond that.

but just one query make it big .

" Just increase the tempdb size and let it go. "

and the point is, one straight query make the tempdb log totally full and it open transaction without commit, and finally SQL server dies.. so increase the tempdb will solve it?

the tempdb is set to auto growth already.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
but that 17GB of tempdb space has been used for a long time and it never grow beyond that.
Like I said before, databases aren't static objects. You can't expect the same behavior forever otherwise DBAs will only be needed once during the database creation.

the tempdb is set to auto growth already.
Let it grow :)
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"Let it grow :)"

of course, that's hwy I am wondering why people always said a limit...... set a big start up size make sense

" You can't expect the same behavior forever otherwise DBAs will only be needed once during the database creation.
"

that's why I can't see why there is a fixed value it , I watch it until it stabelized.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
hi,

I am combining variable  to a string so that I can email that out.

I do this:

   set @body = 'test ' + cast(@kill as varchar(15)) + '; '  +  @AlertSummary + ' ' +  @Timestamp  +  ' on ' + @Instance;

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = '<profile>',
    @recipients = '<email>',
  @body = @body,
    @subject = 'we are testing '

Open in new window


 how I include the ENTER in the email @body ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
how I include the ENTER in the email @body ?
Like this:
set @body = 'test ' + cast(@kill as varchar(15)) + '; 
           '  +  @AlertSummary + ' ' +  @Timestamp  +  ' on ' + @Instance;

Open in new window

0
 
Ryan ChongCommented:
how I include the ENTER in the email @body ?
tried add Char(13) + Char(10) ?
set @body = 'test ' + cast(@kill as varchar(15)) + '; '  +  Char(13) + Char(10) +  @AlertSummary + ' ' +  @Timestamp  +  ' on ' + @Instance;

Open in new window

0
 
marrowyungSenior Technical architecture (Data)Author Commented:
hi sir,

how about the string I want to cut down is :

"Session id 121 on abc is using 463.6 MB of space in tempdb"

I want to take out 121, this case is much complicated, right?
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
Ryan Chong,

"Char(13) + Char(10) "

this one is the ENTER?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I want to take out 121, this case is much complicated, right?
Did you see my last comment? Just hit the RETURN key exactly in the place you want to have a new line.
Example:
Capture.PNG
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
Victor,

that one is for Ryan, not for you . I got you.

I mean for that long statement, how can take the number out only, not about the ENTER anymore.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ofc the CHAR(13) also works:
Capture.PNG
0
 
Ryan ChongCommented:
>>"Char(13) + Char(10) "
>>this one is the ENTER?

Char(13) + Char(10)  is the carriage return, so it will break your text content with another line.
or what do you mean by ENTER here?
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
Ryan, exactly that, tks.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
tks all,

back to the most important part, how can I take the number out from:

""Session id 121 on abc is using 463.6 MB of space in tempdb""
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Taking in consideration that the number will be always after 'Session id ' then you can use the following formula:
SELECT SUBSTRING(@body, 11, CHARINDEX(' ', @body,12)-11)

Open in new window

NOTE: 11 is the length of 'Session id '
0
 
Pawan KumarDatabase ExpertCommented:
Try..

DECLARE @x AS VARCHAR(1000) = 'Session id 121 on abc is using 463.6 MB of space in tempdb'
SELECT SUBSTRING(@x, CHARINDEX('Session id ' , @x , 1) + DATALENGTH('Session id '), ( CHARINDEX(' on' , @x , 1) - (CHARINDEX('Session id ' , @x , 1)+DATALENGTH('Session id ')) ) ) 

Open in new window


Output
---------------------
(No column name)
121
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
tks all.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 17
  • 11
  • 6
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now