Solved

Transaction Log Back Freezes Server

Posted on 2007-03-21
15
504 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

12 Experts available now in Live!

Get 1:1 Help Now