April 7, 2010 at 11:00 am
Hello, I have backups created using the database maintanence tool. They have been fine for years, now as of March 30, 2010 they are not completing, they just keep running. Before I discovered it, one backup had run for 36 hours.
To help find the problem I created this script and ran it.
BACKUP DATABASE [Database Name] (Name changed to protect my job)
TO DISK = 'e:\Location\Database_Name_db_201004071230.BAK'
WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
This has now been running for 30 minutes, way too long. The database is 11 gb is size.
As additional information, I have backups for other databases and they are running fine. Also, they are much smaller databases.
Can some one please help me fix this problem. I need a backup of this database.
April 7, 2010 at 11:21 am
Have you checked the SQL SERVER Logs?
perhaps you are hitting a file size limit? or a limit on the number of files?
Is it a DTS task that is waiting on some other task to finish?
How about the transaction log backups? Are they running OK?
April 7, 2010 at 12:11 pm
I did not mention this is on SQL 2000 with the latest service pack.
I have checked the SQL Error logs and there is no entry for a failed backup or failure of any kind.
Upon further investigating I have found that the back does not start.
To test this I tried to backup to a different device from enterprise manager and the backup never started.The screen just showed the progress bar with no progress. After 30 minutes I cancelled it.
You bring up a good point about file size.
What is the max file size for a mdf or ndf file?
Thanks for any and all help.
Gary
April 7, 2010 at 5:14 pm
I solved the symptom so I thought I would report back in case anyone else encountered the same issue.
A database backup failed, for reason to be determined, and the backup process appeared to be hung up.
When a new backup process tried to execute it could not start because it was being blocked by the initial process.
I tried to kill the process but that did not work.
So a server restart seemed to be the only answer
After the restart, all the hung processes were cleared and the backups are now working again.
Thanks to those who replied.
Gary
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply