SQL Server General DBA

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

Understanding SQL Server 2008 R2 Fixed Database Level Roles

To easily manage the permissions in our databases, Microsoft has provided several roles in SQL Server which are security principals that group other principals. They are like groups in the Microsoft Windows operating system. Database-level roles are database-wide in their permissions scope. The various types of SQL Server fixed database roles are as follows: · […]

Monitor SQL Server Replication Jobs

The Replication infrastructure in SQL Server is implemented using SQL Server Agent to execute the various components involved in the form of a job (e.g. LogReader agent job, Distribution agent job, Merge agent job) SQL Server jobs execute a binary executable file which is basically C++ code. You can download all the scripts for this […]

Backing Up a SQL Server Database Directly Onto a Remote Server

Recently I encountered a situation where the backup drive was short of space on the production server. The policy on the production server was that as soon as soon as the Full Backup is complete, a copy of the production backup is transferred to the staging server using RoboCopy and then on the staging server […]

A Primer on Log Shrinking and Truncation in SQL Server

In the event that a transaction log grows to an out-of-control size, shrinking it can be the only solution – but don’t rush to do this until you are aware of the consequences. Shrinking the log could ‘break the log chain’ – rendering the existing transaction log backups useless. When this happens, the most recent […]

How to Optimize SQL Server Query Plans – The Devil is in the Detail

When dealing with the performance database any detail should can never be neglected, because even the smallest thing can turn out to be a great opportunity for query optimization. Generally, one needs a significant amount of experience to detect that kind of details easily. But what shall the rest do till they gain the required […]

Extended Events in SQL Server 2012

Extended Events were introduced in SQL Server 2008 as a light-weight monitoring mechanism. Most DBAs typically use SQL Profiler to monitor and analyse SQL Server events. However, SQL Profiler is not a lightweight tool and consumes too many resources for use in a production environment. In SQL Server 2008, Extended Events can only be configured […]

Restoring a SQL Server Database – A Practical Example

Performing a Database Restore is a part of a DBA’s daily life. A DBA may need to perform a Restore due to various reasons such as recovery, refreshing a database for testing purpose etc. Many times it can be difficult to perform a Restore due to corrupted media, low disk space on the server and […]

A Journey from OLTP to OLAP- Part 2

In my last  article we saw the basics of the data warehousing in which we saw looked at how OLAP and OLTP technologies differ in their functionalities, what were the models being used to develop the data warehouse and what were the schemas under consideration for designing the data warehouse. In this article I will […]

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