What is Source_Database_ID in Sys.Databases?- Interview Question of the Week #232

4 years ago

Question: What is Source_Database_ID in Sys.Databases and why it is mostly NULL?

Answer: Indeed a very interesting question asked to me by attendees of SQL Server Performance Tuning Practical Workshop – Recorded Classes.

Let us understand the question in detail. Whenever the user was running the following query, she was always noticing the column Source_Database_ID with only NULL values. The name of the column is source database id but she was not able to understand how the newly created database by the user can have a source and hence the question was sent to me.

Let us see the query first.

SELECT name, source_database_id,
DB_NAME(source_database_id) SourceDatabase
FROM sys.databases
GO

In the above query, I have removed unnecessary columns and kept only relevant columns. Now if you notice that the column is source database id only contains NULL.

The column source database id will only have values when the database created is snapshot based on another database. In simple words, if I create a new database SQLAuthority_Snapshot based as a Snapshot of the SQLAuthority database, the column source database id of the snapshot database will contain the id of the source database.

Let us run the following query to understand our example.

CREATE DATABASE SQLAuthority
GO
CREATE DATABASE SQLAuthority_Snapshot ON
( NAME = SQLAuthority, FILENAME =
'D:\Data\WideWorldImporters_Snapshot.ss' )
AS SNAPSHOT OF SQLAuthority;
GO

Now once you run the above statement, it will create two databases 1) SQLAuthority and 2) SQLAuthority_Snashot which is based on the SQLAuthority database. Now run our earlier script once again.

SELECT name, source_database_id, 
DB_NAME(source_database_id) SourceDatabase
FROM sys.databases
GO

In the resultset, you can clearly see that we have a new database which is snapshot showing the course database id of its source database. I hope you find this blog post interesting. Let me know if you have other similar questions, I would have happy to answer them on this blog.

If you are interested, you can sign up for my SQL Server Performance Tuning Practical Workshop. On the popular demand of everyone, I am going to make this class available for everyone for Instant Learning. Here is the video recording of the class, at your own comfort you can now watch it from office, home or on mobile during your daily commute.

Reference: Pinal Dave (https://blog.sqlauthority.com)