Category Archives: Database options

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

Posted on April 24, 2012 by matt.bowler

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 … Continue reading →

Posted in Database options, Disaster Recovery, SQL Server | Tagged , | 2 Comments

Comparing users and role memberships between two databases

Posted on March 21, 2012 by matt.bowler

The other day I was asked to restore a production database into a development environment and then check the database permissions. So after restoring the database I ran use ‘database name’ exec sp_change_users_login ‘report’ to see if there were any … Continue reading →

Posted in Database options, Security, T-SQL | Leave a comment

Instant file initialization

Posted on February 23, 2012 by matt.bowler

A call came in to increase a database datafile by 7-8GB. I was asked if there were any implications of doing the increase. Of course the biggest concern was around creating IO load. One way to mitigate this would be … Continue reading →

Posted in Database options, SQL Server | 2 Comments

Per database tasks

Posted on September 19, 2011 by matt.bowler

Often as a DBA I am required to perform a particular task on every database, or some subset of databases. If it is the same task for each database then it makes sense to automate this in some way. Two … Continue reading →

Posted in Database options, SQL Server, T-SQL | Leave a comment

Database Option Queries

Posted on September 5, 2011 by matt.bowler

Here are a few queries that I use regularly to check database options on a server: Compatibility (100 = 2008, 90 = 2005, 80 = 2000): select name, compatibility_level from sys.databases where compatibility_level != 100 — or 90, or 80 … Continue reading →

Posted in Database options, T-SQL | Leave a comment

Service Broker

Posted on September 5, 2011 by matt.bowler

While checking a SQL Server upgrade I came across a corrupt msdb database. Like a good DBA I had backed up all user databases along with msdb, master and model before the upgrade so I quickly restored msdb from the … Continue reading →

Posted in Database options, Service Broker, SQL Server, T-SQL | Leave a comment

Black Box Trace

Posted on August 19, 2011 by matt.bowler

A database has been changed into simple recovery, and the CIO wants to know who did it. If you want to play along on your test instance run this command: ALTER DATABASE <your_full_recovery_db> SET RECOVERY SIMPLE; There are two main … Continue reading →

Posted in Database options, SQL Server, T-SQL | Leave a comment