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.

TRUNCATE TABLE table_name

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!

Issue

SQL Queries taking longer than normal to complete.

Environment

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.

Resolution

**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',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
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
SQL Statement: ALTER INDEX REORGANIZE

Reference Values (in %): avg_fragmentation_in_percent > 30
Action: Rebuild Index
SQL Statement: ALTER INDEX REBUILD

In my case I then did the following…

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT '[' + OBJECT_SCHEMA_NAME([object_id])+'].['+name+']' AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO