| Author |
 Topic  |
|
|
VinayBhargav
Starting Member
USA
2 Posts |
 Posted - 08/01/2012 : 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
USA
35007 Posts |
|
|
VinayBhargav
Starting Member
USA
2 Posts |
Posted - 08/01/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
1769 Posts |
Posted - 08/02/2012 : 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 - 08/02/2012 : 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'
|
|
|
| |
Topic |
|
Shrink Transaction Logs on a Weekly basis
