Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL server 2005 backup failing

Posted on 2013-07-01
4
Medium Priority
?
305 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 1500 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

There are some very powerful Dynamic 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 di…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Integration Management Part 2
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

782 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