I am working on an MS Access database that needs to be integrated in a CRM system. Since the backend of the CRM is SQL Server (why else would I be involved), I migrated the Access database to SQL Server using the SQL Server Migration Assistant for Access v4.2 (SSMA for Access). Next I turned my attention to translating attributes between the systems and unfortunately, a free text notes field in the Access database is used for a lot of things that need to be translated to attributes. Some things I know how to translate, but some things have to be determined by the administrators for the CRM System and Access application. So I built a query in SQL on the tables formerly stored in the Access database and used Excel (with MS Query) to create a report for the administrators, so they can sort out how customer statuses should be translated.

Extremely simplified, the query looked like this;

SELECT tlbCustomer. CUST_CODE
  , tlbCustomer. PAY_REF
  ,COALESCE(UPPER(REPLACE(tblNotes.NOTE_TXT,'.','')),'')AS NOTE_TXT
FROM tlbCustomer LEFTOUTERJOIN tblNotes
      ON tlbCustomer.CUST_ID = tblNotes. CUST_ID
GROUPBY tlbCustomer. CUST_CODE
  , tlbCustomer. PAY_REF
  ,UPPER(REPLACE(tblNotes.NOTE_TXT,'.','' ))
ORDER BY COUNT (*) DESC

The query worked great on SQL, but did not return the text (NOTE_TXT) to Excel.

To cut the long story short, the original tblNotes.NOTE_TXT field in the Access database was of type MEMO. SSMA 2008 for Access converted this to nvarchar(max) and somehow MS Query (in Office 2007) does not return, nor throw an error on, large data types (varchar(max), nvarchar(max)). A quick test showed that MAX was not really needed;

SELECT MAX ( LEN (tblNotes.NOTE_TXT))FROM tblNotes

So modify the query to return NOTE_TXT as nvarchar(256) does the trick;

SELECT tlbCustomer. CUST_CODE
  , tlbCustomer. PAY_REF
  ,CAST (
      COALESCE ( UPPER ( REPLACE (tblNotes.NOTE_TXT,'.','')),'')
    AS nvarchar (256))AS NOTE_TXT
FROM tlbCustomer LEFTOUTERJOIN tblNotes
      ON tlbCustomer.CUST_ID = tblNotes. CUST_ID
GROUPBY tlbCustomer. CUST_CODE
  , tlbCustomer. PAY_REF
  ,UPPER(REPLACE(tblNotes.NOTE_TXT,'.','' ))
ORDER BY COUNT (*) DESC

Sometimes one is confronted with forgetting an option... It all started with a database that had a single data file of 1.8 GB and a transaction log more than 6 times the size of that. Usually that would be a database in full recovery model and no transaction log backups. This database was in simple recovery model though and according to the Disk Usage report, 99.99% of the transaction log was free. So a DBCC SHRINKFILE should solve the problem, however DBCC SHRINKFILE solved nothing, the log remained at about 11 GB.

A query on sys.databases learned that this particular database had ACTIVE_TRANSACTION for log_reuse_wait_desc. A peek in Activity Monitor then showed that there was an open transaction for the database; a DELETE task with a huge wait time and the BROKER_RECEIVE_WAITFOR wait type.

Then it occurred to me this is the database that used to have a deadlock problem. After implementing some procedure fixes, there haven’t been any deadlocks for over a half year and neither has the server (or service) been shutdown in between.

The stored procedure on the queue does a receive, but no timeout associated to it. Effectively this means that the receive will wait inside the transaction, until a messages comes into the queue. That hasn’t happened in the last six months. The resolution is simple, add the TIMEOUT option to the RECEIVE and the procedure will stop when there wasn’t any work to be done for the specified amount of time (in milliseconds). For the code, see the post “Deadlock Alerts Through Event Notification”, which has been updated with the TIMEOUT option.

After altering the procedure, kill the spid. Service Broker will restart, but now with the TIMEOUT option in the procedure and stop after the specified amount of time has elapsed without messages arriving in the queue.

When restoring a backup of a database, SQL Server recreates the files with the same sizes as listed in the backup, or at least tries to do so. The only option you have, is specifying the path for each file. Generally not a problem, but every now and then I encounter a database with a transaction log about 10 to 50 times the size of the data file(s) . . . yep, full recovery model and just daily full backups. I am the first to acknowledge transaction logs backups are key to prevent this situation, but the next best thing still is that recent valid database backup. . . and then you can’t restore it, because the 200 GB to write that big empty file called transaction log cannot be allocated.

Today was one of those days;

Msg 3257, Level 16, State 1, Line 1
There is insufficient free space on disk volume 'D:\' to create the database. The database requires 101197938688 additional free bytes, while only 19386400768 bytes are available.
Msg 3119, Level 16, State 4, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Surely I wouldn’t be the only one having problems with this and I found two others on connect;

• Option to resize datafile/transaction log during a backup or restore operation
• Restore option to restore minimum file sizes

I voted (as important) for both items, though the second one is already closed. Yet I was triggered by the comments from the MS-people to ask for a specific solution towards the transaction log;

RESTORE DATABASE ... WITH STUB_LOG

One of the habits of RESTORE DATABASE is that it wants to allocate the log size (and files) as found in the backup. Strange, as the size of those files is the result of past activity (not contained in the backup being restored). The only space needed in the transaction log, is that of the activity during the backup. Additional space requirements may exist for subsequent restores of differential backups and log backups (in norecovery or standby) or future activity (after recovery), but that isn’t an issue as the log files would grow again during these restores.

The only real issue I can think of is having a database with fixed size transaction log file(s), which means no auto grow. So for a WITH STUB_LOG option to work properly in these scenarios, it may need to imply that the log file(s) are always set to unrestricted auto grow (possibly only honoring the original increment).

It would at least prevent me from having to allocate 100GB for an empty transaction log on 20GB available.

Microsoft recently launched a Virtual Business Card site for Microsoft Certified Professionals. So if you ever passed one or more of those Microsoft exams in the NT 4.0 or later eras, check out the www.mcpvirtualbusinesscard.com site (Windows Live ID sign in to connect to you records in Microsoft's certification database).

I set my profile page up to show off the credentials..

Also had a look at including the transcript, but that would be a 13-page pdf, a bit of overkill if you ask me.

I knew this was coming and still I was taken by surprise; my son Bram was born last week (July 27th). An event like that turns your life upside down, forever. I'm not in the habit of putting personal stuff on the net, but this is worth the exception. So here they are, a proud father and his one day old son.

Sometimes SQL Server gets it wrong, UNIQUE constraints and NULL is one of the parts where it is wrong. People call it a bug and request for it to be fixed, Microsoft calls requests to fix it common and offers workarounds and overall current SQL Server behavior does not adhere to the standard (ISO-9075).

To see the bug reports (and vote); 126533, 299229, 311223 (closed), 387273. Basically they are each others duplicates and from those, the request 299229 has most attention and the best discussion.

Available workarounds;

• Filtered index (starting with SQL Server 2008)
• Indexed view (starting with SQL Server 2000)
• Triggers (not recommended as the check is after the modification is completed and if invalid has to perform a rollback)

... and don't say Microsoft didn't get it right because it's Microsoft... Access implements UNIQUE constraints correctly.

Microsoft launched a new preview site for SQL Server 2008 R2. You can sign up for notifications about the releases of CTP's.

From an administrative viewpoint, focus seems at tools to manage, utilize and consolidate instances and machines.

Towards usability (mainly business intelligence), enhancements to integrate with Office 2010 (mainly Excel and SharePoint) are featured. Also further integration with Virtual Earth is shown.

Finally there is mention about something called Master Data Services and the Stewardship Portal. In concept a very powerful way to create and maintain a data warehouse bus.

Updated 2010-07-24; added timeout to the receive command in the stored procedure. For more info, see Service Broker can make your transaction log big .

Event Notifications are based on Service Broker. This brings an advantage and a disadvantage; enhanced scalability at the cost of increased complexity (and lack of a User Interface, so we have to take it to T-SQL code). Lets take it there step by step for SQL Server 2005 and 2008;

1. Some code to cause deadlocks.
2. See how the deadlocks look like using the Trace Flags.
3. See how the deadlocks look like in Profiler.
4. Use the Event Notification on the deadlocks.
5. Sending out the mail.

The code shown as part of this article is also available at this link. To code to create the database for the deadlocks is available at this link. The code works with SQL Server 2005 SP2 and SQL Server 2008

1. Some code to cause deadlocks.

To get the code to cause the deadlocks, click this link. Run the code from SQL Server Management Studio (SSMS) to create the DeadLockSample database. Next create two additional connections (New Query) to the DeadLockSample database, execute the procedure procLockedTran1 on one connection and procLockedTran2 on the other connection. There is a 5 seconds delay in the two procedures between locking the first and second resource. So if you execute the two procedures within 5 seconds, you get your deadlock. You could also change the delay in the procedures if you think 5 seconds is to short/long.

Normally a process would be terminated when it is the deadlock victim. For the procedures created by the script, the error is handled , so the process that is the deadlock victim just selects it is the victim and the connection will not be terminated.

If you want to create a deadlock in any of the following scenarios, just run procLockedTran1 and procLockedTran2 on two separate connections simultaneously.

2. See how the deadlocks look like using the Trace Flags.

To get the deadlocks recorded in the SQL Server Error Log, either set the trace flag as startup parameter in SQL Server Configuration Manager (see picture) which will cause the trace flag to be active each time you start your SQL Server (until you remove the trace flag again), or issue a DBCC TRACEON (1222,-1) command which sets the trace flag until you stop SQL Server or issue a DBCC TRACEOFF (1222,-1).

With the trace flag active, you can now see the deadlock appear in you SQL Server Error Log.

3. See how the deadlocks look like in Profiler.

... or in SSMS for that matter. With the SQL Server tools it is possible to display deadlock graphs; XML documents (with .xdl extension) describing the deadlock information of one or more deadlocks. When the deadlock graph event (EventClass 148) with the TextData column is included in the trace definition, the XML to create the deadlock graph is captured (both through the Profiler graphical tool and the sp_trace procedures) and the graph will automatically be displayed when opened in Profiler. Also the graphical profiler has the option to write deadlock graphs directly to a file. Note that the graphical presentation of the deadlock is limited compared to the information in the .xdl document. The information in the .xdl document is the same information as the information written to the SQL Server Error Log with trace flag 1222. One extra comment if you filter your trace on database, you won't get the deadlock graph, as noticed by Kalen Delaney.

4. Use the Event Notification on the deadlocks.

Event Notification lets you capture events that can occur on server, database or broker queue, for a list of events, query sys.event_notification_event_types. For this purpose, the event type 1148, DEADLOCK_GRAPH is of interest. Also note that the DEADLOCK_GRAPH is a trace event, which can only be captured at server level.

The second thing needed is a broker SERVICE to send the event to. Since we'll be handling the events at server level, the SERVICE (and QUEUE) will be created in the msdb database. There is no need to create the MESSAGE TYPEs  or CONTRACTs as they already exist for Event Notification.

USE msdb
GO
-- Create a queue to receive messages.
CREATE QUEUE queDeadLock_Graph ;
GO
-- Create a service on the queue that references
-- the event notifications contract.
CREATE SERVICE svcDeadLock_Graph
ONQUEUE queDeadLock_Graph
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification] );
GO
-- Now query the sys.databases for the service_broker_guid of the msdb database.
SELECT service_broker_guid FROMsys.databasesWHERE name ='msdb';

The result of the query for the Service Broker GUID for the msdb databases is needed to point to the right database for the service.

-- Create the event notification.
CREATE EVENT NOTIFICATION evnDeadLock_Graph
ONSERVER
FOR DEADLOCK_GRAPH
TOSERVICE 'svcDeadLock_Graph'
, '2A7C5300-7C64-43F5-AB10-5B079D07678E' ; -- the GUID for msdb goes here.

You can look at the content of the queue through SELECT * FROM msdb.dbo.queDeadLock_Graph, as a queue in fact is a table. Next step will be processing the messages that get into the queue and more specifically get the deadlock details out of the message_body. The message body looks binary, but if you query it like this;

SELECT CAST (message_body ASxml)FROM msdb.dbo.queDeadLock_Graph

you'll notice that it is actually XML, where the textdata node holds the xdl structure of the deadlock graph.

5. Sending out the mail

Okay, we're in Service Broker land by now. Event Notification acts as the Initiator Service and the svcDeadLock_Graph is the Target Service. In this scenario, the only thing left to be done is specify how the queue should be processed. The processing shall be done by a stored procedure that will be tied to the queue as the activation program. Once something comes in to the queue, this stored procedure will be invoked... kind of like a trigger for insert on a normal table. The specification for the stored procedure will be:

1. Extract the deadlock graph from the message_body and store it in a variable.
2. Extract the datetime of the deadlock graph notification and store it in a variable.
3. Store the datetime and deadlock graph in a table.
4. Send the datetime as part of the message and deadlock graph as attachment in an email. This does require database mail to be set up, if you haven't, an error will be logged in the table.

The code to create the table to hold the deadlock information (and the reason why no mail was sent).

USE msdb
GO
CREATE TABLE dbo.tblDeadLock_Log (
DeadLock_ID intIDENTITY(1,1)CONSTRAINT pk_tblDeadLock_Log PRIMARY KEY
, DeadLock_Detected datetime
, DeadLock_Graph xml
, NoMailReason nvarchar(2048 ))
GO

The stored procedure to be used as the activation program on the queue, this is where the real work is done. Alter the @profile_name and @recipients in at sp_send_dbmail to match the setup of your dbmail setup.

CREATE PROCEDURE dbo.procReceiveDeadLock_Graph
AS
DECLARE @conversation_handle uniqueidentifier
DECLARE @message_body xml
DECLARE @message_type_name nvarchar(128 )
DECLARE @deadlock_graph xml
DECLARE @event_datetime datetime
DECLARE @deadlock_id int
BEGIN TRY
BEGIN TRAN
WAITFOR (
RECEIVE TOP (1) @conversation_handle = conversation_handle
, @message_body =CAST(message_body ASxml )
, @message_type_name = message_type_name
FROM dbo.queDeadLock_Graph)
, TIMEOUT 10000 -- Line added 2010-07-24;
-- http://resquel.com/ssb/2010/07/24/ServiceBrokerCanMakeYourTransactionLogBig.aspx
-- Validate message
IF (@message_type_name ='http://schemas.microsoft.com/SQL/Notifications/EventNotification' AND
@message_body.exist('(/EVENT_INSTANCE/TextData/deadlock-list)')= 1 )
BEGIN
-- Extract the info from the message
SELECT @deadlock_graph = @message_body.query('(/EVENT_INSTANCE/TextData/deadlock-list)' )
, @event_datetime = @message_body.value('(/EVENT_INSTANCE/PostTime)[1]','datetime' )
-- Put the info in the table
INSERT dbo.tblDeadLock_Log(DeadLock_Detected, DeadLock_Graph )
VALUES (@event_datetime, @deadlock_graph )
SELECT @deadlock_id =SCOPE_IDENTITY ()
-- Send deadlock alert mail.
-- Requires configured database mail, will log an error if not (or anything else goes wrong).
BEGIN TRY
DECLARE @subj nvarchar(255), @bdy nvarchar(max), @qry nvarchar(max), @attfn nvarchar(255 )
SELECT @subj ='A deadlock occurred on '+ @@SERVERNAME
, @bdy ='A deadlock occurred at '+CONVERT(varchar(50),@event_datetime, 120)+' on SQL Server: '+@@SERVERNAME+ '. See attached xdl-file for deadlock details.'
, @qry ='SET NOCOUNT ON; SELECT deadlock_graph FROM msdb.dbo.tblDeadLock_Log WITH (READUNCOMMITTED) WHERE DeadLock_ID = '+CAST(@deadlock_id ASvarchar(10 )) -- Locking hint is to prevent this dynamic query to be blocked by the lock held by the insert. The dynamic SQL will not come from inside this transaction.
, @attfn =@@SERVERNAME+'_'+CAST(@deadlock_id ASvarchar(10))+ '.xdl'
EXEC sp_send_dbmail @profile_name = 'default'
, @recipients = 'administrator@localhost'
, @subject = @subj
, @body = @bdy
, @query = @qry
, @attach_query_result_as_file = 1
, @query_attachment_filename = @attfn -- http://support.microsoft.com/kb/924345
, @query_result_header = 0
, @query_result_width = 32767
, @query_no_truncate = 1
END TRY
BEGIN CATCH
UPDATE dbo.tblDeadLock_Log
SET NoMailReason =ERROR_MESSAGE ()
WHERE DeadLock_ID = @deadlock_id
END CATCH
END
ELSE -- Not an event notification with deadlock-list
END CONVERSATION @conversation_handle
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
GO

Finally to tie the stored procedure to the queue so incoming notifications will be processed.

ALTER QUEUE dbo.queDeadLock_Graph
WITH
STATUS = ON ,
ACTIVATION (
PROCEDURE_NAME = msdb.dbo.procReceiveDeadLock_Graph ,
STATUS = ON ,
MAX_QUEUE_READERS = 1 ,
EXECUTE AS OWNER )
GO

Now try the procedures procLockedTran1 and procLockedTran2 and see the results in the msdb.dbo.tblDeadLock_Log and in your mail (if configured).

Okay, here's the deal; deadlocks sometimes occur and you want to be notified as they happen.

So define an SQL Agent Alert on error 1205 to send you an e-mail notification... partially, but it won't work as error 1205 isn't logged.

How to get the error logged; either set the severity level to 19 or above when the message is created (does not apply to 1205) or flag the message to be logged to event log, which can be done using sp_altermessage... at least this was the way to do it with SQL Server 2000 but one of the changes for SQL Server 2005 says:

sp_altermessage cannot be used to change the logging behavior of system messages (messages with Message ID < 50000). To audit system messages, use SQL Trace and the User Error Message Event Class. For more information, see Introducing SQL Trace.

For SQL Server 2005 it used to be a dead end, but with as of Service Pack 3 it is again possible to alter sys.messages < 50000 so they are written to the Windows NT Application Event Log. Specifically for deadlocks you'd run:

EXEC sp_altermessage 1205, 'WITH_LOG', 'true'

but it would apply to other system events you want logged too.

Update 2009-04-08; issue fixed in Service Pack 1 for SQL Server 2008. For SQL Server 2008 (up until Cumulative Update 3) it is still impossible to alter sys.messages < 50000. Though it was mentioned (2008-07-28) at connect in FeedBackID=294122 that this issue would be fixed for SQL Server 2008 too, so far (2009-02-27) it isn't.

Besides the fact that the alert option is still impossible with SQL Server 2008, it can only tell you a deadlock occurred. Should you need more information on the deadlock however, your next option would be TRACE flags 1204 or 1222, the difference between the two of them being the amount of detail. Trace flag 1222 will be use during the post as it provides the most detail. Setting the trace flags writes the deadlock information to the SQL Server Error Log, but not to the Windows NT Application Event Log... so there's one two (and a half) option remaining:

