December 17, 2014 at 4:14 am
Thanks Shafat
I had already cleared it up. I was just inviting for more thoughts. Great link though. keep it up.:-)
January 8, 2015 at 12:10 pm
Gila you are a life safer!!! Thanks!!
January 8, 2015 at 12:11 pm
Thanks so much!!!
GilaMonster (4/10/2009)
Either they were restored from backups of published databases or there was replication at one point and it was removed improperly.Set the server up for replication, create a publication in the DB in question. Publish any table (not important which one) and then completely remove replication again.
January 20, 2015 at 8:05 am
I tried
sp_RemoveDbReplication
and
ALTER DATABASE SET RECOVERY SIMPLE
Neither worked. Then I tried to right click on replication publisher properties, enabled the db in question as a publisher, hit OK, went there again and disabled, and it finally worked.
February 8, 2015 at 9:36 pm
Just set the server up for replication and create a publication in the DB in question.
February 8, 2015 at 10:28 pm
I am creating a publication in the DB
April 6, 2015 at 1:42 am
USE master
EXEC sp_removedbreplication
@dbname = 'dbname'
GO
May 4, 2015 at 12:16 am
GilaMonster (4/10/2009)
Either they were restored from backups of published databases or there was replication at one point and it was removed improperly.Set the server up for replication, create a publication in the DB in question. Publish any table (not important which one) and then completely remove replication again.
Gail, you da bomb! This helped me today, thank you!
Regards,
D.
June 19, 2015 at 8:51 am
Thanks,
Struggled whole day trying to squeeze down a 34 Gb log file. This one did it, finally.
August 11, 2015 at 12:57 pm
Hello,
I struggled a lot, but I'm really stuck here.
Context:
1. SQL2008 R2 Enterprise Edition 64 bit SP1
2. Recovery model: Simple
3. Log file reached 2 TB, I needed to add another one to make DB (is a production one) works
4. Attempt to shrink log failed:
Command:
dbcc shrinkfile ('RHO_Log', 1)
Answer:
Cannot shrink log file 2 (RHO_log) because the logical log file located at the end of the file is in use.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
7 2 268435200 12800 268435200 12800
5. Verified the log_reuse status:
Command:
SELECT log_reuse_wait, log_reuse_wait_desc FROM sys.databases WHERE NAME='RHO'
Answer:
log_reuse_wait log_reuse_wait_desc
-------------- ------------------------------------------------------------
6 REPLICATION
6. No replication involved, but still tried to force replication removal:
Command:
EXEC sp_removedbreplication RHO
Answer:
Command(s) completed successfully.
7. Re-verified step item 5 above, same answer.
8. (Later edit) I checked if there are active transactions. It appears that are not:
Command:
dbcc opentran(RHO)
Answer:
Transaction information for database 'RHO'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (2287630:4128:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
9. Checked the status of VLFs (appears very strange):
Command:
dbcc loginfo (RHO)
Answer:
(839707 row(s) affected) -> Which looks enormous for me, most of them with Status=2.
I'm really stuck in this story, because I really need to shrink that stupid log file tomorrow.
Any help will be greatly appreciated.
Thanks.
___________________________________________________________________
Is better shut up and look stupid than open your mouth and eliminate any doubt
August 11, 2015 at 2:59 pm
Hi
you actually tried almost every thing. You can also try below
1. Take database in single user mode and you are the one only having connection
2. Issue checkpoint on database
3. Increase log file size by 1 MB
4. Run shrink
I am not sure this will work in your environment, But its worth try (worked for me some time back)
Thanks
Saurabh Sinha
August 12, 2015 at 12:42 am
OK, with the help of a friend of mine, which is an excellent DBA (and maybe a member here, but I don't know his username 🙂 ), the problem was solved. He discovered that Change Data Capture (https://msdn.microsoft.com/en-us/library/cc645937.aspx) was enabled for this database and this caused the problem. He disabled the CDC, then log file was shrunk immediately.
___________________________________________________________________
Is better shut up and look stupid than open your mouth and eliminate any doubt
August 20, 2015 at 11:37 am
giraom 1204 (1/20/2015)
I triedsp_RemoveDbReplication
and
ALTER DATABASE SET RECOVERY SIMPLE
Neither worked. Then I tried to right click on replication publisher properties, enabled the db in question as a publisher, hit OK, went there again and disabled, and it finally worked.
this solution works for me, easier and simpler than re-create replication.
August 20, 2015 at 11:39 am
Woo~ awesome. works for me. simpler and easier than re-create replication.
November 27, 2015 at 1:09 am
Perfect
--- 1. Query
SELECT name, log_reuse_wait_desc FROM sys.databases
--- check if MyDatabaseName says
--- log_reuse_wait_desc = Replication
--- 2. Force removal of the replication.
EXEC sp_removedbreplication MyDatabaseName
--- 3. Check
SELECT name, log_reuse_wait_desc FROM sys.databases
--- check if MyDatabaseName says
--- log_reuse_wait_desc = Nothing
--- 4. Force your DB to Simple Recovery:
ALTER DATABASE MyDatabaseName SET RECOVERY SIMPLE
--- 5. Run db shrink command
backup log MyDatabaseName with truncate_only
dbcc shrinkfile(MyDatabaseNameLog)
--- Perfect
--- thanks again 🙂
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply