Dropping a publisher when the distributor is down

Working on a new server, I came across a replication process that had been forgotten about and the pieces were disconnected.  The distributor server/process had been installed on another server and when I went to remove the publisher from the database, I got the below error.

[sourcecode language=”sql”]
DECLARE @publication AS sysname;
SET @publication = N’SmokeTest’;

— Remove a transactional publication.
USE [ReportServerTempDB]
EXEC sp_droppublication @publication = @publication;
[/sourcecode]

OLE DB provider “SQLNCLI10” for linked server “repl_distributor” returned message “Login timeout expired”.OLE DB provider “SQLNCLI10” for linked server “repl_distributor” returned message “A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.”.
Msg 11001, Level 16, State 1, Line 0
TCP Provider: No such host is known.

The linked server was pointing to a server that no longer existed and I thought I was stuck.  I tried lots of options, but ultimately came across this blog that demonstrated how to forcefully remove the publisher when I couldn’t connect to the distributor.

Spoiler: The trick is to use the @ignore_distributor=1 parameter and you should be good to go.

[sourcecode language=”sql”]
DECLARE @publication AS sysname;
SET @publication = N’SmokeTest’;

— Remove a transactional publication.
USE [ReportServerTempDB]
EXEC sp_droppublication @publication = @publication<code>
,@ignore_distributor=1</code>;
[/sourcecode]

You can’t shrink a logfile when . . .

I was asked to look at a database server for the first time.  One of the tasks I was given was to shrink the log file of a database as the previous owner could not.  The DB was only 10 MB, but the log file was 50GB.  No problem, I say to myself–like taking candy from a baby.  This baby; however, was trained in the art of MakeMeCrazy.

Most of the advice out there prompts you to find the root cause of the growth as the log is likely to grow again so shrinking the log file is not a fix, just a band-aid.  I should have pondered this question longer.  This server was running SQL 2008, so the truncate_only option wasn’t available so I changed the recovery model to simple and was then going to shrink the log–only the database was already is simple mode.  I tried shrinking the log and the command executed but did not change the size of the file.

Strange–why would 10 MB DB need a 50 GB log file?  Can you think of a reason?  I admit I was dumbfounded and actually had some strange ideas pop into my head.

 

The reason??  It was setup as a publisher at one point and had been forgotten about.  I removed the publisher and bam–the log shrunk right down.

Importing or Exporting to Access

I had a client request to export some data to Access 2010 for their downstream use.  I have done this in the past and thought it a no-brainer; however, there is always a little trick–hence the post.  🙂

Because I had already installed Office 2010, I assumed the Microsoft Access Database Engine for 2010 would get me what I needed.  Because I was using a new machine, I had to install the 2007 Office System Driver: Data Connectivity Components.  After I did that, I could see the Microsoft Office 12.0 Access Database Engine OLE DB Provider.  I put in the location of the access db, and I was off and running.AccessEngineProvider