8

I'm somewhat of an accidental DBA, being a developer who inherited a couple of database servers (2005 and 2008) from someone who knew little about database administration, and seemingly had even less interest in learning more about the subject.

I'm learning as I go, and am currently trying to figure out transaction log files.

All our databases have been set up with the simple recovery model and autoshrink. I've understood that using autoshrink is usually a horrible idea, but it's my understanding this was done in order to stop the transaction logs from growing out of control. (Does autoshrink actually shrink the log file(s) or just the DB?)

I found this about SQL Server 2012, and was wondering if it's true about 2005 and/or 2008, and exactly what it means: "When a database uses the simple recovery model, the Database Engine truncates the transaction log after a checkpoint. [...] The Database Engine triggers an automatic checkpoint under the simple recovery model when the virtual log becomes 70 percent full." Where is the virtual log size specified?

I want to disable auto shrink on all databases, but before I do that, I need to know that the log files won't grow out of control quickly.

Any help would be greatly appreciated.

1

3 Answers 3

6

A single transaction log file has both a physical size (that you see on disk), and it's also broken down within the physical file into logical sections called virtual log files (VLFs).

Both auto-growth and auto-shrink operate on the physical transaction log file.

Transaction log truncation (also called "log clearing") operates on the logical sections of the transaction log (VLFs), and does not affect the physical file size. This part is frequently the subject of confusion.

A log file must always grow to accomodate a large transaction; turning off auto-shrink will leave the log file with its maximum needed size, instead of physically decreasing its size.

If you don't have large transactions, it will be safe to disable auto-shrink; the log files will not grow without bound like would happen if the database was in FULL or BULK_LOGGED and you weren't taking transaction log backups.

This behaviour is the same for SQL Server 2005+.

1
  • Thanks, that's about what I was looking for when I asked the question. I came to the same conclusions after reading Remus's earlier reply. Oct 10, 2012 at 11:31
2

So, here's what I've found after reading the other replies here and doing some research on my own:

Q: "Does autoshrink actually shrink the log file(s) or just the DB?" A: From what I understand: yes, it does. Autoshrink is set on the database level, and affects all files (seen if you right click the database -> properties -> files, or if you run query 1). Autogrow, however, works on a per file-level.

Q: "Where is the virtual log size specified?" A: See Jon Seigel's reply and the link Remus posted. To see physical and logical log size, use query 2

One problem is that if the database has had full recovery mode activated, grown to a large size, and then had the recovery mode changed to simple, a checkpoint won't be triggered as the VLF has autogrown. It's possible to attempt to resolve this (see Remus's reply for potential problems with head/tail of the log files) by running query 3, which will reduce the logfile down to the size it was when it was originally created.

Queries:

1)

SELECT name, physical_name AS current_file_location, DB_NAME(database_id) AS dbname
FROM sys.master_files
WHERE DB_NAME(database_id) = 'mydb'

2)

DECLARE @tmpt TABLE(
    dbname VARCHAR(255),
    logsize DECIMAL,
    logspaceused DECIMAL,
    stat INT
)

INSERT INTO @tmpt
    EXEC ('DBCC SQLPERF(LOGSPACE)')

SELECT * FROM @tmpt WHERE dbname LIKE 'mydb' ORDER BY logspaceused DESC

3)

checkpoint
DBCC SHRINKFILE('logfile_name')
2

As you referred in your question, in SQL 2005 and 2008 after the checkpoint, the transaction log file will be truncated as well.

My suggestion would be setting the recovery model to full and create a job to take a backup from the transaction log file. This job can be scheduled on your database and will truncate the transaction log after taking the backup. It automatically will truncate the log file for you. Please have a look at the links below:

SQL Server 2005 : http://technet.microsoft.com/en-us/library/ms189085(v=sql.90).aspx

SQL Server 2008 : http://technet.microsoft.com/en-us/library/ms189085(v=sql.100).aspx

2
  • Yeah, that's my plan in the long run, but for now I just want to get rid of autoshrink. Oct 9, 2012 at 10:52
  • Changes are you do want to change them to FULL recovery but before you do evaluate whether or not it's actually needed. If you need to be restore to as close as possible to a disaster then you do. If going back to the last full backup (usually nightly) is fine then the SIMPLE recovery mode will work fine for you. Oct 9, 2012 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.