primary filegroup full

Question

I discovered an error in a log file while troubleshooting another issue on our SQL 2000 server. I am seeing primary filegroup is full. The MDF and LDF files are in the default location on the system partition, on an NTFS drive. The MDF file is 1962MB in size. Some posts have indicated that the size can't excede 2GB. I ran a db shrinkdatabase against it but it does not appear to have changed the size. Is there a command I need to run first to remove old information first, before running the shrink?

When I go into the Enterprise manager I have 2 SQL groups. Once is local and the other is listed by the server name. The database problem is happening on the 2nd gorup. When I try to manually increase the size of the data file it says due to licensing restrictions I am limited to 2048 MB. The SQL instances in the other group allow me to change that number above 2048 MB.

Answer 1

MDF files are where the data is stored, so it's possible it holds that much data.

I've got a MDF file of 13GB so I don't believe that limit is correct.

Regarding shrinking this, by removing data, you might delete some data, and then shrink it, or you could extend that filegroup, by adding another data file to it.

Alternatively create a database maintainence plan to reduce the amount of free space per page, and also possibly remove unused space.

I would suggest you extend it, and then review your maintenance plans to ensure they are correct.

Answer 2

The 2GB database size limit only applies to MSDE (the free, "desktop" version of SQL 2000). Other SQL 2000 version don't have that limitation.

There is no "magic" way to purge or archive old, historical data. You have to know your database and its structure, your customer requirements, and your data retention needs.

Answer 3

You can try this:

ALTER DATABASE foo ADD FILE (
  NAME = 'file2',
  FILENAME = 'C:\PATH\TO\FILE.ndf',
  SIZE = 100MB,
  MAXSIZE = UNLIMITED,
  FILEGROWTH = 10MB;

Make sure you back up first.

Also, are you running SQL Server Express? There is a definite 2GB limit there...