Sql Server : Login failed for user ‘distributor_admin‘. Error number: 18456)

Quick one today:

Recently, on one of our lab machines, this error occurred with replication. On the surface ‘distributor_admin‘ login seems familiar (Obvious with its naming), but we do not configure this login anywhere during replication set up (on the surface).

Login failed for user ‘distributor_admin‘. (Source: MSSQLServer, Error number: 18456)

First we’ll directly get to ideas on resolving it, then we’ll look at some internal details.

Resolution:

1. If you have the password for Distributor_admin, update publisher with it. Go to Replication >> Publisher Properties >> (enter the password in the ‘Administrative Link Password‘ section).

2. 

   Publisher Properties Password

3. If you do not have the password, then we need to first reset it.
   • Go to Distributor. Right click on Replication >> Distributor Properties >> Publisher tab >> (set new password in the ‘Administrative Link Password‘ section)
   • Now, go to Publisher and implement Step 1 (above) on the Publisher.

Distributor Properties Password

 

Story:

In Sql Server, all interactions are carried out under clearly defined security logins and their corresponding roles (for better security management). Same applies to Replication as well. When we configure Distributor, a new default login (a.k.a. distributor_admin) is created; we specify password as part of ‘Configure Distributor‘ wizard. If you go to logins section on the distributor machine, we could see the new login. This login is used to perform any operations on Distributor.

Distributor_admin Login on Distributor

When we configure Publisher, we specify this password (for the default login ‘distributor_admin‘), that the Publisher uses to connect to Distributor. This servers two purposes.

• Provides proper security context for Publisher to establish linked server connection to Distributor and perform any necessary activities.
• Provides Distributor that this Publisher (with correct password) is authorized to remotely carry out operations in Distributor.

Hence it is called Administrative Link Password. For more information, please go through this MSDN article.

 

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server : Reduce or Remove too many VLFs on Transactional Log

Quick one today:

Update : Oct 2016 : Elaborate version for Optimal VLF configuration is added here

Recently, in our lab machine, we had a problem of too many VLF’s. More than 8000 VLFs for each of our VLDB’s.

So, we needed some aggressive approaches to clean up Transactional Log file in a hurry!! This is what we ended up doing.

Note: These steps are not to be used in a production environment. These are aggressive & unsophisticated steps not qualified for production environment. For more information read great blogs on SqlSkills website.

Root Cause:This happens, when Transaction Log files are incrementally grown in small increments over a long period of time a.k.a. AutoGrowth 1 MB.

Benefits: We need to maintain the health of Transactional Log file for better database performance.

Step 1:

If you database is in FULL recovery mode, take a Transactional Log backup. If SIMPLE mode, then run CHECKPOINT and then wait a minute or two.

 

Step 2:

Get the current count of VLFs

--
-- Capture the current VLF count
--
DBCC LogInfo
GO

 

Step 3:

Shrinkfile to the least possible size.

Now, shrink the Transactional Log file to the smallest file size possible. This varies from database to database based on the initial size specified during creation.

TRUNCATEONLY forces the free space to be returned to Operating System. We want to do this, so we could increase it later correctly.

--
-- Shrink log file size first.
--
DBCC SHRINKFILE (N'Sample_log' , 0, TRUNCATEONLY);
GO

 

Step 4:

Increase Transactional Log File Size
Now increase the log file size to its desired or expected size in its final state.

• If you need 5 GB log file size, then assign something like 8 GB.
• If your database usually needs 100 GB log file size, then assign something like 104 GB.
• Essentially in multiples of 8 GB

--
-- Now, increase the log file size to desired size.
--
ALTER DATABASE Sample
MODIFY FILE (
     NAME = N'Sample_log'
   , SIZE = 104 GB
)
GO

 

Step 5:

Again get the current count of VLFs

--
-- Capture the current VLF count
--
DBCC LogInfo
GO

Now you’ll see that the VLF count is much smaller. Each VLF size is larger and help with better performance.

 

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server Replication : Could not find stored procedure ‘sp_MSreplcheck_qv’

Quick one today:

Recently, when setting up replication on a lab machine, this error occured. Seen this error several times in the past, but never took time to blog about it.

TITLE: Configure Distribution Wizard
------------------------------
SQL Server could not retrieve information about server 'DC1REPL01'.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1&EvtSrc=Microsoft.SqlServer.Management.UI.ReplUtilitiesErrorSR&EvtID=CantGetInformation&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
Could not find stored procedure 'sp_MSreplcheck_qv'. (Microsoft SQL Server, Error: 2812)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.00.4100&EvtSrc=MSSQLServer&EvtID=2812&LinkId=20476

We have Sql 2008 R2 for Publisher and Subscriber; And Sql 2014 for Distributor. This error occurred, when replication was being configured from the Publisher (Sql 2008 R2).

Since Distributor is Sql 2014, connecting to Distributor (Sql 2014) from a Publisher (Sql 2008 R2) has resulted in this error.

When configuring replication, we need to make sure all steps are attempted from the Management Studio (SSMS) of the latest version instance (which usually is Distributor.

Solution

This is fairly simple error to resolve.

Reattempt the actions from Distributor’s Management Studio (SSMS).

Log into Distributor machine and connect to Publisher from its Sql 2014 SSMS. Replication activities need to be performed from the instance that has the most latest SQL and SSMS versions. Since Distributor needs to be at least the same or later version of Sql compared to Publisher & Subscriber, we need to attempt these steps from Distributor’s SSMS.

 

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server : How to increase the number of Sql Server Error Log files

Quick one today:

Earlier, on all our production systems, we needed to extend retention of all Sql Server error logs. Usually, after every few days (5 or 6), Sql Server purges oldest Sql Server Error Log. If you want to increase it, there is an easier option.

Step 1:

In Sql Server Management Studio, go to Management >> Sql Server Error Logs >> Right click and go to Configure.

SqlServer ErrorLogs Configure

Step 2:

Set the retention value to appropriate number. For us we are required to maintain 60 days, so it is set to 61 days, just to be safe.

SqlServer ErrorLogs Increase Retention

 

Side Note:

Essentially it is a registry setting on the OS where the instance is installed. Look for something like

> HKEY_Local_Machine
> Software
> Microsoft
> Microsoft Sql Server
> (instance name)
> MSSQLServer
> Look for registry key called NumErrorLogs.

It would look something like this.

SqlServer ErrorLogs Registry Setting

Hope this helps,
_Sqltimes

Read Full Post »