Performance Dash board and performance Baseline

These two articles were published my Microsoft SQL team and it will help you to create Performance dashboard and performance baseline report. These reports are really cool and will eliminate your performance monitoring need. https://blogs.msdn.microsoft.com/sql_server_team/sql-server-performance-dashboard-reports-unleashed-for-enterprise-monitoring/  https://blogs.msdn.microsoft.com/sql_server_team/sql-server-performance-baselining-reports-unleashed-for-enterprise-monitoring/#comment-2705

Server TCP provider failed to listen on [ ‘any’ 1433]. Tcp port is already in use.

Issue – Today I faced very weird issue; on multi instance SQL Server machine, couple of SQL Services was not starting after Server reboot ( In my case it was SQL 2014 and SQL 2016 ) and every time I was trying to restart my SQL Services, I was getting below error message –  … Read More Server TCP provider failed to listen on [ ‘any’ 1433]. Tcp port is already in use.

Cannot be configured for publishing and distribution. Publishing and distribution are supported only in SQL Server version 7.0 or later.

I got really wired issue when i was trying to configure Replication on my Enterprise edition 2016 server. I checked couple of time and everything was looking okay until I realize I was running SSMS 2014. I started SSMS 2016 and it’s fixed my issue. I know its not very big issue but I thought… Read More Cannot be configured for publishing and distribution. Publishing and distribution are supported only in SQL Server version 7.0 or later.

The server principal “XYZ\USER” is not able to access the database “YourDBName” under the current security context

My Development team contacted me and said they are not able to access database  “The server principal “XYZ\USER” is not able to access the database “YourDBName” under the current security context” when I looked into server server I found – Login was there and access was granted. When I looked into database I found user… Read More The server principal “XYZ\USER” is not able to access the database “YourDBName” under the current security context

Could not locate file ‘xy_abc_Log’ for database ‘abc’ in sys.database_files.

Problem – I have found VLF size of my database abc is very huge and when i have tried to shrink the file i have received below error – Could not locate file ‘xy_abc_Log’ for database ‘abc’ in sys.database_files. Resolution – While my investigation I have found logical fine name under sys.master_files is different as compare… Read More Could not locate file ‘xy_abc_Log’ for database ‘abc’ in sys.database_files.

How to bypass restart computer error message while installing SQL Patch

If you ever encounter restart computer error message while performing SQL Server patching and you don’t have opportunity\permission to restart your machine. You can bypass this situation by deleting value from PendingFileRenameOperations key. Here are the steps which you need to follow –> To Open Registry Editor. Click Start  ( win + R ) –> Run,… Read More How to bypass restart computer error message while installing SQL Patch

Replication – Snapshot is not getting generated

Replication issues and resolutions Today I have encountered very strange issue – Scenario – Snapshot is not getting generated for newly added articles or Error when setting up Transactional Replication via SQL scripts. Msg  14100, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 533 Specify all articles when subscribing to a publication using concurrent snapshot processing. Step performed to… Read More Replication – Snapshot is not getting generated

Configuring deadlock and Storing Deadlock events in a table

Configuring deadlock and Storing Deadlock events in a table SQL Server 2012 and above. Verification – Ensure Extended Event “system_health” is running. SSMS, checking Management -> Extended Events -> Sessions. These should be green arrow which indicate its running.   The system_health session writes to two targets, both an event file and the ring buffer:  … Read More Configuring deadlock and Storing Deadlock events in a table

Create Blocking Alert – SQL 2005 , SQL 2008 R2 and SQL 2012

We often need to create blocking alert in our Production server to capture existing blocking on server and keep record for future analysis. Here are steps which help us to create blocking alerts- Either you can receive blocking details through e-mail or you can log event id in application log and configure your monitoring tool to… Read More Create Blocking Alert – SQL 2005 , SQL 2008 R2 and SQL 2012