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.