0

Use KISS Method for SQL Server Transaction Log Management

Over the years, one of the most annoying issues that seems to pop its ugly head up time and time again is “The transaction log for database * is full”.  I’m certain that this will not be the last time I write about transaction log management, but it seems like a good place to start.  Valentine’s Day reminded me of one of my favorite philosophies that we widely use at ProActive Technology: Keep It Simple Stupid (KISS).

The majority of transaction log issues can be resolved by using the KISS method.  Changing the recovery model of the database from full to simple will prevent and resolve most transaction log issues.  Only databases that are setup for Mirroring, as a Replication publisher, or requiring point in time recovery need to be using the full recovery model.  Since most databases fall outside of these categories, I find it odd that, by default, newly created or restored databases are set to use the full recovery model.  There are three steps to resolve this: Change the database with the full transaction log to use the simple recovery model, shrink the log file, and update the model database to set it to use the simple recovery model (optional).

Why Simple Instead of Full Recovery?

The full recovery model retains all transactions that have occurred in the transaction log file until the log is backed up.  So, unless a backup or maintenance plan has been setup to backup the log, it will continue growing until it has used up all of the available disk space.  Using the simple recovery model, once a transaction has been committed or completed, the transaction is removed from the log and the space it used is freed up for other transactions.

Change Database to Simple Recovery Model

SQL Script

ALTER DATABASE [Database Name] SET RECOVERY SIMPLE

Using SQL Server Management Studio

  1. Right click on the database in object explorer
  2. Click on “Properties”
  3. Click on “Options” in the “Select a page” pane
  4. Choose “Simple” in the recovery model drop down
  5. Click OK to save the change

Tlog1

 

Shrink Log File(s)

SQL

  1. Find the logical names of transaction log files.  (The size column contains the number of 8k pages for the file, so we multiply by 8 and divide by 1024 to get number of MB)

USE [Database Name]

GO

select name, size*8/1024 as Size_MB from sys.database_files where type=1

  1. Using the results of the query in step 1, run the following for each name that needs to have the size reduced, along with what the desired new size will be.

USE [Database Name]

GO

DBCC SHRINKFILE (N’[Log Name from step #1]‘ , [Desired Size in MB])

GO

Example:

DBCC SHRINKFILE (N’Test_Log’ , 100)

 

SQL Server Management Studio

  1. Right click on the database in object explorer
  2. Mouse over “Tasks”
  3. Mouse over “Shrink”
  4. Select “Files”
  5. Change “File Type” to “Log”
  6. Select “File name:” to view each files’ current size and amount of free space
  7. Choose “Release unused space” to release all unused space or choose “Reorganize pages before releasing unused space” and set “Shrink file to:” size to desired size in MB.
  8. Click OK to shrink files

Tlog2

Update the Model Database (optional)

Use the above methods to change the model database (found in the “system databases” folder in Object Explorer) to simple recovery mode.

Tlog3

The model database is literally used as a template or “model” for each new database that is created.  So, if there are additional settings, tables, procedures, etc. that need to be created for every database within an instance of SQL Server, adding it to the model will cause it to be setup for each database.  Changing the model database to use the simple recovery model will cause each newly created or restored database to use the simple recovery model by default.

Kaynak: http://www.proactivespeaks.com

 

İp Adresiniz
3.236.126.101

1 Star2 Stars3 Stars4 Stars5 Stars (2 Kişi oy verdi, 5 üzerinden ortalama puan: 5,00. Bu yazıya oy vermek ister misiniz?)
Loading...
Tayfun KUŞAK

Tayfun KUŞAK

Genç yaşlarda bilgisayar ile tanıştı. Sektörde pek çok farklı pozisyonlarda ve farklı firmada görev aldı. Microsoft System Managment, Active Directory, Virtualization, Disaster Recovery, Mail Server, Security uzmanlık alanlarından sadece birkaç tanesi. Şu anda özel bir kuruluşta “Sistem Yöneticisi” olarak çalışmakta. MCP,MCSA,MCSE

Bir cevap yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir