Rahman Agoro, the frontline SQL DBA

Just another WordPress.com weblog

Archive for the ‘Scripts’ Category

Database ‘DatabaseName’ is in transition. Try the statement later Msg 952, Level 16, State 1, Line 1

Posted by rahmanagoro on April 4, 2016

I bumped onto this error message when our monitoring tool started to report errors with a SQL agent job. On taking a close look at the error, I found that whenever I tried to connect to the database, I simply got the error:

Msg 952, Level 16, State 1, Line 1
Database 'DatabaseName' is in transition. Try the statement later.

Digging deeper into the issue and also the error information, i made some interesting discoveries. It appears that we have a blocking chain going on within the database, our environment is  such that database owners can choose to detach databases etc.

I can the following code to check for blocking:

 request_sql_text = st.text,
 most_recent_sql_text = stc.text
from sys.dm_tran_locks l
left join sys.dm_exec_requests r
on l.request_session_id = r.session_id
left join sys.dm_exec_sessions s
on l.request_session_id = s.session_id
left join sys.dm_exec_connections c
on s.session_id = c.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) st
outer apply sys.dm_exec_sql_text(c.most_recent_sql_handle) stc
where l.resource_database_id = db_id('DatabaseName')

It turned out that one user wanted to detach the database whilst another user just started to issue a query on the same database, this was the reason for the error and the change of the transition state. One way to avoid such issue before detaching a database is to take the following steps.

  • Personally, I do like using code/T-SQL to run commands rather then the good old GUI, I can see what i am doing and I can also see if my actions are getting stuck, rather than wondering if my Pc is slow etc.
  • Secondly, when detaching a database, if other connections can be rolled back. The code below can be issued.
ALTER DATABASE DatabaseName SET offline with rollback immediate

The issue

I could see the blocking chain, from the SQL query above which checks the locks. Multiple sessions wanted to connect to a database that was about to change its transition to offline and then detached.

What I simply did was kill one of the sessions which was causing the blocking.




Posted in Management, Scripts, SQL 2008, SQL Administration | Leave a Comment »

Drop SQL server replication after a server name change

Posted by rahmanagoro on August 4, 2011

After spending some time brushing up my photography skills, its back to the real world of work, coming in to work I had a request from a developer to setup SQL server replication on a publisher with one subscriber, I have a routine of powershell scripts that I normally run to do this. After completing the steps, I noticed some errors, upon investigating it became apparent that the identity of the server isn’t what it is expected to be. The servername is different for some reason.

After running the select command to view the server name,  I find that its different from the server in which I have logged in from.

I get the error

Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server, Line 31
Cannot drop server WRONGSERVER because it is used as a Publisher in replication.

–Trying to drop SQL replication I get the following errors


exec master..sp_dropdistributor @no_checks = 1

Msg 208, Level 16, State 1, Procedure sp_MSrepl_dropsubscription, Line 103
Invalid object name 'dbo.syssubscriptions'.
Msg 266, Level 16, State 2, Procedure sp_MSrepl_dropsubscription, Line 103
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Msg 266, Level 16, State 2, Procedure sp_dropsubscription, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Msg 266, Level 16, State 2, Procedure sp_MSpublishdb, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Msg 266, Level 16, State 2, Procedure sp_replicationdboption, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Msg 266, Level 16, State 2, Procedure sp_dropdistributor, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

In this case, I can tell that there has been an exception within the execution of the stored procedures. What needs doing is for the replication markers on the databases to be removed so that replication can be dropped cleanly on the database.

Step 1


-- Remove the replication marker on the databases

EXEC master..sp_MSforeachdb '
USE [?]
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
    SELECT ''?''

 IF OBJECT_ID(''tempdb..#dbproperty'') is not null
 drop table #dbproperty

 create table #dbproperty (OptionName   varchar(20), CurrentSetting varchar(10))

 insert into #dbproperty
 exec sp_dboption ''?'',''published''

 IF EXISTS (Select * from #dbproperty where CurrentSetting = ''ON''  )
 exec sp_removedbreplication @dbname = ''?'', @type = ''tran''

 IF OBJECT_ID(''tempdb..#dbproperty'') is not null
 drop table #dbproperty


This will now drop SQL replication.

exec master..sp_dropdistributor @no_checks = 1

It should now work.

Change the servername now.

sp_dropserver WRONGSERVER

— Create new server name

sp_addserver ‘RIGHTSERVER’,local

Restart the SQL service on the publisher.

The correct new servername should now be reflected on the SQL instance.

Now proceed to setting up SQL replication.


Posted in Scripts, SQL 2008, SQL replication, Tips | 2 Comments »

Troubleshooting using default trace information

Posted by rahmanagoro on March 11, 2011

The default trace is built into SQL server, its one that often tells you operations that are happening on the database server which is often not provided within the SQL error logs.

I regularly query the default trace to pick up issues that might be arising on the database server, the scripts that I use to query the default trace can be found below. The default trace also allows me to create policies using policy based management within SQL 2008, we once had a deployment using Visual studio and for those who have used visual studio, it creatred databases as a project and this is used in storing database schema, database deployment, integration with SVN etc. At the end of the day, the repository was not upto date and Auto skrink was turned on with visual studio, I found this had been turned on with visual studio. This was revealed within the default trace and I was able to create a policy to check for this across the database estate.

To get the default trace file currently in use, run the query below.

FROM fn_trace_getinfo(default);

To group the various events to see what has been happening on the database server, you can run the query below.

--Auditing various event types in the trace file.

trc_evnt.name, trc_evnt.category_id
,count(*) as occurence
FROM fn_trace_gettable('y:\databases\log_5170.trc', NULL) AS dflt_trc
INNER JOIN sys.trace_events AS trc_evnt
ON dflt_trc.EventClass = trc_evnt.trace_event_id
group by trc_evnt.name, trc_evnt.category_id
order by trc_evnt.name

You can change the default trace file to query the last five default traces, so for example y:\databases\log_5170.trc is the current trace, to check the one before this simply decrement the number by one, so run the query y:\databases\log_5169.trc.

I get information similar to the one below

Audit DBCC Event    8    9006
Audit Server Alter Trace Event    8    2
Hash Warning    3    2
Object:Created    5    644
Object:Deleted    5    201
Sort Warnings    3    1

I have used this method to troubleshoot various issues on the database such as the following:

missing statistics: I subesequently created and index or statistics on the column.

transaction logs growing every night: I changed the growth settings and expanded the size so as to remove the need for the growth.

Posted in Management, Scripts, SQL 2008, SQL Administration, Tips, Uncategorized | Leave a Comment »

Visual studio 2010 team edition database deployment and SQL replication support

Posted by rahmanagoro on November 2, 2010

I have been using Microsoft Visual Studio 2010 within our shop, we use it towards the management and version control of SQL databases, these databases are stored as projects under visual studio. It’s within an agile environment and releases can happen almost every weekend or during the week. We also use SQL transaction replication in order to maintain sets of reporting database servers.

In the last couple of weeks, I tried to do a deployment onto a publisher database and ran into and error with visual studio, it was trying to drop and object on the database whilst the object was being replicated. This obviously throws and exception on SQL

It’s official that Microsoft visual studio does not support SQL replication and the only known workaround to the problem is to change the deployment properties to ignore verification checks. This can either be done from the GUI or altering an XML file, which is what the GUI does.

You can get to the deployment properties by navigating to the following location from visual studio.

Project >> Properties >> Database deployment

Posted in Database development, Day to day queries, Management, Scripts, SQL 2008, SQL replication, Tips | Leave a Comment »

Restoring a database when an application or user is still connected

Posted by rahmanagoro on November 2, 2010

Restoring a database whilst application connections persists to the database. I have a .NET application which continually needs to query the database to fetch data therefore opening a connection to the database, the problem is that I need to refresh the data on testing with recent data from production. If one tries to restore the database, you get the error.

Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

In order to get around this problem, what I tend to do is set the database offline, do the restore and set it back online. This is to prevent the application from continuously connection to the database thereby failing the restore. The scripts are shown on this post.

Posted in Database development, Scripts, SQL High Availability | Leave a Comment »

Could not obtain information about Windows NT group/user ‘domain\xxxx’, error code 0x5. sp_dropdistributor

Posted by rahmanagoro on October 11, 2010

I have recently ran into an issue with dropping replication, it complains about the error below.

exec master..sp_dropdistributor @no_checks = 1
Msg 15404, Level 16, State 19, Procedure sp_replcmds, Line 1
Could not obtain information about Windows NT group/user ‘DOMAIN\xxxx.xxxxxx’, error code 0x5.

Exceptions like this are often caused when the login does not have access to a resource, or maybe when one is trying to detach/attach a database in SQL 2008 whilst using different user names. It sounded quite strange that I am having the same issues whilst trying to do something as simple as dropping replication on a test server. I setup a profiler trace for the session that I am using to try and track where the exception is occurring so as to know how to tackle the problem. I realized that the stored procedure was failing when it got the section trying to run the code.

sys.sp_replcmds 0

This stored procedure runs as part of exec master..sp_dropdistributor @no_checks = 1

Upon further investigation, I was able to run the stored procedure on the database which was throwing the exception, the first thing that came to my mind was, “which login owns the database ?”. I then ran the code below to check the owners of each and every database.

select name, SUSER_NAME(sid) as DBOwner from master..sysdatabases (nolock)
where name not like '%Snap'
order by name

Some of the databases came up as “sa” which is my preferred db owner and some came up as null, the ones that came up as null were databases that I had just restored recently using my windows login, once restored the SIDS did not match that which was on the master database in the syslogins table.

In order to fix the problem, I then ran the script below that will update all the database owners on the database to sa.

   EXEC master..sp_MSforeachdb '
USE [?]
IF DB_NAME() not in (''master'',''msdb'',''distribution'',''tempdb'',''model'')
	IF db_name() not like ''%Snap''
		   SELECT ''?''
			exec sp_changedbowner ''sa''

Now whilst trying to drop the distributor, it now works without any problem.

exec master..sp_dropdistributor @no_checks = 1

As a precaution, always change the owner of the databases to SA in order to avoid any such problems. Another issue that can occur is if the windows login which owns the database is removed from active directory ? thesame problem can occur.

   EXEC master..sp_MSforeachdb '
USE [?]
IF DB_NAME() not in (''master'',''msdb'',''distribution'',''tempdb'',''model'')
	IF db_name() not like ''%Snap''
		   SELECT ''?''
			exec sp_changedbowner ''sa''

Posted in Management, Scripts, SQL replication, Tips | 2 Comments »

Granting execute permissions to a database login permanently using db_executor role

Posted by rahmanagoro on August 30, 2010

I often find that some logins will need execute permissions to objects on the database, in some cases a development environment might exists where users dont have GUI interface to interact with the database, i.e every operation is all done through stored procedures. In such cases, it might be acceptable to grant execute permissions to all objects on the database.  

In SQL server the role db_datareader has always existed, but how come we don’t have a role called db_executor. In this blog, I will show you how you can create a custom role which gives execute permissions on stored procedures, functions on the database.  

For the purpose of demonstration I run the following scripts with SA privileges on the database  

--Create a database and a login named test, grant the test login read only access to the DEMO database
( NAME = N'DEMO', FILENAME = N'C:\Database\DEMO.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
( NAME = N'DEMO_log', FILENAME = N'C:\Database\DEMO_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

USE [master]
EXEC sp_addrolemember N'db_datareader', N'test'

Whilst still logged in as myself, create objects under the DEMO database. 

use DEMO
create table tblUser
UserID int identity(1,1),
UserName varchar(30)

insert into tblUser (UserName)
	Select 'Tom'
	Select 'Jen'


Create procedure spGetUserName
select UserID, UserName
from tblUser

exec spGetUserName

Now log on as the test user to the DEMO database and run the following scripts.  

select * from tblUser 
1 Tom
2 Jen 

Try executing the stored procedure we get the error below 
Msg 229, Level 14, State 5, Procedure spGetUserName, Line 1 
The EXECUTE permission was denied on the object ‘spGetUserName’, database ‘DEMO’, schema ‘dbo’. 
I can either grant execute permissions on this single stored procedure, but imagine that we had over a thousand stored procedures. One easy way to do this is to create a role and grant execute permission on the actual schema itself. 

use DEMO
CREATE ROLE [db_executor]
GRANT EXECUTE ON SCHEMA ::dbo TO db_executor
--Then we’ll add the Test user in the database to the db_executor role in the database:
EXEC sp_addrolemember N'db_executor', N'test'

After running the above, we now execute the stored procedure and it executes fine without any problems. 

execute spGetUserName 

1 Tom
2 Jen

Posted in Day to day queries, Scripts, Security, SQL 2008, SQL Administration | Leave a Comment »

Changing collation setting on a SQL 2008 instance

Posted by rahmanagoro on August 28, 2010

It is possible to chang the collation setting on a database, but what happens if you try to change the collation setting on a SQL instance or on the system databases, for SQL 2008 this can be done from the command line. Generally, what i tend to do is as follows.

  1. I will find the location of all my databases, as changing the collation forces a mini re-installation and thus will detach all user databases and rebuild system databases.
select * from master..sysaltfiles

2      A full backup of system databases, msdb, master and model is also recomended. Changing collation will rebuild system databases.

Lets assume that the drive containing the SQL 2008 installation media is E:\

Follow the steps below from the command prompt

cd E:



The parameters are as follows, /SQLSYSADMINACCOUNTS this specifies the sysadmin account nominated.

/SAPWD = This is the SA password to be used afterwards.

Posted in Day to day queries, Scripts, SQL 2008, SQL Administration, Uncategorized | Leave a Comment »

Script out SQL agent jobs from powershell

Posted by rahmanagoro on August 26, 2010

At my shop, we currently use SVN to store database objects, one object type which is often overlooked is SQL agent jobs, I needed a way in which I can generate sql agent jobs into sql files, SQL management studio allows you to script jobs, but you can either do this one at a time, or script all the jobs in one big file. The two options are not really ideal for me, the reason being that I may choose to be selective about which jobs I install on a server, other jobs are installed as part of an installation suite, e.g management data warehouse in SQL 2008.

I have written a simple powershell script that will simple script out all SQL agent jobs, one might have multiple servers, I have made the script such that it reads a text file for a list of servers, simply save this file on a location and call the powershell script, you also need to specify where you want the script to be generated to as well.

# Load SMO extension

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
# Get List of sql servers to check

#$sqlservers = Get-Content "$Env:USERPROFILE\sqlservers.txt";
$sqlservers = Get-Content "C:\Servers.txt";

# Loop through each sql server from sqlservers.txt
foreach($sqlserver in $sqlservers)


      # Create an SMO Server object
      $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver;

      # Jobs counts
      $totalJobCount = $srv.JobServer.Jobs.Count;
      $failedCount = 0;
      $successCount = 0;

      # For each jobs on the server
      foreach($job in $srv.JobServer.Jobs)

            # Default write colour
            $colour = "Green";
            $jobName = $job.Name;
            $jobEnabled = $job.IsEnabled;
            $jobLastRunOutcome = $job.LastRunOutcome;
 			$jobNameFile = "C:\SQLJOBS\" + $jobName+".sql"

 			Write-Host $job.Name
			Write-Host "The location of the file is called " $jobNameFile

#			[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
#			$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
#			#$srv.JobServer.Jobs | foreach {$_.Script()} | out-file -path $path
			$job | foreach {$_.Script()} | out-file $jobNameFile

            # Set write text to red for Failed jobs
            if($jobLastRunOutcome -eq "Failed")


                  $colour = "Red";
                  $failedCount += 1;

            elseif ($jobLastRunOutcome -eq "Succeeded")
                  $successCount += 1;

            Write-Host -ForegroundColor $colour "SERVER = $sqlserver JOB = $jobName ENABLED = $jobEnabled LASTRUN = $jobLastRunOutcome";

      # Writes a summary for each SQL server
      Write-Host -ForegroundColor red "=========================================================================================";
      Write-Host -ForegroundColor red "$sqlserver total jobs = $totalJobCOunt, success count $successCount, failed jobs = $failedCount.";
      Write-Host -ForegroundColor red "=========================================================================================";

Posted in Day to day queries, Powershell, Professional Development, Scripts, SQL 2008, Tips | 4 Comments »

Searching for objects in a database views stored procedures functions etc etc

Posted by rahmanagoro on August 26, 2010

I am often faced with the need to search for objects on a database, for example i want to ensure that there are no other objects referencing the objects, or perhaps I need to change the schema of a table, but want to make sure that I capture and edit all other objects that rely on the object i.e a table.

In one example, I noticed an application was using custom key generation for generating a unique ID, this was causing a deadlock on the database, and I pinned down the problem to a exclusive lock being held on the table during another update, whilst a new key was being generated.

select @myID = max([ID])  + 1 from Company.dbo.tbluser

In order to complete regression testing, I need to ensure that all objects making reference to the table were correctly identified and the code changed.

EXEC sp_MSforeachdb 
'if ''?''  ''master'' and ''?''  ''tempdb''and ''?''  ''model''and ''?''  ''msdb''and ''?''  ''distribution''
select ''Finding the use of tbluser on database '' ?
Use ?

from syscomments A
join sysobjects B
on A.id = B.id
where A.text like ''%tbluser%''
or text like ''% INSERT tbluser %''
order by OBJECT_NAME(A.id), B.type

In the example below, I was looking for any object across the database that use the COLLATE or collate hint, as the database server was case sensitive, I have to try both lower and upper case.

EXEC sp_MSforeachdb 
'if ''?''  ''master'' and ''?''  ''tempdb''and ''?''  ''model''and ''?''  ''msdb''and ''?''  ''distribution''
select ''Finding the use of collate on DB '' ?
Use ?

from syscomments A
join sysobjects B
on A.id = B.id
where A.text like ''%collate%''
or text like ''%%COLLATE%''
order by OBJECT_NAME(A.id), B.type

Posted in Day to day queries, Scripts, SQL 2008 | Leave a Comment »