Microsoft Dynamics Forums Homepage

Forum Home Forum Home > Microsoft Dynamics GP (Great Plains) > GP - Installation and Administration
  New Posts New Posts RSS Feed - SQL log grows unexpectedly
  FAQ FAQ  Forum Search   Register Register  Login Login


SQL log grows unexpectedly

 Post Reply Post Reply
Author
Message
ilyar View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: September 10 2009
Status: Offline
Points: 4
Post Options Post Options   Thanks (0) Thanks(0)   Quote ilyar Quote  Post ReplyReply Direct Link To This Post Topic: SQL log grows unexpectedly
    Posted: September 10 2009 at 8:01am
I am running MS Dynamics GP v10 and MS Sql 2005 x64
Issue: SQL log becomes incredibly large and causes to fill up the whole disk
Has anyone ran into similar problems and what are some of the solutions?
 
Thanks,
Ilya
Back to Top
Sponsored Links


Back to Top
daoudm View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: February 04 2008
Location: Amman
Status: Offline
Points: 442
Post Options Post Options   Thanks (0) Thanks(0)   Quote daoudm Quote  Post ReplyReply Direct Link To This Post Posted: September 11 2009 at 11:41am
What is the recovery model you currently use for your databases and how many transactions do you have per business hour, besides do you have Analytical Accounting Installed?
 
Regards,
Mohammad R. Daoud [MVP]
+962 - 79 - 999 65 85
mohdaoud@gmail.com
http://mohdaoud.blogspot.com
Back to Top
ilyar View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: September 10 2009
Status: Offline
Points: 4
Post Options Post Options   Thanks (0) Thanks(0)   Quote ilyar Quote  Post ReplyReply Direct Link To This Post Posted: September 14 2009 at 6:47am
We are using the Full recovery model and Analystical Accounting is installed. I am not sure about how many transactions per business hour...
Back to Top
daoudm View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: February 04 2008
Location: Amman
Status: Offline
Points: 442
Post Options Post Options   Thanks (0) Thanks(0)   Quote daoudm Quote  Post ReplyReply Direct Link To This Post Posted: September 14 2009 at 7:05am
There are allot of reasons behind such case and I guess you will need to validate them one by one, start by checking the transactions in aaSubLedgerDist (AAG20001) table, is it normal or you have millions?
 
Regards,
Mohammad R. Daoud [MVP]
+962 - 79 - 999 65 85
mohdaoud@gmail.com
http://mohdaoud.blogspot.com
Back to Top
ilyar View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: September 10 2009
Status: Offline
Points: 4
Post Options Post Options   Thanks (0) Thanks(0)   Quote ilyar Quote  Post ReplyReply Direct Link To This Post Posted: September 14 2009 at 8:04am
I am sorry but i am not very familiar with this software..how would I check the transactions?
Back to Top
ilyar View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: September 10 2009
Status: Offline
Points: 4
Post Options Post Options   Thanks (0) Thanks(0)   Quote ilyar Quote  Post ReplyReply Direct Link To This Post Posted: September 14 2009 at 8:22am

actually i just checked and i dont have a AAG20001 table...

Back to Top
daoudm View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: February 04 2008
Location: Amman
Status: Offline
Points: 442
Post Options Post Options   Thanks (0) Thanks(0)   Quote daoudm Quote  Post ReplyReply Direct Link To This Post Posted: September 14 2009 at 10:01am
You need to check in your company database not in Dynamics.
Mohammad R. Daoud [MVP]
+962 - 79 - 999 65 85
mohdaoud@gmail.com
http://mohdaoud.blogspot.com
Back to Top
Rohtash Kapoor View Drop Down
MicrosoftDynamicsForums.com Moderator
MicrosoftDynamicsForums.com Moderator


Joined: July 06 2005
Location: United States
Status: Offline
Points: 655
Post Options Post Options   Thanks (0) Thanks(0)   Quote Rohtash Kapoor Quote  Post ReplyReply Direct Link To This Post Posted: September 15 2009 at 8:40am
Originally posted by ilyar ilyar wrote:

I am running MS Dynamics GP v10 and MS Sql 2005 x64
Issue: SQL log becomes incredibly large and causes to fill up the whole disk
Has anyone ran into similar problems and what are some of the solutions?
 
Thanks,
Ilya
 
You may want to read this:
 
How to stop the transaction log of a SQL Server database from growing unexpectedly
Back to Top
darthvader View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: July 21 2009
Location: Philippines
Status: Offline
Points: 9
Post Options Post Options   Thanks (0) Thanks(0)   Quote darthvader Quote  Post ReplyReply Direct Link To This Post Posted: September 24 2009 at 2:03am
you can try also to detached all the database and move it to another partitions of your harddisk if you have any. then after that you can attached the database again. it works for me.
jher_sc@hotmail.com
Back to Top
FAC View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: December 20 2009
Status: Offline
Points: 1
Post Options Post Options   Thanks (0) Thanks(0)   Quote FAC Quote  Post ReplyReply Direct Link To This Post Posted: December 20 2009 at 3:47am
Hi 
I have the same problem anytime the users try to make a transaction that involves AA it takes minutes to do it and sometimes GP hangs up.
I saw the AAG20001 and AAG20002 and i have 33 million records there
if i do this i got 33 million
select count(*) from AAG20001 where aaSubLedgerHdrId = 0

i think a record aaSubLedgerHdrId = 0 is wrong, isn't it?

please help me

Thanks a lot
Back to Top
jhunter77082 View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: August 15 2006
Location: United States
Status: Offline
Points: 237
Post Options Post Options   Thanks (0) Thanks(0)   Quote jhunter77082 Quote  Post ReplyReply Direct Link To This Post Posted: December 30 2009 at 2:29pm
Let me know if this continues to be an issue.  I have a script that will read what the current recovery model is, store it, change the recovery model to SIMPLE, shrink the db, and then change the recovery model back to it's original value.  I also have it as Store Proc that you can schedule a job to execute it on a regular basis.
 
Let me know if you want them.
John Hunter
MCSE, MCDBA
MBS Certified Master - SL Application - SL Installation and Configuration
Microsoft Certified Business Management Solutions Specialist - Application Dynamics SL, AX
Back to Top
jhunter77082 View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: August 15 2006
Location: United States
Status: Offline
Points: 237
Post Options Post Options   Thanks (0) Thanks(0)   Quote jhunter77082 Quote  Post ReplyReply Direct Link To This Post Posted: December 30 2009 at 2:29pm
By the way... I am also a MBS Certified Master in GP as well.
John Hunter
MCSE, MCDBA
MBS Certified Master - SL Application - SL Installation and Configuration
Microsoft Certified Business Management Solutions Specialist - Application Dynamics SL, AX
Back to Top
daoudm View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: February 04 2008
Location: Amman
Status: Offline
Points: 442
Post Options Post Options   Thanks (0) Thanks(0)   Quote daoudm Quote  Post ReplyReply Direct Link To This Post Posted: December 30 2009 at 2:46pm

There is a bug in the analytical accounting module; one of the stored procedures is going in infinite loop and keeps dropping records in this table, find its hotfix in the knowledgebase.

Regards,
Mohammad R. Daoud [MVP]
+962 - 79 - 999 65 85
mohdaoud@gmail.com
http://mohdaoud.blogspot.com
Back to Top
NeilG View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: January 08 2010
Status: Offline
Points: 6
Post Options Post Options   Thanks (0) Thanks(0)   Quote NeilG Quote  Post ReplyReply Direct Link To This Post Posted: January 08 2010 at 7:16am
From a DBA point of view I would never detach a database and move the files to sort this problem, if you database is current in Full recovery mode that means all transactions are being logged if that's was you require then you need to increase the number of time a transaction log backup is taken.

Otherwise if you don't then depend on you business deciding i'd change the recovery model to Simple this will then not log every transaction
Back to Top
carlssc View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: February 25 2010
Status: Offline
Points: 6
Post Options Post Options   Thanks (0) Thanks(0)   Quote carlssc Quote  Post ReplyReply Direct Link To This Post Posted: February 25 2010 at 1:13pm
Or, if you take a backup of your DB's, the logs will shink on their own.
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down


Copyright 2013 microsoftdynamicsforums.com. All rights reserved. MicrosoftDynamicsForums.com is an independent non-Microsoft website.
Email: contact AT microsoftdynamicsforums DOT com