Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Posts Tagged ‘SQL’

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: , , , , , | 3 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: , , , , , | 6 Comments »