Table ' does not have the identity property. Cannot perform SET operation.

Error Message:


System.Data.SqlClient.SqlException: Cannot find the object "table" because it does not exist or you do not have permissions.

try again
System.Data.SqlClient.SqlException: Table table does not have the identity property. Cannot perform SET operation.
at table.DatabaseIO.UpdateKey(Key key, KeyGroup keyGroup)
at table.Interface.AddKey.btnSave_Click(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at DevExpress.XtraEditors.BaseButton.OnClick(EventArgs e)
Description:
This error message appears when you try to use the SET IDENTITY_INSERT setting for a table that does not contain a column, for which the IDENTITY property was declared.

Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.

Resolution:
Error of the Severity level 16 are generated by the user and are corrigible by the user. The SET IDENTITY_INSERT setting cannot be used on such a table.

Versions:
All versions of SQL Server.

Example(s):
USE test
GO

CREATE TABLE test2
(
test1 int)
GO
SET IDENTITY_INSERT t ON
INSERT INTO test1 SELECT 1
SET IDENTITY_INSERT test1 OFF
DROP TABLE test1
GO

Remarks:
In the above example we try to turn on the IDENTITY_INSERT setting für the table test1 to insert an explicite value into a column for which the IDENTITY property was declared. Because there no such column in table test1, the error is raised.

How to enable access to sql proflier without sysadmin

If a member of your team wants to have trace rights to diag a issue you can grant alter trace.

grant Alter trace to username
go

to stop this right

deny alter trace to username
go

Setting up transactional replication without a snapshot

We had a case where a client needed replication from London to Hong Kong and New York, needless to say we setup the transaction replication we a snapshot and due to the latency issues the snapshot never worked. I decedied to set up replication without the snapshotm heres how to do it if you need to know.

Normally its very easy to setup the publisher and subscriber by default. Thanks to the handy Wizard interface. However, we faced a tiny bit of a problem when using the wizard.

If you are replicating on the same domain and in the same country using the wizzard is great but this is probably only a small about of situations.

I had a problem with the initial snapshot creation as said above when setting up the transactional replication. This is using SQL Server 2008. The initial snapshot creation took a very long time and would not complete. Even after running it over a weekend, for 48 hours, it did not complete. The thing is, while the snapshot agent was running, the database was not accessible to other users. Is there a way to make the snapshot run under low priority?

Since I had to allow users access to the database, I had to stop the snapshot job. Will it start from scratch if restarted or will it continue from where it stopped? I tested and it seemed to start from scratch once again. Another 8+ hours? I don’t think so. Thus, I set out to find a way to create a transactional replication without a snapshot.

I did a little bit of checking around to find out how to do this as we are all DBAs but a DBA does not know everything hence why you are also reading this site. I found a stored procedure called sp_addsubscription(), this will allow one to initialize a subscription without the need to create a snapshot of the publishing database. sp_addsubscription()you will be able to run and create replication with a backup with this and without the snapshot ment not many hours of waiting. I said to myself. So I did. I did the first test in dev and then in prod after it worked,

Here is how to do it
Following are the steps to create a SQL Server 2005 Transactional Replication without an initial snapshot.

1. Create a new publication using the New Publication Wizard. you are able to still do this by using the wizard to create the publication. Just apply the right information in the fields and settings as required according to your environment. However, when you reach the SnapShot Agent dialog, you will need to leave all the checkboxes unticked. The reason being is that you dont want a snapshot remember this? That’s the only thing to note while going through the New Publication Wizard dialogs.

2. Upon completion of the New Publication Wizard, open the property dialog to your publication. You now need to modify a property setting on the publication. You need to set the publication to allow initialization from backup files. Yeah, that’s all there is to it. Just set this setting to True. It really would have been even more dandier if this could be performed within the New Publication Wizard though.


3. you will now need to Perform a full backup of your database. Backup the database to a directory or drive accessible by the SQL Server, such as e:\ted\ted.bak in my case.

4. Restore the backup into the subscriber sql server instance. Don’t know
how to restore from a SQL backup? Google it up. Easy way to restore is from the managemt studio.

5. Run the following stored procedure in the SQL Server Management Studio Query Window on your publisher server.

sp_addsubscription
@publication ='YourPublicationName', --ie tedpub
@subscriber='SubscriberServerDBInstance', --servername in my case tedserver02@destination_db='SubscriberDatabase', -- teddb
@sync_type = 'initialize with backup',
@backupdevicetype = 'disk',
@backupdevicename = d:\ted.bak'
go


You will need to disable or change the job of the cleanup job to run as SA, I would just disable this job before you run the above command as you may encounter an error.

you will now see a new job in the sql agent just go to the job and right click go to properties and then set the job to run as sa.

round up
backup publication database
copy backup to subscriber server
restoring subscriber database
The transactional replication is now happily running between the two servers. Of course, the above process creates only a basic full database transactional replication. If your requirements are a litte bit more complicated, you might want to take a look at the options you can configure in the sp_addsubscription stored procedure.

checking sql2005 replication

select la.name,la.publisher_db,
case lh.runstatus
when 1 then 'Start'
when 2 then 'Succeed'
when 3 then 'In progress'
when 4 then 'Idle'
when 5 then 'Retry'
when 6 then 'Fail'
else 'Unknown'
end as runstatus
, lh.time, lh.comments
from distribution..MSlogreader_history lh
inner join distribution..MSlogreader_agents la on lh.agent_id = la.id
inner join (
select lh.agent_id, max(lh.time) as LastTime
from distribution..MSlogreader_history lh
inner join distribution..MSlogreader_agents la on lh.agent_id = la.id
group by lh.agent_id) r
on r.agent_id = lh.agent_id
and r.LastTime = lh.time
where lh.runstatus not in (3,4) -- 3:In Progress, 4: Idle

select * from distribution..MSlogreader_history

SIZE OF THE DATABASE FILES

SELECT name AS [File Name] , file_id, physical_name AS [Physical Name],
size/128 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0
AS [Available Space In MB]
FROM sys.database_files;

OUTPUT
File Name file_id Physical Name Total Size in MB Available Space In MB
-------------------------------------------------------------------------------------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------- ---------------------------------------
master 1 D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf 80 71.625000
mastlog 2 D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf 1 0.640625

(2 row(s) affected)

How to find the authentication methord used

This script will help you find the authentication method of logins in sql 2005.

SELECT 'Authentication Method'=(
CASE
WHEN nt_user_name IS not null THEN 'Windows Authentication'
ELSE 'SQL Authentication'
END),
login_name AS 'Login Name', ISNULL(nt_user_name,'-') AS 'Windows Login Name',
COUNT(session_id) AS 'Session Count'
FROM sys.dm_exec_sessions
GROUP BY login_name,nt_user_name

quick and dirty shrinklogs for all databases

This is a real quick and dirty way to shink all the database logs in all the databases please makesure you want to shrink the master, msdb,tempdb and so on before running this.

EXEC SP_MSFOREACHDB 'dbcc shrinkfile(2,1)'

Script to check whats running in tempdb and want statements its running

This will help deturming what is running in tempdb this will give you the sql text.

use tempdb
SELECT tst.[session_id],
s.[login_name] AS [Login Name],
DB_NAME (tdt.database_id) AS [Database],
tdt.[database_transaction_begin_time] AS [Begin Time],
tdt.[database_transaction_log_record_count] AS [Log Records],
tdt.[database_transaction_log_bytes_used] AS [Log Bytes Used],
tdt.[database_transaction_log_bytes_reserved] AS [Log Bytes Rsvd],
SUBSTRING(st.text, (r.statement_start_offset/2)+1,
((CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE r.statement_end_offset
END - r.statement_start_offset)/2) + 1) AS statement_text,
st.[text] AS [Last T-SQL Text],
qp.[query_plan] AS [Last Plan]
FROM sys.dm_tran_database_transactions tdt
JOIN sys.dm_tran_session_transactions tst
ON tst.[transaction_id] = tdt.[transaction_id]
JOIN sys.[dm_exec_sessions] s
ON s.[session_id] = tst.[session_id]
JOIN sys.dm_exec_connections c
ON c.[session_id] = tst.[session_id]
LEFT OUTER JOIN sys.dm_exec_requests r
ON r.[session_id] = tst.[session_id]
CROSS APPLY sys.dm_exec_sql_text (c.[most_recent_sql_handle]) AS st
OUTER APPLY sys.dm_exec_query_plan (r.[plan_handle]) AS qp
where DB_NAME (tdt.database_id) = 'tempdb'
ORDER BY [Log Bytes Used] DESC;

Check which tables have triggers and compare

This is useful if you have 2 or more databases in different sites.

Script to check which tables have triggers

SELECT s1.name as tablename, s2.name as triggername FROM sysobjects s1
JOIN sysobjects s2 ON
s1.id =s2.parent_obj
AND s2.xtype = 'TR'
WHERE s1.xtype = 'U'
ORDER BY s1.name


output

tablename triggername
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
AccruedDivis trAccrueddivisAuditQueueInsert
AccruedDivis trAccrueddivisAuditQueueUpdate
AccruedDivis trAccrueddivisAuditQueueDelete
AlertMessages trAlertMessagesInsert
Run this on both of the servers and in the databases required
copy the data to an excel worksheet
I have two databases one in London and one in Hong Kong in this sample



use a vlookup to compare like this and the copy the formula down

=VLOOKUP(B115,'sheet.name'!B:B,1,FALSE)

How to start service broker on a database

enable service broker
-- Alter the database to enable ServiceBroker functionality

DECLARE @dbname VARCHAR(30)

SELECT @dbname=db_name()

PRINT 'Setting up service broker on ' + @dbname



EXEC ('ALTER DATABASE ' + @dbname + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE')

EXEC ('ALTER DATABASE ' + @dbname + ' set NEW_BROKER')

EXEC ('ALTER DATABASE ' + @dbname + ' SET ENABLE_BROKER')

EXEC ('ALTER DATABASE ' + @dbname + ' SET MULTI_USER')

IF (SELECT is_broker_enabled FROM sys.databases WHERE name = @dbname) = 0

BEGIN

-- PRINT 'The Service broker wasn''t enabled !!'

RAISERROR ('The Service broker wasn''t enabled !!', 1, 1)

END

a few ways to check server uptime

Here are a few ways to check your sqlserver2005 uptime

SELECT @@servername as ServerName, datediff(mi, login_time, getdate()) as SQLServer_UpTime_Minutes
FROM master..sysprocesses WHERE spid = 1
go

SELECT @@servername as ServerName, getdate() - login_time as SQLServer_UpDateTime_1900_01_01
FROM master..sysprocesses
WHERE spid = 1
go

SELECT @@servername as ServerName, Year( SQLServer_UpTime) - 1900 - case when month( SQLServer_UpTime) - 1 - case when day( SQLServer_UpTime) - 1 < 0 then 1 else 0 end < 0 then 1 else 0 end as Years
, month( SQLServer_UpTime) - 1 - case when day( SQLServer_UpTime) - 1 < 0 then 1 else 0 end as Months
, day( SQLServer_UpTime) - 1 as Days
, substring(convert(varchar(25), SQLServer_UpTime,121),12,8) as Timepart
from (
SELECT getdate() - login_time as SQLServer_UpTime -- opgepast start vanaf 1900-01-01
FROM master..sysprocesses
WHERE spid = 1
) a

How to restart your sqlserver inside of SQL server

Restarting your SQL service within SQL

we need to bounce the service so that all the tempdb files get created, the agent knows the database config, etc.

The trick isn't stopping the server: "net stop mssqlserver" will do that. The real trick is starting it back up, once you've shut down the SQL server. Since our solution is all done via SQLCMD we needed a way, within SQL itself, to start back up.

Our secret is the ampersand; when the command line interpreter catches it, it views it as you hitting the "Enter" key. So, even though the SQL service is off, the job continues.


declare @sqlcmd varchar(8000)select @sqlcmd = 'net stop SQLSERVERAGENT & ping -n 15 127.0.0.1 & '+ 'net stop MSSQLSERVER & ping -n 15 127.0.0.1 & ' + 'net start MSSQLSERVER & ping -n 15 127.0.0.1 & ' + 'net start SQLSERVERAGENT'EXEC xp_cmdshell (@sqlcmd)

Creating a automated backup and restore with litespeed

Copy this code into a sqljob for and set time to whatever time you would like the backup taken this will work for daily backups and delete weekly.

SET QUOTED_IDENTIFIER ON

DECLARE @lastweekfiletime char(10)
, @dircmd varchar(500)
, @delcmd varchar(500)
, @filetime char(19)
, @sqlcmd varchar(500)


SELECT @lastweekfiletime = convert(char(4),dateadd(dd,-7,getdate()),112)+ '-' + substring(convert(char(8),dateadd(dd,-7,getdate()),112),5,2) + '-'
+ substring(convert(char(8),dateadd(dd,-7,getdate()),112),7,2);

PRINT @lastweekfiletime;

SELECT @dircmd = 'EXEC master..xp_cmdshell ''DIR d:\dir\database_' + @lastweekfiletime + '*.lsb.dmp''';

EXEC(@dircmd);
-- deletes older files
SELECT @delcmd = 'EXEC master..xp_cmdshell ''DEL d:\dir\database_' + @lastweekfiletime + '*.lsb.dmp /Q''';

print @delcmd;

EXEC(@delcmd);

EXEC(@dircmd);

SELECT @filetime = (
SELECT convert(char(4),getdate(),112) + '-'
+ substring(convert(char(8),getdate(),112),5,2) + '-'
+ substring(convert(char(8),getdate(),112),7,2) + '_' +
CASE len(datename(hh, getdate()))
WHEN 1 THEN
'0' + datename(hh, getdate())
ELSE
datename(hh, getdate())
END + '-' +
CASE len(datename(mi, getdate()))
WHEN 1 THEN
CASE datename(mi, getdate())
WHEN 0 THEN
datename(mi, getdate()) + '0'
ELSE
'0' + datename(mi, getdate())
END
ELSE
datename(mi, getdate())
END + '-' +
CASE len(datename(ss, getdate()))
WHEN 1 THEN
CASE datename(ss, getdate())
WHEN 0 THEN
datename(ss, getdate()) + '0'
ELSE
'0' + datename(ss, getdate())
END
ELSE
datename(ss, getdate())
END);


SET @sqlcmd = 'exec master..xp_backup_database @database = ''database'', @filename = ''d:\dir\database_' + @filetime + '_1of1.lsb.dmp''';

EXEC(@sqlcmd);

Restore if you would like to restore daily to another database on the same server you can us this job to pick up the latest full database.
Or create a copy job to copy the backup accross to another server and use this to restore daily.
SET NOCOUNT ON
declare @RestoreDateTime varchar(20)
declare @stmt varchar(500)
declare @restore_stmt varchar(1000)
declare @latest_full varchar(255)

create table #SrcFilesRAW
(
SrcFName varchar(200)
)

select @stmt = 'dir D:\dir\*database_*.dmp /b'

insert into #SrcFilesRAW
exec master..xp_cmdshell @stmt

select * from #SrcFilesRAW
set rowcount 1
select @latest_full = SrcFName from #SrcFilesRAW
where SrcFName like 'database_20%' order by SrcFName desc
set rowcount 0
select * from #SrcFilesRAW
drop table #SrcFilesRAW



select @restore_stmt = 'exec master..xp_restore_database @database = ''database1''
,@FILENAME = ''D:\dir\'+ @latest_full + '''
,@WITH = ''REPLACE'''

exec (@restore_stmt)

Useful SQL Server Scripts


Here are some usefull sqlserver scripts

Useful for telling what version of SQL Server is being run, including which service pack has been applied.


SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'),
SERVERPROPERTY ('edition')


How Much Space Per Table
For each table in the current database, how much space is being used?


EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"


Count All Table Records
Count the number of rows in each table in a database. How to use sysindexes and sysobjects.


SELECT so.[name] as [table name], CASE WHEN si.indid between 1 and 254
THEN si.[name] ELSE NULL END AS [Index Name], si.rowcnt
FROM sysindexes si INNER JOIN sysobjects so ON si.id = so.id
WHERE si.indid < 2 AND so.type = 'U' AND so.[name] != 'dtproperties'
ORDER BY so.[name]


List All SQL Databases
A simple example of cursors and using the master database catalog to find all databases on the server.

Declare @database varchar(255)
Declare DatabaseCursor Cursor
Local
Static
For
select name from master.dbo.sysdatabases
open DatabaseCursor
fetch next from DatabaseCursor into @database

while @@fetch_status = 0
begin
print 'database:' + @database
fetch next from DatabaseCursor into @database
end

close DatabaseCursor

deallocate DatabaseCursor

Reindex All Indexes on All Tables in a Database
Run this in the database you want to reindex.

DECLARE @tn varchar(255)

DECLARE tc CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN tc

FETCH NEXT FROM tc INTO @tn
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@tn,' ',90)
FETCH NEXT FROM tc INTO @tn
END

CLOSE tc

DEALLOCATE tc

Trim SQL Database Space
use my_database


if object_id( 'sp_force_shrink_log' ) is not null drop proc sp_force_shrink_log
go
create proc sp_force_shrink_log
@target_percent tinyint = 0,
@target_size_MB int = 10,
@max_iterations int = 1000,
@backup_log_opt nvarchar(1000) = 'with truncate_only'
as
set nocount on

declare @db sysname,
@last_row int,
@log_size decimal(15,2),
@unused1 decimal(15,2),
@unused decimal(15,2),
@shrinkable decimal(15,2),
@iteration int,
@file_max int,
@file int,
@fileid varchar(5)

select @db = db_name(),
@iteration = 0

create table #loginfo (
id int identity,
FileId int,
FileSize numeric(22,0),
StartOffset numeric(22,0),
FSeqNo int,
Status int,
Parity smallint,
CreateTime numeric(22,0)
)

create unique clustered index loginfo_FSeqNo on #loginfo ( FSeqNo, StartOffset )

create table #logfiles ( id int identity(1,1), fileid varchar(5) not null )
insert #logfiles ( fileid ) select convert( varchar, fileid ) from sysfiles where status & 0x40 = 0x40
select @file_max = @@rowcount

if object_id( 'table_to_force_shrink_log' ) is null
exec( 'create table table_to_force_shrink_log ( x nchar(3000) not null )' )

insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateTime ) exec ( 'dbcc loginfo' )
select @last_row = @@rowcount

select @log_size = sum( FileSize ) / 1048576.00,
@unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,
@shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
from #loginfo

select @unused1 = @unused -- save for later

select 'iteration' = @iteration,
'log size, MB' = @log_size,
'unused log, MB' = @unused,
'shrinkable log, MB' = @shrinkable,
'shrinkable %' = convert( decimal(6,2), @shrinkable * 100 / @log_size )

while @shrinkable * 100 / @log_size > @target_percent
and @shrinkable > @target_size_MB
and @iteration < @max_iterations begin
select @iteration = @iteration + 1 -- this is just a precaution

exec( 'insert table_to_force_shrink_log select name from sysobjects
delete table_to_force_shrink_log')

select @file = 0
while @file < @file_max begin
select @file = @file + 1
select @fileid = fileid from #logfiles where id = @file
exec( 'dbcc shrinkfile( ' + @fileid + ' )' )
end

exec( 'backup log [' + @db + '] ' + @backup_log_opt )

truncate table #loginfo
insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateTime ) exec ( 'dbcc loginfo' )
select @last_row = @@rowcount

select @log_size = sum( FileSize ) / 1048576.00,
@unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,
@shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
from #loginfo

select 'iteration' = @iteration,
'log size, MB' = @log_size,
'unused log, MB' = @unused,
'shrinkable log, MB' = @shrinkable,
'shrinkable %' = convert( decimal(6,2), @shrinkable * 100 / @log_size )
end

if @unused1 < @unused
select 'After ' + convert( varchar, @iteration ) +
' iterations the unused portion of the log has grown from ' +
convert( varchar, @unused1 ) + ' MB to ' +
convert( varchar, @unused ) + ' MB.'
union all
select 'Since the remaining unused portion is larger than 10 MB,' where @unused > 10
union all
select 'you may try running this procedure again with a higher number of iterations.' where @unused > 10
union all
select 'Sometimes the log would not shrink to a size smaller than several Megabytes.' where @unused <= 10

else
select 'It took ' + convert( varchar, @iteration ) +
' iterations to shrink the unused portion of the log from ' +
convert( varchar, @unused1 ) + ' MB to ' +
convert( varchar, @unused ) + ' MB'

exec( 'drop table table_to_force_shrink_log' )
go
sp_force_shrink_log
go
drop proc sp_force_shrink_log
go
dbcc loginfo
go

interview questions

(Q) What is a Database or Database Management System (DBMS)?
Twist: What is the difference between a file and a database? Can files qualify as a database?

Note: Probably these questions are too basic for experienced SQL SERVER guys. But from a fresher�s point of view, it can be a difference between getting a job and being jobless.

1.Database provides a systematic and organized way of storing, managing and retrieving from a collection of logically related information.
2.Secondly, the information has to be persistent, that means even after the application is closed the information should be persisted.
3.Finally, it should provide an independent way of accessing data and should not be dependent on the application to access the information.
Ok, let me spend a few more sentences on explaining the third aspect. Below is a simple figure of a text file that has personal detail information. The first column of the information is Name, second Address and finally Phone Number. This is a simple text file, which was designed by a programmer for a specific application.


Figure 1.1: Non-Uniform Text File
It works fine in the boundary of the application. Now, some years down the line a third party application has to be integrated with this file. In order for the third party application to be integrated properly, it has the following options:

•Use the interface of the original application.
•Understand the complete details of how the text file is organized, example the first column is Name, then Address and finally Phone Number. After analyzing, write a code which can read the file, parse it etc. Hmm, lot of work, right.
That�s what the main difference is between a simple file and a database; database has an independent way (SQL) of accessing information while simple files do not (That answers my twisted question defined above). File meets the storing, managing and retrieving part of a database, but not the independent way of accessing data.

Note: Many experienced programmers think that the main difference is that file cannot provide multi-user capabilities which a DBMS provides. But if you look at some old COBOL and C programs where files were the only means of storing data, you can see functionalities like locking, multi-user etc. provided very efficiently. So it�s a matter of debate. If some interviewers think of this as a main difference between files and database, accept it� going in to debate means probably losing a job.

(Just a note for fresher�s: Multi-user capabilities mean that at one moment of time more than one user should be able to add, update, view and delete data. All DBMS' provides this as in-built functionalities, but if you are storing information in files, it�s up to the application to write logic to achieve these functionalities).

(Q) What is the Difference between DBMS and RDBMS?
As mentioned before, DBMS provides a systematic and organized way of storing, managing and retrieving from a collection of logically related information. RDBMS also provides what DBMS provides, but above that, it provides relationship integrity. So in short, we can say:

RDBMS = DBMS + REFERENTIAL INTEGRITY

For example, in the above Figure 1.1, every person should have an Address. This is a referential integrity between Name and Address. If we break this referential integrity in DBMS and files, it will not complain, but RDBMS will not allow you to save this data if you have defined the relation integrity between person and addresses. These relations are defined by using �Foreign Keys� in any RDBMS.

Many DBMS companies claimed that their DBMS product was RDBMS compliant, but according to industry rules and regulations, if the DBMS fulfills the twelve CODD rules, it�s truly a RDBMS. Almost all DBMS (SQL SERVER, ORACLE etc.) fulfill all the twelve CODD rules and are considered truly as RDBMS.

Note: One of the biggest debates is whether Microsoft Access is an RDBMS? We will be answering this question in later section.

(DB)What are CODD Rules?
Twist: Does SQL SERVER support all the twelve CODD rules?

Note: This question can only be asked on two conditions when the interviewer is expecting you to be at a DBA job or you are complete fresher, yes and not to mention the last one he treats CODD rules as a religion. We will try to answer this question from the perspective of SQL SERVER.

In 1969, Dr. E. F. Codd laid down 12 rules, which a DBMS should adhere to in order to get the logo of a true RDBMS.

Rule 1: Information Rule
"All information in a relational database is represented explicitly at the logical level and in exactly one way - by values in tables."

In SQL SERVER, all data exists in tables and are accessed only by querying the tables.

Rule 2: Guaranteed Access Rule
"Each and every datum (atomic value) in a relational database is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name."

In flat files, we have to parse and know the exact location of field values. But if a DBMS is truly an RDBMS, you can access the value by specifying the table name, field name, for instance Customers.Fields [�Customer Name�].

SQL SERVER also satisfies this rule. In ADO.NET we can access field information using table name and field names.

Rule 3: Systematic Treatment of Null Values
"Null values (distinct from the empty character string or a string of blank characters and distinct from zero or any other number) are supported in fully relational DBMS for representing missing information and inapplicable information in a systematic way, independent of data type.�

In SQL SERVER, if there is no data existing, NULL values are assigned to it. Note NULL values in SQL SERVER do not represent spaces, blanks or a zero value; it is a distinct representation of missing information and thus satisfies rule 3 of CODD.

Rule 4: Dynamic On-line Catalog Based on the Relational Model
"The database description is represented at the logical level in the same way as ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data."

The Data Dictionary is held within the RDBMS. Thus, there is no need for off-line volumes to tell you the structure of the database.

Rule 5: Comprehensive Data Sub-language Rule
"A relational system may support several languages and various modes of terminal use (for example, the fill-in-the-blanks mode). However, there must be at least one language whose statements are expressible, per some well-defined syntax, as character strings and that is comprehensive in supporting all the following items:

•Data Definition
•View Definition
•Data Manipulation (Interactive and by program)
•Integrity Constraints
•Authorization
•Transaction boundaries ( Begin, commit and rollback)"
SQL SERVER uses SQL to query and manipulate data, which has a well-defined syntax and is being accepted as an international standard for RDBMS.

Note: According to this rule, CODD has only mentioned that some language should be present to support it, but not necessary that it should be SQL. Before the 80�s, different�s database vendors were providing their own flavor of syntax until in 1980, ANSI-SQL came in to standardize this variation between vendors. As ANSI-SQL is quite limited, every vendor including Microsoft introduced their additional SQL syntax in addition to the support of ANSI-SQL. You can see SQL syntax varying from vendor to vendor.

Rule 6: View-updating Rule
"All views that are theoretically updatable are also updatable by the system."

In SQL SERVER, not only views can be updated by the user, but also by SQL SERVER itself.

Rule 7: High-level Insert, Update and Delete
"The capability of handling a base relation or a derived relation as a single operand applies not only to the retrieval of data, but also to the insertion, update and deletion of data."

SQL SERVER allows you to update views that in turn affect the base tables.

Rule 8: Physical Data Independence
"Application programs and terminal activities remain logically unimpaired whenever any changes are made in either storage representations or access methods."

Any application program (C#, VB.NET, VB6, VC++ etc) does not need to be aware of where the SQL SERVER is physically stored or what type of protocol it is using, the database connection string encapsulates everything.

Rule 9: Logical Data Independence
"Application programs and terminal activities remain logically unimpaired when information-preserving changes of any kind that theoretically permit un-impairment are made to the base tables."

Application programs written in C# or VB.NET do not need to know about any structure changes in SQL SERVER database. Example: adding of new field etc.

Rule 10: Integrity Independence
"Integrity constraints specific to a particular relational database must be definable in the relational data sub-language and storable in the catalog, not in the application programs."

In SQL SERVER, you can specify data types (integer, nvarchar, Boolean etc.) which put in data type checks in SQL SERVER rather than through application programs.

Rule 11: Distribution Independence
"A relational DBMS has distribution independence."

SQL SERVER can spread across more than one physical computer and across several networks; but from application programs, it has not a big difference but just specifying the SQL SERVER name and the computer on which it is located.

Rule 12: Non-subversion Rule
"If a relational system has a low-level (single-record-at-a-time) language, that low level cannot be used to subvert or bypass the integrity Rules and constraints expressed in the higher level relational language (multiple-records-at-a-time)."

In SQL SERVER whatever integrity rules are applied on every record are also applicable when you process a group of records using application program in any other language (example: C#, VB.NET, J# etc.).

Readers can see from the above explanation that SQL SERVER satisfies all the CODD rules, some database gurus consider SQL SERVER as not truly being an RDBMS, but that�s a matter of debate.

(Q) Is Access Database a RDBMS?
Access fulfills all rules of CODD, so from this point of view, yes it�s truly an RDBMS. However, many people can contradict it as a large community of Microsoft professionals think that Access is not an RDBMS.

(Q) What is the Main Difference between ACCESS and SQL SERVER?
As mentioned before, Access fulfills all the CODD rules and behaves as a true RDBMS. But there�s a huge difference from an architecture perspective, due to which many developers prefer to use SQL SERVER as the major database rather than Access. Following is the list of architecture differences between them:

•Access uses file server design and SQL SERVER uses the Client / Server model. This forms the major difference between SQL SERVER and ACCESS.
Note: Just to clarify what is client server and file server I will make a quick description of widely accepted architectures. There are three types of architectures:
◦Main frame architecture (This is not related to the above explanation but just mentioned as it can be useful during an interview and also for comparing with other architectures)
◦File sharing architecture (Followed by ACCESS)
◦Client Server architecture (Followed by SQL SERVER).
In Main Frame architecture, all the processing happens on central host server. User interacts through a dumb terminal that only sends keystrokes and information to the host. All the main processing happens on the central host server. So the advantage in such type of architecture is that you need least configuration clients. But the disadvantage is that you need a robust central host server like Main Frames.

In File sharing architecture, which is followed by Access database, all the data is sent to the client terminal and then processed. For instance, if you want to see customers who stay in India, in File Sharing architecture all customer records will be sent to the client PC regardless whether the customer belongs to India or not. On the client PC customer records from India are sorted/filtered out and displayed, in short all processing logic happens on the client PC. Therefore, in this architecture, the client PC should have heavy configuration and it increases network traffic as a lot of data is sent to the client PC. However, the advantage of this architecture is that your server can be of a low configuration.


Figure 1.2: File Server Architecture of Access
In client server architecture, the above limitation of the file server architecture is removed. In client server architecture, you have two entities, client and the database server. File server is now replaced by database server. Database server takes up the load of processing any database related activity and the client does any validation aspect of database. As the work is distributed between the entities it increases scalability and reliability. Second, the network traffic also comes down as compared to file server. For example if you are requesting customers from India, database server will sort/ filter and send only Indian customer details to the client, thus bringing down the network traffic tremendously. SQL SERVER follows the client-server architecture.


Figure 1.3: Client Server Architecture of SQL SERVER
•The second issue comes in terms of reliability. In Access, the client directly interacts with the Access file, in case there is some problem in the middle of a transaction, there are chances that an Access file can get corrupt. But in SQL SERVER, the engine sits in between the client and the database, so in case of any problems in the middle of a transaction, it can revert back to its original state.
Note: SQL SERVER maintains a transaction log by which you can revert back to your original state in case of any crash.
•When your application has to cater to a huge load demand, highly transactional environment and high concurrency, then its better to go for SQL SERVER or MSDE.
•But when it comes to cost and support, Access stands better than SQL SERVER. In case of SQL SERVER, you have to pay for per client license, but Access runtime is free.
Summarizing: SQL SERVER gains points in terms of network traffic, reliability and scalability whereas Access gains points in terms of cost factor.

(Q) What is the Difference between MSDE and SQL SERVER 2000?
MSDE is a royalty free, redistributable and cut short version of the giant SQL SERVER database. It is primarily provided as a low cost option for developers who need a database server, which can easily be shipped and installed. It can serve as a good alternative for Microsoft Access database as it overcomes quite a few problems which Access has.

Below is a complete list, which can give you a good idea of the differences:

•Size of database: Microsoft Access and MSDE have a limitation of 2GB while SQL SERVER has 1,048,516 TB1.
•Performance degrades in MSDE 2000 when maximum number of concurrent operations goes above 8 or is equal to 8. It does not mean that you cannot have more than eight concurrent operations but the performance degrades. Eight-connection performance degradation is implemented by using SQL SERVER 2000 workload governor (we will be looking into more detail of how it works). As compared to SQL SERVER 2000, you can have 32,767 concurrent connections.
•MSDE does not provide OLAP and Data warehousing capabilities.
•MSDE does not have support facility for SQL mail.
•MSDE 2000 does not have GUI administrative tool such as enterprise manager, Query analyzer or Profiler. But there are roundabout ways by which you can manage MSDE 2000:
◦Old command line utility OSQL.EXE
◦VS.NET IDE Server Explorer: Inside VS.NET IDE, you have a functionality which can give you a nice GUI administrative tool to manage IDE.
◦SQL SERVER WEB Data administrator installs a web based GUI which you can use to manage your database.
For any details refer here.
•SQL-DMO objects can be used to build your custom UI
•There are many third party tools, which provide administrative capability GUI, which is out of scope of the book as it is only meant for interview questions.
•MSDE does not support Full text search.
Summarizing: There are two major differences: The first is the size limitation (2 GB) of the database and second is the concurrent connections (eight concurrent connections) which are limited by using the workload governor. During an interview, this answer will suffice if the interviewer is really testing your knowledge.

(Q) What is SQL SERVER Express 2005 Edition?
Twist: What is the difference between SQL SERVER Express 2005 and MSDE 2000?

Note: Normally comparison is when the product is migrating from one version to other version. When SQL SERVER 7.0 was migrating to SQL 2000, asking differences was one of the favorite questions.

SQL SERVER Express edition is a scaled down version of SQL SERVER 2005 and the next evolution of MSDE.

Listed below are some major differences between them:

•MSDE maximum database size is 2GB while SQL SERVER Express has around 4GB.
•In terms of programming language support MSDE has only TSQL, but SQLSERVER Express has TSQL and .NET. In SQL SERVER Express 2005, you can write your stored procedures using .NET.
•SQL SERVER Express does not have connection limitation, which MSDE had and was controlled through the workload governor.
•There was no XCOPY support for MSDE, SQL SERVER Express has it.
•DTS is not present in SQL SERVER express while MSDE has it.
•SQL SERVER Express has reporting services while MSDE does not.
•SQL SERVER Express has native XML support and MSDE does not.
Note: Native XML support means now in SQL SERVER 2005:

•You can create a field with data type XML.
•You can provide SCHEMA to the SQL SERVER fields with XML data type.
•You can use new XML manipulation techniques like XQUERY also called as XML QUERY.
There is a complete chapter on SQL SERVER XML Support, so till then this will suffice.

Summarizing: The major difference is the database size (2 GB and 4 GB), support of .NET support in stored procedures and native support for XML. This much can convince the interviewer that you are clear about the differences.

(DB) What is SQL Server 2000 Workload Governor?
Workload governor limits the performance of SQL SERVER Desktop engine (MSDE) if the SQL engine receives more load than what is meant for MSDE. MSDE was always meant for trial purpose and non-critical projects. Microsoft always wanted companies to buy their full blow version of SQL SERVER, so in order that they can put limitation on MSDE performance and number of connections, they introduced Workload governor.

Workload governor sits between the client and the database engine and counts the number of connections per database instance. If Workload governor finds that the number of connections exceeds eight connections, it starts stalling the connections and slowing down the database engine.

Note: It does not limit the number of connections but makes the connection request go slow. By default 32,767 connections are allowed both for SQL SERVER and MSDE. But it just makes the database engine go slow above eight connections.

What is the Difference between SQL SERVER 2000 and 2005?
Twist: What is the difference between Yukon and SQL SERVER 2000?

Note: This question will be one of the favorites during SQL SERVER interviews. I have marked the points which should be mentioned by developers as PG and DBA for Database Administrator.

Following are some major differences between the two versions:

•(PG) The most significant change is the .NET integration with SQL SERVER 2005. Stored procedures, user-defined functions, triggers, aggregates, and user-defined types can now be written using your own favorite .NET language (VB.NET, C#, J# etc.). This support was not there in SQL SERVER 2000 where the only language was T-SQL. In SQL 2005, you have support for two languages T-SQL and .NET.
•(PG) SQL SERVER 2005 has reporting services for reports which is a newly added feature and does not exist for SQL SERVER 2000. It was a separate installation for SQL Server 2000.
•(PG) SQL SERVER 2005 has introduced two new data types varbinary (max) and XML. If you remember in SQL SERVER 2000, we had image and text data types. Problem with image and text data types is that they assign the same amount of storage irrespective of what the actual data size is. This problem is solved using varbinary (max) which acts depending on amount of data. One more new data type is included XML which enables you to store XML documents and does schema verification. In SQL SERVER 2000, developers used varchar or text data type and all validation had to be done programmatically.
•(PG) SQL SERVER 2005 can now process direct incoming HTTP request without IIS Web server. In addition, stored procedure invocation is enabled using the SOAP protocol.
•(PG) Asynchronous mechanism is introduced using server events. In Server event model the server posts an event to the SQL Broker service, later the client can come and retrieve the status by querying the broker.
•For huge databases, SQLSERVER has provided a cool feature called �Data partitioning�. In data partitioning, you break a single database object such as a table or an index into multiple pieces. But for the client application accessing the single database object, �partitioning� is transparent.
•In SQL SERVER 2000, if you rebuilt clustered indexes even the non-clustered indexes where rebuilt. But in SQL SERVER 2005 building the clustered indexes does not build the non-clustered indexes.
•Bulk data uploading in SQL SERVER 2000 was done using BCP (Bulk copy program�s) format files. Now in SQL SERVER 2005 bulk, data uploading uses XML file format.
•In SQL SERVER 2000 there were maximum 16 instances, but in 2005 you can have up to 50 instances.
•SERVER 2005 has support of �Multiple Active Result Sets� also called as �MARS�. In previous versions of SQL SERVER 2000 in one connection, you could only have one result set. Now in one SQL connection, you can query and have multiple results set.
•In previous versions of SQL SERVER 2000, system catalog was stored in the master database. In SQL SERVER 2005, it�s stored in a resource database which is stored as sys object. You cannot access the sys object directly as in the older version we were accessing the master database.
•This is one of the hardware benefits which SQL SERVER 2005 has over SQSERVER 2000 � support of hyper threading. WINDOWS 2003 supports hyper threading; SQL SERVER 2005 can take advantage of the feature unlike SQL SERVER 2000 which did not support hyper threading.
Note: Hyper threading is a technology developed by INTEL which creates two logical processors on a single physical hardware processor.
•SMO will be used for SQL Server Management.
•AMO (Analysis Management Objects) to manage Analysis Services servers, data sources, cubes, dimensions, measures, and data mining models. You can mapm AMO in old SQL SERVER with DSO (Decision Support Objects).
•Replication is now managed by RMO (Replication Management Objects).
Note: SMO, AMO and RMO are all using .NET Framework.
•SQL SERVER 2005 uses current user execution context to check rights rather than ownership link chain, which was done in SQL SERVER 2000.
Note: There is a question on this later see for execution context questions.
•In previous versions of SQL SERVER the schema and the user name was same, but in current, the schema is separated from the user. Now the user owns schema.
Note: There are questions on this, refer �Schema� later.
Note: Ok below are some GUI changes.
•Query analyzer is now replaced by query editor.
•Business Intelligence development studio will be used to create Business intelligence solutions.
•OSQL and ISQL command line utility is replaced by SQLCMD utility.
•SQL SERVER Enterprise manager is now replaced by SQL SERVER Management studio.
•SERVER Manager which was running in system tray is now replaced by SQL Computer manager.
•Database mirror concept is supported in SQL SERVER 2005, which was not present in SQL SERVER 2000.
•In SQL SERVER 2005 Indexes can be rebuilt online when the database is in actual production. If you look back in SQL SERVER 2000, you cannot do insert, update, and delete operations when you are building indexes.
•(PG) Other than Serializable, Repeatable Read, Read Committed, and Read Uncommitted isolation levels, there is one more new isolation level �Snapshot Isolation level�.
Note: We will see �Snapshot Isolation level� in detail in the coming questions.
Summarizing: The major significant difference between SQL SERVER 2000 and SQL SERVER 2005 is in terms of support of .NET Integration, Snap shot isolation level, Native XML support, handling HTTP request, Web service support and Data partitioning. You do not have to really say all the above points during an interview. A sweet summary and you will rock.

(Q) What are E-R diagrams?
E-R diagram also termed as Entity-Relationship diagram shows the relationship between various tables in the database. Example: Tables Customer and Customer Addresses have a one to many relationship (i.e. one customer can have multiple addresses) this can be shown using the ER diagram. ER diagrams are drawn during the initial stages of a project to forecast how the database structure will shape up. Below is a screen shot of a sample ER diagram of �Asset Management� which ships free with Access.


Figure 1.4: Asset management ER diagram.
(Q) How many Types of Relationship Exist in Database Designing?
There are three major relationship models:

•One-to-one


Figure 1.5: One-to-One relationship ER diagram
•One-to-many
In this many records in one table correspond to the one record in another table.
Example: Every one customer can have multiple sales. So there exist one-to-many relationships between customer and sales table.
One Asset can have multiple Maintenance. So Asset entity has one-to-many relationship between them as the ER model shows below.


Figure 1.6: One-to-Many Relationship ER diagram
•Many-to-many
In this, one record in one table corresponds to many rows in another table and also vice-versa.
For instance: In a company, one employee can have many skills like Java , C# etc. and also one skill can belong to many employees.
Given below is a sample of many-to-many relationship. One employee can have knowledge of multiple Technology. So in order to implement this, we have one more table Employee Technology which is linked to the primary key of Employee and Technology table.


Figure 1.7: Many-to-Many Relationship ER diagram

What is Normalization

(Q) What is Normalization? What are the Different Types of Normalization?
Note: A regular .NET programmer working on projects often stumbles on this question, which is but obvious. The bad part is sometimes the interviewer can take this as a very basic question to be answered and it can be a turning point for the interview. So let's cram it.

It is set of rules that have been established to aid in the design of tables that are meant to be connected through relationships. This set of rules is known as Normalization.

Benefits of Normalizing your database include:

•Avoiding repetitive entries
•Reducing required storage space
•Preventing the need to restructure existing tables to accommodate new data
•Increased speed and flexibility of queries, sorts, and summaries
Note: During an interview, people expect to answer a maximum of three normal forms and that's what is expected practically. Actually you can normalize database to fifth normal form. But believe this book, answering three normal forms will put you in a decent shape during an interview.

The three normal forms as follows:

First Normal Form
For a table to be in first normal form, data must be broken up into the smallest units possible. In addition to breaking data up into the smallest meaningful values, tables in first normal form should not contain repetitions groups of fields.


Figure 1.8: Repeating groups example
In the above example, city1 and city2 are repeating. In order for these tables to be in First normal form, you have to modify the table structure as follows. Also note that the Customer Name is now broken down to first name and last name (First normal form data should be broken down to the smallest unit).


Figure 1.9: Customer table normalized to first normal form
Second Normal Form
The second normal form states that each field in a multiple field primary key table must be directly related to the entire primary key. In other words, each non-key field should be a fact about all the fields in the primary key.

In the above table of customer, city is not linked to any primary field.


Figure 1.10: Normalized customer table.

Figure 1.11: City is now shifted to a different master table.
That takes our database to a second normal form.

Third Normal Form
A non-key field should not depend on another Non-key field. The field Total is dependent on Unit price and qty.


Figure 1.12: Fill third normal form
So now the Total field is removed and is the multiplication of Unit price * Qty.

(Q) What is Denormalization?
Denormalization is the process of putting one fact in numerous places (it is vice-versa of normalization). Only one valid reason exists for denormalizing a relational design - to enhance performance. The sacrifice to performance is that you increase redundancy in a database.

(DB) Can you Explain Fourth Normal Form?
Note: Whenever the interviewer is trying to go above the third normal form, there can be two reasons, ego or to fail you. Three normal forms are really enough, practically anything more than that is an overdose.

In fourth normal form, it should not contain two or more independent multi-valued facts about an entity and it should satisfy �Third Normal form�.

So let us try to see what multi-valued facts are. If there are two or more many-to-many relationship in one entity and they tend to come to one place, it is termed as �multi-valued facts�.


Figure 1.13: Multi-valued facts
In the above table, you can see that there are two many-to-many relationships between Supplier / Product and �Supplier / Location (or in short multi-valued facts). In order for the above example to satisfy the fourth normal form, both the many-to-many relationships should go in different tables.


Figure 1.14: Normalized to Fourth Normal form.
(DB) Can you Explain Fifth Normal Form?
Note: UUUHHH if you get this question after joining the company, do ask him if he himself really uses it?

Fifth normal form deals with reconstructing information from smaller pieces of information. These smaller pieces of information can be maintained with less redundancy.

Example: Dealers sell Product which can be manufactured by various Companies. Dealers in order to sell the Product should be registered with the Company. So these three entities have a mutual relationship within them.


Figure 1.15: Not in Fifth Normal Form.
The above table shows some sample data. If you observe closely, a single record is created using lot of small information. For instance: JM Associate can sell sweets under the following two conditions:

•JM Associate should be an authorized dealer of Cadbury
•Sweets should be manufactured by Cadbury company
These two smaller bits of information form one record of the above given table. So in order for the above information to be �Fifth Normal Form� all the smaller information should be in three different places. Below is the complete fifth normal form of the database.


Figure 1.16: Complete Fifth Normal Form
(DB) What is the Difference between Fourth and Fifth normal form?
Note: There is a huge similarity between Fourth and Fifth normal form, i.e. they address the problem of �Multi-Valued facts�.

�Fifth normal form� multi-valued facts are interlinked and �Fourth normal form� values are independent. For instance in the above two questions Supplier/Product and Supplier/Location are not linked. While in fifth form, the Dealer/Product/Companies are completely linked.

(DB) Have you Heard about Sixth Normal Form?
Note: Arrrrggghhh yes there exists a sixth normal form also. But note guys you can skip this statement. Just in case you want to impress the interviewer...

If you want a relational system in conjunction with time, you use sixth normal form. At this moment SQL Server does not support it directly.

(Q) What is Extent and Page?
Twist: What is the relationship between Extent and Page?

Extent is a basic unit of storage to provide space for tables. Every extent has a number of data pages. As new records are inserted new data, pages are allocated. There are eight data pages in an extent. So as soon as the eight pages are consumed, it allocates a new extent with data pages.

While extent is basic unit storage from a database point of view, page is a unit of allocation within extent.

(DB) What are the Different Sections in Page?
Page has three important sections:

•Page header
•Actual data i.e. Data row
•Row pointers or Row offset
Page header has information like timestamp, next page number, previous page number etc.

Data rows are where your actual row data is stored. For every data row, there is a row offset which points to that data row.


Figure 1.17: General view of a Extent
(Q) What are Page Splits?
Pages are contained in extent. Every extent will have around eight data pages. But all the eight data pages are not created at once; they are created depending on data demand. So when a page becomes full it creates a new page, this process is called as �Page Split�.

(Q) In which Files does SQL Server Actually Store Data?
Any SQL Server database is associated with two kinds of files: *.mdf and *.ldf. *.mdf files are actual physical database files where your data is stored finally. *.ldf (LOG) files are actually data, which is recorded from the last time data was committed in the database.


Figure 1.18: MDF and LDF files.
(Q) What is Collation in SQL Server?
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types, and character width.


Figure 1.19: Collation according to language
Note: Different languages will have different sort orders.

Case Sensitivity
If A and a, B and b, etc. are treated in the same way, then it is case-insensitive. A computer treats A and a differently because it uses ASCII code to differentiate the input. The ASCII value of A is 65, while a is 97. The ASCII value of B is 66 and b is 98.

Accent Sensitivity
If a and A, o and O are treated in the same way, then it is accent-insensitive. A computer treats a and A differently because it uses ASCII code for differentiating the input. The ASCII value of a is 97 and A 225. The ASCII value of o is 111 and O is 243.

Kana Sensitivity
When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.

Width Sensitivity
When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.

Backups vdi and stack dumping

When using litespeed version 4.8.3 with sql2005 we found that we recieved the below errors.
> Title:
> "State: nn\n.SqlDumpExceptionHandler: Process nn generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process"
>
>
> Problem Description:
> Backing up transaction logs for multiple databases can result in the generation of a stack dump error with the below logs:
>
> - State: 0\015.Stored function 'xp_sqllitespeed_version' in the library 'xpSLS.dll' generated an access violation. SQL Server is terminating process 62.
> - State: 0\015.SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> - Job 'DBA - Backup TranLogs - LiteSpeed - ReportServerTempDB'
> (0xBC7DB5449E405941A11F84897255D32E) - Status: Failed - Invoked on:
> 2009-03-16 20:00:04 - Message: The job failed. The Job was invoked by
> Schedule 691
> - State: 0\015.Stored function 'xp_sqllitespeed_version' in the library 'xpSLS.dll' generated an access violation. SQL Server is terminating process 60.
> - Job 'DBA - Backup TranLogs - LiteSpeed - UIPState'
> (0x5EBFFF7592D92F479BF9D5613CAEF565) - Status: Failed - Invoked on:
> 2009-03-16 20:00:00 - Message: The job failed. The Job was invoked by
> Schedule 694 (Every 30
> - State: 0\015.SqlDumpExceptionHandler: Process 60 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> - Job 'DBA - Backup TranLogs - LiteSpeed - ClientValuationService'
> (0x62F11A3F9A73AC4081B1AD8F5C42EAC8) - Status: Failed - Invoked on:
> 2009-03-16 20:00:01 - Message: The job failed. The Job was invoked by
> Schedule
>
>
> Cause:
> Insufficient memory as a result of defect in product.
> See example stats below:
>
> Memory
> MemoryLoad = 68%
> Total Physical = 3839 MB
> Available Physical = 1198 MB
> Total Page File = 7778 MB
> Available Page File = 4455 MB
> Total Virtual = 3071 MB
> Available Virtual = 213 MB


in doing some research and lo0king at the stackdump. It looks like we are in the stack when a deallocation goes sideways on heap corruption.


ntdll!RtlpExecuteHandlerForException+0xd
ntdll!RtlDispatchException+0x1b4
ntdll!KiUserExceptionDispatcher+0x2d
ntdll!DbgBreakPoint
ntdll!RtlpDphReportCorruptedBlock+0x239
ntdll!RtlpDphNormalHeapFree+0x45
ntdll!RtlpDebugPageHeapFree+0x203
ntdll!RtlDebugFreeHeap+0x3b
ntdll!RtlFreeHeapSlowly+0x4e
ntdll!RtlFreeHeap+0x15e
xpSLS+0xc0350
0x7eea8f70
xpSLS+0x149b18
0xffffffff


Based on the inital exception message,

Exception happened when running extended stored procedure "xp_sqllitespeed_version" in the library "xpSLS.dll".
SQL Server is terminating process 164. Exception type: Win32 exception; Exception code: 0x80000003.


We know this has to do with LiteSpeed's xpSLS.dll. xpSLS.dll implements LiteSpeed's extended stored procedures, and is loaded into the address space of the SQL Server engine. This is tied to the implementation of our extended stored procedures.

we suspected this to be a memory corruption.

and we looked at upgrading to 5.0.1 as we read on quests website tthat there were some bugs in older versions of litespeed.
here is a work around if you see the same issues.

> Schedule backups to run in serial instead of in parallel or upgrade to the latest version of Litespeed for sql server.
>
>
here's some additional information about the the memory consumption of LiteSpeed, in particular which memory area would it compete (use) on SQL Server.
>
> VDI uses the area set by the SQL Server mem to leave setting. When SQL Server starts up it allocates mem-to-leave amount of memory. It then allocates its buffer pool and a number of other memory allocations needed for the database to run. Then when it is finished allocating all of the memory required at the end of start up it then *releases*/frees the memory it had just allocated for the mem to leave. It does this in this manner so that it will have a contiguous mem to leave area separate from the buffer pool and such. One can see this behavior by injecting into sqlservr.exe and detouring malloc and such. THEN as third party apps execute extended stored procedures or connect and use VDI to perform backups, any memory that SQL Server allocates just using normal malloc or AllocateVirtualMemory will come out of that mem-to-leave area. SQL Server native backups do not use VDI and do not allocate extra memory from that area themselves when doing native backups.

In order to fix these errors, I would suggest to install 5.0.1 or later. to install the package so you dont see this error
State: 0\015.Stored function 'xp_sqllitespeed_version' in the library 'xpSLS.dll' generated an access violation. SQL Server is terminating process

Reinstalling litespeed

Phase I: Before upgrade, steps to take.

a. Stop all your backup jobs.

b. Close out your LiteSpeed console.

c. Logon with system administrator privilege to perform the installation.

d. Copy LiteSpeed.msi to C or D drive locally.

e. Run your installation and press next at the license registration to complete (it will pick up the existing license key)

f. If all is good, go to phase IV, else proceed next steps below.



Phase II: In the event, you may get an error like xpSLS.dll or slssqlmaint.exe is being used.

a. Go to your SQLServer\binn\ rename name xpSLS.dll to *.old and slssqlmaint.exe *.old

b. Try your installation again, if it is still fail with the same error, then try to restart your SQLServer services.

a. In the event, it may still lock on the file, reboot the server is the last option (not very likely, but I have seen it, and fyi only.)

c. Then, you can run through phase I again.

d. If all is good, go to phase IV, else proceed next steps below.



Phase III: Other scenarios for cluster that you may get an error like, fail to create director or access is denied. Perform the step below;

1. Copy your LiteSpeed.msi to the active node.

2. Logon with system administrator account equivalent.

3. Double click to begin the setup.

4. Get to the screen where it gives you the option to SELECT all instances for the same login and show ACTION (INSTALL) for your instances.

5. Click the INSTALL | change it to IGNORE for all.

6. Click next to get to the license registration | select demo for now.



It should pass the error, if you are still getting the same error, then copy LiteSpeed.msi, and run the setup from your failing node to create the directory. Then, follow the step above from 2 to 6. Once, it is done with the installation, and you should be able to run the INSTANCE CONFIGURATION to populate the binary files,

see steps below;



a. Select Start | all programs | Quest Software | LiteSpeed | Instance Configuration.

b. Leave the INSTALL intact this time.



Phase IV: Confirm your LiteSpeed engine is in place.



a. Open a query analyzer | select master | type EXEC XP_SQLLITESPEED_VERSION

a. It should return with product and engine match the value for both parameter.

b. In the event, it did not match, please restart your SQLServer services.

b. Test your backup from a LiteSpeed console against a small database.

Spin locks in sql 2005 crashing sql2005

how to resolve spinlocks in 2005 as you can see below this version of sql2005 has service pack 3 and cu5 on it.
I've documented in red what is going on with the system.
0:005> lmv msqlservr

start end module name

00000000`01000000 00000000`03662000 sqlservr (deferred)

Mapped memory image file: D:\SymCache\sqlservr.exe\4A7245F62662000\sqlservr.exe

Image path: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe

Image name: sqlservr.exe

Timestamp: Fri Jul 31 09:16:38 2009 (4A7245F6)

CheckSum: 025DBEE2

ImageSize: 02662000

File version: 2005.90.4230.0

Product version: 9.0.4230.0

File flags: 0 (Mask 3F)

File OS: 40000 NT Base

File type: 1.0 App

File date: 00000000.00000000

Translations: 0409.04e4

CompanyName: Microsoft Corporation

ProductName: Microsoft SQL Server

InternalName: SQLSERVR

OriginalFilename: SQLSERVR.EXE

ProductVersion: 9.00.4230.00

FileVersion: 2005.090.4230.00

FileDescription: SQL Server Windows NT - 64 Bit

LegalCopyright: © Microsoft Corp. All rights reserved.

LegalTrademarks: Microsoft® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation

Comments: NT AMD64



And you have 8 CPUs there installed on the server, while all of them were executing the following:

SOS_Scheduler:3BA4080 m_id:0 status:VISIBLE ONLINE

m_pCurrentWorker:9FF61C0 Debug id:147



# Child-SP RetAddr Call Site

00 00000000`48c4ba58 00000000`77d705d6 ntdll!ZwDelayExecution+0xa

01 00000000`48c4ba60 00000000`0193a631 kernel32!SleepEx+0xaf

02 00000000`48c4bb00 00000000`0168aeb7 sqlservr!SpinlockBase::Sleep+0x1566c1

03 00000000`48c4bc80 00000000`01dc31c6 sqlservr!SpinlockBase::SpinToAcquire+0x117

04 00000000`48c4bcd0 00000000`02173ddc sqlservr!TSyncHashTable_EntryAccessor,0,16,&CQSIndexIdent::UlIndexIdentHash>::TSyncHashTable_EntryAccessor,0,16,&CQSIndexIdent::UlIndexIdentHash>+0x76

05 00000000`48c4bd10 00000000`019e2cc9 sqlservr!CQSIndexStatsMgr::AddNewMissingIndex+0x7c

06 00000000`48c4bda0 00000000`016df878 sqlservr!CIdxSuggestion::Register+0x18a819

07 00000000`48c4bfb0 00000000`0173b192 sqlservr!COptExpr::PqteConvert+0x1d8

08 00000000`48c4c040 00000000`016df77d sqlservr!CPhyOp_RestrRemap::PqteConvert+0xc2

09 00000000`48c4c2b0 00000000`0173ab18 sqlservr!COptExpr::PqteConvert+0xdd

0a 00000000`48c4c340 00000000`016ef4f7 sqlservr!COptExpr::PqteConvertTree+0x78

0b 00000000`48c4c4d0 00000000`01712037 sqlservr!COptContext::PcxteOptimizeQuery+0x7c7

0c 00000000`48c4e880 00000000`0170658d sqlservr!CQuery::Optimize+0x217

0d 00000000`48c4ea10 00000000`01509e87 sqlservr!CQuery::PqoBuild+0x54d

0e 00000000`48c4ee50 00000000`014f0c0a sqlservr!CStmtQuery::InitQuery+0x167

0f 00000000`48c4eef0 00000000`01724abd sqlservr!CStmtSelect::Init+0x8a

10 00000000`48c4ef80 00000000`0157f15b sqlservr!CCompPlan::FCompileStep+0x3ed

11 00000000`48c4f040 00000000`014e8af1 sqlservr!CSQLSource::FCompile+0x6eb

12 00000000`48c4f2d0 00000000`016786f7 sqlservr!CSQLSource::FCompWrapper+0x121

13 00000000`48c4f390 00000000`01669db2 sqlservr!CSQLSource::Transform+0x377

14 00000000`48c4f450 00000000`0166ae92 sqlservr!CSQLSource::Execute+0x292

15 00000000`48c4f5a0 00000000`0166a9ad sqlservr!process_request+0x312

16 00000000`48c4f830 00000000`0167139e sqlservr!process_commands+0x3fd

17 00000000`48c4faf0 00000000`01670b89 sqlservr!SOS_Task::Param::Execute+0xee

18 00000000`48c4fc00 00000000`01676ca4 sqlservr!SOS_Scheduler::RunTask+0xc9

19 00000000`48c4fc90 00000000`017afcb7 sqlservr!SOS_Scheduler::ProcessTasks+0xb4

1a 00000000`48c4fd00 00000000`014c5a79 sqlservr!SchedulerManager::WorkerEntryPoint+0xe7

1b 00000000`48c4fda0 00000000`017b02c0 sqlservr!SystemThread::RunWorker+0x59

1c 00000000`48c4fde0 00000000`017f9b88 sqlservr!SystemThreadDispatcher::ProcessWorker+0x130

1d 00000000`48c4fe80 00000000`781337a7 sqlservr!SchedulerManager::ThreadEntryPoint+0x128

1e 00000000`48c4ff20 00000000`78133864 msvcr80!_callthreadstartex+0x17

1f 00000000`48c4ff50 00000000`77d6b6da msvcr80!_threadstartex+0x84

20 00000000`48c4ff80 00000000`00000000 kernel32!BaseThreadStart+0x3a



0:270> ub 00000000`0168aeb7

sqlservr!SpinlockBase::SpinToAcquire+0xf4:

00000000`0168ae94 e970ffffff jmp sqlservr!SpinlockBase::SpinToAcquire+0x69 (00000000`0168ae09)

00000000`0168ae99 458bcd mov r9d,r13d

00000000`0168ae9c 458bc6 mov r8d,r14d

00000000`0168ae9f 498bd4 mov rdx,r12

00000000`0168aea2 488bcf mov rcx,rdi

00000000`0168aea5 4889742428 mov qword ptr [rsp+28h],rsi

00000000`0168aeaa c744242001000000 mov dword ptr [rsp+20h],1

00000000`0168aeb2 e8b9901500 call sqlservr!SpinlockBase::Sleep (00000000`017e3f70)



0:147> u sqlservr!SpinlockBase::Sleep

sqlservr!SpinlockBase::Sleep:

00000000`017e3f70 4053 push rbx

00000000`017e3f72 55 push rbp

00000000`017e3f73 56 push rsi

00000000`017e3f74 57 push rdi

00000000`017e3f75 4154 push r12

00000000`017e3f77 4155 push r13

00000000`017e3f79 4157 push r15

00000000`017e3f7b 4881ec40010000 sub rsp,140h



0:147> dq 00000000`48c4bb00+0x140+8+8+8

00000000`48c4bc58 00000000`801c3428 00000000`03ba0508

00000000`48c4bc68 00000000`00001e68 00000000`00002710

00000000`48c4bc78 00000000`0168aeb7 00000000`00000000

00000000`48c4bc88 00000002`0f7e0a20 00000000`801b82c8

00000000`48c4bc98 00000000`01c5c471 00000000`00000001

00000000`48c4bca8 00000000`03ba0508 00000000`0009001f

00000000`48c4bcb8 00000000`48c4bdd8 00000000`00000001

00000000`48c4bcc8 00000000`01dc31c6 00000000`801c3418



So the rdi is on the stack, and we found it to be 00000000`801c3428, and it told us the spin lock was owned by the thread whose id is 7396,

0:147> ~~[0n7396]s

ntdll!ZwSignalAndWaitForSingleObject+0xa:

00000000`77ef1b5a c3 ret

0:270> kL

Child-SP RetAddr Call Site

00000000`5f3bbab8 00000000`77d44ebb ntdll!ZwSignalAndWaitForSingleObject+0xa

00000000`5f3bbac0 00000000`0165de56 kernel32!SignalObjectAndWait+0x133

00000000`5f3bbb70 00000000`01662d87 sqlservr!SOS_Scheduler::SwitchContext+0x316

00000000`5f3bbe60 00000000`0166f5a0 sqlservr!SOS_Scheduler::Suspend+0x97

00000000`5f3bbea0 00000000`019518a1 sqlservr!SOS_Event::Wait+0x150

00000000`5f3bbf00 00000000`01673a40 sqlservr!SOS_UnfairMutexPair::LongWait+0x3fef51

00000000`5f3bbf80 00000000`01676b20 sqlservr!CMemThread::Free+0x4e0

00000000`5f3bbfe0 00000000`02173e1d sqlservr!commondelete+0x30



0000000`5f3bc010 00000000`019e2cc9 sqlservr!QSIndexStatsMgr::AddNewMissingIndex+0xbd

00000000`5f3bc0a0 00000000`016df878 sqlservr!CIdxSuggestion::Register+0x18a819

00000000`5f3bc2b0 00000000`0173ab18 sqlservr!COptExpr::PqteConvert+0x1d8

00000000`5f3bc340 00000000`016ef4f7 sqlservr!COptExpr::PqteConvertTree+0x78

00000000`5f3bc4d0 00000000`01712037 sqlservr!COptContext::PcxteOptimizeQuery+0x7c7

00000000`5f3be880 00000000`0170658d sqlservr!CQuery::Optimize+0x217

00000000`5f3bea10 00000000`01509e87 sqlservr!CQuery::PqoBuild+0x54d

00000000`5f3bee50 00000000`014f0c0a sqlservr!CStmtQuery::InitQuery+0x167

00000000`5f3beef0 00000000`01724abd sqlservr!CStmtSelect::Init+0x8a

00000000`5f3bef80 00000000`0157f15b sqlservr!CCompPlan::FCompileStep+0x3ed

00000000`5f3bf040 00000000`014e8af1 sqlservr!CSQLSource::FCompile+0x6eb

00000000`5f3bf2d0 00000000`016786f7 sqlservr!CSQLSource::FCompWrapper+0x121



When the query optimizer of SQL Server 2005 generates a query plan, the query optimizer may wait for a spinlock to be released. The spinlock is used to access internal structure to record information about the missing indexes for the query plan. While the query optimizer is waiting for the spinlock, the query optimizer does not release the scheduler. The query optimizer does not obtain the spinlock and keeps holding the scheduler. All the threads blocked in the spin lock, which cause the SQL Server cannot respond to the CheckServcieAlive request from the Windows cluster, which resulted in the SQL Server resource failure and SQL group failover.



We can see it hit a known issue in this build of SQL Server:

974205 FIX: Error message in the Errorlog file of SQL Server 2005 or of SQL Server 2008 after the SQL Server service stops responding: "Timeout occurred while waiting for latch"

http://support.microsoft.com/default.aspx?scid=kb;EN-US;974205



It was fixed in the SQL Server 2005 SP3 CU6. You can download it from the following URL:

http://support.microsoft.com/kb/974648/LN/

If you get this error or similar install the SQL Server 2005 SP3 CU6 at your earliest convenience.