February 20, 2009 at 6:05 pm
I have a relatively simple database set up in SQL 2005 that has 3 tables.
The grand total of the data and indexes in all three tables is approximately 750K. The data itself is about 350K if I pull it all out as text, and I understand there is some overhead, and the few indexes will take some space.
But the database is growing and growing and growing and growing. The number of rows in the tables is relatively static, but they are updated regularly. The MDF file continues to expand. It's at 175 megs now, growing 30-40 megs per hour.
The database is set to simple recovery, so it's not the transaction log.
I tried turning on autoshrink, but the space doesn't seem to be released.
There is no off-row data stored as BLOBS.
The small table does have an XML field, but I exported that table, XML and everything, and it's 36K. Total. Not 175 megs.
It does use service broker, and I understand that about well enough to admit I don't know anything about it. Does service broker use space to hold queued messages? If so, how can I convince it to release that space?
Any other ideas?
February 20, 2009 at 6:13 pm
Would you mind sharing the output of sp_spaceused and sp_helpfile while connected to the database in question?
I believe the facts the you laid out in your post. I just need a baseline of information to begin an analysis process.
Ken
February 20, 2009 at 6:25 pm
It's already gone to 285 megs, so I turned off unlimited autogrowth to see what error messages popped up in the logs. Pages and pages of this now.
2009-02-20 19:22:59.53 spid69s Could not allocate space for object 'dbo.queue_messages_37575172'.'queue_clustered_index' in database 'RELATIVITY_INTERNAL' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
2009-02-20 19:23:04.53 spid57s The activated proc [dbo].[OBJECTAUDITPROC] running on queue RELATIVITY_INTERNAL.dbo.ObjectCreationQueue output the following: 'Could not allocate space for object 'dbo.queue_messages_37575172'.'queue_clustered_index' in database 'RELATIVITY_INTERNAL' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.'
2009-02-20 19:23:04.53 spid57s Error: 1105, Severity: 17, State: 2.
2009-02-20 19:23:04.53 spid57s Could not allocate space for object 'dbo.queue_messages_37575172'.'queue_clustered_index' in database 'RELATIVITY_INTERNAL' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
2009-02-20 19:23:09.53 spid57s The activated proc [dbo].[OBJECTAUDITPROC] running on queue RELATIVITY_INTERNAL.dbo.ObjectCreationQueue output the following: 'Could not allocate space for object 'dbo.queue_messages_37575172'.'queue_clustered_index' in database 'RELATIVITY_INTERNAL' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.'
2009-02-20 19:23:09.53 spid57s Error: 1105, Severity: 17, State: 2.
2009-02-20 19:23:09.53 spid57s Could not allocate space for object 'dbo.queue_messages_37575172'.'queue_clustered_index' in database 'RELATIVITY_INTERNAL' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
2009-02-20 19:23:14.54 spid57s The activated proc [dbo].[OBJECTAUDITPROC] running on queue RELATIVITY_INTERNAL.dbo.ObjectCreationQueue output the following: 'Could not allocate space for object 'dbo.queue_messages_37575172'.'queue_clustered_index' in database 'RELATIVITY_INTERNAL' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.'
2009-02-20 19:23:14.54 spid57s Error: 1105, Severity: 17, State: 2.
2009-02-20 19:23:14.54 spid57s Could not allocate space for object 'dbo.queue_messages_37575172'.'queue_clustered_index' in database 'RELATIVITY_INTERNAL' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
2009-02-20 19:23:19.53 spid57s The activated proc [dbo].[OBJECTAUDITPROC] running on queue RELATIVITY_INTERNAL.dbo.ObjectCreationQueue output the following: 'Could not allocate space for object 'dbo.queue_messages_37575172'.'queue_clustered_index' in database 'RELATIVITY_INTERNAL' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.'
2009-02-20 19:23:19.53 spid57s Error: 1105, Severity: 17, State: 2.
2009-02-20 19:23:19.53 spid57s Could not allocate space for object 'dbo.queue_messages_37575172'.'queue_clustered_index' in database 'RELATIVITY_INTERNAL' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
2009-02-20 19:23:24.54 spid57s The activated proc [dbo].[OBJECTAUDITPROC] running on queue RELATIVITY_INTERNAL.dbo.ObjectCreationQueue output the following: 'Could not allocate space for object 'dbo.queue_messages_37575172'.'queue_clustered_index' in database 'RELATIVITY_INTERNAL' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.'
2009-02-20 19:23:24.54 spid57s Error: 1105, Severity: 17, State: 2.
2009-02-20 19:23:24.54 spid57s Could not allocate space for object 'dbo.queue_messages_37575172'.'queue_clustered_index' in database 'RELATIVITY_INTERNAL' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
February 20, 2009 at 6:30 pm
Ken Garrett (2/20/2009)
Would you mind sharing the output of sp_spaceused and sp_helpfile while connected to the database in question?I believe the facts the you laid out in your post. I just need a baseline of information to begin an analysis process.
Ken
sp_spaceused
database_name database_size unallocated space
---------------------------- ------------------ ------------------
RELATIVITY_INTERNAL 285.69 MB 0.18 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
290568 KB 273960 KB 15936 KB 672 KB
sp_helpfile
name fileid filename
------------------------- ------ --------------------------------------------------------
RELATIVITY_INTERNAL 1 F:\SQL DATA\RELATIVITY_INTERNAL\RELATIVITY_INTERNAL.mdf
RELATIVITY_INTERNAL_log 2 F:\SQL DATA\RELATIVITY_INTERNAL\RELATIVITY_INTERNAL_log.LDF
February 20, 2009 at 6:43 pm
Hi Wayne,
Is the table [dbo.queue_messages_37575172] a persistent table or something created dynamically by the application?
Is it possible the UPDATE transactions are modifying values of columns defined in the clustered index of [dbo.queue_messages_37575172] and causing page splitting?
Ken
February 20, 2009 at 7:04 pm
I will check into the possibility of posting the exact script for creating all this, but I would need to clear it with the original author.
To summarize what it is doing, there are two tables involved in this piece. One is a reference table of SQL Object Types and the associated names...
(NOT ACTUAL NUMBERS HERE, ONLY FOR ILLUSTRATION PURPOSES)
1 - table
2 - index
3 - trigger
4 - SQL Login
The second table contains a history of these types of items being added/modified/deleted on the server
Service broker is used to trigger on server-wide events Object_Created, Object_Deleted, Object_Altered
When that message fires, it sticks an entry in the history log
An SQLAgent job is created to run once an hour, look for items that have not been emailed, and email them to me.
This way I can keep track of what the programmers are adding in the database, because it's kind of important for a DBA to keep track of that, even if I am the "newbie self trained trial by fire" kind of DBA.
[dbo.queue_messages_37575172] is apparently the server created message queue.
At this point, it's 3 hours past quitting time, so I'm going to disable it, because now instead of the database racing to fill the hard drive, the errorlog is growing like crazy.
Edit: OK, I found where the queue is being created with retention turned on. This means when the messages are not deleted, but just marked as read, until the conversation ends.
Apparently the conversation is not ending, because there are 135 thousand messages sitting in the queue. I'll redo this with retention turned off and see how that turns out.
February 20, 2009 at 7:21 pm
Wayne,
Have a nice night and hopefully a good weekend.
I think that your tracing application may be a bit too sensitive or your event storage db is too small. The way to be certain would be to monitor the space growth of individual tables over a period of time.
The object [dbo.queue_messages_37575172] seems to be a table and I suspect it is created dynamically by the service broker application. If I were you I would try to capture information using a Profiler trace, and also by checking the space consumed by the dynamic table (sp_spaceused 'table_name') while the application is running.
I am no expert with Service Broker, but it seems like the DMV sys.dm_broker_forwarded_messages may be useful to you as well.
I hope that helps a bit.
Ken
February 20, 2009 at 7:35 pm
Thanks for the help in getting me thinking Ken. I turned off retention, did some testing, and the queue dropping the messages after they are processed, which will prevent it from overtaking the server.
May 6, 2009 at 6:16 am
i dont have an answer to you Question But i have a question for you
What are you using to make this relativity connection to.
I have a app that i use relativity (rcg) to connect to and i have no Problem connecting in sql2000 but in 2005 I can get the connection I try odbc and it test ok but to when I try to run it it I get it not supported.
Can you help me with this ?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply