Quick query to list which Reporting Services report the SQL Agent jobs relate to.

SELECT
  jb.[name] as [job_name]
  , cat.[name] as [report_name]
  , cat.[path] as [report_location]
  , sub.[description] as [sub_desc]
  , sub.[LastRunTime], sub.[LastStatus]
FROM (
    select 
      jb.[name]
      , replace(right(stp.[command]
        , charindex('=ataDtnevE@', reverse(stp.[command]))-1)
          , '''', '') as [SubscriptionID]
    from [msdb].[dbo].[sysjobs] jb
      inner join [msdb].[dbo].[sysjobsteps] stp
      on jb.job_id = stp.[job_id]
    where jb.[category_id] = 100
  ) jb
  JOIN [ReportServer].[dbo].[ReportSchedule] sch 
  ON jb.[SubscriptionID] = sch.[SubscriptionID]
    JOIN [ReportServer].[dbo].[Subscriptions] sub
    ON sch.[SubscriptionID] = sub.[SubscriptionID]
      JOIN [ReportServer].[dbo].[Catalog] cat
      ON sub.[report_oid] = cat.[itemid]

DBA Survivor Name That Caption Contest

Thomas LaRock is running a a “Name That Caption” contest over at his DBA Survivor website promoting his upcoming book DBA Survivor: Become a Rock Star DBA of the same name. 

Here's my entry.

"Hmmm ... if I concentrate hard enough I can't hear that Access Developer outlining his next 'SQL Server' project"

or
"I wonder if anyone would mind if I stick the system views poster here."

Open Transactions with SQL text

Just trying to track down which spid is bashing my poor little SQL machine.

Quite coincidentally Paul Randall just blogged about a new script to show who has open transactions on the server.

Only problem is I want the same information in SQL 2000 land ... :(

Best I can come up with at the moment is,

   1:  select [spid], [blocked], [waittime], [lastwaittype]

   2:    , [waitresource], [dbid], [cpu], [physical_io]

   3:    , [memusage], [login_time], [last_batch], [open_tran]

   4:    , [status], [hostname], [program_name]

   5:  from master..sysprocesses

   6:  where [spid] > 51

   7:    and [blocked] > 0

   8:  order by [blocked], spid

   9:   

  10:  DECLARE @Handle binary(20)

  11:  SELECT @Handle = sql_handle FROM master..sysprocesses 

  12:  WHERE spid = 527  

  13:  SELECT * FROM ::fn_get_sql(@Handle)

SQL Server x32 Perfmon on Windows x64

For me to find later 

http://weblogs.asp.net/guybarrette/archive/2009/06/16/missing-sql-server-x32-performance-counters-on-x64-windows.aspx

Quick snapshot of current activity

This query provides a quick snapshot of current active and waiting sessions in SQL Server 2008.

select er.[session_id], txt.[text], pln.[query_plan]
  , sess.login_name, sess.host_name, db.name as db_name
    , sess.last_request_start_time, sess.last_request_end_time , er.[blocking_session_id], er.[last_wait_type], er.[wait_time]
  , er.[cpu_time], er.[reads], er.[writes], er.[logical_reads]
from sys.dm_exec_sessions sess
  inner join sys.dm_exec_requests er
  on sess.session_id = er.session_id
    left join sys.databases db
    on er.database_id = db.database_id
    outer apply sys.dm_exec_sql_text(er.[sql_handle]) txt
    outer apply sys.dm_exec_query_plan(er.[plan_handle]) pln
where sess.is_user_process = 1

SQL 2008 Database Mail Resend

Try this query to resend failed items in the Database Mail queue.

declare @mailItem int
declare @mailRequest nvarchar(max)
declare @mailRecipient nvarchar(max)
declare @mailSubject nvarchar(255)
declare @mailSendDate datetime
declare @statusMsg nvarchar(400)
declare @rc int

declare curFailedMail cursor fast_forward for
  SELECT [mailitem_id]
  FROM [msdb].[dbo].[sysmail_faileditems]

open curFailedMail
fetch next from curFailedMail into @mailItem

while @@fetch_status = 0 
begin -- fetch loop
    -- Create request xml
    SET @mailRequest = '<requests:SendMail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" '
        + 'xsi:schemaLocation="http://schemas.microsoft.com/databasemail/requests RequestTypes.xsd" '
        + 'xmlns:requests="http://schemas.microsoft.com/databasemail/requests"> '
        + '<MailItemId>' + convert(nvarchar(20), @mailItem) + N'</MailItemId></requests:SendMail>'

    -- put the request on the queue.
    EXEC @rc = msdb..sp_SendMailQueues @mailRequest
    IF @rc = 0
    BEGIN -- resend success
        set @statusMsg = N'Mailitem "' + convert(nvarchar(20), @mailItem) + '" '
            + N', was added to the queue for re-sending.'
        RAISERROR(@statusMsg, 10, 1) WITH NOWAIT
    END -- resend success
    ELSE
    BEGIN -- resend failure
         RAISERROR(14627, 16, 1, @rc, 'send mail') WITH LOG
    END -- resend failure
    
    fetch next from curFailedMail into @mailItem

end -- fetch loop

close curFailedMail
deallocate curFailedMail

A handy pickup for unambiguous date formats

Jamie Thomson demystifies date conversion with different languages with this post.

Interesting to note the change in functionality at the end of his post.

Who is Active?

Adam Machanic has released v9.57 of his epic procedure for SQL Server activity monitoring.

Get it while it's hot.

Virtual File Stats for all

Shock, Horror … yes its a blog post from Phil

Thought I’d post this script so I can find it. It captures the output from fn_VirtualFileStats to a table and runs on ALL versions of SQL Server from 2000 upwards.

For SQL 2000, it just leaves the extra columns provided by SQL 2005 & 2008 as nulls. It also join to sysaltfiles (SQL 2000) and sys.master_files (SQL 2005 & 2008) to return the logical and physical file names.

use [dba]
go

if exists (
    select * from dbo.sysobjects 
    where id = object_id(N'[dbo].[usp_sample_virtualfilestats]') 
      and objectproperty(id,N'isprocedure') = 1
  )
  drop procedure [dbo].[usp_sample_virtualfilestats]
go

set ansi_nulls on
go
set quoted_identifier on
go

create procedure [dbo].[usp_sample_virtualfilestats]

as
begin -- procedure
  set nocount on

  declare @hour_id int
  declare @prodVer varchar(20)
  declare @ver int
  declare @srvr varchar(50)
  -- set hour_id for sample
  set @hour_id = convert(varchar(10), GetDate(), 112) 
                    + convert(varchar(2), GetDate(), 108)
  -- get product version and convert major version to int
  set @prodVer = cast(serverproperty('ProductVersion') as varchar(20))
  set @ver = cast(left(@prodVer, charindex('.',@prodVer)-1) as int)
  -- get server name
  set @srvr = cast(serverproperty('ServerName') as varchar(50))
  -- make sure virtualFileStats table exists
  if object_id('[DBA].[dbo].[virtualFileStats]') is null
  begin
    create table [DBA].[dbo].[virtualFileStats] (
      [server_name] varchar(50) not null
      , [hour_id] int not null
      , [db_id] smallint not null
      , [file_id] smallint not null
      , [logical_name] nvarchar(128)
      , [physical_name] nvarchar(255)
      , [time_stamp] bigint
      , [number_of_reads] bigint
      , [number_of_writes] bigint
      , [bytes_read] decimal(19,2)
      , [bytes_written] decimal(19,2)
      , [io_stall_ms] bigint
      , [io_stall_read_ms] bigint
      , [io_stall_write_ms] bigint
      , [bytes_on_disk] decimal(19,2)
      , constraint [pk_virtualfilestats] primary key clustered (
        [hour_id]
        , [db_id]
        , [file_id]
      )
    ) 
  end

  -- check if running on SQL 2000
  if @ver = 8
  begin
    -- run command using SQL 2000 format
    insert into [DBA].[dbo].[virtualFileStats] (
      [server_name], [hour_id], [db_id], [file_id]
      , [logical_name], [physical_name], [time_stamp]
      , [number_of_reads], [number_of_writes]
      , [bytes_read], [bytes_written], [io_stall_ms]
    )
    select 
      @srvr, @hour_id, vfs.[DbID], vfs.[FileId], alt.[name]
      , alt.[filename], vfs.[TimeStamp], vfs.[NumberReads]
      , vfs.[NumberWrites], vfs.[BytesRead]
      , vfs.[BytesWritten], vfs.[IoStallMS]
    from ::fn_virtualfilestats(-1,-1) as vfs  
      inner join master..sysaltfiles alt
        on vfs.[dbid] = alt.[dbid]
        and vfs.[fileid] = alt.[fileid]
  end    
  else if (@ver = 9) or (@ver = 10)
  begin
    -- run command using SQL 2005 & 2008 format
    insert into [DBA].[dbo].[virtualFileStats] (
      [server_name], [hour_id], [db_id], [file_id]
      , [logical_name], [physical_name], [time_stamp]
      , [number_of_reads], [number_of_writes]
      , [bytes_read], [bytes_written], [io_stall_ms]
      , [io_stall_read_ms], [io_stall_write_ms]
      , [bytes_on_disk]
    )
    select
      @srvr, @hour_id, vfs.[database_id], vfs.[file_id]
      , mf.[name], mf.[physical_name], vfs.[sample_ms]
      , vfs.[num_of_reads], vfs.[num_of_writes]
      , vfs.[num_of_bytes_read], vfs.[num_of_bytes_written]
        , vfs.[io_stall], vfs.[io_stall_read_ms]
        , vfs.[io_stall_write_ms], vfs.[size_on_disk_bytes]
    from sys.dm_io_virtual_file_stats(null, null) as vfs
        inner join sys.master_files mf
        on vfs.[database_id] = mf.[database_id]
        and vfs.[file_id] = mf.[file_id]

  end
  
end -- procedure

go

Enjoy!

Check last backups for all databases

 

Been way to long between posts. Have had my head down busy with work and being secretary of the cricket club.

 

Here's a handy script from my tool chest. It shows the database status, last full backup and last log backup.

select

    db.[db_name]
    , databasepropertyex(db.[db_name], 'status') as [db_status]
    , db.[last_backup] as [last_db_backup]
    , lg.[last_backup] as [last_log_backup]
    , getdate() as [current_date]
from ( 
    select
        convert(varchar (50), substring(db .name,1,50)) as [db_name]
        , max (bs.backup_finish_date) as [last_backup]
    from master..sysdatabases db (nolock)
        left join msdb..backupset bs ( nolock) 
        on db .name = bs.database_name
        and bs.type = 'd'
    group by db. name
) as db
left join (
    select
        convert (varchar(50), substring (db.name,1 ,50)) as [db_name]
        , max(bs.backup_finish_date ) as [last_backup]
    from master..sysdatabases db (nolock )
        left join msdb ..backupset bs (nolock) 
        on db.name = bs .database_name
        and bs.type = 'l'
    group by db.name
) as lg
on db.[db_name] = lg.[db_name]

 

SQL Server 2005 Dynamic Management View Performance Data Warehouse

http://www.codeplex.com/sqldmvstats

Basically a bunch of stored procedures and SQL Agent jobs to gather info and a series of reports to show the data.

Quite interesting is the use of a report as a management interface where you can add/remove databases and disable SQL Agent Jobs. Sort of makes the static report into an application.

SQL Server 2008 Release Date

News is slowly circulating around the web.

 

At the Worldwide Partner Conference Microsoft annouced that the release date for SQL Server 2008 will be February 27, 2008.

 

That's a little over 200 days. Hope everyone that's planning a SQL 2000 -> SQL 2005 migration is happy with this news :)

 

 

Next User Group Meeting

Next Tuesday 17th of July will be the next SQL Server User Group meeting in Victoria.

It promises to be an interesting and highly sought after topic, "Understanding SQL Server Execution Plans"

Head over to the AUSQLUG website for all the details and make sure you register.

SQL Server 2000 SP3a Support Lifecycle

For those of you that haven’t applied Service Pack 4, or upgraded to SQL Server 2005, support for SQL Server 2000 Service Pack 3a expires on the 10^th of July 2007. After this date you’ll need to be on SQL Server 2000 Service Pack 4, or SQL Server 2005 to receive product support from Microsoft.

For product support for each edition of SQL Server 2000 see http://support.microsoft.com/lifecycle/?p1=2852

For detail support for each service pack see http://support.microsoft.com/gp/LifeSupSps#Servers (You’ll need to scroll down to SQL Server)

Super sp_who for SQL 2005

Uses DMVs and shows statement text.

SELECT req.session_id
       , req.blocking_session_id
       , req.cpu_time
       , req.Reads
       , req.writes
       , req.logical_reads
       , sess.login_time
       , conn.last_read
       , conn.last_write
       , sess.host_name
       , conn.client_net_address
       , sess.program_name
       , db_name(req.database_id) As databasename
       , stmt.text As command_text
       , req.status
FROM   sys.dm_exec_requests req
       INNER JOIN sys.dm_exec_connections conn
         On req.session_id = conn.session_id
       INNER JOIN sys.dm_exec_sessions sess
         ON req.session_id = sess.session_id
       CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS STMT
WHERE  req.session_id >= 51

SQL Server User Group : Analysis Services and Many 2 Many

Good presentation by Analysis Services aficionado Darren Gosbell at this evenings SQL User Group meeting.

The presentation covered many to many relationships in Analysis Services. Many to many relationships have been around in the relational DBMS arena for many a year, but it's only been with the 2005 release that it's been possible in Analysis Services.

One of these days I might get around to putting some of my meager Analysis Services skills into practice ... one day ... in my spare time ...

Quote of the Day:
The company's most urgent task is to learn to welcome, beg for, demand - innovation from everyone
--Tom Peters

In case you're wondering what all the emoticons and "Quote..." stuff is about, I'm playing with the latest release of Windows Live Writer. Looks pretty cool so far, but I don't think it'll take pride of place before Outlook or my mobile phone as my blogging tool of choice. It's far too easy in the other tools.

Split function

Another script that I'm forever tracking down.

CREATE FUNCTION [dbo].[fn_Split] (
  @arr AS VARCHAR(MAX)
  , @sep AS CHAR(1)
)
RETURNS TABLE
AS
RETURN

  SELECT n - Len(REPLACE(LEFT(@arr,n),@sep,'')) + 1 AS pos
    ,CAST(Substring(@arr,n,Charindex(@sep,@arr + @sep,n) - n) AS INT) AS Element
  FROM (SELECT @arr AS arr) AS a
    JOIN dbo.nums
     ON n <= Len(@arr)
     AND Substring(@sep + @arr,n,1) = @sep

Script to generate Nums table

Forever tracking this down so I thought I'd post it here so I can get it easily.


-- create table
CREATE TABLE dbo.nums (n INT NOT NULL PRIMARY KEY)
GO
DECLARE @rows INT

SET @rows = 10000
-- prime the table
INSERT INTO dbo.nums VALUES (1)

-- loop around while rows are being inserted
WHILE @@rowcount > 0
BEGIN
  INSERT dbo.nums
  SELECT t.n + x.MaxRowNum   FROM dbo.nums t
    CROSS JOIN (SELECT MAX(n) MaxRowNum FROM dbo.nums) x
  WHERE t.n <= @rows - x.MaxRowNum
END
GO


Listing Report Server Subscription Details

Ever get annoyed by the list of SQL Agent Reporting Services jobs with their non-sensical names. You don't know which ones are active subscriptions and which ones are only once off? Users ring you to say they're not receiving the report they subscribed to, and there is no quick way to see how the subscription is setup.

In Report Manager you can only view the subscriptions for the selected report. To see the schedule details to need to drill down another couple of screens.

Using Management Studio you are still stuck with looking at each individual report and then each subscription. At least when you bring up the properties for the subscription it's all within one dialog.

The following query will list the basic details about all the subscriptions in the ReportServer database. In the next iteration I hope to include more details such as file format, parameters selected, etc...

SELECT
  cat.path AS [ReportServerPath]
  , own.username AS SubscriptionCreatedBy
  , jb.date_created AS [SubscriptionCreatedOn]
  , mod.username AS [SubscriptionModifiedBy]
  , sub.ModifiedDate AS [SubscriptionModifiedOn]
  , sub.Description AS [SubscriptionDescription]
  , sub.EventType AS [SubscriptionType]
  , CASE
      WHEN PATINDEX('%Recurrence%', sub.MatchData) > 0THEN 'Recurring' 
      ELSE 'Once Off'
    END AS [ScheduleType]
  , sch.LastRunTime AS [SubscriptionLastRun]
FROM msdb.dbo.sysjobs jb
  INNER JOIN [ReportServer].[dbo].[Schedule] sch
  ON jb.name = CAST(sch.scheduleid AS VARCHAR(40))
    INNER JOIN [ReportServer].[dbo].[Subscriptions] sub
    ON sch.eventdata = sub.subscriptionid
      INNER JOIN [ReportServer].[dbo].[Catalog] cat
      ON sub.Report_OID = cat.ItemID
      INNER JOIN [ReportServer].[dbo].[Users] own
      ON sub.OwnerID = own.UserID
      INNER JOIN [ReportServer].[dbo].[Users] mod
      ON sub.ModifiedByID = mod.UserID 

Programmatically printing reports ... SP2 = Ahhh!

As I mentioned in a previous post Phills Philosophies: Programmatically printing reports ... SP2 = Ouch! printing reports directly to a printer had some undesirable effects when using SP2.

Well thanks to some assistance from Daniel Reib on the MSDN forums we discovered that not only did I need to set the printing points as per a comment by Keith Walton, I also needed to explicitly set the PageHeight and PageWidth.

I was able to retireve these easily enough via the web service and then based on the page sizing set the Landscape property in the DefaultPageSetting for the document.

My next trick is to intergrate what is now two command line apps (one for printing and one for report mailing) so we have a single application that provides all means of distributing the rendered reports.