How to shrink data files in SQL Server 2000

chintan · July 25th, 2007, 06:32 AM

Problem:
I am using SQL Server 2000. And I can't shrink some data files.

The way I shrink files:
SQL Enterprise Manager -> select Database -> Right click on the name and select Shrink Database -> select data files you want to shrink.

There are 30 files in my database. I can shrink 23 data files successfully. But the remaining 7 files which can't get shrinked. These 7 data files can free 26 GB, if I can shrink them successfully. But if I start shrinking of any from those 7 data files, after 8-9 hrs it hardly frees 4-5 MBs. I also tried DBCC shrinkdatabase and DBCC shrinkdatafile procedures. I also failed. So anybody knows other way to shrink files very efficiently.

Thanks in advance.

happygv · July 25th, 2007, 06:47 AM

Did you find out if there are any free space (unallocated space) left in the data/log files that can be freed by shrinking? I doubt it doesn't have unallocated space, thus not shrinking beyond 4-5 mb.

Cheers.

_________________________
- Vijay G
Strive for Perfection

chintan · July 25th, 2007, 07:11 AM

For example, there is one file "baselog". If I select that file as per above mentioned steps it shows:

Current Size : 10282 MB
Space Used : 4957 MB

This is real situation. so if I can shrink that file it will free 5 GB.

happygv · July 27th, 2007, 02:10 AM

Did you find the used space using sp_spaceused?

You can also use
DBCC SHRINKFILE (<logical file name>, <post_shrink_size>)
from query analyser.

Eg: DBCC SHRINKFILE ( 'baselog', 9500 ) -- check logical file name using sysfiles table

end of it you should see the file size as 9.5 GB, try doing this in chunks, like reducing it by half a GB or 1 GB at a time, so that it doesnt take much time to shrink it.

_________________________
- Vijay G
Strive for Perfection

chintan · July 27th, 2007, 04:12 AM

Sorry, I forgot to mention that I have already used "DBCC SHRINKFILE" procedure. And it also doesnât work properly. Any other solution???

munkee · July 30th, 2007, 06:06 PM

Here is what I use and it has gotten around any shrinkage problems.

use master
backup log blahblah with no_log
go
use blahblah
go
--Releases the Free space to the OS
dbcc shrinkfile ('blahblah_log')

Hope it helps.

jesusq · October 15th, 2008, 01:58 PM

Hello Chintan,

You would need to use "dbcc shrinkfile" but once a time and using small amount of size.

Example.

You have a database of 10 GB called database_10 and you know you can shrink to 7 GB , run in the Query Analyzer this sentences.

Run once a time to see if it is working.

database_10
files:
database_10_Data
database_10_Log

Code:

DBCC SHRINKFILE('database_10_Data',9800) -- Only Shrink to 9800 MB 
--Depends on time it takes you can shrink more space.
DBCC SHRINKFILE('database_10_Data',9500) -- Only Shrink to 9500 MB
DBCC SHRINKFILE('database_10_Data',9000) -- Only Shrink to 9000 MB

DBCC SHRINKFILE('database_10_Data',8800) -- Only Shrink to 8800 MB
DBCC SHRINKFILE('database_10_Data',8500) -- Only Shrink to 8500 MB
DBCC SHRINKFILE('database_10_Data',8000) -- Only Shrink to 8000 MB

DBCC SHRINKFILE('database_10_Data',7800) -- Only Shrink to 9800 MB
DBCC SHRINKFILE('database_10_Data',7500) -- Only Shrink to 9800 MB
DBCC SHRINKFILE('database_10_Data',7000) -- Only Shrink to 9800 MB

With this you can shrink the database.

Hope this work for you.

Antonio S.
DBA and Web Developer.
http://www.grupoKino.com