Sql Server Database Mail stopped working after Service Pack upgrade.

After a recent SP4 upgrade to Sql Server the Database mail stopped working.

The following error message would generate in the logs.
After several attempts of troubleshooting the solution was to run the script

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\install\sysdbupg.sql

This MS Article http://support.microsoft.com/kb/2008286 explains it can be caused after a Service Pack upgrade.


Message
1) Exception Information
===================
Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
Message: Mail configuration information could not be read from the database.
Data: System.Collections.ListDictionaryInternal
TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.Account GetAccount(Int32)
HelpLink: NULL
Source: DatabaseMailEngine

StackTrace Information
===================
   at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID)
   at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateSendMailCommand(DBSession dbSession)
   at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandRunner.Run(DBSession db)
   at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.MailOperation(Object o)

2) Exception Information
===================
Exception Type: System.IndexOutOfRangeException
Message: timeout
Data: System.Collections.ListDictionaryInternal
TargetSite: Int32 GetOrdinal(System.String)
HelpLink: NULL
Source: System.Data

StackTrace Information
===================
   at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)
   at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
   at System.Data.SqlClient.SqlDataReader.get_Item(String name)
   at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID)

Sql Server Perfmon Counters Quick Setup

The following steps allows you to quickly setup perfmon counters for Sql Server..

Create a text file called "sql2x51.txt" and copy\paste the following list of counters.
( Replace the Sql instance name with your Sql instance name.)


"\PhysicalDisk(*)\Avg. Disk sec/Read"
"\Memory\Available MBytes"
"\PhysicalDisk(*)\Avg. Disk sec/Write"
"\PhysicalDisk(*)\Disk Reads/sec"
"\PhysicalDisk(*)\Disk Writes/sec"
"\PhysicalDisk(*)\Disk Reads Bytes/sec"
"\PhysicalDisk(*)\Disk Write Bytes/sec"
"\PhysicalDisk(*)\Avg. Disk Bytes/Reads"
"\PhysicalDisk(*)\Avg. Disk Bytes/Write"
"\Process(sqlserver)\IO Data Operations/sec"
"\Process(sqlserver)\Thread Count"
"\Process(*)\% Processor Time"
"\Processor(*)\% Privileged Time"
"\Processor(*)\% Processor Time"
"\Processor(_Total)\% Privileged Time"
"\Processor(_Total)\% Processor Time"
"\System\Context Switches/sec"
"\mssql$MySqlInstance:Access Methods\Page Splits/sec"
"\mssql$MySqlInstance:Buffer Manager\Buffer cache hit ratio"
"\mssql$MySqlInstance:Buffer Manager\Checkpoint pages/sec"
"\mssql$MySqlInstance:Buffer Manager\Lazy writes/sec"
"\mssql$MySqlInstance:Buffer Manager\Page life expectancy"
"\mssql$MySqlInstance:Buffer Manager\Page reads/sec"
"\mssql$MySqlInstance:Buffer Manager\Page writes/sec"
"\mssql$MySqlInstance:General Statistics\User Connections"
"\mssql$MySqlInstance:Locks(_Total)\Lock Requests/sec"
"\mssql$MySqlInstance:Locks(_Total)\Lock Timeouts (timeout > 0)/sec"
"\mssql$MySqlInstance:Locks(_Total)\Lock Wait Time (ms)"
"\mssql$MySqlInstance:Locks(_Total)\Lock Waits/sec"
"\mssql$MySqlInstance:Locks(_Total)\Number of Deadlocks/sec"
"\mssql$MySqlInstance:Memory Manager\Target Server Memory (KB)"
"\mssql$MySqlInstance:Memory Manager\Total Server Memory (KB)"
"\mssql$MySqlInstance:Memory Manager\Memory Grants Pending"
"\mssql$MySqlInstance:SQL Statistics\Batch Requests/sec"
"\mssql$MySqlInstance:SQL Statistics\SQL Compilations/sec"
"\mssql$MySqlInstance:SQL Statistics\SQL Re-Compilations/sec"

Place the text file in the directory c:\temp or a directory of your preference.

Now run the following command from the command console.

logman create counter SQLBASELINEGG -f bincirc -max 500 --v -o "c:\temp\SQLout" -cf "c:\temp\sql2x51.txt"

You now have setup perfmon counters for your Sql Instance.
Open up perfmon and you will see a new User Defined set called SQLBASELINEGG.

Alter multiple Columns data types

This script was used in a staging environment to change all decimal and char columns to float and varchars.


Instructions are:

1. Run the script in SSMS.
2. Copy and paste the results in a new query pane and execute..

SQL Server Finding Timeouts

You need to create a table that contains your trace data.

You can import this from your .trc file using
Or if you used Sql Profiler save the trace data File -> Save As -> Trace Table

Running the following command you can identify the process that had not completed.

Sql Server Script AutoFix Orphaned Users

A common task I'm asked to perform by developers\testers is the copying of production databases to dev or test environments.
The backup and restoring in batches of databases can have the affect of creating the orphaned users, where the database accounts become misaligned with the Sql Server accounts.

The general resolve is to use the sp_change_users_login proc. The issue arises when there are multiples of accounts to fix  along with several databases. This can be a time consuming process to do this manually for each account.

The following script I wrote helps reduce the time involved in fixing the orphaned accounts.