Error 14641 Databse Mail is stopped

Found this fun error today.

Msg 14641, Level 16, State 1, Procedure sp_send_dbmail, Line 81
Mail not queued. Database Mail is stopped. Use sysmail_start_sp to start Database Mail.

I first tried starting Database mail with this command:

EXEC msdb.dbo.sysmail_start_sp; 

Database mail was started so I kicked off my job again and received the Msg 14641. I executed the status command and noticed Database Mail was stopped again.

EXEC msdb.dbo.sysmail_help_status_sp;

Odd. After some digging I found this command in the sqlservercentral.com forum and it worked.

--First start databse mail again
EXEC msdb.dbo.sysmail_start_sp;

SET NOCOUNT ON
DECLARE @ch uniqueidentifier;
DECLARE @message_type nvarchar(256);
DECLARE @message_body nvarchar(max);
DECLARE @ctr bigint;
DECLARE @ctr2 bigint;
WHILE (1=1)
  BEGIN
     RECEIVE @ch = CONVERSATION_HANDLE, @message_type = message_type_name FROM ExternalMailQueue
     SET @ctr2 = @ctr2 + 1
     SET @ctr = (SELECT COUNT(*) FROM ExternalMailQueue)
     IF @ctr = 0
       BREAK
  END

This will loop through the ExternalMailQueue using the RECEIVE command and clears out the messages in the queue. It seems I had some messages backed up in the mail queue causing Database Mail to crash.

DDL search using SQL Server Default Trace

Need a quick way to search for recent changes to objects using the Default Trace? Then you my friend are in luck. Here is a stored procedure to search the Default Trace for recent object changes including the user, application, object, action, and date the change was made.

CREATE PROCEDURE DDLBusted
@DBName VARCHAR(255) = NULL,
@ObjectName VARCHAR(255) = NULL
AS

DECLARE @TraceFileName varchar (500)
SELECT @TraceFileName = PATH FROM sys .traces WHERE is_default = 1

SELECT   LoginName, HostName, ApplicationName, DatabaseName, ObjectName, s.name ObjectType, e.Name Action, StartTime, t.TextData
FROM   ::fn_trace_gettable(@TraceFileName, default) AS t
LEFT JOIN sys.trace_events AS e ON t.EventClass = e.trace_event_id  
LEFT JOIN master.dbo.spt_values s ON t.ObjectType = s.number
WHERE (@DBName IS NULL OR DatabaseName = @DBname)  
AND (@ObjectName IS NULL OR t.ObjectName = @ObjectName)
AND EventSubClass = 0
AND s.type = 'EOB'
ORDER   BY t.StartTime desc;

This can easily be adapted to store the data into an audit table, since entries in the Default Trace are not permanent. Feodor Georgiev has a tons more default trace info and better queries here: https://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server—the-power-of-performance-and-security-auditing/

Remove Log file from SQL Server database

Background Info
I was executing a large INSERT statement the other day and started to run out of disk space. I thought I had properly calculated the space needed but it turns out my calculations were wrong. Since I was 46 minutes into the INSERT I did not want to run out of space. A Log backup was running but it would not complete before I ran out of space. Therefore I decided to kill the backup and add a log file on another drive as a temp solution so the INSERT would work. The insert took 1 hour and 11 minutes and completed. Now I had a log file I didn’t need. Here are the steps I used to delete the log file.

Steps

  • Check to see how much log file is being used.
    DBCC SQLPERF(logspace)
    
  • Check the status of the log files for my target database
    DBCC LOGINFO(DBName)
    
  • If status is 0 then try to run the ALTER DATABASE REMOVE FILE command
    ALTER DATABASE DBName REMOVE FILE LogFileName
    
  • Received an error that the log file was not empty
    Clipboard01

 

  • Executed a LOG Backup on that database
    BACKUP LOG DbName TO DISK="C:\YourBackupPath\File.bak"
    
  • Tried the Remove File command again
    ALTER DATABASE DBName REMOVE FILE LogFileName
    
  • Log file was removed. Executed a SELECT from sys.database_files
    SELECT * FROM YourDB.sys.database_files
    
  • Log file is showing OFFLINE
  • Executed another Log Backup
    BACKUP LOG DbName TO DISK="C:\YourBackupPath\File.bak"
    
  • Offline file was removed.
    SELECT * FROM YourDB.sys.database_files
    

Good to go.

No Exceptions should be raised by this code

Hit a fun error today throwing a Severity 020 error. Here is the error from the Error Log

SQL Server Assertion: File: <qxcntxt.cpp>, line=956 Failed Assertion = '!"No exceptions should be raised by
this code"'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to
check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.

The error was happening every hour at 34 minutes past the hour. The problem started at 4:34PM the day before and was consistently happening every hour. I figured I had a SQL Agent job throwing some error or something. However after some searching around this error seemed pretty rare.

The alert notification email had some more clues:

Process ID 67 attempted to unlock a resource it does not own: DATABASE: 30 . Retry the transaction,
because this error may be caused by a timing condition. If the problem persists, contact the database administrator.

I figured that the DATABASE: 30 was a reference to a database with the DB_ID of 30. However there was no database with the ID of 30 in the instance. The highest DB_ID was 28. I fired up a SQL Profiler trace to see if I could capture the session and the statement. The Process ID was showing up in the alert email so that helped me track down the statement.

SELECT database_id, object_id, index_id, partition_number, page_io_latch_wait_count, page_io_latch_wait_in_ms
FROM sys.dm_db_index_operational_stats(null,null,null,null)
WHERE page_io_latch_wait_count >0

We have a Diagnostics tool that was running the SQL Statement above. I ran the statement in SSMS and was able to reproduce the error. So now what. Well I noticed we had some databases that were set to Offline. We are in the process of decommissioning this server. I detached a couple of the databases and the statement completed with no errors.

Above the statement that was erroring out was a query to grab all the databases on the server and throw them in a table. My guess is that the statement used to grab all the databases grabbed one of the Offline Databases by mistake. Then the sys.dm_db_index_operational_stats procedure was executed against the offline database thus throwing this wild error.

One problem with that theory is that the offline database was not being returned in the database list query. So maybe the diganostics tool was running off a cached copy of the database list or something. Not sure. Anyway, after the databases were detached the problem went away.

The following feature couldn’t be installed .net framework 3.5

I entered an almost infinite loop of installation fun on Windows Server 2012 the today. I wanted to share my adventure with others in an effort to save time and prevent you from drop-kicking your laptop like I almost did.

How it all started

  1. Started install of SQL Server 2014 on fresh Windows Server 2012 build
  2. Install reported I needed .NET 3.5 Service Pack 1 to continue
  3. Launched Server Manager
  4. Clicked Add Roles and Features
  5. Clicked through to the Features list and selected .NET Framework 3.5 and then clicked install

At this point I entered the almost infinite loop. The install failed several times. I tried going to the Microsoft website to get the install. When I launched the install I was delivered a message that you are not cool and that this won’t work, go back to add features in Server Mangaer.

First Lifeline

First lifeline was found in this support article from Microsoft: http://support.microsoft.com/kb/2734782/en-us. This article has a section on troubleshooting error code 0x800F0907. I followed the steps in this article and was able to add the .NET 3.5 Feature using server manager.

The Fix

Her is how I fixed the problem.

  1. Open Group Policy Editor
  2. Browse to Computer…Administrative Templates…System
  3. Double click on the Speciy settings for optional component installation and component repair option.
  4. Click Enable and check the box next to Contact Windows update directly to download repair content instead of WSUS
  5. Click OK
  6. Launch an elevated command prompt and type in gpupdate /force to update the Group Policy
  7. Return to the Server Manager and try to add the .NET 3.5 Service Pack 1 Feature.
  8. Crack open celebratory Mt. Dew and bask in the warm glow of your dominance.

2014-10-06 13_04_10

SQL Server User Security Report Script

I needed a way to report on what permissions are assigned to a user on SQL Server. For added bonus I wanted to be able to specify a group and then list out all the users in that group with their permissions. As a double added bonus I wanted to set a flag so you could generate the DDL for a new user in case you want to duplicate the permissions to a new user or group.

Bingo. First version here. I am sure there are several versions of this on the web, but at the moment this one is working well for me.



SET QUOTED_IDENTIFIER OFF
GO

IF OBJECT_ID('dbo.UserSecurityReport') IS NULL
    EXEC ('CREATE PROCEDURE dbo.UserSecurityReport AS SELECT 0')
GO
/*
*********************************************************************************************************
*
* Date:				8/27/2014			
* Purpose: 			Generate a Security report server wide or for specific user
* Parameters: 		@Username - if you pass a user name the report will only return permissions for 
					that user. If you leave this NULL you will get a system wide report
*
*********************************************************************************************************
*/
ALTER PROCEDURE [dbo].[UserSecurityReport]
@Username VARCHAR(250) = NULL,
@CreateDDL CHAR(1) = 'N',
@Newuser VARCHAR(250) = NULL,
@ExpandGroup CHAR(1) = 'N'
AS

SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @db SYSNAME,
 @scope VARCHAR(16),
 @sql NVARCHAR(MAX),
 @MemberName VARCHAR(250);

DECLARE @member TABLE (
 RoleName SYSNAME,
 MemberName SYSNAME NULL,
 MemberSid VARBINARY(128)
);

DECLARE @report TABLE (
 ScopeID TINYINT,
 ScopeLevel VARCHAR(16),
 ScopeName SYSNAME,
 RoleName SYSNAME,
 MemberName SYSNAME NULL,
 ObjectName SYSNAME,
 PermType VARCHAR(500),
 PermState VARCHAR(50)
);

DECLARE @userList TABLE
(
account_name VARCHAR(400),
[type] VARCHAR(50),
[privilege] VARCHAR(50),
mapped_login_name VARCHAR(200),
permission_path VARCHAR(400)
)

DECLARE @exceptionList TABLE
(
groupName VARCHAR(400),
ErrorNumber VARCHAR(200),
ErrorMessage VARCHAR(2000)

)


SELECT @db = @@SERVERNAME, @scope = 'SERVER';

INSERT INTO @member ([RoleName], [MemberName], [MemberSid])
EXEC sp_helpsrvrolemember;


INSERT INTO @report ([ScopeID], [ScopeLevel], [ScopeName], [RoleName], [MemberName], [ObjectName], [PermType], [PermState])
SELECT 1, @scope, @db, RoleName, MemberName, 'N/A', 'N/A', 'N/A' FROM @member

DECLARE db_csr CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT [name], 'DATABASE'
FROM [master].sys.databases
WHERE [database_id] > 4
AND ( [state] = 0
 OR ( [state] = 1 AND [is_in_standby] = 1 ) )
AND [source_database_id] IS NULL
AND LOWER([name]) NOT IN ('admin', 'distribution', 'ops')
AND LOWER([name]) NOT LIKE 'adventurework%'
AND LOWER([name]) NOT LIKE 'reportserv%'
ORDER BY [name];

OPEN db_csr;

WHILE 0 = 0
BEGIN
 DELETE FROM @member;

 FETCH NEXT FROM db_csr INTO @db, @scope;
 
 IF @@FETCH_STATUS <> 0 BREAK;

 SET @sql = " USE [" + @db + "]; 
 SELECT USER_NAME(role_principal_id), USER_NAME(member_principal_id), 0 
 FROM [" + @db + "].sys.database_role_members drm 
 WHERE LOWER(USER_NAME(role_principal_id)) NOT LIKE 'msrepl%' ";
 
 INSERT INTO @member ([RoleName], [MemberName], [MemberSid])
 EXEC sp_ExecuteSQL @sql;

 INSERT INTO @member ([RoleName], [MemberName], [MemberSid])
 SELECT 'explicit_owner', SUSER_SNAME([owner_sid]), [owner_sid]
 FROM [master].sys.databases
 WHERE [name] = @db;

 INSERT INTO @report ([ScopeID], [ScopeLevel], [ScopeName], [RoleName], [MemberName], [ObjectName], [PermType], [PermState])
 SELECT 2, @scope, @db, RoleName, MemberName, 'N/A', 'N/A', 'N/A' FROM @member;
 
 SET @sql = " USE [" + @db + "]; 
 SELECT 3, p.class_desc, DB_NAME(), 'N/A', USER_NAME(p.grantee_principal_id),ISNULL(OBJECT_NAME(p.major_id), 'N/A'), p.permission_name, p.state_desc 
 FROM sys.database_permissions p 
 INNER JOIN sys.database_principals dp 
 ON p.grantee_principal_id = dp.principal_id 
 WHERE LOWER(USER_NAME(p.grantee_principal_id)) NOT IN ('dbo','guest','public') ";
 
 INSERT INTO @report ([ScopeID], [ScopeLevel], [ScopeName], [RoleName], [MemberName], [ObjectName], [PermType], [PermState])
 EXEC sp_ExecuteSQL @sql;
END

CLOSE db_csr;
DEALLOCATE db_csr;

DELETE FROM @report WHERE LOWER(MemberName) IN ('dbo', 'sa');
IF @ExpandGroup = 'N'
BEGIN
 SELECT * FROM @report where (@Username IS NULL OR membername = @Username) ORDER BY 1, 2, 3, 4, 5, 6, 7 ;
END

IF @ExpandGroup = 'Y'
BEGIN
 BEGIN TRY
 INSERT INTO @userList
 EXEC ('EXEC xp_logininfo ''' + @Username + ''',''members''')
 END TRY
 BEGIN CATCH
 INSERT INTO @exceptionList
 SELECT @username, ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage
 END CATCH

 SELECT r.ScopeID,r.ScopeLevel,r.ScopeName,r.RoleName,u.mapped_login_name,r.ObjectName,r.PermType,r.PermState
 FROM @userList u JOIN @report r ON u.permission_path = r.MemberName
 ORDER BY u.mapped_login_name


END


IF @CreateDDL = 'Y'
BEGIN

IF @Newuser IS NOT NULL
 SET @MemberName = @Newuser
ELSE
 SET @MemberName = @UserName



DECLARE @versionCheck TINYINT;
--Check Version Number
--If the SQL version is < 10 meaning pre 2008 then we set the Version Check to 0
--We will use this to determine if to deploy certain 2005 compatible objects
IF (SELECT CAST(REPLACE(SUBSTRING(CONVERT(VARCHAR(50), SERVERPROPERTY('productversion')), 1, 2),'.','') AS INT)) <= 10
 SET @versionCheck = 0
ELSE
 SET @versionCheck = 1


PRINT "Member Name: " + @MemberName

IF @versionCheck = 1
BEGIN
 SELECT "ALTER SERVER ROLE [" + RoleName + "] ADD MEMBER [" + @MemberName + "]"
 FROM @Report
 WHERE ScopeID = 1
 AND MemberName = @UserName
END
ELSE
 IF @versionCheck = 0
 BEGIN
 SELECT "EXEC master..sp_addsrvrolemember '" + @MemberName + "', '" + RoleName + "'"
 FROM @Report
 WHERE ScopeID = 1
 AND MemberName = @UserName
 END

IF @versionCheck = 1
BEGIN
 SELECT "CREATE USER [" + @MemberName + "] FOR LOGIN [" + @MemberName + "]
 GO
 USE [" + ScopeName + "]
 GO
 ALTER ROLE [" + RoleName + "] ADD MEMBER [" + @MemberName + "]
 GO"
 FROM @report
 WHERE ScopeID = 2
 AND MemberName = @UserName
END
ELSE 
IF @versionCheck = 0
BEGIN
 SELECT "CREATE USER [" + @MemberName + "] FOR LOGIN [" + @MemberName + "]
 GO
 USE [" + ScopeName + "]
 GO
 EXEC sp_addrolemember ['" + RoleName + "'], ['" + @MemberName + "']
 GO"
 FROM @report
 WHERE ScopeID = 2
 AND MemberName = @UserName

END


SELECT "USE [" + ScopeName + "]
 GO
 GRANT CONNECT TO [" + @MemberName + "]
 GO"
FROM @Report
WHERE ScopeID = 3
AND MemberName = @UserName

END