Link to home
Start Free TrialLog in
Avatar of marrowyung
marrowyung

asked on

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 ?
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

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
Avatar of Ryan Chong
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

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.
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
Avatar of marrowyung
marrowyung

ASKER

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?
Which variable?
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.
"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 ?
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.
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.
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?
Agree with other experts Killing a session is not a good approach. We should look for alternative approaches.
yeah, but what is the other approach to control this kind of abnormal break down by some stupid queries ?
for resource governor, it only control RAM and CPU, which I considered before. But it don't control tempdb size of each queries.

agree?
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/
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?
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.
is around 17GB with mdf and log.
How big is the user/application database?
"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,
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.
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.
"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. :):)
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.
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 :)
"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.
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 ?
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

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

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?
Ryan Chong,

"Char(13) + Char(10) "

this one is the ENTER?
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:
User generated image
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.
Ofc the CHAR(13) also works:
User generated image
SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ryan, exactly that, tks.
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""
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
tks all.