February 28, 2012 at 12:07 pm
Hi,
We have full/differential/log backups scheduled as below:
Full 7pm
Diff 1pm
log every 15 mins 12am 11:59pm
The backups happening fine from several months.
but all of the sudden, the differential backup of one of the databases got hung. It's running for ever...So I tried to kill the session. The rollback also running for ever and the subseuent Full backup of the same database also got hung..
So now, both Diff & Full backups are running for ever. As a last option, I restarted the sql service and all looks good.
But after two days, same issue occured again. There are no errors in errorlog. There is plenty of backup space available on disk.
This is happening with only one database. The rest of the databases's backups are normal. Please advice how we can invetigate this issue.
Environment: SQL Server 2008 R2 SP1.
Filestream enabled
Hosting Share point database.
Using SQL Server native backups.
Thanks
February 28, 2012 at 12:12 pm
When the backup is in hung state what you see in lastwaitype column for that spid.. also check the disk where taking the backup.
February 28, 2012 at 12:29 pm
last Wait Type is BACKUPTHREAD.
The backup disk fine. Because all other databases backups have NO issue except one database that got hung.
February 28, 2012 at 2:15 pm
Is this native SQL Server backup, or a third party backup?
Is it local, or to a remote drive or share?
February 29, 2012 at 12:44 pm
Is this native SQL Server backup, or a third party backup?
Is it local, or to a remote drive or share?
It is sql native backup.
Backups to a clustered drive.
February 29, 2012 at 3:18 pm
gmamata7 (2/29/2012)
Is this native SQL Server backup, or a third party backup?
Is it local, or to a remote drive or share?
It is sql native backup.
Backups to a clustered drive.
edit the job step and add a log file output for the step, we can then view this next time it fails. can you post details of the script you are using for diffs
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 1, 2012 at 10:43 am
edit the job step and add a log file output for the step, we can then view this next time it fails. can you post details of the script you are using for diffs
I already did that and the log is as below. It's hung at 90 % completion and some times at 70% completion...It just hung forever until I restart the sql service.
10 percent processed. [SQLSTATE 01000]
20 percent processed. [SQLSTATE 01000]
Processed 903336 pages for database 'Mydb2', file 'Mydb2' on file 1. [SQLSTATE 01000]
Processed 903744 pages for database 'Mydb2', file 'Mydb2_1' on file 1. [SQLSTATE 01000]
30 percent processed. [SQLSTATE 01000]
40 percent processed. [SQLSTATE 01000]
50 percent processed. [SQLSTATE 01000]
60 percent processed. [SQLSTATE 01000]
70 percent processed. [SQLSTATE 01000]
80 percent processed. [SQLSTATE 01000]
90 percent processed. [SQLSTATE 01000]
Thanks
March 1, 2012 at 10:56 am
Can you post the script
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 1, 2012 at 11:09 am
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[BackupDatabase]
@DBName VARCHAR(75),
@BackupPath VARCHAR(2000),
@BackupType VARCHAR(4) = 'FULL'
AS
BEGIN
DECLARE @BackupName VARCHAR(255)
DECLARE @Prefix VARCHAR(50)
DECLARE @backupSetId AS INT
-- Make sure database exists on server
IF NOT EXISTS (SELECT 1 FROM sys.sysdatabases WHERE name = @DBName)
BEGIN
SELECT 'Database name does not exist'
RETURN(1)
END
-- Make sure one one is trying to take tempdb backup
IF @DBName = 'tempdb'
BEGIN
SELECT 'tempdb cannot be backedup'
RETURN(1)
END
-- full, Differential and log backups are allowed, other wise do not do any thing.
IF @BackupType NOT IN ('FULL', 'LOG', 'DIFF')
BEGIN
PRINT 'Invalid type of Backup selected, only FULL and LOG backup is allowed'
RETURN(1)
END
-- If user did not give the back slash, add one.
IF RIGHT(@BackupPath,1) <> '\'
SET @BackupPath = @BackupPath + '\'
IF @BackupType = 'FULL'
BEGIN
SET @BackupName = @DBName + ' Full Backup'
SET @BackupPath = @BackupPath + @DBName +'_'+ CONVERT(VARCHAR(10),GETDATE(),110)+'.bak'
SELECT @BackupPath AS Full_BackupPath_of_The_Database
BACKUP DATABASE @DBName
TO DISK = @BackupPath
WITH NOFORMAT, NOINIT, NAME = @BackupName,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
SELECT @backupSetId = position FROM msdb..backupset WHERE database_name= @DBName
AND backup_set_id=(SELECT MAX(backup_set_id) FROM msdb..backupset
WHERE database_name = @DBName )
IF @backupSetId IS NULL
BEGIN
RAISERROR (N'Verify failed for database', 16, 1)
END
RESTORE VERIFYONLY
FROM DISK = @BackupPath
WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
END
IF @BackupType = 'DIFF'
BEGIN
SET @BackupName = @DBName + ' Differential Backup'
SELECT @Prefix = CONVERT(VARCHAR(50), GETDATE(),120)
SELECT @Prefix = REPLACE(@Prefix, '-', '')
SELECT @Prefix = REPLACE(@Prefix, ':', '')
SELECT @Prefix = REPLACE(@Prefix, ' ', '')
SET @BackupPath = @BackupPath + @DBName + '_' + @Prefix +'.bak'
SELECT @BackupPath AS Diff_Backup_Path
BACKUP DATABASE @DBName
TO DISK = @BackupPath
WITH DIFFERENTIAL ,NOFORMAT, INIT, NAME = @BackupName,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
SELECT @backupSetId = position FROM msdb..backupset WHERE database_name= @DBName
AND backup_set_id=(SELECT MAX(backup_set_id) FROM msdb..backupset
WHERE database_name = @DBName )
IF @backupSetId IS NULL
BEGIN
RAISERROR (N'Verify failed for database', 16, 1)
END
RESTORE VERIFYONLY
FROM DISK = @BackupPath
WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
END
IF @BackupType = 'LOG'
BEGIN
SELECT @Prefix = CONVERT(VARCHAR(50), GETDATE(),120)
SELECT @Prefix = REPLACE(@Prefix, '-', '')
SELECT @Prefix = REPLACE(@Prefix, ':', '')
SELECT @Prefix = REPLACE(@Prefix, ' ', '')
SET @BackupName = @DBName + ' Log Backup'
SET @BackupPath = @BackupPath + @DBName + '_Log' + @Prefix + '.trn'
SELECT @BackupPath AS Log_Backup_Path
BACKUP log @DBName
TO DISK = @BackupPath
WITH NOFORMAT, NOINIT, NAME = @BackupName,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
SELECT @backupSetId = position FROM msdb..backupset WHERE database_name= @DBName
AND backup_set_id=(SELECT MAX(backup_set_id) FROM msdb..backupset
WHERE database_name = @DBName )
IF @backupSetId IS NULL
BEGIN
RAISERROR (N'Verify failed for database', 16, 1)
END
RESTORE VERIFYONLY
FROM DISK = @BackupPath
WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
END
END
GO
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply