Best way to shrink LARGE log files

Question

Best way to shrink LARGE log files

Juanita

SSCertifiable

Points: 5300
More actions
May 10, 2004 at 11:40 am

#85509

HI. I noticed that I have about 4 LARGE log files When I did a dbcc sqlperf(logspace) I spotted them imediately. I've never modified the sizes of my log files. I have 3 questions:
1) Best method to shrink the log files
2) Best method to determine what size the files should be. I can see the % log space used and was wondering if i calculate what size that is by figuring the % space used against the actual size of the file.
3) one of the large log files is with the MSDB database. does this log file need to be handles differently?
THANKS SO MUCH !
Juanita

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

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

Gerardo Talamo SSC Enthusiast Points: 199 More actions · Answer 1

You might need to consider if your log file /recovery plan is correct. do you need it on full, you might find it easier to have it on bulked log or simple.

Try BOL truncating logs, or if you want to shrink them try

DBCC Shrinkfile this is also in Books on line.

hope this helps

webnando SSC Enthusiast Points: 173 More actions · Answer 2

When i have to clear a log i use:

BACKUP LOG DBNAME WITH TRUNCATE_ONLY

DBCC SHRINKFILE ('LOGNAME', 1)

Juanita SSCertifiable Points: 5300 More actions · Answer 3

Thanks a bunch. works like a charm.. I wasn't doing the truncate only and this was holding me up.

Juanita

Juanita SSCertifiable Points: 5300 More actions · Answer 4

Now that I've got my log files down in size by doing the backup log <dbname> truncate only and then the shrink file command, should i THEN perform a full db backup with a tlog log backup? I read somewhere in BOL about doing a db backup after a backup log.

Does this sound familiar?

Juanita

dbuchan SSCrazy Points: 2328 More actions · Answer 5

Absolutely. I had a similar problem once.

BOL - refer "truncate_only"

Although the transaction log may be truncated manually, it is strongly recommended that you do not do this, as it breaks the log backup chain. Until a full database backup is created, the database is not protected from media failure. Use manual log truncation only in very special circumstances, and create a full database backup as soon as practical.

DB

The systems fine with no users loggged in. Can we keep it that way ?br>

hoo-t SSChampion Points: 12713 More actions · Answer 6

A transaction log backup will perform a truncate after backing up the log. Therefore, to keep your logs small, and have maximum protection, you should schedule regular transaction log backups between your full database backups. If you do this, you should never have to do a backup log with truncate_only.

Steve

Juanita SSCertifiable Points: 5300 More actions · Answer 7

Thanks so much to everybody that replied. I think I got it now. Sure helps to get first hand help in addition to BOL.

A happy camper,

Juanita

Ionel Ciobanu-132742 Grasshopper Points: 22 More actions · Answer 8

Juanita, U can create a Database Maintenance Plan (which solves many problems for one shut); and watch your server with "SQL Check" (free from IDERA).

All the best,

Jimmy