SQL Server General DBA

Point of time Recovery In a typical production environment, in order to recover data in the even of disaster, most DBAs will schedule a robust backup plan. Since scheduling a full back-up during peak times will have an impact on database performance, most DBAs will have combination of full, differential and log backup in the [...]

Have you noticed the ‘guest’ user in all of your databases? Who The Guest User? When you login to the SQL Server, it first verifies you for authentication on the server. If it succeeds, SQL Server checks whether your login is associated or mapped with a database user in the database that the login is [...]

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

Contained Databases have been an oft requested feature which has finally arrived in SQL Server 2012. In prior SQL Server versions, database were not 100% portable. In case you need to move a database from one server to another using backup and restore, the data in the database will of course be preserved. However, to [...]

XQuery was developed by W3C as a query language designed to programmatically interact with and manipulate Extensible Markup Language (XML).  XQuery uses XPath methods and expressions to search and retrieve XML data.  In this article, I’d like to show a few simple, practical examples of these implementations. First, let’s create a table to hold sample [...]

Paging Data Paging is a big issue for developers as it is required for many applications but entails many performance problems.  Developers have used different workarounds to support paging. For example, assume we need to display several pages of the HumanResource.Employee object with 5 rows per page. Below is the query you need to execute [...]

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

In replication, the standard way of creating a publisher is to create a publication with a snapshot (which is what all the documentation recommends). However there are a few problems with this method: Creating the snapshots and then applying them is time consuming. Snapshots will consume disk space. There is an alternative to this, where [...]

In Implementing Transactions Part I I briefly described the role of Transactions in SQL Server and outlined a very basic implementation. In this second part, I will explain how a DBA can best implement Transactions in scripts that are to be deployed on production databases. One of the regular tasks of a DBA is to [...]

A basic requirements in most SQL Server based applications is implementing a database operation (DDL or DML) in a single unit of work. The DDL requirement is more from a DBA’s perspective rather than the application, since any application would perform only INSERTs, UPDATEs and DELETEs on the existing data, and a DBA would be [...]