August 24, 2010 at 2:45 am
Hi All,
I am trying to back up 6 Databases with the total of 108GB combined, all the databases are in simple recovery. I have tried to backup the databases on an external hard drive of 435GB but for the past 4 nights it hasn't been successful. When I have a look in the Job Activity Monitor the backup keeps saying it is executing but never backs up anything, it is also preventing the Truncate Transaction log backup from running which runs every half hour. I'm using the following script to run the backup:
BACKUP DATABASE 'Databsename'
TO DISK = 'E:\'filename'.BAK'
August 24, 2010 at 2:48 am
If your databases are in Simple recovery mode, your transaction log backups will fail. What happens if you run your BACKUP DATABASE statement in a SSMS query window?
John
August 24, 2010 at 3:01 am
Thank you for the reply.
When I try to run the Truncate Log job it gives me the following error message:
Msg 3023, Level 16, State 3, Procedure 'stored procedure name', Line 5
Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
Msg 3013, Level 16, State 1, Procedure 'stored procedure name', Line 5
BACKUP LOG is terminating abnormally.
August 24, 2010 at 3:08 am
Yes, I would expect that. But you were complaining that your BACKUP DATABASE statement was unsuccessful. What happens when you run that?
John
August 24, 2010 at 3:15 am
I set the backup to run at 4am because there are no other backups that occur at that time except for the "Truncate transaction log" job. The job continually runs and when I look for in the Event viewer and Error logs, there are no error or warnings, so I end up having to stop the job.
Thank you.
August 24, 2010 at 3:18 am
Please copy the command from the job, paste it into a SSMS query window, run it, and tell me what happens.
John
August 24, 2010 at 3:30 am
All the Databases are live so I'm concerned backing up any of then during the day. I will set the job to run, just one of the databases (the main database) for tomorrow morning and I will let you know the result.
Thank you very much for your help.
August 24, 2010 at 4:22 am
When you run the job, does the backup file gets created on E drive? Also you may use this query to check the backup status.
SELECT A.NAME,B.TOTAL_ELAPSED_TIME/60000 AS [Running Time],
B.ESTIMATED_COMPLETION_TIME/60000 AS [Remaining],
B.PERCENT_COMPLETE as [%],(SELECT TEXT FROM sys.dm_exec_sql_text(B.SQL_HANDLE))AS COMMAND FROM
MASTER..SYSDATABASES A, sys.dm_exec_requests B
WHERE A.DBID=B.DATABASE_ID AND B.COMMAND LIKE '%BACKUP%'
order by percent_complete desc,B.TOTAL_ELAPSED_TIME/60000 desc
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
August 24, 2010 at 4:48 am
When I check drive E, it has one of the databases backup files but with the file size of 0, which obviously means that nothing has been backed up.
The script that you sent to check the Backup status throws an error; does it work for SQL 2000 Servers?
Thank you for your help.
August 24, 2010 at 5:23 am
That script won't work on SQL Server 2000. You did post on a SQL Server 2005 forum after all.
Run sp_who2, find if the BACKUP processes are being blocked, if so, by which process.
Do note that given the backup command you are using i.e.
BACKUP DATABASE 'Databasename' TO DISK = 'E:\'filename'.BAK'
you are appending backup sets to the file, so after 4 days of backing up the 4 databases totaling 108 GB, and assuming most of the database space is used, you will run out of space on that external drive. Also, do check if the external drive has been formatted using FAT32 or NTFS.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
August 24, 2010 at 7:21 am
The hard drive is formatted in NTFS and the only job that runs every half hour throughout the day is the "Truncate Transaction log" job but when I re-run the job again tomorrow, I will be able to see whether another process is blocking the job from running.
Thank you.
August 24, 2010 at 7:49 am
tt-615680 (8/24/2010)
the only job that runs every half hour throughout the day is the "Truncate Transaction log" job
Truncate or back up? If truncate, why?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2010 at 8:09 am
This is the stored procedure that runs the 'Truncate Transaction log'
ALTER PROCEDURE [dbo].['name of stored procedure']
AS
BACKUP LOG 'Databasename'
WITH
TRUNCATE_ONLY
August 24, 2010 at 8:20 am
Ray Mond (8/24/2010)
That script won't work on SQL Server 2000. You did post on a SQL Server 2005 forum after all.Run sp_who2, find if the BACKUP processes are being blocked, if so, by which process.
Do note that given the backup command you are using i.e.
BACKUP DATABASE 'Databasename' TO DISK = 'E:\'filename'.BAK'you are appending backup sets to the file, so after 4 days of backing up the 4 databases totaling 108 GB, and assuming most of the database space is used, you will run out of space on that external drive. Also, do check if the external drive has been formatted using FAT32 or NTFS.
The hard drive is formatted in NTFS and the only job that runs every half hour throughout the day is the "Truncate Transaction log" job but when I re-run the job again tomorrow, I will be able to see whether another process is blocking the job from running.
Thank you.
August 24, 2010 at 8:24 am
tt-615680 (8/24/2010)
BACKUP LOG 'Databasename'WITH
TRUNCATE_ONLY
Why?
If you need point-in-time recovery, then you need log backups. If you don't need point in time recovery, set the recovery model to simple and leave it alone.
Please read through this - Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply