Solved

SQL server 2005 backup failing

Posted on 2013-07-01
4
296 Views
Last Modified: 2013-11-06
I have a SQL Serer 2005 DB that is about 120GB in size.

I have the recovery mode set to simple because we don't need minute by minute recovery.

In the interest of full disclosure I do not do backups on a regular basis which I know is frowned upon. We have a disaster recovery plan that takes a server image nightly and snapshots of the server throughout the day so in the event of a server crash I can restore from those images.

When I do try to do a backup through the management studio the process seems to run fine. It gets to 100% complete then hangs. No completion notice pops up. The backup file size is 0. What is really troubling is the drive that the data resides on shows nothing. I have a 300GB hard drive that typically has 160GB of free space. When the backup locks that drive shows nothing. Then the entire server will eventually lock. Also if I check the server performance in task manager the memory is maxed out at 12GB.

HAs anyone ever seen this before? What can I do?

THanks for the help.
0
Comment
Question by:clifford_m71
  • 2
  • 2
4 Comments
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 500 total points
ID: 39289817
With regard to server memory maxing out, have you set a max limit for SQL to use? This would probably be the reason why all the memory on the server is being taken.

Try doing the backup from a query window and see if that produces the same result. The syntax is...

BACKUP DATABASE [DB NAME] TO DISK='C:\Backup.bak' WITH STATS=1

Open in new window

0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39293388
Do you require further assistance?
0
 

Author Comment

by:clifford_m71
ID: 39555947
EvilPostIt, Thank you for the suggestion. I apologize for taking soooo long to get back to this. I ran the backup from a query. The good news is the backup was created. The drive space did NOT fill up. The bad news is it still froze the server. Any additional suggestions?
0
 

Author Closing Comment

by:clifford_m71
ID: 39627409
Running the backup from a query window for some reason at least created a valid backup. The server still froze but I will look into possible hardware/software problems to resolve that. Thanks for the help.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question