VMware Cloud Community
knightmm
Contributor
Contributor

VIM_VCDB database 'PRIMARY' filegroup is full

Hi ,

Our VCDB hit the limitation of SQL Express. Data file size hit 4 GB. Does any one encounter this problem ? How to resolve this issue.

Thanks and regards

"Could not allocate space for object 'dbo.VPX_EVENT'.'VPXI_EVENT_CATEGORY' in database 'VIM_VCDB' 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."

Reply
0 Kudos
4 Replies
Dave_Mishchenko
Immortal
Immortal

Welcome to the VMware Community forums. THe 4 GB limit is a limit of SQL Server Express 2005. If you needed to grow your database beyond that size, you would want to upgrade to SQL Server 2005 Workgroup / Standard or Enterprise. If you want to continue using SQL Express, then you'll need to purge data from the database. Here's a thread that describes that - http://communities.vmware.com/thread/189882. However, if you install SQL Express management tools (you can get this from microsoft.com) then you should be able to run the script and shrink the DB without installing another SQL version. You will want to shutdown the VC service while you do this.

Reply
0 Kudos
knightmm
Contributor
Contributor

Hi Dave,

thanks a lot. I had cleared the records older than 160 days.

We also found out why our database grows so fast . Cause , we create a test alarm on VMs ( 20% of CPU usage) and we have not remove it for a period.. we know that this alarm create a lot of records at the DB. How can we clear only those records (i.e. created by that alarm) from the database and which tables?

Regards

Knightmm

Reply
0 Kudos
Dave_Mishchenko
Immortal
Immortal

I think you'll want to look at the event table. This schema might help you out - http://blog.mr-vm.com/2008/01/virtualcenter-database-schema.html.

Reply
0 Kudos
ftempel
Enthusiast
Enthusiast

I had the same issue today. The referenced articled help, but rely on having SQL Server full available, Luckily, SQL Server Express comes with a tool to fire off queries to the database. Albeit basic, it works:

If stuck in OSQL, the following works (SQL Server Express 2005) to clean up the database from old tasks and events. Based on KB 1025914 (vCenter 4 database).

(Assuming defaults, if you need to know the server name, osql.exe -L, if you need to know the database name, look at the database files).

c:> "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\OSQL.EXE" -S <server>\SQLEXP_VIM -E
1> use VIM_VCDB
2> go
1> update vpx_parameter set value='<value>' where name='event.maxAge'
2> update vpx_parameter set value='<value>' where name='task.maxAge'
3> update vpx_parameter set value='true' where name='event.maxAgeEnabled'
4> update vpx_parameter set value='true' where name='task.maxAgeEnabled'
5> go
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
1> exec cleanup_events_tasks_proc
2> go
1> dbcc shrinkdatabase ('VIM_VCDB')
2> go
DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
      5           1       81080         280       78776          78776
      5           2         128         128         128            128

(2 rows affected)
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
1> quit

It went from 4GB to 630MB using a 30 day retention time on tasks and events. Quite a difference Smiley Wink