Database mail not working - restart/re enable service broker

Situation:

- no mail is send

- databasemail.exe is missing in taskmanager

- in event log "An error occurred in Service Broker internal activation while trying to scan the user queue 'msdb.dbo.ExternalMailQueue' for its status. Error: 1222, State: 56. Lock request time out period exceeded. This is an informational message only. No user action is required."

Try:

ALTER DATABASE [msdb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE [msdb] SET DISABLE_BROKER

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' ;

ALTER DATABASE [msdb] SET ENABLE_BROKER

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' ;

ALTER DATABASE [msdb] SET MULTI_USER

EXEC msdb.dbo.sysmail_stop_sp;

EXEC msdb.dbo.sysmail_help_status_sp;

EXEC msdb.dbo.sysmail_start_sp;

EXEC msdb.dbo.sysmail_help_status_sp;

EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';

Kids don't delete LDF files!

If you have, and no backup etc, read following articles:

try with "CREATE DATABASE name ON fileMDB FOR ATTACH_REBUILD_LOG"
http://social.msdn.microsoft.com/forums/en-US/sqldisasterrecovery/thread/d48bb79b-0403-46d8-abb9-11fff40e7e72/

in case of opened transactions (error:" The protocol can not be rebuilt because there were open on shutdown of database transactions / users that no probe was used for the database or the database was write protected. This error can occur if the transaction log file was manually deleted or lost due to hardware failure or external failure.")

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114812
http://stackoverflow.com/questions/773059/how-to-recover-database-from-mdf-in-sql-server-2005

Uncommited transacions will appear in recovered databases.

SQL @@SERVERNAME can return NULL, but it should not

Probably we are all using "@@SERVERNAME" variable for geting SQL Server name and usually it does what we expect.

Today some custom maintenance procedures stoped working. After investigation I've noticed that SELECT @@SERVERNAME returns "NULL".

Background: @@SERVERNAME gets value (at SQL Server boot) from table sys.servers where server_id is equal to 0.

SELECT [name] FROM sys.servers WHERE server_id = 0

in this system table there are also linked servers and "my server" was there with server_id different than zero.

Solution:
EXEC master.dbo.sp_dropserver @server=N'serverName', @droplogins='droplogins'
EXEC sp_addserver 'serverName', LOCAL

and restart server.

Good workaround (probably best practice) is to use function ServerProperty('serverName').

More info at: http://randyjean.blogspot.com/2005/11/sql-servername-can-sometimes-return.html

PS Server is SQL Server 2005 Standard

Windows Internal Database - management- Ali kako do njega preko nadzornih orodij?

Windows Internal Database (SSEE - SQL Server Embedded Edition), na voljo od Windows Serverja 2003 naprej, gre za minimini ms sql server 2005, ki je vdelan že v sam strežnik za potrebe strežnika ali pomožnik storitev (WSUS, SharePoint, ...)

Nekoliko se zatakne pri naslavljanju/ dostopu do njega preko nadzornik orodij (npr. Sql Server Managment Studio Express) instanciranje je sila preprosto:

\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

Naslov skoraj daljši kot vsebina! juhej! :)

SQL Server kazalci učinkovitosti - uporaba stavkov T-SQL

Hitra in enostavno do kazalcev preko T-SQL stavkov:

SELECT * FROM sys.dm_os_performance_counters

SELECT * FROM sys.dm_os_performance_counters
  WHERE counter_name IN ('Transactions/sec')

Potrebujemo: MS SQL Server 2005 ali višji