Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am interested in determining the ages of all 70+ my servers. I would like to create a growth graph showing when servers and databases were added to the environment either/both monthly or annually. I executed the following query using the SSMS multi-query feature with only partial success.

SELECT [name], create_date FROM sys.databases

It's partial success because the master and model databases return with a date of 'April 8, 2003' when I know that I created some of these servers during the past couple years.

I refresh databases from one server to another several times a week, which appears to be causing issues with the database create date.

Is there a simple method to determine the actual create date for both servers and databases?

share|improve this question

2 Answers 2

determine the actual create date for SQL Server

This can be found using

SELECT  createdate as SQL_SERVER_INSTALLATION_DATE
FROM    sys.syslogins 
where   sid = 0x010100000000000512000000 

Note : sys.syslogins is deprecated, so using sys.server_principals will give you the same results.

So you can use :

--Using  sys.server_principals
SELECT SERVERPROPERTY ('MachineName') AS PhysicalMachineName,
   SERVERPROPERTY ('ServerName') AS SQLServerName,
   SERVERPROPERTY ('Edition') AS ServerEdition,
   CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) +  ' + ' 
       + CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build'
       + CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion,
    RIGHT(@@version, (Len(@@Version)-charindex('Windows',@@version, 1))+1) AS [O.S.],
 SERVERPROPERTY ('Collation') AS Collation
      ,create_date as 'SQL Server Installation Date'
FROM sys.server_principals 
WHERE name='NT AUTHORITY\SYSTEM'

--Using sys.server_principals 
SELECT create_date as 'SQL Server Installation Date'
FROM sys.server_principals 
WHERE name='NT AUTHORITY\SYSTEM'

Actual creation of database

This can be found by reading the boot page of the database.

Note: DBCC PAGE is undocumented. I have not seen any side effects of using it, but since it is undocumented, use it with caution.

DBCC TRACEON(3604)
GO
 -- Actual creation time of database is stored in the boot page of the database
 -- This is even retained after restore or detach/attach of the database. 
 --- Look for dbi_crdate.
DBCC PAGE('database_name_Goes_here', 1, 9, 3)
GO

DBCC TRACEOFF(3604)
GO

Even better using DBCC PAGE WITH TABLERESULTS

CREATE TABLE PageData
(
      ParentObject VARCHAR(1000)NULL,
      Object VARCHAR(4000)NULL,
      Field VARCHAR(1000)NULL,
      ObjectValue VARCHAR(MAX)NULL,
)
GO   

INSERT INTO PageData (ParentObject, Object, Field, ObjectValue)
EXEC ('DBCC PAGE (''AdventureWorks2008R2_copy_August_2014'',1, 9, 3) WITH TABLERESULTS')
GO


select DatabaseName = (select ObjectValue from PageData where field ='dbi_dbname')
, DatabaseCreateDate = ( select ObjectValue from PageData where field = 'dbi_crdate')
share|improve this answer
    
Just what I was looking for. Perfect! –  Dave Aug 12 at 21:34
    
Glad that it helped you. If you feel that your question is answered, please upvote and/or accept it as answer. –  Kin Aug 12 at 21:36
    
Having issue with the database create date using SSMS multi query. Is there a way to return a create date list for each database on all the servers? –  Dave Aug 12 at 21:51
    
@Kin you should also mention in your answer that DBCC PAGE command is undocumented. syslogins is outdated now and is replaced by sys.sql_logins or sys.server_principal. –  Shanky Aug 13 at 11:51
    
@Shanky Good point and I forgot to mention it. I have updated my answer. THanks ! –  Kin Aug 13 at 14:32

For instance installation date:

SELECT instance_installed = create_date
  FROM sys.server_principals
  WHERE name = N'NT AUTHORITY\SYSTEM';

For the database creation date, why on earth do you care about the create_date for system databases like msdb, model, etc.? And tempdb gets re-created every time you restart the service, so unless you're also looking for that event, what good does that do? You already know when the server was installed from the above query, so really you should only care about user databases. Just modify your query to:

SELECT [database] = name, create_date 
  FROM sys.databases
  WHERE database_id > 4;

This will still pull in quasi-system databases like those for replication, SSISDB, MDW, ASPState, etc. But will eliminate the four primary system databases.

share|improve this answer
    
I do not care about the create date for system databases, just noting that they were incorrect. Thanks for the database create date script. Perfect! –  Dave Aug 13 at 14:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.