SQL Server General DBA

Product Review: dbForge SQL Complete

Introduction If you are an intensive T-SQL writer, you might need an additional help As a T-SQL developer, one of the major obstacles that you will come across is determining the columns names for relevant tables. One of the major features, among many other valuable features, that this tool is equipped with is, finding the […]

Product Review: SQL Database Repair Software

  Introduction Though many SQL Administrators have found the SQL Database Repair software as one of the most competitive database repair and recovery tools, yet I thought of trying the software on my own. Let me explain you why?     Installation & configuration Installation is very simple with couple of steps. No special configurations […]

Monitoring Transactional Replication – The Distribution Queue

Note : The scripts for this article can be downloaded here. Monitoring the distribution queue is an important link in the chain of transactions delivery over a replicated topology. A chain that includes the log reader agent scanning the published database’s transaction log for commands to be moved to the distribution database from which a […]

Performance Tuning Re-indexing and Update Statistics – A Case Study

Recently we started experiencing a very strange issue in our production reporting environment where the Re-indexing and Update Statistics operation suddenly began taking more than 2 days to complete and was thus causing blockage in the database which in turn caused impairment in application performance. Reporting Server Configuration Details: SQL Server Version: SQL Server 2005 […]

Physical Join Operators in SQL Server – Nested Loops

SQL Server implements three different physical operators to perform joins. In this article series we will examine  how each  operators works, its advantages and challenges. We will try to understand the logic behind the optimizer’s decisions on which operator to use for various joins using (semi) real life examples and how to avoid common pitfalls. […]

New T-SQL Commands in SQL Server 2017

Despite the maturity of TSQL, SQL Server 2017 still introduces several new features which to the T-SQL family which can be very useful in database development. CONCAT_WS The CONCAT command was first introduced with SQL Server 2012< to simplify joining multiple columns as below: SELECT CONCAT(Title,' ' , FirstName, ' ' , MiddleName, ' ' […]

Implementing Database Settings Using Policy Based Management

Introduction Database Administrators have always had a tough time to ensuring that all the SQL Servers administered by them are configured according to the policies and standards of organization. Using SQL Server’s  Policy Based Management feature DBAs can now manage one or more instances of SQL Server 2008 and check for policy compliance issues. In […]

Capture SQL Server Deadlocks using Extended Events

Deadlocks in SQL Server usually occur when users try to place exclusive locks on each other’s objects. For example, a database user Bob first acquires an exclusive lock on Sales table and then attempts to place an exclusive lock on Orders table. Another database user Joe already has an exclusive lock on Orders table, and […]

Recovering a SQL Server Database from Suspect Mode

A couple of days back at I got a call from my support team informing me that one of our database located on the Production Server went into Suspect Mode. The version used was SQL Server 2005 Service Pack 3. Being a Production Database server, it was a Priority 1 incident and the expected time […]

Controlling Growth of a msdb Database

I recently encountered a situation where the drive hosting Sharepoint Databases in a Staging environment ran out of space. I logged onto the server and found that the msdb database has itself occupied 38 GB of the total disk space. Msdb database generally contain maintenance information for the database such as backups, log shipping and […]