[Webinar] Streamline your web hosting managementRegister Today


When do transaction log files release and reset to a smaller size?

Posted on 2008-02-07
Medium Priority
Last Modified: 2008-02-07
I am doing a full backup of a SIMPLE logged database that used to be set as FULL, yet the transaction log does not reduce in size. It has grown to be 300MB.
1. Is this normal?
2. How and when do transaction logs purge/reset themselves to their smallest size?
3. What do I have to do to manage them?
Question by:advlgx
  • 2
LVL 39

Accepted Solution

BrandonGalderisi earned 1600 total points
ID: 20843388
Transaction log files are managed by right clicking on the database in sql management studio and choosing properties.
Choose Files and then find the item in File Type as "Log"
Click the "..." in the AutoGrowth tab.  

From there you can manage the growth properties of the database.  

To shrink it manually, if you wish to, you can Right Click the DB > Tasks > Shrink > Files.

It will only shrink to the smallest size it originally was created.
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 400 total points
ID: 20843587
Contrary to popular belief the Transaction Log is an integral part of the database even when set to Simple Recovery Model.

So what you should do is set it to the appropriate size for your use and not shrink it down every time.  It will only have to increase again.  Every time it does that you will take a hit in performance.
LVL 39

Assisted Solution

BrandonGalderisi earned 1600 total points
ID: 20843803
To further elaborate on acperkins' provided insight, you will suffer the performance hit during the shrink/expand operations themselves in addition to fragmenting the physical file on disk making accessing it slower.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question