Decrease mdf File Size in SQL Express 2005/2008 Edition

If you are using the SQL Express edition then you have only 4 GB space to use. So, once you have reached the Max space (4GB), you will get the given below error message in the application -

“Error could not allocate space for object ‘TableName‘ in database ‘DatabaseName’ because the 'primary' filegroup is full.Create disk space by deleting unneeded files,dropping objects in the filegroup,adding additional files to the file group,or setting autogrowth on for existing files in the filegroup”

At this moment you may think that deleting some records may decrease your mdf file size. But no. This will not happen.

You have only one option to reduce the mdf file size, that is “SHRINK DATABASE”. Using this you can reduce the mdf size up to 1 GB.

Find below the SQL Query to shrink the database and to allow 10 percent free space in the database –

DBCC SHRINKDATABASE (DATABASENAME, 10);

Ebook Download
View all
Learn
View all