November 2005 - Posts

You may have noticed the small banner above this and all my posts. Unfortunately this has been done by the owners of SQL Junkies and I have no control. I have tried to get round it however I believe I have no choice as they done it in an iframe.


Posted by simonsabin | with no comments

Tony has started a new feature, fortnightly briefings via LiveMeeting.

These are intended as being an overview of whats happening in SQL Server land, I think its a great idea. Whats more is they are recorded and that allows you to what at maybe a more convenient time. You can also what in a shorter period by upping the speed in Windows Media Player, and the sound is perfectly audible. Sounds like Tony's been on the helium.

As well as whats going on Tony is intending on doing demos, this months is on errorhandling in SQL Server 2005

To look at this months briefing go to

Posted by simonsabin | with no comments

There are a number of sites that offer free training or dedicated training however few offer the level of interaction you get at the UK Usergroup.

Itzik Ben-Gan is holding an advanced query performance These events are very interactive sessions and speaking from experience well worth the money. It is rare to have an idividual of such stature available for such dedicate training. The events are designed to ensure you get as much as possible out, so make sure you go ready to learn.


Posted by simonsabin | with no comments
Filed under: ,
Scalability and performance are to area that some often t ink is a black art. Well it is definitely a skill to know how to identify and resolve. To assist anyone doing this the bods from the customer advisory team, customer support team and members of SQL Server Database engine team have written a Performance white paper to assist. Its full of great stuff.
Thanks to EuanG-
Posted by simonsabin | 1 comment(s)
Filed under:
Ken Henderson has posted here about his attempt to blog every day. I agree that blog content should be relevant and not just online pointers. I try and post when I learn something new especially if I found it hard to find the information.
What I find the most time consuming part is making sure what I post is well thought out and correct.(most of the time).
This does often result in some posts building up, but I think that’s acceptable. Maybe these should be articles but that requires a bit more effort.-
Posted by simonsabin | with no comments

I was looking into a question raised at the lauanch about the best way to pass large sets of values to sql. Currently the person is restricted by the 8000 character limit for a varchar in SQL 2000. A number of options where mentioned including bulk inserting, xml, a user defined type and a varchar(max).

Well in looking into the last, varchar(max) I came across some interesting behaviour.

If you declare a variable as varchar(max) then it does not force an expression that is used to set the value to use varchar(max) let me show an example

declare @t nvarchar(max)
@t = replicate('simon,',2000) + 'some more text'
select len(@t)

This highlights that the expression replicate('simon,',4000) is evaluated to be of type varchar(8000) when the text 'som more text' is added the variable is already full and so is not added, thus the length of @t is 8000.

This seems to be because of the 'simon,' literal that does not have an explicit type, and so the implied type is a varchar that by default has a maximum length of 8000. If the literal is explicitly converted to varchar(max) then we have a different result.

declare @t nvarchar(max)
@t = replicate(cast('simon,' as nvarchar(max)),2000) + 'some more text'

This results in a string of length 12014, as expected.

From what I can deduce the data type is determined as the largest data type in the expression, unfortunately the engine does not figure out that the combination of two variables may require the use of the (max) data type and so truncation occurs.

Therefore to avoid this make sure that all components of an expression evaluate to the correct datatype, i.e. literals are explicitly converted to the required data type. You could be conservative as in the above example however to avoid confusion it may be better to convert all literals, i.e.

declare @t nvarchar(max)
@t = replicate(cast('simon,' as nvarchar(max)),2000) + cast('some more text'as nvarchar(max))

Update: Corrected barckets in last code snippet

Posted by simonsabin | 4 comment(s)
Filed under: ,

There has been lots of speculation about the new google service. Well its now live

Upload all your stuff, recipes, products, reviews, jobs, you can even create your own type.

Posted by simonsabin | with no comments

Another question raised at the chalk and talks was how does a database that is acting as a mirror become resynchronised if database mirroring is paused. For some background on this. You may want to pause mirroring to patch your mirror server, or do some other maintenance on the server.

Database mirroring is all based around the transaction logs. The resync process applies the transactions in the transaction log that occurred during the time the mirroring was paused.

What does that mean to your transaction logs? Well all the transactions have to be in the active transaction log for them to be transferred which means that whilst mirroring is paused, you cannot backup or truncate those transactions from the log. So yes your log will grow and grow and grow, until mirroring is either restarted or stopped.

So how do manage long periods of downtime? 2 things you need to consider are, 1. the disk space required to hold all those transactions and 2. the impact on you network of having to send all those transactions over the network when you restart mirroring. You maybe better off stopping database mirroring and backing up the transaction log, transferring the backups to the mirrror server, restoring then and then re-establishing mirroring.

I hope that answers your questions.

Posted by simonsabin | with no comments
Filed under:

Over the next few weeks I will be following up on the questions that were asked at the chalk and talks which we didn't have answers for. The first question was one raised today about database mirroring.

Q. How can you find out the latency of a database mirror pair?

A. As with a lot of questions like this that require information about the server the answer is DMVs. There is a dmv (sys.dm_db_mirroring_connections) which provides the following information. In addition there are perfmonance management counters that provide information about the mirroring  for a database

Column name Data type Description
connection_id uniqueidentifier Identifier of the connection.
transport_stream_id uniqueidentifier Identifier of the SNI connection used by this connection for TCP/IP communications.
state smallint Current state of the connection. Possible values:

1 = NEW




state_desc nvarchar(60) Current state of the connection. Possible values:





connect_time datetime Date and time at which the connection was opened.
login_time datetime Date and time at which login for the connection succeeded.
authentication_method nvarchar(128) Name of the Windows Authentication method, such as NTLM or KERBEROS. The value comes from Windows.
principal_name nvarchar(128) Name of the login that was validated for connection permissions. For Windows Authentication, this value is the remote user name. For certificate authentication, this value is the certificate owner.
remote_user_name nvarchar(128) Name of the peer user from the other database that is used by Windows Authentication.
last_activity_time datetime Date and time at which the connection was last used to send or receive information.
is_accept bit Indicates whether the connection originated on the remote side.

1 = The connection is a request accepted from the remote instance.

0 = The connection was started by the local instance.
login_state smallint State of the login process for this connection. Possible values:

0 = Initial

1 = Negotiate

2 = SSPI

3 = PublicKeyLogin

4 = PublicKeyTentative

5 = LoggedIn,

6 = Arbitration
login_state_desc nvarchar(60) Current state of login from the remote computer. Possible values:







peer_certificate_id int The local object ID of the certificate used by the remote instance for authentication. The owner of this certificate must have CONNECT permissions to the database mirroring endpoint.
receives_posted smallint Number of asynchronous network receives currently posted for this endpoint.
is_receive_flow_controlled bit Whether network receives have been postponed due to flow control.
sends_posted smallint The number of asynchronous network sends posted and not complete for this connection.
is_send_flow_controlled bit Whether sends have been postponed due to network flow control because the network is busy.

1 = True
total_bytes_sent bigint Total number of bytes sent by this connection endpoint.
total_bytes_received bigint Total number of bytes received by this connection endpoint.
total_fragments_sent bigint Total number of database mirroring message fragments sent by this connection endpoint.
total_fragments_received bigint Total number of database mirroring message fragments received by this connection endpoint.
total_sends bigint Total number of network send requests issued by this endpoint.
total_receives bigint Total number of network receive requests issued by this endpoint.
encalg smallint Encryption algorithm used for this connection. Possible values:

1 = No Encryption

2 = RC4

3 = AES
Posted by simonsabin | with no comments
Filed under:

There is an undocumented function in SQL Server for performing the conversion of a binary value into a string representation of the binary value i.e. 0x0233DAF to '0x0233DAF'. You can't just convert to a varchar because the varchar is just the characters representing the ascii values in the binary i.e

select cast(0x73696D6F6E0000000000 as varchar(20))

results in 'simon', rather than '0x73696D6F6E0000000000'

The function in question is dbo.fn_sqlvarbasetostr.

What is odd is that the function is still undocumented in SQL Server 2005 although its still present. This may suggest that it might not exist in the future so be warned.

Posted by simonsabin | with no comments
Filed under:
More Posts Next page »