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.

Petter Brodin
  • 897
  • 2
  • 11
  • 18

3 Answers3

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

Jon Seigel
  • 16,922
  • 6
  • 45
  • 85
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

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
Sky
  • 3,744
  • 18
  • 53
  • 68
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')
Petter Brodin
  • 897
  • 2
  • 11
  • 18