SQL Server 2005/2008: Deadlock while changing database to MULTI_USER mode

Msg 1205, Level 13, State 68, Line 10 Transaction (Process ID 79) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Msg 5069, Level 16, State 1, Line 10 ALTER DATABASE statement failed.


On a couple of occasions I have got caught in a situation where I ALTER the database to single_user mode and some other process takes a connection to the database. After this I can not change the database to multi_user mode.

It is possible to look at sp_who2 or sysprocesses and find the user SPID ( > 50) that is connected to the database and then kill it.

But sometimes I have found that the SPID is less than 50. Meaning it is a system process and you can not kill a system process. You will get the following error when you try to do that.

Msg 6107, Level 14, State 1, Line 1
Only user processes can be killed.

This means that there is a background system process that is connected to the database. It is possible that database has AUTO_UPDATE_STATISTICS_ASYNC option set to ON and the stats might be getting updated. This could be potential cause of the problem. To verify this check the DMV sys.dm_exec_background_job_queue

SELECT * FROM sys.dm_exec_background_job_queue

You can grab the JOB_ID from the result set and then use it in the command KILL STATS JOB to stop the process.

KILL STATS JOB 23

Apart from this the only other way to get the database in multi_user mode would be to stop SQL Server and then start the instance in single user mode and then changing the database to multi_user mode.

To avoid this situation just turn off the AUTO UPDATE STATISTICS configuration of the database before changing it to SINGLE USER mode.


USE [master]
GO
ALTER DATABASE [AdventureWorksDW_V2] SET AUTO_CREATE_STATISTICS OFF WITH NO_WAIT
GO
ALTER DATABASE [AdventureWorksDW_V2] SET AUTO_UPDATE_STATISTICS OFF WITH NO_WAIT
GO
ALTER DATABASE [AdventureWorksDW_V2] SET AUTO_UPDATE_STATISTICS_ASYNC OFF WITH NO_WAIT
GO

SQL Server 2005/2008: Get row counts on all the tables.

If you have to count the number of records on all the tables then there are two ways to do that. The first one is the more efficient one and you have to query the system tables to get that info.


SELECT  SCHEMA_NAME(t.schema_id) AS Schema_Name, t.name AS Table_Name, 
i.rows as Row_Count
FROM sys.tables AS t INNER JOIN
sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2
order by i.rows desc

But if you want to SELECT count(*) from all the tables then use the following query.


CREATE TABLE #counts
(     table_name varchar(255),
    row_count int )
EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'

SELECT table_name, row_count FROM #counts ORDER BY  row_count DESC
DROP table #counts

I prefer the first method since that does not involve selecting from each table.

Index (zero based) must be greater than or equal to zero and less than the size of the argument list

You get the following error when you run the Standard reports to get information such as Disk Usage in SQL Server Management studio.

Index (zero based) must be greater than or equal to zero and less than the size of the argument list

If you check the database properties then you will find that the database is in Compatibility mode 80 (SQL Server 2000) and you need the compatibility mode to be at least 90 to run these reports.

The reason becomes obvious if you try to run the report via SQL Server 2005 Management studio. You will get the following error on SQL Server 2005 Management studio.

Unable to display report because the database has a compatibility level of 80. To view this report, you need to use the Database Properties dialog to change the compatibility level to SQL Server 2005 (90).

In order to resolve this you need to change the compatibility mode to at least 90. But before you do this please test all your applications to ensure that it works fine.

How to script the SQL Server Agent Operators?

As part of the Disaster recovery procedures, I wanted to script out every server object that we had created. This included SQL Server jobs, logins, operators, linked servers and proxies. I was able to script out everything but the SQL Server Agent operators.

After several unsuccessfull Google searches my colleague found the following script to do this.

USE msdb
set nocount on;

create table #tbl (
id int not null,
name sysname not null,
enabled tinyint not null,
email_address nvarchar(100) null,
last_email_date int not null,
last_email_time int not null,
pager_address nvarchar(100) null,
last_pager_date int not null,
last_pager_time int not null,
weekday_pager_start_time int not null,
weekday_pager_end_time int not null,
Saturday_pager_start_time int not null,
Saturday_pager_end_time int not null,
Sunday_pager_start_time int not null,
Sunday_pager_end_time int not null,
pager_days tinyint not null,
netsend_address nvarchar(100) null,
last_netsend_date int not null,
last_netsend_time int not null,
category_name sysname null);

insert into #tbl
  EXEC sp_help_operator;

select 'USE msdb;' + char(13) + char(10) + 'if exists (select * from dbo.sysoperators where name =' + quotename(name, char(39)) + ') ' + char(13) + char(10) +
'exec sp_add_operator ' +
'@name = ' + quotename(name, char(39)) + ', ' +
'@enabled = ' + cast (enabled as char(1)) + ', ' +
'@email_address = ' + quotename(email_address, char(39)) + ', ' +
case
when pager_address is not null then '@pager_address = ' + quotename(pager_address, char(39)) + ', '
else ''
end +
'@weekday_pager_start_time = ' + ltrim(str(weekday_pager_start_time)) + ', ' +
'@weekday_pager_end_time = ' + ltrim(str(weekday_pager_end_time)) + ', ' +
'@Saturday_pager_start_time = ' + ltrim(str(Saturday_pager_start_time)) + ', ' +
'@Saturday_pager_end_time = ' + ltrim(str(Saturday_pager_end_time)) + ', ' +
'@Sunday_pager_start_time = ' + ltrim(str(Sunday_pager_start_time)) + ', ' +
'@Sunday_pager_end_time = ' + ltrim(str(Sunday_pager_end_time)) + ', ' +
'@pager_days = ' + cast(pager_days as varchar(3)) + 
case
when netsend_address is not null then ', @netsend_address = ' + quotename(netsend_address, char(39))
else ''
end +
case
when category_name != '[Uncategorized]' then ', @category_name = ' + category_name 
else ''
end +
'; ' + char(13) + char(10) + 'go'
from #tbl order by id;

drop table #tbl;

Mixed Mode authentication but did not provide a strong password.

Upgrade SQL Server 2000 to SQL Server 2005

Today I was upgrading a SQL Server 2000 instance to 2005 and was getting the following error during the install.

You selected Mixed Mode authentication, but did not provide a strong password.

Even after changing the password for SA and using a strong password we kept getting this error. After a couple of attempts it became apparent that the problem was not with the password.

Then came across the following link that explained the problem and the work around.
http://blogs.msdn.com/b/sqlserverfaq/archive/2009/07/15/upgrade-advisor-returns-error-while-upgrading-an-instance-of-sql-server-2000-to-sql-server-2005.aspx

In short the solution is to copy the BPAClient.dll file from the following location 'C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\BPA\bin' to 'C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\BPA'

After that the upgrade went without any hiccups. Once again thank you to Google.

If you do not find the file under the 'Program Files' folder then search under 'Program Files (x86)' folder.

Problem Event Name: CLR20r3

Install SQL Server 2008 R2 Enterprise Edition

Recently, I started installing SQL Server 2008 R2 Enterprise Edition and came across an exception. The install would fail and when I clicked on the report I saw the following message.

Problem signature:
  Problem Event Name:    CLR20r3
  Problem Signature 01:    setupvm.exe
  Problem Signature 02:    1.0.523.0
  Problem Signature 03:    4a7a0bdd
  Problem Signature 04:    mscorlib
  Problem Signature 05:    2.0.0.0
  Problem Signature 06:    4a27471d
  Problem Signature 07:    349e
  Problem Signature 08:    9a
  Problem Signature 09:    System.IO.IOException
  OS Version:    6.1.7600.2.0.0.272.7
  Locale ID:    2057

After searching on the net I found several cases where the install was failing for the same version. But after going through several issues, it became apparent that the issue was the install media.

So created a brand new ISO file for the installation media and used that to install SQL Server and that seemed to work.

Sometimes it is easier to begin from scratch than to keep troubleshooting these issues.

Run queries against different schema

Often times I run multiple queries in a TSQL script in a new query window. But some or all the objects might belong to a schema other than 'dbo'. This means we have to explicitly mention the schema name like
$schemaname.$tablename

But it might be difficult to edit the TSQL to mention the schemaname explicitly. As a DBA who comes from a different RDBMS background, I am used to commands such as

SET CURRENT SCHEMA ABC
or
USE SCHEMA ABC
or
ALTER SESSION SET CURRENT_SCHEMA='schemaname'


So I have always thought of a command or a query option to set the schema for all subsequent commands.

If you run the commands without the correct schema name then you will get the following error.

Msg 208, Level 16, State 1, Line 1
Invalid object name 'tablename'.

In order to run all subsequent queries against a different schema use the following command

EXECUTE AS USER = 'SCHEMANAME'