November 11, 2010 at 2:20 am
Hi, I am using SQL 2005 Enterprise on a Windows 2003 x86 OS. The server has 4GB RAM. The database is 40GB and is used for virtual center, so there are a lot of transactions. I am seeing the log file grow over the space of a day to 50GB which I have limited it to so the disk is not filled. I have tried using a maintenance job to back it up and shrink it, but it seems that the transactions in the log have not been processed. The SQL service is only using 64MB of RAM and yet there is over 1.5GB free. I have configured SQL to use a minimum of 1GB yet it doesn't seem to work.
What things can I check or change to try and get the transaction log to be processed quicker so it does not grow so large?
November 11, 2010 at 2:25 am
shutts318ci (11/11/2010)
Hi, I am using SQL 2005 Enterprise on a Windows 2003 x86 OS. The server has 4GB RAM. The database is 40GB and is used for virtual center, so there are a lot of transactions. I am seeing the log file grow over the space of a day to 50GB which I have limited it to so the disk is not filled. I have tried using a maintenance job to back it up and shrink it, but it seems that the transactions in the log have not been processed. The SQL service is only using 64MB of RAM and yet there is over 1.5GB free. I have configured SQL to use a minimum of 1GB yet it doesn't seem to work.What things can I check or change to try and get the transaction log to be processed quicker so it does not grow so large?
in what recovery model is you Database? for minimal logging, set it to SIMPLE.
You could always run a truncate to release the allocated space in the log for next transactions.. <-NOTE: it's an option, not a recommendation. (but its way better than shrinking the whole time, stop doing that 🙂 )
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
November 11, 2010 at 2:33 am
Please read through this - Managing Transaction Logs[/url]
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
November 11, 2010 at 2:39 am
I am in Full, it may be better setting it to simple then, see what happens!
November 11, 2010 at 2:53 am
Did you read the article Gail suggested?
If the log file is filling fast, you need to increase the frequency of your log backups or let it increase to a size which is acceptable. Log file cannot be shrunk the way data files are.
Taking log backup is the only way to ensure SQL Server reuses the space in the log file when your database is in full recovery mode.
Changing it to simple recovery mode may be decided by management depending on the duration for which they can afford to lose data in case of disaster.
November 11, 2010 at 3:21 am
Hi, I did read the article yes but unfortunately it doesn't help. When I was in "full" I got the error "full backup does not exist" when I tried to backup the log even though a full backup did exist.
I have changed the mode to "simple" and managed to shrink the log, but now it is growing at an ever faster rate, it's 17gb after 20 mins. I am running a backup now to see if that reduces the size again. If this works, would you normally expect the log to grow so large and have to do a full backup every 30 mins or less?
//////////////////
Backup completed only unlike last time, this time I could not shrink the log file 🙁 30GB and growing :pinch:
November 11, 2010 at 3:34 am
shutts318ci (11/11/2010)
Hi, I did read the article yes but unfortunately it doesn't help. When I was in "full" I got the error "full backup does not exist" when I tried to backup the log even though a full backup did exist.
Looks like you killed the backup chain. If considering full you better start again with a new full backup, and then regulare log backup afterwards
I have changed the mode to "simple" and managed to shrink the log, but now it is growing at an ever faster rate, it's 17gb after 20 mins. I am running a backup now to see if that reduces the size again. If this works, would you normally expect the log to grow so large and have to do a full backup every 30 mins or less?
//////////////////
Backup completed only unlike last time, this time I could not shrink the log file 🙁 30GB and growing :pinch:
On simple you cannot backup the log. and you sure its on simple? In that case you got 1 big transaction going on there
November 11, 2010 at 3:35 am
Since ur database is in simple recovery mode, i guess you must be taking full backup. This has no effect on the log file size.
In simple recovery mode, log is reused automatically.
Check to see if there is some bulk transaction happening?
something like bulk insert/reindexing etc.
November 11, 2010 at 3:37 am
I've set it back to full, running a full backup again. Then will try "BACKUP LOG dbname" is that the correct format? WITH TRUNCATE_ONLY no longer works as it's deprecated.
November 11, 2010 at 3:42 am
shutts318ci (11/11/2010)
I've set it back to full, running a full backup again. Then will try "BACKUP LOG dbname" is that the correct format? WITH TRUNCATE_ONLY no longer works as it's deprecated.
Please, please read over that article again.
You need to first take a full backup (after the switch to full recovery), then you can start taking log backup.
The truncate_only option is NOT a log backup. It's the equivalent to simple recovery, no point in time recovery of the database is possible.
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
November 11, 2010 at 3:45 am
shutts318ci (11/11/2010)
I have changed the mode to "simple" and managed to shrink the log, but now it is growing at an ever faster rate, it's 17gb after 20 mins. I am running a backup now to see if that reduces the size again. If this works, would you normally expect the log to grow so large and have to do a full backup every 30 mins or less?
Full backups have no effect on the log. What's the reason for the log not been reused? (Check log_reuse_wait_desc in sys.databases for this DB)
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
November 11, 2010 at 3:50 am
Thank you, but I have read the article and have done what both it and you suggest but it does not work.
I returned to full, ran a full backup, ran a backup of the log file, but still the log file is the same size, 50GB now.
November 11, 2010 at 3:55 am
It did exactly as it was supposed to. Log backups do not shrink the file. Never have.
If the log has grown excessively large, do a once off shrink. By excessively large I mean far larger than the log needs to be for normal operation of the database. You can only tell whether a log is excessively large after monitoring it for a period of time.
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
November 11, 2010 at 3:58 am
Ah ok, I misunderstood then. I will give the log some more room to grow, but considering the db is only 40GB, this seems large. I could split the log onto another drive if you think this is necessary?
PS, the shrink did not work as the whole log is being used. Also, the log_reuse_wait_desc is ACTIVE_TRANSACTION
Apologies if I'm causing frustration here!
November 11, 2010 at 4:04 am
That means you have a long-running transaction open that's keeping the log large. You need to identify and fix that and whatever caused it before you decide what to do about the log.
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
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply