Friday, 18 December 2009

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, 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

Tuesday, 15 December 2009

SQL 2008 Database Mail Resend

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

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

curFailedMail cursor fast_forward for
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="" '
+ 'xsi:schemaLocation=" RequestTypes.xsd" '
+ 'xmlns: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
-- resend success
-- 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

Wednesday, 9 December 2009

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.

Friday, 4 December 2009

Who is Active?

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

Get it while it's hot.