Fixing – Log Reader Performance : Critical (Seen in SQL Replication Monitor)

I ran into a problem with a Transactional Publication today.

I’d moved the database from old hardware, to newer hardware, and in the process, restored the SQL 2000 database onto SQL 2008 (running in SQL 2000 compatibility mode)

I’d re-created the publication, and initialized it.

After about half an hour, I checked the Replication Monitor, and noticed that it said:

Performance : Critical

Unfortunately, I don’t have a screenshot

Latency was also somewhere in the mid 40’s

I noticed that my recovery mode was FULL.

I changed this to BULK LOGGED, re-initialized replication, and this dramatically reduced the latency – down to 0.0 in fact!

Upgrading from Navision 4 to Navision 5 – Database Error

This is a tad of a messy blog post, so apologies for that, however it’s mainly for my own reference.

Here at Crocus, we run Navision to handle product inventory, orders, and the like…
We wanted to upgrade from version 4, to version 5.

After following the upgrade instructions, I got this error:

Msg 8662, Level 16, State 0, Line 1
Cannot create the clustered index "VSIFTIDX" on view "Nav5.dbo.Crocus Live$OrderRequestDetail$VSIFT$1" because the view references an unknown value (SUM aggregate of nullable expression). Consider referencing only non-nullable values in SUM. ISNULL() may be useful for this.

Annoying.

There wasn’t anything obviously wrong.
So I opened up SQL Profiler, and re-ran the update process in the Navision client, to capture the exact SQL queries that were causing the error….

I saw it created a view called Crocus Live$OrderRequestDetail$VSIFT$1

Heres the create view script:

CREATE VIEW dbo."Crocus Live$OrderRequestDetail$VSIFT$1"
WITH SCHEMABINDING
AS
SELECT "PaidFor",COUNT_BIG(*) "$Cnt",SUM("Amount") "SUM$Amount",
SUM("AmountIncludingVAT") "SUM$AmountIncludingVAT"
FROM dbo."Crocus Live$OrderRequestDetail"
GROUP BY "PaidFor"

It was failing on the following query:

CREATE UNIQUE CLUSTERED INDEX "VSIFTIDX"
ON dbo."Crocus Live$OrderRequestDetail$VSIFT$1" ("PaidFor")

Problem was:

Some of the columns specified as “boolean” within Navision, were in fact NULLABLE tinyint columns within SQL Server.

I suspect that this was due to some legacy version of SQL not supporting bit columns, or something along those lines…

Anyway, none of the values in the column (close to a million rows within the table) was null, so I changed the datatype to BIT and set NOT NULL. I did this for each of the columns in the table which should of been a boolean (and were specified in the CREATE VIEW statement (in my case was Amount, AmountIncludingVAT and PaidFor)

This immediately solved the “Cannot create the clustered index” problem

Hotel Floris Avenue, Brussels

Outside the Eurostar Thalys Terminus station in Brussels you will find an organized queue for taxis, (complete with taxi marshal)
We were allocated our taxi, and I presented the driver with a sheet of paper with the hotels address printed on it.
After some consideration, he said it would be 10 Euros, but that it’s only a 10 minute walk from the station.
We opted for the taxi, since I didn’t have a clue where I was going.
Yes, I could of used my iPhone to get us there, with a little help from Google maps, but the charges for data, even in the EU. Put me off.

The day we arrived in Brussels (10th May) there was some special festival, so there were many people out in the streets.

We pulled up outside the hotel around 5 minutes after we left the station (it really would be a 10 Minute walk)
First Impressions were good – hotel is nicely presented, clean and well lit.

Checking in, I noticed that you could buy Internet connection for 10 Euros for 24 hours (which is what I’m using to write this post!)

We were given room 304.

Room

Room was very clean, large, and spacious, with modern decor.
The bathroom was brilliant, had a bath, with shower.
The shower was extremely powerful, lots of pressure, which I like.
One thing that we noted – no soap was left for us on arrival but we did get it on day 2)

The bed, although advertised as a double, was actually two singles pushed together, but this did not matter. Was very comfortable.

We had a TV, with a type of Freeview box that allowed us to get BBC1 and BBC2.

Gym

The hotel was equipped with a “fitness room”
This was a little disappointing – not because there was not much equipment (there isn’t really room) but because none of it worked.
There is:
A treadmill
A cross trainer
A bike
A multigym – Lat pull down, leg extension, pec-dec etc.

This would of been adequate for our 2 night stay, however most of the equipment did not work.
The multigym had come off its pulley system, the treadmill was missing its safety key, the bike, didn’t move. The only thing working was the cross trainer.

Breakfast

Breakfast was served on level -1
Consisted of cereal, ham, cheese, fruit, pastries (croissants etc..) cooked sausages, bacon, eggs.
All in all, was very nice.

Location

The hotel is brilliantly located. Just a few minutes walk from Great Market Square and many shops.
Directly opposite the hotel was a supermarket, where we bought some things for the room, since the mini-bar was hideously expensive – ?3 for a can of coke!!

 Useful links

Official Site: http://www.hotelflorisavenue.com
Hotel Chain: http://www.florishotels.com
Trip Advisor: http://www.tripadvisor.com/Hotel_Review-g188644-d344865-Reviews-Floris_Avenue_Hotel-Brussels.html

Copying tables across databases

So, you’ve got a requirement to move or copy a table from one database to the other? Including data?

Use SELECT INTO.

For example, we have a database called “MyDatabase”
We also have another database – “MyNewDatabase”

MyDatabase contains the table “Customers” – with a bunch of data that we want to preserve.

You need to move this into MyNewDatabase?

Here is an example of the query:
[sql]SELECT * INTO MyNewDatabase.dbo.Customers
FROM MyDatabase.dbo.Customers[/sql]
You could of course predicate your query, include where clauses, select only a few columns etc.

Check out this post on W3Schools for more information, and how to use this in the same database (for temp. backups for example)

Trimming

If you have imported a large amount of data, or had some rogue users, helpfully putting a space at the beginning and / or end of a string, then this simple update will clear that out:

UPDATE TableName SET ColumnName = RTRIM(ColumnName)

Of course, if you only want to select the data, you could also:

SELECT RTRIM(ColumnName) FROM TableNames

SQL Server 2005 has both a RTRIM and a LTRIM function, which removes spaces from the Left or the Right of the specified column.

Examples:

Cities
‘London ‘
‘Paris’
‘Milan’

In the above example, London, has a trailing space (space at the end of the word)
In this instance, using:
[

SELECT RTRIM(Cities) FROM TableName

Would return:
Cities
‘London’
‘Paris’
‘Milan’

Ok, so what about a leading space?
For example,

‘ London’
Well, as above, but use

SELECT LTRIM(Cities) FROM TableName

to return ‘London’

But what if we have:
‘ London ‘
That’s unfortunate. Somehow, you have data with a leading AND a trailing space.

Well, your in luck.
You can run RTRIM together with LTRIM
Like so:

SELECT LTRIM(RTRIM((ColumnName)) FROM TableName

The same would of course also work for UPDATE:

UPDATE TableName SET ColumnName = LTRIM(RTRIM(ColumnName))