Shrink DB and Log on 2008

Question

Post reply

Shrink DB and Log on 2008

SRatnaparkhi

SSC-Addicted

Points: 474
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 7 (of 7 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: 474 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: 474 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

Shrink DB and Log on 2008

Cookies on SQLServerCentral