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

Tuesday 15 December 2009

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

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.