February 11, 2011 at 6:55 am
No matter what I do unable to truncate sql log on sql 2008
tried backup change mode from full to simple
and shrinking the file.
the log file is over 120 GB
I checked with DBCC Opentran
there is one open tran
Transaction information for database 'Benefit_Repl'.
Oldest active transaction:
SPID (server process ID): 9s
UID (user ID) : -1
Name : tran_sp_MScreate_peer_tables
LSN : (162731:16184:1)
Start time : Feb 9 2011 10:20:07:573AM
SID : 0x01
Replicated Transaction Information:
Oldest distributed LSN : (164494:13028:15)
Oldest non-distributed LSN : (0:0:0)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Please help
February 11, 2011 at 7:20 am
What replication are you running?
The log is been kept active by replication, you won't be able to do anything until you sort out the root cause.
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
February 11, 2011 at 7:34 am
I am running transaction log replication
February 11, 2011 at 7:35 am
Also i tried stopping the replication and shrinking it that did not work too
February 11, 2011 at 7:45 am
Transactional replication? If that's the case, check the log reader SQL agent job and make sure that it's running.
Also check that open transaction. The output of opentran indicates that a single transaction has been running since the 9th Feb. You need to find that and either kill the session or figure out what's keeping it active and fix it.
DO NOT shrink at this point in time. It won't help! The log is full, it needs to grow, trying to shrink it is completely counter-productive.
Let's get the cause fixed, then look at cleaning up the effects later.
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
February 11, 2011 at 7:57 am
Checked the job under publisher and subscriber both the server the log reader job is running
February 11, 2011 at 8:02 am
And the open transaction?
GilaMonster (2/11/2011)
Also check that open transaction. The output of opentran indicates that a single transaction has been running since the 9th Feb. You need to find that and either kill the session or figure out what's keeping it active and fix 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
February 11, 2011 at 8:06 am
how do i check what process is running this ?
Can i kill it and shrink the log file
when i check in the replication monitor the log reader and distributer seems to be running fine
Is there are way i can see in the .ldf file what is it making it grow so big to 120 GB
February 11, 2011 at 8:39 am
newbee dba (2/11/2011)
how do i check what process is running this ?
Query sys.dm_exec_request and sys.dm_exec_sessions
Can i kill it and shrink the log file
Don't fixate on shrinking the log. What's important here is removing whatever is preventing the log from being reused. Getting it back to a reasonable size can be done after the fact and is NOT the most important thing.
You won't be able to kill it, as it's a system process. What you do need to do is see if it's blocked (and by what) and what it's wait type is.
Is there are way i can see in the .ldf file what is it making it grow so big to 120 GB
The log_reuse_wait_desc in sys.databases. It'll likely say 'Active_Transaction' at this point
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
February 11, 2011 at 8:56 am
I queried sys.dmexec_requests and see 3 session waiting since 44 hours
session_idrequest_idstart_timestatuscommandsql_handlestatement_start_offsetstatement_end_offsetplan_handledatabase_iduser_idconnection_idblocking_session_idwait_typewait_timelast_wait_typewait_resourceopen_transaction_count
2402011-02-09 10:20:07.653backgroundBRKR TASKNULLNULLNULLNULL11NULL0BROKER_TRANSMITTER178348044BROKER_TRANSMITTER0
2502011-02-09 10:20:07.653backgroundBRKR TASKNULLNULLNULLNULL11NULL0BROKER_TRANSMITTER178348044BROKER_TRANSMITTER0
902011-02-09 10:20:07.653backgroundSIGNAL HANDLERNULLNULLNULLNULL11NULL0KSOURCE_WAKEUP174755473KSOURCE_WAKEUP1
February 11, 2011 at 9:11 am
So there's nothing in the blocked_by column?
Let me get a second opinion on this.
Is restarting SQL an option?
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
February 11, 2011 at 9:25 am
yes blocking session id is 0
restart sql is an option but not right now as it is production but let me check
you want me to stop and start the sql server services
February 11, 2011 at 9:37 am
newbee dba (2/11/2011)
you want me to stop and start the sql server services
Not right now, I've asked some others for a second opinion. I just wanted to know if it was an option.
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
February 11, 2011 at 10:13 am
actually i did stopped and restart the sql server agent
than i shrink the file
I think this bought down the log file from 122 gigs to 24 gigs
But i think this is still huge
what should i do now
February 11, 2011 at 10:38 am
24 GB is not necessarily large for a log. How large is the DB? How active is the DB? What's the frequency of your log backups? What's the max space that the log uses?
What does OPENTRAN report now? What's the log reuse wait description in sys.databases for this DB?
May be worth a read - 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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply