May 242016
 

Have you ever tried to restore over an existing database only to receive the following error message?

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing ‘AdventureWorks2012’ database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

We could easily avoid this error by using the WITH REPLACE option in the RESTORE DATABASE command, but we want to know why this error occurs and how SQL Server knows they are not from the same family. The answer is the database_guid.

The database_guid is a GUID that is unique to every database; even across servers. If you restore a backup to another server, the restored database will get a new database_guid. You can view this value for every database by looking in the catalog view sys.database_recovery_status.

SELECT DB_NAME(database_id), database_guid
FROM master.sys.database_recovery_status;

If the database_guid is different for every database regardless of which SQL Server it’s on, then how does the restore still know the backup and database are from two different families? The answer is the family_guid.

The family_guid is the original database_guid when the database was first created, and does not change for the life of that database. When you issue a RESTORE DATABASE command, SQL Server will compare the family_guid to the database_guid if you are attempting to restore over an existing database. When those numbers don’t match, you will receive that error. You can get the family_guid from the same catalog view.

SELECT DB_NAME(database_id), database_guid, family_guid
FROM master.sys.database_recovery_status

You can use the following code to pull the family_guid from the backup and then compare it to the database you are trying to overwrite.

-- Build a temp table to store the backup file header information.
IF OBJECT_ID('tempdb..#BackupHeader') IS NOT NULL
  DROP TABLE #BackupHeader;
GO

CREATE TABLE #BackupHeader (
   BackupName nvarchar(128)
  ,BackupDescription nvarchar(255)
  ,BackupType smallint
  ,ExpirationDate datetime
  ,Compressed bit
  ,Position smallint
  ,DeviceType tinyint
  ,UserName nvarchar(128)
  ,ServerName nvarchar(128)
  ,DatabaseName nvarchar(128)
  ,DatabaseVersion int
  ,DatabaseCreationDate datetime
  ,BackupSize numeric(20,0)
  ,FirstLSN numeric(25,0)
  ,LastLSN numeric(25,0)
  ,CheckpointLSN numeric(25,0)
  ,DatabaseBackupLSN numeric(25,0)
  ,BackupStartDate datetime
  ,BackupFinishDate datetime
  ,SortOrder smallint
  ,CodePage smallint
  ,UnicodeLocaleId int
  ,UnicodeComparisonStyle int
  ,CompatibilityLevel tinyint
  ,SoftwareVendorId int
  ,SoftwareVersionMajor int
  ,SoftwareVersionMinor int
  ,SoftwareVersionBuild int
  ,MachineName nvarchar(128)
  ,Flags int
  ,BindingID uniqueidentifier
  ,RecoveryForkID uniqueidentifier
  ,Collation nvarchar(128)
  ,FamilyGUID uniqueidentifier
  ,HasBulkLoggedData bit
  ,IsSnapshot bit
  ,IsReadOnly bit
  ,IsSingleUser bit
  ,HasBackupChecksums bit
  ,IsDamaged bit
  ,BeginsLogChain bit
  ,HasIncompleteMetaData bit
  ,IsForceOffline bit
  ,IsCopyOnly bit
  ,FirstRecoveryForkID uniqueidentifier
  ,ForkPointLSN numeric(25,0) NULL
  ,RecoveryModel nvarchar(60)
  ,DifferentialBaseLSN numeric(25,0) NULL
  ,DifferentialBaseGUID uniqueidentifier
  ,BackupTypeDescription nvarchar(60)
  ,BackupSetGUID uniqueidentifier NULL
  ,CompressedBackupSize bigint
  ,containment tinyint NOT NULL
);

--SQL Server 2014/2016 have three extra columns in the file header.
IF (@@MICROSOFTVERSION/POWER(2,24) > 11)
ALTER TABLE #BackupHeader
ADD
   KeyAlgorithm nvarchar(32)
  ,EncryptorThumbprint varbinary(20)
  ,EncryptorType nvarchar(32);
GO

-- Insert file header info into the temp table.
INSERT #BackupHeader
EXEC ('RESTORE HEADERONLY FROM DISK = ''C:\SQL_Backup\AdventureWorks2012.bak''');
GO

-- Compare the family_guid values.
SELECT DatabaseName,FamilyGUID FROM #BackupHeader;
GO

SELECT DB_NAME(database_id),family_guid FROM sys.database_recovery_status
WHERE database_id = DB_ID('AdventureWorks2012');
GO

As you can see they do not match, which is why we get the error.

BONUS INFO:
There is one case where the database_guid can be the same even for different databases. If you detach a database, make a copy of the MDF and LDF files, and then re-attach those files as a different database name, the database_guid values will be the same.

SELECT DB_NAME(database_id), database_guid, family_guid
FROM sys.database_recovery_status;

As you can see, the database_guid and family_guid are the same for all three copies of the AdventureWorks database.

Have you ever tried to restore over an existing database only to receive the following error message?

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing ‘AdventureWorks2012’ database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

We could easily avoid this error by using the WITH REPLACE option in the RESTORE DATABASE command, but we want to know why this error occurs and how SQL Server knows they are not from the same family. The answer is the database_guid.

The database_guid is a GUID that is unique to every database; even across servers. If you restore a backup to another server, the restored database will get a new database_guid. You can view this value for every database by looking in the catalog view sys.database_recovery_status.

SELECT DB_NAME(database_id), database_guid
FROM master.sys.database_recovery_status;

If the database_guid is different for every database regardless of which SQL Server it’s on, then how does the restore still know the backup and database are from two different families? The answer is the family_guid.

The family_guid is the original database_guid when the database was first created, and does not change for the life of that database. When you issue a RESTORE DATABASE command, SQL Server will compare the family_guid to the database_guid if you are attempting to restore over an existing database. When those numbers don’t match, you will receive that error. You can get the family_guid from the same catalog view.

SELECT DB_NAME(database_id), database_guid, family_guid
FROM master.sys.database_recovery_status;

You can use the following code to pull the family_guid from the backup and then compare it to the database you are trying to overwrite.

-- Build a temp table to store the backup file header information.
IF OBJECT_ID('tempdb..#BackupHeader') IS NOT NULL
  DROP TABLE #BackupHeader;
GO
CREATE TABLE #BackupHeader (
   BackupName nvarchar(128)
  ,BackupDescription nvarchar(255)
  ,BackupType smallint
  ,ExpirationDate datetime
  ,Compressed bit
  ,Position smallint
  ,DeviceType tinyint
  ,UserName nvarchar(128)
  ,ServerName nvarchar(128)
  ,DatabaseName nvarchar(128)
  ,DatabaseVersion int
  ,DatabaseCreationDate datetime
  ,BackupSize numeric(20,0)
  ,FirstLSN numeric(25,0)
  ,LastLSN numeric(25,0)
  ,CheckpointLSN numeric(25,0)
  ,DatabaseBackupLSN numeric(25,0)
  ,BackupStartDate datetime
  ,BackupFinishDate datetime
  ,SortOrder smallint
  ,CodePage smallint
  ,UnicodeLocaleId int
  ,UnicodeComparisonStyle int
  ,CompatibilityLevel tinyint
  ,SoftwareVendorId int
  ,SoftwareVersionMajor int
  ,SoftwareVersionMinor int
  ,SoftwareVersionBuild int
  ,MachineName nvarchar(128)
  ,Flags int
  ,BindingID uniqueidentifier
  ,RecoveryForkID uniqueidentifier
  ,Collation nvarchar(128)
  ,FamilyGUID uniqueidentifier
  ,HasBulkLoggedData bit
  ,IsSnapshot bit
  ,IsReadOnly bit
  ,IsSingleUser bit
  ,HasBackupChecksums bit
  ,IsDamaged bit
  ,BeginsLogChain bit
  ,HasIncompleteMetaData bit
  ,IsForceOffline bit
  ,IsCopyOnly bit
  ,FirstRecoveryForkID uniqueidentifier
  ,ForkPointLSN numeric(25,0) NULL
  ,RecoveryModel nvarchar(60)
  ,DifferentialBaseLSN numeric(25,0) NULL
  ,DifferentialBaseGUID uniqueidentifier
  ,BackupTypeDescription nvarchar(60)
  ,BackupSetGUID uniqueidentifier NULL
  ,CompressedBackupSize bigint
  ,containment tinyint NOT NULL
);

--SQL Server 2014/2016 have three extra columns in the file header.
IF (@@MICROSOFTVERSION/POWER(2,24) > 11)
ALTER TABLE #BackupHeader
ADD 
   KeyAlgorithm nvarchar(32)
  ,EncryptorThumbprint varbinary(20)
  ,EncryptorType nvarchar(32);
GO
 
-- Insert file header info into the temp table.
INSERT #BackupHeader
EXEC ('RESTORE HEADERONLY FROM DISK = ''C:\SQL_Backup\AdventureWorks2012.bak''');
GO
 
-- Compare the family_guid values.
SELECT DatabaseName,FamilyGUID FROM #BackupHeader;
GO
 
SELECT DB_NAME(database_id),family_guid FROM sys.database_recovery_status
WHERE database_id = DB_ID('AdventureWorks2012');
GO

As you can see they do not match, which is why we get the error.

BONUS INFO:
There is one case where the database_guid can be the same even for different databases. If you detach a database, make a copy of the MDF and LDF files, and then re-attach those files as a different database name, the database_guid values will be the same.

SELECT DB_NAME(database_id), database_guid, family_guid
FROM sys.database_recovery_status;

As you can see, the database_guid and family_guid are the same for all three copies of the AdventureWorks database.

Jul 072015
 

Recently, I ran into a critical error while I was helping a customer troubleshoot an issue in SQL Server. That may not sound like a big deal, but we were installing Cumulative Update 6 for SQL Server 2012 SP2 to fix our initial problem when we encountered the following error.

What gives SQL Server? We just upgraded several other instances on this same server. Why does this one have to fail right now? Well, a quick peak in the errorlog sure points me in the right direction.

Starting execution of SSIS_HOTFIX_INSTALL.SQL
:
:
Error: 9002, Severity: 17, State: 4.
The transaction log for database ‘SSISDB’ is full due to ‘ACTIVE_TRANSACTION’.

Error: 912, Severity: 21, State: 2.
Script level upgrade for database ‘master’ failed because upgrade step ‘SSIS_hotfix_install.sql’ encountered error 3602, state 251, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

Error: 3417, Severity: 21, State: 3.
Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

SQL Server shutdown has been initiated.

This is definitely not good. Here we are trying to apply CU6 to fix one problem, and it we get another one in return. Needless to say, the CU6 upgrade failed, and SQL Server was dead in the water. Restarting the SQL Server service will just continue to run the internal upgrade and return the same error message.

It’s easy to see the root cause of the problem; the transaction log for the SSISDB database is full. But if we can’t start the service how will we ever get the error fixed? Lucky for us, Microsoft has a trace flag for that. Trace Flag 902 will bypass the internal upgrade script on startup. Just open SQL Server Configuration Manager and add -T902 to the Startup Parameters, save the changes, and then start the service.

SQL Server started up just fine and fully recovered all databases; including SSISDB. However, there was a warning about 1 transaction being rolled back for SSISDB.

1 transactions rolled back in database ‘SSISDB’ (5:0). This is an informational message only. No user action is required.

This is most likely from the SSIS_HOTFIX_INSTALL.SQL script that failed during the upgrade. Next, we need to check the configuration and the file sizes of the SSISDB database. Sure enough, the log file is still at its original size of 1MB with autogrowth was disabled.

For now, we can enable autogrowth for the log file and then save the changes. Next, we’ll need remove the T902 trace flag from the Startup Parameters, and then restart the SQL Server service. Finally, SQL Server was able to startup without any errors. In the errorlog we can see the internal upgrade script ran successfully.

Starting execution of SSIS_HOTFIX_INSTALL.SQL
Start applying SSIS_HOTFIX_INSTALL changes.
:
:
Schema build in SSISDB has been updated to 11.0.5592.0
Execution of SSIS_HOTFIX_INSTALL.SQL completed

At this point, we need to rerun the CU6 setup again, so it can verify everything was correctly installed.

It shows the current patch level at 11.2.5592.0, which is correct, but the status is “Incompletely installed”. Just click next to continue with the upgrade to clear out the previous install failures.

So what is the lesson learned from this error? Should we leave autogrowth enabled on all databases before running a patch? Not necessarily, but it is an option. I think a better option is to keep a mental note of Trace Flag 902. With that trace flag, we were able to get SQL Server online long enough to correct the root problem, and we didn’t have to worry about changing any settings on a database.

References:

Jun 252013
 
Industry experts will tell you that virtualization of your environment is not done to improve performance, it’s done to make it cheaper and easier to manage.  The task of most VM administrators is to cram as many VMs into a farm as possible.  One of the ways is to accomplish that is to allocate “thin provisioned” storage to each VM.
For each VM that is created, the VM admin has to specify the number of virtual CPUs, the amount of virtual RAM, the number and size of each virtual disk, as well as a few other items.  The virtual disks can be allocated in two different ways: thin provision or thick provision.  The difference between thick and thin is very simple and outlined in this diagram from VMware.
Thick provisioned storage allocates all storage when the disk is created.  This means if a VM admin allocate 25GB for a virtual disk, then VMDK file on the host is actually 25GB. 
Thin provisioned storage allows the VM admin to essentially over allocate storage, much in the same way they can over allocate memory.  For example, if a VM admin allocates 25GB for a virtual disk, then the VMDK file will start out at a few MB then grow as the space is used by the VM.  However, within the VM, the Windows operating system will see the disk as having a total capacity of 25GB.
Below, you can see Windows shows both Drive E and F as 25GB in size.

However, vSphere shows the thick provisioned disk (Drive E) as 25GB, but the thin provisioned disk (Drive F) is 0GB.
VMSTORAGETEST_4-flat.vmdk is the thick provisioned disk (Drive E).
VMSTORAGETEST_5-flat.vmdk is the thin provisioned disk (Drive F).
Thin provisioning is a great concept for using only what you need, and not allowing you waste valuable storage.  However, this can have a detrimental effect on database performance.  Thin provisioned disk will auto grow the VMDK file as the VM needs more space on that disk.  When VMware needs to grow the VMDK file, it will cause a delay in the VM’s disk access while it’s growing.  Let’s take a look at a few examples.
Example 1 – File copy from within Windows
In this test, we’ll use ROBYCOPY to copy a 20GB folder from the C drive to the thick provisioned disk (Drive E).
ROBOCOPY C:SQL E:SQL *.* /E /NFL /NDL /NJH
Copy time of 4 min 24 sec at a rate of 82MB/sec.

Now let’s do the same copy to the thin provisioned disk (Drive F) and compare the results.

ROBOCOPY C:SQL F:SQL *.* /E /NFL /NDL /NJH

Copy time of 5 min 01 sec at a rate of 73MB/sec.

Windows is getting nearly 10MB/sec faster copy times to the thick provisioned disk (Drive E).

Example 2 – Database backup to disk from SQL Server
In this test, we’ll backup a database to each of the disks and compare the runtimes.

First, we’ll backup to the thick provisioned disk (Drive E).

BACKUP DATABASE AdventureWorks2012
TO DISK = ‘E:AdventureWorks2012.BAK’WITH INIT;
GO
Processed 449472 pages for database ‘AdventureWorks2012’, file ‘AdventureWorks2012_Data’ on file 1.
Processed 2 pages for database ‘AdventureWorks2012’, file ‘AdventureWorks2012_Log’ on file 1.
BACKUP DATABASE successfully processed 449474 pages in 74.125 seconds (47.372 MB/sec).
Now backup to the thin provisioned disk (Drive F).

BACKUP DATABASE AdventureWorks2012
TO DISK = ‘E:AdventureWorks2012.BAK’WITH INIT;
GO
Processed 449472 pages for database ‘AdventureWorks2012’, file ‘AdventureWorks2012_Data’ on file 1.
Processed 2 pages for database ‘AdventureWorks2012’, file ‘AdventureWorks2012_Log’ on file 1.
BACKUP DATABASE successfully processed 449474 pages in 83.285 seconds (42.162 MB/sec).
As you can see, we’re seeing similar results that we saw in our earlier test.  Within SQL Server we’re getting about 5MB/sec faster backup times. 

After running these tests, we can look back in vSphere to see the new size of the VMDK file for our thin provisioned disk (Drive F).  You’ll see the VMDK is now showing over 24GB of used space for that file.

These simple tests reveal that thin provisioning storage within VMware can indeed impact performance.  This doesn’t mean that you should thick provision storage on every VM, but it does show you how this configuration can affect Windows and SQL Server.  You can equate this to the data/log file auto grow feature within SQL Server; you should right-size the virtual disk from day one the same way you should right-size your database files from day one.  As I stated earlier, virtualizing your SQL Servers is done to make things cheaper and easier to manage, not to make them perform better.
May 282013
 
SQL Server includes a DMV, sys.dm_exec_query_stats, that returns performance statistics for each query plan cached in memory.  However, it can also help give you insight into how consistent your developers are with writing code.
For this topic, we’ll just concentrate on a few columns returned by the DMV: sql_handle and plan_handle.  Per Books Online, sql_handle is a value that refers to the batch or stored procedure that the query, and plan_handle is a value that refers to the compiled plan of that query.  For each query that is processed, SQL Server can generate one or more compiled plans for that query.  This one-to-many relationship can be caused by a number factors, but one simple reason can be coding inconsistency.
One simple coding difference that I often see is within the SET statements preceding a query.  If a developer executes the exact same query using different SET statements, then SQL Server will compile a separate plan for each one.
First, we need to clear the cache.
DBCC FREEPROCCACHE;
GO

Next run these two queries.
SET QUOTED_IDENTIFIER OFF;
GO
SELECT p.FirstName,p.LastName FROM Person.Person p
JOIN HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.Gender =‘M’;
GO
SET QUOTED_IDENTIFIER ON;
GO
SELECT p.FirstName,p.LastName FROM Person.Person p
JOIN HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.Gender =‘M’;
GO

As you can see, the only difference between the two queries is the value for SET QUOTED_IDENTIFIER. Now let’s query the DMV.
SELECT s.text, q.sql_handle, q.plan_handle
FROM sys.dm_exec_query_statsq CROSS APPLY sys.dm_exec_sql_text(sql_handle) s;
GO


We can see that we have 2 rows returned, one for each query.  As you’ll notice, the sql_handleis the same for each, but the plan_handle is different.  Next let’s look at the graphical query plan of each.

SELECT * FROM sys.dm_exec_query_plan(0x0600050049DA7633D08998220000000001000000000000000000000000000000000000000000000000000000);
GO
SELECT * FROM sys.dm_exec_query_plan(0x0600050049DA7633908298220000000001000000000000000000000000000000000000000000000000000000);
GO
You will see the query plan is the same; however, SQL Server treats each one as if it were a completely distinct query.  If this were just a typo by the developer, then SQL Server just doubled the amount of plan cache needed for this query and wasted valuable resources.
Let’s look at the same queries from another angle.  This time we’ll remove the SET statements, but change the formatting of the queries.
First clear the plan cache.
DBCC FREEPROCCACHE;
GO
Next run these two queries.
SELECT p.FirstName,p.LastName FROM Person.Person p
JOIN HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.Gender =‘M’;
GO

SELECT
        p.FirstName
       ,p.LastName
FROM Person.Person p
JOIN HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.Gender =‘M’;
GO
Finally, look at the DMV.
SELECT s.text, q.sql_handle, q.plan_handle
FROM sys.dm_exec_query_statsq CROSS APPLY sys.dm_exec_sql_text(sql_handle) s;
GO

What you’ll notice is SQL Server will still treat each query as if it were two completely different statements; however, the only difference is the formatting.

In these examples, we’ve covered how important it is for the developers to be consistent with all the code passed to SQL Server.  Just minor changes in the code will cause SQL Server to generate different query plans and lead to plan cache bloat and wasted resources.  As a DBA, these are some simple examples of feedback you should be providing to your development teams.  Be proactive and don’t let them down!

Mar 122013
 

T-SQL Tuesday #40 is underway, and this month’s host is Jennifer McCown (blog|twitter).  The topic is about File and Filegroup Wisdom.  Jennifer says she’s a big fan of the basics, so I thought I would talk about the basics of proportional fill within a filegroup.  This should be pretty common knowledge, but I still talk to a lot of DBAs that don’t know anything  about it, or if they have heard of it, they still don’t know how it works.
The proportional fill algorithm is used to keep the amount of free space within a filegroup evenly distributed across all files in a filegroup.  SQL Server’s proportional fill falls in line with the strategy of placing your files and filegroups across multiple disks, and thus, allowing for improved I/O performance.
Let’s say we need to add more storage space for our AdventureWorks2012 database that has outgrown the current drive D.  Because of storage limitations, we can’t add any more space to D, so our only choice is to add a completely new drive E.  
Once we add the new E drive to the server, we add a new data file to the PRIMARY filegroup of the AdventureWorks2012database using the following query.
USE master;
GO
ALTER DATABASE AdventureWorks2012
ADD FILE (
     NAME = N’AdventureWorks2012_Data2′
    ,FILENAME = N’E:MSSQL11.TEST1MSSQLDATAAdventureWorks2012_Data2.ndf’
    ,SIZE = 200MB
    ,FILEGROWTH = 1024KB
) TO FILEGROUP [PRIMARY];
GO

One might think we’re safe at this point; however, because of the proportional fill feature we’re not.  Once new data is written to the data files, SQL Server will create the new page allocations on the newly created AdventureWorks2012_Data2.ndf file because it has a higher percentage of free space compared to AdventureWorks2012_Data.mdf.  Drive E now suddenly becomes a new I/O hotspot on the server.
You can check the space used with the following query.
USE AdventureWorks2012;
GO
SELECT
     name AS ‘LogicalName’
    ,physical_name AS ‘PhysicalName’
    ,CONVERT(INT,ROUND(size/128,0)) AS ‘Size (MB)’
    ,CONVERT(INT,ROUND(FILEPROPERTY(name,‘SpaceUsed’)/128,0)) AS ‘SpaceUsed (MB)’
FROM sys.database_files
WHERE type = 0;
GO

To avoid this disk hotspot issue, we need to have the data more evenly balanced across both files in the filegroup in terms of data page allocations.  The quickest way to do this is to rebuild all of the clustered indexes within the database.
ALTER INDEX [PK_AWBuildVersion_SystemInformationID] ON [dbo].[AWBuildVersion] REBUILD;
ALTER INDEX [PK_ErrorLog_ErrorLogID] ON[dbo].[ErrorLog] REBUILD;
ALTER INDEX [PK_Department_DepartmentID] ON[HumanResources].[Department] REBUILD;
:
:
ALTER INDEX [PK_Store_BusinessEntityID] ON[Sales].[Store] REBUILD;
GO

SQL Server will do its best to automatically rebalance all of the page allocations across all files within the same filegroup.  In our case, both data files are still part of the PRIMARY filegroup. 
Check the space used again with the following query.
USE AdventureWorks2012;
GO
SELECT
     name AS ‘LogicalName’
    ,physical_name AS ‘PhysicalName’
    ,CONVERT(INT,ROUND(size/128,0)) AS ‘Size (MB)’
    ,CONVERT(INT,ROUND(FILEPROPERTY(name,‘SpaceUsed’)/128,0)) AS ‘SpaceUsed (MB)’
FROM sys.database_files
WHERE type = 0;
GO

Now what we have is much more evenly balanced allocation across both data files.  This will allow SQL Server to even distribute the write I/O across both disk drives.
By doing this one index maintenance step after adding a new file, you’ll help prevent a write hotspot on one of your disks and help SQL Server improve its I/O performance.  But keep in mind that proportional fill only affects all files in the same filegroup.  If we had added the second file to a new filegroup, then we would have to manually move tables to the new filegroup.
For more info on files and filegroups, check out BooksOnline.