March 7, 2007 at 12:20 pm
Hi
I have scheduled database back up once a week and the transaction log backup every four hours. But for no apparent reason the logfile backup did not fire for few days and the log file grew to 20GB and reached the upper limit i had put.
I am at a loss to know why it is happening. Also, is there a way to find out if the backup job scheduled using Maintenance Plan failed to run (other than checking manually)?
I re-scheduled the transaction log to run once an hour (it ran twice) then re-scheduled to run every 4 hours; Now it has shrunk the Trn.log to 128MB; Is there a way to find out what is causing this type of occurance?
Any inputs would be appreciated.
Thanks
March 8, 2007 at 2:34 am
Hi
Are you scheduling the job from Sql Server Agent, you could check the job history there, or run this query from Query Analyzer to find the problem....
USE MSDB
select j.name as 'Name', h.run_date as 'Date', h.step_name as 'Step', h.step_id as 'No',h.message
from msdb.dbo.sysjobhistory h
join msdb.dbo.sysjobs_view j on h.job_id = j.job_id
where
message like '%fail%' -- LOOK FOR FAILED JOBS
and name like 'YOUR JOB NAME' -- PUT YOUR JOB NAME HERE
and convert(datetime, -- WHERE THE JOB RAN IN THE LAST 2 DAY S
substring
(convert(varchar,run_date),1,4)+'-'+substring(convert(varchar,run_date),5,2)+'-'+substring(convert(varchar,run_date),7,2))
>= dateadd(dd,-2,getdate())
order by run_date desc
March 8, 2007 at 1:49 pm
Hi there,
Just a quick thought as I have had a similar occurance on scheduled jobs in general.
Althought the scheduled job is enabled, when you click on the schedule tab, is the schedule enabled?
Also, I'm assuming the job history was blank for the job? Any errors? Enough disk space on the backup drive?
March 8, 2007 at 2:19 pm
If the job was scheduled and enabled, go the schedule tab of the job and check what day it was scheduled to start.
-SQLBill
March 8, 2007 at 2:21 pm
Also, when you right click on the job and select View Job History, the history window will have a check box for View Job Steps. Check that. Then you can see the actual step that failed. Also, look in the SQL Server Error Logs.
-SQLBill
March 12, 2007 at 10:56 am
Here you go. Put this in your pipe and smoke it (in your master db and run it, that is).
CREATE Procedure sp_trx_log_shrink (@MaxMinutes INT,
@NewSize INT )
/*=========================================================================
Title: Shrink SQL Server Transaction Log Stored procedure
Script sp_trx_log_shrink.sql
Purpose: system proc based on
INF: How to Shrink the SQL Server 7.0 Transaction Log
Force shrink transaction log of current database to
specific size.
Params: @MaxMinutes = Max number minutes to run before stoppint
(recommend 2 at least)
@NewSize = New size in MBs of the log file after shrinking
(recommend at least 10 MB in most DBs)
Create/Update History:
10/31/2005 9:38:24 PM - GMilner: Pre-delete DummyTrans table if exists.
3/9/2005 3:33:44 PM - GMilner: Converted to procedure.
Notes:
Assumes only 2 physical database files and that _Data file
is file id 1 in sysfiles table and that log file is file id 2.
Original Source:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q256650
Microsoft Knowledge Base Article - 256650 ;
=========================================================================*/
AS
SET NOCOUNT ON
DECLARE @err int
DECLARE @LogicalFileName sysname
--DECLARE @SSQL as VARCHAR(255)
DECLARE @DBN as nVarchar(50)
-- Setup / initialize
DECLARE @OriginalSize int
set @DBN = (select db_name())
PRINT 'Database: ' + @DBN
SET @LogicalFileName = (SELECT FILE_NAME (2))
PRINT 'Log logical filename: ' + @LogicalFileName
PRINT ''
EXEC sp_helpdb @DBN
SELECT @OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
IF OBJECT_ID('DummyTrans') IS NOT NULL
DROP TABLE DummyTrans
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles
WHERE name = @LogicalFileName) -- the log has not shrunk
-- IF thhe value passed in for new size is smaller than the current size...
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
-- Because it is a char field it inserts 8000 bytes...
INSERT DummyTrans VALUES ('Fill Log')
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END -- update
EXEC (@TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
IF @err 0
BEGIN
RETURN(@err)
PRINT '*** SHRINK FAILED!!! ***'
END
IF @err = 0
BEGIN
PRINT '*** Perform a full database backup ***'
END
G. Milner
March 28, 2007 at 6:38 am
Are you running replication on this database? If so, be sure your log reader is running. The transaction log will not empty unless the log reader has marked the transaction.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply