Please confirm CLP commands to reduce size of logfiles 2

[DeepDiverMom]

As an example of my situation, for one of my databases, I have a "...Data.MDF" file that is 982MB; my "...Log.LDF" file for that database is 5GB. As similar scenario exists with other databases, as well.

Can someone confirm for me the CLP command syntax I should use to reduce the storage consumption to as small as possible for my log files? I have read that I must first truncate the log, then shrink the log file. Since these are very important databases, I just need a "warm fuzzy" from you gurus confirming the CLP commands I should issue to recover to the operating system the ton of space that I'm sure the Log.LDF file is consuming but no longer needs.

Thanks,

Becca

 

[mrdenny]

To clear the log:

backup log {database} with truncate_only
go

To shrink the log:

use {database}
go
dbcc shrinkfile ({log_file_name}, {target_size_in_MB})
go

You'll want to backup the log on a regular basis to keep it from growing.
After you backup the log and shrink the file, you'll want to do a full backup of the database.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[DeepDiverMom]

Denny,

I sincerely appreciate your confirmation of the correct commands. I believe I have just one additional question before I try this out, experience success (thanks to you), and do the Purple Star thang: "How do I anticipate the 'target_size_in_MB' that I should specify?" What are the implications of specifying a size that is too small? For example, if I specify "1" MB, what damage will that cause?

Thanks again,

Becca

 

[Joeybear23]

The target size should not be smaller tha the data contained in the log, but after a truncate that is 0, so theoretically it does not matter. In th real world, you should set it to 50-100% larger than the size it normally grows to between regular log backups. Also make sure the growth is set accordingly, if file growth for the log is set to 10% and the file is only 1 MB you can run into problems if 20MB of changes come through.

I would try shrinking it to 250MB for your application, then watch how the used space behaves before deciding on what size you want to set it at later.

 

[mrdenny]

There is no dnager of setting the size below the current size of the data. It will simply shrink to the current data size. I normally recommend setting the transaction log size to 2-3 times what it needs to be.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[DeepDiverMom]

Denny and Joey,

Thanks so much for your help and good answers. Havvah Star !

Becca

 

[mrdenny]

Thanks, glad to help.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)