#0325 – SQL Server – fn_trace_gettable – Get database name change history from default trace


A few weeks ago, I received a very interesting question from one of the readers. The question was:



“Given that no explicit auditing/logging exists on a particular database, can we get a history of all the various names that a database was assigned?”


This is a classic case where the answer is: “It depends!


NOTE: The explanation below requires an understanding of SQL Server’s default tracing mechanism.


Depending upon the needs of the business, it is quite possible that there exist multiple copies of the same database on the same or multiple servers (in a development or QA environment, for example). Hence, it may become very difficult to maintain the database name history within the database (because there would be many questions like – should the history be truncated upon restore; etc). Besides, the database name itself does not have any impact on the operation of the database then why should we maintain a history of the various names that the database has assumed over time?


Therefore, there is no durable log of the database rename activity stored in the database or any of the system databases.


By default, Microsoft SQL Server maintains a default profiler trace that can help administrators get a log of activity primarily related to the configuration options that happened within SQL Server. However, the default trace is limited to a set of files and when the SQL Server fills up all of these files, it rolls over and deletes the first of the files in the set to maintain the same number of trace files by continuing the trace in a new file.


Because a trace is constantly running against the server, the trace would have captured the renaming of the database. Hence, if we query the default trace file, we can get information about database renames, provided that the trace file has not yet been rolled over by SQL Server. Allow me to demonstrate with an example:


The following query creates a database and renames it thrice. It then queries the default trace to get a log of all rename activity.

–Safety check
IF EXISTS(SELECT * FROM sys.databases AS sd WHERE sd.name = ‘BRNAV0325v3’)
DROP DATABASE BRNAV0325v3;
GO

–Now create the test database
CREATE DATABASE BRNAV0325;
GO

–Rename the database (1st time)
ALTER DATABASE BRNAV0325
MODIFY NAME=BRNAV0325v1;
GO

–Rename the database (2nd time)
ALTER DATABASE BRNAV0325v1
MODIFY NAME=BRNAV0325v2;
GO

–Rename the database (3rd time)
ALTER DATABASE BRNAV0325v2
MODIFY NAME=BRNAV0325v3;
GO

–If the default trace is enabled, get the path to the log file
–SELECT * from sys.traces where is_default = 1

–Query the default trace to get a record of database renames
SELECT ft.DatabaseID ,
ft.DatabaseName ,
MIN(ft.StartTime) AS ActivityDate ,
ft.LoginName,
DB_NAME(ft.DatabaseID) AS CurrentDatabaseName
FROM ::fn_trace_gettable( ‘C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2012MSSQLLoglog.trc’ , DEFAULT) AS ft
WHERE ft.EventClass IN( 46 , –CREATE
47 , –DROP
164) –ALTER
AND ft.EventSubclass = 0 –We are not interested in granularity
AND ft.DatabaseID <> 2 –tempdb activity can be ignored
AND ft.IndexID IS NULL –We do not need any table/column activity
–(including statistics)
AND ft.DatabaseName LIKE ‘BRNAV0325%’
GROUP BY ft.DatabaseID,
ft.DatabaseName,
ft.LoginName;
GO

–Cleanup
IF EXISTS(SELECT * FROM sys.databases AS sd WHERE sd.name = ‘BRNAV0325v3’)
DROP DATABASE BRNAV0325v3;
GO


The output looks similar to the following:


image



As you can see, subject to the availability of the necessary data in the default trace, we can use the function – fn_trace_gettable() – to get a record of the database rename operations.


Further Reading



  • sys.fn_trace_gettable [Books On Line Link]
  • Default Trace Server configuration option [Books On Line Link]

Until we meet next time,


Be courteous. Drive responsibly.

Advertisements

Let me know what you think about this post by leaving your feedback here!

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