Transaction Log Back Freezes Server

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.
xstaxjkeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lahousdenCommented:
Did you use the "New Maintenance Plan..." Wizard to schedule the backups or did you set them up "by hand"?
0
xstaxjkeAuthor Commented:
I setup the plan by hand.
0
lahousdenCommented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Jim P.Commented:
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
TRACEYMARYCommented:
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
xstaxjkeAuthor Commented:
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
xstaxjkeAuthor Commented:
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
Jim P.Commented:
>> 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
xstaxjkeAuthor Commented:
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
Jim P.Commented:
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
xstaxjkeAuthor Commented:
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
Jim P.Commented:
What's the server -- cpu, memory, etc.....
0
Jim P.Commented:
delete-refund.
0
xstaxjkeAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.