SET ROWCOUNT - SQL Server to stop processing the query after the specified number of rows are returned

NOTE:Setting the SET ROWCOUNT option causes most Transact-SQL statements to stop processing when they have been affected by the specified number of rows. This includes triggers. The ROWCOUNT option does not affect dynamic cursors, but it does limit the rowset of keyset and insensitive cursors. This option should be used with caution.

SET ROWCOUNT overrides the SELECT statement TOP keyword if the rowcount is the smaller value.

The setting of SET ROWCOUNT is set at execute or run time and not at parse time.

SET ROWCOUNT stops processing after the specified number of rows. In the following example, note that over 500 rows meet the criteria of Quantity less than 300. However, after applying SET ROWCOUNT, you can see that not all rows were returned.

USE AdventureWorks2012;
GO
SELECT count(*) AS Count
FROM Production.ProductInventory
WHERE Quantity < 300;
GO

Here is the result set.

Count

-----------

537

(1 row(s) affected)

Now, set ROWCOUNT to 4 and return all rows to demonstrate that only 4 rows are returned.

SET ROWCOUNT 4;
SELECT *
FROM Production.ProductInventory
WHERE Quantity < 300;
GO

(4 row(s) affected)

In Database Mirroring Configuration you might encounter with following error:
Msg 1418, Level 16, State 1, Line 1
The server network address "%.*ls" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.


Run the command:
ALTER DATABASE TestDBMirror SET PARTNER = 'TCP://HostName:5050'

You might notice that server network endpoint will not be responding because the specified server network address cannot be reached or does not exist.

Solution.

1. Verify Status of ENDPOINTS. ENDPOINTS should have the status started.
      select state_desc from sys.database_mirroring_endpoints

2. Accounts running  SQL Server services on both machine should be same
       Verify it thru SQL Server Configuration Manager

3. Enable Named Pipe protocols
    Verify it thru SQL Server Configuration Manager  

4. The server should not be behind a firewall.
            ping servername in cmd

5.  The principal server instance and mirror instance should be listening on the same ports and must be available.

6. Enable Named Pipe protocols
    Verify it thru SQL Server Configuration Manager

7. The usernames to run SQL Server services have NT Auth Access between both machines.

Total number of error messages in SQL Server = 10548

SELECT message_id
 ,severity
 ,TEXT
FROM sys.messages
WHERE language_id = 1033;

Change the ownership of SQL Server agent jobs

The jobs may start failing when their AD accounts are removed. If the users have left it is better to change the job owner to a different account.
To ensure such issues wont happen, it is better to create a generic AD account with only necessary permissions and use it exclusively for this purpose.

List down all the jobs which are tied to the users and share it with all the respective project managers and technical architects and get a approval .
Decide on a common user account and create new one .