Shrink Transaction Logs on a Weekly basis

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Shrink Transaction Logs on a Weekly basis

Author

Topic

VinayBhargav
Starting Member

2 Posts

Posted - 2012-08-01 : 18:31:21

Hello All,

I have a task at hand to shrink transaction logs on a weekly basis. I have created the below pasted stored procedure to achieve it. The logic seems ok to me, but let me know if I am making mistake anywhere.

The error i am facing is

Executed as user: NT AUTHORITY\SYSTEM. DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) Could not locate file 'PRC_Data_Warehouse_log' for database 'tempdb'. [SQLSTATE 42000] (Error 8985). The step failed.

I need to pass Database name dynamically to achieve this task. Please provide your valuable suggestions.

USE [master]
GO
/****** Object: StoredProcedure [dbo].[Shrink_Transactionlog] Script Date: 08/01/2012 15:24:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Shrink_Transactionlog]
AS
BEGIN

DECLARE @runid int,
@rowcount int,
@DBName varchar(1000),
@LogName varchar(1000),
@LogSize float,
@PrcntUsed float

SET @runid = 5
SET @rowcount = 0

CREATE TABLE #logspace (
DBName varchar( 100),
LogSize float,
PrcntUsed float,
status int,
CountCheck INT IDENTITY(1,1)
)
INSERT INTO #logspace
EXEC ('DBCC sqlperf(logspace)')
/*
process the data
*/
--ALTER TABLE #logspace ADD CountCheck INT IDENTITY(1,1)

SET @rowcount = (select COUNT(*) from #logspace)

While (@runid <= @rowcount)

Begin

SET @DBName = (SELECT DBName from #logspace
WHERE #logspace.CountCheck = @runid)

SET @LogName = (Select F.NAME
FROM MASTER.SYS.MASTER_FILES F
INNER JOIN MASTER.SYS.DATABASES DB
ON DB.database_id = F.database_id
WHERE F.file_id = 2
AND DB.name = @DBName)

SET @LogSize = (select LogSize from #logspace where DBName = @DBName)

SET @PrcntUsed = (select PrcntUsed from #logspace where DBName = @DBName)

IF @LogName is not NULL
BEGIN
IF (@LogSize > 100.00)
Begin
DBCC SHRINKFILE (@LogName, 10) WITH NO_INFOMSGS
SET @runid = @runid + 1
End
ELSE
IF (@PrcntUsed > 50.00)
Begin
DBCC SHRINKFILE (@LogName, 10) WITH NO_INFOMSGS
SET @runid = @runid + 1
End
Else
SET @runid = @runid + 1
END
ELSE
SET @runid = @runid + 1

END
/*
Cleanup - drop the temp table
*/

drop table #logspace

END

VB

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-08-01 : 18:51:19

You should not be shrinking the transaction logs on a scheduled basis!

Here are some articles:
http://www.sqlskills.com/blogs/paul/post/why-you-should-not-shrink-your-data-files.aspx
http://www.sqlservercentral.com/articles/Administration/64582/

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

VinayBhargav
Starting Member

2 Posts

Posted - 2012-08-01 : 19:02:40

I have already suggested the same, but I have been asked to do it. Now I have to complete this task.

VB

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-08-02 : 01:39:04

Analyse firstly , why the log files are growing to a certain size. If the transaction log files are growing to a certain size, then keep the size.
To keep the transaction log files smaller , commit some regular BACKUP LOGS

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Krishna_DBA
Starting Member

4 Posts

Posted - 2012-08-02 : 10:37:17

As tkizer and Jack suggested, you should not be shrinking the logs. However if you have to do it, then get the DBCC command to a variable and execute it as below.

set @sql = 'use ' + @DBName ';DBCC SHRINKFILE (' + @LogName + ', 10) WITH NO_INFOMSGS'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources