An old XP application requires MSDE 2000 which has a maximum 2gb size for the database. Every six months or so the database becomes full and I have to copy and delete the oldest data. However, since the database size is FIXED at 2 GB, there is no status I can find that tells me it is full. The "properties" always says size = 2gb, free = 0. (2247.63 mb, 0 avail, which includes the 200 mb log file.) How can I tell how much free space is still available inside the database?
(I have found the table space used by each of the tables but I can't figure out how the numbers I get add up to the database size. It is as if the tables get compressed or something.)
In SQL 2005 or later you can query sys.database_files. For SQL 2000, I think the equivalent system table is dbo.sysfiles in master database but I don't know if the columns are the same or any other details I don't have a SQL 2000 installation to test.
select
SizeMB = size/128.0 ,
SpaceUsedMB = CAST(FILEPROPERTY(name, 'SpaceUsed') as decimal(18,2))/128.0,
FreeSpaceMB = size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') as decimal(18,2))/128.0
from
sys.database_files
In 2000 master sysfiles or [database name]..sysfiles does not have all the columns, i.e. no space used. I am looking at master..sysperinfo which is for "performance" I guess and it has a "free pages" listed and some other. Can't find a def for these so far.
You might want to try running your application with SQL 2005 Express which I think should run on Windows XP.
This would give you a database size of up to 4GB and access to sys.database_files.
Still for our program, the 4gb would still have to be monitored for full condition so to decide when to delete oldest data.
2000 is limited to 2gb but right now using various views and sys data like sysindexes I see that more than 2 gb has been used. The number of "reserved" pages is 213126 and the "used" is 213099 on one table, and then I add up all the tables and I get more than 2g. I'm ignoring the 200 mb for the log. The total data (adding each table "used" pages) comes to 2.33 gb! What is going on????
2000 must know if there is any free space available so it can insert new data -- so where is that? Is it compressing the tables before putting them into the mdb file?
When I do a space used sql command on the whole database (i.e. not individual tables) I get the proper total size 2 gb + 200 mb (data + index), but I get a NEGATIVE value for allocated, reflecting the fact that only 2gb is allowed but more than that has been "allocated".