SQL Resource

Comments: No Comments
Published on: March 22, 2011

By now, I am sure that you are well aware of the Resource Database.  Starting with SQL Server 2005, a new system database was made available – the Resource Database.  In SQL 2005 you could move the Resource Database – but in 2008 and beyond you cannot.  If you plan your install accordingly, your databases should install where you would like them (i.e. the program files directory that is the default installation directory can be changed during setup).

That is all well and good, but what is in this database and how can we take a look into it?  I thought about this as I was reading a blog by Thomas LaRock where he discusses system tables and system views.  I started thinking about the underpinnings of these views and if we could see them in the resource databse.  Taking a peek into the Resource Database is rather trivial.  I will show you how to copy that database and attach it as an alternate database.

The first step in taking a peek is to locate the directory where the data and log file for the hidden database are stored.  You can find the path by referring back to the SQL 2005 and 2008 links earlier in this post.  In my case, the path (I am using SQL 2008 SP2) for me is displayed in the next image.

Take the two highlighted files, copy them and place them into a new directory.  I copied and renamed the files – placing the files into my “Data” directory as follows.

As you can see, the database is rather small – and you could probably do this procedure multiple times ;).

Once you have the files copied to the new locations, you are now ready to attach the database.  I am just using the GUI to attach the database – for illustrative purposes.  While attaching, I think there is an interesting thing that should stand out.

Now look closely at the image just above – pay attention to the current file path.  This is the screen you will see after you browse to your new directory location and choose to add the mdf file for the Resource database.  You will see that this file path does not match either of the file paths that I have already shown.  Take a closer look:

You can see that the file path references a path for e:\sql10_katmai_t\sql…  I find that pretty interesting.  I don’t know the reason for that path persisting like that.  The file paths shown need to be changed to the appropriate file path where we placed the copied files – as follows.

There is one more thing that I think you should change.  This really is a matter of preference and is good housekeeping.  Change the name of the database in the Attach As field.  I appended _test to the end of the database name.  This should make it obvious that this database is NOT the system database.

With these few steps, you now have the resource database attached as a User Database.  With that, you now can take a look at the inner makings of some of the system views.  Take the following queries for example:

[codesyntax lang=”tsql”]

[/codesyntax]

Both of these queries return exactly the same thing.  Both of these queries are only provided as a means of backwards compatibility.  You can actually see master.sys.sysdatabases (just like mssqlsystemresource_test.sys.sysdatabases).  One thing you can do though is script out that view and see what the source of the data is.  Well…with the resource database attached as described, you can now script it out.  When you script out the view, make sure you try to script the correct version of it.  Just like any user database, you will get a category of system tables and one of system views.  The objects under those categories cannot be scripted.  The objects, however, are also represented in this database as “user” objects – which can be scripted.

After you script out the object, sys.sysdatabases you will see that the source of the data is the following:

[codesyntax lang=”tsql”]

[/codesyntax]

There you have it – this view is predicated on the newer view called sys.databases.  As has been said over and over again, it only exists as a backwards compatibility view until Microsoft decides to remove it.  Also note that the other part of the foundation for the sysdatabases view is the sys.master_files$ view.  You can now see this in the system views collection inside the Resource database that we attached.  Unfortunately you can’t script it, but at least you can see the columns that it would return.  You also can’t query directly to that view – you have to reach it through the system views.

SQL Server Standard Reports Winforms

In SQL Server, there is a good set of preloaded reports to help you monitor the Instance.  These reports can be found under the Standard Reports and can be reached via context menu.  To find them, right click your Instance in Object Explorer from within SSMS.  Then navigate the context menu – much like the menus shown here.

You can see from the image that there is a decent amount of good reports available to choose.

When you run one of these reports, it is like a report that you would run from SSRS.  However, SSRS is not required to be installed in order to run these reports.

Occasionally, you may encounter an error when trying to run these reports.  The error that you may encounter is as follows:

The file ‘Microsoft.ReportViewer.WinForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ cannot be opened.

This error message seems to pop up commonly after an upgrade to SQL Server is performed (CU or SP).  Sometimes, it can crop up without an upgrade having been recently performed.

In the event that you encounter this particular error, you should try installing BIDS.  Though it is not necessarily a pre-requisite – it is a good idea to have it installed anyway and it also resolves this error message.

Another method that has worked for me to resolve this error is to install Microsoft Report Viewer 2008 SP1.

Additionally, besides looking into these Standard reports and hopefully helping you circumvent the aforementioned error, there is another recommendation.  In SQL 2005, there is a download for the Performance Dashboard Reports.  And in SQL 2008 there is a new feature called Management Data Warehouse.

You can download the Performance Reports from here.

You can see how to configure the Management Data Warehouse here.

Oh, and if you want to retrofit SQL 2008 with the 2005 Performance Dashboard, you will have to tweak it a bit.  You can read up on that here.

page 1 of 1








Calendar
April 2017
M T W T F S S
« Mar    
 12
3456789
10111213141516
17181920212223
24252627282930
Content
SQLHelp

SQLHelp


Welcome , today is Friday, April 28, 2017