Solved

SQL log file massive

Posted on 2010-08-24
18
403 Views
Last Modified: 2013-11-15
Hi,

Ive got a database which sits on our SQL2005 server which also runs Goldmine 7 CE. The database is 32gb in size whilst all other db's are tiny in comparison. Why is this database so big compared to others, the main customer database is only 3Gb!

The name of the file isUpdateGM_log.ldf

Is this a log file which for some reason is not clearing down? Ive checked the maintenance plan for the server and this database in question is part of the plan.
0
Comment
Question by:daiwhyte
  • 8
  • 6
  • 2
  • +2
18 Comments
 

Expert Comment

by:Emerex
Comment Utility
Check your backup jobs to make sure they are running.   If your database recovery model is set to full you need to make backups of your transaction logs to prevent the log file from growing out of control.   See the following...

http://support.microsoft.com/kb/873235
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
>>  Ive checked the maintenance plan for the server and this database in question is part of the plan.

Do you have a Transaction Log maintenance backup created for that particular database..
0
 
LVL 3

Expert Comment

by:59cal
Comment Utility
Usually for GoldMine you don't need the DB to be set to full recovery mode. I would run a manual backup of the transaction log. This will apply the changes to the db file. Then change the recovery mode to simple from full (right click on the db, options, set the recovery mode to simple). Once this is done, truncate the log file by running dbcc shrinkfile () in SQL.
0
 

Author Comment

by:daiwhyte
Comment Utility
I dont think so, Im not very knowledgeable with SQL2005 but Ive looked and I can see the databases are backed up daily but nothing in the maintenance plan regarding log files.
0
 

Author Comment

by:daiwhyte
Comment Utility
Just checked the properties of the db in question and in the autogrowth field is has the following.

By 10 percent, restricted growth to 2097152MB - I guess that not right!
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
>> Im not very knowledgeable with SQL2005 but Ive looked and I can see the databases are backed up daily but nothing in the maintenance plan regarding log files.

If you don't have transactional log backup maintenance plan defined, then you need to create one to keep your Log file sizes in control.
Do you have the rights to create Maintenance plan in your server..
0
 
LVL 3

Expert Comment

by:59cal
Comment Utility
Usually for GoldMine you don't need the DB to be set to full recovery mode. I would run a manual backup of the transaction log. This will apply the changes to the db file. Then change the recovery mode to simple from full (right click on the db, options, set the recovery mode to simple). Once this is done, truncate the log file by running dbcc shrinkfile () in SQL.
0
 

Author Comment

by:daiwhyte
Comment Utility
Yes, I have full access to create maintenance plans.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
Since you have access, can you create a Maintenance plan to take Transactional log backup every 15 or 30 minutes..
Once it is created and running, Shrink your Transactional log file to a lesser value after a Transactional log backup..
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:daiwhyte
Comment Utility
How do you do a backup of the log files? Is this something which is done within SQL Management Studio? Is it simply right click on the database in question and select backup (see screenshot)

Once Ive got a backup of it, I then change the recovery mode from FULL to SIMPLE then shrink the database (right click on the db in question and select SHRINK).

One final thing, can this process be carried out during working hours?
ee.jpg
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
Starting from 2005, Eventhough you can do it when Server is online, I would recommend you to do these set of changes during off-peak hours or during Maintenance window.
Kindly let me know whether you have figured out how to create Maintenance plans for transactional log backup, else I would let you know the steps involved.

Note: If you have Full backup defined, then the only change would be to set the Backup type from Full to Transactional log.
0
 

Author Comment

by:daiwhyte
Comment Utility
rrjegan17, are you saying the only change I need to make is  changing the backup type from full to simple and thats it - nothing else needs doing? Shrink maybe?
0
 

Author Comment

by:daiwhyte
Comment Utility
PS - Sorry for taking soo long to come back to this thread, other commitments got the better of me.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
>> only change I need to make is  changing the backup type from full to simple and thats it

Nope, just create a Maintenance plan which you are using for Full backup and the only change required would be to change Backup type from Full to Transactional Log.

Kindly let me know if you are able to figure out, else I would post some snaps to help you out.
0
 

Author Comment

by:daiwhyte
Comment Utility
rrjegan17 - if I could have some screenshots, that would be a massive help.
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
Comment Utility
Ok, Create a New Maintenance Plan and use Backup Database Task and configure it as mentioned in attached snapshot.

And do include a Maintenance cleanup task to delete older database backup files.
Log-Backup-Maintenance-Plan.jpg
0
 
LVL 1

Expert Comment

by:RichardYoung
Comment Utility
Theres also a bug in SQL which will sometimes kick and even after a backup it wont clear down the logs.   If thats the case then you need to setup a script to perform the job like this:

http://forums.prior-analytics.com/viewtopic.php?f=3&t=24

Hope it helps.
0
 

Author Closing Comment

by:daiwhyte
Comment Utility
Thank you for your assistance.
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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

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

17 Experts available now in Live!

Get 1:1 Help Now