Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 1,702,904 hits
  • Select GETDATE()

    December 2019
    M T W T F S S
    « Nov    
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  

Posts Tagged ‘sql server’

Help : How to suppress DBCC TRACEON/OFF messages in the Error Log?

Posted by blakhani on December 9, 2014


This has been asked into a forum recently (here). I remember that there was a way in SQL 7.0 so I searched and found KB 243352. As per KB article trace Flag 2505 can do that. I decided to test in SQL Server 2014 to see if it still works and I was happy to see that it is still working.

Trace flag 2505 works as DBCC TRACEON and Startup parameter as well. Here is the script to test it.

-- recycle the error log
EXEC sp_cycle_errorlog
-- turn on deadlock logging trace flag 
DBCC TRACEON (1222, -1)
GO
-- Read ERRORLOG using filter.
EXEC xp_readerrorlog 0, 1, N'dbcc'
go
-- turn off deadlock logging trace flag 
DBCC TRACEOFF (1222, -1)
GO
--Read ERRORLOG
EXEC xp_readerrorlog 0, 1, N'dbcc'
go
-- Now magic trace flag
DBCC TRACEON (2505, -1)
GO
-- 1204 to check if this is logged.
DBCC TRACEON (1204, -1)
GO
-- read ERRORLOG again
EXEC xp_readerrorlog 0, 1, N'dbcc'
go
-- Turn off magic trace flag
DBCC TRACEOFF (2505, -1)
GO

After 1222 Turn ON

LogDate ProcessInfo Text ----------------------- ------------ ------------------------------------------------------------------------------------------------------------------- 2014-12-09 05:14:03.920 spid59 DBCC TRACEON 1222, server process ID (SPID) 59. This is an informational message only; no user action is required. (1 row(s) affected)
After 1222 Turn OFF LogDate ProcessInfo Text ----------------------- ------------ ----------------------------------------------------------------------------------------------------------------- 2014-12-09 05:14:03.920 spid59 DBCC TRACEON 1222, server process ID (SPID) 59. This is an informational message only; no user action is required. 2014-12-09 05:14:03.970 spid59 DBCC TRACEOFF 1222, server process ID (SPID) 59. This is an informational message only; no user action is required. (2 row(s) affected)


After 2505 and 1204 were ON LogDate ProcessInfo Text ----------------------- ------------ ----------------------------------------------------------------------------------------------------------------- 2014-12-09 05:14:03.920 spid59 DBCC TRACEON 1222, server process ID (SPID) 59. This is an informational message only; no user action is required. 2014-12-09 05:14:03.970 spid59 DBCC TRACEOFF 1222, server process ID (SPID) 59. This is an informational message only; no user action is required.

As we can see in last output, there are no additional entries made in ERRORLOG for 1204. Interestingly there is no entry for 2505 itself. 

Note that this trace flag is not listed in documentation other than SQL 7.0 KB article which I mentioned above. So please don’t shoot me if it doesn’t work in any version other than SQL 7.0 but I don’t see any reason why this functionality would be removed.

Hope this helps.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Posted in SQL Server, Trace Flag | Tagged: , , , , , | 2 Comments »

    Help : How to Downgrade SQL Server Edition (Enterprise to Developer/Standard)

    Posted by blakhani on November 25, 2014


    Life is beautiful when you have good friend around you. If you are regular reader of my blog, you would understand what kind of friends I have. Anyways, as usual, another friend, another interesting question and another blog topic.

    My dear friend called and told that he has enterprise edition Installed on his laptop and it was “accidental" install. His end goal was to use developer edition but “someone” installed enterprise. Since he has done a lot of work in this database and wanted to make sure his data is not lost, he called me for advice. In short, he wanted to change his edition from enterprise to developer with no data loss.

    Well, upgrading edition of SQL Server is easy (my earlier blog) and downgrade is not “so” easy. We are calling it as “downgrade” because its not listed as supported upgrade on books online. If you are using features which are not supported in edition where we want to downgrade (like enterprise to standard) then it’s tricky as we have to remove those features first. Note that Developer and Enterprise are exactly the same so my friend was little safe. I must confess that I am not the inventor of this “downgrade trick”. It is well known and tested by many earlier. I am just writing them down so that it would help someone in future. Standard disclaimer applied on this blog post.

    Before attempting the steps we should take backup of ALL databases (system and user). Make a note of location of various component (system databases, shared components) in operating system. The trick would work only if the paths are not changed.

    • Open Reporting Service Configuration Manager and take a backup the encryption keys.
    • Use SQL Server Management Studio and connect to SQL. We need to run the following query on each database.  

      SELECT
      * FROM sys.dm_db_persisted_sku_features
    • Above DMV will tell us if the database is utilizing any of the “Enterprise only” features (like Partitioning, Compression etc.). If there is any such feature which is not supported on destination editions then we should remove it otherwise database would not come online after last step. In my friend’s case he was using compression but it works in developer edition as well so there was no need to remove that. If there are no rows returned in any database then we are good to move next.
    • Run SELECT @@VERSION and make a note of exact version and build number (like 11.00.3000 –  which is SQL 2012 + SP1) . This is needed because we have to upgrade the newly installed SQL instance to the exact same build later.
    • Stop SQL Server service. Copy all the database files. We need to copy all mdf, ldf and ndf files for system and user databases.
    • Now we can safely uninstall SQL Server. You should take a screenshot of the “Select Features” screen while uninstalling so that you could be certain to install the correct features when installing again in later steps.
    • Reboot, if necessary.
    • Now, install New SQL Server Server instance having SAME name and SAME path as of earlier instance.
    • Since we want to reuse databases, we need to apply SQL Server Patches so that the version matches with what we had earlier. (11.00.3000 in my example)
    • Stop SQL Server service.
    • Take backup of current files to make sure we can revert to this state.
    • Move all databases files back to their original locations. We also need to replace the system database files with the previous one.
    • Now we need to Start SQL Service again.
    • Verify that databases are online and healthy again with new Edition!

    Someone said that this is old trick and used in Sybase as well. And my answer – hmm.. Yeah, I am old now.

    Posted in SQL Server | Tagged: , , , , , | 5 Comments »

    Script : Set up Notification to get blocking tree via email in SQL Server

    Posted by blakhani on November 18, 2014


    In one of my previous blog, I shared a script to generate email notification which would contain query output in HTML format. The query was to find currently running processes in SQL Server. There was a problem with that script. The script only deals with currently “executing” query. So if a query has an open transaction and waiting for either commit or rollback then it would not be shown in email.

    Due to that I wrote another script by getting idea from Ajith Krishnan’s (FaceBook ) script which would also show the open transaction making the life little more easier. Here is the email notification

    In above example there are two blocking chains in SQL Server. Chain # 1 is where 72 is blocked by 68 and 68 is blocked by 59. In second chain, 71 is blocked due to 75.

    Here is the script to get such emails. There are some initial settings required.

    1. Configure Database Mail
    2. Modify script to change the profile name and email address (else I will receive email for blocking alerts on your server)
    3. 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.
    */
    
    
    SET nocount ON; 
    SET concat_null_yields_null OFF 
    
    go 
    
    WITH blockers (spid, blocked, level, batch, lastwaittype,hostname,cmd,dbid,loginname,open_tran,login_time) 
         AS (SELECT spid, 
                    blocked, 
                    Cast (Replicate ('0', 4-Len (Cast (spid AS VARCHAR))) 
                          + Cast (spid AS VARCHAR) AS VARCHAR (1000))         AS 
                    LEVEL, 
                    Replace (Replace (T.text, Char(10), ' '), Char (13), ' ') AS 
                    BATCH, 
                    R.lastwaittype,
                    R.hostname,r.cmd,r.dbid,r.loginame,r.open_tran,r.login_time
             FROM   sys.sysprocesses R WITH (nolock) 
                    CROSS apply sys.Dm_exec_sql_text(R.sql_handle) T 
             WHERE  ( blocked = 0 
                       OR blocked = spid ) 
                    AND EXISTS (SELECT spid, 
                                       blocked, 
                                       Cast (Replicate ('0', 4-Len (Cast (spid AS 
                                             VARCHAR 
                                             ))) 
                                             + Cast (spid AS VARCHAR) AS VARCHAR ( 
                                             1000)) 
                                       AS 
                                           LEVEL, 
                                       blocked, 
                                       Replace (Replace (T.text, Char(10), ' '), 
                                       Char (13 
                                       ), 
                                       ' ') AS 
                                           BATCH, 
                                       R.lastwaittype, 
                                        R.hostname,r.cmd,r.dbid,r.loginame,r.open_tran,r.login_time
                                FROM   sys.sysprocesses R2 WITH (nolock) 
                                       CROSS apply 
                                       sys.Dm_exec_sql_text(R.sql_handle) T 
                                WHERE  R2.blocked = R.spid 
                                       AND R2.blocked <> R2.spid) 
             UNION ALL 
             SELECT R.spid, 
                    R.blocked, 
                    Cast (blockers.level 
                          + RIGHT (Cast ((1000 + R.spid) AS VARCHAR (100)), 4) AS 
                          VARCHAR 
                          ( 
                          1000)) AS 
                    LEVEL, 
                    Replace (Replace (T.text, Char(10), ' '), Char (13), ' ') 
                    AS BATCH, 
                    R.lastwaittype,
                    R.hostname ,r.cmd,r.dbid,r.loginame,r.open_tran,r.login_time
             FROM   sys.sysprocesses AS R WITH (nolock) 
                    CROSS apply sys.Dm_exec_sql_text(R.sql_handle) T 
                    INNER JOIN blockers 
                            ON R.blocked = blockers.spid 
             WHERE  R.blocked > 0 
                    AND R.blocked <> R.spid) 
    SELECT N'' 
           + Replicate (N'|.......', Len (level)/4 - 2) 
           + CASE WHEN (Len (level)/4 - 1) = 0 THEN 'HEAD - ' ELSE '|------ ' END + 
           Cast ( 
           spid AS VARCHAR (10)) + ' ' + batch AS BLOCKING_TREE, 
           hostname, 
           lastwaittype, cmd,dbid,loginname,open_tran,login_time,
           Getdate()                           AS 'RunTime' ,
           level
    INTO #BLOCKERS
    FROM   blockers WITH (nolock) 
    ORDER  BY level ASC 
    
        --Select BLOCKING_TREE,hostname,lastwaittype,time,cmd,dbid,loginname,open_tran from   #BLOCKERS
        --order by level asc
    
        DECLARE @tableHTML NVARCHAR(MAX);
    
        SET @tableHTML = N'<H1>Blocking Tree Report</H1>' + N'<table border="1">' + N'<tr>' + 
        N'<th>Blocking_Tree</th>' + N'<th>hostname</th>' + N'<th>lastwaittype</th>'+'<th>CurrentTime</th>' 
        + N'<th>cmd</th>' 
        + N'<th>dbid</th>' 
        + N'<th>loginname</th>' 
        + N'<th>open_tran</th>' 
        + N'<th>login_time</th>' 
        + '</tr>' + CAST((
    SELECT td = Blocking_Tree,'',
             td =hostname,'',
             td =lastwaittype,'',
             td =RunTime,'',
             td= cmd,'',
             td= dbid,'',
             td= loginname,'',
             td=open_tran,'',
             td=login_time,''
             FROM #BLOCKERS
                 order by level asc
    FOR XML PATH('tr')
                        ,TYPE
                    ) AS NVARCHAR(MAX)) + N'</table>';   
    
    If (select count(*) from #BLOCKERS) > 1
    begin
    
        EXEC msdb.dbo.sp_send_dbmail @body = @tableHTML
            ,@body_format = 'HTML'
            ,@profile_name = N'Database Mail'
            ,@recipients = N'blakhani@outlook.com'
            ,@Subject = N'Blocking Tree Report'        
    end
    
    drop table #BLOCKERS

    Hope this helps.

    Cheers,

    Balmukund

    Posted in Blocking, Script, SQL Server | Tagged: , , , , , | 8 Comments »