Checking for a Service in Powershell

Recently I had the need to check if a service was across all our SQL servers. In the interests of efficiency I thought it would be fun to write a little powershell script to do the checking for me.

Here's what I came up with, just pass the service name & machine(s) you want (either singly on in a *.txt file) & it will spit out a list of all with the machine, friendly service name (if installed) or whatever you choose for those where it's not installed (I chose blank).

The cmd would look like...
./Check-ServiceInstalled SERVICENAME "C:\Lists\Servers.txt"
OR
./Check-ServiceInstalled SERVICENAME MACHINENAME

The Code (using Registry)...
$SvcName = $Args[0]
$ServerName = $Args[1]

$ErrorActionPreference = "SilentlyContinue"

$RegPath = "SYSTEM\\CurrentControlSet\\Services\\" + $SvcName

if ($ServerName -like "*.txt*")
{$Servers = Get-Content $ServerName}
else
{$Servers = $ServerName}

ForEach ($Computer in $Servers)
{
$Reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine',$Computer)
$RegKey = $Reg.OpenSubKey($RegPath)
if ($RegKey -eq $null)
{new-object psobject |
add-member -pass NoteProperty Computer $Computer.ToUpper() |
add-member -pass NoteProperty Installed ""
}
else
{new-object psobject |
add-member -pass NoteProperty Computer $Computer.ToUpper() |
add-member -pass NoteProperty Installed $RegKey.GetValue('DisplayName')
}
}

The Code (using gwmi & service)...
$SvcName = $Args[0]
$ServerName = $Args[1]

if ($ServerName -like "*.txt*")
{$Servers = Get-Content $ServerName}
else
{$Servers = $ServerName}

ForEach ($Computer in $Servers)
{
$Svc = Get-WmiObject win32_service -ComputerName $Computer | where {$_.name -eq $SvcName} -ErrorAction SilentlyContinue
if ($Svc -eq $null)
{new-object psobject |
add-member -pass NoteProperty Computer $Computer.ToUpper() |
add-member -pass NoteProperty Installed ""
}
else
{new-object psobject |
add-member -pass NoteProperty Computer $Computer.ToUpper() |
add-member -pass NoteProperty Installed $Svc.DisplayName
}
}

Determining Duration

Recently I had the need to determine the duration of a peice of code. After playing around with a few methods I came up with the below. I did a search & didn't see a lot out on the net regarding determining duration so thought I'd post this.

declare @startdt datetime,
@enddt datetime,
@diff int

set @enddt = '2009-05-12 15:27:29.560'
set @startdt = '2009-05-12 15:27:26.560'

set @diff = (datediff(s, @startdt, @enddt))

select right('00' + convert(varchar(30),@diff/3600),2) + ':' + right('00' + convert(varchar(30),(@diff%3600)/60),2) + ':' + right('00' + convert(varchar(30),@diff%60),2) as [Duration]

More fun with the February 2009 SQL Security Patch

On one of our clustered instances, we had to leave the services running for the February 2009 SQL Security Patch install to initiate successfully. We did not investigate the root cause since it was in the middle of a very busy period of work & we unfortunately didn't have the time. After applying the patch, we started getting complaints from users that their application was unable to login to the DB. Looking at the SQL error logs we saw the following errors...

Login failed for user 'Fake_User'. The user is not associated with a trusted SQL Server connection. [CLIENT: 127.0.0.1]
Error: 18452, Severity: 14, State: 1.

The login in question is a SQL login not Windows so this made no sense to us, on parsing the SQL error logs we noticed this (the instance had been running in MIXED mode)...

Authentication mode is WINDOWS-ONLY.

We changed the instance back to MIXED (SQL Server and Windows Authentication) mode & that resolved the issue. Thought I'd share this since it made for an interesting few minutes.

Searching for a peice of text across a DB

Awhile back I had the need to search for a specific peice of text across a database. After playing with the code I came up with the below stored proc. I placed the SP in our "Scripting/Administation" DB. It takes a param of database & the value to find, you can use wildcards in the value to find. The SP returns the table, column & number of occurences of the searched value.

USE DBAScriptDB
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROC dbo.usp_SearchTextAcrossDB
    @DBToSearch VARCHAR(512),
    @ValToFind VARCHAR(512)
AS
/*    --------------------------------------------------------
        Author  :    
        Date    : 
        Purpose : Search across all Tables/Columns in any DB for a text value. 
                            Wildcards may be used in @ValToFind.
        DB(s)        : DBAScriptDB, all DBs
        Modification History
        Date        Programmer         Description
        --------    -------------     ----------------   
   
*/    ---------------------------------------------------------
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
    SET NOCOUNT ON
    
    DECLARE @errTxt VARCHAR(256),
                    @objName VARCHAR(192),
                    @own VARCHAR(384), 
                    @tbl VARCHAR(384), 
                    @col VARCHAR(384),
                    @sqlSrch VARCHAR(8000),
                    @sqlFld VARCHAR(8000)
    
    CREATE TABLE #Results
        (
            Table_Name VARCHAR(384),
            Column_Name VARCHAR(384),
            Occurrence_Count INT      
        )

    CREATE TABLE #TblFlds
        (
            TblOwner VARCHAR(384),
            TblName VARCHAR(384),
            ColName VARCHAR(384)
        )

    SELECT    @objName = 'dbo.usp_SearchTextAcrossDB'

    SELECT @sqlFld = 
        'INSERT INTO #TblFlds (TblOwner, TblName, ColName)' + CHAR(10) +
        '    SELECT u.name, o.name, ''['' + c.name + '']''' + CHAR(10) +
        '    FROM ' + @DBToSearch + '.dbo.syscolumns c (NOLOCK)' + CHAR(10) +
        '        INNER JOIN ' + @DBToSearch + '.dbo.sysobjects o (NOLOCK) ON c.id = o.id' + CHAR(10) +
        '        INNER JOIN ' + @DBToSearch + '.dbo.sysusers u (NOLOCK) ON o.uid = u.uid' + CHAR(10) +
        '        INNER JOIN ' + @DBToSearch + '.dbo.systypes t (NOLOCK) ON c.xusertype = t.xusertype' + CHAR(10) +
        '    WHERE o.type = ''U''' + CHAR(10) +
        '                AND (t.name = ''varchar'' OR t.name = ''char'' OR t.name = ''nvarchar'' OR t.name = ''nchar'' OR t.name = ''text'' OR t.name = ''ntext'' OR t.name = ''sysname'')' + CHAR(10) +
        '    ORDER BY 1, 2'

    EXEC (@sqlFld)
    
    WHILE EXISTS (SELECT TblOwner FROM #TblFlds)
        BEGIN
            SELECT @own = (SELECT TOP 1 TblOwner FROM #TblFlds)
            
            SELECT @tbl = (SELECT TOP 1 TblName FROM #TblFlds WHERE TblOwner = @own)
            
            SELECT @col = (SELECT TOP 1 ColName FROM #TblFlds WHERE TblOwner = @own AND TblName = @tbl)
            
            DELETE #TblFlds 
            WHERE TblOwner = @own
                AND TblName = @tbl 
                AND ColName = @col
        
            IF @col IS NOT NULL
                BEGIN
                    SET @sqlSrch = 'SELECT ''' + @own + '.' + @tbl + ''', ''' + @col + ''', COUNT(*) FROM ' + @DBToSearch + '.' + @own + '.' + @tbl + ' (NOLOCK) WHERE ' + @col + ' LIKE ''' + @ValToFind + ''''
                    
                    INSERT INTO #Results (Table_Name, Column_Name, Occurrence_Count)
                        EXEC (@sqlSrch)
                END
            
            IF (SELECT COUNT(TblOwner) FROM #TblFlds) <= 0
                BREAK
        END

    IF @@ERROR <> 0
        BEGIN
            SET @errTxt = 'Exception: ' + @objName + ' occured in SELECT Statement'
            RAISERROR(@errTxt, 1, 2)
        END

    SELECT Table_Name, Column_Name, Occurrence_Count 
    FROM #Results 
    WHERE Occurrence_Count > 0 
    ORDER BY 1

    DROP TABLE #TblFlds, #Results
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Proactive File Deletion

One of the things I've been trying to accomplish lately is automating more of the repetative "DBA 101" type tasks as much as possible. Among other things, freeing up this time allows me to concentrate your efforts in areas where there a greater value is added from a DBA's input like performance tuning or security auditing. It also greatly reduces the chance of making mistakes, I'm sure we've all had those "execute... whoops!" moments that just make you feel like a fool, or even worse, make you look like a fool in front of your boss. Recently I took a look at our drives & noticed 2 main areas where I thought a scripted solution made sense and was safe (this will vary according to your environment's needs). The areas I decided to focus on were deleting old Red-Gate SQLBackup log files & deleting old backup files. I'm a big fan of Reg-Gate so I don't want this taken as a jab against them in any way. That being said, the Red-Gate GUI places log files in "C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\" by default with no retention policy, you can set a retention on these logs, but you have to do it on a case by case basis & frankly I'm too efficient (read: lazy) to do it this way, plus I tend to forget to change the setting. We store all our backups in a specific directory named sqlbu (name changed to protect the innocent) so my backup deletion script is geared to look for that directory on all drives & only delete from that location. The backup delete script also has a few filters that will allow backup files to be kept longer than the days to keep indicated. Specifically it will not delete the file if it has "goldcopy" or "donotdelete" in the name or has "do_not_delete" in the path. I created a SQL Agent job on all our servers that runs 2 powershell scripts that do the actual work, the scripts also return how many files were deleted. The scripts both take a param for the days to keep. SQL Agent Job...

1. USE [msdb]   
2. GO   
3.   
4. EXEC msdb.dbo.sp_add_operator @name=N'DBA',    
5.         @enabled=1,    
6.         @weekday_pager_start_time=90000,    
7.         @weekday_pager_end_time=180000,    
8.         @saturday_pager_start_time=90000,    
9.         @saturday_pager_end_time=180000,    
10.         @sunday_pager_start_time=90000,    
11.         @sunday_pager_end_time=180000,    
12.         @pager_days=0,    
13.         @email_address=N'dba@yourcomp.com',    
14.         @category_name=N'[Uncategorized]'  
15. GO   
16.   
17. BEGIN TRANSACTION  
18. DECLARE @ReturnCode INT  
19. SELECT @ReturnCode = 0   
20.   
21. DECLARE @jobId BINARY(16)   
22. EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'00 Old File Cleanup',    
23.         @enabled=1,    
24.         @notify_level_eventlog=0,    
25.         @notify_level_email=2,    
26.         @notify_level_netsend=0,    
27.         @notify_level_page=0,    
28.         @delete_level=0,    
29.         @description=N'The job will delete all *.bak & *.sqb files older than 3 days that do not have “goldcopy” or “donotdelete” in the name & do not have “do_not_delete” in the path.  The job will also delete all RG log files that are older than 7 days in the “C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\” folder.',    
30.         @category_name=N'[Uncategorized (Local)]',    
31.         @owner_login_name=N'sa',    
32.         @notify_email_operator_name=N'DBA', @job_id = @jobId OUTPUT  
33. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback   
34. /****** Object:  Step [Backup Files]    Script Date: 02/25/2009 15:38:25 ******/   
35. EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup Files',    
36.         @step_id=1,    
37.         @cmdexec_success_code=0,    
38.         @on_success_action=3,    
39.         @on_success_step_id=0,    
40.         @on_fail_action=2,    
41.         @on_fail_step_id=0,    
42.         @retry_attempts=0,    
43.         @retry_interval=0,    
44.         @os_run_priority=0, @subsystem=N'CmdExec',    
45.         @command=N'powershell "& C:\WINDOWS\Script\del-oldbackups-single.ps1 3"',    
46.         @flags=0   
47. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback   
48. /****** Object:  Step [RG Logs]    Script Date: 02/25/2009 15:38:25 ******/   
49. EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'RG Logs',    
50.         @step_id=2,    
51.         @cmdexec_success_code=0,    
52.         @on_success_action=1,    
53.         @on_success_step_id=0,    
54.         @on_fail_action=2,    
55.         @on_fail_step_id=0,    
56.         @retry_attempts=0,    
57.         @retry_interval=0,    
58.         @os_run_priority=0, @subsystem=N'CmdExec',    
59.         @command=N'powershell "& C:\WINDOWS\Script\del-rglogs-single.ps1 7"',    
60.         @flags=0   
61. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback   
62. EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1   
63. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback   
64. EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Sat-Weekly',    
65.         @enabled=1,    
66.         @freq_type=8,    
67.         @freq_interval=64,    
68.         @freq_subday_type=1,    
69.         @freq_subday_interval=0,    
70.         @freq_relative_interval=0,    
71.         @freq_recurrence_factor=1,    
72.         @active_start_date=20090225,    
73.         @active_end_date=99991231,    
74.         @active_start_time=30000,    
75.         @active_end_time=235959   
76. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback   
77. EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'  
78. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback   
79. COMMIT TRANSACTION  
80. GOTO EndSave   
81. QuitWithRollback:   
82.     IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION  
83. EndSave:   
84. GO   

Del-OldBackups-Single.ps1...

1. $factor = $Args[0]   
2.   
3. $disks = Get-WmiObject Win32_Volume -filter "DriveType = 3"  
4. $cutoff = (Get-Date).AddDays(-$factor)   
5.   
6. ForEach ($drv in $disks)   
7. {   
8.     $path = $drv.Name + "sqlbu\"  
9.       
10.     if ((Test-Path $path) -eq "True")  
11.     {  
12.         $count = (Get-ChildItem $path -include *.sqb,*.bak -Exclude *goldcopy*,*donotdelete* -recurse |   
13.                     ?{$_.LastWriteTime -lt $cutoff -and !$_.PSIsContainer -and $_.PSPath -notlike "*do_not_delete*"}).Count  
14.         if ($count -eq $null) {$count = 0}  
15.               
16.         Get-ChildItem $path -include *.sqb,*.bak -Exclude *goldcopy*,*donotdelete* -recurse |  
17.         ?{$_.LastWriteTime -lt $cutoff -and !$_.PSIsContainer -and $_.PSPath -notlike "*do_not_delete*"} |  
18.         Remove-Item  
19.         Write-Host "There were" $count "backup files deleted from" $path  
20.     }   
21. }  

Del-RGLogs-Single.ps1...

1. $factor = $Args[0]   
2.   
3. $cutoff = (Get-Date).AddDays(-$factor)   
4.   
5. $path = "C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\"  
6.  
7. if ((Test-Path $path) -eq "True")  
8. {  
9.     $count = (Get-ChildItem $path -include *.log -recurse |   
10.                 ?{$_.LastWriteTime -lt $cutoff -and !$_.PSIsContainer}).Count  
11.     if ($count -eq $null) {$count = 0}  
12.           
13.     Get-ChildItem $path -include *.log -recurse |  
14.     ?{$_.LastWriteTime -lt $cutoff -and !$_.PSIsContainer} |  
15.     Remove-Item  
16.     Write-Host "There were" $count "files deleted"  
17. }  
18. else {Write-Host "There are no files to be deleted"}   

SQL Agent job fails with xXx subsystem failed to load

We had a SQLAgent job that runs cmdexec fail. Looking at the job history, the following error was present "The CmdExec subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended). The step failed.". Looking in the SQLAgent log the below errors were reported at startup [125] Subsystem 'SSIS' could not be loaded (reason: The specified module could not be found) [125] Subsystem 'ANALYSISCOMMAND' could not be loaded (reason: The specified module could not be found) [125] Subsystem 'ANALYSISQUERY' could not be loaded (reason: The specified module could not be found) [125] Subsystem 'QueueReader' could not be loaded (reason: The specified module could not be found) [125] Subsystem 'Merge' could not be loaded (reason: The specified module could not be found) [125] Subsystem 'Distribution' could not be loaded (reason: The specified module could not be found) [125] Subsystem 'LogReader' could not be loaded (reason: The specified module could not be found) [125] Subsystem 'Snapshot' could not be loaded (reason: The specified module could not be found) [125] Subsystem 'CmdExec' could not be loaded (reason: The specified module could not be found) [125] Subsystem 'ActiveScripting' could not be loaded (reason: The specified module could not be found) After a little research we found that the subsystems (a collection of DLLs) loaded by SQLAgent are stored in a table named "dbo.syssubsystems" in msdb. The entries in dbo.syssubsystems pointed to a location that did not exist. A search for one of the DLLs quickly revealed the actual location of the "missing" DLLs, in our case same drive, but a different folder. Once we had the true location, we updated dbo.syssubsystems.subsystem_dll to reflect to correct location (see script below). After restarting SQLAgent the previously logged errors were not present & the job was able to complete successfully. /* Allow updates to system tables */ sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE /* Update to correct path */ UPDATE msdb.dbo.syssubsystems SET subsystem_dll = REPLACE(subsystem_dll,'C:\Program Files\Microsoft SQL Server\MSSQL.1','C:\Program Files\Microsoft SQL Server\MSSQL.2') FROM msdb.dbo.syssubsystems WHERE subsystem_dll LIKE 'C:\Program Files\Microsoft SQL Server\MSSQL.1%' /* Dis-allow updates to system tables */ sp_configure 'allow updates', 0 RECONFIGURE WITH OVERRIDE /* Verify update */ SELECT * FROM msdb.dbo.syssubsystems

July 2008/Febuary 2009 SQL Security Patch – Known Issues and Resolutions

After going through the last few SQL Patches, I thought it would be usefull to compile a list of the top errors encountered and their fixes.
Error # in Summary.txt: 29506 Description: MSP Error: 29506 SQL Server Setup failed to modify security permissions on file xxx.xxx for user xxx. To proceed, verify that the account and domain running SQL Server Setup exist, that the account running SQL Server Setup has administrator privileges, and that exists on the destination drive.
Fix: See the following MS Article KB916766. Notes: check on the file level, look for oddly named or abnormal modified dates as a starting place. Security must be granted at the individual file level.

Error # in Summary.txt: 11032 Description: Unable to install Windows Installer MSP file
Fix: A folder named FTData is not in the expected path, a search of the patch install log for "ERROR: FTE: InstallFTERef: Fail to create FTERef file:" (no quotes) will reveal the expected path. Create the folder in this pattern “F:\MSSQL.1\MSSQL\FTData” changing the path to match your install & what shows in the patch install log, when install complete copy all files to the existing FTData folder (for example) “F:\Microsoft SQL Server\MSSQL.1\MSSQL\FTData” and delete the path\folder you created.

Error #: N/A Description: User has lost custom permissions to SSIS after patching. i.e. “Connect to SSIS Service on machine failed: Access is denied”
Fix: Patch hjorks the custom perms we have setup for SSIS. Recreate the perms according to the following post Connecting to a Remote SSIS Server with Access Is Denied Error.

Error #: N/A Description: Patch installs & reports success, but, one or more nodes in the cluster are un-patched (i.e. still on previous build)
Fix: The problem occurs when the "Windows Installer" service is set to manual & is not running. Start the service & re-run the patch on the un-patched node(s). You should be prompted to reboot, if not, reboot manually. After reboot check @@version on the node(s) in question.