Shrink DB and Log on 2008

Question

Post reply

Shrink DB and Log on 2008

SRatnaparkhi

SSC-Addicted

Points: 464
More actions
November 19, 2009 at 5:40 am

#216825

Can any one tell me how to shrink log file.
I have a database in that log file is 15 GB and data file size is 148 MB.
Please can someone help me for this..
Thanx in advance
Satish R

Viewing 7 posts - 1 through 6 (of 6 total)

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

Old McDonald SSCertifiable Points: 5768 More actions · Answer 1

Old McDonald

SSCertifiable

Points: 5768

November 19, 2009 at 5:55 am

#1081485

http://tinyurl.com/ygfgzgq

-Vikas Bindra

SRatnaparkhi SSC-Addicted Points: 464 More actions · Answer 2

Thanks Vikas for quick reply, I already use

DBCC SHRINKFILE (DBName, Size)

for reducing my database but it not work. Database base log file size remain same.

I also run a script:-

http://www.sqlservercentral.com/scripts/63782/

but this is only for 2005 not for 2008.

Is their any other way to comprase/Delete Log file.

Thanks

-Satish R

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 3

I have a database in that log file is 15 GB and data file size is 148 MB.

is your DB 15gb and log = 148? or the ther way around?

either way, you can try the easy way:

in SSMS, right click the DB, Tasks, Shrink, Files.

You will see a screen with options:

Set: FILE TYPE to Log

At the Bottom you wil se Shrink Action Options:

1. Release unused space

2. Reorganize Pages ......

3. Empty file by moigrating data.

my choice is 1 or 2.

3 will give you sort of the same result as Truncating it. But it wont make the file smaller, just emptier.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Old McDonald SSCertifiable Points: 5768 More actions · Answer 4

satish.ratnaparkhi (11/19/2009)
Thanks Vikas for quick reply, I already use
DBCC SHRINKFILE (DBName, Size)
for reducing my database but it not work. Database base log file size remain same.
I also run a script:-
http://www.sqlservercentral.com/scripts/63782/
but this is only for 2005 not for 2008.
Is their any other way to comprase/Delete Log file.
Thanks
-Satish R

Hi Satish,

If DBCC SHRINKFILE with target size is not working then can you open DBCC SHRINKFILE in BOL and there a section "The File Does Not Shrink", try the steps provided there and let us know the output.

-Vikas Bindra

SRatnaparkhi SSC-Addicted Points: 464 More actions · Answer 5

After doing whatever proccess told by Henrico. Database size remain same, and in my database ldf size is 15gb and mdf size is 200mb.

Actually I want to make ldf size smaller. so I can restore into the testing server, for futher testing.

With DBCC SHRINKFILE option I think it is not possible. So, Is there any other way to do it.

Thanks

Satish R

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Please read through this - Managing Transaction Logs[/url]

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass