Retrieve a list of all indexes along with their file groups

This is a script we use frequently to identify the location of all clustered and non-clustered indexes as part of storage re-arrangement operations. It returns the list of all indexes along with the file group name.

use
go
SELECT
    t.name AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    fg.name AS FileGroupName
FROM sys.indexes i
INNER JOIN sys.filegroups fg on i.data_space_id = fg.data_space_id
    AND i.name IS NOT NULL
INNER JOIN sys.tables t ON i.object_id = t.object_id
/*
TableName  IndexName       IndexType     FileGroupName
---------- --------------- ------------- -------------
Table1     IDX_Table1_Col1 CLUSTERED     PRIMARY
Table1     IDX_Table1_Col2 NONCLUSTERED  NCI
Table2     IDX_Table2_Col1 NONCLUSTERED  NCI
Table2     IDX_Table2_Col2 NONCLUSTERED  NCI
*/

Script to retrieve the queries that are Most expensive / use the Most IO

The script below helps to identify the top 10 queries that use the most I/O as ordered by Total IO, taking more time to execute (means most expensive) across all databases on the server.

SELECT TOP 10
        [Individual Query] = SUBSTRING(qt.TEXT,
                                       ( qs.statement_start_offset / 2 ) + 1,
                                       ( ( CASE qs.statement_end_offset
                                             WHEN -1 THEN DATALENGTH(qt.TEXT)
                                             ELSE qs.statement_end_offset
                                           END - qs.statement_start_offset )
                                         / 2 ) + 1),
        [Total IO] = ( qs.total_logical_reads + qs.total_logical_writes ),
        [Average IO] = ( qs.total_logical_reads + qs.total_logical_writes ) / qs.execution_count,
        [Execution Count] = qs.execution_count,
        [Total Logical Reads] = qs.total_logical_reads,
        [Total Logical Writes] = qs.total_logical_writes,
        [Total Worker Time/CPU time] = qs.total_worker_time,
        [Total Elapsed Time In Seconds] = qs.total_elapsed_time / 1000000,
        [Parent Query] = qt.text,
        [DatabaseName] = DB_NAME(qt.dbid),
        [Query Plan] = qp.query_plan
FROM    sys.dm_exec_query_stats qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
        CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY [Total IO] DESC --IO
-- ORDER BY [Total Elapsed Time In Seconds] DESC  --elapsed time
-- ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

How to: Check the Status of E-Mail Messages Sent With Database Mail?

use msdb
go

select * from sysmail_allitems

Defragment all indexes on Database

It finds the Indexes on Database which need to be De-fragmented. You just need to replace 'database_name' with your database name. Include this Stored Procedure in to your Scheduled JOB, schedule it whenever you want.

/***************************************************************************************
  
----------------------------------------------------------------------------------------
SP to Alter INDEXES for Defragmentation
----------------------------------------------------------------------------------------
--Exec USP_AlterIndex_Defragment
***************************************************************************************/

CREATE PROCEDURE [dbo].[USP_AlterIndex_Defragment]
AS   
BEGIN        
DECLARE @TableName VARCHAR(200)      
DECLARE cur_index CURSOR FOR       
SELECT  DISTINCT ss.NAME +'.'+        
OBJECT_NAME(dt.object_id) Tablename         
FROM    ( SELECT    object_id ,      
index_id ,      
avg_fragmentation_in_percent ,       
fragment_count,       
avg_fragment_size_in_pages,       
page_count,      
avg_page_space_used_in_percent      
FROM      sys.dm_db_index_physical_stats(DB_ID('HMY_TEST'),      
NULL, NULL, NULL,      
'DETAILED')       
WHERE     index_id <> 0       
) AS dt       
INNER JOIN sys.indexes si ON si.object_id = dt.object_id        
AND si.index_id = dt.index_id       
AND dt.avg_fragmentation_in_percent > 10        
--AND dt.avg_page_space_used_in_percent < 75       
INNER JOIN sys.objects so ON so.object_id = dt.OBJECT_ID        
INNER JOIN sys.schemas ss ON ss.schema_id = so.schema_id        
ORDER BY Tablename DESC         
OPEN cur_index        
FETCH NEXT  
FROM cur_index   
INTO @TableName         
WHILE @@FETCH_STATUS = 0            
BEGIN               
EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR=90,ONLINE=OFF)')                 
FETCH NEXT  
FROM cur_index  
INTO @TableName           
END       
CLOSE cur_index ;       
DEALLOCATE cur_index ;   
END

Identify orphaned (no longer targeted to a database) SQL Server Agent jobs after dropping a database

When a database is dropped, the jobs remain active in the SQL Server Agent and if a schedule is associated to the job, failures would be reported whenever the jobs are automatically executed. As part of the standard process that I follow whenever I drop a database, I run the following script to identify SQL Server Agent jobs which are no longer targeted to any database.

USE msdb;
GO
--Query to identify orphaned jobs!
SELECT sj.database_name AS OriginalTargetDBName,      
sj.job_id AS JobId,       
sj.step_id AS JobStepId,       
sj.step_name AS StepName,      
sj.subsystem AS SubSystem,       
sj.command AS JobStepCommand,       
sj.last_run_date AS LastExecutionDate
FROM msdb.dbo.sysjobsteps AS sj
WHERE DB_ID(sj.database_name) IS NULL AND   --If the database no longer exists,                                         
--DB_ID() would return NULL  
sj.database_name IS NOT NULL
GO

When was Index Maintenance (rebuild/reorganize) last performed on your database?

USE
AdventureWorks2008R2

GO


SELECT
ss.name AS SchemaName,

st.name AS TableName,

s.name AS IndexName,

STATS_DATE
(s.id, s.indid) AS 'Statistics Last Updated',

s.rowcnt AS 'Row Count',

s.rowmodctr AS 'Number Of Changes',

CASE
WHEN s.rowmodctr > 0

THEN
CAST ((CAST (s.rowmodctr AS DECIMAL (28, 8)) / CAST (s.rowcnt AS DECIMAL (28, 2)) * 100.0) AS DECIMAL (28, 2))

ELSE
0

END
AS '% Rows Changed'

FROM
sys.sysindexes AS s

INNER
JOIN

sys
.tables AS st

ON
st.[object_id] = s.[id]

INNER
JOIN

sys
.schemas AS ss

ON
ss.[schema_id] = st.[schema_id]

WHERE
s.id > 100

AND
s.indid > 0

ORDER
BY SchemaName, TableName, IndexName

Remotely Reboot a Server that is Locked Up

Occasionally a server is locked up and you can't even log into the server. In those cases you either have to physically reboot the machine (difficult to do when the server is located in an out-of-state facility) or issue a remote reboot command.

shutdown /r /f /m \\SVRNAME_OR_IP

Also you can try another option

In CMD prompt type Shutdown - i.

The following window will open. Click on Add and type the server IP you want to reboot,then click on ok. It will reboot the server.

Enable Change Tracking on all Tables in a Database

The following script is definitely a sandbox script. It will enable Change Tracking on every table in your database, so please do not try this in a production without first understanding the impact.
First, enable Change Tracking at the database level.

USE [master]
GO
ALTER DATABASE [YOUR_DATABASE] SET CHANGE_TRACKING = ON
 (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)
GO

Ensure your Hard Drives Don't Run out of Free Space

DECLARE
@MinSpace int =1024 --less than 1GB

DECLARE
@Drives TABLE (

[drive] CHAR(1) NOT NULL PRIMARY KEY CLUSTERED,

[MBfree] VARCHAR(10) NOT NULL )

INSERT
INTO @Drives EXEC xp_fixeddrives

SELECT
* FROM @Drives

WHERE
MBfree < @MinSpace

GO

Kill all users and set single user mode

USE master;
 GO
 ALTER DATABASE AdventureWorksLT2008R2
 SET SINGLE_USER
 WITH ROLLBACK IMMEDIATE;
 GO
 ALTER DATABASE AdventureWorksLT2008R2
 SET MULTI_USER;
 GO

What is Kerberos?

Windows authetical protocol

Pre 2000, uses NTLM
Post 2000 Kerberos.

For Window’s login, passes authentication to Windows Security Support Provider Interface (SSPI)

SSPI is going to first try and authenticate using Kerberos.This is the preferred protocol for Windows 2000 and above. In order to do this, there needs to be a Service Principal Name (SPN)

A Service Principal Name (SPN) provides the information to the client about the service. Basically, each SPN consists of 3 or 4 pieces of information:
The type of service (for SQL Server it is called MSSQLSvc)
The name of the server
The port (if this needs to be specified)
The service account running the service.

NTLM suffers from the following drawbacks:

It is susceptible to “replay” attacks.
It assumes the server is trustworthy.
It requires more authentication traffic than Kerberos.
It doesn’t provide for a means of going past that first hop.

Kerberos:

only within time limit ex.10 sec.
Checks SPN with AD
Only once to issue ticket. Tickets expire after 10hr
Can multiple hoops

Getting connection information

SELECT
s.session_id
, c.connect_time
, s.login_time
, s.login_name
, c.protocol_type
, c.net_transport
, c.auth_scheme
, c.num_reads
, c.num_writes
, s.HOST_NAME
, s.program_name
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id

What happened when query was submitted?

1. The SQL Server Network Interface (SNI) on the client established a connection to the SNI on the SQL Server using a network protocol such as TCP/IP. It then created a connection to a TDS endpoint over the TCP/IP connection and sent the SELECT statement to SQL Server as a TDS message.
2. The SNI on the SQL Server unpacked the TDS message, read the SELECT statement, and passed a “SQL Command” to the Command Parser.
3. The Command Parser checked the plan cache in the buffer pool for an existing, usable query plan. When it didn’t find one, it created a query tree based on the SELECT statement and passed it to the Optimizer to generate a query plan.

4. The Optimizer generated a “zero cost” or “trivial” plan in the pre-optimization phase because the statement was so simple. The query plan created was then passed to the Query Executor for execution.
5. At execution time, the Query Executor determined that data needed to be read to complete the query plan so it passed the request to the Access Methods in the Storage Engine via an OLE DB interface.
6. The Access Methods needed to read a page from the database to complete the request from the Query Executor and asked the Buffer Manager to provision the data page.
7. The Buffer Manager checked the data cache to see if it already had the page in cache. It wasn’t in cache so it pulled the page from disk, put it in cache, and passed it back to the Access Methods.
8. Finally, the Access Methods passed the result set back to the Relational Engine to send to the client.

Did you know… When we rename a domain group in Active Directory, we can have some issues related to the login in SQL Server

The Active Directory group we used to grant access to a snapshot publication does not show up in the Publication access list (PAL) anymore when viewing the properties of the publication. However, the AD group does show up in the MSPublication_Access table for the snapshot publication in the Distribution database.

There is a disparity between what the Replication property settings and what is actually in the MSPublication_Access table. When we try to add the group to the PAL in the GUI, it doesn't give any error message and completes. But when we again check the PAL, it doesn't show up.

 CAUSE
The login was changed in the AD sometime back. The change was that the login was renamed and not deleted and recreated afterwards. So the SID remained same for the changed login in the AD. But because of this change in the AD, SQL Server was never aware of the change of the name. The login with the old name still existed in the server and mapped to the same SID. Then somehow the login was deleted and recreated at the SQL Server level. So when the login was created again at the SQL Server, the new name was created on the SQL Server but the same Old SID was associated with it.

But the entry for the login remained there in the mspublication_access table. This login was never removed during the deletion of the login from the SQL Server. Afterwards whenever they used GUI to check the PAL, it was unable to find the login name from the SQL Server logins and hence didn't show up in the PAL GUI. But whenever they tried to add the login in the PAL, it was able to find an existing record with the same SID and hence failed to add the same. But it never gave any error message.

RESOLUTION
Delete the record manually from the MSPublication_Access table in distribution database. Then again add the login to the SQL Server and PAL.

The subscription fails to Sync with the Publisher and marked inactive even though the settings have been configured to “never expire”

Sometimes we get the below error message when distribution agent tries to sync,

Error: 14151, Severity: 18, State: 1.The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.

CAUSE:When we check the Publication properties, we find that the options for subscription expiration are marked as "Subscriptions never expire".

When we check the sql server error log, we see that the following error is reported in the error log,
Message in Errorlog :-Replication-Replication Distribution Subsystem: agent Servername\Instname-Directory-Directory_R-Servername\Instname-8 scheduled for retry. Query timeout expiredError: 14151, Severity: 18, State: 1.Replication-Replication Distribution Subsystem: agent Servername\Instname-Directory-Directory_R-Servername\Instname-8 failed. The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.Error: 14151, Severity: 18, State: 1.Replication-Replication Distribution Subsystem: agent Servername\Instname-Directory-Directory_R-Servername\Instname-8 failed. The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.Error: 14151, Severity: 18, State: 1.Replication-Replication Distribution Subsystem: agent Servername\Instname-Directory-Directory_R-Servername\Instname-8 failed. The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.
From the above error log it is clear that the Subscriber was deactivated due to some reason.

So in order to find the cause of deactivation we need to check the Transaction Retention period in the Distribution properties. If it is set to a lower number like 3 hours, then this indicates that if the subscriber does not synchronies in 3 hours, the distribution cleanup agent will truncate the commands tables in distribution and hence the subscriber will be marked as deactivated.Since the commands are deleted from distributor there is no other option but to reinitialize the subscriber.

****Also to find the cause of the Distributor not being able to sync we can check the Distribution history in the below tables on distribution database,

msdistribution_agents
msdistribution_history

Log Reader Agent Fails when the Sql server instance is failed over to Node 2

In some cases where we use sql server replication on a clustered instance of sql server and when we try to failover from Node 1 to Node 2 the Log Reader agent may fail to start with the following error

Error messages:

The process could not execute 'sp_MSpub_adjust_identity' on 'CRPSCSMSQ69V1\PUB'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)

Get help: <<http://help/MSSQL_REPL20011>>

 An error occurred during decryption. (Source: MSSQLServer, Error number: 15466)

Get help: <<http://help/15466>>

The process could not execute 'sp_MSpub_adjust_identity' on 'CRPSCSMSQ69V1\PUB'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)

Get help: <<http://help/MSSQL_REPL22037>>

As seen above the root cause of the issue is the error

“An error occurred during decryption”

Which mean when the sql server instance fails over to Node 2 it is not able to decrypt the stored procedure sp_MSpub_adjust_identity due to which it is not able to execute the stored procedure sp_MSpub_adjust_identity which in turn causes Log Reader Agent to fail on Node 2. It was not able to decrypt the stored procedure as the Service Master Key on Node 2 was different than the Service Master Key on Node 1.

The Service Master Key is the root of the SQL Server encryption hierarchy. It is generated automatically the first time it is needed to encrypt another key. By default, the Service Master Key is encrypted using the Windows data protection API and using the local machine key. The Service Master Key can only be opened by the Windows service account under which it was created or by a principal with access to both the service account name and its password. Regenerating or restoring the Service Master Key involves decrypting and re-encrypting the complete encryption hierarchy.

Service Master Key automatically encrypts all secure system data such as:
Database Master Keys
Linked Server passwords
Provider strings stored in sysservers
Credentials Secrets

So we can resolve the issue in following ways

Solution 1

One of the ways to resolve the above error is to backup the service master key from Node 1 and restore the same on Node 2

BACKUP SERVICE MASTER KEY TO FILE = 'path_to_file' ENCRYPTION BY PASSWORD = 'password' ---------------- (Use this command to backup the service master key on Node 1 )

RESTORE SERVICE MASTER KEY FROM FILE = 'path_to_file' --------------------------- (Use this command to restore the service master key on Node 2)
DECRYPTION BY PASSWORD = 'password' [FORCE]

Solution 2

Another solution is to regenerate the service master key on Node 2 with Force option using the following command

ALTER SERVICE MASTER KEY REGENERATE FORCE

This solution should be used with precaution because in this case when you fail back to Node 1 the Log Reader agent will fail again with the same error.
Again, when we regenerate the Service Master Key forcibly, the Linked servers on the server can fail as the password for the security logins cannot be decrypted and hence we may have to recreate the Linked servers or change the password for the Linked servers which can be tedious and undesirable.

Solution 3

The above issue occurs since the service account of the clustered instance of sql server was changed using the Windows services console (services.msc). For a clustered instance of the sql server we recommend changing the service account using the sql server configuration manager because it decrypts all the secure system data using the old service master key, generates a new service mastery key based on the new startup account and re-encrypt all the system data with the new service master key. It also checkpoints all the information in the quorum so that the changes gets replicated to Node 2 when the sql server instance is failed over to Node 2. Hence the above error does not occur when the sql server startup account is changed using sql server configuration manager. When we use sql server configuration manager to change the service account for sql server we are sure that the same account will be used as sql server startup account on all the nodes of the cluster.

So to resolve the above error we need to change the Service Account to another account using the Configuration Manager on Node 1 and try to failover on Node 2 and check whether the Log Reader Agent worked fine. We can then change the Service Account back to the original account using the Configuration manager and try to failover again and the replication should work fine.

We can refer to the following links to determine the what type of service accounts should be used and how to change the service account for Sql server instance in a cluster

Reference

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

http://support.microsoft.com/kb/254321/

http://support.microsoft.com/kb/239885/

SQL Server could not create a subscription for Subscriber 'Servername\InstanceName'.The value for the @sync_method parameter is not valid.

Creation of a Subscription was failing with the following error:-

TITLE: New Subscription Wizard   
SQL Server could not create a subscription for Subscriber 'Servername\InstanceName'.    
 
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)    
   
The value for the @sync_method parameter is not valid. Could not subscribe to publication 'Publication_name' because non-SQL Server Subscriber 'Servername\InstanceName' only supports values of 'character', 'bcp character', 'concurrent_c', and 'database snapshot character' for the @sync_method parameter.
The subscription could not be found.
Changed database context to 'test'. (Microsoft SQL Server, Error: 14095)
Cause: The issue is due to an incorrect entry in system table (master..sysservers) due to which SQL thinks that current subscriber
is NON-SQL. Interestingly the entry for the current subscriber server in master..sysservers (on publisher server) was showing 1 for "nonsqlsub" column.
Solution:
First look at sys.servers and confirm whether the nonsqlsub column is set to 1 for the concerned subscriber. If the above is true then run the following TSQL to change nonsqlsub column from 1 to 0

exec sys.sp_serveroption 'Servername\InstanceName','nonsqlsub',False

Note – Please replace Servername\InstanceName with concerned subscriber\instancename. After this, re-attempt the creation of subscription and it should succeed this time.

References: Sys.servers :- http://msdn.microsoft.com/en-us/library/ms178530.aspx

Failure of SQL Server 2008 Installation due to MSI error

Issue:

Installation of SQL Server 2008 might fails with below error on 64-Bit Operating system.

MSI Error: 1719 The Windows Installer Service could not be accessed. This can occur if you are running Windows in safe mode, or if Windows Installer is not correctly installed. Contact your support personnel for assistance.

Resolution:
Delete HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\msiserver\WOW64

Run command: c:\windows\system32>misexec /regserver
Run command c:\widnows\syswow64>msiexec /regserver
Restart Windows Installer Service.

Root Cause:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\msiserver\WOW64 with REG_DWORD = 1 is used to make the installer run in 32 bit mode on 64 –Bit systems assuming windows is 32-bit. This is helpful in running installers that are not compatible with 64bit OS

However, most of the 32bit installers are compatible with 64bit OS and do not need this value to 1. When the value of WOW64 is set 0 it returns the actual OS architecture to the installer. If set to 1 then it returns OS architecture as 32-Bit making Windows Installer to run in 32-bit mode.

Error Messages and the Solutions Related to “distributor_admin” login

Error messages:

The log reader fails with the following error message:       
The process could not execute 'sp_MSpub_adjust_identity' on 'PR2K8\YUKON'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
Login failed for user 'distributor_admin'. (Source: MSSQLServer, Error number: 18456)
Get help: http://help/18456

Snapshot Agent may fail with this error:  

Could not retrieve agent status. (Login failed for user 'distributor_admin'. (.Net SqlClient Data Provider))

Distribution agent may fail with this error message:       
An error occurred while attempting to access the subscription. (View Synchronization Status)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Rmo)
------------------------------
Login failed for user 'distributor_admin'. (Microsoft SQL Server, Error: 18456)
------------------------------
For all the above error messages, the cause of the problem may be that the distributor_admin login password does not match with the password set on the distributor server.

Solution:       
If you know the password of distributor_admin login on the distributor server, then set the same password on the Publisher Server using stored procedure sp_changedistributor_password
If you don’t know the password for distributor_admin login on the Distributor Server then you need to follow the below steps:
1. On the distributor server, run SP sp_changedistributor_password with the new password that you would like to set.
Example: sp_changedistributor_password 'newpassword'
Or, you can also use the Distributor Properties window on the Distributor Server((Right click on the Replication folder and click Distributor Properties) to change the Distributor_Admin password as shown in the below figure:

                                                      

 

 

2. On all the remote publisher server/s, run SP sp_changedistributor_password using the password specified in the first step.
Or, you can also use the Publisher Properties window on the Publisher Server to change the distributor_admin password (Right click on the Replication folder and click Publisher Properties) as shown in the below figure:

                                                         

Information about distributor_admin login in SQL Server and why it is needed in replication       
Distributor_admin is a SQL Server login account which is used by the repl_distributor linked server (remote server) in SQL Server replication. This login is created on the Distributor Server and the administrator specifies the password for the distributor_admin login (for the Remote Distributor Server). The same password is then needed at the remote publisher when distribution is configured from the remote publisher server. This helps in securing the Distributor Server so that none of the servers in the topology can use the Distributor Server for distribution purpose without specifying the distributor_admin password. Therefore, the distributor_admin password works as a key for the Publisher Server to enable itself to use the remote server as its distributor server.

What is Local and Remote distributor
local distributor - distribution database is present within publisher instance
remote distributor - distribution database is present on the remote SQL Server instance and not on the publisher instance
Configuring a Local Distributor :      
When you run the "Configure distribution" wizard on the Distributor Server for local distributor, the following gets created on the Distributor Server:
1. distributor_admin (SQL Server login account) is created on the SQL Server Distributor Server and the password is generated automatically.
2. Also, the linked server (remote server) named repl_distributor is created on the SQL Server Distributor Server which uses the distributor_admin login credentials for the remote server connections.

Configuring a Remote Distributor :      
Configuring a Remote Distributor is done in two steps:
A. When you run the "Configure distribution" wizard on the Distributor Server for remote distributor, you specify the remote publisher and the following gets created on the Distributor Server:
1. Distributor_admin (SQL Server login account) is created on the SQL Server Distributor Server and the password is specified by the Administrator.
2. Also, the linked server (remote server) named repl_distributor is created on the SQL Server Distributor Server which uses the distributor_admin login credentials for the remote server connections
B. You then go to the Publisher Server and run the “configure distribution” wizard and specify the distributor server along with the distributor_admin login password (same password that you specified in Step A-1).
The repl_distributor linked server is used for the following purposes:
1. Used by the replication stored procedures and functions that require processing on the publication database as well as the distribution database.For example: When you create the publication using wizard or using SP sp_addpublication an entry is made in syspublications table on the publication database and details about this publication are also inserted in the mspublications table in the distribution database.
This linked server is used regardless of local distributor or for the remote distributor
2. Used to fetch the information from the distribution database like replication agent status:
For example: Information about distribution agent and log reader agent status from msdistribution_history, mslogreader_history tables from the distribution database
Whenever you add the first remote publisher on the distributor server, the password for distributor_admin login is required. When you add the subsequent remote publisher on the Distributor server, the same password is used that was used for the first publisher.
Also, when you configure distribution from the remote publisher, distributor_admin password is required.

Please note that the login distributor_admin should never be deleted. To change the password for the distributor_admin login, always use the SP sp_changedistributor_password on the Distributor server as well as on the remote publisher server/s or use the above GUI screens.

Extended Support for SQL Server 2000 To End on Tuesday,9th April 2013.

SQL Server 2000 will lose its "extended support" from Microsoft on Tuesday, April 9,

The loss of extended support means no more security updates will be delivered to customers via Microsoft's update service. Remedies for organizations still using SQL Server 2000 are to upgrade the product (Microsoft is recommending moving to its current SQL Server 2012 product) or pay for "custom support" through Microsoft Premier Support services, according to a Microsoft announcement.

It's possible to continue to run SQL Server 2000 after April 9, but that approach isn't recommended by Microsoft for security reasons. Self-help resources, such as Knowledge Base articles and troubleshooting tools, will continue to be available online for a minimum of 12 months.

Upgrading from SQL Server 2000 to SQL Server 2005 maybe isn't such a good idea because that product already entered the extended support phase, which happened on April 12, 2011. Extended support for SQL Server 2005 will end on April 12, 2016, according to Microsoft's lifecycle page.

Support Lifecycle Index of All Microsoft Product

http://support.microsoft.com/gp/lifeselectindex

Support options for organizations still using the older products, such as SQL Server 2000 and SQL Server 2005, can be found here. Microsoft also has this general SQL Server support portal, which provides links to various resources.

Support for Microsoft SQL Server

http://support.microsoft.com/ph/1044