Solved

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

Posted on 2016-11-08
39
64 Views
Last Modified: 2016-11-15
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
Comment
Question by:marrowyung
  • 17
  • 11
  • 6
  • +2
39 Comments
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41879973
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
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41879974
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41880025
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
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41880043
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41880084
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
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41880088
Which variable?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41880089
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41880102
"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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41880104
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41880586
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41881574
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
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41881602
Agree with other experts Killing a session is not a good approach. We should look for alternative approaches.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41881664
yeah, but what is the other approach to control this kind of abnormal break down by some stupid queries ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41881665
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
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41881700
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41881751
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41881844
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41881846
is around 17GB with mdf and log.
How big is the user/application database?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41881849
"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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41881858
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41882359
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41883192
"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
 
LVL 1

Author Comment

by:marrowyung
ID: 41883197
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41883406
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41886017
"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
 
LVL 1

Author Comment

by:marrowyung
ID: 41887449
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41887467
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
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41887469
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41887544
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41887545
Ryan Chong,

"Char(13) + Char(10) "

this one is the ENTER?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41887552
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41887554
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41887555
Ofc the CHAR(13) also works:
Capture.PNG
0
 
LVL 49

Assisted Solution

by:Ryan Chong
Ryan Chong earned 125 total points
ID: 41887557
>>"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
 
LVL 1

Author Comment

by:marrowyung
ID: 41887558
Ryan, exactly that, tks.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41887559
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
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 41887577
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
 
LVL 18

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 125 total points
ID: 41887583
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
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 41889105
tks all.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now