Resource Database in SQL Server 2008

Reference: http://technet.microsoft.com/en-us/library/ms190940.aspx

The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sysschema of every database. The Resource database does not contain user data or user metadata.

The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server. Similarly, rolling back system object changes in a service pack only requires overwriting the current version of the Resource database with the older version.

Physical Properties of Resource:

The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are located in <drive>:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\MSSQL\Binn\. Each instance of SQL Server has one and only one associated mssqlsystemresource.mdf file, and instances do not share this file.

Backing Up and Restoring the Resource Database:

SQL Server cannot back up the Resource database. You can perform your own file-based or a disk-based backup by treating the mssqlsystemresource.mdf file as if it were a binary (.EXE) file, rather than a database file, but you cannot use SQL Server to restore your backups. Restoring a backup copy of mssqlsystemresource.mdf can only be done manually, and you must be careful not to overwrite the current Resource database with an out-of-date or potentially insecure version.

Note: After restoring a backup of mssqlsystemresource.mdf, you must reapply any subsequent updates.

Accessing the Resource Database:

The Resource database should only be modified by or at the direction of a Microsoft Customer Support Services (CSS) specialist. The ID of the Resource database is always 32767. Other important values associated with the Resource database are the version number and the last time that the database was updated.

To determine the version number of the Resource database, use:

SELECT SERVERPROPERTY('ResourceVersion');

GO

To determine when the Resource database was last updated, use:

SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');

GO

To access SQL definitions of system objects, use the OBJECT_DEFINITION function:

SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects'));

GO

SQLCMD Mode - Run your queries against several servers in a single query window.

To run your queries on different servers using the same query editor, you have to 

1.  First connect to a server
2. Open a new query
3. And now click on “Query” menu.
4. Choose “SQL CMD Mode”

Once you click on “SQL CMD Mode”, the query editor will go into command line mode.

Now type :Connect ServerName. This command will establish a connection to the server you have specified and then it will execute your query on that server.

Results:

Now if you go to “Messages” Tab, you will see the below result text (self explanatory).

MyWindowsClub.com - Another Good website from DotNetSpider.com

MyWindowsClub.com is a new website launched from the famous DotNetSpider.com.

This website provides a room for discussion on most of the major technologies. Even SQL server is also included for the discussion. This site has many other good features apart from technical discussions.

To know more visit http://www.mywindowsclub.com and to start discussion on SQL Server related topics visit http://www.mywindowsclub.com/forum/Category44.aspx.

To register to this site click here.

'Sev. 14 Errors' - Login failed for user 'NT AUTHORITY\SYSTEM'- Troubleshooting

Sometimes your SQL Error log might fill up with the error “Sev. 14 Errors – Login failed for user ‘NT AUTHORITY\SYSTEM’. [CLIENT : Local System ]”
This happens when user ‘NT AUTHORITY\SYSTEM’ local group does not have access to SQL Server and some service is trying to access SQL Server using this account.

To Trace out which services is causing this issue, first you will have to run the profiler. We do not require all the events, so we can apply the filter for profiler to capture only “Audit Login Failed” event class.

The results of profiler are below

With this data we can conclude that it is coming from SQLsafe application.

But from which process this is coming?

For this now we have to check the ClientProcessID column in the profiler results i.e. 5316 from the profiler results.

Now, when we check in the task manager of the server, PID 5316 is allocated for “SQLSafeBackupService.exe”

If PID is not visible in the processes Tab of TaskManager then go to “view” option and click on “Select Columns”. And choose PID (Process Identifier)

Now if we go to “services.msc” and check for the logon account of this service we see that this is running under “Local System” account.

Once the Logon account is changed to some service account which has access to SQL Server, then the error stops.

This article is also available in pdf format for downloading.

Please Click here to get your copy.

Row Not Found at the Subscriber - Replication Issue

When you find an issue in replication with the error “The row was not found at the Subscriber when applying the replicated command.”, first we have to get the Transaction sequence number and Command ID from the error.

This can be found at Distributer to Subscriber history in replication monitor.

Once we get the Transaction Sequence Number and Command ID we can easily drill down to the command which is causing the issue by using sp_browsereplcmds. Before to this, we have to also find out publisher_database_id.

For finding publisher_database_id, we need to make use of Transaction Sequence Number and Command ID.

Query to find publisher_database_id using Transaction Sequence Number and Command ID

select * from msrepl_commands where xact_seqno = 0x000BF8FB0003411E000400000000 and command_id=6

Once we get the publisher_database_id from the above query, then we need to execute the below query to get the command which is causing the error.

Query to find the command which is causing error

exec sp_browsereplcmds @xact_seqno_start = '0x000BF8FB0003411E000400000000', @xact_seqno_end = '0x000BF8FB0003411E000400000000', @Command_id=6, @publisher_database_id=60

Once we get the command, we can manually sync the missing data from publisher to subscriber to make the replication work fine as before.

Note: All these commands have to be run on distribution database.

This article is also available in pdf format for downloading.

Please Click here to get your copy.