December 16, 2010 at 6:12 am
So I have a situation where some of you probably have a simplistic answer for me, and I hope you do. The situation, I have a database named Carecast. It's log file is over 430,000 MB. My Log drive is quickly filling up, I have only about 1 GB to spare. The database is a full recovery model database. I ensured a full backup ran and completed successfully last night. I also have a new transaction log backup job that runs hourly. This started running successfully last night after the full backup as I expected. In addition, I have another command shown below that tries to shrink the size of the log. The job results in success, but the space allocated to the log file stays at 430K MB.
USE Carecast;
GO
DBCC SHRINKFILE (Carecast_Log, 200000);
GO
So my question is, since I have new jobs setup to handle and backup the transactions on a hourly basis, why can I not get the log file drive space reallocated back down to a more reasonable amount? What are other commands I could use to get my transaction log back into check?
Help! This database is based on Sql Server 2008 Enterprise Edition.
Jason Nance, MHA
December 16, 2010 at 6:27 am
Two things come to mind. First, you don't mention if you are doing the log backup with truncation. If not, then all transactions will remain, as the default is without truncate. Second, if you are truncating the log when you back up, there may be an old open transaction back at the beginning of the log file which is preventing that part of the file to be marked inactive, thus keeping anything after it in the log. I would check those 2 things first.
Hope this helps.
December 16, 2010 at 6:47 am
How do I truncate the transaction log in SS2008? I thought that command is invalid in sql server 2008? Is there any code you could give me for either one of your suggestions?
Jason
December 16, 2010 at 7:03 am
I went through the tasks -> backup GUI and ensure truncate the transaction log was checked. It was and I am not able to reclaim any of the 430K MB space by the log, issue still exists. Jason
December 16, 2010 at 7:15 am
And I have no open old transactions : ran this command DBCC OPENTRAN
Received these results
Transaction information for database 'CareCast'.
Oldest active transaction:
SPID (server process ID): 55
UID (user ID) : -1
Name : ggs_repl
LSN : (33114:57895:4)
Start time : Dec 16 2010 9:14:48:693AM
SID : 0x01
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (32133:172909:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
December 16, 2010 at 8:08 am
I also switched the recovery model to simple ran the DBCC OPENTRAN (which returned no results), tried to shrink the log, and it is still the same size. Jason
December 16, 2010 at 10:07 am
jasonnance77 (12/16/2010)
Replicated Transaction Information:Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (32133:172909:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
You've got some partially configured replication that's preventing the log records from being marked as reusable. Is this database supposed to have a transactional replication publication in it?
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
December 16, 2010 at 10:10 am
Rich-403221 (12/16/2010)
First, you don't mention if you are doing the log backup with truncation. If not, then all transactions will remain, as the default is without truncate.
Err, no. The default is with truncation.
BACKUP LOG <db name> TO DISK=<Somewhere>
will back the log up then mark the space used by the inactive vlfs that it backed up as reusable. To get a log backup to not truncate, you have to explicitly request it.
BACKUP LOG <db name> TO DISK=<Somewhere> WITH NO_TRUNCATE
This will back the log up and do nothing else. It's for use when the database is damaged and the truncation of the log will fail. This is called a tail-log backup and it's definitely neither default nor something that you're going to do most of the 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
December 16, 2010 at 11:14 am
Gail,
Thanks. I don't know what the heck I was thinking there. My bad. :blush:
Rich
December 16, 2010 at 11:16 am
Your comment made me think a little differently about this. I queried the sys.databases table and looked at the log_reuse_wait_desc column, it stated REPLICATION. I have NO IDEA why...
I ran this statement: EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
and then tried to shrink the log file, which did FINALLY happen.
So I just took a full backup and moved my transactional logs back to running hourly again.
I don't know why this occurred though. It makes no sense to me!
Jason Nance, MHA
December 16, 2010 at 1:00 pm
jasonnance77 (12/16/2010)
Your comment made me think a little differently about this. I queried the sys.databases table and looked at the log_reuse_wait_desc column, it stated REPLICATION. I have NO IDEA why...I ran this statement: EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
and then tried to shrink the log file, which did FINALLY happen.
So I just took a full backup and moved my transactional logs back to running hourly again.
I don't know why this occurred though. It makes no sense to me!
I've seen this a number of times before, and what you did may not have been a complete fix. Check log reuse again in a couple of days (and DBCC OpenTran) and see if there's any sign of replication again. If so, post back here and I'll walk you through a full solution.
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
December 16, 2010 at 1:02 pm
Excellent, Thank you Gail! ~ Jason
December 16, 2010 at 1:21 pm
Gail:
I need to get those other steps from you, the database is already back in a REPLICATION state when I query sys.databases.
Thanks,
Jason Nance, MHA
December 16, 2010 at 1:24 pm
1) Create a transactional replication publication. Publish a single article (a small one)
2) Stop the log reader job
3) Run the same repldone command you ran earlier
4) Delete the publication that you created
5) Verify that there is no mention of distributed and non-distributed LSNs in the output from DBCC OPENTRAN
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
December 16, 2010 at 1:46 pm
Is this a BUG with SS2008? Is there a specific patch that fixes it?
Thanks again for your help!
Jason
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply