Where is my SQL BPA(Best Practice Analyzer) for SQL 2014/2016/2017?

We recently built a SQL 2016 Server for a client and wanted to check if the best practices have been followed or not. Most of us have used the tool BPA (Best Practice Analyzer) to evaluate an instance. The Microsoft SQL Server 2012 BPA is a diagnostic tool that performs the following functions: Gathers information…


Configure Alerts for a Hung/Stuck SQL Agent Job step

SQL Server does a decent job alerting us of SQL Agent Job failures/ Success But, what if we  want to know if a job is hung, been running for a long time and has been stuck? When my customer approached me with the below problem I realized that the Job history table in the MSDB…


SQL 2012 BPA error- ‘Login does not exist on SQL Server OR Login is not a member of the Systems Administrator role’

I was recently trying to use the diagnostic tool Microsoft SQL Server 2012 Best Practice Analyzer on my customer’s machine that was getting ready for a migration. ·        Using this tool a DBA can determine whether the SQL Server is configured as per the best practices recommended by the SQL Server Products Team. ·        This tool…


Using REST API to pause Azure SQL Data Warehouse – “Bearer authorization error=’invalid_token’, error_description=’The authentication scheme of not supported”

  A customer of mine recently approached me for assistance to help resolve an Authorization error he was receiving , when he was running a custom prepared automation script in C# that would pause and restart the SQL data warehouse. To save costs, you can pause and resume compute resources on-demand.  He had tried the…


Automatically Monitor and Change the service tier and performance level (pricing tier) of an Azure SQL database based on the DTU alert using Webhooks, Alerts, Runbooks

  We all know how easy it is to change the service tier of an Azure SQL database as the needs of your application change using the Azure Portal/PowerShell. But what if you want to automate the upgrade and the downgrade of your database? And, what if you have many databases and don’t want to…


Could not obtain exclusive lock on database ‘model’. Retry the operation later. CREATE DATABASE failed

After installing a SQL server 2008 R2 instance SQL_SERVER\MSSQLSERVER2008R2 side by side on the Box which already has SQL 2005 production instance, we are unable to create a database from the management studio   Receiving the below error   Create failed for Database ‘x’. (Microsoft.SqlServer.Smo) —————————— ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL…


There is insufficient system memory in resource pool ‘internal’ to run this query. Error: 701, Severity: 17, State: 123" on SSB Target Server – CACHESTORE_BROKERTO is consuming memory

I recently worked with a customer where the Target server for his Service broker application encounters “There is insufficient system memory in resource pool ‘internal’ to run this query. Error: 701, Severity: 17, State: 123” CACHESTORE_BROKERTO was consuming about 18 GB of RAM and SQL server is running out of memory.   Environment: ================== SQL:…


Service Pack Setup completes successfully on clustered instance but SQL Server Version is not updated on one node

I recently worked on an issue where one of my customers had successfully finished patching their SQL 2005 instances on a 3 node cluster. However the Build number was not getting updated on one of the nodes (NODE3) even after successfully completing the SP4 installation  SQL version On problem node: Database Services (INSTANCEX) SP3 2005.090.4340.00 …


SQL Server 2008 Add node setup hangs for hours at “Install SQL_DTS_VSTAConfiguration_install_Cpu64: RollbackCleanup: Removing backup files”

We recently encountered a situation while trying to ‘Add a node’ to the SQL 2008 Cluster The installation hung for hours at Install SQL_DTS_VSTAConfiguration_install_Cpu64: RollbackCleanup: Removing backup files It may also hang at Install_VS_Shell_Cpu32_Action: RollbackCleanup: Removing backup files We do not see any error messages from the SQL setup, however the setup just hangs and…


SQL Agent skipping Scheduled job runs!!– But the Job History does not reveal anything!

A Scheduled SQL Agent job skipped a Scheduled run on one particular day and the DBA wanted to investigate the root cause as he did not see any error messages or Job failures reported! This Job was used to collect some information and upload the data at regular intervals for the DBA’s customers to use….