Finding Out DirtyPages in the Databases.

SELECT db_name(database_id) AS 'Database',count(page_id) AS 'Dirty Pages'

FROM sys.dm_os_buffer_descriptors

WHERE is_modified =1

GROUP BY db_name(database_id)

ORDER BY count(page_id) DESC

Query for Finding Blocking

(SELECT * FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses))
SELECT 
spid, status, loginame=substring(loginame, 1, 12),
hostname=substring(hostname, 1, 12),
blk=CONVERT(char(3), blocked),
open_tran,
dbname=substring(db_name(dbid),1,10),cmd, 
waittype, waittime, last_batch
FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked=0 

Replication Commands

sp_helpdistributor
--By Running this command in the Publisher serer we can find the Distributor server name

sp_browsereplcmds
--run this command under Distribution database,Under distributor server

Best Practices to follow Before Installing SQL Server Service Pack

1)Perform a full backup of all user,system and Resource databases.
2)Note down of Important SQL Server configuration settings,Start Up parameters,Linked Servers and Script out SQL Server Agent Jobs,Script Out SQL Server Logins,Memory Utilization,CPU and Disk Utilization.
3)Create an appropriate service pack Deployment and Rollback Plan
4)Always Install Service Packs first In Development environment and then test all applications which are using by SQL Server
5)Once everything looks good in development environment then only install service pack in QA environment and also test your rollback plan
6)If everything good in development and QA Environment then plan to install service pack in a Production Environment by communicating an appropriate downtime window to the stake holders and database application users.
7)Once the service pack installed successfully in a production environment perform sanity checks to confirm all applications are working fine.
8)Reboot sql server once service pack/Cumulative Updates or Security Updates are applied successfully on the server.
9)If every thing looks good then release the production environment for user activities and monitor the environment closely for a week or two to make sure there are no unusual spikes in CPU and Memory Utilization.

Push Vs Pull Subscriptions in Replication

Push Subscription: Runs all agents at the Distirbutor.
This option makes it easier to administer the synchoronization of subscriptions centrally.

Pull Subscription: Runs each agent at its subscriber.
This option reduces the processing overhead at the distributor and lets each Subscriber administer the synchronization of its subscription.

The mail could not be sent to the recipients because of the mail server failure. And Mail is shutting Down

Some times you may get the above error while you are install SQL Mail Successfully and testing it form TestMail. Then you need to look at the below issues.
Go to the your Mail Server
Click Run--> InetMgr-->In the InetMgr You can find DEFAULT SMTP SERVICES(If this not there need to be installed. If this is not there in that server you can not use this as @mailserver_name in msdb.dbo.sysmail_add_account_sp procedure)-->Right Click-->Properties-->Access-->Give ip address there in Connection and Relay.

List of known issues when you install SQL Server 2005 on Windows Server 2008

The below link is describing about List of known issues when you install SQL Server 2005 on Windows server 2008
http://support.microsoft.com/kb/936302