Database Mail in SQL Server 2005

Enable database mail

To enable database mail on the database instance, run the following SQL against the master database

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Database Mail XPs’, 1;
GO
RECONFIGURE
GO

To check the configuration value, run

sp_configure ‘Database Mail XPs’;
GO

 

Configure a mail profile

In order for the SQL Server jobs to alert by e-mail database mail will need to be configured.

In SQL Server Management Studio open the Object Explorer

image

Right click on the Database Mail option

clip_image003

And select Configure Database Mail

Note – you may be prompted to confirm you wish to enable this feature.

clip_image005

Select Set up Database Mail by performing the following tasks and click on Next.

clip_image007

Enter the profile name you wish to create and click Next

image

The account name can be chosen to suit. Accounts are created within the profile. The server name for the mail server is 172.***.***.***. Once complete click OK.

image

Click Next to continue (or add another account)

clip_image013

Leave the public checkbox blank and click Next

clip_image015

Click Next to continue

clip_image017

Click Finish to create the profile and accounts.

clip_image019

Any errors will be displayed. Click close to finish. The profile created can be used in any monitoring jobs set up.

Test Database Mail

Right click on the Database Mail option

clip_image003[1]

And select Send Test E-Mail

clip_image021

Enter the email address to send the test mail to and click the Send Test E-Mail button.

clip_image023

If properly configured, a test email should be received in the specified inbox.

Property Owner is not available for Database ‘[database_name]’ Error

I came across this issue for some of our databases today when trying to view database information through the GUI

image

After investigation, i have found that this happens because the owner of the database has been removed from the domain or access to the server for that person has been removed. this can happen if a colleague who created the database or was the DB Owner leaves etc.

The problem this causes is that the sid in the server_principals system table becomes NULL for this user, preventing a match against the owner_sid in the sys.databases table.

To find out if any of your databases have this occurance –

SELECT databases.NAME,server_Principals.NAME
FROM sys.[databases]
LEFT JOIN sys.[server_principals]
ON
[databases].owner_sid = [server_principals].sid

To resolve, use the following syntax to change the database owner to a user that exists. e.g.

USE [CMS3000Train]
GO
EXEC sp_changedbowner ‘sa’
GO

Dynamic Management Views (DMVs)

I tend to do a fair bit of performance tuning and optimisation of reports and find the following views very useful.

select * from sys.dm_db_index_usage_stats
where database_id = 5
order by last_user_seek DESC 

This gives index usage stats – useful in finding out whether the optimiser is using your indexes, or whether you have inefficient indexes which get a lot of DML – if you find scans, seeks and lookups at 0 and updates high then you have an inefficient index

select * from sys.dm_exec_sessions 

This gives you similar output to sp_who2 but in more detail. Logical reads and physical reads are important if you want to see if your data is being read out of memory/cache or disk

select * from sys.dm_exec_requests

In Depth detail about each user request – some duplication with exec_sessions but this also gives you a more details command breakdown and percent complete

If you are tuning a query and want to relate to IO usage, then run SET STAISTICS IO ON before the query.

select  TOP 100
      objtype, 
      p.size_in_bytes,
      LEFT([sql].[text], 100) as [text]
from sys.dm_exec_cached_plans p
outer apply sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY usecounts DESC

Find plans in cache – i.e. compiled query plans etc

More Examples:

–The sys.dm_db_file_space_usage can used to troubleshoot insufficient disk space in tempdb.
–To find the number of unallocated pages in kb,we can use the sys.dm_db_file_space_usage DMV

SELECT SUM(unallocated_extent_page_count) AS [free pages], 
         (SUM(unallocated_extent_page_count)*8) AS [free space in KB]
    FROM sys.dm_db_file_space_usage

–To determine the number of sessions established by every login
select 
   login_name
 , count(session_id) as session_count
 from sys.dm_exec_sessions 
 group by login_name

 

–Returns the cursors that are open in the databases

 

SELECT * FROM sys.dm_exec_cursors(0)

 

–Returns information about the connections made to the
–instance of the SQL Server and the details of each connection.

 

select a.session_id
     , b.login_name
  FROM sys.dm_exec_connections a ,sys.dm_exec_sessions b
  WHERE a.session_id=b.session_id

 

–Returns the query plans which are cached by the SQL Server

 

select * from sys.dm_exec_cached_plans

 

–This gives information about the threads that waiting for resources in milliseconds

 

select * from sys.dm_os_wait_stats

 

–Returns information about the computer and the resources available and consumed by the SQL Server

 

select * from Sys.dm_os_sys_info

 

–Returns information about the transactions in a SQL Server instance

 

select * from Sys.dm_tran_active_transactions

 

–Returns a single row which gives information about the current transaction in the current session

 

select * from Sys.dm_tran_current_transaction

 

–Returns information about the transactions in that database

 

select * from Sys.dm_tran_database_transactions


Many more DMV’s – these are the ones I use mainly. More Details – http://www.codeproject.com/KB/database/Dynamic_Management_Views.aspx

Stats Date Function and Most Used Indexes

Statistics date is a useful function in retruning the date of the most recent update for a statistics object on a table or an indexed view 

SELECT ‘Index Name’ = i.name,
    ‘Statistics Date’ = STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
WHERE o.name = ’employee’ AND o.id = i.id]

 

Comment out the predicate to get details of the whole database

Similarly, It can also be useful to find out the most used indexes (or least used) depending on what the investigation is looking at –

select object_name(s.object_id) as TableName, isnull(i.name,‘HEAP’) as IndexName,
case i.index_id
when 0 then ‘HEAP’
when 1 then ‘CLUS’
else ‘NC’
end as IndexType
,reads=user_seeks + user_scans + user_lookups
,writes =  user_updates
from sys.dm_db_index_usage_stats s join sys.indexes i
on s.object_id = i.object_id and i.index_id = s.index_id
where objectproperty(s.object_id,‘IsUserTable’) = 1
and s.database_id = db_id()
order by reads desc

There are many different uses for sys.dm_db_index_usage_stats The Last_User_Seek, Last_User_Update columns can be useful in identifying unused indexes

Fragmentation in SQL Server 2005

Fragmentation can directly impact performance in a SQL Server 2005 database. It is therefore critical to maintain indexes\tables via regular maintenance.

For more details on causes of Fragmentation see http://www.sql-server-performance.com/articles/per/detect_fragmentation_sql2000_sql2005_p1.aspx

One of my most useful scripts is to get fragmentation reports for individual tables:

DECLARE @object_id INT;

DECLARE @db_id SMALLINT;

SET @db_id = DB_ID(N‘Futureproof’);
SET @object_id = OBJECT_ID(N‘Futureproof.dbo.Order_Insert’);

 

IF @db_id IS NULL
BEGIN;
    PRINT N‘Invalid database’;
END;
ELSE IF @object_id IS NULL
BEGIN;
    PRINT N‘Invalid object’;
END;
ELSE
BEGIN;
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , ‘LIMITED’);
END;
 
Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes. Taken from http://msdn.microsoft.com/en-us/library/ms188917.aspx

Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
— Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
— and convert object and index IDs to names.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ‘LIMITED’)
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

 

— Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

 

— Open the cursor.
OPEN partitions;

 

— Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

 

— 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N‘ALTER INDEX ‘ + @indexname + N‘ ON ‘ + @schemaname + N‘.’ + @objectname + N‘ REORGANIZE’;
        IF @frag >= 30.0
            SET @command = N‘ALTER INDEX ‘ + @indexname + N‘ ON ‘ + @schemaname + N‘.’ + @objectname + N‘ REBUILD’;
        IF @partitioncount > 1
            SET @command = @command + N‘ PARTITION=’ + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N‘Executed: ‘ + @command;
    END;

 

— Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

 

— Drop the temporary table.
DROP TABLE #work_to_do;
GO

TempDB Full – How to Clear TempDB Log in SQL Server 2005

The SQL Server log proves that tempdb was having problems…

02/19/2009 11:50:41,spid278,Unknown,The transaction log for database ‘tempdb’ is full. To find out why space in the log cannot be reused<c/> see the log_reuse_wait_desc column in sys.databases
02/19/2009 11:50:41,spid278,Unknown,Error: 9002<c/> Severity: 17<c/> State: 4.


Do the following to find out why tempdb is not reusing space – the Log_reuse_wait_desc will give details.

Select [name], log_reuse_wait_desc from sys.databases where name = ‘tempdb’

The most common reason for this is a long running, active transaction. The log_reuse_desc will be ACTIVE_TRANSACTION if this is the case. If so, you can run the queries below to find the culprit:

-- Find oldest transaction
DBCC OPENTRAN
-- Get input buffer for a SPID
DBCC INPUTBUFFER(21) – Substitute the SPID number from above

In future, If Tempdb fills up then it is not necessary to restart SQL Server. The following actions should be taken:

On the SQL Server – Open Query analyser and run:

–This will show the size of the tempdb

Use [Tempdb]

GO

SELECT name AS ‘File Name’ , physical_name AS ‘Physical Name’, size/128 AS ‘Total Size in MB’, size/128.0 CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Available Space In MB’—, * FROM sys.database_files;
 

–This will shrink all segments up until the last used segment – so any free segments at the back of the tempdb data file

USE [tempdb]
GO
DBCC SHRINKFILE (N‘tempdev’ , 0, TRUNCATEONLY)
GO

–To find out which segments are in use etc – a status of 2 shows that the virtual segment is in use
use [tempdb]
go
dbcc loginfo
go

If there are empty segments then you can run a script to rearrange segment usage – The number variable below is the size you want to shrink to

USE [tempdb]
GO
DBCC SHRINKFILE (N‘tempdev’ , 1024)
GO 

Tempdb will not release segments if they are in use by open transactions. Please read this before attempting any Tempdb alterations http://support.microsoft.com/kb/307487