Delving a little into SQL Storage engine and Service Broker today. I had faced this problem sometime back and it presented me with an opportunity to see how the SSB queues/tables are represented internally by SQL Server.

---

Scenario
My customer wanted to remove the secondary File Group “FG2_fg" from a database. But was unable to remove the filegroup since these service broker tables were present in filegroup FG2_fg.

queue_messages_223180947
queue_messages_225181061
queue_messages_327181175

All of the above objects are internal system tables which are placeholders for the default queues that are present for every database starting with SQL Server 2005.

QueryNotificationErrorsQueue
EventNotificationErrorsQueue
ServiceBrokerQueue

Whenever we tried to remove the file/filegroup we got this error,

Alter database test3 remove file fil2

Msg 5031, Level 16, State 1, Line 1

Cannot remove the file ‘fil2’ because it is the only file in the DEFAULT filegroup.

We used the following queries to identify the objects present in the secondary filegroup:-

1) Find out the objects present in the secondary filegroup

select name, type, type_desc, is_ms_shipped from sys.objects where object_id in (select object_id from sys.indexes where data_space_id in (2))

— Here data_spaceid=2 represents the filegroup ID 2

2)

SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]

FROM sys.indexes i

INNER JOIN sys.filegroups f

ON i.data_space_id = f.data_space_id

INNER JOIN sys.all_objects o

ON i.[object_id] = o.[object_id]

WHERE i.data_space_id = 2 — Filegroup ID

Background Info
1. SQL Server uses a proportional fill strategy as it writes to each file within the filegroup and NOT across filegroups. Read more about this in here.
2. The only exception to this could have been IF someone had changed the default filgroup to the secondary filegroup. If so, then any new objects created afterwards would have gone to the secondary instead of the primary.

Repro on SQL 2005
As any good engineer does, so did I – TESTING. To test my little theory I did the following repro on SQL 2005 :-

1) Created a new database with secondary filegroup and 1 file in that.
2) Made secondary FG as default FG for database "test".

*Note: This needs to be done as part of create database and not added later on.

3) What I found was that you DON’T NEED service broker to be enabled for the queue_messages tables to get created.
4) I found that on my test database I had 3 Internal queue_message tables which where for the following parent object

QueryNotificationErrorsQueue
EventNotificationErrorsQueue
ServiceBrokerQueue

4) All of the 3 were were of type SERVICE_QUEUE. You can find this out from sys.objects view
5) Even a new database has the same object numbers and looking at my customer’s object numbers, they are higher, which means they must have had Service Broker implemented at some point.

queue_messages_254180947
queue_messages_286181061
queue_messages_318181175

 

6) I tested this by creating a service queue when my secondary filegroup was the default filegroup.

use test

GO

CREATE QUEUE TestQueue

WITH

STATUS = OFF,

RETENTION = ON

 

7) This created the table queue_messages_2105058535 on FG 2 of type "QUEUE_MESSAGES" indicating its a SSB queue.

 

8)  To remove the internal table I did a DROP QUEUE.

drop queue TestQueue

 

9) This removed the associated internal table and the indexes. You can use the following query to identify internal tables which are for service broker queues.

select a.name as [ChildName], a.object_id as [ChildObjectID], a.type_desc as [ChildType], a.internal_type_desc as [ChildTypeDesc],

a.parent_id as [Parent_ObjectID], b.name as [Parent Name], b.type_desc as [Parent_Type]

from sys.internal_tables a

inner join sys.objects b

on a.parent_id = b.object_id

 

So the issue still does NOT reproduce on SQL 2005 (same for SQL 2008). I then did the following repro on a SQL Server 2000 instance.

Repro on SQL 2000
1. Created a database on SQL 2000
2. Added File-group (FG2) and made it as default.
3. Took a backup of the database in SQL 2000.
4. Restored this to SQL 2005/2008.
5. System Services/Queues got created on FG2.
*Note: We cannot modify them because they are system objects.

6) The remove File command gives us this error,

Alter database test remove file fil2

Msg 5031, Level 16, State 1, Line 1

Cannot remove the file ‘fil2’ because it is the only file in the DEFAULT filegroup.

 

7)  Next up, I ran these commands.

 

dbcc shrinkfile(3, EMPTYFILE)

go

alter database test remove file fil2

 

This worked so far and in sys.database_files I see the status of file2 as OFFLINE. But unfortunately I still cannot remove the filegroup. It still says that it is not empty. Even though the emptyfile commands worked, the objects (SSB queues) still exist on FG2.

I followed same steps for a database created on SQL 2008 and took a backup and restored it again on SQL 2008 and issue did not reproduce. i.e. objects were created on Primary and none of them went to the Secondary FG. I did the same test on SQL 2005 Database backed up and restored to SQL 2008. This also did NOT reproduce the issue.

So, what did I learn out of all my testing (and precious time). Read on…

My Theory
This database had to have been restored from SQL 2000 to a higher version or been upgraded in-place from SQL 2000 to higher version? Tracking down the source of the original database would help us figure this out.

If yes, we now know the answer and that this behaviour is ByDesign and understood, i.e. how the system tables went to the secondary filegroup.  This is because the non-primary filegroup FG2_fg was set as DEFAULT prior to backup from SQL 2000 or in-place upgrade. Once the upgrade was done, since SSB was newly introduced starting with SQL 2005, during the upgrade the upgrade scripts created these objects on the secondary filegroup since it was set as the default filegroup.

Verifying My Theory
To confirm if an in-place upgrade was done, we can look in sys.database_files DMV and look at the column file_GUID. The file_guid column will be NULL if the database was upgraded from an earlier version of Microsoft SQL Server. (Upgraded only, not for a Restore).

I have to thank the SQL Storage Engine folks for thinking about this kind of a scenario and capturing such detailed info in the catalog views. Thanks Folks!

I went back to my customer’s database and looked at the sys.databases output and I noticed this,

file_id	file_guid	type	type_desc	data_space_id	name	physical_name
1	08C43589-1462-4492-8778-D4BCA128ED66	0	ROWS	1	test_Data	C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test_Data.mdf
2	A7ADDAF4-0425-4BB8-988C-FE260A41331C	1	LOG	0	test_Log	C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test_Log.ldf
5	3542AAD9-4AC4-499A-90B8-D342D0CBFFE6	0	ROWS	1	test_Data2	C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test_Data2.ndf
6	NULL	0	ROWS	2	fil2	C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fil2.ndf
7	12D108E4-DEEE-4302-A61A-AD6FE21B5EF3	0	ROWS	1	test_Data3	C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test_Data3.ndf

 

Notice that for data_space_id=2 which is for the secondary filegroup FG2_fg, the file_guid column has a value of NULL !!!
EUREKA!!!

This confirms that this instance/database was indeed upgraded from an earlier version of SQL 2000/7.0 to SQL (2005/2008). This behaviour is by design and we now understand how the system SSB objects were created on secondary filegroup.

Conclusion

Q: Why cannot we remove the file and filegroup?
Ans: Because of the above mentioned queue_messages objects which are present in the secondary filegroup. If there were user-created service broker queues/services then they can be Dropped or Altered to move them to the primary filegroup. Since these came as part of the database during DB Creation, they cannot be modified/dropped/moved. Hence, we cannot remove the filegroup.

Q: How did these "system" objects get to the secondary file group?
Ans: These system objects became part of the database starting with SQL Server 2005 onwards. So, If you had a database in SQL 2000 and you upgraded that to SQL 2005/2008 these system objects will get created. But, in SQL 2000 if the secondary file-group FG2_fg was set as the DEFAULT file-group, then any new objects created without an explicit MOVE TO will go the default filegroup. So when you upgrade the database to SQL 2008 these automatically got created on the secondary file-group.

Q: What data do I have to prove that the theory above is true in your case?
Ans: We store the information about each database file in sys.database_files DMV. There is a column called file_guid which when NULL indicates that the database/file was upgraded from an earlier version on SQL Server. So when I looked at sys.database_files in the database test, I saw the value for the secondary file as NULL.

Reference – http://technet.microsoft.com/en-us/library/ms174397(SQL.100).aspx

 

I spent some serious time to arrive at this conclusion and I am sharing this with the SQL community, so that it saves you some time. As always, stay tuned to theSQLDude for more…