Solved

Transaction Log Back Freezes Server

Posted on 2007-03-21
15
522 Views
Last Modified: 2008-02-16
Yesterday I made a discovery. I had a 9 MB database with a 6.7 GB Transaction Log. After searching here I did a backup on the one database shrank it and everything was fixed.

So to keep the log from growing out of control again I added a daily scheduled backup but i did it for all my databased and low and behold it locks the server up. I removed a few of the smaller databases and left just the higher priority ones but still nothing. To make matters even better the server manages to lock up before anything can be written to a log. Event Viewer stops 3 seconds before the schedule was supposed to start and the SQL Server logs don't show anything for a full 20 minutes before the scheduled log backup.

I spent the better part of the day yesterday attempting to solve this through trial and error but other than not running the backup I can't find a solution.
0
Comment
Question by:xstaxjke
  • 6
  • 5
  • 2
  • +1
15 Comments
 
LVL 10

Expert Comment

by:lahousden
ID: 18763734
Did you use the "New Maintenance Plan..." Wizard to schedule the backups or did you set them up "by hand"?
0
 

Author Comment

by:xstaxjke
ID: 18763885
I setup the plan by hand.
0
 
LVL 10

Expert Comment

by:lahousden
ID: 18764495
We do all our backups of this kind using the wizard (our backup requirements are pretty much "vanilla") and we have not experienced any difficulty.  If you are looking for options at this stage, maybe try setting it up through the wizard one time to see if there is any difference in the resulting performance...
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 18766669
http://www.experts-exchange.com/Database/Miscellaneous/Q_21939682.html#a17232961

You need to know your backup options. Read my comment several chunks down.

Once you get that down, I would suggest using the Mant Plan Wizrd mentioned by lahousden to set up your maint plans originally, then get more granular.

Not the Check Integrity bug in SQL2K.
0
 
LVL 7

Expert Comment

by:TRACEYMARY
ID: 18768071
If in full mode do log backup say every 15 minutes

in simple mode no need to do backup log as on checkpoint it clears this.
Agree use wizard.
0
 

Author Comment

by:xstaxjke
ID: 18768307
I have 2 databases that I'm concerned with. The first had a 6 GB transaction log and while the site is important the information is not critical so I've changed it to simple. The second database I've left as full. In 5 years of using this database I've never needed to do a rollback but I don't want to take the chance. I've removed my old maintenance plans and setup a new one using the wizard. I did not use the 'Check database integrity' option on the Integrity tab or the 'Verify the integrity of the backup upon completion' options on the Complete Backup tab. The first transaction log backup is set to run tonight at midnight. If it runs without problem I may change it to run every 15 minutes as was recommended. I'll post a follow up afterwards.
0
 

Author Comment

by:xstaxjke
ID: 18769317
I should have known the problem would end up being some odd detail. The lockup appears to have been caused by a space in a directory name. The information everyone provided was valuable in getting my backups setup properly but the actual issue of the server locking up ended up being something I checked on a whim. I'm not sure how to divy up points on this one.
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 38

Expert Comment

by:Jim P.
ID: 18770526
>> The lockup appears to have been caused by a space in a directory name.

Note: Even though M$ says spaces are okay -- don't use them.

>> I'm not sure how to divy up points on this one.

Iw would ask for a delete-refund in CS. Saving this one doesn't really add to the PAQ.

>> If in full mode do log backup say every 15 minutes

It all depends on your tolerance for data loss. If you work for a small insurance office, and only do 40 transactions a day, every 15 minutes is severe overkill.

If you are an online sales website selling 10 widgets per minute then that may be appropriate, along with other methods of uptime.

There is no one size fits all for backup and recovery options.
0
 

Author Comment

by:xstaxjke
ID: 18788254
I thought I was done with this but for some reason the transaction log still freezes my server. If I log on and manually run the job everything runs fine. It seems that the lockup happens right when the server starts the backup. I'm wondering if the process is somehow different when it runs in automatic.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 18792121
Are the SQL Services running under the LocalSystem account, or some type of domain acct? If domain acct, does it have permissions to all the resources needed (sqlmaint.exe, directory where data written....)?
0
 

Author Comment

by:xstaxjke
ID: 18793301
Everything appears to be under LocalSystem. I played a hunch and about 20 minutes before my last transaction log backup I rebooted the server. The log backup went through fine. Rebooting before a backup certainly saves time over opening a support ticket but it's not exactly how I want to go about processing backups. So in light of a reboot helping any ideas?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 18793521
What's the server -- cpu, memory, etc.....
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 19640541
delete-refund.
0
 

Accepted Solution

by:
xstaxjke earned 0 total points
ID: 20855450
Problem Solved Finally: This should have occurred to me sooner but the files on my server were fragmened far worse than I could have imagined. I was having multiple problems so I cleaned up some files, checked for updates, basically treated my server as I would my PC. I also went looking for a program that defrags on the fly. After 15 days the trial expired and I setup a batch file that runs a couple times a day to handle defragmenting. The server is running better across the board and solved the crash during log backups.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

910 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now