Solved

SQL log file massive

Posted on 2010-08-24
18
409 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
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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…
In this article I will describe the Backup & Restore 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.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

806 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