November 9, 2009 at 3:41 am
Hi there,
I'm a reasonably new member to SSC but it has fast become my must read website of a morning. I especially like the QOTD although it does show me that I need to improve. Anyways on to the point.
I am a DB Developer who is also dabbling in DBA for one of our clients. The databases here have been largely ignored and I have slowly been sorting them out alongside my other work.
We have a couple of databases but I will concerntrate on the ASPState database that is currently being used for one of our online applications.
So here goes.
We have an ASPState DB on SQL2005 which is currently 44671.69MB large. The data file is 110MB and the log file is 44563MB! It is on Full recovery model and backups are done nightly.
The log file is 99.6% used and 0.4% unused according to the Disk usage report. I have performed a couple of transaction log backups in the hope that it would truncate the major part of the log and I would then be able to shrink the log file down to a more acceptable size say 5Gb. However the Log backups work but they hardly reduce the used part of the log file at all.
I don't know the full history of the server but I have done a DBCC CheckDB and there are no errors or inconsistancies. I have done a DBCC CheckAlloc and there are no errors or inconsistancies (although I must admit I didn't fully understand the output of this command). I have also done a DBCC OpenTran and there are no open transactions blocking the truncation of the log file.
Now I can think of ways round this problem but I don't understand it which is infuriating me. Does anyone have any ideas or suggestions as to what is going on. It would be most appreciated.
November 9, 2009 at 3:46 am
I forgot to add that as this problem was reported to me I had just noticed that a brilliant article was written on SSC http://www.sqlservercentral.com/articles/Design+and+Theory/63350/ by James Rea. This helped me to understand the problem a bit better and also sent me off on a web search of many helpful areas. However I still haven't managed to make any headway.
Just wanted to mention it
Cheers
Stuart
November 9, 2009 at 4:00 am
It is on Full recovery model and backups are done nightly.
What sort of backups... database or transaction log?
When you backed up your transaction log, how big was the backup file?
Is that database involved in any form of replication?
Edit. Just spotted that the OP mentioned he tried a couple of txn log backups to no avail.
November 9, 2009 at 4:14 am
The log_reuse_wait_desc column of sys.databases will tell you what is holding up internal log truncation. See Factors That Can Delay Log Truncation for details. As Ian says, if the reason is 'REPLICATION', we'll need to give you some specific advice...:-)
You may also find these links useful:
Checkpoints and the Active Part of the Log
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 9, 2009 at 4:22 am
I checked the sys.databases log_reuse_wait_desc column and its set to NOTHING.
The database is not using replication, log shipping or mirroring.
Just isn't making sense to me.
The size of the transaction log backups are as follows
First one I did was 1.2MB
The second was 952K.
I did overwrite the first transaction log instead of doing it to a new log backup file.
Would this make a difference??
Might try that quick now...
November 9, 2009 at 4:23 am
BTW thanks for your posts guys.
Forgot my manners
Stuart
November 9, 2009 at 5:12 am
No worries. 'NOTHING' is good - be double sure it was listed as that for the database in question thoiugh. Under some circumstances, two log backups are required to allow the log to internally trim. Take the second log backup as you suggest, then run:
USE {db name}
CHECKPOINT;
DBCC SQLPERF(LOGSPACE);
I'd like to see the figures produced for the database concerned please.
Thank you 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 9, 2009 at 7:59 am
Right,
I've had a success but Im not entirely sure why.
As I mentioned in the previous posts the first two transaction log backups were very small in size.
I went back onto the server and performed another transaction log backup to a new file (don't think this was the difference) but the backup was 37MB.
So for fun, I did yet another Transaction log backup and this one was 150MB.
When I went back and ran the report again 99.6% of the log file was unused!!!
Great I thought, but the question that then comes to mind is why did I need to do 4 transaction log backups before the space was freed up.
After that I truncated and shrunk the log file and then the database and got the file down to the size I wanted originally.
So I got to where I wanted but the question remains. I have seen it posted on this site and in the MSDN documentation that on occasion two transaction log backups may need to be taken but I've never seen 4 before.
Many thanks to Paul and Ian for their quick response.
I also learned about the CHECKPOINT statement which I hadn't heard of before.
Very useful
Ta guys.
I shall go to work on the other DB now armed with my new knowledge. That DB does have replication enabled though and in sys.databases the log_wait_reuse_desc does say REPLICATION.
I think this page has helped me though so maybe it will help others too.
November 9, 2009 at 9:01 am
are the log backups set for no truncate? i don't do log backups in my environment, but I think there is a setting where it will back it up and still keep the data in there
if you don't need log backups you can just set the database to SImple Recovery Model and run backup_log with no log once a day or so. This is what i do and even with the simple model it tends to fill up sometimes for no apparent reason.
another thing you can do is run "backup_log <database_name> with no log", then run a full backup and that should do it
November 10, 2009 at 7:51 am
Please take a read through this: http://www.sqlservercentral.com/articles/64582/
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 10, 2009 at 8:42 am
Hi Everyone,
Figured i would ask the question on this topic.
I have a database set for Full Recovery, which I run Snapshot Replication on for DW purposes once per day. (The log_reuse_wait field = 6).
On this database I run a log backup every 15 minutes throughout the day and a full backup each night.
Here is my log backup script. The backup script is table driven, hence the parameters.
Backup Log @iDb_Name
To Disk = @iBackUpPath
With
RetainDays = @iRetainDays,
NoFormat,
NoInit,
Name = @BackUpFileName,
Skip,
Stats = 10
However, my log sizes are very large and the log usage is always 99%.
Any ideas on what I can do to reduce the log size?
Thanks much.
-Dan
November 10, 2009 at 1:39 pm
dan-404057 (11/10/2009)
(The log_reuse_wait field = 6).
What's log_reuse_wait_desc? I don't offhand know the codes.
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 10, 2009 at 1:44 pm
GilaMonster (11/10/2009)
dan-404057 (11/10/2009)
(The log_reuse_wait field = 6).What's log_reuse_wait_desc? I don't offhand know the codes.
From BOL:
6 = Replication
November 10, 2009 at 3:41 pm
dan-404057 (11/10/2009)
I have a database set for Full Recovery, which I run Snapshot Replication on for DW purposes once per day. (The log_reuse_wait field = 6).
Any transactional replication?
What does DBCC OPENTRAN return?
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 10, 2009 at 3:42 pm
Edit: Forum timeout.
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 20 total)
You must be logged in to reply to this topic. Login to reply