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

Ctrl-alt-geek

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 mattsql.wordpress.com.

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,827 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,106 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, 635 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, 1,997 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, 687 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:

DBCC SQLPERF('LogSpace')

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,491 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,425 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, 575 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,645 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, 785 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

5 comments, 2,292 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, 444 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

5 comments, 3,499 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, 587 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
reconfigure

Now when you run… Read more

2 comments, 541 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, 665 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, 416 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,235 reads

Posted in Ctrl-alt-geek on 30 April 2012

SQL Server 2005 backups in a hung state because of full text search process

I came across an issue today where a backup process was in a blocked state with a wait type of MSSEARCH. The issue boiled down to an issue with communication/contention between the SQL Server engine service and the SQL Server Full Text Search service.

An attempt to kill the backup… Read more

2 comments, 315 reads

Posted in Ctrl-alt-geek on 24 April 2012

Instant File Initialization Security Risk

A while ago I blogged about instant file initialization. A colleague of mine pointed out that there is a small but non-zero risk involved with using this setting.

So I decided to put a demo together to test out the security risks involved.

I have a default instance and… Read more

0 comments, 360 reads

Posted in Ctrl-alt-geek on 16 April 2012

Newer posts

Older posts