Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,017,613 hits
  • Select GETDATE()

    November 2022
    M T W T F S S
     123456
    78910111213
    14151617181920
    21222324252627
    282930  

Posts Tagged ‘SQL’

Script : Setup email notification when blocking occurs

Posted by blakhani on November 12, 2014


How many times you have faced blocking situation and you are always late to take action. 

Recently one of my friend asked if there is anyway to get automatic email when blocking appears in SQL Server. There is no default mechanism in SQL Server to send such emails. I have asked my friend to create a SQL Agent Job. Here are the information which can be queried and notified.

  • How long blocking exists?
  • Which are the queries/SPs creating blocking?
  • What is the statement causing blocking.

What is needed to run below script?

  1. Database mail should be configured to send emails. In my example below I have a profile configured called “Database Mail” and I am sending mail to myself.
    1. Create Agent Job to run this script on periodic basis (5 min, 10 min or whatever you like)
/*
Discialmer:
The sample scripts are provided AS IS without warranty of any kind. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. 
In no event I shall be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, 
or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation. */
SET NOCOUNT ON -- Checked for currenlty running queries by putting data in temp table SELECT s.session_id ,r.STATUS ,r.blocking_session_id ,r.wait_type ,wait_resource ,r.wait_time / (1000.0) 'WaitSec' ,r.cpu_time ,r.logical_reads ,r.reads ,r.writes ,r.total_elapsed_time / (1000.0) 'ElapsSec' ,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, ( ( CASE r.statement_end_offset WHEN - 1 THEN Datalength(st.TEXT) ELSE r.statement_end_offset END - r.statement_start_offset ) / 2 ) + 1) AS statement_text ,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text ,r.command ,s.login_name ,s.host_name ,s.program_name ,s.host_process_id ,s.last_request_end_time ,s.login_time ,r.open_transaction_count INTO #temp_requests FROM sys.dm_exec_sessions AS s INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st WHERE r.session_id != @@SPID ORDER BY r.cpu_time DESC ,r.STATUS ,r.blocking_session_id ,s.session_id IF ( SELECT count(*) FROM #temp_requests WHERE blocking_session_id > 50 ) <> 0 BEGIN -- blocking found, sent email. DECLARE @tableHTML NVARCHAR(MAX); SET @tableHTML = N'<H1>Blocking Report</H1>' + N'<table border="1">' + N'<tr>' + N'<th>session_id</th>' + N'<th>Status</th>' + N'<th>blocking_session_id</th><th>wait_type</th><th>wait_resource</th>' + N'<th>WaitSec</th>' + N'<th>cpu_time</th>' + N'<th>logical_reads</th>' + N'<th>reads</th>' + N'<th>writes</th>' + N'<th>ElapsSec</th>' + N'<th>statement_text</th>' + N'<th>command_text</th>' + N'<th>command</th>' + N'<th>login_name</th>' + N'<th>host_name</th>' + N'<th>program_name</th>' + N'<th>host_process_id</th>' + N'<th>last_request_end_time</th>' + N'<th>login_time</th>' + N'<th>open_transaction_count</th>' + '</tr>' + CAST(( SELECT td = s.session_id ,'' ,td = r.STATUS ,'' ,td = r.blocking_session_id ,'' ,td = r.wait_type ,'' ,td = wait_resource ,'' ,td = r.wait_time / (1000.0) ,'' ,td = r.cpu_time ,'' ,td = r.logical_reads ,'' ,td = r.reads ,'' ,td = r.writes ,'' ,td = r.total_elapsed_time / (1000.0) ,'' ,td = Substring(st.TEXT, (r.statement_start_offset / 2) + 1, ( ( CASE r.statement_end_offset WHEN - 1 THEN Datalength(st.TEXT) ELSE r.statement_end_offset END - r.statement_start_offset ) / 2 ) + 1) ,'' ,td = Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') ,'' ,td = r.command ,'' ,td = s.login_name ,'' ,td = s.host_name ,'' ,td = s.program_name ,'' ,td = s.host_process_id ,'' ,td = s.last_request_end_time ,'' ,td = s.login_time ,'' ,td = r.open_transaction_count FROM sys.dm_exec_sessions AS s INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st WHERE r.session_id != @@SPID AND blocking_session_id > 0 ORDER BY r.cpu_time DESC ,r.STATUS ,r.blocking_session_id ,s.session_id FOR XML PATH('tr') ,TYPE ) AS NVARCHAR(MAX)) + N'</table>'; EXEC msdb.dbo.sp_send_dbmail @body = @tableHTML ,@body_format = 'HTML' ,@profile_name = N'Database Mail' ,@recipients = N'blakhani@outlook.com' ,@Subject = N'Blocking Detected' END DROP TABLE #temp_requests

Here is the sample email which I received when I caused blocking and executed the script.

if you want to use it, you need to create a SQL Agent Job which would run the script on periodic basics and send email.

Please modify as per your requirement/environment.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in Script, SQL Server | Tagged: , , , , , | 30 Comments »

    Tips and Tricks : Why TempDB files lesser than Configured?

    Posted by blakhani on September 16, 2014


    TempDB is one of the special system database which has special characteristics. Recently I was trying to simulate a problem and demonstrate the solution to tempdb PFS page contention. While doing something I realized that number of tempDB files were only 2 as opposed to 4 which I configured.

    Here is the query which I used to find configured files vs actual files.

    use master
    select    name, physical_name 
    from    sys.master_files
    where    database_id = 2
    go
    use tempdb
    go
    Select    name,physical_name 
    from    sys.database_files
    

     

    Then I looked into the SQL Server ERRORLOG and found below messages during startup.

    2014-09-16 17:45:56.330 spid11s      Starting up database ‘tempdb’.

    2014-09-16 17:45:57.380 spid11s      Error: 5123, Severity: 16, State: 1.

    2014-09-16 17:45:57.380 spid11s      CREATE FILE encountered operating system error 32(The process cannot access the file because it is being used by another process.) while attempting to open or create the physical file ‘E:\TempDB\AnotherTempDBFile.ndf’.

    2014-09-16 17:45:57.380 spid11s      Error: 5123, Severity: 16, State: 1.

    2014-09-16 17:45:57.380 spid11s      CREATE FILE encountered operating system error 32(The process cannot access the file because it is being used by another process.) while attempting to open or create the physical file ‘E:\TempDB\AnotherTempDBFile.ndf’.

    Why this happened? Well. I had done some goof-up and pointed two instances to use same file. Which means that it is expected behavior that if tempdb database is able to initialize the primary files, it would come up and use only two files. This would also happen if there is a problem with the location of newly added files.

    2014-09-16 15:31:14.290 spid11s      Starting up database ‘tempdb’.

    2014-09-16 15:31:14.890 spid11s      Error: 5123, Severity: 16, State: 1.

    2014-09-16 15:31:14.890 spid11s      CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘E:\TempDB\AnotherTempDBFile.ndf’.

    2014-09-16 15:31:14.890 spid11s      Error: 5123, Severity: 16, State: 1.

    2014-09-16 15:31:14.890 spid11s      CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘E:\TempDB\AnotherTempDBFile.ndf’.

    To repro above, I have renamed the folder TempDB to something else. Note that same thing might happen if we have space issues to create new files.

     

    Hope this helps.

     

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in Tips and Tricks | Tagged: , , | 3 Comments »

    Troubleshooting : Error – Incorrect syntax near ‘GO’

    Posted by blakhani on August 28, 2014


    Sometimes we are very comfortable and used to with certain things that if they change, we become nervous and uneasy. I have a lovely daughter and she always greets me when I come back home from office. That one “hello” takes away all my worries and I feel alive. Yesterday she didn’t do that and I was worried. I checked with my wife and she told that there was a mild fever and her mood is little different today. That made me little nervous.

    Same feeling happened when I saw below in management studio of my colleague:

    Msg 102, Level 15, State 1, Line 2

    Incorrect syntax near ‘GO’.

    image

     

    and management studio intellisense feature was also complaining about syntax. “Incorrect syntax near ‘End Of File’. Expecting ‘=’. As per documentation “GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor”

    I went to Tools > Option in Management studio and found that there was a customization done on batch separator. SSMS> Tools > Options >Query Execution > SQL Server > General > Batch Separator.

    Which means if I run with “come” it should work and as expected.

    image

    Perfect! This mystery is solved now. This made me think that can we customize SQLCMD as well. If we look into help of SQLCMD we can see parameter -c cmdend

    image

    Here is the usage of parameter –c . I have used hello as command end parameter and hello works same as go in true sense.

    image

    I hope this clears some confusion about batch separator.

    If you are getting this error while using ExecuteNonQuery in .net program and running script then refer http://blogs.msdn.com/b/onoj/archive/2008/02/26/incorrect-syntax-near-go-sqlcommand-executenonquery.aspx which has a workaround.

    Hope this helps!

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in SSMS, Troubleshooting | Tagged: , , , , , , | 2 Comments »