Solved

Transaction Log Back Freezes Server

Posted on 2007-03-21
15
534 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

785 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