Shrink Data File - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Shrink Data File

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2006-02-14 : 22:51:27

Dear all

We have a database that the following situation:
DB_data: 74,825MB total with 56,613MB used space and 17,911MB unused space.
DB_log: 2,002MB total with 22MB used space and 1,980MB unused space.

Before, we manage to shrink the transaction log from 16GB down 2GB.

Now, we would like to shrink the datafile from 74GB in total to 60GB in total .. leave out 5GB for ununsed space. We setup threshold that increase every 5GB in database properties.

How do I do this again? Is this the same procedure as shrinking tranction logs? So do like this?

DBCC SHRINKFILE ('DB_data', 60000)

Or something else?

Cause I couldn't find a good reference regarding this? I can find so many procedre to do shrinking trans log but not data file.

Any ideas?

Thanks

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-14 : 22:58:59

Hi,
You can use
DBCC SHRINKDATABASE when you want to shrink all data and log files for a specific database.
OR
DBCC SHRINKFILE when yu want to shrink one data or log file at a time for a specific database.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-02-15 : 01:13:59

You can also shrink on a per-file basis pretty easily using Enterprise Manager. There are examples of how to do this in BOL.

Now that you know how to shrink the files, why are you doing it? Are you low on disk space?

-ec

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-02-15 : 02:00:28

unless you have freed enough disk space like data deletions/archiving,
you may not see much difference in shrinking the database file

HTH

--------------------
keeping it simple...

dewacorp.alliances

452 Posts

Posted - 2006-02-16 : 21:32:08

The reason I asked this due I want to know the behaviour after you do this. Cause as you if you DBCC SHRINKFILE for transaction log ... somethimes the size is still as used to be (never change) unless you do the BACKUP LOG WITH TRUNCATE_ONLY stuff.

So ... I'm just wondering is there any a case if you do DBCC SHRNKFILE for data file ... that the size is the same not what you've expected? Any input regarding this .. I'm really I appreciated. I'm more concern on after you run DBCC command for data file.

Thanks

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-02-16 : 22:11:33

ok, shrinking log and shrinking data files will have different behavior.

for data file, please refer to my previous post

for log file, if the recovery model is set to simple, no need to shrink or truncate the log file, once trans get committed, they are removed from the log file

for full recovery model, even after the trans get committed, they will still be in the logs, this is to enable point in time recovery unless you do a log backup, truncating at this point is not good especially if do intend to do point in time,

so, if you backup regularly, you may find that you don't need to do truncate and shrink afterall, because this gets handled by the backup log

but if you don't backup regularly, you will observe that the log increases in size and you may feel there is a need to truncate then shrink...

truncate means as is, cutting off the log file without backing up, in backup log, you get to roll back whatever transactions that already got committed through restore, in truncate, no option

so shrinking is physically decreasing the size of the log file by compressing the space, removing as much free space in between possible

in the end... if you want point-in-time restoration, implement routine log backups to maintain log file size

otherwise, just set your recovery model to simple

HTH

--------------------
keeping it simple...

dewacorp.alliances

452 Posts

Posted - 2006-02-16 : 23:17:54

Hi Jen

Thanks for that. Our database (data) has 74GB total with 56GB used space and 18GB unused space and we would like to reduce this unused space to 5 GB so the total will be 62GB.

So my question there will be no issue or no strange behaviour to to do DBCD Shrinkfile on this data file?

Thanks

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-02-17 : 00:34:51

question really is:

is there any special requirement for you to shrink it? coz eventually it'll grow back

otherwise, shrinking it will not have any adverse effect (afaik, but someone who is an expert may probably give you more technical information on this) aside probably on how fast the shrink will be done, so better do it off-peak hours

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources