Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL log file massive

Posted on 2010-08-24
18
Medium Priority
?
420 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 2
  • +2
18 Comments
 

Expert Comment

by:Emerex
ID: 33511215
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
ID: 33511347
>>  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
ID: 33511404
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:daiwhyte
ID: 33511410
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
ID: 33511484
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
ID: 33511501
>> 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
ID: 33511513
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
ID: 33511589
Yes, I have full access to create maintenance plans.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33521706
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
 

Author Comment

by:daiwhyte
ID: 33585265
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
ID: 33589426
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
ID: 33670573
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
ID: 33670577
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
ID: 33690879
>> 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
ID: 33742546
rrjegan17 - if I could have some screenshots, that would be a massive help.
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 33753988
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
ID: 33806212
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
ID: 33936828
Thank you for your assistance.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

721 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