SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Basit's SQL Server Tips

Basit Farooq is a Lead Database Administrator, Trainer and Technical Author. He has more than a decade of IT experience in development, technical training and database administration on Microsoft SQL Server platforms. Basit has authored numerous SQL Server technical articles, and developed and implemented many successful database infrastructure, data warehouse and business intelligence projects. He holds a master's degree in computer science from London Metropolitan University, and industry standard certifications from Microsoft, Sun, Cisco, Brainbench, Prosoft and APM, including MCITP Database Administrator 2008, MCITP Database Administrator 2005, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Archives: July 2012

How to move master and resource system databases?

I received an phone call from a friend today asking how to move master and resource system databases in Microsoft SQL Server 2012.

Well, the process is very simple and is explained in this blog post:

Moving “master” database

The following are the steps to move master database:

  1. Launch SQL…

Read more

4 comments, 6,616 reads

Posted in Basit's SQL Server Tips on 29 July 2012

Different techniques to identify blocking in SQL Server

SQL Server is able to service requests from a large number of concurrent users. When SQL Server is servicing requests from many customers, there is a strong possibility that conflicts arise because different processes access the same resources at the same time. A conflict in which a process waits a… Read more

0 comments, 1,538 reads

Posted in Basit's SQL Server Tips on 27 July 2012

Retrieving Deadlock Graphs with Event Notification

Checkout my article (i.e. Retrieving Deadlock Graphs with Event Notification) that is published on SSWUG.org. This article shows the steps to capture deadlock graphs using Service Broker and Event Notification object and then sending them over to intended recipients using Database Mail.

Read more

0 comments, 2,885 reads

Posted in Basit's SQL Server Tips on 26 July 2012

Transact-SQL Script to Monitor Replication Status

Today, I’ve written the following T-SQL script which you can use to monitor the status of transactional replication and performance of publications and subscriptions. This script helps you to answer common questions such as:

  • Is my transactional replication healthy?
  • How far behind is my transactional subscription?
  • How long will it…

Read more

3 comments, 6,985 reads

Posted in Basit's SQL Server Tips on 25 July 2012

Using Cell-Level Encryption in SQL Server

Industry guidance such as the Payment Card Industry Data Security Standard (PCI-DSS), Healthcare Insurance Portability and Accountability Act (HIPAA) and numerous state privacy breach notification laws require the use of encryption for sensitive data such as credit card numbers, security related data etc. To meet these compliance and regulatory requirements,… Read more

2 comments, 10,250 reads

Posted in Basit's SQL Server Tips on 19 July 2012

Encrypting Connections to SQL Server

I manage databases for financial organisation. These databases have confidential data of our customers such as credit card numbers, security related data etc. This confidential data must be encrypted when it is transmitted between the client and the server over the network. That way, if anyone is reading our network… Read more

10 comments, 5,180 reads

Posted in Basit's SQL Server Tips on 16 July 2012

Introducing Windows Server 2012 – Get the FREE eBook!

Introducing Windows Server 2012 
- can be downloaded for free here.

This book provides the early, high-level information you need to begin preparing now for deployment and management of Window Server 2012. Below are the contents of this book:

Chapter 1: The business need for Windows Server 2012
The… Read more

0 comments, 2,252 reads

Posted in Basit's SQL Server Tips on 14 July 2012

Retrieving password policy settings for SQL login accounts

Today, I wrote the following query for our internal audit report for SAS70. This query provides all the necessary details about SQL Logins policy settings.

This query is using LOGINPROPERTY function to retrieve the sql login policy settings information:

USE [master]

DECLARE @PwdExpirationAge    [int]

SET @PwdExpirationAge =…

Read more

1 comments, 3,006 reads

Posted in Basit's SQL Server Tips on 10 July 2012

Purging MSDB Backup and Restore History Tables

Today while checking our database growth reports, I noticed that size of msdb database on most of our SQL Server instances is greater than 2GB.  After further investigation, I noticed that it was due to the size of the following backup/restore history tables:

  • backupfile
  • backupfilegroup
  • backupmediafamily
  • backupmediaset
  • backupset
  • restorefile
  • restorefilegroup

Read more

1 comments, 2,045 reads

Posted in Basit's SQL Server Tips on 9 July 2012

Securing Database Contents with Transparent Data Encryption (TDE)


Microsoft SQL Server has many security features available within the database, but until release of SQL Server 2008 there has been no “out-of-the-box” method for protecting the data at the operating system level. The Transparent Data Encryption (TDE) feature introduced in SQL Server 2008 allows sensitive data to be… Read more

10 comments, 7,595 reads

Posted in Basit's SQL Server Tips on 8 July 2012

Find the size of Index in SQL Server

As we know, sp_spaceused gives the size of table and index but it gives the sum of size of all indexes on a table. What if you need to capture size of individual index on a table? Thats where the following two queries comes handy:

Query 1:
Uses sys.indexes and… Read more

7 comments, 6,118 reads

Posted in Basit's SQL Server Tips on 6 July 2012

View SQL Server information using Transact-SQL script

Recently one of our clients requested information about their SQL Server infrastructure which we manage. In order to complete this task, I’ve written the following Transact-SQL script to retrieve information about SQL Server infrastructure.

This script is compatible with SQL Server 2005 and above:


DECLARE  @SQLServerStartupMode…

Read more

10 comments, 8,628 reads

Posted in Basit's SQL Server Tips on 4 July 2012

Useful new DMV’s in SQL Server 2008 R2 SP1 and SQL Server 2012


Microsoft SQL Server 2008 R2 and SP1 and SQL Server 2012 has a new set of DMVs that includes sys.dm_server_memory_dumps, sys.dm_server_registry, sys.dm_server_services, sys.dm_os_windows_info, sys.dm_os_volume_stats and sys.dm_exec_query_stats. These DMVs can be used to return the information about SQL Server configuration and installation, memory dumps related information and information that… Read more

0 comments, 5,390 reads

Posted in Basit's SQL Server Tips on 3 July 2012

Determine space used for each table in a SQL Server database

I have written following Microsoft SQL Server T-SQL scirpt to quickly determine space used for each table in a SQL Server database. This script returns following information for each table in the database:

  • SchemaName – Name of the schema.
  • TableName – Name of the table.
  • TableType – Type of the…

Read more

1 comments, 4,369 reads

Posted in Basit's SQL Server Tips on 1 July 2012