On SQL 2005 you have to tell SQL Server Agent / Properties / Alert System, which profile that you'd like to enable. Here's what it does.
e.g. and don't forget to restart SQL Agent.
USE [msdb]
GO
EXEC master.dbo.xp_instance_regwrite
N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'DatabaseMailProfile',
N'REG_SZ',
N'MyDatabaseProfile'
GO
|
Please refer MSDN To set up SQL Server Agent Mail to use Database Mail
|
Here are some useful debugging information queries.
SET nocount ON
USE master
GO
EXEC xp_msver
SELECT
'Microsoft SQL Server ' +
CONVERT(VARCHAR, SERVERPROPERTY('ProductVersion') ) + ' ' +
CONVERT(VARCHAR, SERVERPROPERTY('ProductLevel') ) + ' ' +
CONVERT(VARCHAR, SERVERPROPERTY('Edition') ) + ' ' +
CONVERT(VARCHAR, SERVERPROPERTY('EngineEdition') ),
@@VERSION
GO
PRINT '** Database master compatibility level:'
EXEC sp_dbcmptlevel master
GO
Here is a good resource to figure out what version of SQL you are running using the version number.
Here' my helper function which I put in my master_admin database.
CREATE FUNCTION fn_GetCurrentSQL (
@sql_handle binary(20),
@stmt_start INT,
@stmt_end INT
)
RETURNS VARCHAR(8000)
AS
BEGIN
RETURN (
SELECT
SUBSTRING(s.TEXT,
COALESCE(NULLIF((@stmt_start/2)+1, 0), 1),
CASE @stmt_end
WHEN -1
THEN DATALENGTH(s.TEXT)
ELSE
(@stmt_end - @stmt_start)
END
)
FROM ::fn_get_sql(@sql_handle) s
)
END
GO
Here’s my sp_who2 replacement script.
CREATE PROCEDURE [dbo].[sp__who2] (
@nSortFlag INT = 0,
@nMinAgo INT = NULL,
@nSPID INT = NULL,
@vcDBName VARCHAR(128) = NULL,
@bAll BIT = 0
)
AS /*
** Procedure
** Description
** Parameters
** Requirements
** Usage
EXEC dbo.sp_who2 0, 0
EXEC dbo.sp__who2 0, 0, 84
EXEC dbo.sp__who2 0, NULL, 84
EXEC dbo.sp__who2 NULL, NULL, NULL
EXEC dbo.sp__who2 0, NULL, NULL
EXEC dbo.sp__who2 1, NULL, NULL
EXEC dbo.sp__who2 2, NULL, NULL
EXEC dbo.sp__who2 0, 2, NULL
EXEC dbo.sp__who2 1, 2, NULL
-- DECLARE @nSortFlag bit,
-- @nMinAgo int
--
-- SELECT
-- @nSortFlag = 0,
-- @nMinAgo = 1
** Changes
2008-02-19 John Perry
*/
DECLARE @bootjobtime DATETIME
SELECT @bootjobtime = MIN(last_batch)
FROM master.dbo.sysprocesses (NOLOCK)
WHERE spid < 20
SELECT CAST('A' AS VARCHAR(50)) SortC,
CAST(0 AS INT) SortN,
IDENTITY( INT, 1,1 ) AS ID_Num,
CAST(DB_NAME(dbid) AS VARCHAR(35)) DatabaseName,
spid,
ecid,
BlkBy = CASE CONVERT(CHAR(5), ISNULL(blocked, '0'))
WHEN '0' THEN ' .'
ELSE CONVERT(CHAR(5), ISNULL(blocked, '0'))
END,
open_tran AS openx,
physical_io AS io,
cpu,
memusage,
waittime AS waitMS,
CAST(ISNULL(master_admin.dbo.fn_GetCurrentSQL(sql_handle, stmt_start, stmt_end), cmd) AS VARCHAR(1024)) cmd,
hostname,
program_name,
CONVERT(SYSNAME, RTRIM(loginame)) AS loginname,
CONVERT(VARCHAR, last_batch, 121) AS last_date,
CASE WHEN waittype = 0x0000 THEN lastwaittype
ELSE '*' + lastwaittype
END WaitType,
waitresource
INTO #tmpWHO
FROM master.dbo.sysprocesses (NOLOCK)
WHERE (ISNULL(blocked, '0') > 0
OR open_tran > 0
)
AND (@nSortFlag IS NOT NULL)
ORDER BY spid,
ecid
INSERT #tmpWHO
(SortC,
SortN,
DatabaseName,
spid,
ecid,
BlkBy,
openx,
io,
cpu,
memusage,
waitMS,
cmd,
hostname,
program_name,
loginname,
last_date,
WaitType,
waitresource
)
SELECT CASE WHEN 1 = @nSortFlag THEN 'B' + CONVERT(VARCHAR(35), DB_NAME(dbid))
WHEN 2 = @nSortFlag THEN 'B' + CONVERT(VARCHAR(35), last_batch, 121)
ELSE 'B'
END SortC,
CASE WHEN 0 = 0 THEN spid
ELSE 99
END SortN,
CAST(DB_NAME(dbid) AS VARCHAR(35)) DatabaseName,
spid,
ecid,
BlkBy = CASE CONVERT(CHAR(5), ISNULL(blocked, '0'))
WHEN '0' THEN ' .'
ELSE CONVERT(CHAR(5), ISNULL(blocked, '0'))
END,
open_tran AS openx,
physical_io AS io,
cpu,
memusage,
waittime AS waitMS,
cmd,
hostname,
program_name,
CONVERT(SYSNAME, RTRIM(loginame)) AS loginname,
CONVERT(VARCHAR, last_batch, 121) AS last_date,
CASE WHEN waittype = 0x0000 THEN lastwaittype
ELSE '*' + lastwaittype -- (active)
END WaitType,
waitresource
FROM master.dbo.sysprocesses (NOLOCK)
WHERE ((@nSortFlag IS NULL)
OR (ISNULL(blocked, '0') = 0
AND open_tran = 0
)
)
AND (@nSPID > 0
OR @nMinAgo IS NULL
OR last_batch >= DATEADD(minute, -@nMinAgo, GETDATE())
)
SELECT DatabaseName,
spid,
ecid,
BlkBy,
openx,
io,
cpu,
memusage,
waitMS,
WaitType,
cmd,
hostname,
program_name,
loginname,
last_date,
waitresource
--, SortC,SortN,id_NUM
FROM #tmpWHO
WHERE (@bAll = 1
OR last_date > @bootjobtime
OR @nMinAgo IS NULL
)
AND (@vcDBName IS NULL
OR DatabaseName LIKE '%' + @vcDBName + '%'
)
AND (@nSPID IS NULL
OR @nSPID = 0
OR spid = @nSPID
)
ORDER BY SortC,
SortN,
spid,
ecid
DROP TABLE #tmpWHO;
GO
As a DBA you've gotta love Kimberly L. Tripp. Here is what she says.
8) Check/fix your internal fragmentation. OK, so this is the one thatreally helped the customer in Turkey. Often, when transaction logs arenot pre-allocated and/or when there's been a lot of autogrowths, thetransaction log can become internally fragmented. Internally yourtransaction logs are broken down into smaller more granular chunkscalled VLFs (Virtual Log Files). The size and number of VLFs you'llhave depends largely on the size that the chunk is when it's added toyou transaction log. If you add a new chunk to the transaction logwhich is 20MB (through autogrowth or through manual growth) then thenumber of VLFs that are added is 4. If you add a chunk which is greaterthan 64MB but less than or equal to 1GB, you'll add 8 VLFs. If you addmore than 1GB then you'll add 16VLFs. In general, most transaction logswill only have 20 or 30 VLFs – even 50 could be reasonable depending onthe total size of the transaction log. However, in many cases whathappens is that excessive autogrowths can cause an excessive number ofVLFs to be added – sometimes resulting in hundreds of VLFs. Having anexcessive number of VLFs can negatively impact all transaction logrelated activities and you may even see degradation in performance whentransaction log backups occur. To see how many VLFs you have solelylook at the number of rows returned by DBCC LOGINFO. The number of rowsreturned equals the number of VLFs your transaction log file has. Ifyou have more than 50, I would recommend fixing it and adjusting yourautogrowth so that it doesn't occur as fequently. To get rid of all ofthe execessive VLFs, follow these easy steps to shrink off thefragmented chunk and add a new, clean chunk to your transaction log:
1. Wait for an inactive time of day (ideally, it would be best toput the database into single user mode first) and then clear alltransaction log activity through a regular transaction log backup. Ifyou're using the simple recovery model then you don't need to do a logbackup… Instead, just clear the transaction log by running acheckpoint.
BACKUP LOG databasename TO devicename
2. Shrink the log to as small a size as possible (truncateonly)
DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY)
NOTE: if you don't know the logical filename of your transaction log use sp_helpfile to list all of your database files.
3. Alter the database to modify the transaction log file to the appropriate size – in one step
ALTER DATABASE databasename
MODIFY FILE
(
NAME = transactionloglogicalfilename
, SIZE = newtotalsize
)
And, that should do it!
–klt
Given that, here is some of the code you might use to optimize your database log file internals. (VLF). I use my own sp__who2 but you just want to see who's in the database. The main feature of this script is it calculates the ALTER DATABASE log file recreate script — as mentioned above — so when you're done all the previous file growths have been beautifully reduced to this one growth. Instead of CHECKPOINT on FULL recovery databases you'll need to run a LOG backup.
SELECT 'USE ' + DB_NAME() + ';' + CHAR(13)+CHAR(10) +
'EXEC sp__who2 1,null,null,' + DB_NAME() + ';' + CHAR(13)+CHAR(10) +
'SELECT DATABASEPROPERTYEX(''' + DB_NAME() + ''',''Recovery'');' + CHAR(13)+CHAR(10) +
'DBCC LOGINFO;' + CHAR(13)+CHAR(10) +
'CHECKPOINT;' + CHAR(13)+CHAR(10) +
'DBCC SHRINKFILE(' + RTRIM(name) + ', TRUNCATEONLY);' + CHAR(13)+CHAR(10) +
'ALTER DATABASE ' + DB_NAME() + ' MODIFY FILE (NAME = ' + RTRIM(name) +
', SIZE = ' + CAST(size/1024*8 AS VARCHAR(25)) + ');'+ CHAR(13)+CHAR(10) +
'DBCC LOGINFO;'
FROM dbo.sysfiles
WHERE FILEPROPERTY(name,'IsLogFile') = 1
Licensing is a pain but needing a degree to figure out what you need is even worse. Here's where to get started.
See if PING in a command window gives us a return value.
L:\>ping sqldev -n 1
Pinging sqldev.mylan.local [10.01.64.37] with 32 bytes of data:
Reply from 10.01.64.37: bytes=32 time<1ms TTL=128
Ping statistics for 10.01.64.37:
Packets: Sent = 1, Received = 1, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms
L:\>ECHO %ERRORLEVEL%
0
L:\>ping sqldevx -n 1
Ping request could not find host sqldevx. Please check the name and try again.
L:\>ECHO %ERRORLEVEL%
1
It does -- see if we can utilize that using T-SQL. I'm using some of my helper procedures e.g. sp__PRINT but you can replace it with just PRINT commands for debugging.
SET nocount ON
DECLARE @RC INT,
@bVERBOSE bit,
@vcCmd VARCHAR(1024),
@vcSQL NVARCHAR(1024),
@vcServerName VARCHAR(128)
SELECT @bVERBOSE = 1,
@vcServerName = 'SQLDEV',
@vcCmd = 'ping ' + LEFT(@vcServerName , CASE CHARINDEX('\',@vcServerName)
WHEN 0 THEN LEN(@vcServerName) ELSE CHARINDEX('\',@vcServerName) -1 END) + ' -n 1',
@vcSQL = 'IF NOT EXISTS (SELECT srvname FROM master.dbo.sysservers
WHERE quotename(srvname) = quotename(''' + @vcServerName + ''') ) RAISERROR(''ERROR: no linked server to test'',16,1)'
EXEC @RC = master.dbo.xp_cmdshell @vcCmd, no_output
IF @bVERBOSE = 1
EXEC sp__PRINT @RC
IF @RC <> 0
BEGIN
RAISERROR('ERROR: Server %s NOT FOUND on the network.', 16,1, @vcServerName)
END ELSE
BEGIN
IF @bVERBOSE = 1
BEGIN
EXEC sp__PRINT 'See if there is a linked server, locally'
PRINT @vcSQL
END
EXEC @RC = sp_executesql @vcSQL
IF @bVERBOSE = 1
BEGIN
EXEC sp__PRINT 'Run REMOTE QUERY - Test remote linked server connectivity'
PRINT @vcSQL
END
EXEC @RC = sp_executesql @vcSQL
IF @RC <> 0
BEGIN
RAISERROR('ERROR: connecting to linked server [%s].', 16,1,@vcServerName)
END
END ELSE
SELECT srvname FROM master.dbo.sysservers
END
GO
Here's some output:
IF NOT EXISTS (SELECT srvname FROM master.dbo.sysservers
WHERE quotename(srvname) = quotename('SQLDEV') ) RAISERROR('ERROR: no linked server to test',16,1)
********************************************************************************
2009-02-11 14:29:00.013 0
********************************************************************************
********************************************************************************
2009-02-11 14:29:00.013 See if there is a linked server, locally
********************************************************************************
IF NOT EXISTS (SELECT srvname FROM master.dbo.sysservers
WHERE quotename(srvname) = quotename('SQLDEV') ) RAISERROR('ERROR: no linked server to test',16,1)
********************************************************************************
2009-02-11 14:29:00.077 Run REMOTE QUERY – Test remote linked server connectivity
********************************************************************************
IF NOT EXISTS (SELECT srvname FROM [SQLDEV].master.dbo.sysservers
WHERE quotename(srvname) = quotename('SQLDEV') ) RAISERROR('ERROR: cannot connect to linked server',16,1)
Here's a tutorial site I like.
If you get this error and all else is as it should be, try this.
Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42
profile name is not valid
Run this and then the TestSQL output to make sure you've really got the correct Profile name.
SELECT [profile_id]
,[name]
,[description]
,[last_mod_datetime]
,[last_mod_user]
,'EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''' + name + ''',
@recipients = ''me@test.com'',
@subject = ''Test'',
@body = ''Message'',
@body_format = ''HTML'';' AS TestSQL
FROM [msdb].[dbo].[sysmail_profile]
–xp_cmdshell 'dir e:\mssql\reports'
–Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
–The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.
EXEC sp__cmdshell 'dir e:\mssql\reports'
Works!
Here's the procedure:
CREATE PROCEDURE [dbo].[sp__cmdshell] (
@vcCmd VARCHAR(8000),
@bNoOutput bit = 0
)
AS
SET nocount ON
DECLARE @RC INT
IF @bNoOutput = 0
EXEC @RC = master.dbo.xp_cmdshell @vcCmd
ELSE
EXEC @RC = master.dbo.xp_cmdshell @vcCmd, no_output
IF @RC <> 0 OR @@ERROR <> 0
PRINT 'ERROR: ' + @vcCmd
RETURN @RC
GO
You probably also need to give permissions to a login or a role to run the new procedure.
GRANT EXECUTE ON [dbo].[sp__cmdshell] TO [db_executor]