Prevent creating stored procedures with RECOMPILE hint – Policy Based Management is your friend !

I got motivated to write this post as there was a question on twitter which was like  –

PBM Recompile 1

The user was checking if there was a way to prevent procedures which are created with RECOMPILE hint.

I was pretty sure that Policy Based Management did provide us a Stored Procedure Facet, and there will be some expressions which we can create using the same to ensure that no new stored procedures are created with this hint.

Will I use RECOMPILE hint ever in my stored procedure? That’s a huge ‘It depends’ question. I might not use it within a stored procedure, but at times I might have to use it during run time.

Kendra Little(B/T) has an excellent video on recompile, and you can find it here. [Really nice stuff out there !]

I did reply to the user that I’m positive that there will be some option, but it needs to be tested out .

So, this post is my test case to try and see if we can actually prevent users from creating stored procedures with RECOMPILE hint using PBM .

First of I created a condition using facet stored procedure and added an expression like what is shown in the image below –

PBM Recompile 2

I went ahead and created a policy which used the condition which was created in the first step, and chose evaluation mode as On Change : Prevent

PBM Recompile 3

Note – I’m doing this check for every database and every stored procedures as this is a test case. This can be checked against a single db using another condition and that’s the recommended approach.

Once the policy is created I made sure that its in enabled state.[Apparently nothing will be validated if the policy is not enabled ]

Next step is to try and create a stored procedure with recompile hint. For this purpose I will be using a database called SuperMan (Yes,just for fun).This db got a table called People and it has 3 rows of data.

PBM Recompile 4

I tried to create a simple stored procedure named FindSuperMan which will do a simple SELECT * from the table.This stored procedure will try to force a RECOMPILE hint.

FROM [dbo].[People]

As soon as I ran the command there was an error which said  –

Policy ‘Policy_Prevent_Recompile’ has been violated by ‘SQLSERVER:\SQL\TESTServer\DEFAULT\Databases\SuperMan\StoredProcedures\dbo.FindSuperMan’.
This transaction will be rolled back.
Policy condition: ‘@Recompile != True()’
Policy description: ”
Additional help: ” : ”
Statement: ‘CREATE PROCEDURE dbo.FindSuperMan
FROM [dbo].[People]
Msg 3609, Level 16, State 1, Procedure sp_syspolicy_dispatch_event, Line 65
The transaction ended in the trigger. The batch has been aborted.

What actually happens here is the transaction will be rolled back as it violated the policy which was defined.


This proves the fact that PBM can indeed be used to prevent some conditions which you don’t want to happen in your db/environment.

Thanks for reading and keep watching this space for more !


Moving a database from on-premise to the cloud – Step by Step Walkthrough

Cloud is the future and moving there is now as easy as few mouse clicks.

Today we will see how an On-premise database can be moved to a SQL Server instance running up on a Windows Azure Virtual Machine.There are multiple ways to accomplish this and in this post we will see how this can be achieved using SSMS wizards.

Step 1

I have a database named “MoveMeToCloud”,and this is one who is ready to take the flight and move to the cloud. This is a database which is deployed on my on-premises SQL Server Instance.

The first process is to export this database. For that purpose we will choose Tasks —

Export Data-tier Application


As always an introduction wizard is opened up, and you can proceed by choosing next.(Also can say do not show this again !)


Step 2

In this step we will connect Windows Azure Storage Account and we will export the BACPAC directly to the cloud storage.


When we connect the Azure storage account we will need to provide the account name and the key. Manage Access Keys under the Azure portal(Storage section) will get this info for us.



Step 3

Once the storage account connection is established we can proceed and finish this export activity.


The wizard will do multiple checks, and the process will complete eventually(Provided all the conditions are met).

I will talk about the conditions in a different post.


Step 4

Once the export is completed successfully we can check and verify that the BACPAC is indeed available under Windows Azure Storage Account


Step 5

As the BACPAC is now local to the cloud we can now proceed and do an Import of that file.

We will connect to a Windows Azure Virtual Machine which is running SQL Server 2012 up in Azure to do the import.

We will choose Import Data Tier Application to initiate the import.


We will need to provide the storage account details under the import settings


Once the connection is established correctly we can proceed and choose the database settings


Database settings will allow us to place the data file and log file according to our requirements.

We can proceed and finish the wizard to complete the import activity.



Step 6

Once the import is completed we are good to access the database up in the cloud.

SSMS Cloud

Thanks for reading and keep watching this space for more.

Have you configured Database Mail to utilize multiple SMTP Accounts ?

Lets start discussing about this subject by closely looking at the term Redundancy.  What is redundancy all about ?

Wikipedia defines redundancy as “In engineering, redundancy is the duplication of critical components or functions of a system with the intention of increasing reliability of the system, usually in the case of a backup or fail-safe.”

Technically this is redundancy too  !


That’s a very nice and to the point definition isn’t? Well, very true.

What’s this hype all about redundancy in database mail ? Lets closely look database mail for more details.

When you create a new database mail profile, you have an option to add multiple SMTP accounts. If an account fails when sending an email, the profile uses the next account in the priority list.

Yes, this is some level of redundancy for database mail.


You can set the priority of accounts by moving them up and down.

Adding a new account is a simple process and can be done easily with the GUI.


The ability to configure multiple SMTP accounts comes really handy when your enterprise infrastructure is having multiple SMTP servers as part of redundancy plan.


Incase you do have an opportunity to add multiple SMTP accounts for database mail, then dont think twice. Its always good to have a backup account which will ensure that you will never miss that important alert/email.

Thanks for reading and keep watching this space for more.

The Activity Monitor is unable to execute queries against the server !

I’m generally not a great fan of Activity Monitor which is built in with SSMS,however today I wanted to check some processes details and opened Activity Monitor after a long long time.

Straight away I was struck with this error.

Activity Monitor1

By using the ‘Show Technical Details’ button I tried to see if there is something which I can fix by myself,however there were no much details other than the program details for which I don’t have any visibility.

I was connected to a SQL2005 (9.0.5000) Instance using SSMS 2012 (11.0.3339.0) and was trying to launch Activity Monitor. Ideally this should have worked well without issues as higher version of SSMS should not have any issues connecting working with lower versions of Database Engine.

The error message also says that Activity Monitor is in paused state and use the context menu in the overview pane to resume it.

Activity Monitor2

This resume did not worked and apparently I received the same error message which I received in the beginning.

I tried multiple options, however there is nothing which I could find to get this fixed and start Activity Monitor properly.

Note – Rebooting SSMS unfortunately wont fix any problem permanently here.

I finally decided to use SQL 2008 R2 SSMS 10.50.4000 ( Yes, I had one !) and connected to the instance.

Viola, that worked ! Activity Monitor was just fine this time.

Activity Monitor3

This clearly means that the inability of SSMS 2012 to pull Activity Monitor for a SQL2005 Instance is because of a BUG.

A quick search on connect revealed some items related to this issue. Even though this connect item is dealing with a different version of SSMS and a SQL Engine, the issue seems to be the same.

I also tried connecting SQL2012 SSMS with SQL2012 and SQL2008 R2 instances and launched Activity Monitor successfully. Apparently the issue seems to be floating around with higher version of SSMS connecting to SQL2005 and trying to launch Activity Monitor.

Workaround –  I generally maintain major versions of SSMS on my work machine and this  comes handy during some instances.


Again this is not a mission critical bug which will cause work stoppage, however this should be fixed eventually and I hope SQLServer Product Team will fix this during upcoming SPs.

Thanks for reading and keep watching this space for more.

Linked Server created using a script is missing Product name and Data source fields !

Today I will discuss about an issue which was observed while creating a linked server via script.

The existing linked server was scripted out from a SQL2005 instance and the same script was used to create a linked server under a SQL2012(SP1/CU2) instance.

This a common procedure to follow if you are doing a side by side upgrade of SQLServer and ensure you have all objects in place under the new server.

After running the script the linked server was created successfully, however important details like Product name, Data source fields were found missing.

Here is the view which will explain the problem –

Linked Sever Issue Missing

The newly created linked server will work just fine, and users wont be facing any issues other than the missing details.

This looked like a bug to me right away and I opened a case with Microsoft.

Connect Item details –

This is not a critical bug which will impact anyone badly, so I have a strong feeling that Microsoft might set low priority for this item and a fix might come only during the next release.


This is not an issue which is major, however details like Product name, Data source available to view comes handy if you need to trouble shoot issues related to linked server.

CU3 for SQL2012 SP1 – Couple of interesting hotfixes.

Here comes CU 3 (Build 11.0.3349.0.) for SQLServer 2012 SP1. You can download the same from

Similar to CU2 there are some interesting hotfixes which are included in this CU release, and its worth testing it out.

The ones which attracted my attention are –


(  )

FIX:   Access violation when you put a FILESTREAM-enabled database offline in SQL   Server 2008 R2 or in SQL Server 2012



(  )

FIX:   Poor performance in SQL Server 2012 when you run a SQL Server trace



(  )

FIX:   Error 3456 after you add new file groups to the principal database in a SQL   Server 2012 asynchronous database mirroring environment



( )

FIX:   CPU spike when there is no load on a server after you install SQL Server 2012   on the server



(  )

SQL   Server 2012 experiences performance issues in NUMA environments

The last 2 fixes attracted me a lot, however these are type of issues might not occur for all the environments. The CPU spike fix has a situation like a server with 64 or 128 cores +NUMA and 256GB – 1TB of Memory.These specifications are beyond imagination for small environments and majority of the enterprise(As of now !),so these might not even be a concern for the admin’s.


The decision to test or deploy CU3 for SQL2012SP1 varies from environment – environment and your approach towards CUs.

Thanks for reading and keep watching this space for more.

Apply CU2 for SQL2012 SP1 – Fix for Windows Installer starting repeatedly and causing CPU spikes !

CU2 for SQLServer 2012 SP1 is one key update which you should review and deploy if found necessary. I have found this very much needed for all my SQL2012 SP1 deployments. This CU contains a very important fix(KB2793634).

Note – The choice to deploy CU2 or just the fix (KB2793634) depends on your environment needs.

Here is why you need this fix –

As soon as you deploy SP1 for SQL2012 the Windows Installer (Msiexec.exe) process is repeatedly started to repair certain assemblies. Additionally, the following events are logged in the Application log:

EventId: 1004 Source: MsiInstaller Description: Detection of product ‘{A7037EB2-F953-4B12-B843-195F4D988DA1}’, feature ‘SQL_Tools_Ans’, Component ‘{0CECE655-2A0F-4593-AF4B-EFC31D622982}’ failed. The resource”does not exist.

EventId: 1001 Source: MsiInstaller Description: Detection of product ‘{A7037EB2-F953-4B12-B843-195F4D988DA1}’, feature ‘SQL_Tools_Ans’ failed during request for component ‘{6E985C15-8B6D-413D-B456-4F624D9C11C2}’

Source  –

Here are the screenshots of the application log. These are shown as warnings, and could easily be missed.



When this occurs there is a spike in CPU.

This process is repeated every 2 minutes and can cause heavy registry usage issues.

Registry issues can cause multiple failures and a sample one is


Note – Again, this varies from environment to environment.


Deploying the fix or the CU will resolve the issues related to the installer process.

Thanks for reading and keep watching this space for more.

Adding a Linked Server with ‘Oracle Provider for OLE DB’ as the provider – Proceed with caution !

Adding a linked server is an easy task which can be done at any point of time. I was under this same assumption until I came across a strange scenario.

CautionHere is the scenario for you all.

Use SSMS to create a New Linked Server and choose provider as ‘Oracle provider for OLE DB’

This one small step can bring the whole SQLServer to grinding halt if certain steps are not followed.

SSMS will be in frozen state during the New linked server creation step and eventually queries againt the databases will run forever.

Detailed explanation of the scenario

You wanted to create a new linked server with provider as Oracle provider for OLE DB.

Step 1 – The initial step to start of the whole process is to install the Oracle client on the SQLServer machine for which you want to add the linked server.

Step 2 – Oracle client installation will not force a reboot after the install is completed.

Step 3 – Without rebooting the server or recycling the SQLServices you will open SSMS and connect to the instance and see that the oracle provider is available.


Step 4You will use SSMS wizard to create a new linked server and will choose the provider as Oracle provider for OLE DB.


As soon as you select the provider you can notice that new linked server creation window will be in frozen state.

Any queries made against the databases will run indefinitely.

This situation will continue till you force a reboot of the machine. You cannot even recycle SQLServices as the service will fail to restart.

Think about a situation  –  You are doing this on a live production environment during normal business hours. As mentioned earlier this simple task can slowdown everything and can bring the server to a halt.In case the server is business critical, then rest is history(Start updating the resume !)

Right way of doing things

You will need to schedule changes like these ideally during a change window. You will need to recycle the SQL services or even bounce the box soon after the Oracle client is installed. This is a very important step. Once the server is up and running after the reboot you can either use SSMS to create the linked server or can opt to use sp_addlinkedserver system stored procedure. This time there wont be any slowness or performance related issues.


This simple scenario forced me to think twice and made up my mind to stick to one policy ‘Changes to business critical servers occurs only during a dedicated change window period’ no deviations what so ever.

Thanks for reading and keep watching this space for more.

Get Database information in mail using PowerShell !

Would you love to get a weekly status report of all the databases hosted on your mission critical servers? ,Would you love to get a report which will help you maintain and manage your large shared SQLServer environments neat and clean?

If your answer is Yes, then powershell is your solution. Welcome to the world of powerful powershell(Well, that sounds like a good marketing buzz word !).


Lets’ think about a large enterprise situation where in you are managing a huge SQLServer shared server which has many databases. Each day your DBA team members will be adding new databases to the server, and you would like to get a status report to ensure that the databases are compliant to the regular database ‘rules’ as you are responsible for the well being of the environment.

Example – No database can have a compatibility level which is less than 110,No databases can have owner other than SA etc.

I’m a great fan of the articles written by Microsoft Scripting Guy, Ed Wilson(B) and one of his articles gave me a quick idea of deploying a powershell function which will pull the database details on a weekly basis.

I slightly modified his code mentioned in the article here (This is a very useful/detailed article, and I highly recommend this to everyone ) and my little function will pull database information such as Name,PageVerify,CompatibilityLevel,Size,LastBackupDate,Recoverymodel,Owner etc and will send across an email.

Note – All credits goes to Ed for his original code. I have great respect for Ed,and if you are interested in powershell, then I highly recommend reading his articles.

Here is the powershell function   –

#Remember to make changes for SMTP area

Function Get-DBInfo ($SQLInstance)


    if ($SQLInstance.Contains(“`\”))

        { $location = “SQLSERVER:\SQL\$SQLInstance\Databases” }


        { $location = “SQLSERVER:\SQL\$SQLInstance\DEFAULT\Databases” }

    $style = ‘<style>’

    $style = $style + ‘BODY{background-color:peachpuff;}’

    $style = $style + ‘TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}’

    $style = $style + ‘TH{border-width: 1px;padding: 2px;border-style: solid;border-color: black;background-color:thistle;font-size:16}’

    $style = $style+ ‘TD{border-width: 1px;padding: 5px;border-style: solid;border-color: black;background-color:palegoldenrod;font-size:14}’

    $style = $style + ‘</style>’

        $body=dir -force  $location | where-object {$_.Name; $_.Refresh()} |

        Select Name, CompatibilityLevel,Pageverify,Size,LastBackupDate,RecoveryModel,Owner |ConvertTo-Html -Head $style -Property Name,CompatibilityLevel,Pageverify,Size,LastBackupDate,RecoveryModel,Owner |Out-String

        #Set your smtpserver information

        Send-MailMessage -To “” -Subject “Database Details from SQL2012 – Notification” –From “” -SmtpServer “” -Body $Body -BodyAsHtml


Get-DBInfo SQL2012

<Modified 01/27/2013> Above code was modified to pass the correct variable <Modified>

If you schedule this function as a SQL Job then you will receive automated emails according to your requirements.

Here is a sample email which was generated by the Get-DBInfo function.

Powershell Notification


Automated reports is going to help DBAs a lot and powershell is making life more easier by helping us to create highly flexible functions.

Thanks for reading and keep watching this space for more.

Check the version of Windows Installer !

This post is a quick tip to find the version of Windows Installer installed on your box.

You might wonder why I ever need to check this version, so here is my small story –

Today I was installing SQLServer 2008 Upgrade advisor for a Windows Server 2003(SP2) box(Pretty old one) and right away got this error –


The error message was pointing that the server might be running an old version of windows installer.

I decided to check what was the basic system requirements to install SQLServer 2008 Upgrade Advisor. Oh btw, that check should have been done earlier, but you know these things happen sometimes ! [With a promise not to repeat it later on].

SQLServer 2008 Upgrade Advisor needs Windows Installer 4.5 to work and that’s clearly mentioned in the download page.


Now I was curious to see what is the version of windows installer which is currently running.

How do I do that? I haven’t bothered to check that in the past too.


After doing some random search I finally got a simple answer to my question, thanks to

The best and easiest way to check the version is by typing msiexec in Run prompt to get


This quickly tells you about the version.

I did little more checks to find that there is yet another way to check the version details and that’s by directly checking the .DLL files.

Note – System files should never be modified, moved or deleted.


The above picture shows the details of Windows Installer version once it was updated to 4.5 which was the basic requirement for SQLServer 2008 Upgrade Advisor.

I know you are now thinking of Windows Installer 5.0,here is that information for you.

The Windows Installer 5.0 is part of the Windows Server 2012, Windows 8, Windows Server 2008 R2, and Windows 7. There is no redistributable for Windows Installer 5.0.

SQL Server 2012 Upgrade Advisor also needs Windows Installer 4.5.


One error helped me to learn something more about windows installer and if you are interested too,then here are some links for you

Thanks for reading and watch this space for more.