Understanding datafile limits in MSDE

[bubarooni1]

Hi All,

I am currently running GP 8 with a MSDE database. I am set to upgrade to Great Plains 10.0/SQL 2005 Express in June.

However, I seem to be running up against my MSDE datafile size limit now.

When I run 'exec sp_spaceused' in Query Analyzer my results are:

database_name database_size unallocated space
------------- ------------- -----------------
myCompanyDB 1749.19 MB -371.01 MB

reserved data index_size unused
------------- ------------- ---------------- -------
2150600 KB 1001520 KB 1016800 KB 132280 KB

When I look in Enterprise Manager and check the properties of the db, on the Data Files tab 'File Properties' are set to 'Automatically grow file' by 20 percent AND 'Maximum file size' is unrestricted growth.

The other day when we were having a problem with the db being out of room and I ran a script to do an INDEXDEFRAG on the whole db. This changed the database size to about 2600 MB's (well over the limit of 2 GB's in MSDE) and showed I had 700 MB's of free space where before it had showed 0.

Am I really out of room for the db or can I milk another 2 months outta this? Is there a setting I can change in Enterprise manager that will free up some space or something I can run in Query Analyzer (DBCC SHRINKDATABASE doesn't seem to do anything).

I would really like to make this thing last until the upgrade in June, as I'm gonna get a new server to house it and a couple of other apps on.

Thanks in advance for any insight or ideas!

 

[mrdenny]

If you are still getting the negative number for the free space then you need to run sp_spaceused @updateusage='true'. This will tell SQL to go and figure out how much space you actually have free.

It sounds to me like you are pretty close to the 2 Gig limit. What's the output when you run the command I put above?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[bubarooni1]

database size - 1910.00 MB
unallocated space - 159.28 MB

I take it I may not make it to June....

Payroll and large check runs might fill that up?

 

[bubarooni1]

just outta curiousity, what did the negative number mean?

 

[mrdenny]

All the negative number means is that SQL has not been keeping good track of the space of all the objects. It happens some times.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog