Script Commands to Disable/Enable SQL Agent Jobs

May 20th, 2014 No comments

The script below is a quick way to create disable and enable SQL Agent job commands for existing jobs. It will script out the correct ‘EXEC msdb..sp_update_job’ statements. In this example, the where clause filters for “enabled = 1″, but you can use it create statements based of various job characteristics. We tend to use these when migrating to new servers and want to leave the old server in place.

Selec All Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
/*
Script commands to disable/enable jobs
5/20/2014 JCD
Tested: 2005, 2008 R2 and 2012 SP1
 
You can further filter jobs with other sysjobs columns to specify
an even smaller subset of enabled jobs. Good examples for adding
filers are owner_sid, description and category_id.
 
*/
SET NOCOUNT ON
GO
 
USE msdb
GO
-- Script disable commands for enabled jobs
SELECT 'EXEC msdb..sp_update_job @job_name='''+REPLACE(name,'''','''''')+''',@enabled=0;'
FROM msdb..sysjobs
WHERE enabled = 1;
GO
-- Script enable commands to enabled jobs after disabling. Should be run when creating the disable script
SELECT 'EXEC msdb..sp_update_job @job_name='''+REPLACE(name,'''','''''')+''',@enabled=1;'
FROM msdb..sysjobs
WHERE enabled = 1;
GO
Categories: Administration, DBA, t-sql Tags:

SSRS Active Session Information

March 11th, 2014 No comments

SQL Server Reporting Services (SSRS) showed up to the party in SQL Server 2000.  A really awesome tool for data delivery.  Most SQL Server environments heavily leverage this powerful tool.  Microsoft has baked in some excellent views and tables for report rendering information (dbo.ExecutionLog, dbo.ExecutionLog2 and dbo.ExecutionLog3).  SSRS also supplies a text log with additional details for errors and warnings.  All are  useful when trouble shooting a failure or identifying report execution trends, but what happens if you want to know what is running, now?

Recently, I needed to  identify an active report that was creating a considerable amount of blocking.  We were able to identify a reporting login , verify the  blocking originated from SSRS. I could grab the query being executed, but needed to find the report and user or maybe subscription causing the blocking.  As I mentioned, for the most part the ReportServer database gives us historical information.  I always based most of my queries on the ReportServer database.  In a brief moment of clarity, I realized that maybe just maybe this ReportServerTempDB database might just store ‘temporary’ data about active sessions. Welcome back to the show, Jack!

ReportingServerTempDB(default name) has a table dbo.SessionsData. I used  it  for report path,  session owner  and session creation time. I wanted to also include report name , user name and data source name. I did so by dipping back into the ReportServer database dbo.Catalog, dbo.DataSource and dbo.users.  If the data source had a meaningful name, I could use it to further verify the report was indeed the one connected to the now seriously crippled SQL instance. Previously, we identified the start time of the lead blocker.  I used it to look for a session that started just before that time. Need to allow for the time a user takes to enter parameters if required.

It is also a good script to remind that guy in accounting  not to run the same report 5 times all at the same time. Favorite saying, ” a fool with a tool is still a fool”!

Blah, blah, blah. Enough said! Here is the script:

Selec All Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
/*
SSRS Find Active Session Information
3/11/2014 JCD
Uses standard DB names [ReportServer] and [ReportServerTempDB]
Tested: SQL 2005, SQL 2008 R2 and SQL 2012
*/
SET NOCOUNT ON 
GO
SELECT 
       CONVERT(VARCHAR(20),s.[CreationTime],22) [SessionStart]
	  ,u.[UserName]
	  ,c.[name] [Report]
	  ,d.[name] [DataSource]
      ,s.[ReportPath]
      ,s.[EffectiveParams]
	  ,DATEDIFF(minute,s.[CreationTime],GETDATE()) [RunningTimeMinutes]
FROM  [ReportServerTempDB].[dbo].[SessionData]  as s with (NOLOCK)
JOIN   [ReportServer].[dbo].[Catalog]  as c with(NOLOCK)
	ON c.path= s.reportPath
JOIN [ReportServer].[dbo].[DataSource]  as d with(NOLOCK)
  ON c.ItemID = d.ItemID
JOIN [ReportServer].[dbo].[Users] as u with(NOLOCK) 
	on u.UserId = s.ownerID
ORDER BY s.[CreationTime];
GO

Sample Results

Categories: Administration, DBA, SQL Server, SSRS, t-sql Tags:

SQL Query to script DTUTIL.exe SSIS Export Commands

February 18th, 2014 No comments

There are several reasons why DBAs will need to export/import SSIS packages for upgrades, development environments and backups.  Many use SSMS to do imports and exports of the dtsx packages. It isgreat unless you need to export several or even all the packages. Microsoft supplies a command line tool dtutil.exe to import, export, delete, update and verify SSIS packages.

I wrote the script below to query msdb and script out the dtutil commands. I save the output to text or file. It can be executed windows batch file(.cmd or .bat).  If your environment uses configuration files(.dtsconfig), make sure you grab a copy of those, too.

Selec All Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
USE msdb
GO
SET NOCOUNT ON 
GO 
-- Scripts  DTUTIL commands 
-- Results to Text 
-- Script out commands to execute on target server
-- Output to be used as a windows batch file
-- Tested: SQL 2008 R2  and SQL 2012
-- REMOVE  " ---- " before saving as a .cmd or .bat file
-- JCD 01/29/2013
 
DECLARE @folderlocation VARCHAR(300)
 
-- Set location of folder to store 
SET @folderlocation = 'S:\SSISPackages' 
 
--------------------------
--- Do not alter below ---
--------------------------
 
-- Add Source Servername to folder structure (ex.C:\SSISExport\MyServer\)
SET @folderlocation = @folderlocation + '\'+CONVERT(VARCHAR(50),ServerProperty('SERVERNAME'))+'\'
 
--- Create Folder Structure
SELECT  'MKDIR "'+@folderlocation+'"'
UNION
SELECT 'MKDIR "'+ @folderlocation+RTRIM(foldername)+'"'
from [msdb].[dbo].[sysssispackagefolders]
 
-- Create DTUtil Commands
select 'DTUtil /SQL ' + 
'"\' + CASE WHEN DATALENGTH(RTRIM(F.foldername)) > 0 THEN RTRIM(F.foldername)+'\' ELSE '' END +
P.name + '"' +  ' /SourceS "'+CONVERT(VARCHAR(50),ServerProperty('SERVERNAME'))+'" /Encrypt  FILE;"'+@folderlocation+ 
CASE WHEN DATALENGTH(RTRIM(F.foldername)) > 0 THEN RTRIM(F.foldername)+'\' ELSE '' END + P.name + '.dtsx";0 /Q'
from [msdb].[dbo].[sysssispackages] P inner join [msdb].[dbo].[sysssispackagefolders] F 
on P.folderid = F.folderid
ORDER BY F.foldername
 
GO
Categories: DBA, SQL Server, SSIS, t-sql Tags:

SQLPerformance.com from SQLSentry

July 25th, 2012 No comments

I was pretty excited to see the launch of a new website from SQLSentrySQLPerformance is going to be a great resource for the SQL Server community.  Congrats to SQL Server MVPs Aaron Bertrand and Kevin Kline on the launch of the site.

Read Aaron Bertrand’s announcement  here.

Check out the free tool SQL Sentry Plan Explorer v1.3 . We use it a lot to review queryplans.

Quick Server Info – sys.dm_os_sys_info and SERVERPROPERTY ()

July 19th, 2012 No comments

I needed a quick query to get some server information using  sys.dm_os_sys_info and SERVERPROPERTY ( propertyname ) .  It is budget time so folks want to know some specifics about some of the current infrastructure. This is a nice way to provide a high level look at you infrastructure. Tested on SQL 2005 and SQL 2008 R2.

The SQLOS Team  has a neat article about changes in 2012 for the DMV.

Read more...