June 3, 2020 at 9:02 pm
I am trying to monitor the sql services on couple of instances and found the following script online which kinds of creates tables with sql server services details.
set nocount on
-- Variable to store windows server name
DECLARE @server_name varchar(100)
DECLARE @SQLInstance_name varchar(100)
-- table to store PowerShell script output
CREATE TABLE #output
(line varchar(max) null)
-- Declaring cursor to fetch windows server name
DECLARE server_cursor CURSOR FOR
select distinct LTRIM(rtrim(windowsservername)) as windowsServerName,SQLInstanceName from tbl_sqlcmdb
OPEN server_cursor
FETCH NEXT FROM server_cursor
INTO @server_name, @SqlInstance_Name
WHILE @@FETCH_STATUS = 0
BEGIN
declare @svrName varchar(255)
declare @sql varchar(400)
set @svrName = @server_name
-- Preparing PowerShell Dynamic Statement
set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class win32_service | where {$_.name -like ' + QUOTENAME('*SQL*','''') + '} | select-object Name,state,systemname,startmode,startname | for each{$_.name+''|''+$_.state+''%''+$_.systemname+''*''+$_.startmode+''@''+$_.startname+''!''}"'
-- Inserting PowerShell Output to temporary table
insert #output
EXEC xp_cmdshell @sql
-- Deleting the rows which contains error or has not sufficient data
delete from #output where len(line) < 30
update #output set line = line + '!' where line not like '%!%'
IF (SELECT COUNT(*) FROM #output where line like '%Get-Wmi%') = 0
begin
insert into tbl_serviceStatus(SQLServiceName, servicestatus, windowservername, startmode, startname, InstanceName)
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as SQLServiceName
,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1, (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) ))) as ServiceStatus
--,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1, (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) ))) as WindowsServerName
,@server_name
,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('*',line)+1, (CHARINDEX('@',line) -1)-CHARINDEX('*',line)) ))) as startmode
,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('@',line)+1, (CHARINDEX('!',line) -1)-CHARINDEX('@',line)) ))) as startname
,@SQLInstance_name
from #output
where line is not null and LEN(line) > 30
end
-- Clearing output table
truncate table #output
-- Next windows record
FETCH NEXT FROM server_cursor
INTO @server_name,@SQLInstance_name
END
CLOSE server_cursor;
DEALLOCATE server_cursor;
-- dropping the temporary table
drop table #output
Here is the error below...please suggest the fix.
Msg 537, Level 16, State 3, Line 29
Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.
Msg 537, Level 16, State 3, Line 29
Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.
June 3, 2020 at 9:03 pm
Also, please suggest any idea for sending an alert when sql service stops on any instance..? thanks!
June 3, 2020 at 9:50 pm
To fix problem number 1 (LEFT or SUBSTRING error), lets look at your query for all cases of LEFT or SUBSTRING. That gives us this small chunk of your code:
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as SQLServiceName
,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1, (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) ))) as ServiceStatus
--,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1, (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) ))) as WindowsServerName
,@server_name
,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('*',line)+1, (CHARINDEX('@',line) -1)-CHARINDEX('*',line)) ))) as startmode
,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('@',line)+1, (CHARINDEX('!',line) -1)-CHARINDEX('@',line)) ))) as startname
,@SQLInstance_name
from #outputEDIT - removed a chunk here because it is wrong. Your DELETE and WHERE clause make sure that blank string are handled. What about error strings? If the server doesn't exist, you are inserting an error message from powershell into your table. That could be the problem. Might not hurt to look at the data prior to doing the substrings and see what data exists.
As for the second one, SQL cannot monitor it's own services stopping. Your best bet here is some proper monitoring software on a specific server to watch and make sure the services stay up. Failing that, assuming you can trust the server the above query runs on, you could grab the value of "started" along with the other properties and you would see if it is running now. But this is only valid based on how frequently you run the above script. If it is a run-once script, that isn't helpful. But you could fine tune the powershell to only bring back the "started" property, dump it into a table and then send an alert if "SELECT COUNT(1) FROM <table> WHERE started = false" has more than 0 rows? Won't work if you have a cold failover though.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply