Tag Archives: system databases

Database ‘mssqlsystemresource’ cannot be opened due to inaccessible files

Recently I was applying Service Pack on one of the instances on my Virtual Server. It is not uncommon for my laptop to run out of memory because it is *loaded* beyond its capacity. That day as well the installer froze on me and I had the Power Off the VM. When the Server was restarted, SQL Server greeted me with this error.

Error: 5173, Severity: 16, State: 1
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup
Log file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatamssqlsystemresource.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
Error: 945, Severity: 14, State: 2
Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details

I just broke the chain

The unfinished SQL Server Service Pack setup had corrupted the Resource database. It had not partially upgraded the instance but got hung while the Resource database was being upgraded. As the error message states, the version of the data file did not match that of the transaction log file for the resource database. This left the instance and the resource database running in different versions.

What next? I applied the Service Pack on another instance which completed successfully. Then I copied the mssqlsystemresource.mdf and mssqlsystemresource.ldf files from that instance and overwrote those files in the C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData folder of the instance having trouble starting. And SQL Server started without any issues.

This approach worked out well since only the resource database of the older version. Often DBA takes the backup of all databases before applying hotfixes/service packs but forgets about mssqlsystemresource database. Backing up this databases is nothing more than copying the data and transaction log file to a different location. Restoring the resource database is also overwriting the existing copy of the files with the *backed up* files.

SQL Server Service won’t start because I forgot to move the Resoruce database

On Sunday I got a call from a DBA from another team who had an issue that the SQL Server service was not starting up. It was a SQL Server 2005 instance. The SQL Server Error Log had the following error messages.

FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'E:mssqlsystemresource.mdf'. Diagnose and correct the operating system error, and retry the operation.
File activation failure. The physical file name "E:mssqlsystemresource.mdf" may be incorrect.
Error: 945, Severity: 14, State: 2.
Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

The SQL Server service was unable to find the files of mssqlsystemresource (a.k.a. resource) database. The SQL Server service was not starting up after the server was rebooted. Here is what happened a couple of days ago. The E drive hosting the the system databases had to be re-provisioned and the DBA had to move all the SQL Server databases to a new drive. The DBA moved all the system databases but forgot to move the mssqlsystemresource database. This came into light when the SQL Server service got restarted. But by that time the E drive was already out of the server.

I could not continue to support this case due to some other reason. Here is how I had planned to resolve this issue.

  • Copy the files of the mssqlresource database from another SQL Server 2005 instance which is of the same build to the new drive on this server.
  • Start SQL Server from the command prompt using sqlservr.exe. The -f (minimal configurtion) or -m (single-user mode) parameters have to be passed on to sqlservr.exe along with the Trace Flag 3608 (recovers only the master database).
  • Issue ALTER DATABASE command to move the files of mssqlresource database to point to the new location.

The actual command would be
C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnsqlservr -f -T3608

This command would start the SQL Server in minimal configuration mode and recover only the master database. Then executing the below command in sqlcmd would move the resource database to the new location.

ALTER DATABASE mssqlsystemresource
    MODIFY FILE (NAME=data, FILENAME= 'X:mssqlsystemresource.mdf')
GO
ALTER DATABASE mssqlsystemresource
    MODIFY FILE (NAME=LOG, FILENAME= 'X:mssqlsystemresource.ldf')
GO

The output of this command would be similar to the one below.

After making these changes, the SQL Server service would start gracefully.

Does tempdb runs out of space during DBCC CHECKDB?

Recently I read one of the forum posts where the user was running DBCC CHECKDB against one of the biggest databases on an instance. But CHECKDB will terminate abruptly reporting that the tempdb had run out of space. Starting SQL Server 2005, SQL Server makes use of Database Snapshots while running CHECKDB. The snapshot is created on the same drive where the data file is located. If there is no enough space for the snapshot to be created, CHECKDB will fail reporting the error. I had come across this issue and blogged about it here.

But in this case, CHECKDB was failing because of space constraints on tempdb. Why does tempdb come into picture while running CHECKDB? While running CHECKDB, SQL Server stores the information related to the Database in memory. If the database is huge or if there is not enough memory, SQL Server is forced to store this information in tempdb. Hence the tempdb grows and if there is not enough space for the tempdb to grow, CHECKDB will fail. This article on MSDN explains how to Optimize DBCC CHECKDB Performance.

DBCC CHECKDB has an option to check how much space is required by tempdb. This estimation can be obtained before running DBCC CHECKDB against a given database.

DBCC CHECKDB ('databasename') WITH ESTIMATEONLY

Here is the estimated space needed for tempdb to run CHECKDB against a 25 GB database.

This option helps to size the tempdb properly, before running DBCC CHECKDB on a big database.

Whenever I hear DBCC CHECKDB, the first name that comes to my mind is Paul Randal (blog | twitter). His posts in the CHECKDB From Every Angle series are the best source of information available on the internet regarding DBCC CHECKDB.

The error is printed in terse mode because there was error during formatting

On Wednesday, the monitoring tool sent an alert on an instance which was running low on memory. The alert read

Error: 14667, Severity: 16, State: 1. (Params:).
The error is printed in terse mode because there was error during formatting.
Tracing, ETW, notifications etc are skipped.

This instance is running SQL Server 2005 and I realized that we just bumped on a bug. As per this BUG listed for SQL Server 2005, this error message is generated because of an incorrect line the code of sp_readrequest stored procedure in MSDB database.

SET @localmessage = FORMATMESSAGE(14667, convert(NVARCHAR(50), @mailitem_id))

should be changed to

SET @localmessage = FORMATMESSAGE(14667,  @mailitem_id)

This error message does not mean anything critical, I started receiving few more extra alerts in my already flooded mailbox. Hence a Change Control has already been processed to modify this stored procedure as suggested.

I quickly looked up in SQL Server 2008 to see if this BUG has been fixed or not. Not yet. Even SQL Server Denlai still has the same line in sp_readrequest stored procedure. I am trying to inform about this BUG on MS Connect but due to some reasons I am unable to create a new feedback. Not a great BUG to report but fixing this would surely reduce some unwanted emails in the DBA’s inbox.

Update:

Aaron Bertrand (blog | twitter) has just posted a BUG on MS Connect regarding this. Please vote up for it here. Thanks Aaron for your help!

Pruning backup history taking too long to complete? Read on

Just a while ago, one of the colleagues from my previous employer posted a question on #sqlhelp to know how backup related records in MSDB database get purged. I gave him this screenshot of SQL Server Agent properties which controls the Job History in MSDB.

I was about to tweet about sp_delete_backuphistory stored procedure which can be used to prune the backup/restore history in MSDB. But someone else did it before I could. This stored procedure reminded me of one of the questions I had come across in one of the forums, where the user trying to drop the database using SSMS and SSMS would just hang. After a few responses he informed that he had selected “Delete backup and restore history information….” option while dropping the database.

There were no processes which were blocking this still the database would never get dropped. He had several thousand records of backup history for this database. The drop database session was progressing but it was really slow because it was waiting for the “Delete backup and restore….” transaction to complete. You may be surprised to know the reason for this. No indexes/foreign keys are there on the tables related to Backup/Restore history tables! sp_delete_backuphistory stored procedure deletes data from the following tables in MSDB.

  • backupfile
  • backupfilegroup
  • backupmediafamily
  • backupmediaset
  • backupset
  • restorefile
  • restorefilegroup
  • restorehistory

Run this query on MSDB and you would be surprised to know that there are only 4 indexes present for those 8 tables.

SELECT
    t.name as [TableName],
    ind.name as [IndexName],
    col.name as [ColumnName],
    ind.type_desc [IndexType]
from
    sys.indexes ind
inner join
    sys.index_columns ic on
      ind.object_id = ic. object_id and ind.index_id = ic. index_id
inner join
    sys.columns col on
      ic.object_id = col.object_id and ic.column_id = col.column_id
inner join
    sys.tables t on
      ind.object_id = t. object_id
where
     ind.is_unique = 0
    and ind. is_unique_constraint = 0
    and t.name in
    (
    'backupfile',
       'backupfilegroup',
       'backupmediafamily',
       'backupmediaset',
       'backupset',
       'restorefile',
       'restorefilegroup',
       'restorehistory'
    )
order by
    t .name, ind.name

Since there are no proper indexes on these tables, the stored procedure would obviously take a long time to complete. What is the fix? Create the indexes on the columns that are being referred by the sp_delete_backuphistory stored procedure. Geoff N. Hiten had written a good script which does this job. The script can be downloaded from here. The user created the indexes on MSDB using this script and he was in for a surprise. The backup job history pruning completed quickly and the database got dropped in a few seconds.

If you are facing similar issues, please go ahead and create those indexes on MSDB tables. Even if you are not affected by this yet, it doesn’t hurt to keep these indexes on. You never know when you need them 😉

tempdb, Please allow me to shrink you

Today bang at midnight I got a call that one of the drives on a Production Instance was running out of space. The reason for it was the tempdb size. The tempdb datafile was ~200 GB in size and used space was only 3 MB. Now the task at hand was to bring down the tempdb database size.

The easiest approach to bring down the tempdb database size is to restart the SQL Server instance, since tempdb gets re-created every time the instance is restarted. But it was Business hours for the customer and we decided that restarting the SQL Server instance would be the last option. So the DBA started trying to shrink the tempdb.

DBCC SHRINKFILE ('tempdev', 1024)

The query executed successfully but the size of the database did not change.

The next obvious step would be to check for any open transaction on tempdb.

SELECT * FROM sys.dm_exec_requests WHERE database_id = 2

No open transactions! Alright, any process holding locks on tempdb?

select * from sys.dm_tran_locks
where resource_database_id= 2

No locks! Then I stumbled upon this article on MSDN. There was a mention of sys.dm_db_session_space_usage DMV which helps to  track the number of page allocation and deallocation by each session on the instance. Hence tried my luck with this query.

select * from sys.dm_db_session_space_usage
where user_objects_alloc_page_count<> 0

The output was a pleasant surprise.

The last two session ids were of the DBA trying to shrink the database. The first one was of one of the application which was in sleeping status. Thinking that I found the culprit, I asked my team to reach out the Application team to check if this session can be killed. Since I was eager to catch some precious Friday night sleep, told my team “Guys, kill the SPID and then shrink it. Everything should be fine.” Without a second thought logged from the server and slept off peacefully.

Welcome Saturday. Since I did not get any calls overnight, I just peeped into see if the issue was resolved. No! “Pradeep, we deleted one old backup file on the drive and now the drive has some free space left. But the tempdb is not shrinking yet” was the response from my team when I called up. Wow! This issue was getting very interesting.

Any session would use tempdb for creating some temporary objects. Since the database was not shrinking, obviously some user defined tables would be there on it.

SELECT * FROM tempdb..sys.all_objects
where is_ms_shipped = 0

The is_ms_shipped column would be 1 for all the system objects. The output of this query looked like this.

As guessed, there were plenty of user tables on tempdb. But why are they here? Most likely some user executed a stored procedure which made the tempdb grow and eventually that session got terminated because tempdb ran out of space. Most importantly when a stored procedure is run cached objects are created on tempdb. These cached objects are in turn associated with a query plan. These objects are still present in tempdb because the query plan of the run away query is still present in the Procedure Cache. By design, these cached tables are not deleted, instead they are truncated so that these tables can be reused when the stored procedure is executed again. This article has more details on this topic.

The only way to get rid of these cached objects is to clear the Procedure Cache. It is not a good idea to clear the procedure cache, since it will force the stored procedures to be recompiled and thereby negatively affecting performance. But for me, tempdb size was more of a concern than the stored procedures being recompiled. I went ahead and cleared the Procedure Cache using the following command.

DBCC FREEPROCCACHE

I was jumping alone!

Bang. DBCC SHRINKFILE now did what it was supposed to do. tempdb was finally shrunk!

Differential backup of master database?

Someone posted a question yesterday. “Is it possible to take the differential backup of master database?”. The answer is No. Why not try taking the differential backup of master database instead. Open SSMS and the backup option looks like this.

Only FULL backup type is listed. So SQL Server does not allow any other type of backup operation to be performed on master database. Let us try out the script to take the differential backup of master database.

Here as well SQL Server states the same. You can only perform a full backup of the master database. It is confirmed that SQL Server is designed to allow only FULL backup of master database.

But why? For differential backups to be applied on a database, the database has to be in NORECOVERY (Recovering) state. But SQL Server does not allow master database to be restored without recovery. Master database has to be in Recovered status always.

Moral of the story is you can only take FULL backup of master database.