Oracle or SQL Server – How to Find the Instance Startup Time?

SQL Server

A DBA or developer might have a simple question – “Since when has my SQL Server instance been continuously running?”.

There are many places to look but the easiest is to look at the creation date of the system “tempdb” database.

SELECT create_date
FROM sys.databases
WHERE name = 'tempdb'

Now, to the question of why?
TempDB is special in a lot of respects. It gets recreated upon every restart so, the creation timestamp of that database is when the SQL Server instance was last started.

As you may know, among other things, anytime temporary tables are created, they get stored in the TempDB (and hence the commonly known “public toilet” analogy!).

On an unrelated note, unlike Oracle, where temporary space can be segmented between users if necessary, in SQL Server, there is no choice but for all users across databases in the instance to share the single TempDB. I hope that this changes in a newer version of SQL Server!

Oracle

Here is how one would find out the same information for Oracle:

SELECT startup_time
FROM v$instance
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s