How to reduce MDF file

I have a huge primary MDF - ~8.3GB. I've tried all the actions I know of to reduce the physical size of it... Backup up the DB, Backed up the transaction log, used DBCC ShrinkDatabase & DBCC ShrinkFile...but the MDF size remain same..!

The first time I ran the DBCC ShrinkFile the transaction log went down to 5 MB -- but MDF remains at ~8.3GB. I used the Shrink command but this did not affect the size.

How much space is actually used in the 8.3 GB MDF file? If 8.3 GB is used in the file then that is how much space the database will take up. It won't help to try and shrink the MDF file if all the space in it is used.

If that is the case and you think the data in the database should be less then look for archive or log tables in the database. If there is a batch job or something logging to the database look if some of the data can be deleted or archived to another database. In EM choose "view taskpad" when looking at the database and go trough all tables to see if anyone take up too much space.

Use sp_spaceused with update usage to see what is the actual space used. If u see the same figures, data can not be reduced. U'll have to archieve the database to move some data from the main database to the archived database.

GauravModerator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

I used sp_spaceused and I found the same figures..(data size ~8.3 GB)..
today, I did a small experiment: i transfered All tables, views, proc., etc to new database in different server, and I after transfere I ran sp_spaceused , and I found the data size went down to 900 MB only..!

Did u drop the transfered tables, views, proc, etc from the old database where your size decreased to 900 mb, if yes then what is left in the database ? Or is it that after transferring the above mentioned objects, have u truncated the tables ?

Wait a minute, I thought we are talking about data file and not log file.

As far as reducing the data file size is conccerned, as I said before, if the data size in the database is 8.3 GB then there is no way you can decrease the data file beyond 8.3 GB. If you want to do that, consider partitioning the data between two databases keeping the active data in one database and old data in archieved database.

GauravModerator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Back to the DBCC SRHINKDATABASE. If you ran it from QA, with what parameters did you ran it?
If you did from EM, did you check the option that says "move pages to beginning of file before shrinking"?
If you didn't, try again to run this command from QA

DBCC SRHINKDATABASE('database_name', 10)

If it doesn't shrink it and the data (data file not log!) is really only 900 MB, than I really don't understand this...

another possibility is if varchar columns have been removed from tables, but dbcc cleantable has not been run. SQL won't reclaim space of dropped varchar column automatically 7GB seems excessive though, are you counting index space in that 900MB too? the mdf file includes indexes as well as data...

OK if there is no way I can decrease the MDF data file beyond 8.3 GB, i want to know why after transfering the same dta( all tables, views, procedure..etc..) to new dtabase in different server, I check the size of the MDF file ( for this new database) and I found it ( 900 MB) only..althogh it contains same data from original DB?

Satya may have a point about the T-Log contributing to the perceived 8.3GB size of the database despite the fact that Essam is referring only to the MDF file. An irreducible 8.3GB could have resulted from a T-Log that cannot be truncated (and shrunk) any further.The movement of ALL database objects that gave Essam only 900 MB maybe be testomony of this although there is a possibility that not 'ALL' objects were moved e.g. indexes. Essam might also benefit from checking how much data the system tables of the old database contain - I have seen blotted system tables before.

7.4 GB of data (or space for that matter) is hard to miss and only a rare bug would attempt to explain it.