500 Points: How to reduce mdf file size??

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

and what about using the Shrink Database from the Enterprise Manager? isn't it useful?

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

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

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.

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?

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

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

>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

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

thank you angellll for being patient :)

I refered to this URL since i'm using SQL 2000 not 2005
https://www.experts-exchange.com/questions/20324001/Filegroups.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.

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?

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

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.

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

it WORKED :) :) :)

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

thanks alot Angelll and mwvisa1.
angelll you were very specific and patient, thanks again