SQL Server General DBA

Physical Join Operators in SQL Server – Hash Operator

In second part of this series on physical join operators we looked at the Merge Operator. In the final part of the series we turn our attention to the Hash operator. For this article series I am using an analogy of two sets of standard playing cards. One set with a blue back and another […]

Physical Join Operators in SQL Server – Merge Operator

In Part I of this series on physical join operators we looked at Nested Loops. We now turn our attention to the Merge operator. For this article series I am using an analogy of two sets of standard playing cards. One set with a blue back and another with a red back that need to […]

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 […]

Understanding SQL Server Change Data Capture

DBA’s often encounter a requirement to audit DML activity such as INSERT/UPDATE/DELETE operations executed against a particular table or a group of tables in a particular database. Change Data Capture (first introduced in SQL Server 2008 R2) can greatly assist this function. Only members of sysadmin fixed server role can enable the Change Data Capture […]

Replicating A Volume Of Large Data via Transactional Replication

During weekend maintainence, members of the support team executed an UPDATE statement against the database on the OLTP Server. This database was a part of Transactional Replication and the database on the subscriber was used by the SSRS reports. The UPDATE statement impacted around 3500000 records. Once the UPDATE statement was executed, the Replication procedure […]

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 […]

Using Decision Trees in SQL Server

As a practical introduction to decision imagine you are assessing the risk of a PC being infected with a virus. The below data on factors influencing affecting the risk of a PC being infected with a virus is available on MSDN. Shares files Uses scanner Infected before Risk Yes Yes No High Yes No No […]

Does Transactional Replication Include Structural Changes Made to a Database

We typically use Transactional Replication for Reporting purposes. In Transactional Replication, the idea is to replicate the changes done on the Publisher Database (OLTP) onto the Subscriber Database (Reporting).  The main challenge in Replication is whether it will replicate the structural changes made on the Publisher database successfully onto the Subscriber database. For Demonstration purposes, […]

Does LogShipping Include the Structural Changes Made to the Database?

Recently a client asked me “ Does LogShipping ship the Create Table, Alter Table, Drop Table commands onto the Secondary Server?”. Obviously the answer to his question was “Yes” but I wanted to be 101% sure before committing something to him. In this article we are going to prove that LogShipping not only propogates the […]

SQL Server Hardware Optimization

An important concern in optimizing the hardware platform is hardware components that restrict performance, known as bottlenecks. Quite often, the problem isn’t correcting performance bottlenecks as much as it is identifying them in the first place. Start with obtaining a performance baseline. You monitor the server over time so that you can determine Server average […]

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |