Master database in single user mode after restore

Recently I had to restore master database on a SQL instance. One way to restore master database is

  1. Restore the master database as user database on same version of SQL.
  2. Detach the database from the instance.
  3. Copy the files to original instance.
  4. Restart the original instance.

Make sure the logical and physical file names are same as original when the database was restored in step 1.

Anyways, after I did the above steps, I noticed the master database was coming up in “single user” mode on the original instance ( Step 4).

I, eventually, realized that it was due to how I was detaching the database. So, when I was detaching the database, I selected the option to “drop connections” in the UI and SQL accomplishes this by putting the DB in single user mode before it detaches.

That’s the reason why it was coming up in single user mode on the original instance when the files were copied over.

Query to find the Log File Internals

The following script helps in understanding the log file. It gives all the details about transaction log file such as no of VLF’s associated,log internal space usage,last log backup taken,DB recovery mode etc. I think this script helps in understanding the internal log file usage and can be used as a health check script.

/*Databases that are set to 'AutoClose' are not considered by dbcc sqlpref(logspace) and Offline databases are not considered.[FreeSpace_IN_LogFile_toShrink_InBytes] means the amount of shrinkable Log File space in bytes.It calculates this by summing the inactive VLF's from bottom up till it hits active VLF's(Status=2).This does not mean you *can* shrink the Log File.
Please use caution. This is only for informational purposes.
Also, added code to check SQL version because dbcc loginfo on sql 2012 and above returns one extra column.*/

If object_ID('Tempdb.dbo.#TempMain') is not null
Begin
Drop Table #TempMain
End

Create Table #TempMain (
SeqID int Identity(1,1),[Database Name] varchar(max),[Log Size(MB)] decimal(15,3),[Log Space Used(%)] decimal(15,3),[Status] int,UsedLogFileSize_InMB decimal(15,3),No_of_Active_VLFs int,
UnUsedLogFileSize_InMB decimal(15,3),No_Of_InActive_VLFs int,
ReasonFor_LogFile varchar(50),LastLogBackupTaken datetime,Db_RecoveryMode varchar(11),[FreeSpace_IN_LogFile_toShrink_InMB] decimal(15,3))

If object_ID('Tempdb.dbo.#Temp') is not null
Begin
Drop Table #Temp
End

Create Table #Temp (ID int Identity(1,1),RecoveryUnitId int,FileID tinyint,FileSize bigint,StartOffset varchar(max),FSeqNo int,[Status] tinyint,Parity tinyint,CreateLSN varchar(max))
declare @SQlLogSpace nvarchar(max)
set @SQlLogSpace = 'dbcc sqlperf(logSpace)'

Insert #TempMain([Database Name],[Log Size(MB)],[Log Space Used(%)],[Status])
Execute(@SQlLogSpace)

update #TempMain set Db_RecoveryMode =A.recovery_model_desc,
ReasonFor_LogFile=Log_reuse_wait_desc,LastLogBackupTaken=C.[LastLogBackupTime]
from Sys.databases A INNER JOIN #TempMain B on B.[Database Name]=A.Name
LEFT OUTER JOIN (Select max(backup_finish_date) as [LastLogBackupTime],database_name from msdb.dbo.backupset A
INNER JOIN Sys.databases B on B.Name=A.Database_Name
where type ='L' and B.recovery_model_desc in ('FULL','Bulk-Logged')
group by database_name) C on C.[database_Name]=A.Name

update #TempMain set UsedLogFileSize_InMB=cast(cast(([Log Size(MB)]) as decimal(20,3))*cast(([Log Space Used(%)]/100) as decimal(20,3))as decimal(20,3)),
unUsedLogFileSize_InMB=cast(([Log Size(MB)]) as decimal(20,3))-cast(cast(([Log Size(MB)]) as decimal(20,3))*cast(([Log Space Used(%)]/100) as decimal(20,3))as decimal(20,3))
from #TempMain

declare @a int,@b varchar(max)
select @a =min(SeqID) from #TempMain
While (@a>=1)

Begin
Select @b=[Database Name] from #TempMain where seqID=@a
declare @Sql nvarchar(max)
IF (Substring(cast(SERVERPROPERTY('productversion') as varchar(16)),1,
CHARINDEX('.',cast(SERVERPROPERTY('productversion') as varchar(16)),1)-1))>10
Begin
Set @sql = 'USE ['+@B +'] declare @SQlLogInfo nvarchar(max)
set @SQlLogInfo = ''dbcc loginfo'' Insert into #Temp( RecoveryUnitId,FileID,FileSize,StartOffSet,FSeqNo,Status,Parity,CreateLSN)
Execute(@SQlLogInfo)'
End
Else
Begin
Set @sql = 'USE ['+@B +'] declare @SQlLogInfo nvarchar(max)
set @SQlLogInfo = ''dbcc loginfo'' Insert into #Temp( FileID,FileSize,StartOffSet,FSeqNo,Status,Parity,CreateLSN)
Execute(@SQlLogInfo)'
End

