How to monitor replication subscription errors

 One of my client has a big setup Server for SQL Server. He also has a lot of transactional replication and monitoring its is a really problem for him. Frequently he has problem of out of sync replications or failed replication

SQL Server Downloads

As we are using SQL Server , we have to do number of download related to SQL Server
I have tried to gather all details together for us.

SQL Server 2008 R2

SQL Server 2008 R2 Books Online

SQL Server 2008 Evaluation edition

SQL Server 2008 Books Online

SQL Server 2008 upgrade Advisor

SQL Server 2008 SP1

SQL Server 2008 SP2

DMVs for Memory

Today we will look into DMVs related to memory

First we will look into sys.dm_os_sys_memory which will give us memory related basic informatio for the system
This will give us detail for physical memory, available memory , toatl page file and available page file and
high/low memory status. If  memory status is low then it means system is facing memory pressure.

SELECT total_physical_memory_kb / ( 1024.0 * 1024 )     total_physical_memory_gb,

       available_physical_memory_kb / ( 1024.0 * 1024 ) available_physical_memory_gb,

       total_page_file_kb / ( 1024.0 * 1024 )           total_page_file_gb,

       available_page_file_kb / ( 1024.0 * 1024 )       available_page_file_gb,

       system_high_memory_signal_state,

       system_low_memory_signal_state,

       system_memory_state_desc

FROM   sys.dm_os_sys_memory

dm_os_process_memory
This dmv is supprted on SQL 2008 and later versions
it provides some  more detailed information about memory in sql server
All details are in kb

SELECT physical_memory_in_use_kb,

       large_page_allocations_kb,

       locked_page_allocations_kb,

       total_virtual_address_space_kb,

       virtual_address_space_reserved_kb,

       virtual_address_space_committed_kb,

       virtual_address_space_available_kb,

       page_fault_count,

       memory_utilization_percentage,

       available_commit_limit_kb,

       process_physical_memory_low,

       process_virtual_memory_low

FROM   sys.dm_os_process_memory

This will give us physical memory , locked page allocation (should be 0 if AWE is not enabled) ,
total , reserved, committed and available virtual address space, page fault count , memory utilization percentage and process physical memory low if the server is facing memory pressure

xp_readerrorlog parameter detail

Recently we had  hardware and performance issues on SQL Server,with our client , so while monitoring the system we also looked at error log. We used xp_readerrorlog procedure.
This is really useful procedure and I feel I should share some information for this undocumented store procedures

use procedure as input in dataflow task

If we use procedure as  source in data flow task , this generally dont work in SSIS
so we have to pass

SET nocount OFF

SET fmtonly OFF

 before running the procedure


So sql command will be like this

SET nocount OFF

SET fmtonly OFF

EXEC Proc1

cheers :-)

Server details using DMV

Sometimes we need basic server  details , we can get this information from below DMV

SELECT *

FROM   sys.dm_os_sys_info

It provides valuable miscellaneous  hardware information which includes  number of logical cpu and hyperthread ratio for servers. Also provides information for physical memory , virtual memory  , buffer pool , sql server start time . In one of my previous article I have discussed how to get SQL server start time from tempdb
But from here we can get more accurate detail about sql server start time .

SELECT cpu_count                                         logical_cpu,

       hyperthread_ratio,

       cpu_count / hyperthread_ratio                     physcial_cpu,

       physical_memory_in_bytes / ( 1024 * 1024 * 1024 ) memory_in_gb,

       virtual_memory_in_bytes / ( 1024 * 1024 * 1024 )  virtual_memory_in_gb,

       bpool_committed                                   buffer_pool_committed,

       bpool_commit_target                               buffer_pool_commit_target,

       bpool_visible                                     buffer_pool_visible,

       sqlserver_start_time

FROM   sys.dm_os_sys_info