Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Backup Expand / Collapse
Author
Message
Posted Tuesday, August 24, 2010 2:45 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:17 AM
Points: 247, Visits: 910
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'
Post #973918
Posted Tuesday, August 24, 2010 2:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:47 AM
Points: 5,233, Visits: 9,476
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
Post #973922
Posted Tuesday, August 24, 2010 3:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:17 AM
Points: 247, Visits: 910
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.

Post #973928
Posted Tuesday, August 24, 2010 3:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:47 AM
Points: 5,233, Visits: 9,476
Yes, I would expect that. But you were complaining that your BACKUP DATABASE statement was unsuccessful. What happens when you run that?

John
Post #973931
Posted Tuesday, August 24, 2010 3:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:17 AM
Points: 247, Visits: 910
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.
Post #973935
Posted Tuesday, August 24, 2010 3:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:47 AM
Points: 5,233, Visits: 9,476
Please copy the command from the job, paste it into a SSMS query window, run it, and tell me what happens.

John
Post #973937
Posted Tuesday, August 24, 2010 3:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:17 AM
Points: 247, Visits: 910
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.
Post #973946
Posted Tuesday, August 24, 2010 4:22 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 28, 2014 11:14 AM
Points: 1,618, Visits: 20,979
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
Post #973989
Posted Tuesday, August 24, 2010 4:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:17 AM
Points: 247, Visits: 910
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.
Post #973999
Posted Tuesday, August 24, 2010 5:23 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, June 21, 2014 2:00 AM
Points: 343, Visits: 1,517
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.


Ray Mond
Yohz Software
Providing SQL Server database tools for 9 years and counting.
http://www.yohz.com
Post #974025
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse