Feeds:
Posts
Comments

Archive for November, 2014

Earlier a few weeks ago, I ran into an interesting error message.

Msg 3098, Level 16, State 2, Line 10
The backup cannot be performed because 'COMPRESSION' was requested after the media was 
formatted with an incompatible structure. To append to this media set, either omit 
'COMPRESSION' or specify 'NO_COMPRESSION'. Alternatively, you can create a new media 
set by using WITH FORMAT in your BACKUP statement. If you use WITH FORMAT on an 
existing media set, all its backup sets will be overwritten.

Msg 3013, Level 16, State 1, Line 10
BACKUP LOG is terminating abnormally.

Recreate the Error

These are the steps I performed, when I ran into this error.

Step 1: Perform database backup using BACKUP LOG command without the clause “WITH COMPRESSION”

--
-- Perform LOG backup (without COMPRESSION)
--
BACKUP LOG Ahold_RT
TO DISK = N'M:\MSSQL\Backup\MSSQLSERVER\Without_Compression.TRN'
GO

Step 2: Perform backup again (using same file and location), but this time use “WITH COMPRESSION”

--
-- Now perform backup to the same file and location <strong>WITH COMPRESSION</strong>
--
BACKUP LOG Ahold_RT
TO DISK = N'M:\MSSQL\Backup\MSSQLSERVER\Without_Compression.TRN'
WITH COMPRESSION
GO

Result: Error message

Explanation

Two points:

  1. When Sql Server performs backup, it stores the backup file in a certain format.
  2. When we perform multiple backups using the same file, the new backup data is appended to the original backup file.

Given these, it is important the the first backup and second backup going to the same file and location, need to be in the same format.

Solution

  • Either perform backup to a different location. Or
  • Perform backup using “WITH FORMAT” clause to invalidate the previous file and then create a fresh new one with new format.
--
-- Perform backup using 'WITH FORMAT'
--
BACKUP LOG Ahold_RT TO DISK = N'M:\MSSQL\Backup\MSSQLSERVER\Without_Compression.TRN'
WITH COMPRESSION
   , FORMAT
GO

 

Hope this helps,

_Sqltimes

Read Full Post »

Quick one today:

Common Table Expression, since it was introduced in Sql Server 2005 is another great feature. The value is immense and cannot be understated.

  • It reduced the need for temporary tables and table variables in scripts a lot.
  • Make code more intuitive
  • All the tables in multiple CTE’s are touched only when the final query at the bottom of CTE is executed – resulting in better utilization of resources.
  • There are lot more uses, but I’ll stop here.

Today, I wanted to touch on one aspect of CTE. Ordering the data inside CTE. Due to the nature of CTE, ordering data inside CTE does not make sense.

Example:

--
-- This is not allowed
--
WITH Sample_CTE (ID)
AS (
     SELECT Col14 AS ID
     FROM SomeTable AS C
     INNER JOIN OtherTable AS S
          ON C.LocationID = S.LocationID
     ORDER BY C.PKID ASC
)
SELECT *
FROM Sample_CTE
GO

Error Message:

Msg 1033, Level 15, State 1, Line 6
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, 
and common table expressions, unless TOP or FOR XML is also specified.

CTE is just logical representation of a group of qualifying records. The order of records is not guaranteed — as it depends on several factors of the table storage (indexes, heap, order). But if you have TOP clause in the select statement, then it is allowed.

--
-- This is allowed
--
WITH Sample_CTE (ID)
AS    (
        SELECT TOP 1 Col14 AS ID
        FROM SomeTable AS C
        INNER JOIN OtherTable AS S
            ON C.LocationID = S.LocationID
        ORDER BY C.PKID ASC
    )
SELECT * FROM
Sample_CTE
GO

 

Hope this helps,

_Sqltimes

Read Full Post »

Quick one today:

On a table, we could create Clustered and NonClustered indexes. Each table can only have 1 clustered index, but multiple nonclustered index.

When a primary key is created on a column, it by default becomes Clustered index. But sometimes, we run into a situation where we need to separate PRIMARY KEY and Clustered index. It sounds counter intuitive, but this distinction is important and in some cases necessary to achieve better performance.

As an example, we use Product table.

To accomplish this, we need the following steps.

Step 1: Drop the Primary Key constraint. This also drops the Clustered index on that column. Recreate Primary Key constraint with Nonclustered index

Step 2: Create Clustered index on a different column

Step 1: Drop PK Constraint and recreate it without Clustered index

--
-- First DROP the Primary Key constraint and then recreate it with NonClustered Index
--
ALTER TABLE dbo.Product
DROP CONSTRAINT PK_Product_ID
GO

--
-- Notice the NONCLUSTERED clause
--
ALTER TABLE dbo.Product
ADD CONSTRAINT PK_Product_ID PRIMARY KEY NONCLUSTERED (Id ASC)
ON [nCI_01]
GO

Step 2: Recreate CLUSTERED INDEX without Primary Key

--
-- Create Clustered index on a different column
--
CREATE CLUSTERED INDEX CI_Product_Kiosk_ID
     ON dbo.Product (Kiosk_ID ASC)
WITH (
     SORT_IN_TEMPDB = ON
   , MAXDOP         = 2
   , FILLFACTOR     = 80
) ON [CI_01]
GO
Hope this helps,

_Sqltimes

Read Full Post »

Quick one today:

Rare, but sometimes, there is a need to INSERT specific values into a table that has an IDENTITY specified on one of the columns. Luckily, Sql Server provides mechanisms to insert data in such situations.

When we try to insert values into an IDENTITY column, we get errors like this.

Msg 544, Level 16, State 1, Line 1
 Cannot insert explicit value for identity column in table 'Product' when IDENTITY_INSERT is set to OFF.
--
-- Using IDENTITY_INSERT ON
--
SET IDENTITY_INSERT dbo.Products ON
GO

INSERT INTO dbo.Products (ProductID, ProductTypeID, Description, LastUpdate)
VALUES (1232, 345, 'tooth Paste', GETDATE())
GO

Now, after done inserting records, reset the IDENTITY_INSERT flag using SET statement.

--
-- Reset the IDENTITY_INSERT flag
--
SET IDENTITY_INSERT dbo.Products OFF
GO
Hope this helps,

_Sqltimes

Read Full Post »

Quick one today:

Earlier last month, while running server side traces on a lab Sql Server machine, I ran into this interesting error.

Error Details:

Windows error occurred while running SP_TRACE_CREATE. Error = 0x80070005(Access is denied.).
Msg 19062, Level 16, State 1, Procedure sp_trace_create, Line 1
Could not create a trace file.
Trace File Permissions

Trace File Permissions

 
 This is easy to fix. Go to the folder where you are attempting to store the trace files. Open the folder properties and go to Security tab.
Make sure the Sql Server service account is added to the permissions list with necessary read and write permissions.
Hope this helps,

_Sqltimes

Read Full Post »