We had an SCCM DB that had an LDF that grew out of control. By the time all was said it was over 130+ gig.
I was able to get the LDF to stop growing by rebooting the server but was not able to get the LDF to shrink. I tried manual and script based shrink tactics and nothing worked. After a few days it started to grow once again.
The DB is set to Simple as MS instructs for SMS/SCCM so it should auto shrink as it processes information.
We finally got a DBA (he was on summer vacation :-) ) to look at the issue and here is the solution that he used to fix the problem.
Apparently there were some transitions that were marked as uncommitted so the LDF would not auto shrink.
Steps used
1) Log into the affected database.
2) Identify the problem:
a. Run ‘DBCC OPENTRAN’
b. If the output has the word “DISTRIBUTED” in it, there are transactions marked as uncommitted distributed transactions.
3) If the database is being replicated, resolve any replication issues; otherwise do the following.
4) Create a publication on the affected database. Use the Management Studio GUI and right click on the
“Replication->Local Publications” folder for the database server. Select the “New Publication Task”, and select the defaults. There is no need to create or schedule a snapshot. The following should also work, but I haven’t tried it.
EXEC sp_addpublication
@publication = @publication,
@status = N'active',
@allow_push = N'true',
@allow_pull = N'true',
@independent_agent = N'true'
5) Execute the following to remove the uncommitted distributed transactions from the transaction log.
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
6) Run “DBCC OPENTRAN” again to verify that the uncommitted distributed transactions have been removed.
7) Drop the publication with the GUI by right-clicking on the publication object and selecting DELETE or run the following:
EXEC sp_droppublication @publication = @publication;
8) Shrink the transaction log file if necessary.
One other thing:
If you used the GUI/Wizard to create the publication, disable and drop any jobs created by it. These jobs will interfere with the next command, and the error message will not give you an accurate indication of the nature of the problem. So add:
4.5 ) Disable and drop any jobs created by the Publication Wizard.
Special thanks to George Nace from DOH for figuring out this solution.
Chris Stauffer <><