Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 424
  • Last Modified:

SQL log file massive

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
daiwhyte
Asked:
daiwhyte
  • 8
  • 6
  • 2
  • +2
1 Solution
 
EmerexCommented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>>  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
 
59calCommented:
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
daiwhyteAuthor Commented:
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
 
daiwhyteAuthor Commented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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
 
59calCommented:
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
 
daiwhyteAuthor Commented:
Yes, I have full access to create maintenance plans.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
daiwhyteAuthor Commented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
daiwhyteAuthor Commented:
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
 
daiwhyteAuthor Commented:
PS - Sorry for taking soo long to come back to this thread, other commitments got the better of me.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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
 
daiwhyteAuthor Commented:
rrjegan17 - if I could have some screenshots, that would be a massive help.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
RichardYoungCommented:
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
 
daiwhyteAuthor Commented:
Thank you for your assistance.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 8
  • 6
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now