Wednesday 25 February 2015

1.22 - Identifying A Database From The Data File

A few times in the past I have had to deal with trying to identify a detached data file and try to work back as to what database the files belonged to.
After a lot of searching, I have long last found the solution and I hope it comes in helpful to those in a similar position to myself.

Enjoy!

Syntax:DBCC CHECKPRIMARYFILE ({'PhysicalFileName'} [,opt={0|1|2|3}])

PhysicalFileName is the full path for the primary database file.

opt=0 - checks if the file a primary database file.
opt=1 - returns name, size, maxsize, status and path of all files associated to the database.
opt=2 - returns the database name, version and collation.
opt=3 - returns name, status and path of all files associated with the database.

REM: You need the security on the folder/file to read the info!

Wednesday 4 February 2015

1.21 - SQL Profiler Trace Duration Units

Always have to look this up so it must be useful:

  • Beginning with SQL Server 2005, the server reports the duration of an event in microseconds (one millionth, or 10-6, of a second) 
  • Beginning with SQL Server 2005, the server reports the duration of CPU time used by the event in milliseconds (one thousandth, or 10-3, of a second)
  •  In SQL Server 2000, the server reported both duration and CPU time in milliseconds. In SQL Server 2005 and later
  • In SQL Server 2005 and later, the SQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds.