SQL Server General DBA

SQL Server Disaster Recovery Test

This article describes a simple procedure we implemented to ensure that the data on our the database on the Disaster Recovery server is consistent with the production server. Our DR Server is a separate server which is located in a separate data centre. The DR server receives data from the production database server via Transactional […]

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

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

VARCHAR (MAX), NVARCHAR (MAX) and NTEXT Data Types

Despite NTEXT and TEXT being deprecated in SQL Server for some time they are still both used in production systems. In this article, I will briefly demonstrate the difference between a VARCHAR (MAX), NVARCHAR (MAX) and the NTEXT data types, and the impact on performance from using NTEXT/TEXT. Let’s create a table named Student using […]

Tuning your SQL Query – Generating a Proper Execution Plan.

Recently one of my developers came to and complained of slow performance on a particular query. During the investigation I noticed that the primary cause was the poor query execution plan. In this article, I will discuss the approach which I used to improve the execution plan. For demonstration purpose, I will be creating a […]

Accessing SSRS Reports Using Report Viewer

Introduction Reports developed in the report designer need to be published to the report server in order to make them available to the end users. Once the reports have been deployed on the report server they can be managed through the report manager web application but that is more relevant to a site administrator as […]

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

Automating SQL Server Transactional Log Shipping Alerts

DBAs often follow a practice of using the Transactional Log Shipping Monitor in SSMS to ensure that the Log Shipping is running without any issues. However, it is not always possible to do 24/7 monitoring using the Log Shipping monitor. The alert messages generated by third party monitoring systems can be overkill and generate messages […]

TroubleShooting SQL Server Memory Consumption

Recently on one of our staging servers had a memory consumption issue and even 32 GB of RAM was looking insufficient and application performance was being impaired. Initially I thought the cause would be poorly designed queries, but after a thorough investigation this turned out not to be the case. I noticed that whenever the […]

Create Alert Messages When SQL Server Transactional Replication Is Blocked

Recently we had an issue in our Production environment, on the Reporting Database Server the Transactional Replication procedure was being blocked due to execution of some heavy T-SQL queries and as a result business users were unable to view the latest data. During our investigation, I had a looked at the Synchronisation status of the […]
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 |