sys.dm_db_file_space_usage : tells us the total page count in the unallocated extents in tempdb
sys.dm_db_session_space_usage: tells us the total page count that is allocated/reserved for user objects in this session.in other words this reports page allocation and deallocation activity by session
sys.dm_db_task_space_usage:reports page allocation and deallocation activity by task.
sys.dm_db_partition_stats :Returns page and row-count information for every partition in the current database.
Tuesday, January 26, 2010
Important things to remember for any sql dba !
1 page=8 kb in size along with an inclusion of 96 byte header
1 mb=1024 kb=1024/8=128 pages=16 extents
1 page=how many rows ? find this by exec dbcc checkdb.
1 extent=8 contigous pages
1 mb=1024 kb=1024/8=128 pages=16 extents
1 page=how many rows ? find this by exec dbcc checkdb.
1 extent=8 contigous pages
Practical guide to Database Mail in sql server 2008 !
How to create a database mail account via t-sql ?
-------------------------------------------------
exec msdb.dbo.sysmail_add_account_sp
@account_name='account2',
@email_address='sudan.madhan@gmail.com',
@display_name='CASPRO Admin',
@replyto_address='sudan.madhan@gmail.com',
@description='This is the second account created for csfspd1caspro instance',
@mailserver_name='smtp.gmail.com',
@port=25,
@username='sudan.madhan@gmail.com',
@password='XXXXXXXX',
@enable_ssl=1
How to view all the database mail accounts ?
----------------------------------------------
exec msdb.dbo.sysmail_help_account_sp
How to create a new profile ?
----------------------------------------------
exec msdb.dbo.sysmail_add_profile_sp
@profile_name='profile2',
@description='this is my second profile'
How to view all the profiles ?
----------------------------------------------
exec msdb.dbo.sysmail_help_profile_sp
How to add account to the profile ?
=============================================
exec msdb.dbo.sysmail_add_profileaccount_sp
@profile_name='profile2',
@account_name='account2',
@sequence_number=1
--The sequence number determines the order in which accounts are used in the profile.
How to view the profiles and the accounts associated with it ?
==================================================================
exec msdb.dbo.sysmail_help_profileaccount_sp
How to grant access to a particular profile to all users ?
==========================================================
exec msdb.dbo.sysmail_delete_principalprofile_sp
@principal_name='public',
@profile_name='profile2'
--public here is the database role within msdb db.
How to view the principals that are given access to db mail profiles ?
======================================================================
exec msdb.dbo.sysmail_help_principalprofile_sp
How to start db mail ?
=========================
exec msdb.dbo.sysmail_start_sp
How to stop db mail ?
============================
exec msdb.dbo.sysmail_stop_sp
How to send an email ?
============================
exec msdb.dbo.sp_send_dbmail
@profile_name='profile2',
@recipients='sudan.madhan@gmail.com',
@subject='System administrator',
@body='This message is for testing purposes only',
@body_format='html'
How to find out if our db mail queue status is started or not ?
===============================================================
exec msdb.dbo.sysmail_help_status_sp
How to find out if there are any mail items in the queue waiting to be sent ?
=============================================================================
exec msdb.dbo.sysmail_help_queue_sp
--the length column gives us the clue if there are any pending items
How to view the configuration settings/properties of the db mail ?
===================================================================
exec msdb.dbo.sysmail_help_configure_sp
How to break the connectivity of a account and the associated profile ?
=================================================
exec msdb.dbo.sysmail_delete_profileaccount_sp
@profile_name='profile2',
@account_name='account2'
How to delete the account ?
============================
exec msdb.dbo.sysmail_delete_account_sp
@account_name='account2'
How to revoke the access to principals from a profile ?
===================================================
exec msdb.dbo.sysmail_delete_principalprofile_sp
@principal_name='public',
@profile_name='profile2'
How to delete the profile ?
============================
exec msdb.dbo.sysmail_delete_profile_sp
@profile_name='profile2'
What if any changes have to be made to either accounts or profiles ?
============================
Use the below stored procedures as per the situation :
exec msdb.dbo.sysmail_update_account_sp
exec msdb.dbo.sysmail_update_profile_sp
exec msdb.dbo.sysmail_update_profileaccount_sp
exec msdb.dbo.sysmail_update_principalprofile_sp
What are the catalog views available related to db mail ?
=========================================
The following are the views available : (these are self explanatory)
select * from msdb.dbo.sysmail_allitems
select * from msdb.dbo.sysmail_sentitems
select * from msdb.dbo.sysmail_unsentitems
select * from msdb.dbo.sysmail_faileditems
select * from msdb.dbo.sysmail_mailattachments
select * from msdb.dbo.sysmail_event_log
How to delete events from the Database Mail log (or) How to delete all events in the log or those events meeting a date or type criteria ?
=============================================
exec msdb.dbo.sysmail_delete_log_sp
@logged_before='2010-01-26'
exec msdb.dbo.sysmail_delete_log_sp
@event_type='error'
exec msdb.dbo.sysmail_delete_log_sp
--this deletes entire table
Give some example queries related to sending an db mail via t-sql syntax ?
==================================
1)
exec msdb.dbo.sp_send_dbmail
@profile_name='profile1',
@recipients='sudan.madhan@gmail.com',
@subject='Shift availability information',
@query='select * from HumanResources.shift',
@attach_query_result_as_file=0,
@execute_query_database='AdventureWorks'
2)
exec msdb.dbo.sp_send_dbmail
@profile_name='profile1',
@recipients='sudan.madhan@gmail.com',
@subject='Shift availability information',
@query='select * from HumanResources.shift',
@attach_query_result_as_file=1,
@execute_query_database='AdventureWorks',
@query_result_separator='|',
@query_result_width=100
How to create a sql server agent job to archive database mail messages and event logs ?
=====================================
job name : dbmail_archives
job description : This job is required to run first day of the month and archives the data from db mail event log,attachments and allitems into 3 separate tables.
job category : Database Maintenance
no of steps in this job : 4
job1 : Clean the existing table
in destiny db,
if exists(select name from sys.objects where name
in('dbmail_event_log_archives','dbmail_attachements_archives','dbma
il_allitems_archives'))
begin
drop table dbmail_event_log_archives;
drop table dbmail_attachements_archives;
drop table dbmail_allitems_archives;
end
job2 : archiving data into dbmail_event_log_archives
in msdb database,
select *
into destiny.dbo.dbmail_event_log_archives
from msdb.dbo.sysmail_event_log
job3 : archiving data into dbmail_attachements_archives
in msdb database,
select *
into destiny.dbo.dbmail_attachements_archives
from msdb.dbo.sysmail_attachments
job4 : archiving data into dbmail_allitems_archives
in msdb database,
select *
into destiny.dbo.dbmail_allitems_archives
from msdb.dbo.sysmail_allitems
job5 : cleaning the sysmail events,attachments and allitems
in msdb database,
delete from dbo.sysmail_allitems;
delete from dbo.sysmail_attachments;
delete from dbo.sysmail_event_log;
Schedule this job to run every month 1st day.
Why do we need to archive data into a separate database from this msdb database ?
=============================================
How to configure sql server agent mail to use database mail ?
=========================================
Rt click sql server agent->click properties->click alert system->check the box 'Enable Mail Profile'->choose db mail as mail system and choose the profile u want as the mail profile list->click ok->restart sql server agent.
**************************
Troubleshooting Guide :
***************************
1) Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.sp_send_dbmails'
Fix : User must have executed under different db context other than msdb.Hence execute this stored procedure under msdb db or use 3 part name as "exec msdb.dbo.sp_send_dbmail ....."
2) Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42
profile name is not valid
Fix : User is trying to send email using the sp with a profile name that actually doesnt exists.So check the profile name properly and make sure it exists.
-------------------------------------------------
exec msdb.dbo.sysmail_add_account_sp
@account_name='account2',
@email_address='sudan.madhan@gmail.com',
@display_name='CASPRO Admin',
@replyto_address='sudan.madhan@gmail.com',
@description='This is the second account created for csfspd1caspro instance',
@mailserver_name='smtp.gmail.com',
@port=25,
@username='sudan.madhan@gmail.com',
@password='XXXXXXXX',
@enable_ssl=1
How to view all the database mail accounts ?
----------------------------------------------
exec msdb.dbo.sysmail_help_account_sp
How to create a new profile ?
----------------------------------------------
exec msdb.dbo.sysmail_add_profile_sp
@profile_name='profile2',
@description='this is my second profile'
How to view all the profiles ?
----------------------------------------------
exec msdb.dbo.sysmail_help_profile_sp
How to add account to the profile ?
=============================================
exec msdb.dbo.sysmail_add_profileaccount_sp
@profile_name='profile2',
@account_name='account2',
@sequence_number=1
--The sequence number determines the order in which accounts are used in the profile.
How to view the profiles and the accounts associated with it ?
==================================================================
exec msdb.dbo.sysmail_help_profileaccount_sp
How to grant access to a particular profile to all users ?
==========================================================
exec msdb.dbo.sysmail_delete_principalprofile_sp
@principal_name='public',
@profile_name='profile2'
--public here is the database role within msdb db.
How to view the principals that are given access to db mail profiles ?
======================================================================
exec msdb.dbo.sysmail_help_principalprofile_sp
How to start db mail ?
=========================
exec msdb.dbo.sysmail_start_sp
How to stop db mail ?
============================
exec msdb.dbo.sysmail_stop_sp
How to send an email ?
============================
exec msdb.dbo.sp_send_dbmail
@profile_name='profile2',
@recipients='sudan.madhan@gmail.com',
@subject='System administrator',
@body='This message is for testing purposes only',
@body_format='html'
How to find out if our db mail queue status is started or not ?
===============================================================
exec msdb.dbo.sysmail_help_status_sp
How to find out if there are any mail items in the queue waiting to be sent ?
=============================================================================
exec msdb.dbo.sysmail_help_queue_sp
--the length column gives us the clue if there are any pending items
How to view the configuration settings/properties of the db mail ?
===================================================================
exec msdb.dbo.sysmail_help_configure_sp
How to break the connectivity of a account and the associated profile ?
=================================================
exec msdb.dbo.sysmail_delete_profileaccount_sp
@profile_name='profile2',
@account_name='account2'
How to delete the account ?
============================
exec msdb.dbo.sysmail_delete_account_sp
@account_name='account2'
How to revoke the access to principals from a profile ?
===================================================
exec msdb.dbo.sysmail_delete_principalprofile_sp
@principal_name='public',
@profile_name='profile2'
How to delete the profile ?
============================
exec msdb.dbo.sysmail_delete_profile_sp
@profile_name='profile2'
What if any changes have to be made to either accounts or profiles ?
============================
Use the below stored procedures as per the situation :
exec msdb.dbo.sysmail_update_account_sp
exec msdb.dbo.sysmail_update_profile_sp
exec msdb.dbo.sysmail_update_profileaccount_sp
exec msdb.dbo.sysmail_update_principalprofile_sp
What are the catalog views available related to db mail ?
=========================================
The following are the views available : (these are self explanatory)
select * from msdb.dbo.sysmail_allitems
select * from msdb.dbo.sysmail_sentitems
select * from msdb.dbo.sysmail_unsentitems
select * from msdb.dbo.sysmail_faileditems
select * from msdb.dbo.sysmail_mailattachments
select * from msdb.dbo.sysmail_event_log
How to delete events from the Database Mail log (or) How to delete all events in the log or those events meeting a date or type criteria ?
=============================================
exec msdb.dbo.sysmail_delete_log_sp
@logged_before='2010-01-26'
exec msdb.dbo.sysmail_delete_log_sp
@event_type='error'
exec msdb.dbo.sysmail_delete_log_sp
--this deletes entire table
Give some example queries related to sending an db mail via t-sql syntax ?
==================================
1)
exec msdb.dbo.sp_send_dbmail
@profile_name='profile1',
@recipients='sudan.madhan@gmail.com',
@subject='Shift availability information',
@query='select * from HumanResources.shift',
@attach_query_result_as_file=0,
@execute_query_database='AdventureWorks'
2)
exec msdb.dbo.sp_send_dbmail
@profile_name='profile1',
@recipients='sudan.madhan@gmail.com',
@subject='Shift availability information',
@query='select * from HumanResources.shift',
@attach_query_result_as_file=1,
@execute_query_database='AdventureWorks',
@query_result_separator='|',
@query_result_width=100
How to create a sql server agent job to archive database mail messages and event logs ?
=====================================
job name : dbmail_archives
job description : This job is required to run first day of the month and archives the data from db mail event log,attachments and allitems into 3 separate tables.
job category : Database Maintenance
no of steps in this job : 4
job1 : Clean the existing table
in destiny db,
if exists(select name from sys.objects where name
in('dbmail_event_log_archives','dbmail_attachements_archives','dbma
il_allitems_archives'))
begin
drop table dbmail_event_log_archives;
drop table dbmail_attachements_archives;
drop table dbmail_allitems_archives;
end
job2 : archiving data into dbmail_event_log_archives
in msdb database,
select *
into destiny.dbo.dbmail_event_log_archives
from msdb.dbo.sysmail_event_log
job3 : archiving data into dbmail_attachements_archives
in msdb database,
select *
into destiny.dbo.dbmail_attachements_archives
from msdb.dbo.sysmail_attachments
job4 : archiving data into dbmail_allitems_archives
in msdb database,
select *
into destiny.dbo.dbmail_allitems_archives
from msdb.dbo.sysmail_allitems
job5 : cleaning the sysmail events,attachments and allitems
in msdb database,
delete from dbo.sysmail_allitems;
delete from dbo.sysmail_attachments;
delete from dbo.sysmail_event_log;
Schedule this job to run every month 1st day.
Why do we need to archive data into a separate database from this msdb database ?
=============================================
How to configure sql server agent mail to use database mail ?
=========================================
Rt click sql server agent->click properties->click alert system->check the box 'Enable Mail Profile'->choose db mail as mail system and choose the profile u want as the mail profile list->click ok->restart sql server agent.
**************************
Troubleshooting Guide :
***************************
1) Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.sp_send_dbmails'
Fix : User must have executed under different db context other than msdb.Hence execute this stored procedure under msdb db or use 3 part name as "exec msdb.dbo.sp_send_dbmail ....."
2) Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42
profile name is not valid
Fix : User is trying to send email using the sp with a profile name that actually doesnt exists.So check the profile name properly and make sure it exists.
Subscribe to:
Posts (Atom)