How to Identify the Location of Resource Database in SQL Server
Nov 16, 2013
Introduction
In this article we will take a look at how to identify the location of Resource database in SQL Server using a TSQL script.
Importance of Resource Database in SQL Server
Microsoft initially introduced Resource database in SQL Server 2005. Resource database is a read-only system database which is hidden from users. System objects such as sys.objects are physically stored in Resource Database which logically appears in the sys schema of each database. However, resource database will only store system objects and you cannot store user data or metadata.
Resource database consists of two files namely mssqlsystemresource.mdf and mssqlsystemresource.ldf. By default these files are located in the <drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder. Most importantly the ID of resource database is always 32767. The ID value of resource database has remained same across all versions of SQL Server 2005 and later.
Identity the Location of Resource Database in SQL Server Using TSQL Script
Using the below TSQL Script one can easily identify the physical location of Resource Database in SQL Server. This script is tested on SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012.
Use master
GO
SELECT 'ResourceDB' AS 'Database Name'
, NAME AS [Database File]
, FILENAME AS [Database File Location]
FROM sys.sysaltfiles
WHERE DBID = 32767
GO

Current Version of Resource Database on my instance of SQL Server
Using the below script you can identify the current version of SQL Server. This version number is same as the build number of SQL Server. To know more about different SQL Server Build Numbers read the following article titled “SQL Server 2005, 2008, 2008 R2, 2012 Build List”.
/* Version Number of Resource Database */
SELECT SERVERPROPERTY ('ResourceVersion') AS 'Resource DB
Version Number';
GO

The build number 10.50.1600 means you are running the Release to Manufacturing (RTM) version of SQL Server 2008 R2. For more information on various version numbers of SQL Server 2008 R2 read the following article SQL Server 2008 R2 Build List.
Last Time when Resource Database was updated
Using the below script you can get to know when last time resource database was updated.
/* When Last Time Resource Database was last
updated */
SELECT SERVERPROPERTY ('ResourceLastUpdateDateTime') AS
'Resource Database Last Updated on';
GO

IDENTIFY CPU BOTTLENECKS IN SQL SERVER
PERFORMANCE DASHBOARD REPORTS IN SQL SERVER 2012
IMPROVE SQL SERVER REPLICATION PERFORMANCE
Sign up today for MyTechMantra.com Newsletter Like Us on FACEBOOK | Follow Us ON TWITTER
Conclusion
In this article you have seen how easily you can identify the location of Resource Database using TSQL code.
|
Geeks who read this article also read…
- Performance Dashboard Reports in SQL Server 2012
- How to Backup an Analysis Services Database Using SQL Server Management Studio
- How to Restore an Analysis Services Database Using SQL Server Management Studio
- Automate Backup of Analysis Services Database Using SQL Server Agent Job
- How to Attach Analysis Services Database in SQL Server
- How to Detach Analysis Services Database in SQL Server
- How to Backup Database in SQL Server
- How to Restore Database in SQL Server
- How to Attach Database Without a Transaction Log File in SQL Server
- New Features in SQL Server 2012 Reporting Services for Developers
- New Features in SQL Server 2012 for Database Administrators
- New Features in SQL Server 2012 for Database Developers
- Read More SQL Server Articles…