Ads

16 May 2015

The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE.

Error :
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65554. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE.

Solution 1: KB Article
https://support.microsoft.com/en-us/kb/918040

Solution 2: Change the Permission Set : External

While doing this faced the below error :

ERROR: 
The database owner SID recorded in the master database differs from the database owner SID recorded in database
To check who is the owner listed in the master database run the following:

SELECT  SD.[SID]
       ,SL.Name as [LoginName]
  FROM  master..sysdatabases SD inner join master..syslogins SL
    on  SD.SID = SL.SID
 Where  SD.Name = ''


To correct this issue, run the following command:

Alter Authorization on Database:: to []

Once done set the required settings on the properties of Assembly.

16 April 2015

Mirroring Endpoint Owner Changing



Breaking mirror and recreating by logging into the desired account will be the easiest way. But we can follow the below steps to fix the account without breaking.

1. Query to find the current endpoint owner of the instance and take a note.

SELECT [PrincipalName] = sp.name, [PrincipalId] = sp.principal_id, me.* FROM sys.database_mirroring_endpoints me with(nolock)
inner join sys.server_principals sp with(nolock) on me.principal_id = sp.principal_id

2. New endpoint not exists then we need to create as login and need to be granted the following authorization.

GRANT CONNECT ON ENDPOINT::Mirroring TO [Domain\LOGINNAME];
ALTER AUTHORIZATION ON ENDPOINT::Mirroring TO [Domain\LOGINNAME];

3. Verify the current owner of endpoint using step 1.

15 April 2015

SQL Server Agent - xp_readerlog[] returned error3 -System can not find the path specified

Here in the steps to follow:



USE MASTER
GO
EXEC msdb..sp_get_sqlagent_properties
GO

USE MASTER
GO
EXEC msdb.dbo.sp_set_sqlagent_properties
@errorlog_file=N'PATH'  -- N'k:\MSSQL10_50\MSSQL\Log\SQLAGENT.OUT'
GO


USE MASTER
GO
EXEC msdb..sp_get_sqlagent_properties
GO


Restart SQL server agent.

10 April 2015

Query to verify Replication on the SQL Server Instance

 select name, is_published, is_subscribed, is_merge_published, is_distributor
  from sys.databases
  where is_published = 1 or is_subscribed = 1 or   is_merge_published = 1 or is_distributor = 1

13 March 2015

Distribution Agent Errors _OS Error


Error 1:
The process could not read file T:\ Microsoft SQL Server\\MSSQL\repldata\\ due to OS error 3. (Source: MSSQL_REPL, Error number: MSSQL_REPL20016)
Error 2:
Cannot DROP TABLE 'dbo.PrFlagAon' because it is being referenced by object 'VW_FlsAsoU2'. (Source: MSSQLServer, Error number: 3729)
Error 3:
One or more BLOB columns could not be sent to the server, attempt to recover from the problem failed.

Resolution1:
The error occurs in pull subscriptions because the remote distributor you must specify a UNC network share such as \\\snapshot rather than a local path for the snapshot folder. 
To alleviate this issue, make the snapshot folder a UNC network share and update the Distributor Properties to reflect the change the path.
Drop and recreate or reinitialize subscription with new snapshot.

Resolution 2:
The view is a schema bounded and which is creating the problem. Login to subscriber go to the database script out the view. Once you have script drop the view until it applies the snapshot on the subscriber.
Once applying snapshot is completed and replication applies all scripts re-create the views which are dropped in the above step.

Resolution 3:

This will fix automatically as the data is BLOB and if any network packet drop outs will cause this. If this a IMAGE type and failing each time, then MSDN says that to replicate LOB data, using "Distribution Profile for OLEDB streaming" profile is optimized.