sp_spaceused and other MS SQL Server Dictionary views

Many times I am asked to look at Microsoft SQL Servers to investigate things, and I find the following T-SQL procedures and Microsoft SQL Server dictionary views very handy.


To find out how much disk space you Microsoft SQL Server database consumes and other things, you can run the following T-SQL procedures inside the Query Analyzer.


sp_spaceused @updateusage = 'TRUE'


To see quickly how many stored procedure, views, tables and other objects your database has you can use this query here

See table disk space usage

sp_spaceused 'Tablename'


See all tables

select *
from INFORMATION_SCHEMA.tables


See all foreign key constraints

select *
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS


See foreign keys, primary keys of a particular table

select
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE


See the foreign key of a particular table.


select table_name, column_name "foreign key", constraint_name
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
where TABLE_NAME='put_table_name_here'
and constraint_name not like 'PK%'



Find which MS SQL Server Stored procedure is using a particular table, or in other words dependencies between stored procedures and tables.


SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%put_table_name_here%' -- enter table name here.




For more info go to: http://msdn.microsoft.com/en-us/library/aa933209(SQL.80).aspx

Service Level Agreement (SLA) figures

How does downtime affect your Service Level Agreement (SLA) with your customer?

What does 99.99999 % uptime per year mean?


If you want answers to these questions you have too look at the availability levels below. They show how many minutes, seconds downtime/system outage you are allowed to have in a year, to meet certain SLA figures and your contractual obligations.

Level of availability         Total downtime per year
=====================         ======================
99%                           3.6 days
99.9%                         8.76 hours 
99.99%                        52 minutes 
99.999%                       5 minutes 
99.9999%                      30 seconds 
99.99999%                     3 seconds