Lesson 12

This module discussed how to monitor SQL Server databases using SQL and SSMS.
Now you should be able to:
Here's a task-by-task breakdown showing how you can accomplish each using SQL Server Management Studio (SSMS) in SQL Server 2022, with modern best practices and notes on legacy commands like `sqlmaint.exe`.
✅ 1. Monitor the Size of a Database
How to do it in SSMS (2022):

• GUI Method:
  • Right-click the database → Reports → Standard Reports → Disk Usage.
  • You can also use Disk Usage by Top Tables.
• T-SQL Method:

  • EXEC sp_spaceused;

• Advanced (per file):

  • SELECT name, size * 8 / 1024 AS SizeMB 
    FROM sys.master_files 
    WHERE database_id = DB_ID('YourDatabaseName');

✅ 2. Use `DBCC` to Monitor Databases How to do it in SSMS (2022): * Use the Query Editor to run DBCC commands such as:

  DBCC SQLPERF(LOGSPACE);  -- Transaction log size and usage
  DBCC MEMORYSTATUS;       -- Memory usage details
  

Note: Many `DBCC` monitoring commands have been replaced with DMVs like `sys.dm_os_memory_clerks`.
✅ 3. Monitor the Consistency of Individual Tables
How to do it in SSMS (2022):

• Run in Query Editor:

  DBCC CHECKTABLE ('dbo.YourTableName');

• Alternative for full DB:

  DBCC CHECKDB ('YourDatabaseName');

Common DBCC commands to run via SSMS:

Command	Purpose
DBCC SHOWCONTIG	❌ Deprecated — use sys.dm_db_index_physical_stats
DBCC INPUTBUFFER(spid)	View last statement for a session
DBCC OPENTRAN	View oldest open transaction
DBCC SQLPERF	View log space usage

🔍 *Prefer DMVs for modern monitoring.*
✅ 5. Monitor the Transaction Log
T-SQL in SSMS Query Window:

SELECT 
    DB_NAME(database_id) AS DatabaseName,
    total_log_size_in_bytes / 1024 / 1024 AS TotalLogSizeMB,
    used_log_space_in_bytes / 1024 / 1024 AS UsedLogSpaceMB,
    used_log_space_in_percent
FROM sys.dm_db_log_space_usage;

Or:

DBCC SQLPERF(LOGSPACE);

How to update statistics in SSMS:

• Right-click table → Properties → Storage → Update Statistics
• T-SQL:

  
      UPDATE STATISTICS dbo.YourTableName;
      

• Whole database:

  
      EXEC sp_updatestats;
      

✅ 7. View Statistics Information Run in Query Editor:

DBCC SHOW_STATISTICS ('dbo.YourTableName', 'IndexOrStatName');

Or query metadata:

SELECT * FROM sys.stats WHERE object_id = OBJECT_ID('dbo.YourTableName');

⚠️ 8. Run `sqlmaint.exe`
❌ Deprecated in SQL Server 2005+
Replaced by Maintenance Plans and SQL Server Agent Jobs.
Use this instead:

• In SSMS: Management → Maintenance Plans → New Maintenance Plan
• Automates tasks like:
  • Backup
  • Reorganize/Rebuild Indexes
  • Check DB Integrity
  • Update Statistics

Steps in SSMS:

1. Expand Management → Right-click Maintenance Plans → Choose Maintenance Plan Wizard.
2. Choose tasks like:
   • Backup Database
   • Rebuild Index
   • Update Statistics
   • Check Database Integrity
   • Cleanup History
3. Assign to SQL Server Agent Schedule.

🧠 Summary

Task	Modern Method in SSMS
Monitor DB size	Reports → Disk Usage or sp_spaceused
Use DBCC	Run via Query Editor (some commands deprecated)
Table consistency	DBCC CHECKTABLE
Misc DBCC monitoring	DBCC SQLPERF, DBCC OPENTRAN, DBCC INPUTBUFFER, etc.
Monitor transaction log	sys.dm_db_log_space_usage or DBCC SQLPERF(LOGSPACE)
Maintain statistics	UPDATE STATISTICS or sp_updatestats
View statistics	DBCC SHOW_STATISTICS or sys.stats
Run sqlmaint.exe	❌ Deprecated → use Maintenance Plans in SSMS
Database Maintenance Plan Wizard	Found under Management in SSMS GUI

The following terms were introduced to you in this module:

1. Extent: SQL Server allocation unit. An extent is 8 data pages.
2. Fragmentation: A condition that occurs when data is not stored consecutively.
3. Leaf page: The lowest level of an index.
4. Single user mode: A database that is configured to allow only one user to access it.
5. Statistics: Track the data distribution.

The next module will cover how to use SQL Server Profiler to monitor SQL Server.

Before you continue, click the Exercise link below to check your understanding of monitoring and maintaining databases.
Develop Database Maintenance Plan - Exercise