Need to Shrink data & log file

Question

Need to Shrink data & log file

gary1

SSC-Insane

Points: 21152
More actions
November 10, 2009 at 1:40 pm

#217455

Hi,
We have SQL Server 2005 EE x64 with SP3. We have a databse of size 35 GB and the OLD archived data has been deleted. Now,
1. the databse size is 35 GB and space availble is 17 GB (looked at database properties from Management studio)
2.Data file size is 29 GB and free space in data file is 17 GB
3.Log file size is 6 GB and free space is 5 GB
Now, I tried the following steps to Shrink the data & log file
1. From Management studio->Database->Right click-> tasks-> shrink-> file-> selected data and click OK with default option release unused space. BUT it did NOT release any space and still showing Free sapce as 17 GB.
2. From Management studio->Database->Right click-> tasks-> shrink-> file-> selected Log and click OK with default option release unused space. BUT it did NOT release any space and still showing Free sapce as 5 GB.
Please help me in shrinking these data & log files. Because these files will not be grow much in future
thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Before you get totally flamed. What do you mean that the files will not grow much in the future? In my experience if a database or log has grown to a certain size it will grow there again. What steps will you be taking to keep them from growing again?

The general recommendation is to never shrink database files because it causes major fragmentation so if you have the drive space, it appears you do, you should probably just leave it. You could probably get away with a smaller log file if you are having backup issues.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

gary1 SSC-Insane Points: 21152 More actions · Answer 2

thank you,

we have space issue and thats why we deleted unwanted data from the database and free space is 17 GB. I want to keep free space as 5GB and release the rest to OS. Because 5GB free space is enough for this database to grow in 1 year.

thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Run this:

select name, log_reuse_wait_desc from sys.databases

What does the log_reuse_wait_desc say for this particular database?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Shrinking only releases space at the end of the file, from BOL:

TRUNCATEONLY
Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent.

So you may want to do a DBCC ShrinkFile('Data File', NOTRUNCATE) and then run TRUNCATEONLY.

A similar thing applies to the LOG file. If the active VLF is at the end of the log you will not get any shrinkage. You need to make the Log warp around to the beginning and do a log backup then Shrink.

You'll want to re-organize/rebuild indexes and update stats when you are done. Be sure to leave enough space.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

You can also run DBCC LOGINFO('db name') to see the VLF's in your log which will show which are inactive. The status will be 0 for available/inactive. If there are non-0's near the end you can only shrink back to them.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

You can use the following script to shrink the data file.

Shrink DB File by Increment to Target Free Space

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355

After you shrink the datafile, you should:

1. Defragment the indexes and update the statistics on all tables.

2. Then shrink the log file.

gary1 SSC-Insane Points: 21152 More actions · Answer 7

thanks,

I have executed the below script:

select name, log_reuse_wait_desc from sys.databases

where name = 'MyDB_PROD'

Results:

name log_reuse_wait_desc

MyDB_PROD NOTHING

How the Shrink file is related to log_reuse_wait_desc?

lokhande.deepali SSC Eights! Points: 833 More actions · Answer 8

Hi,

log_reuse_wait_desc detemines or givesDescription of reuse of transaction log space is currently waiting on one of the following:

NOTHING

CHECKPOINT

LOG_BACKUP

Note:

If the reason is LOG_BACKUP, it may take two backups to actually free the space.

ACTIVE_BACKUP_OR_RESTORE

ACTIVE_TRANSACTION

DATABASE_MIRRORING

REPLICATION

DATABASE_SNAPSHOT_CREATION

LOG_SCAN

OTHER_TRANSIENT

For more information, see Factors That Keep Log Records Active in book online

as your database log_reuse_wait_desc detemines NOTHING i.e you can shrink the log file .

Rd,

Deepali