Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in


Matt Bowler is a DBA at Trade Me, and teaches database design and administration courses at the local institute of technology. A recent but avid discoverer of SQL Server, Matt is a regular contributor to forums at MSDN, Experts Exchange and SSC and he blogs at

SQL Server 2008 Statistics: What does a DBA need to know?

I’ve been looking into the behaviour of statistics in SQL Server 2008 – so I’d like to present an overview from a DBA perspective.

What are statistics?

Statistics are a database scoped object that are used by the query optimiser in creating query plans. They describe the distribution of values… Read more

0 comments, 5,190 reads

Posted in Ctrl-alt-geek on 14 February 2013

Restore database permissions


I came across a couple of subtle gotchas around the permissions required to restore a database in SQL Server 2008+ recently. I’m interested in situations where a DBA needs to allow certain users the ability to restore a given database, for example refreshing UAT, while maintaining minimum permission levels… Read more

8 comments, 1,942 reads

Posted in Ctrl-alt-geek on 10 January 2013

Migrating SQL Server Databases that use Database Master Keys

There’s a lot of things to consider when migrating databases between SQL Server instances. I want to provide some tips for dealing with Database Master Keys, and in particular the case when those keys are encrypted by the Service Master Key.

Before getting into the details, let’s take a quick… Read more

10 comments, 1,931 reads

Posted in Ctrl-alt-geek on 13 November 2012

Don’t believe everything you read: Truncate table is not logged

Actually I’m not sure if anyone still believes this anymore. If you read the BOL description carefully it states that truncate de-allocates the pages rather than deleting the rows – meaning only the de-allocations are logged – resulting in far fewer log records. Incidentally this is also why the truncate… Read more

1 comments, 3,217 reads

Posted in Ctrl-alt-geek on 31 October 2012

Quick tip: Count table rows in a database, and locate those rows in the data files.

Here are a couple of quick database and table level queries that I have found useful lately.

How many rows in each table of a database?

The first temptation with this one was to use something like sp_MSforeachtable with a count(*), until I recalled that the catalog view sys.indexes has… Read more

0 comments, 687 reads

Posted in Ctrl-alt-geek on 30 October 2012

Querying database usage

A question that a DBA will often be asked is what databases are actively being used on my SQL Server? We get asked it all the time, usually as part of a consolidation or rationalisation project.

My solution to this is to query the sys.dm_db_index_usage_stats DMV, which maintains counters of… Read more

0 comments, 2,056 reads

Posted in Ctrl-alt-geek on 17 October 2012

Quick tip: Querying database metadata with DBCC DBINFO

Page 9 in file 1 (of the primary filegroup) in a SQL Server database is the header page and contains a wealth of metadata about the database. We can have a look at this with DBCC PAGE or DBCC DBINFO.

In order to redirect the output of the DBCC command… Read more

0 comments, 732 reads

Posted in Ctrl-alt-geek on 11 September 2012

Quick tip: Using DBCC SQLPERF(‘LogSpace’)

A lot of a DBA’s time is spent managing and investigating transaction log files.

A commonly used tool is the command:


which returns a list of all databases, the size of their transaction log file, and the percentage of that space that is used for active transactions. One… Read more

2 comments, 1,589 reads

Posted in Ctrl-alt-geek on 10 September 2012

Deploying and processing SSAS cubes

In SSAS we build what’s known as a UDM, or Unified Dimensional Model, over the top of a database schema, usually a data warehouse. The UDM consists of several objects designed to aid analysis of the data. The UDM is built in BIDS (Business Intelligence Development Studio) and in terms… Read more

1 comments, 3,696 reads

Posted in Ctrl-alt-geek on 1 August 2012

Restores in SQL Server Analysis Services

“You’re only as good as your last restore”

I’ve no idea who originally said that – but it probably qualifies as DBA folklore. I blogged recently about backups in SSAS so to close the loop here are the basics of restoring SSAS databases.

In the GUI:

Connect to the SSAS… Read more

1 comments, 611 reads

Posted in Ctrl-alt-geek on 1 August 2012

Backups in SQL Server Analysis Services

Analysis Services databases should be backed up at regular intervals like any other database. Here are the basics.

Using the GUI: Connect to the SSAS service in SQL Server management studio. Right click on the database and select backup.

The Backup dialog presents a number of options.

Allow file overwrite… Read more

1 comments, 1,724 reads

Posted in Ctrl-alt-geek on 19 July 2012

Quick tip: configure SQL Server authentication mode using T-SQL

SQL Server stores its authentication mode in the registry. The registry can be read and updated using the T-SQL commands xp_regread and xp_regwrite.

Step 1: Construct our registry key path, run the following query and record the result:

DECLARE @InstanceName NVARCHAR(1000),

EXEC master..xp_regread N'HKEY_LOCAL_MACHINE',
   N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\',
   N'MSSQLSERVER', --for…

Read more

0 comments, 829 reads

Posted in Ctrl-alt-geek on 11 July 2012

Don’t believe everything you read: Reconfigure flushes the plan cache

I’ve been doing a fair bit of research and investigation into the behavior of the plan cache recently. So I was concerned when I read somewhere that the reconfigure statement causes the SQL Server plan cache to be flushed. If this is true then it is a pretty crucial piece… Read more

6 comments, 2,392 reads

Posted in Ctrl-alt-geek on 25 June 2012

Corrupt Model database

An interesting case arose at work the other day. SAN issues had caused SQL Server to shutdown and it was unable to be restarted because the data files were unavailable.

Once the SAN was back online the instance still could not be restarted. The error logs were reporting that tempdb… Read more

1 comments, 474 reads

Posted in Ctrl-alt-geek on 20 June 2012

Service Broker: What does a DBA need to know?

Service Broker is SQL Server’s internal messaging system. It has been designed as an asynchronous, reliable and transactional messaging system for inter or intra database messaging, or even inter SQL instance messaging.

It is a powerful tool for decoupling extended transactions across time and across databases. It is designed to… Read more

6 comments, 3,830 reads

Posted in Ctrl-alt-geek on 7 June 2012

Plan cache in SQL Server 2008R2

I was curious to look at the effect of database options, on the SQL server plan cache.

I have restored the Adventure works sample database onto my SQL Server 2008 R2 RTM instance four times as AW1, AW2, AW3 and AW4.

I have used, and slightly modified, a query from… Read more

1 comments, 666 reads

Posted in Ctrl-alt-geek on 23 May 2012

Quick tip: sp_configure and sys.configurations

In order to query the configuration settings of a server you can run this sproc:

exec sp_configure

On a default install of sql server this will return 16 rows. In order to see all rows you need to run this:

exec sp_configure 'show advanced options',1

Now when you run… Read more

2 comments, 588 reads

Posted in Ctrl-alt-geek on 16 May 2012

Setting up transactional replication using T-SQL

Replication is one of the more complex of the SQL native “HA/DR” technologies. There are a lot of moving parts. Here are the steps to configure a basic transactional replication topology, with a publisher and distributor on the same instance and using push subscriptions. Most of the stored procedures have… Read more

1 comments, 740 reads

Posted in Ctrl-alt-geek on 15 May 2012

Working with bitwise data in SQL Server

I was working with a group of students on a design brief. The brief was to partially implement a database design based on the LinkedIn web site. A discussion came up around the possibility of implementing the contact preferences (essentially a set of boolean values) as bitwise data, so I… Read more

0 comments, 469 reads

Posted in Ctrl-alt-geek on 10 May 2012

Locked out of SQL Server.

This is a very cool and useful tip if you find yourself locked out of the sysadmin role in sql server.

I have a SQL Server instance with the sa account disabled and all other logins removed to simulate a situation were I am locked out of SQL Server. My… Read more

3 comments, 1,339 reads

Posted in Ctrl-alt-geek on 30 April 2012

Newer posts

Older posts