July 16, 2008 at 8:40 am
We have a SQL 2005 db and a developer testing an Access project application (2007) using datasets. Just recently when he's been testing more frequently, the transaction log is filling up daily. The log is plenty big (I have not shrunk the file)...I just reduced the % space used. I just did this yesterday morning and this morning it is full again. There are no open transactions either. I need to find the underlying cause.....so I was wondering if Access can cause issues like this to arise?
July 16, 2008 at 8:52 am
What recovery model is the database in? If you do not need to recover to point in time you should use simple mode which will remove committed transactions on checkpoint. Otherwise you should be backing up the tx log regularly so this will happen. The log will grow and fill if neither of these things are done.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 16, 2008 at 8:54 am
To find the cause of the log filling up, run
SELECT name, recovery_model_desc, log_reuse_wait_desc from sys.databases
The log_reuse_wait_desc will tell you what is preventing the space in the log file from been reused.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2008 at 8:58 am
I obviously need to read a lot more.
Was a similar query available pre-2005?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 16, 2008 at 9:03 am
The recovery mode is Full and I have a tran log backup running nightly so it will truncate the log. I would put it in Simple mode and will probably do this, to take care of this problem. However I'm trying to find out why this may be happening. This Access application will go into production soon and hits our production master database. I was just trying to find out the underlying cause of this so it doesn't happen in production.
July 16, 2008 at 9:04 am
oh and I ran that query and it says 'Nothing' under that column
July 16, 2008 at 9:04 am
Jack Corbett (7/16/2008)
I obviously need to read a lot more.Was a similar query available pre-2005?
No. In 2000 finding the cause of log filling was more trial and error. DBCC OPENTRAN helped, but not when the cause was lack of log backups
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2008 at 9:15 am
nicole.willis (7/16/2008)
The recovery mode is Full and I have a tran log backup running nightly so it will truncate the log. I would put it in Simple mode and will probably do this, to take care of this problem. However I'm trying to find out why this may be happening. This Access application will go into production soon and hits our production master database. I was just trying to find out the underlying cause of this so it doesn't happen in production.
If you are only running the transaction log backup nightly, in my opinion, you may as well be in simple mode and be doing a full backup nightly.
Since the query is returning "NOTHING" then currently there is space available in the log file that will be reused. You can also use the builtin Disk Usage report in SSMS to see if there is space available in the transaction log file and this report also shows when growth events are occurring.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 16, 2008 at 9:18 am
GilaMonster (7/16/2008)
Jack Corbett (7/16/2008)
I obviously need to read a lot more.Was a similar query available pre-2005?
No. In 2000 finding the cause of log filling was more trial and error. DBCC OPENTRAN helped, but not when the cause was lack of log backups
I did not think that there was anything like that in 2000. I was aware of DBCC OPENTRAN and had used it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply