Thursday, November 08, 2012

Error on executing sp_cycle_agent_errorlog Msg 22022, Level 16, State 1, Line 0

sp_cycle_agent_errorlog

Every time SQL Server Agent is started, the current SQL Server Agent error log is renamed to SQLAgent.1; SQLAgent.1 becomes SQLAgent.2, SQLAgent.2 becomes SQLAgent.3, and so on. sp_cycle_agent_errorlog enables you to cycle the error log files without stopping and starting the server.

This stored procedure must be run from the msdb database.

Error on executing of sp_cycle_agent_errorlog

clip_image002

image

 

USE msdb

go

EXEC sp_cycle_agent_errorlog

GO

Msg 22022, Level 16, State 1, Line 0

SQLServerAgent Error: Access is denied.

Each time the stored procedure was run it just overwritten the current agent log without renaming it and because of this only one SQLAGENTOUT file was left.

Wednesday, November 07, 2012

SQL Mail: The mail could not be sent to the recipients because of the mail server failure.


SQL Mail Sent fail with errors below;

Error from the SQL Mail log:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2012-11-07T22:09:50). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it 205.191.22.44:25). )

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2012-01-17T11:13:58). Exception Message: Could not connect to mail server. (No such host is known). )

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2012-01-17T11:12:44). Exception Message: Cannot send mails to mail server. (Failure sending mail.). )

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2010-10-24T19:55:11). Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 16.236.32.51:25). )

Resolution:

From the error message it is clear that it is a communication issue between SQL Server host and the Mail Exchange Server.

Problem was related to Exchange Server not allowing relaying from SQL Server.

Please engage Network Administrator to add SQL Server host IP Address to allow to send emails taking below steps, like

On Mail server > Exchange Server Manager > SMTP Connector Properties
> Access Properties > Access Tab > Relay button > Add  IP Address of SQL Server

Also cross check if the Anti Virus (AV) program running on the SQL Server host. AV blocks services from sending on TCP-25. Adjust the AV software accordingly. Add DATABASEMAIL90.EXE to the list of innocent programs in your AV.

Tuesday, November 06, 2012

Query to find the Linked Servers with given remote login

 

It would be useful to cross check, when the Linked Server remote login password has changed.
In this case, linked server will wail due to wrong password. To fix this we have reset the correct password.

SELECT s.server_id,S.name,S.product,S.provider,l.remote_name,'Remote_Login_Modified_Dt'=l.modify_date,s.modify_date
FROM SYS.SERVERS S
INNER JOIN SYS.LINKED_LOGINS L
ON  s.server_id = L.server_id
WHERE S.server_id > 0
AND S.product ='SQL Server'
AND L.remote_name ='sa'

Returning the parameters for a specified stored procedure or function

 

Before you run the following query, replace <database_name> and <schema_name.object_name> with valid names.

USE <database_name>;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
    ,o.name AS object_name
    ,o.type_desc
    ,p.parameter_id
    ,p.name AS parameter_name
    ,TYPE_NAME(p.user_type_id) AS parameter_type
    ,p.max_length
    ,p.precision
    ,p.scale
    ,p.is_output
FROM sys.objects AS o
INNER JOIN sys.parameters AS p ON o.object_id = p.object_id
WHERE o.object_id = OBJECT_ID('<schema_name.object_name>')
ORDER BY schema_name, o.object_name, p.parameter_id;
GO

 

Reference: http://msdn.microsoft.com/en-us/library/ms190324.aspx

List database objects modified in the last ‘N’ days

 

Before you run the following query, replace <database_name> and <n_days> with valid values.

 

USE <database_name>;
GO
SELECT name AS object_name
  ,SCHEMA_NAME(schema_id) AS schema_name
  ,type_desc
  ,create_date
  ,modify_date
FROM sys.objects
WHERE modify_date > GETDATE() - <n_days>
ORDER BY modify_date;
GO

 

Source: http://msdn.microsoft.com/en-us/library/ms190324.aspx

Thursday, November 01, 2012

Script to check SQL Server Linked Server Status

 

It leverages on the system stored procedure sys.sp_testlinkedserver


SET NOCOUNT ON;
DECLARE @LinkName NVARCHAR (128), @retval INT, @msg varchar(300)
IF (SELECT COUNT (srvname) FROM sys.sysservers WHERE srvname <> @@SERVERNAME) = 0 RETURN

DECLARE srvname INSENSITIVE CURSOR FOR
        (SELECT srvname FROM sys.sysservers WHERE srvname <> @@SERVERNAME) FOR READ ONLY
OPEN srvname
    FETCH NEXT FROM srvname INTO @LinkName
WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        EXEC @retval = sys.sp_testlinkedserver @LinkName
    END TRY
   
    BEGIN CATCH
        IF @LinkName IS NULL RETURN
        SET @retval = SIGN(@@ERROR)
        IF @retval <> 0
        BEGIN
          SET @msg = 'Unable to connect to the Linked server : ' + @LinkName
          RAISERROR (@msg, 16, 2 )
        END
    END CATCH
    FETCH NEXT FROM srvname INTO @LinkName
   
END   
   
CLOSE srvname
DEALLOCATE srvname