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

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

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

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

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

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

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

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]
GO

DECLARE @PwdExpirationAge    [int]

SET @PwdExpirationAge =…

Read more

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

Introduction

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

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

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:

SET NOCOUNT ON;

DECLARE  @SQLServerStartupMode…

Read more

Introduction

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

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