Log shrink clarification

[lory]

Microsoft Windows 2000
Microsoft SQL Server 2000 Enterprise
Recovery Method = Full
Production database file = 36,140MB current size, 26,535MB space used
Production log file = 1,322MB current size, 43MB space used
Autoshrink = NO

Backup schedule Monday-Saturday:
Log backups every 4 hours/daily
Differential database backups everyday at noon
Full database backups every night at 9pm

Sunday jobs
Job #1 -- 1AM
Step 1. Reindex all tables in production database
Step 2. Full database backup

Job #2 -- 10AM
Step 1. Log backup (22.5GB)

The log file doesn't seem to shrink after the full database backup (Job #1, Step #2). If I run a BACKUP LOG WITH TRUNCATE_ONLY, will that shrink the physical log size? If so, how much? The space allocated for the log changes as the log grows?!

Should I run a DBCC SHRINKFILE ([dbname],[target percent]) to shrink the file after Job #1, Step #2? My goal is to reduce the physical log file before Job #2.

 

[Corran007]

The log will not shrink unless you tell it to do so. Backup log with trunacte only will clear out all the completed transactions, but this will still not reduce the size. Two ways to shrink it. Shrink database or shrink file. If your goal is to reduce only the log file, then use shrink file. This should be done after a full backup or a transaction log backup if you want to reduce space. So to answer your question, yes add a shrink file after step 2 and it will reduce it before it gets to job 2.

 

[lory]

Well, I tried added this step after the database backup DBCC SHRINKFILE (dbfilename, 10) and nothing happened. At least the file did not get smaller. I assumed that would happen since you have to actually truncate to force the transaction log to reduce.

At any rate, are there real performance issues with setting the option "autoshrink"? I would just rather do this, but I need to make sure we don't take a hit of any kind before we do this.

 

[Corran007]

Nothing appreared to happen, but it actually did. The log file is linear. Likely it is currently writing to the end of the log file. It is unable to delete anything above its current location, only below it. Use DBCC LOGINFO to see where it is at. a 2 means its current location and is likely at the end of the file. as it rotates around to the beginning it will reduce in size since future transactions will be written to the beginning of the file.

As for autoshrink, i dont know the affects of the performance issues. I believe it checks ever 30 mins and sees if it needs to be done? Big guess. I do know it has a performance it.