Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Nakul Vachhrajani is a Technical Specialist & Systems development professional with iGATE. He holds a MCTS (SQL Server 2008: Implementation & Maintenance)
Browse by Tags · View All
#SQLServer 305
SQL Server 304
Administration 252
DBA 241
T-SQL 234
#TSQL 232
Development 226
Tips 216
Guidance 148
Best Practices 119

Archive · View All
April 2011 14
March 2012 11
December 2011 11
March 2011 11
December 2012 10
October 2011 10
January 2011 10
September 2013 9
January 2013 9
November 2012 9

DBCC DBINFO WTIH TABLERESULTS - In which SQL Server version was this database created?

Oct 10 2011 12:00AM by Nakul Vachhrajani   

If you work for a product based organization, you would know that databases created in versions as old as SQL 7 are still around. The only thing is that over time, they have evolved via upgrade programs which have upgraded them to the latest and greatest levels – running on latest editions such as Microsoft SQL Server 2008 R2.

Whenever you work with a production database for troubleshooting or for an upgrade, have you ever wondered about the SQL Server version in which a particular database created? Answer to this question would answer questions such as:

  1. How old is this database?
  2. Has this database ever been reconstructed? (Reconstruction meaning data from an existing database pumped to a new database)

Getting answers to these questions is not that difficult, provided you can run a DBCC command against a database. All that needs to be done is:

DBCC DBINFO('AdventureWorks2008R2') WITH TABLERESULTS

That’s it! Well, let’s dissect the DBCC command that we used:

DBINFO: This, to the best of my knowledge, is an undocumented DBCC procedure. This means that it does not come with any sort of warranty from Microsoft (and also none from this site and me). It is therefore advised not to use this in production environments.

Next up, as the name suggests, DBINFO provides “insider” information about the database – something that is not available regularly via viewing the database options or via any DMV. The details of each and every result option is not in scope of this discussion. You may want to follow posts on SQLSkills [Paul Randall (blog)] for insights into DBCC from the guru himself.

TABLERESULTS: Most of us would have used DBCC CHECKDB to verify the integrity of our database. We get a whole lot of information as the output. However, not all DBCC commands return an output. The various output formats for DBCC that I know of are:

  1. Output to client (i.e. – display “on screen” as is the case with DBCC CHECKDB)
  2. Log to SQL Server Log
  3. Provide output via the use of TRACE flag – 3604 (When researching this post, I could not find documentation on MSDN for this flag. In such cases, please follow the assumption I take – it’s undocumented, and therefore not covered by warranty)
  4. In addition, we have a 4th output format – output as a table. The WITH TABLERESULTS clause specifies the results in a tabular format that can be loaded into a table.

By default DBCC DBINFO does not return any results. Therefore, we either need to use option #3 (TRACE Flags) or #4 to display the output of the DBINFO command. In the query I have mentioned above, we used the TABLERESULTS option.

The result

When I ran the above mentioned query against the AdventureWorks2008R2 sample database for Denali CTP03, the following was the output:

image

If we look at the fields - dbi_createVersion and dbi_crdate, we can see that they indicate the SQL Server version in which the database was created and creation date/time respectively.

For the AdventureWorks2008R2 for SQL 11 (“Denali”) CTP03, the values are:

  • dbi_createVersion = 679
  • dbi_crdate = 2010-08-02 17:26:22.770

So, now we know that this database was upgraded from another version of Microsoft SQL Server (dbi_createVersion is different when compared to dbi_version). Also, we know that the database was created first in August, 2010.

I trust that the above information will be helpful to you sometime in the future.

Until we meet next time,

Be courteous. Drive responsibly.

Tags: Administration, T-SQL, Tips, SQL Server, #SQLServer, DBA,


Nakul Vachhrajani
4 · 36% · 11624
2
 
0
Lifesaver
 
 
0
Learned
 
0
Incorrect



Submit

3  Comments  

  • And where is the info/table that will convert the DBI_Version number to the SQL Server Version designation?

    commented on Oct 20 2013 12:29PM
    BitBangerDBA
    2834 · 0% · 3
  • Hello, BitBangerDBA!

    The dbi_version list is not documented in the Books On Line. One can run this DBCC statement against the model database to know the version number for that version of SQL server.

    commented on Nov 11 2013 1:25PM
    Nakul Vachhrajani
    4 · 36% · 11624
  • That was not my question. I want to see a listing of all dbi_versions VS all SQL Versions. Thanks and regards,

    commented on Nov 11 2013 3:12PM
    BitBangerDBA
    2834 · 0% · 3

Your Comment


Sign Up or Login to post a comment.

"DBCC DBINFO WTIH TABLERESULTS - In which SQL Server version was this database created?" rated 5 out of 5 by 2 readers
DBCC DBINFO WTIH TABLERESULTS - In which SQL Server version was this database created? , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]