MADExop: The Mid Atlantic Developer Expo in Hampton, VA June 30 - July 1, 2011

Ok, so I get an email from Andrew Duthie at Microsoft asking me to get the word out about the upcoming Mid Atlantic Developer Expo (MADExop) in Hampton, VA. Now this was the first time I had heard about it and really wanted go after I visited the website and read all the cool sessions they had lined up. But then, I saw that for only $20 you could bring your child for an all day kid geekout session! Now, what could be better? You go for two days of awesome and, did I mention cheep?, developer training from the best developers around and inspire your child to follow in your footsteps at the same time!

Take a look at the lineups for Thursday and Friday sessions:
Click here for a PDF of the Day 1 Agenda
Click here for a PDF of the Day 2 Agenda

Register here!
MADExpo 2011 Registration!

Idera's SQLsafe Restore Error "too recent to apply to the database"

Last week, my manager came to me and asked if I could perform our first restore since installing the new SQLsafe Backup and Recovery software. Because I was the one that pushed for SQLsafe to take over as our Backup and Recovery solution for all production SQL Servers, I was more than happy to show off SQLsafe's ease of use in restoring databases. I select the point in time that I wanted to restore to and clicked NEXT.

I was expecting for the database to be restored in no time, but instead, and with horror written all over my face, I received the following error message in the "Result Text" with a BIG RED "Error" Progress indicator next to my Restore status.

-------------------------------------------- snip -------------------------------------------

<!--[if gte mso 9]> Normal 0 false false false EN-US X-NONE X-NONE

" Server instance: INSTANCE/NAME, Database: mas500_pl

The log in this backup set begins at LSN 94000000227900001, which is too recent to apply to the database. An earlier log backup that includes LSN 94000000221200001 can be restored.

RESTORE LOG is terminating abnormally."

Normal 0 false false false EN-US X-NONE X-NONE

--------------------------------------------------------------------------------------------

With embarrassment, I turned to my manager and told him that I would call Idera's support staff and get help with restoring the database. I called the support number and got routed to a voice mailbox. However, within just a few minutes, I got a call back from Carl at Idera's Customer Support. I explained to him the issue I was having and after some brief research on his part, he said based on the message I was receiving that it looked like another backup had been done and that the SQLsafe backup that I was attempting to use was not the most current.

I then opened SSMS and started the restore database wizard. I selected the database that I wanted to restore and then clicked file and add to browse to the folder. Then SURPRISE! The default backup folder appeared with recent backups of the database that I was attempting to restore.

I then clicked on the SQL Server Agent and found a Full Backup job scheduled to run every Sunday at 2:00 am which was after the SQLsafe Full backups.

To restore the database, I first had to restore the native Full backup from Sunday morning with the following sql script;

RESTORE DATABASE [mas500_pl]

FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BACKUP\mas500_pl_db_201105010200.BAK'

WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

GO

Next I opened SQLsafe and restored each Differential, one at a time, with "Force restore", "Ingnore Checksum Errors", and with non-recovery, "Not accessible", until the last Differential. For the last Differential, I restored it with recovery or "Fully accessible".

So a BIG Thank You goes out to Carl at Idera's Customer Support!

PASS Summit 2010 Keynote David DeWitt

This was the best presentation during all of PASS Summit 2010. Everyone enjoyed Dr. DeWitt's keynote session on SQL Query Optimization.

PASS Summit 2010 Keynote David DeWitt

View more presentations from GraySystemsLab.

SQL Server Row Counts in Properties Are Wrong!

One of my users came to me and asked why the row count in the table properties was different from the SELECT COUNT(1) from the table. This was a database that was sent to us from a different company and attached to our server. The first question for me was; where does row count in properties come from in SQL Server? As it turns out, it gets this count from the sysindexes table which we can see from the following;

USE DatabaseName
GO
SELECT rowcnt
FROM dbo.sysindexes
WHERE OBJECT_NAME(id) = 'TableName'
GO

-- Returns
330013

A count of the table results in the following;

USE DatabaseName
GO
SELECT COUNT(1) AS CNT
FROM dbo.TableName
GO


-- Returns
331882



To update the sysindexes table with the correct row counts, we run the following DBCC;

USE DatabaseName
GO
DBCC UPDATEUSAGE(0) WITH COUNT_ROWS;
GO

Now when we run our queries again, we get the following;

USE DatabaseName
GO
SELECT rowcnt
FROM dbo.sysindexes
WHERE OBJECT_NAME(id) = 'TableName '
GO

SELECT COUNT(1) AS CNT
FROM dbo.TableName
GO

-- Returns
331882
-- Returns
331882



Cast Scientific Notation as Money or Varchar

I run into Scientific Notation on occasion and always refer back to my TSQL scripts folder. Most of the time, I'll have a column where about 50% of the rows contain Scientific Notation. In these cases, I use a CASE statement.

First, CAST as VARCHAR:

LTRIM(RTRIM(CAST(CAST('2.50823E+12' AS FLOAT) AS NVARCHAR)))

Second, CAST as MONEY:

LTRIM(RTRIM(CAST(CAST(CAST('2.50823E+12' AS FLOAT) AS NVARCHAR)AS MONEY)))


Now, let's see this in a CASE statement:

,CASE
WHEN [AMOUNT] LIKE '%E-%' THEN LTRIM(RTRIM(CAST(CAST(CAST([AMOUNT]AS FLOAT) AS NVARCHAR)AS MONEY)))
WHEN [AMOUNT] = ' ' THEN NULL
ELSE [AMOUNT]
END AS [AMOUNT]



CHECKDB - A severe error occurred on the current command. The results, if any, should be discarded.

CHECKDB found 0 allocation errors and 0 consistency errors in database

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.


I ran into a case where I had corrupted pages in my database. I had previously had this database on a SQL Server 2005 instance. When I ran CHECKDB in SSMS 2005, it returned info messages detailing the consistency errors. However, after restoring the database on a 2008 instance and running CHECKDB in SSMS 2008, I received the message above. At first, you get this, “What now!” feeling. Don't panic. If you go to the SQL error log, you’ll see the CHECKDB results. Be sure that you are running CHECKDB with the WITH ALL_ERRORMSGS option.

Adding Leading Zeros in T-SQL

Today I ran into a situation where I had a character data type column that contained product ID numbers and these numbers were missing leading numbers. The column was a CHAR(11) as all product IDs should begin with leading zeros and be 11 characters long. To add leading zeros to a NVACHAR, VARCHAR, OR CHAR type column, use the RIGHT function.

SELECT RIGHT('00000000000' + LTRIM(RTRIM(PRODUCT_ID)),11) AS PRODUCT_ID

Or;

SELECT RIGHT(REPLICATE('0', 11) + LTRIM(RTRIM(PRODUCT_ID)),11) AS PRODUCT_ID