New version of DB Goby soon ready!

A new version of DB Goby is soon ready.

New features:

1) Moving database files will be easier.
It will now be possible to move files for severeal databases at once, based on a rule set. In this way it is easier to i.e. move all data files on a server to a second disk, and all log files to a third disk. This in just one operation.

This is a great feature when you run out of disk space on your server, and need to add more disks, or if you need to tidy up your placements of data files.

2) Database file management.

• Create file groups and files in an easier way based on customizable templates.
• Redistribute data over several files within a file group, so that data is evenly spread over the files.

When you are adding new files to a filegroup, the existing data will still remain in the old files resulting in an unbalanced file distribution. If you are designing your server architecture like described in the article "Physical Database Storage Design", you will get a better I/O after using this redistribution feature.

The new version is estimated available in a week or two.

Best regards
Øystein

Orphaned Transactions

If you are using distributed transactions (MS DTC), it may happen that Sql Server loses the connection to the coordinator and the transactions will neither be commited or rolled back.
The locks by this transaction will remain on the server until you manually remove it, or restart the server.
You will see these locks in the Activity Monitor with a Process ID (PID) = -2

You can't just kill the -2 PID, so you need to get hold of what the Unit of Work ID is for these transactions by running the following query:

select request_owner_guid
from sys.dm_tran_locks
where request_owner_guid <> '00000000-0000-0000-0000-000000000000'

For those UoW IDs, run the KILL command, like this:

KILL 'B8041DFE-0732-418F-98AA-FE4D05DA554B'

Problems with Dual Core Processors

I have a computer with a dual core processor myself, and I experienced problems with some computer games where the internal CPU timer really messed up the game. The graphics went faster and faster until my computer crashed with a blue screen. One trick was to set the CPU affinity on the application to one core. But that wasn't a satisfying solution.

I used a lot of time reading discussion forums to get rid of the problem.

The first recommondation I got was to try a hotfix that Microsoft had released, but that didn't solve the problem, it only introduced some more.

I also disabled the Qool'n'Quit technology in the BIOS, that helped some, but there was still a problem.

Later AMD came with a driver update that fixed the problem, and things seemed to work ok.

Now I have been introduced to similar problems for SQL Server. I know there has been problems with time stamping both for SQL Server 2005 and Reporting Services due to CPUs that change frequencies.

Microsoft has released a KB about this that you should read, if you have a servers running on dual core processors and you are getting strange error messages (i.e. I/O timeout).

http://support.microsoft.com/default.aspx?scid=kb;EN-US;931279

How NULL values affect NOT IN queries

Using NOT IN queries may give unexpected results when the sub query contains Null values. I'll give you an example:

We create a table:

Create table #Person
([Id] int identity, [Name] nvarchar(20) not null, [Spouse] int null)

insert into #Person([Name], [Spouse])
select 'Jim', 2
union all
select 'Nora', 1
union all
select 'Jane', null

Jim and Nora are married to each other. Jane is unmarried.

Lets try a query that counts unmarried people, using NOT IN:

select count(*) as [Unmarried]
from #Person where [Id] not in
(select [Spouse] from #Person)

Unmarried
-----------
0
(1 row(s) affected)

So none is unmarried?

Ok. Let's try something different. Let's make a query to find out how many is not married to 'Jim':


select count(*) as [Not married to Jim]
from #Person where [Id] not in
(select [Spouse] from #Person
where [Name] like 'Jim')


Not married to Jim
------------------
2
(1 row(s) affected)

Hmm. Here we got a correct result. Is it because the sub query does not contain a Null value? Let's try getting another query where the sub query contains Null:


select count(*) as [Not married to Jane]
from #Person where [Id] not in
(select [Spouse] from #Person
where [Name] like 'Jane')

Not married to Jane
-------------------
0
(1 row(s) affected)

Ok? Appearantly, all are married to Jane...

Lets try the first query with Not Exists, instead of Not In:

select count(*) as [Unmarried]
from #Person P1 where not exists
(select 1 from #Person P2 where P2.[Spouse]=P1.[Id])

Unmarried
-----------
1
(1 row(s) affected)

Here we got a correct answere.

I guess the conclusion is to be very aware when using Not In in a query. If Null is in the result in the sub query, you most likely will get a wrong result. Use Not Exists rather then Not In if possible.

Changing Sql Server Edition

Have you been in a situation where you have installed and used Sql Server Standard Edition, but need features from Enterprise Edition?
Or perhaps you have Enterprise Edition but should have installed Standard Edition due to licence costs?
I have even experienced vendors to install Developer Edition on a production server, which is not allowed!

If you need to change to another edition, you might have serious issues to uninstall the server so you can install the preferred edition. All information in the system databases will be lost.

Off course you can do a restore of the msdb and tempdb databases to get jobs etc back, but the master database can't be restored due to different version numbers.

The master database might contain user defined procedures, functions, sysmessages, extended procedures etc. that are vital for the applications.

So what do you do?

Well, you can actually change the edition without reinstalling. Just run the setup.exe file from command prompt with the SKUUPGRADE parameter:

start /wait setup.exe ADDLOCAL=SQL_Engine INSTANCENAME=MSSQLSERVER
UPGRADE=SQL_Engine SKUUPGRADE=1 /qb

This will work for the default instance. For a named instance you will have to change some of the parameters.

After installation, remember to reinstall Service Packs.

Be sure to take a backup of your databases before doing this! ;-)

Check out the install features at: http://msdn2.microsoft.com/en-gb/library/ms144259.aspx