SQL Server 08 monitoring and common system tables

/*  

The reliability and performance monitor is a standard tool used by sql08 to monitor servers. It has updated the report counter for SQL Server.
You can use these counters to track many different server resources and activities.

SQL Server Profiler is an analysis and optimization tool that can be used to track server events.

  */ 

Sp_who
 --  Report the current user and process. When executing sp_who, you can pass the login name as a parameter. If no logon name is specified and a null value is passed in this parameter, all results are returned.  
--  If the keyword "active" is used as the login name, only the active process is displayed. All the processes waiting for the user's next command are excluded.  
--  You can also use the id value of a system process instead of a specific login name such as SA.  
 

User Login prohibited
Use Master
 Go 

 Alter Login SA disable

 --  Display database information  
  Select   *   From SYS. database_files


 --  Display the capacity of the current database table  
  Use Test
Go 

 Select So. Name As   '  Tablename  ' , So. type, Sum (Row_count) As   '  Totalrows  '  
 From SYS. dm_db_partition_stats As PS Inner  Join SYS. Objects As So
 On PS. Object_id  = So. Object_id  
 Where Index_id <=  1   And So. Type =  '  U  '  
Group   By So. Name, so. Type
 Order   By   Sum (Row_count) Desc  


 --  --------------------------------------------------------------------  
/* 
DBCC statement: This command set is used to check SQL server statistics, trace activities, and check database integrity.
Sp_helpdb this stored procedure displays information about the database
Sp_helpindex this stored procedure reports information about indexes on tables or views
Sp_helpserver this stored procedure provides the information configured for remote access and replication of SQL Server instances
Sp_monitor this stored procedure displays important SQL server usage statistics, such as CPU idle time and CPU usage

Sp_spaceused: displays the estimates of the disk space used by the table, index view, or service broker queue in the current database.

Sp_who: This stored procedure displays the snapshot information of the current SQL Server user and Process

SYS. dm_tran_locks this dynamic management view displays information about Object locks.


-- Copy monitor:
Publishing Server View:
1. Release: independent projects are displayed for each configuration release. The icon displays the type and status of the release.
A. There is a blue circle of Purple Book icon inside, which indicates snapshot copying.
B. The Blue Book icon with a green right arrow inside indicates transaction Replication
C. The Yellow Book icon with a green left arrow and a blue right arrow inside indicates merging and copying
D. A red circle around X indicates the error status.
2. The subscription Monitoring List displays the status of each subscription by type. Use the first drop-down list to specify the subscription type to be displayed, and use the second drop-down list to specify that all subscriptions of the specified type are displayed,
Or display a subset.
3. The agent displays all SQL Server proxy jobs shared by publishing on the selected Publishing Server. To determine whether a replication problem is potential, pay attention to the status, last startup time, and duration.
Jobs in the 'unstarted' status or those that have been running for a long time may have problems.

  */ 

 -- The sys. Messages directory view in the master database contains a list of error messages and descriptions that can be returned by SQL Server.  
   --  To view all error messages that can be returned by SQL Server, execute the following T-SQL statement:  
   Use Master
 Go 

 Select   *   From SYS. Messages

 --  --------- ============================================ -------------------------- 
 

 --  -- Enable, disable, and unlock an account  
 
 --  Syntax:  
  Use Master
 Go 
 Alter Login '  Login  ' Disable | Enable | Unlock

 --  Disable Logon:  
  Use Master
 Go 

 Alter Login '  Galaxy \ wrstanek  ' Disable

 --  Enable Logon:  
  Use Master
Go 

 Alter Login '  Galaxy \ wrstanek  ' Enable

 --  Unlock logon usage  
  Use Master
 Go 

 Alter Login '  Galaxy \ wrstanek  ' Unlock
 Go 


 --  ----------------------------------  
 
 --  Delete Logon  
 Syntax:
 Drop Login '  Login  ' 

Usage:
 Drop Login'  Galaxy \ wrstanek  '