Tuesday, July 5, 2011

View Reporting Services subscriptions and run them ad hoc

In some Reporting Services environments, subscriptions are extensively used to distribute business data. This results in 10's or even 100's or subscriptions. Its quite useful to have a view of these subscriptions and the associated sql agent jobs that actually run these. The thing is these jobs have GUID's as names. Very human unfriendly. There are many reasons I'd want to see this, for example, whne last was a subscription run? One of the main reasons I'd want to see a job linked to a subscription is if I wanted to run a subscription manually. I could of course edit the schedule of the subscription, or create a once off schedule, but that is quite tedious.

I've written some code to 1st of all view Subscriptions in a friendly way. This selects from 3 tables in the ReportServer database and shows the subscriptions and the associated job name. This is the data I display in my 1st Reporting Services report, i.e. show me all subscriptions and the associated job names, and some other data.

I then have a 2nd reporting services report to actually run the SQL agent job that runs the subscription and show the results of the job execution. So I'm using a RS report to execute a SQL agent job. From the 1st report I pass the job_name value to the 2nd report as an input parameter. This parameter is used to start a sql agent job, using the system stored procedure sp_start_job. I then wait for 30 seconds and get the last execution value of that job from sysjobhistory.

Herewith the code:
Select to show subscriptions and populate 1st report:

SELECT
c.Name AS ReportName
, rs.ScheduleID AS JOB_NAME
, s.[Description]
, s.LastStatus
, s.LastRunTime
FROM
ReportServer..[Catalog] c
JOIN ReportServer..Subscriptions s ON c.ItemID = s.Report_OID
JOIN ReportServer..ReportSchedule rs ON c.ItemID = rs.ReportID
AND rs.SubscriptionID = s.SubscriptionID
order by LastRunTime desc


Code to run report subscription based on job_name:

create proc usp_sub @JobName NVarchar(255)
as

begin
exec msdb.dbo.sp_start_job @JobName
WAITFOR DELAY '00:00:30'

select top 1 message, run_date, run_time, run_duration from msdb.dbo.sysjobhistory jh
inner join msdb.dbo.sysjobs j
on jh.job_id = j.job_id
where j.name = @JobName
and step_id = 0
order by jh.instance_id desc
end


The RS reports are very basic, just displaying the results of the above queries in tables. On the main report that shows the subscriptions, I have an action defined on the Subscription name column. The action is to go to the 2nd report which runs the job, taking the job_name (GUID) as an input parameter for the above stored proc.

Wednesday, June 22, 2011

Enable non sysadmins to run a profiler trace for a set period of time and view the results of the trace

My customer is a large corporate with a large Microsoft Development team. This team develops and suports numerous in house applications. These developers quite often need to troubleshoot problems on these apps and them having the ability to run a profiler trace greatly improves their ability to effectively troubleshoot problems. Obviously sysadmin rights on production servers for Developers is not allowed and not a good idea. Even the developers agree. With SQL 2005 and 2008 assigning sufficient permissions to achieve the above is not too difficult.

I've encapsulated everything the developers need into one stored procedure that takes 1 input paramater, run duration for the trace, with a default of 5 minutes.

The developers AD group is given login rights to a production SQL Server that they need to run the trace on. Then the ALTER TRACE Server permission is given to this group. The enables them to create a profiler trace (using sp_trace_create) and start (sp_trace_setstatus) the trace. The trace that's created by the stored procedure is set to capture RPC:Completed, SQL:BatchCompleted events as well as the CPU usage, Duration, Reads and Writes and some other information. The trace writes to a .trc file to the root of the C drive. The run duration input parameter for the sp specifies how long the trace runs for. Once the trace run time is done the trace is stopped and closed. The trace file is then imported into a table in msdb called PerfTrace. This is done using the fn_trace_gettable system function. Finally the PerfTrace table is queried and the results displayed to the user. The code is reasonably self explanatory.


USE [MSDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PerfTrace](
[TextData] [ntext] NULL,
[BinaryData] [image] NULL,
[DatabaseID] [int] NULL,
[TransactionID] [bigint] NULL,
[LineNumber] [int] NULL,
[NTUserName] [nvarchar](256) NULL,
[NTDomainName] [nvarchar](256) NULL,
[HostName] [nvarchar](256) NULL,
[ClientProcessID] [int] NULL,
[ApplicationName] [nvarchar](256) NULL,
[LoginName] [nvarchar](256) NULL,
[SPID] [int] NULL,
[Duration] [bigint] NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[Reads] [bigint] NULL,
[Writes] [bigint] NULL,
[CPU] [int] NULL,
[Permissions] [bigint] NULL,
[Severity] [int] NULL,
[EventSubClass] [int] NULL,
[ObjectID] [int] NULL,
[Success] [int] NULL,
[IndexID] [int] NULL,
[IntegerData] [int] NULL,
[ServerName] [nvarchar](256) NULL,
[EventClass] [int] NULL,
[ObjectType] [int] NULL,
[NestLevel] [int] NULL,
[State] [int] NULL,
[Error] [int] NULL,
[Mode] [int] NULL,
[Handle] [int] NULL,
[ObjectName] [nvarchar](256) NULL,
[DatabaseName] [nvarchar](256) NULL,
[FileName] [nvarchar](256) NULL,
[OwnerName] [nvarchar](256) NULL,
[RoleName] [nvarchar](256) NULL,
[TargetUserName] [nvarchar](256) NULL,
[DBUserName] [nvarchar](256) NULL,
[LoginSid] [image] NULL,
[TargetLoginName] [nvarchar](256) NULL,
[TargetLoginSid] [image] NULL,
[ColumnPermissions] [int] NULL,
[LinkedServerName] [nvarchar](256) NULL,
[ProviderName] [nvarchar](256) NULL,
[MethodName] [nvarchar](256) NULL,
[RowCounts] [bigint] NULL,
[RequestID] [int] NULL,
[XactSequence] [bigint] NULL,
[EventSequence] [bigint] NULL,
[BigintData1] [bigint] NULL,
[BigintData2] [bigint] NULL,
[GUID] [uniqueidentifier] NULL,
[IntegerData2] [int] NULL,
[ObjectID2] [bigint] NULL,
[Type] [int] NULL,
[OwnerID] [int] NULL,
[ParentName] [nvarchar](256) NULL,
[IsSystem] [int] NULL,
[Offset] [int] NULL,
[SourceDatabaseID] [int] NULL,
[SqlHandle] [image] NULL,
[SessionLoginName] [nvarchar](256) NULL,
[PlanHandle] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

USE [MSDB]
GO

CREATE PROC usp_startTrace5
@Runtime VARCHAR(10) = '00:05:00'
AS
BEGIN

DECLARE @RC int, @TraceID int, @on bit, @maxfilesize BIGINT, @stoptime DATETIME, @FileName nvarchar(256), @Dt VARCHAR(20), @VAL BIGINT
SET @Val = 1000
set @on = 1
SET @maxfilesize = 100
SET @Dt = CONVERT(VARCHAR(20), GETDATE(), 105)+'_'+CONVERT(VARCHAR(10), DATEPART(Hh, GETDATE()))+CONVERT(VARCHAR(10), DATEPART(mi, GETDATE()))
--SET @stoptime = DATEADD(mi, 1, GETDATE())
SET @FileName = 'C:\PerfTrace5_'+CAST(SERVERPROPERTY('MachineName') AS NVARCHAR(50))+'_'+@Dt

EXEC @rc = sp_trace_create @TraceID output, 0, @FileName, @maxfilesize, NULL
-- Set the events and data columns you need to capture.
EXEC sp_trace_setevent @TraceID, 10, 1, @On --1 = Text Data, RPC:Completed
EXEC sp_trace_setevent @TraceID, 12, 1, @On --1 = Text Data, SQL:BatchCompleted
EXEC sp_trace_setevent @TraceID, 10, 3, @On --3 = DatabaseID
EXEC sp_trace_setevent @TraceID, 12, 3, @On --3 = DatabaseID
EXEC sp_trace_setevent @TraceID, 10, 12, @On -- 12 = SPID
EXEC sp_trace_setevent @TraceID, 12, 12, @On -- 12 = SPID
EXEC sp_trace_setevent @TraceID, 10, 13, @On -- 13 = Duration
EXEC sp_trace_setevent @TraceID, 12, 13, @On -- 13 = Duration
EXEC sp_trace_setevent @TraceID, 10, 14, @On -- 14 = StartTime
EXEC sp_trace_setevent @TraceID, 12, 14, @On -- 14 = StartTime
EXEC sp_trace_setevent @TraceID, 10, 16, @On -- 16 = Reads
EXEC sp_trace_setevent @TraceID, 12, 16, @On -- 16 = Reads
EXEC sp_trace_setevent @TraceID, 10, 17, @On -- 17 = Writes
EXEC sp_trace_setevent @TraceID, 12, 17, @On -- 17 = Writes
EXEC sp_trace_setevent @TraceID, 10, 18, @On -- 18 = CPU
EXEC sp_trace_setevent @TraceID, 12, 18, @On -- 18 = CPU
EXEC sp_trace_setevent @TraceID, 10, 26, @On -- 26 = ServerName
EXEC sp_trace_setevent @TraceID, 12, 26, @On -- 26 = ServerName

-- Set any filter by using sp_trace_setfilter.
--EXEC sp_trace_setfilter @TraceID, 13, 0, 2, @Val This would filter out any queries that run for shorter than 1 second

-- Start the trace.
EXEC sp_trace_setstatus @TraceID, 1;

WAITFOR DELAY @Runtime

EXEC sp_trace_setstatus @TraceID, 0;
EXEC sp_trace_setstatus @TraceID, 2;

WAITFOR DELAY '00:00:05'

TRUNCATE TABLE msdb.dbo.PerfTrace;
declare @FN VARCHAR(100)
SET @FN = @FileName+'.trc'
--select @FN
INSERT PerfTrace Select * FROM ::fn_trace_gettable(@FN, default);

SELECT TextData, db.name as DatabaseName, Duration, Starttime, Reads, Writes, CPU, SPID
FROM [MSDB].[dbo].[PerfTrace] p INNER JOIN master.sys.databases db
on p.DatabaseID = db.database_ID
WHERE DatabaseID > 4

END

USE MASTER
GO

GRANT ALTER TRACE TO [Domain\group]

USE MSDB
GO

GRANT EXEC ON usp_startTrace5 TO [Domain\group]
GRANT INSERT, SELECT, DELETE ON PerfTrace TO [Domain\group]


Friday, June 3, 2011

Post SQL Installation Configuration Script

After installing SQL Server and applying the latest or required Service pack, there are always a number of tasks that may be completed in a particular environment. These can be quite repetative, especially in large environments with many servers being deployed.

Some of these tasks may include configuring SQL Mail, changing the default configuration for the Model database, setting up operators, etc.

Below is a list of these tasks:
Set up default maintenance plan
Configure SQL Mail
Change recovery model of Model database to simple
Change default file growth settings in Model
Set up SQL Agent notifications and DDSQLTeam operator
Change job history
Set up error logs to cycle and keep 31 logs

Most of these can be accomplished using T-SQL or powershell. The only one I didn't take the time to script was the default maintenance plan.

The rest of the configuration options are in the script below:


-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DEVS SQL Mail',
@description = 'DEVS SQL Mail' ;

-- Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'DEVS',
@description = 'DEVS',
@email_address = 'DEVS@za.com',
--@replyto_address = 'john.doe@domain_name.com',
@display_name = 'DEVS SQL Mail',
@mailserver_name = '10.10.10.1' ;

-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DEVS SQL Mail',
@account_name = 'DEVS',
@sequence_number =1 ;

-- Grant access to the profile to the DBMailUsers role
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'DEVS SQL Mail',
@principal_id = 0,
@is_default = 1 ;

SELECT * FROM msdb.dbo.sysmail_profile
SELECT * FROM msdb.dbo.sysmail_account


-- Alter Model db recovery setting
ALTER DATABASE model
SET RECOVERY SIMPLE
GO

-- Alter Model file growth settings
ALTER DATABASE model
MODIFY FILE
(
Name = modeldev,
Size = 100,
FILEGROWTH = 100
)
GO

ALTER DATABASE model
MODIFY FILE
(
Name = modellog,
Size = 20,
FILEGROWTH = 20
)

-- Add SQL Team operator

USE [msdb]
GO

EXEC msdb.dbo.sp_add_operator @name=N'SQLTeam',
@enabled=1,
@weekday_pager_start_time=90000,
@weekday_pager_end_time=180000,
@saturday_pager_start_time=90000,
@saturday_pager_end_time=180000,
@sunday_pager_start_time=90000,
@sunday_pager_end_time=180000,
@pager_days=0,
@email_address=N'SQLTeam@gcn.local',
@category_name=N'[Uncategorized]'
GO

-- Change Job history
EXEC master..xp_regwrite @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.DEVS\SQLServerAgent', @value_name='JobHistoryMaxRows', @type='REG_DWORD', @value=100000
EXEC master..xp_regwrite @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.DEVS\SQLServerAgent', @value_name='JobHistoryMaxRowsPerJob', @type='REG_DWORD', @value=1000

-- Change number of error logs
EXEC master..xp_regwrite @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.DEVS\MSSQLServer', @value_name='NumErrorLogs', @type='REG_DWORD', @value=31

-- Add Job to cycle error logs
USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 06/01/2011 13:30:22 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Cycle Error Logs',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Cycle Log] Script Date: 06/01/2011 13:30:24 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Cycle Log',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec sp_cycle_errorlog',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20090420,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO



--Enable SQL Agent Mail
USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'DEVS SQL Mail'
GO


Just a bit about each option.
Configure SQL Mail is straightforward. Once you've done that you need to enable SQL Agent to use the SQL Mail profile you've created.

Changing the Model database growth and recovery model settings is quite important in a large busy environment. The default growth out of the box for the data file is 1MB, starting at 1MB. This is NOT ideal and if a database is created without changing this setting, the growth will cause severe fragmentation of the disk and of the mdf. In my particular environment there are 1600 databases. The policy is to have all db's use the SIMPLE recovery model. This means that if a database is created and the default setting is not changed, the recovery will be FULL. This new database log file will grow very big and will not be backed up.

The SQL Agent Operator is also quite obvious. Each SQL Server Agent has jobs that will inevitably fail sometime or another. Notifications need to be sent to a group of DBA's. This is a standard distribution list.

Changing the Job history is not so obvious. The default setting here is 1000 entries in the job history table with a max of 100 per job. This will be overrun very quickly on a busy server with many jobs that run frequently. If you want to see a jobs history, it may have been overwritten. Hence this change. Its two registry keys that are changed using xp_regwrite.

Changing the number of error logs to keep is quite handy when troubleshooting. I like to keep each days worth of SQL error logs in 1 log file. I then keep 31 error logs, so I have a months worth of error logs. I set up a job that cycles the error log at midnight. Neat, tidy, efficient. There is nothing worse than trying to open a huge SQL error log to try and find an error that may have happened 2 weeks ago.