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

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:

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

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:

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:

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.

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: