December 20, 2011 at 2:54 am
Hi,
I unable to remove t-log file from my db...
Issue is my t-log file got full which having transaction replication. Now I want either shrink t-log or add new t-log file and remove existing t-log file.
Shrink is getting failed so I am trying to add and remove exsting t-log file but getting error "Cann't remove primary data/log file"... whenever t-Log file have any file group.
Can you please help me out to reslove the issue...?
Thanks!!!
December 20, 2011 at 3:04 am
You cannot remove a file from the database unless the file has no existing data or transaction log information; the file must be completely empty before it can be removed.
For More: http://technet.microsoft.com/en-us/library/ms191433.aspx
December 20, 2011 at 3:10 am
SQL Server has been restarted and also issue the checkpoint... means all uncommited transaction should be go in mdf... than do you think data present in t-log file?
Thanks,
December 20, 2011 at 3:15 am
Not Necessarily.
Please go through following articles. It will answer your current (as well as next) questions.
Troubleshooting a Full Transaction Log
http://msdn.microsoft.com/en-us/library/ms175495.aspx
Recover from a full transaction log in a SQL Server database
December 20, 2011 at 3:19 am
One snippet here, from BOL for your case (Replication & Transaction Log issue).
Merge replication and snapshot replication do not affect transaction log size, but transactional replication can. If a database includes one or more transactional publications, the log is not truncated until all transactions relevant to the publications have been delivered to the distribution database. If the transaction log is growing too large, and the Log Reader Agent is running on a scheduled basis, consider shortening the interval between runs. Or, set it to run in continuous mode. If it is set to run in continuous mode (the default), ensure that it is running.
December 20, 2011 at 3:30 am
You can't remove the first log file that the database had. If you add other log files you can remove them, but the first one has to stay.
Why do you want to shrink? What's the situation (in detail please)? What's the size of the log? What's the % used? What's the log_reuse_wait_desc? What's the output of DBCC OPENTRAN? What's the status of the log reader?
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 20, 2011 at 3:43 am
I have 2 TB SAN Drive and now t-log size reach this much size there is no disk space.
There are no open transaction....
I stopped replication and other source which creates the transaction...
Now My issue is there is not disk space available on log drive so I need to create space on this drive.
Either shrink the t-log file or add and delete the existing t-log file....
or any other way to create to space...
But both suivation I didn't perform....
Thanks,
December 20, 2011 at 3:54 am
GilaMonster (12/20/2011)
What's the size of the log? What's the % used? What's the log_reuse_wait_desc? What's the output of DBCC OPENTRAN? What's the status of the log reader?
Not asking for my own amusement...
Please take a read through this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/
We can help you, but you're going to have to give us something to work with...
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 20, 2011 at 4:10 am
I just figure out the idea where I can reslove the issue.
log_reuse_wait_desc = REPLICATION
Thanks,
December 20, 2011 at 7:13 am
Hello,
Can you please help me out how I remove log_reuse_wait_desc = Replication to Active_Transcation.
Thanks in advanace for help me out ...
Regards,
December 20, 2011 at 8:40 am
Getting there slowly...
GilaMonster (12/20/2011)
What's the output of DBCC OPENTRAN? What's the status of the log reader?Please take a read through this if you haven't already (I know there are some details in there on replication waits): http://www.sqlservercentral.com/articles/Transaction+Log/72488/
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 20, 2011 at 9:50 am
khandelwal_arun (12/20/2011)
Hello,Can you please help me out how I remove log_reuse_wait_desc = Replication to Active_Transcation.
Thanks in advanace for help me out ...
Regards,
Are you taking regular backups of the publication\distribution databases?
Is the log reader agent running or running successfully?
Please return the results of the following
Against the distributor
select databasepropertyex('distribution', 'IsSyncWithBackup')
Against the publication database
select databasepropertyex('your pub db', 'IsSyncWithBackup')
Have you checked Replication Monitor for furtehr details?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 20, 2011 at 11:01 am
The issue has been resloved thanks for your prompt help... 🙂
I really appriciate your prompt and helpful response....
Thanks,
December 20, 2011 at 11:09 am
khandelwal_arun (12/20/2011)
The issue has been resloved thanks for your prompt help... 🙂I really appriciate your prompt and helpful response....
Thanks,
Care to share the resolution, it may help others in future
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 20, 2011 at 12:35 pm
Add new drive in my system and create new log file on new added drive....
remove replicaiton ....
clean distributor log....
... shrink log file with empty....
.... and get it done...
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply