GrahamR99
asked on
MSDE database deleting data to make space.
Hello Expert-Exchange Members
I have an application that has a MSDE database, this week the database has hit the MSDE limit of 2GB data file. I have some space that will keep the application going for a couple of weeks. I need to make more space in the data file.
I have delete data out of the largest tables, but no space has been made in the data part of the database file. I have never seen this before, I deal with many SQL Database everyday and have been able to delete data from them to make sapce.
I have doubled check to see if the applcation is rebuilding the data in the database but that is not the case.
I think it may have something to do with the fillfactor between the databse table rows as it is set to 0% and I have never seen that before. Is that the case and how would I resove?
Thank you for Reading
Reagrds
GrahamR99
I have an application that has a MSDE database, this week the database has hit the MSDE limit of 2GB data file. I have some space that will keep the application going for a couple of weeks. I need to make more space in the data file.
I have delete data out of the largest tables, but no space has been made in the data part of the database file. I have never seen this before, I deal with many SQL Database everyday and have been able to delete data from them to make sapce.
I have doubled check to see if the applcation is rebuilding the data in the database but that is not the case.
I think it may have something to do with the fillfactor between the databse table rows as it is set to 0% and I have never seen that before. Is that the case and how would I resove?
Thank you for Reading
Reagrds
GrahamR99
ASKER
Hi Mcmonap
You have misunderstood my problem, I am not trying to make the data file smaller on the Disk drive.
The problem I have is that I have deleted two years worth of data out of four tables in the database, these tables are the bulk of the databse, but I have only managed to make a few MB's of space in the file its self. I am expecting to free up a few 100 MB's in the database as each table is around 500MB in size.
I don't understand how I am deleting rows of data in the tables but only a few MB's is beening freed up.
Regards
GrahamR99
You have misunderstood my problem, I am not trying to make the data file smaller on the Disk drive.
The problem I have is that I have deleted two years worth of data out of four tables in the database, these tables are the bulk of the databse, but I have only managed to make a few MB's of space in the file its self. I am expecting to free up a few 100 MB's in the database as each table is around 500MB in size.
I don't understand how I am deleting rows of data in the tables but only a few MB's is beening freed up.
Regards
GrahamR99
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The database files will not shrink in size if you delete data, SQL Server essentially marks that space as usable for any new data, you can see how much of the databse files actually contain data by using sp_spaceused (see below) you can also use your backup size as a rough and ready guide - if your sql backup is 500MB then there is 500MB of data in the database (this is only rough though). If you really want to reclaim the disk space you can use the DBCC SHRINKFILE command which will shrink the database file. There is a not a great deal of point to this though unless you need the disk space - details are below.
sp_spaceused:
http://msdn2.microsoft.com/en-us/library/ms188776.aspx
DBCC SHRINKFILE:
http://msdn2.microsoft.com/en-us/library/ms189493.aspx