We help IT Professionals succeed at work.

500 Points: How to reduce mdf file size??

3,562 Views
Last Modified: 2011-10-19
Hello Experts,

i got a huge dabatase where its mdf file is about 22G of size!! I need to reduce this size. I was thinking of shrinking the database, it seems that the admin who left set a schedule to shrink this DB ( look at the attached jpg) but it seems that it was failing. i attached the error  as well.

my question is, is shrinking my only option here?

what are the recommended settings for shrinking a DB?? i need a spicific answer plz. . like, what shall i  set in "Maximum free space in files after shrinking:  "default is 0%"

and shall i keep "Move pages to beginning of file before shrinking "unchecked""

regards,

EventViewer.JPG
SQLShrinkSettings.JPG
Comment
Watch Question

Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
ok, lets go through it:

DBCC SHRINKFILE
(
     { 'file_name' | file_id }
    { [ , EMPTYFILE ]
    | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
    }
)
[ WITH NO_INFOMSGS ]

but what does it mean by the file logical name. there is only physical name.
and if i used  NOTRUNCATE  option what is the target-size i should specify??

Author

Commented:
and what about using the Shrink Database from the Enterprise Manager? isn't it useful?
Brian PiercePhotographer
CERTIFIED EXPERT
Awarded 2007
Top Expert 2008

Commented:
The command would be eg:

DBCC SHRINKFILE (DataFile, 10);
GO

(where 10Mb is the target size)

You can use the Enterprise Manager, but obviously you cannot schedule that, You need to find why the scheduled task is failing - the account its using needs to be sysadmin or db_owner and there needs to be more free space on the disk than the size of the file to allow the process to work. See http://msdn.microsoft.com/en-us/library/ms189493.aspx for more detail

Author

Commented:
thank you for tour reply, i used the command and it is still running.

i tried to run the command by specifying the mdf file name " DataFile.mdf" but it only accepted "Datafile " with out extensions, i'm affraid this will only shrink the log file 2G which is small compared to the data file 23G.

will let you what happens. and hope this would solve my problem
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
azz-khamees, if you scroll to towards the bottom of the MSDN article, they had syntax for the data file.  If you look in the file properties, each file has a logical name.

Usually something like databasename_data.

Since you have statistics showing about 9 GB of free space, I would set the shrik to go down to 12-15.  You can try the Shrink Database from Enterprise Manager and tell it to reorganize data.  Usually when that doesn't work, you use the DBCC SHRINKFILE but it sounded like you were at that point since your scheduled task wasn't doing the trick.
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
the log file got begger!!!! 4G!!! while nothing changed in the data file size. how could this happen after runnig the SHRINKFILE?

I took a backup of the data and i don't mind to clear some of the tables records. i tried to manually delete but couldnt ( got no enough memory error) is there a way to delete old records (i.e delete any 2006 and 2007 records)

angellll: thank you for your reply, actually the developer who created this system has left, and i'm a system admin and have no idea of how to redesign a DB.

mwvisa1:

thank you again for your reply, I used the following command:

DBCC SHRINKFILE (DataFile, 10);
GO  

which is mentioned in the artichle to shrink data files and the log file got begger in size!!

what is going on?
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>the log file got begger!!!! 4G!!! while nothing changed in the data file size. how could this happen after runnig the SHRINKFILE?

when the log file increases, there were some transactions ongoing to use that space.
for example, deleting table data will use transaction space

to avoid have a t-log file getting bigger and bigger, implement regular (for example hourly) t-log backups.

>angellll: thank you for your reply, actually the developer who created this system has left, and i'm a system admin and have no idea of how to redesign a DB.
no "need" for a developer for that :)

in short, in the database properties, you can add filegroups, and add (new) files to the filegroups.
then, you can "move" tables or indexes to those new filegroups (indexes is "easy", tables are moved by adding/recreating a clustered index for the table on the destination file group).

once all the moves have completed, you can shrink the data files one by one, giving you a nice db.

Author

Commented:
>you can add filegroups, and add (new) files to the filegroups.
just to make sure before doing anything, i went to DB properties, filegroup tab, there was only PRIMARY file. I should add new files here, am i right?

>you can "move" tables or indexes to those new filegroups

how can i do this?
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>just to make sure before doing anything, i went to DB properties, filegroup tab, there was only PRIMARY file. I should add new files here, am i right?
in the filegroup tab, you add filegroups first :)

>>you can "move" tables or indexes to those new filegroups
http://msdn.microsoft.com/en-us/library/ms175905.aspx

Commented:

try running DBCC SHRINKDATABASE(dbname). This function will not actually shrink the log file but mark it to shrink. Then truncate log or backup log, depending on your log management strategy for the database

To Shrink the LOG file (.ldf) follow the procedure.

step 1 :-run the following command

dbcc shrinkfile ( logical file name,target size,truncateonly)

note :- you can get the logical file name for the log by running the
following select statement (select * from sysfiles)

step 2 :- if the database has the truncate log checkpoint turned off, then the
transaction log file will be shrinked to the target size only when the
transaction log is backed up. or if the truncate log on checkpoint is
turned on the go to enterprise manager - all tasks - and click truncate
log this will shrink the file upto the target size and will release all the
unused space to the os

(note :- you cannot shrink the log file (.ldf) below certain size ,for more
information , refer on line manual.)

To Shrink the DATA file (.mdf) follow the procedure.


Step 1 :- dbcc shrinkfile ( logical file name,target size)


Example (before shrinking the .mdf file)



1) database name is ebdata

used free

data -- 180 mb 1819.5 mb

log -- 56 mb 443 mb

(now we know that dba made mistake in the sizing
of the database. )

to get the logical name of the (ebdata - data file , run the following
select statement (select * from sysfiles)

the following are the query results

fileid groupid size maxsize growth status perf name filename
1 1 256000 -1 10 1081346 0 EBData_Data F:CVDatabaseEBData_Data.MDF
2 0 64000 -1 10 1081410 0 EBData_Log F:CVDatabaseEBData_Log.LDF

Run the dbcc shrinkfile to shrink the datafule (.mdf)

Dbcc shrinkfile (ebdata_data,400)

This will reduce the size (EBData_Data.MDF) to 400 mb and 1.6 gb free space to
the os.)

Author

Commented:
thank you angellll for being patient :)

I refered to this URL since i'm using SQL 2000 not 2005
https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_20324001.html
in moving tables to filegroups. I created a new file group, all tables moved easily except 2 tables, it seems that they are the huge ones. so i created new filegroup and tried to move ine of them in another location ( has 10G free) since the current has only 2G left, and i'm "praying" that this table won't exceed 10G of size :) the Enterprise Manager is not responding but i can see that the file size is changing (it reached now 2.5G)
So i guess i have to wait till tomorrow and see what would happen!!

I started shrinking the old PRIMARY filegroup (since it is taking a log time too) using the SHRINKFILE command.

Author

Commented:
one table is left and now and it is in the process of moving to the new filegroup.

my question now, can i delete the primary filegroup and the old data file  after moving all tables the new filegroups??
or shall i do the shrink and keep the primamy?
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>my question now, can i delete the primary filegroup
no. it MUST remain (you cannot delete it)
it does contain the procedure/view/function script stuff (hidden)...

Author

Commented:
so my only hope is that (even after moving all tables to other filegroups) this Data file would shrink after  running the command!

ok, i'm waiting now for the last file to be moved so i can run the command and will let you know what happen.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
no need to "hope", it will work  :)

Author

Commented:
it WORKED :) :) :)

Thank you soooo Much angelll. you deserve 100000 points :)

Author

Commented:
thanks alot Angelll and mwvisa1.
angelll you were very specific and patient, thanks again
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.