Microsoft Dynamics GP (Archived)

SQL log grows unexpectedly

(0) Share

Report

Posted on by ilya r

I am running MS Dynamics GP v10 and MS Sql 2005 x64

Issue: Our SQL GP database 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

*This post is locked for comments

I have the same question (0)

All responses (19)

Answers (0)

Richard Wheeler 75,848 Moderator on at

Like (0)

Report

RE: SQL log grows unexpectedly

Extremely enlightening. Thank you!

Was this reply helpful? Yes No
Suggested answer

Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report

RE: SQL log grows unexpectedly

I came across this SQL Magazine article from Greg Low, which explains what could cause sometimes an 'unexpected Log file growth' in a SQL database...

Turns out that long-running queries could be the culprit...

Read on here.

As always, the devil is in the detail... in this case it was a missing Index that was causing a delete query to run forever on a larger dataset.

Was this reply helpful? Yes No
Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report

RE: SQL log grows unexpectedly

Leslie,

We monitor all our servers (not only SQL) disk space with a small script in PS that runs off the DC and sends out every 4 hrs a disk space report for servers where the free space falls below the 10% total size of the disk...

Before that, we received in the mail every 4hrs a full report of all the disk space status, but with servers in the hundreds mark, nobody was looking at the reports anymore, thus missing critical situations.. With the new report, only the ones that may potentially run soon into trouble are listed, still time to act upon it :-).

On SQL servers, you know which drives hold your MDF & LDF files... so those are the ones to keep an eye on.

Was this reply helpful? Yes No
L Vail 65,271 on at

Like (1)

Report

RE: SQL log grows unexpectedly

Good point Victoria, changing by a specific size would definitely be more predictable. However, if you don't have anyone monitoring the database size (as most of my clients do not), you could get in trouble. Still, your method is definitely predictable making storage space possible to manage. I should consider this for those clients that have a full time IT staff. Or perhaps sell a new service to monitor the database size. :)

Kind regards,

Leslie

Was this reply helpful? Yes No
Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report

RE: SQL log grows unexpectedly

This short query provides a list of all the LDF files with their current growth settings and physical size & location :

SELECT name ,
       physical_Name ,
       state_desc ,
       size ,
       max_size ,
       growth ,
       is_percent_growth
FROM sys.master_files
WHERE file_id = 2;

Makes it easy to re-adjust the settings if required.

Edit: the size field is not representative of the actual physical size... Use sp_helpfile for that purpose.

Was this reply helpful? Yes No
Victoria Yudin 22,768 on at

Like (0)

Report

RE: SQL log grows unexpectedly

I personally always change the autogrowth for both the files to be in MB rather than %. That way it's much more predictable.

Was this reply helpful? Yes No
Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report

RE: SQL log grows unexpectedly

This is true and I think it set by default to 25% when GP allocates the Company DB's... not sure that SQL does that... or maybe it does :-).

Past a certain DB file size, I'd too suggest to reduce this to a merely 5 or 10%, rather then 25% (that's 25Gb in one shot at a 100Gb dB!).. I'm not that rich in disk real estate :-).

Was this reply helpful? Yes No
L Vail 65,271 on at

Like (0)

Report

RE: SQL log grows unexpectedly

Hi Beat & Victoria,

Great Links from you both, thank you. I'm way more likely to shrink the log file than the db file. What I watch too is what percentage I've got the db to 'auto-grow'. As the db gets bigger, the auto grow percentage might be set too high. If you've got a 75 or 100 gig database, you probably don't want to auto grow it by 25%.

Kind regards,

Leslie

Was this reply helpful? Yes No
Suggested answer

Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report

RE: SQL log grows unexpectedly

Hi Victoria & Leslie,

I'm aware of the bad practice of shrinking SQL databases... And I fully agree with Brent (whom blog I've been following for a couple of years) that on normal operations you should never have to deal with SQL DB disk space issues... if you operate under normal conditions from a perspective of a data warehouse and DBA. But as most of us, I've to deal with eternal disk shortage on our IT infrastructure side... So I need to carefully manage (and watch) the disk space on my servers...

Paul Randal explains it well in his post here (www.sqlskills.com/.../why-you-should-not-shrink-your-data-files) and the issue it causes... That's also why my scheduled shrink operation is followed by a 'rebuild index' and 'update stats' task...

When I've to deal with a log file that has grown too large (usually only on my test system, as I don't do transaction log backups), I just detach the DB and re-attach it after having deleted the LDF file.. SQL automatically rebuilds a new LDF file with a default size of 512 or 1024Kb.. Again, not something I'd do on a production DB..

Even with regular TRX Log backups and daily full backups, your LDF can grow over time and become very large, and that's when you have to shrink just that file, not the entire database. Read on this excellent blog post about all the LDF concerns : www.sqlskills.com/.../8-steps-to-better-transaction-log-throughput

Looking forward to see you in Reno at the GPUG Summit.

Was this reply helpful? Yes No
Victoria Yudin 22,768 on at

Like (0)

Report

RE: SQL log grows unexpectedly

Hi Leslie and Beat,

My understanding is that shrinking SQL databases can negatively impact performance and should not be something you do regularly.

Here is a great blog post with a number of helpful links (and some more in the comments) as to why shrinking databases is bad: www.brentozar.com/.../stop-shrinking-your-database-files-seriously-now

While it was written a number of years ago, to my knowledge nothing has changed in this regard.

Was this reply helpful? Yes No