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

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))