Show me the wait stats

Using wait stats to identify pressure points on your SQL Server is a common method for troubleshooting. Unfortunately , it is very easy to jump to conclusions about problems on the basis of the highest wait stats. My general approach is to use wait stats , but letting them guide me to extra analysis. There could be all sorts of reasons why a wait stat or a combination of wait stats appear . The more you use wait stats , you develop methods for troubleshooting. Interpreting the initial findings will help you start to ask the right question. These are... Read more →

How to spot a SQL Antipattern

Survival in the DBA world is following good practises but just as importantly it is about identifying bad practises. Bad practises can also be called antipatterns. An antipatterns is a standardised fix to a common problem that may lead to a counterproductive outcome. There is a well known book – AntiPatterns: Refactoring Software, Architectures, and Projects in Crisis – where this term was first coined. The authors highlighted two criteria required to satisfy a fix as an antipattern A commonly used fix or solution , which may appear to solve the issue , but tends to bad consequences. A simple... Read more →

OFFLINE Database files not deleted when DROP DATABASE is executed

A DBA contacted me in a panic this afternoon. They’d accidentally dropped a database . We had a brief conversation about whether a valid backup existed , and if it did exist can we restore the database. It turned out there was no valid backup in place. During the course of our conversation we established the database status prior to the DROP database statement and any other information which may be useful in recovering the database. It emerged , the DBA had placed the database in an OFFLINE status prior to the DROP DATABASE statement. I remembered reading in SQL... Read more →

Msg 15138 - The database principal owns a schema in the database and cannot be dropped.

Question: I’ve attempted to drop a SQL Server database user. When I execute the DROP USER , I receive the error message: Msg 15138, Level 16, State 1, Line 2 The database principal owns a schema in the database, and cannot be dropped. How can I fix this problem? Answer: Step 1 : Find out the database schemas owned by the database user SELECT name FROM sys.schemas WHERE principal_id = USER_ID(a_user_id) Step 2 : Either drop the schema or change the schema owner To drop the database schema DROP SCHEMA [my_schema_name] You may receive an error message such as :... Read more →

Ask the Duck Your Question

Asking the right question in troubleshooting is critical to understanding and fixing problems. Read more on Asking the right question in SQL performance Tuning. Developing Analytical thinking is part of part of the IT education. Solving problems is part of the daily job . Possessing thinking tools assists in solving problems. A useful approach on developing thinking is Ask the Duck. The basic idea is to get to the fundamentals of the issue by attempting to ask the right question. Quite often ,in the process of framing a question – the answer or good clues appear leading to the right... Read more →