2008 R2 Log will not shrink

Question

2008 R2 Log will not shrink

thotvedt

SSCommitted

Points: 1915
More actions
December 19, 2014 at 1:25 pm

#397793

I'm having a serious problem with the log file on one of my servers. While the primary and secondary db files themselves only add up to about 25 GB, the log has grown to nearly 250 gb, even though log backups (running hourly) consistently yield 1.3 GB .trn files. I have tried multiple shrink operations, both via the SSMS interface and T-SQL with no success. According to the interface there is 0 available free space. I have even tried changing the recovery model to simple with no success (I know, spare me the lecture. I'm desperate). I'm becoming desperate because all of my full backups are routinely taking up that entire amount of storage, even though (logs and all) they should be taking no more than 30 GB.
Tom

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

Was replication ever implemented? Database Mirroring? Either of those, without a system on the other side receiving updates, will prevent the log from being re-used.

http://www.sqlservercentral.com/articles/Database+Mirroring/117489/

http://www.sqlservercentral.com/articles/Replication/107698/

thotvedt SSCommitted Points: 1915 More actions · Answer 2

Replication had been failing and has since been removed.

JeremyE SSCoach Points: 16201 More actions · Answer 3

You can find what the transaction log is waiting on by running the following and replacing <dbname> with the name of the database in question. This should point you in the direction to resolve the issue.

SELECT name, log_reuse_wait_desc

FROM sys.databases

WHERE name ='<dbname>'

thotvedt SSCommitted Points: 1915 More actions · Answer 4

The results of the helpdb execution are as follows:

namedb_sizeownerdbidcreatedstatuscompatibility_level

CampusVue 266730.13 MBc20008Aug 17 2011Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=655, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAnsiNullsEnabled, IsAnsiPaddingEnabled, IsAnsiWarningsEnabled, IsArithmeticAbortEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled, IsNullConcat, IsQuotedIdentifiersEnabled90

I know that's a little scrambled but I'm sure you know what you're looking at. I tried the fix in the article your sent with the remove replication execution, which stated it complete successfully, but I was still unable to shrink the log. I was quite hopeful, because the fictional scenario is my place exactly.

thotvedt SSCommitted Points: 1915 More actions · Answer 5

The results of:

SELECT name, log_reuse_wait_desc

FROM sys.databases

WHERE name = 'dbname'

was:

name log_reuse_wait_desc

CampusVue REPLICATION

And this is after running:

EXEC sys.sp_removedbreplication @dbname = dbname

GO

thotvedt SSCommitted Points: 1915 More actions · Answer 6

thotvedt

SSCommitted

Points: 1915

December 19, 2014 at 3:03 pm

#1765811

I really am at my wits end with this.

JeremyE SSCoach Points: 16201 More actions · Answer 7

Since you ran the stored proc to remove replication, it might be that Change Data Capture may be enabled and the SQL Agent jobs are not running. What does the following return?

SELECT name, is_cdc_enabled

FROM sys.databases

WHERE name='<dbname>'

thotvedt SSCommitted Points: 1915 More actions · Answer 8

It returns a "1". How do I disable CDC? I have two jobs, CDC enable and CDC cleanup.

thotvedt SSCommitted Points: 1915 More actions · Answer 9

Actually those two jobs are "CDC Capture" and "CDC Cleanup".

JeremyE SSCoach Points: 16201 More actions · Answer 10

Alright, now we are getting some where. If you are using cdc, you should enable and run the CDC jobs. There should be capture and cleanup jobs. The capture job should run continuously and the cleanup job should run daily. Now if you REALLY want to disable CDC, you can run the following.

USE <dbname>

GO

EXEC sys.sp_cdc_disable_db

GO

If this doesn't work, you may have to manually remove CDC. This article describes the process.

http://www.mssqltips.com/sqlservertip/3003/manual-cleanup-change-data-capture-for-a-sql-server-database

Edit - link came up wrong

thotvedt SSCommitted Points: 1915 More actions · Answer 11

Okay, I ran the following query:

USE MyDB

GO

EXEC sys.sp_cdc_disable_db

GO

I am now seeing that the log is 98% free space. I'm pretty sure I can take this from here.

THANK YOU for you help!!!

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 12

Please mark Jeremy's advice as the correct answer if it worked. That will help others find the answer.

Shafat Husain SSCrazy Points: 2754 More actions · Answer 13

Hi,

Tsql to Disable CDC:

USE <Name of the database>

GO

EXEC sys.sp_cdc_disable_db

GO

You dont have to delete the jobs or manually disable the CDC from tables. Running the above statement would automatically remove all the CDC metadata from your database.

You would need SYSADMIN privileges to run this command.

Hope it Helps..!!!

Regards

Shafat Husain

Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)

Shafat Husain SSCrazy Points: 2754 More actions · Answer 14

OOps-- Duplicate Answer

Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)