Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
Read this rusanu.com/2012/07/27/how-to-shrink-the-sql-server-log and see if it sheds any light on what's going on. –  Remus Rusanu Oct 9 '12 at 9:16
add comment

3 Answers 3

up vote 5 down vote accepted

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+.

share|improve this answer
    
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. –  Petter Brodin Oct 10 '12 at 11:31
add comment

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')
share|improve this answer
add comment

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

share|improve this answer
    
Yeah, that's my plan in the long run, but for now I just want to get rid of autoshrink. –  Petter Brodin Oct 9 '12 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. –  cfradenburg Oct 9 '12 at 17:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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