How to reduce MDF file

Dear All;

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.

does someone have any suggestions?

Many Thanks,
Essam Andrew

try to rebuild indexes on tables, it might help, and then run
DBCC SRHINKDATABASE('db_name', 10) -- 10 percent free space or whatever suits you

Bambola.

Thanks Bambola....

Actually, I rebuilt indexes as you advise me and I ran SHRINKDATABASE, but it reduced by 200 MB only..!

is there any alternate way to reduce my huge MDF?

Many Thanks..
Essam Andrew

Why do you expect it to be much smaller? And how do you read the size of the mdf file? from EM? QA?

Bambola.

Enterprise manager, right click on DB, choose properties, then on Data File tab, in space allocated (MB), I find 8.3 GB as MDF size..!

Any suggestions to reduce this MDF..?

Regards..

Essam Andrew

Again, why do you expect to reduce this size bu much?
What makes you think that it can be much smaller?

Bambola.

BTW, did you srhink it from EM or run from QA the DBCC SRHINKDATABASE command?

Bambola.

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.

/Argyle

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.

Gaurav
Moderator
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..!

any comments..

Regards..
Essam Andrew

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 ?

Bhushan

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.

Gaurav
Moderator
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...

Bambola.

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...

Essam, please address these issues:

1. What is contained in the 900 MB?
2. What size reduction are you expecting?

And

1. What was the size of the new container database after moving ALL the objects?
2. Did you try shrinking this new container database?

Nathan H.O.
Moderator
SQL-Server-Performance.com

true, but Tlog isnt an MDF file <img src='/community/emoticons/emotion-1.gif' alt='' />

Dear Gaurav,

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?

Regards..
Essam Andrew

DB Backup size = 3.9 GB
size of Tlog = 10 MB ( I truncate Tlog on daily basis )

the result of SP_SPACEUSED :

reserved : 8.3 GB
data : 1.5 GB
index_size : 90 MB
unused : 6.8 GB

Regards...
Essam Andrew

Now could you please run DBCC SRHINKFILE(data_file, 10) and then repeat the numbers?
You can get the file name from sp_helpdb 'database_name'.

Bambola.

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.



Nathan H.O.
Moderator
SQL-Server-Performance.com

Nathan, as mentioned in one of the previous posts, the transaction log size is 10 MB.

What was the size of the database when it was created?

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

Bambola: the figures after I ran DBCC SHRINKFILE(data_file, 10) are:

CurrentSize: 831080
MinimumSize: 128
UsedPages: 831072
EstimatedPages: 831072

regards..
Essam Andrew