Run Query on Multiple Servers

The ability to query multiple servers instantaneously is a welcome addition to SQL 2008. You achieve this by adding the target servers as a group of Registered Servers. Once this has been done, you right click on the group and select ‘New Query’. This action will open a new query window which is ready to execute across all the servers in your registered servers group.

I find this particularly useful after I arrive in the office as the execution of a single script can interrogate the environments contained within your group and give you immediate results.

For example running the following script on a single server returns jobs that failed on the last occasion they were run.

select h.server as [Server],

j.[name] as [Name],

h.message as [Message],

h.run_date as LastRunDate,

h.run_time as LastRunTime

from msdb.dbo.sysjobhistory h

inner join msdb.dbo.sysjobs j on h.job_id = j.job_id

where --j.enabled = 1 and

h.instance_id in

(select max(h.instance_id)

from msdb.dbo.sysjobhistory h group by (h.job_id))

and h.run_status = 0

When executing using this method of querying multiple servers, the results are returned from each server union-ed into one result set. The result set begins with an additional column which lists the ‘Server Name’, before returning the other columns as normal.

Another example is to immediately identify the versions of multiple servers by using the following server properties:

SELECT

SERVERPROPERTY('productversion'),

SERVERPROPERTY ('productlevel'),

SERVERPROPERTY ('edition')

Run this over multiple servers using query window and the results are returned as follows (note the additional Server Name column and how the results appear as subject of a UNION).

Of course the query window which is spread across multiple servers can also be used for any other ad-hoc action you would perform on a single. A very useful feature indeed!

Oh what job was it? A procedure to search your jobs for a particular string

After regularly querying job data from the system tables I decided to write a very simple procedure that could be used to reduce the time taken rewriting the same TSQL multiple times a day.

The following procedure allows you to pass in string of text which well then be used to return any jobs that contain an instance of that particular string. The procedure will interrogate the job name, the job steps, and the contents of the step.

The results are in order of the job name, followed by each of the job steps that contain the string. Please feel free to amend/improve/expand the procedure, but if possible, let me know what you have done (for my own interest!).

/****** Object: StoredProcedure [dbo].[sp_SearchInJobs] Script Date: 12/08/2009 09:35:14 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROC [dbo].[sp_SearchInJobs]

@texttofind nvarchar(255)

/************************************************************************************************************

**************************************************IMPORTANT**************************************************

** Purpose: Searches names and content of jobs and

** job steps for a provided string of text.

** Notes: from http://www.sqlservercentral.com/blogs/james_howards_sql_blog/

** Created By: James Howard

** Created On: 08th December 2009

************************************************************************************************************/

AS

SET NOCOUNT ON

SET @texttofind='%'+@texttofind+'%'

SELECT j.name AS JobName

,case j.enabled

when 0 then 'No'

when 1 then 'Yes'

else 'UNKNOWN'

END as enabled

,js.step_id AS StepNum

,js.step_name AS StepName

,js.subsystem AS Type

,js.command AS StepContent

,js.database_name AS db

,js.last_run_date AS LastRun

FROM msdb.dbo.sysjobs j

INNER JOIN msdb.dbo.sysjobsteps js

ON j.job_id=js.job_id

WHERE (j.name like @texttofind

OR js.step_name like @texttofind

OR js.command like @texttofind)

ORDER BY j.job_id,js.job_id

GO

Data Collection Job - ‘collection_set_3_upload’ failing…. its a bug!

 

The past couple of hours have seen me frustrated as one of the jobs related to the great Data Collection functionality packed into SQL Server 2008 keeps failing without much error information. Unfortunately the issue appears unanswered on many of the forums in which I searched.

 

The failing job is named ‘collection_set_3_upload’ and it fails on each of the servers on which it has been applied.

 

Following some investigation it seems Microsoft have identified and fixed the bug so can install Cumulative Update 5 for SQL Server 2008 SP1.

 

Information on the problem can be found here:

http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/e1aa9f08-2239-4c0b-b902-b19b8693a799

 

While the actual update can be located here:

http://support.microsoft.com/default.aspx/kb/975977/en-us

 

Right, I’m off to update some forums!

Please, Sir, I want some more… orphaned database users.

Permissions on our development servers are considerably less stringent than elsewhere with developers afforded the freedom to restore backup’s ad-hoc. Occasionally (or more accurately, often) there are redundant credentials in the form of Database users not attached to any logins. This can be incredibly irritating.

I wanted to write something that would identify these orphaned users across the server, quickly and accurately, perhaps even have it scheduled for a periodic review.

The following script will interrogate the users in each database on the server. I select all records from sysusers where the record contains a SID and I then use suser_sname(SID), which returns the login name associated with a security identification number (SID). If the returned value is null, the database user is not attached to a valid login.

I then applied various filters to exclude records I do not want to see eg database and application roles.

A temporary table is then populated with data from each database using the sp_MSforeachdb procedure.

/*************************************************

** Purpose: To retun database users (for each db) orphaned from any login.

** Created By: James Howard

** Created On: 03 DEC 09

*************************************************/

--create a temp table to store the results

CREATE TABLE #temp (

DatabaseName NVARCHAR(50),

UserName NVARCHAR(50)

)

--create statement to run on each database

declare @sql nvarchar(500)

SET @sql='select ''?'' as DBName

, name AS UserName

from ?..sysusers

where (sid is not null and sid <> 0x0)

and suser_sname(sid) is null and

(issqlrole <> 1) AND

(isapprole <> 1) AND

(name <> ''INFORMATION_SCHEMA'') AND

(name <> ''guest'') AND

(name <> ''sys'') AND

(name <> ''dbo'') AND

(name <> ''system_function_schema'')

order by name

'

--insert the results from each database to temp table

INSERT INTO #temp

exec SP_MSforeachDB @sql

--return results

SELECT * FROM #temp

DROP TABLE #temp

I can now use this to schedule the retrieval of the results and will be able to immediately identify orphaned users.

Hopefully someone will find this useful. I’d be interested to hear from anyone who has a more efficient method to achieve the same results.

She Sells PowerShell on The Sea Shore

 

The frequency with which I interact with PowerShell 2 is ever increasing, and the product has become a useful method of reducing the time taken by particular tasks. I use PowerShell to rollout the latest versions of our applications, to create and amend both the structure and data of the database. PowerShell scripts can also be used towards monitoring of your SQL Servers.

 

 

You can find a variety of examples on PowerShell.com, the community for PowerShell People (http://powershell.com/cs/media/g/sql-server/default.aspx). I recommend having a look through the scripts as it will give you an idea of the potential of PowerShell.

 

 

Also well worth a read is an excellent overview piece by Robert Sheldon entitled Creating Windows PowerShell scripts to manage SQL Server 2008 instances (http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1344850_mem1,00.html?track=sy41)

 

 

Finally, there is another simple explanatory piece by Muthusamy Anantha Kumar which, as the title suggests, shows you how to ‘Check your SQL Server using Windows Powershell‘. http://www.databasejournal.com/features/mssql/article.php/3752866/Check-your-SQL-Server-using-Windows-PowerShell--Part-1.htm

 

 

I suggest becoming at least familiar with PowerShell, I can see its rise in popularity and increased usage continuing for some time to come.