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

PASS SQL Summit 2009 Discount Code

With time running out for taking advantage of the pre-registration discounts to PASS 2009, here is a discount code for $100 off the registration fee. Get $100 off SQL Summit with code 24HR3D. See you at PASS 2009!

Microsoft Launches Non-Profit Open Source Foundation

I’m not sure what to think of this. I have always been a fan of the open source movement. I know, then why am I a Microsoft DBA? I can only say, at the time, my employers required me to support Microsoft products. And after working with SQL Server for many years, I became comfortable with it.

Anyway, Microsoft launched a new Non-Profit Open Source Foundation yesterday named, The CodePlex Foundation. And already, there are a lot of discussions about it. I think that Microsoft is trying to continue to build its community by having developers create open source software that will assist the community as a whole.

Also, I have no doubt that Microsoft has a business motivation in this as well, because after all, Microsoft is a profit driven company. It could be that Microsoft would like to see if there are ways for them sell their products to entities that run open source, or it could be something more. Either way, I don’t believe open source developers will embrace it as evident in this Slashdot post.

The CodePlex Foundation : http://codeplex.org/

New SQL Server Background

I just picked up on Twitter that Buck Woody (MSFT) has published a blog post about SQL Server Desktop Screen Background. Use the link below to get it.

http://blogs.msdn.com/buckwoody/archive/2009/09/07/sql-server-desktop-screen-background.aspx

SQL Server Snapshot Sparse File Size

When you execute the CREATE AS SNAPSHOT sql statement, the snapshot creates a NTFS sparse file. In a sparse file only “nonzero” information is stored on the disk. The maximum size of a database snapshot is the size of the source database at snapshot creation. In Windows Explorer, the snapshot files are shown with a size matching that of the source database. However, when you go into the file properties you will see that the size on disk is 0KB or a multiple of 64KB (representing 1-8 data pages) if some data has been transferred.

At the beginning, there is no source/user data in the snapshot file. It’s only after changes are made in the source database that data pages get written to the snapshot file. When a data page in the source database is changed, the original data page is first copied to the snapshot file before updating in the source database (copy-on-write). The first time any page is written to the sparse file the entire 64K extent is allocated. All other pages in the extent are written as zero's.

When a sparse file is populated, Windows limits the amount of data that may reside in the file. Once the amount of data stored in the sparse file exceeds the limit further data storage in the file may be prevented.

• Windows 2003 - 64GB (Error 1450 returned)
• Windows 2008 and Vista - 16GB (Error 665 returned)

Windows 2003 Error: The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x000031abb4e000 in file with handle 0x00000F74. This is usually a temporary condition and the SQL Server will keep retrying the operation. If the condition persists then immediate action must be taken to correct it.

Windows 2008 Error: The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x000005bd3dc000 in file 'Test.mdf:MSSQL_DBCC8'

Let’s take a look at what takes place when the snapshots are created.

• A sparse file is created. This file will have the same file size as the database. Remember, the only data that may be contained in the snapshot file at this point are pointers to the source database data files. Only after something changes in the source database will copy-on-write insert data into the snapshot file.
  
• The database is checkpointed (all dirty pages in the buffer cache are written to disk).
  
• If uncommitted transactions are in progress in the database, the snapshot will reflect the state of the database before these transactions started in other words, in the context of the snapshot, these transactions will be rolled back – but transactions in the database itself are not.
  
• The snapshot is brought online and is ready for use.
  

Quick Note:

From a Disaster Recovery standpoint, SQL Server snapshots are not very useful as they require an online database to function. In situations where there is a hardware failure, the database is no longer online and the snapshot becomes useless. Also, snapshots cannot be restored when users are accessing the database. So if there is a roll back in progress or some other process that does not allow the database’s state to be changed, snapshot restores can not be applied. However, in situations where an object was accidently deleted or altered, and needs to be restored, snapshots are ideal.

For a more detailed look inside how Copy-On-Write works, see: http://blogs.msdn.com/psssql/archive/2009/01/20/how-it-works-sql-server-sparse-files-dbcc-and-snapshot-databases-revisited.aspx

Can’t Delete Jobs (Microsoft SQL Server, Error: 547)

I was creating a new job to handle weekly database maintenance and when I attempted to delete the old job, I received the following error;

TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop failed for Job 'Weekly_Maintenance'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Job&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.

The statement has been terminated. (Microsoft SQL Server, Error: 547)
------------------------------

If you try deleting the job from the sysmaintplan_subplans table, you also get the following error:

Msg 547, Level 16, State 0, Line 41

The DELETE statement conflicted with the REFERENCE constraint "FK_sysmaintplan_log_subplan_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_log", column 'subplan_id'.

I wanted to create a stored procedure that I could just pass a job name to and it would check each of the tables and delete all references for me. Here is what I came up with.

CREATE PROC dbo.DropJob
@JobName AS VARCHAR(200) = NULL
AS
DECLARE @msg AS VARCHAR(500);

IF @JobName IS NULL
BEGIN
SET @msg = N'A job name must be supplied for parameter @JobName.';
RAISERROR(@msg,16,1);
RETURN;
END
IF EXISTS (
SELECT subplan_id FROM msdb.dbo.sysmaintplan_log WHERE subplan_id IN
( SELECT subplan_id FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN
(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName)))
BEGIN
DELETE FROM msdb.dbo.sysmaintplan_log WHERE subplan_id IN
( SELECT subplan_id FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN
(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName));

DELETE FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN
(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName);

EXEC msdb.dbo.sp_delete_job @job_name=@JobName, @delete_unused_schedule=1;
END
ELSE IF EXISTS (
SELECT subplan_id FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN
(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName))
BEGIN
DELETE FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN
(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName);

EXEC msdb.dbo.sp_delete_job @job_name=@JobName, @delete_unused_schedule=1;
END
ELSE
BEGIN
EXEC msdb.dbo.sp_delete_job @job_name=@JobName, @delete_unused_schedule=1;
END
GO

Now you can call the SP with the following;

USE [msdb];
EXEC dbo.DropJob @JobName = N'Shrink_AWP_Databases.Subplan_1';