Solved

Transaction Log Back Freezes Server

Posted on 2007-03-21
15
545 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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
 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

830 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