//
you're reading...
Database, IT-Center, SQL Server

Empty Transaction Log SQL Server 2005 & 2008

A. SQL SERVER 2005
Handle error Transaction Log is full :

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[empty_log]
AS
BEGIN
-- SHRINKFILE LOG FILE

DBCC SHRINKFILE(<logicalname_log>, 1)
BACKUP LOG <dbname> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<logicalname_log>_log, 1)
END

B. SQL SERVER 2008

Introduction
You know there is always an issue – the log file growing very fast and big.  If you have plenty of storage, then this might not be a problem for you.  Anyway, this is no exception in the latest version of SQL, we still have to do something to truncate and shrink these files.

Implementation
1)   
Let’s first check the log file size.
SELECT
–DB_NAME(database_id) AS DatabaseName,
–Physical_Name,
Name AS Logical_Name,
(size*8)/1024 SizeMB
FROM
sys.master_files
WHERE   DB_NAME(database_id) = ‘tempdb’
GO

Output

2)   Now truncate the log file.

USE tempdb;

GO

— Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE tempdb

SET RECOVERY SIMPLE WITH NO_WAIT;

GO

— Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE(tempdb_log, 1);  –file_name is the logical name of the file to be shrink

GO

— Reset the database recovery model.

ALTER DATABASE tempdb

SET RECOVERY FULL WITH NO_WAIT;

GO

3)   Let’s check the log file size.

SELECT

–DB_NAME(database_id) AS DatabaseName,

–Physical_Name,

Name AS Logical_Name,

(size*8)/1024 SizeMB

FROM

sys.master_files

WHERE

DB_NAME(database_id) = ‘tempdb’

GO

Output

____________________________________________________________________________

See Also :
*) http://www.simple-talk.com/sql/learn-sql-server/managing-transaction-logs-in-sql-server/
*) http://blog.sqlauthority.com/2010/05/03/sql-server-shrinkfile-and-truncate-log-file-in-sql-server-2008/


About berbagisolusi

Berbagi merupakan sebuah bentuk simbol keikhlasan untuk membantu dan menolong, sedangkan solusi adalah cara menyelesaikan masalah. Setiap manusia pasti mengalami masalah, tetapi kita tidak perlu mengalami masalah yang sama jika orang lain pernah mengalami dan kita tahu hal tersebut.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 9 other followers

December 2011
M T W T F S S
« Nov   Jan »
 1234
567891011
12131415161718
19202122232425
262728293031  

Archives

Web Statistic

Blog Stats

  • 149,580 hits
%d bloggers like this: