Solved

SQL log file massive

Posted on 2010-08-24
18
406 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
 

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to set audit trial on sql server 4 44
SQl Agent job fails--SSIS package looses password 6 45
Pivot Query Problem 9 37
Global Address book 5 61
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
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, f…

910 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

22 Experts available now in Live!

Get 1:1 Help Now