Execute Sp_Executesql @SQL

Update #TempMain Set No_Of_InActive_VLFs =B
From (Select sum(FileSize) as A ,Count(FileID) as B from #Temp where [status] = 0) A where #TempMain.SeqID=@a

Update #TempMain Set No_Of_Active_VLFs = B
From (Select sum(FileSize) as A ,Count(FileID) as B from #Temp where [status] = 2) A where #TempMain.SeqID=@a

Update #TempMain Set [FreeSpace_IN_LogFile_toShrink_InMB] = A
From (select cast((cast(sum(FileSize) as decimal(15,3))/1024.00/1024.00) as decimal(15,3)) as A from #Temp where ID>(Select Max(ID) from #Temp where Status=2)) A where #TempMain.SeqID=@a

Truncate table #Temp

IF (@a<(Select Max(seqId) from #TempMain))
Begin
Set @a=@a+1
continue
End
Else
Begin
select SeqID,[Database Name],Db_RecoveryMode,[Log Size(MB)],
[Log Space Used(%)],UsedLogFileSize_InMB,No_Of_Active_VLFs,
UnUsedLogFileSize_InMB,No_Of_InActive_VLFs,[FreeSpace_In_LogFile_ToShrink_InMB],
ReasonFor_LogFile,LastLogBackupTaken from #TempMain
Return
End
Drop table #TempMain
End

Moving system database files to new location

Sometimes, we need to move system databases to a different location for various reasons. Moving system databases is little different compared to user databases. TempDB is the usual suspect as it can fill up the disk space and if it is not on a dedicated disk, it can cause more problems. In that case, we can either add a new file or move the TempDB to different drive.

To add new file to TempDB, you can do as below.
USE [MASTER]
GO
Alter database TempDB
Add File (Name= 'TempDev2', FileName = 'E:\TempDBData\TempDev2.ndf',SIZE = 4096MB , FILEGROW =500MB)

Note: TempDB cannot have user defined filegroups. Also, Sql Server uses Proportional Fill algorithm to split the data across multiple files. So, use caution on how you allocate the disk space.

If you could afford some downtime and prefer to move the TempDB files to different location, you can do as below and RESTART the sql server.
USE [MASTER]
GO
Alter database TempDB
MODIFY File (Name= 'TempDev', FileName = 'E:\TempDBData\TempDev.mdf',SIZE = 4096MB , FILEGROW =500MB)
GO
Alter database TempDB
MODIFY File (Name= 'TempLog', FileName = 'E:\TempDBLog\TempLog.ldf',SIZE = 1024MB , FILEGROW =250MB)

NOTE: one of the best practice suggestion is to assign one tempdb file for every 4 logical processors, i.e, if you have 16 logical processors, it is best to have maximum of 4 TempDB files.

MSDB: I recently had to move MSDB to different drive and below are steps I followed to accomplish the same :
1. Use Alter Database .. Modify File .. command to make the sys.database_files point to the new location.
2. We cannot set the MSDB database to offline. So, we need to stop the SQL Server.
3. Copy over the files to new location.
4. Restart the SQL Server.

How to move user database files to a different location

Sometimes, we need to move the user database files to a different location. There are several ways to do it,

One way is to use sp_detach_db and copy the files and use sp_attach_db.

sp_detach_db cannot be used, if the database is being replicated. Also,sp_attach_db is not recommended anymore by Microsoft.

One other way to move database files to new location is:

USE [TESTA]
GO
Select * from sys.database_files--This gives the current location of the files
GO

USE [master]
GO
Alter database TestA
Modify File (Name = 'TestA',filename= N'E:\Data\Data\TestA.mdf')
GO
Alter Database TestA
Modify File (Name = 'TestA_Log',FileName = N'E:\Data\Data\TestA_Log.Ldf')
GO

ALTER DATABASE TESTA Set OFFLNE

---Copy the physical files to the new location

ALTER DATABASE TestA Set ONLINE

If you are moving the files to a new location, the path should already exists(I.e.I moved the files to new location,”E:Data\Data”. This path along with the folder structure should already exists).

Also, This method works for replicated databases as well. It is better to stop the replication jobs while doing this operation and enable them again.