SQL Server Error: 14607, profile name is not valid [SQLSTATE 42000]

SQL Email Failures, Failed to notify via email, issues?

For some reason emailing of SQL Server Agent job reports stopped working when I disabled the ‘sa’ account and created a different admin user. Never could figure out why until today!

I’m not even sure if disabling the ‘sa’ account had anything to do with it because when I checked the properties of the job in question it wasn’t even configured to send alerts!

Anyway, the way I fixed it was to right click the SQL Server Agent node in SQL Management Studio and select Properties. Click the Alert System node and Enable the Mail profile you’d like to allow the SQL Server Agent to use, restart the SQL Server Agent Service (just the service, not the server!) and it should start working.


Quickly delete all records from a MSSQL database table

How can you remove all data from a table efficiently? Using the delete command can take a very long time if you have lots of data.

If you want to delete all rows from the table – the simplest way to do this is to use the TRUNCATE command.


Remember that the table cannot have foreign keys constraints, but there is a workaround. You can delete foreign keys temporarily and recreate them afterward.

Action steps:

  • Drop the FK constraints
  • Execute TRUNCATE command
  • Recreate the FK constraints

Once you removed all rows – consider resetting the identity value as follows:

DBCC CHECKIDENT ('table_name', RESEED, 0)

Shout out to Piotr Bach for the article that can be found here

How to Check Index Fragmentation on Indexes in a SQL Database

Firstly I would like to point out that the following article was copied from here so all credit goes to them. I found it while tying to find out why one of our databases was incredibly slow!


SQL Queries taking longer than normal to complete.


SQL Server 2005, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016

When a database is frequently updated via INSERT, UPDATE, or DELETE statements we can expect it to become fragmented over the time.

If database indexes are fragmented, the SQL Server query optimizer may chose a non-optimal execution plan when using an index to resolve a query.

This will affect the overall query performance and you may notice a query behaving slower than normal.


**Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.**

The following is a simple query that will list every index on every table in your database, ordered by percentage of index fragmentation.

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

This query can be modified to focus on specific tables by append the table name to the ‘where’ clause:

WHERE indexstats.database_id = DB_ID() AND dbtables.[name] like '%%'

In order to reduce fragmentation we will have to reorganize or rebuild the indexes. Choosing between reorganizing or rebuilding depends on the query results.

For heavily fragmented indexes a rebuild process is needed, otherwise index reorganisation should be sufficient.

The following info summarises when to use each one:

Reference Values (in %): avg_fragmentation_in_percent > 5 AND < 30
Action: Reorganise Index

Reference Values (in %): avg_fragmentation_in_percent > 30
Action: Rebuild Index

In my case I then did the following…

DECLARE @fillfactor INT
SET @fillfactor = 80
SELECT '[' + OBJECT_SCHEMA_NAME([object_id])+'].['+name+']' AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
CLOSE TableCursor

MSSQL Maintenance cleanup task does not delete older files

If you are running Microsoft SQL Server 2008 R2 and find that your maintenance cleanup task is not deleting old files then it’s likely to be one of the following…

  1. Make sure you have the correct path specified and that it does not have a “\” on the end
  2. Make sure the file extension does not have a dot before it e.g.  “.bak” It should just read “bak”. You could also try *.* that would delete all files regardless of their file extension.