Process the Error Log with the undocumented sp_readerrorlog as part of the alert response... but that won't work for SQL Server 2008 (yet) and you have a better option for SQL Server 2005. For more info see MSSQLTips.

SQL Server 2005 & 2008: Event Notification

SQL Server 2005 & 2008: WMI Alert, see MSDN

So next post will be on getting your deadlock info through Event Notification.

For those of you who are not familiar with the tool Relog; it is part of Windows and allows you to reprocess System Monitor logs. This is quite useful, as logging counters to the binary format (.blg) is very space efficient. However to analyze those counters, you may want to use SQL Server. Relog allows to rewrite the log and also creates the tables for you on SQL Server, if not yet present (syntax samples). The unfortunate part is in the CounterDateTime column of the CounterData table, despite the column name, the date type is char(24). When trying to convert the char(24) to a datetime, this throws the error;

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.

I was getting a bit annoyed by this error, as a conversion from a char to a datetime should succeed if the string is valid... and say 2008-09-30 12:10:15.322 looked valid. I even checked Tibors ultimate datetime guide to see if I missed something, because I was unsure of the impact of the last byte (a valid string yyyy-mm-dd HH:mi:ss.sss actually is 23 characters). Also when trying to insert a string with a valid datetime in a variable, the conversion succeeds. Next I turned my attention to how the data was stored in the CounterDateTime column, by inserting a valid datetime string and retrieving one that was inserted by Relog (both returning the string (char(24)) and its binary representation varbinary(24)).

Source	Character representation	Binary representation
Relog insert	2008-09-30 12:10:15.322	0x323030382D30392D33302031323A31303A31352E33323200
Manual insert	2008-09-30 12:10:15.322	0x323030382D30392D33302031323A31303A31352E33323220

The difference is in the last byte of the binary representation, the datetime string written by relog isn't padded with spaces as one would expect to happen for the unused positions in a char. Instead it is zeroed as one would expect for unused bytes in a binary string. To get a datetime representation of the CounterDateTime a double conversion is needed;

CAST(SUBSTRING([CounterDateTime],1,23) AS datetime)

Now for the "nasty" in Relogs database schema:

• Changing the CounterDateTime to datetime is a no-go. Relog does not want it.
• Adding a computed column with the conversions above is a no-go. Relog does not have all columns bound.

What remains is creating a view on top of the CounterData table with this conversion included.

Another issue that may save you some time, the DSN you create to access the SQL Server should be the "SQL Server" provider, not the "SQL Native Client".

A while back, I wrote about using sp_ procedures in master to create a toolbox, which works fine for me... until recently I hit a strange problem. I developed a procedure that would read the definition of a table and would create a change_log table and trigger to populate that table. On my dev-environment (Windows Vista x64, SQL Server 2005 Dev Ed x64 SP2) this worked like I expected. But when I tested the procedure on a different server, it didn't!?! A very little sample that touches the essence of the problem I witnessed;

use master
go
if object_id ( 'sp_test' , 'p' ) is not null
drop procedure sp_test
go
create procedure sp_test
as
select db_name () + '.' + table_schema +'.'+ table_name
frominformation_schema. tables
order by table_schema, table_name
go
exec sp_test
go
use adventureworks
go
exec sp_test
go
use master
go
drop procedure sp_test
go

Now on my dev machine, this once listed all tables in master and once all tables in AdventureWorks, as intended. But on other servers, it returned the tables from master on both executions of sp_test (the db_name function is executed correctly in both cases; once master, once AdventureWorks). For some reason, when referencing objects, the stored procedure stayed to master. I was puzzled, but before crying BUG (out loud) I tried the newsgroups and a prompt reply from SQL Server MVP Roy Harvey pointed me to the undocumented stored procedure sp_MS_marksystemobject. Simply execute;

exec sp_MS_marksystemobject sp_test

and you won't suffer from the inconsistent behavior I witnessed.