SQL2005Help

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

2010-12-22T21:30:00.000-08:00

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

2010-12-22T21:27:00.000-08:00

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

2010-12-04T05:05:00.000-08:00

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

2010-06-28T20:29:00.001-07:00

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

2010-06-08T23:11:00.000-07:00

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

2010-06-08T23:03:00.000-07:00

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

2010-05-30T22:18:00.000-07:00

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

2010-05-26T23:08:00.000-07:00

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

2010-05-17T20:01:00.000-07:00

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

2010-05-02T19:52:00.001-07:00

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

2010-04-25T22:33:00.000-07:00

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

2010-04-24T22:09:00.000-07:00

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

2010-04-23T17:55:00.000-07:00

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)

2010-04-16T08:21:00.000-07:00

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

2010-04-16T08:19:00.001-07:00

(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

2010-04-16T08:16:00.000-07:00

(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

2010-04-16T06:40:00.000-07:00

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

2010-04-16T06:12:00.000-07:00

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.

whos blocking

2009-06-07T07:41:00.000-07:00

-- A quick little standalone script that tells you what process is blocking and what processes the blocking processing actually blocking.

-- When running this script in QA, change your output to "Results in Text" ( CTRL-T ). Utilizes the blocking info in sp_who2 combined with dbcc inputbuffer and a little cursor to wrap it all up. Formatting needs a little work, but the info is all there.

set nocount on

declare @spid varchar(10)

declare @blkby varchar(10)

declare @stmt varchar(100)

if not exists ( select top 1 name from tempdb..sysobjects where name like '#temp%' )

begin

create table #temp ( spid integer, status varchar(100), login varchar(50), hostname varchar(25), blkby varchar(10),

dbname varchar(25), command varchar(100), cputime integer, diskio integer, lastbatch varchar(25),

programname varchar(255), spid2 integer )

end

else

begin

truncate table #temp

end

insert into #temp

exec sp_who2

declare curs cursor for

select convert(varchar(10),spid), blkby from #temp where blkby not like '%.%'

open curs

fetch next from curs into @spid, @blkby

while @@fetch_status = 0

begin

set @stmt = 'dbcc inputbuffer(' + @blkby + ')'

raiserror('SPID:%s is Blocking with the following statement',0,1,@blkby) with nowait

exec (@stmt)

raiserror('SPID that is Blocked:%s',0,1,@spid) with nowait

set @stmt = 'dbcc inputbuffer(' + convert(varchar(10), @spid) + ')'

exec (@stmt)

fetch next from curs into @spid, @blkby

end

close curs

deallocate curs

script out logins for 2000 to 2005 servers

2009-06-07T07:39:00.000-07:00

USE master

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL

DROP PROCEDURE sp_hexadecimal

CREATE PROCEDURE sp_hexadecimal

@binvalue varbinary(256),

@hexvalue varchar(256) OUTPUT

DECLARE @charvalue varchar(256)

DECLARE @i int

DECLARE @length int

DECLARE @hexstring char(16)

SELECT @charvalue = '0x'

SELECT @i = 1

SELECT @length = DATALENGTH (@binvalue)

SELECT @hexstring = '0123456789ABCDEF'

WHILE (@i <= @length)

BEGIN

DECLARE @tempint int

DECLARE @firstint int

DECLARE @secondint int

SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))

SELECT @firstint = FLOOR(@tempint/16)

SELECT @secondint = @tempint - (@firstint*16)

SELECT @charvalue = @charvalue +

SUBSTRING(@hexstring, @firstint+1, 1) +

SUBSTRING(@hexstring, @secondint+1, 1)

SELECT @i = @i + 1

END

SELECT @hexvalue = @charvalue

IF OBJECT_ID ('sp_help_revlogin_2000_to_2005') IS NOT NULL

DROP PROCEDURE sp_help_revlogin_2000_to_2005

CREATE PROCEDURE sp_help_revlogin_2000_to_2005

@login_name sysname = NULL,

@include_db bit = 0,

@include_role bit = 0

DECLARE @name sysname

DECLARE @xstatus int

DECLARE @binpwd varbinary (256)

DECLARE @dfltdb varchar (256)

DECLARE @txtpwd sysname

DECLARE @tmpstr varchar (256)

DECLARE @SID_varbinary varbinary(85)

DECLARE @SID_string varchar(256)

IF (@login_name IS NULL)

DECLARE login_curs CURSOR STATIC FOR

SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')

FROM master.dbo.sysxlogins

WHERE srvid IS NULL AND

[name] <> 'sa'

ELSE

DECLARE login_curs CURSOR FOR

SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')

FROM master.dbo.sysxlogins

WHERE srvid IS NULL AND

[name] = @login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb

IF (@@fetch_status = -1)

BEGIN

PRINT 'No login(s) found.'

CLOSE login_curs

DEALLOCATE login_curs

RETURN -1

END

SET @tmpstr = '/* sp_help_revlogin script '

PRINT @tmpstr

SET @tmpstr = '** Generated '

+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'

PRINT @tmpstr

PRINT ''

PRINT '/***** CREATE LOGINS *****/'

WHILE @@fetch_status = 0

BEGIN

PRINT ''

SET @tmpstr = '-- Login: ' + @name

PRINT @tmpstr

IF (@xstatus & 4) = 4

BEGIN -- NT authenticated account/group

IF (@xstatus & 1) = 1

BEGIN -- NT login is denied access

SET @tmpstr = '' --'EXEC master..sp_denylogin ''' + @name + ''''

PRINT @tmpstr

END

ELSE

BEGIN -- NT login has access

SET @tmpstr = 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ''' + @name + ''')'

PRINT @tmpstr

SET @tmpstr = CHAR(9) + 'CREATE LOGIN [' + @name + '] FROM WINDOWS'

PRINT @tmpstr

END

ELSE

BEGIN -- SQL Server authentication

EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT

IF (@binpwd IS NOT NULL)

BEGIN -- Non-null password

EXEC sp_hexadecimal @binpwd, @txtpwd OUT

SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=' + @txtpwd + ' HASHED'

END

ELSE

BEGIN -- Null password

SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD='''''

END

SET @tmpstr = @tmpstr + ', CHECK_POLICY=OFF, SID=' + @SID_string

PRINT @tmpstr

END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb

END

IF @include_db = 1

BEGIN

PRINT ''

PRINT '/***** SET DEFAULT DATABASES *****/'

FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb

WHILE @@fetch_status = 0

BEGIN

PRINT ''

SET @tmpstr = '-- Login: ' + @name

PRINT @tmpstr

SET @tmpstr = 'ALTER LOGIN [' + @name + '] WITH DEFAULT_DATABASE=[' + @dfltdb + ']'

PRINT @tmpstr

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb

END

IF @include_role = 1

BEGIN

PRINT ''

PRINT '/***** SET SERVER ROLES *****/'

FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb

WHILE @@fetch_status = 0

BEGIN

PRINT ''

SET @tmpstr = '-- Login: ' + @name

PRINT @tmpstr

IF @xstatus &16 = 16 -- sysadmin

BEGIN

SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''sysadmin'''

PRINT @tmpstr

END

IF @xstatus &32 = 32 -- securityadmin

BEGIN

SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''securityadmin'''

PRINT @tmpstr

END

IF @xstatus &64 = 64 -- serveradmin

BEGIN

SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''serveradmin'''

PRINT @tmpstr

END

IF @xstatus &128 = 128 -- setupadmin

BEGIN

SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''setupadmin'''

PRINT @tmpstr

END

IF @xstatus &256 = 256 --processadmin

BEGIN

SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''processadmin'''

PRINT @tmpstr

END

IF @xstatus &512 = 512 -- diskadmin

BEGIN

SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''diskadmin'''

PRINT @tmpstr

END

IF @xstatus &1024 = 1024 -- dbcreator

BEGIN

SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''dbcreator'''

PRINT @tmpstr

END

IF @xstatus &4096 = 4096 -- bulkadmin

BEGIN

SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''bulkadmin'''

PRINT @tmpstr

END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb

END

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0

exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1

Reading a SQL Profiler Trace file

2009-06-07T07:38:00.000-07:00

Reading a SQL Profiler Trace file

In SQL Profiler you can open data from a file or a table with File -> Open menu option. If you have your data in multiple rolled over files SQL profiler will ask you if you'd like to open the next file after it reaches the end of the current file. You can also replay your trace using the Replay menu in which you can even set breakpoints.

Personally I like to analyze my trace files with SQL. To do that you have to have the data in a trace table. If you have a file you should import that file into the table.

The only way to do that is by using the fn_trace_gettable built-in table valued function:

select *

from fn_trace_gettable('c:\MyTraceFile.trc', default)

The first parameter is obviously the path to the trace file on the server's disk. The second parameter is the number of rolled over files to be read. The default value is -1 which means all files are read. If you want to read just one of many rolled over files use 1 as your parameter value.

Although you could use the function to read the trace file each time, the better way is to simply import the trace into a table and query the table itself since reading the file each time is slow. A simple way to do this is:

select IDENTITY(BIGINT, 1, 1) AS RowNumber, *

into MyTraceTable

from fn_trace_gettable('c:\MyTraceFile.trc', default)

where 1 = 0 -- just create the table without any data

After you have the table definition you can index it to your liking and then simply use insert to populate the table:

insert into MyTraceTable()

select

from fn_trace_gettable('c:\MyTraceFile.trc', default)

Combining SQL Profiler Trace file and PerfMon log file

Start SQL Profiler and open a trace file in it. The SQL Trace file and the PerfMon log file must be time synchronized. So either you have to be gathered on the same machine or the 2 machines have to be time synchronized. Wait until the SQL Trace file is fully loaded. Then go to File -> Import Performance Data...

Choose the PerfMon log file (.BLG, .CSV) that correlates with the SQL trace file. A dialog will open asking you to select the counters you'd like to display. Click OK and you should get a screen looking similar to this:

Picture 4: Combine SQL Profiler Trace file and PerfMon counters log file

By clicking on the PerfMon graph you can see that the selected row in your trace changes. The PerfMon counter has the minimum polling interval of 1 second so you will get more than one sql event per PerfMon counter and the selected row in the trace is the row that best corresponds with the PerfMon counter at that time.

Shrink a named transaction log file belonging to a database

2009-06-07T07:37:00.000-07:00

Shrink a named transaction log file belonging to a database

Originally found at;

http://support.microsoft.com/support/kb/articles/q256/6/50.asp

Changes:

28.08.2001

Modified the inner loop so it tested the dx time so long overruns did not happen

Modified the inner loop so it had a fixed minimum quantity so there was no skip in skip out

29.08.2001

Modified the inner loop so it had a dynamic minimum quantity to allow faster shrinkage

24.01.2002

Modified the USE statement so it uses brackets around the dbname

Modified the @TruncLog variable so it uses brackets around the dbname

31.05.2002

Modified the code to use PRINT instead of SELECT in several cases

Modified the code to use @MaxCount instead of two unclear rules

Modified the code to use @Factor instead of several hard-coded values

Commented the use of @Factor

Moved the configuration and @Counter init code to before the start of the first loop to avoid repetition

Modified the code to display the process runtime in seconds rather than minutes

SET NOCOUNT ON

DECLARE @LogicalFileName SYSNAME,

@MaxMinutes INT,

@NewSize INT,

@Factor FLOAT

The process has several control parameters, most of the time you only need to worry about the first four

as these are the big controls whereas the fifth is simply a fine tuning control which rarely needs to

come into play.

--This is the name of the database for which the log will be shrunk.

USE [Speqs]

--Use sp_helpfile to identify the logical file name that you want to shrink.

SET @LogicalFileName = 'Speqs_Log';

--Limit on time allowed to wrap log in minutes

SET @MaxMinutes = 5;

--Ideal size of logfile in MB

SET @NewSize =100;

Factor determining maximum number of pages to pad out based on the original number of pages in use

(single page = 8K). Values in the range 1.0 - 0.8 seems to work well for many databases.

Increasing the number will increase the maximum number of pages allowed to be padded, which should

force larger amounts of data to be dropped before the process finishes. Often speeds up shrinking

very large databases which are going through the process before the timer runs out.

Decreasing the number will decrease the maximum number of pages allowed to be padded, which should

force less work to be done. Often aids with forcing smaller databases to shrink to minimum size

when larger values were actually expanding them.

SET @Factor = 1.0;

All code after this point is driven by these parameters and will not require editing unless you need to

fix a bug in the padding/shrinking process itself.

-- Setup / initialize

DECLARE @OriginalSize INT,

@StringData VARCHAR(500)

SELECT @OriginalSize = size -- in 8K pages

FROM sysfiles

WHERE name = @LogicalFileName;

SELECT @StringData = 'Original Size of ' + db_name() + ' LOG is ' +

CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +

CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'

FROM sysfiles

WHERE name = @LogicalFileName;

PRINT @StringData;

PRINT ''

--Drop the temporary table if it already exists

IF ( OBJECT_ID('[dbo].[DummyTrans]') IS NOT NULL )

DROP TABLE [DummyTrans]

CREATE TABLE [DummyTrans]( [DummyColumn] CHAR(8000) NOT NULL );

-- Wrap log and truncate it.

DECLARE @Counter INT,

@MaxCount INT,

@StartTime DATETIME,

@TruncLog VARCHAR(500)

-- Try an initial shrink.

DBCC SHRINKFILE (@LogicalFileName, @NewSize)

SET @TruncLog = 'BACKUP LOG [' + db_name() + '] WITH TRUNCATE_ONLY';

EXEC (@TruncLog)

-- Configure limiter

IF @OriginalSize / @Factor > 50000

SET @MaxCount = 50000

ELSE

SET @MaxCount = @OriginalSize * @Factor

-- Attempt to shrink down the log file

PRINT 'Minimum Quantity : '+CAST( @MaxCount AS VARCHAR(10) )

PRINT 'Maximum Time : '+CAST( @MaxMinutes AS VARCHAR(10) )+' minutes ('+CAST( @MaxMinutes*60 AS VARCHAR(10) )+' seconds)'

PRINT ''

SET @Counter = 0;

SET @StartTime = GETDATE();

--loop the padding code to reduce the log while

-- within time limit and

-- log has not been shrunk enough

WHILE (

(@MaxMinutes*60 > DATEDIFF(ss, @StartTime, GETDATE())) AND

(@OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)) AND

((@OriginalSize * 8 / 1024) > @NewSize)

)

BEGIN --Outer loop.

--pad out the logfile a page at a time while

-- number of pages padded does not exceed our maximum page padding limit

-- within time limit and

-- log has not been shrunk enough

WHILE (

(@Counter < @MaxCount) AND

(@MaxMinutes*60 > DATEDIFF(ss, @StartTime, GETDATE())) AND

(@OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)) AND

((@OriginalSize * 8 / 1024) > @NewSize)

)

BEGIN --Inner loop

INSERT INTO DummyTrans VALUES ('Fill Log') -- Because it is a char field it inserts 8000 bytes.

DELETE FROM DummyTrans

SELECT @Counter = @Counter + 1

--Every 1,000 cycles tell the user what is going on

IF ROUND( @Counter , -3 ) = @Counter

BEGIN

PRINT 'Padded '+LTRIM( CAST( @Counter*8 AS VARCHAR(10) ) )+'K @ '+LTRIM( CAST( DATEDIFF( ss, @StartTime, GETDATE() ) AS VARCHAR(10) ) )+' seconds';

END

--See if a trunc of the log shrinks it.

EXEC( @TruncLog )

END

PRINT ''

SELECT @StringData = 'Final Size of ' + db_name() + ' LOG is ' +

CONVERT(VARCHAR(30),size) + ' 8K pages or ' +

CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'

FROM sysfiles

WHERE name = @LogicalFileName;

PRINT @StringData

PRINT ''

DROP TABLE DummyTrans;

PRINT '*** Perform a full database backup ***'

SET NOCOUNT OFF

Tools for helping to backup databases

2009-01-27T22:29:00.000-08:00

I find litespeed the best backup too by far for mssql products, Why might you ask do I need a backup tool when mssql has sqlbackup built in. Well the main reason is that mssql backup until now did not have a compression tool 2008 now does. However, if you are on sql2005 or below then I would recommend litespeed.

here is how to install litespeed and a small guide to using it to back up.

SQL Lite Speed Installation Guide

Installation Steps

1. Put in the CD brows the cd
2. Launch the install by double clicking on the file litespeedforsqlserver32bit_45000157_1.msi

The screen below will be shown once the installer starts

Choose Next> to move to the next screen

Choose Next> to move to the next screen

Accept the License Agreement and choose Next> to move to the next screen

Enter the User Name and Organization as above. This is the default and should already be filled in. Ensure the application is installed for anyone who uses the computer and choose Next >

The default destination folder will be on the C: drive. This needs to be changed to reside on the E: drive.
Choose the Change… button

Enter the folder name as E:\Imceda\LiteSpeed\SQL Server\ and click OK

If the destination folder is defined correctly choose Next >

Select Complete setup and choose Next >

Click the Install button to begin the installation

Choose Next >

Select the “Use Same login details for all instances” and ensure Install is listed as the Install Action against all SQL Instances on the server.

We do not perform activity logging so ensure that Activity Logging is not setup and choose Next >

The following screen will summarise what will be installed. Choose Next >

The Installation has now completed. Choose Finish.

The Registration process will now be launched.

Choose Next >

Select “Register your copy of LiteSpeed 2005” and choose Next >

Select “Load Registration Key From File”. The file you have to load is called GlobalLiteSpeedLicenseKey.txt and is located in the same folder as the installation software.

Choose Next > to proceed

Once the Registration process has been completed, the installation is now complete.

to backup the database use
xp_backup_database
@database = database
,@filename = '\path\backup\database.bak'

To restore a database with litespeed us this command
How to restore a file with litespeed if you get the error this is not a vaild backup file.

xp_restore_database
@database = 'database'
,@filename = '\path\backup\database.BAK'

or you could create a job to do this for you.

Select Tutorial

2009-01-08T15:15:00.000-08:00

There are many ways to select data from tables I will go through them.

Counting rows

Counting Rows :

COUNT(*) counts the number of rows in a table.

The syntax is
select count(*) from table name
here is an Example

select count(*) from test;
+----------+
| count(*) |
+----------+
| 148|
+----------+
1 row in set (0.00 sec)

This query shows the number of rows in the test table

The most basic of querys in the

select everything query
and now you learned how to insert data into the table, we should probably check the datas is stored correctly. To do so, we use the SELECT statement.

The Select syntax is

SELECT what_to_select from table name;

To view all the data from the table, we use the below query.

select * from test;
OfficeCode AccountCode AccountName SecType
1111 084534334 Test 1
1112 3434343435 3434 2
2 rows in set (0.00 sec)
The above example query will list the complete details of the test table. Here * will select all the columns from the

To also limit your select
use

select top 10 * from test

this will show the top 10 rows

OfficeCode AccountCode AccountName
6 6670708 test
6 6610108 test2
6 6044208 test3
6 6045208 test4
6 6049208 test5
6 6046208 test6
6 6050208 test7
6 6047208 test8
6 6048208 test9
6 6041208 test10

Inserting in to a table

2009-01-07T22:27:00.000-08:00

Now you have created your table here is a basic insert statement to show you how to insert data.

INSERT INTO dbo.BRIAN ( a, b ) VALUES ( 1, 21 )
go
INSERT INTO dbo.BRIAN ( a, b ) VALUES ( 39, 21 )
go
INSERT INTO dbo.BRIAN ( a, b ) VALUES ( 1998, 2001 )
go
INSERT INTO dbo.BRIAN ( a, b ) VALUES ( 26, 2817 )
go
INSERT INTO dbo.BRIAN ( a, b ) VALUES ( 21, 2938 )
go
INSERT INTO dbo.BRIAN ( a, b ) VALUES ( 12, 21 )
go
INSERT INTO dbo.BRIAN ( a, b ) VALUES ( 3939, 4848 )
go

creating a table

2009-01-07T21:46:00.000-08:00

Now you know how to create a database here is an example of creating a table.

USE [test]
GO
/****** Object: Table [dbo].[Audit] ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test]( [id] [int] IDENTITY(1,1) NOT NULL, [tracename] [nvarchar](100) NULL, [enable] [int] NULL) ON [PRIMARY]

If NULL is specified, the field is allowed to be left empty. If NOT NULL is specified, the field must be given a value. In the absence of either a NULL or NOT NULL, NULL is assumed.

The above query will create the table student with fields ID and Name.PRIMARY KEY : A PRIMARY KEY is a field in a table that uniquely identifies a record. This attribute is used to define the field name to create a primary key.

PRIMARY KEY(id)

What is a Database Administrator(DBA)

2009-01-07T21:37:00.000-08:00

here are too types of DBA, A Production DBA and Software DBA Software DBA's mainly help with designing databases and schemers where as a Production DBA will help to keep a system tight design what permissions a use has, Backup maintain the databases and perform code releases.

The best way to become a DBA is to take the relevant classes and familiarize yourself with the specific, popular databases that are in demand. Becoming certified in a specific database program is a good way to show your skill level. Internships are often a great start to a Database Administrator career. Other technical support roles, such as System Administrators and Network Administrators, may easily transition into a Database Administration role by becoming certified as a Database Administrator

What is SQL?

2009-01-07T21:26:00.000-08:00

SQL stands for Structured Query Language and is used to communicate with Databases, this is the standard language for use on relational database management systems(RDBMS). SQL statements are used for such commands as selecting from tables updates and deletes. Selects retrieve data from the database tables. The most common RDBMS system are Sybase, Oracle, Microsoft SQL server, MYSQL, Access, Postgress and Informix. Although most Databases systems use SQL some databases have there own software. The standard commands in SQL as mentioned before are Select", "Insert", "Update", "Delete", "Create", and "Drop" these commands can accomplish most tasks that one needs to do with a database.

Economic Down Turn

2009-01-07T20:25:00.000-08:00

I am now living in Japan, I am a DBA for a contract company contracting to a bigger company.
With the downturn I am always worried about my job, I guess I am lucky as I still have my job, However, it is funny as I am told there is always work for DBA's and DBA's are still in demand.

I am upset that the govenment has not done anything to protect the people that are laid off and are bailing out the people that caused all the trouble.

What we can do during these times is try to make our skills count. And, instead of worrying about or jobs and letting our work suffer is try to reduced the change of us getting laid off try to make your job so valuble that you wont get cut. How you might ask?
firstly I would say try to boost your DBA and IT skills how you might ask again? My Training budget has been cut.

Well I would say use the internet there are a number of resources out there.
try sites like
http://www.databasejournal.com
http://www.sqlservercentral.com
www.sybase.com
www.oracle.com
all these sites have good techical questions and also Oracle and Sybase have great online books free to download.

Start new projects maybe a migration or try new technologies if you are using database backup try litespeed, have a look what else is out there you may get free downloads you can practise with add it too your skill set so if you do get laid off then you can show these skills.

Try to find events some of these have free training, i know SQL PASS and code Camps do.

So in other words dont sit around moping, invest in yourself. this may cost nothing but your time but you will reap there rewards in the end.

if you delete your transaction log

2009-01-07T19:57:00.001-08:00

If you delete the transactionlog by mistake try this script

This script should fix a databases where the transaction log has been deleted or croupted. You will have do do some of this manually as explained it will take a while depending on the databases size.
/*
Stop SQL service, rename mydb.mdf to mydbBad.mdf.
started SQL service, created fake mydb db (with log etc)
Stopped SQL service
Deleted mydb.mdf
Renamed mydbBad.MDF to mydb.MDF
Started SQL service.
Ran following script:
*/
ALTER DATABASE mydb SET EMERGENCY
sp_dboption 'mydb', 'single user', 'true'
DBCC CHECKDB ('mydb', REPAIR_ALLOW_DATA_LOSS)
sp_dboption 'mydb', 'single user', 'false'

scripting out logins from 2000 to 2005

2009-01-07T19:55:00.000-08:00

SQL 2000 to SQL 2005: Where have all the old features gone?

http://www.sqlmag.com/Articles/ArticleID/16090/16090.html

Introduction where have all the good features goine in sql2005
As more DBAs across the planet begin using SQL 2005 Tools, but still manage SQL 2000 servers with them, I suspect there will be lots of muffled moaning and wondering where have all the good old features gone. Although Management Studio has some very nice long-awaited features, some of the good old stuff just isn’t there.
Where are my tried and true tools, such as taskpad? Where’s the IF EXISTS DROP option when I script out the stored procedures? Could someone pinch me and tell me this is just a bad dream?
The aspirin industry will profit enormously from that sort of thing.
To name a few good old pals that have all but disappeared into obscurity:
• Taskpad
• Ability to quickly script permissions on stored procedures
• Ability to quickly see disk space in database properties
• Time of creation of stored procedures
Sure, if you're connecting to an instance of SQL Server 2005 with Management Studio you get colorful reports and plethora of professional-looking graphs at your disposal, but what about the majority of us that still did not migrate our servers to SQL 2005, but already upgraded the tools?
The good news is this will tend to convert many GUI DBAs into hardened command-line pros, improve they're typing skills, etc. In the next section I will show how to still take advantage of the old tools functionality.
Taskpad functionality
I don't know about you all, but I really like the Taskpad and use it all the time. I am used to it like to an old slipper; it fits my needs. And even if it did throw a vague error now and then I forgive it now that it's done….forever. But how can we get its functionality back?
The General tab in Database section is now in database properties under the same heading.
Maintenance section-like information can be found by querying the backupset table in msdb:
select max(backup_start_date) from backupset
where database_name = ‘my_db'
Note: Database options, Number of Users, Date Created and Owner can still be found in database properties in the SQL 2005 tools.
Space allocated section info can be found by running this T-SQL:
select * from sysfiles
or if you just need to find the space used by you log, execute:
DBCC SQLPERF (LOGSPACE)
Table Info tab
I don't use this one very often, but you can get similar functionality by running:
Exec sp_spaceused ‘your_table_name'
To script multiple stored procedures including permissions:
Right-click the database->Tasks->Generate Scripts, pick your database. Set Include object Level Permissions to True. Note: If you set the Include if NOT EXISTS option to true, the script will not create the stored procedure if it already exists on target database.
Click Next, and select Stored Procedures only. Next select which procs you want to script, review you final options and click Finish.
Unfortunately, if you want to drop/recreate the procedures if they exist on the target server, you will need to manually include the following script in the beginning of each procedure:
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'your_proc_name' AND type = 'P')
DROP PROCEDURE 'your_proc_name'
GO
This one is truly beyond me, for reasons known only to Microsoft and the CEO of Bayer (or whoever is the biggest headache drug company these days) this option was excluded from final SQL 2005 RTM.
Check disk space
If you're like I am, you're used to clicking on database properties, and the ellipsis in order to see the free disk space on the server. In SQL Server 2005 you can get this in the report, but until then you can run undocumented extended stored procedure:
exec xp_fixeddrives
The result would look something like this:

Time of creation of stored procedures:
For some reason the time part of the Create Date column in the Summary tab of SQL 2005 is depreciated. Why? I guess someone thought DBAs don't need it any longer (rolling eyes). The good news is you can still get this information by querying the sysobjects table, like:
Select crdate as DateCreated
From dbo.sysobjects
where name = 'your_proc_name'

Note: make sure NOT to specify the owner, as in dbo.your_proc_name, but simply the procedure name. The result is:

Summary
I've shown you here how to get by with available SQL 2005 Tools until you upgrade your servers to SQL 2005 Server edition. People get used to their favorite ways to get the job done, and sometimes get “surprised” when their tools get taken away, and now they have to hammer the nail with the handle of a screwdriver. Hopefully the ways to attain old functionality will increase productivity, and hopefully the tools will continue to improve.

MSSQL How to transfer logins
http://support.microsoft.com/kb/246133
• You transfer logins and passwords from SQL Server 7.0 to SQL Server 2005.
• You transfer logins and passwords from SQL Server 2000 to SQL Server 2005.
• You assign logins to roles.
To transfer logins and passwords between different versions of SQL Server and then assign logins to roles, follow these steps:
1. Run the following script on the source SQL Server.
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin_2000_to_2005') IS NOT NULL
DROP PROCEDURE sp_help_revlogin_2000_to_2005
GO
CREATE PROCEDURE sp_help_revlogin_2000_to_2005

@login_name sysname = NULL,
@include_db bit = 0,
@include_role bit = 0

AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @dfltdb varchar (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)

IF (@login_name IS NULL)
DECLARE login_curs CURSOR STATIC FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] = @login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb

IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END

SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** CREATE LOGINS *****/'

WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr

IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = '' --'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE
BEGIN -- NT login has access
SET @tmpstr = 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ''' + @name + ''')'
PRINT @tmpstr
SET @tmpstr = CHAR(9) + 'CREATE LOGIN [' + @name + '] FROM WINDOWS'
PRINT @tmpstr
END
END
ELSE
BEGIN -- SQL Server authentication
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT

IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=' + @txtpwd + ' HASHED'
END
ELSE
BEGIN -- Null password
SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD='''''
END

SET @tmpstr = @tmpstr + ', CHECK_POLICY=OFF, SID=' + @SID_string
PRINT @tmpstr
END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END

IF @include_db = 1
BEGIN
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** SET DEFAULT DATABASES *****/'

FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb

WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr

SET @tmpstr = 'ALTER LOGIN [' + @name + '] WITH DEFAULT_DATABASE=[' + @dfltdb + ']'
PRINT @tmpstr

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
END

IF @include_role = 1
BEGIN
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** SET SERVER ROLES *****/'

FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb

WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr

IF @xstatus &16 = 16 -- sysadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''sysadmin'''
PRINT @tmpstr
END

IF @xstatus &32 = 32 -- securityadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''securityadmin'''
PRINT @tmpstr
END

IF @xstatus &64 = 64 -- serveradmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''serveradmin'''
PRINT @tmpstr
END

IF @xstatus &128 = 128 -- setupadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''setupadmin'''
PRINT @tmpstr
END

IF @xstatus &256 = 256 --processadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''processadmin'''
PRINT @tmpstr
END

IF @xstatus &512 = 512 -- diskadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''diskadmin'''
PRINT @tmpstr
END

IF @xstatus &1024 = 1024 -- dbcreator
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''dbcreator'''
PRINT @tmpstr
END

IF @xstatus &4096 = 4096 -- bulkadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''bulkadmin'''
PRINT @tmpstr
END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
END

CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1
GO
2. Save the output, and then paste and run the output in SQL Server Management Studio on the destination SQL Server 2005.
Note If the source SQL Server contains a login that has a blank password, the output contains a statement that resembles the following.
CREATE LOGIN LoginName WITH PASSWORD = '', CHECK_POLICY = OFF, SID = MySID
Back to the top
Remarks
• Review the output script carefully before you run it on the destination SQL Server. If you have to transfer logins to an instance of SQL Server in a different domain than the source instance of SQL Server, edit the script generated by the sp_help_revlogin procedure, and replace the domain name with the new domain in the sp_grantlogin statements. Because the integrated logins granted access in the new domain will not have the same SID as the logins in the original domain, the database users will be orphaned from these logins. To resolve these orphaned users, see the articles referenced in the following bullet item. If you transfer integrated logins between instances of SQL Servers in the same domain, the same SID is used and the user is not likely to be orphaned.
• After you move the logins, users may not have permissions to access databases that have also been moved. This problem is described as an "orphaned user". If you try to grant the login access to the database, it may fail indicating the user already exists:
Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists in the current database.
For instructions about how to map the logins to the database users to resolve orphaned SQL Server logins and integrated logins, see the following article in the Microsoft Knowledge Base:
240872 (http://support.microsoft.com/kb/240872/) How to resolve permission issues when you move a database between servers that are running SQL Server
For instructions about using the sp_change_users_login stored procedure to correct the orphaned users one-by-one (this will only address users orphaned from standard SQL logins), see the following article in the Microsoft Knowledge Base:
274188 (http://support.microsoft.com/kb/274188/) "Troubleshooting Orphaned Users" topic in Books Online is incomplete
• If the transfer of logins and passwords is part of a move of databases to a new server running SQL Server, see the following article in the Microsoft Knowledge Base for a description of the workflow and steps involved:
314546 (http://support.microsoft.com/kb/314546/) How to move databases between computers that are running SQL Server
• You can do this because of the @encryptopt parameter in the sp_addlogin system stored procedure, that allows a login to be created by using the encrypted password. For more information about this procedure, see the "sp_addlogin (T-SQL)" topic in SQL Server Books Online.
• By default, only members of the sysadminfixed server role can select from the sysxlogins table. Unless a member of the sysadmin role grants the necessary permissions, end users cannot create or run these stored procedures.
• This approach does not try to transfer the default database information for a particular login because the default database may not always exist on the destination server. To define the default database for a login, you can use the sp_defaultdb system stored procedure by passing it the login name and the default database as arguments. For more information about using this procedure, see the "sp_defaultdb" topic in SQL Server Books Online.
• During a transfer of logins between instances of SQL Server, if the sort order of the source server is case-insensitive and the sort order of the destination server is case-sensitive, you must enter all alphabetical characters in passwords as uppercase characters after the transfer of logins to the destination server. If the sort order of the source server is case-sensitive and the sort order of the destination server is case-insensitive, you will not be able to log in with the logins transferred using the procedure outlined in this article, unless the original password contains no alphabetical characters or unless all alphabetical characters in the original password are uppercase characters. If both servers are case-sensitive or both servers are case-insensitive, you will not experience this problem. This is a side effect of the way that SQL Server handles passwords. For more information, see the "Effect on Passwords of Changing Sort Orders" topic in SQL Server 7.0 Books Online.
• When you run the output from the sp_help_revlogin script on the destination server, if the destination server already has a login defined with the same name as one of the logins on the script output, you may see the following error upon execution of the output of the sp_help_revlogin script:
Server: Msg 15025, Level 16, State 1, Procedure sp_addlogin, Line 56
The login 'test1' already exists.
Likewise, if a different login exists with the same SID value on this server as the one you are trying to add, you receive the following error message:
Server: Msg 15433, Level 16, State 1, Procedure sp_addlogin, Line 93
Supplied parameter @sid is in use.
Therefore, you must carefully review the output from these commands, examine the contents of the sysxlogins table, and address these errors accordingly.
• The SID value for a particular login is used as the basis for implementing database level access in SQL Server. Therefore, if the same login has two different values for the SID at the database level (in two different databases on that server), the login will only have access to that database whose SID matches the value in syslogins for that login. Such a situation might occur if the two databases in question have been consolidated from two different servers. To resolve this problem, the login in question would have to be manually removed from the database that has a SID mismatch by using the sp_dropuser stored procedure, and then added again by using the sp_adduser stored procedure.

logshipping

2009-01-07T19:53:00.000-08:00

Log shipping:

Using the log shipping wizard
To use the log shipping wizard in SQL Server 2005, right click on your database and select Tasks and Ship Transaction Logs as illustrated in Figure 1.
Figure 1: Accessing the log shipping wizard. (Click on image for enlarged view.)
You may get the dialog you see displayed in Figure 2. SQL Server is telling you that this database is not a full or bulk logged recovery model.
Figure 2: Dialog displayed if your database is in the simple recovery model. (Click on image for enlarged view.)
To change the recovery model for your database, right click on your database, select properties and in the Options tab, click on the Recovery Model drop down list box and select Full or Bulk logged recovery model. This is illustrated in Figure 3. Then click OK.
Note: If you do select these recovery models and you back up your database or have already backed up the database, you will need to maintain your transaction logs by scheduling a backup of them. The log shipping wizard will configure this for you.
Figure 3: Changing the recovery model for your database. (Click on image for enlarged view.)
Once you've set the correct recovery model for your database, right click on your database and select Ship Transaction Logs again. You will receive the dialog box illustrated in Figure 4.
Figure 4: Configuring log shipping. (Click on image for enlarged view.)
Note: In this figure, I selected the check box that enables this as a primary database in a log shipping configuration. Make sure you select this check box as well. Clicking on the Backup Settings button will display the Transaction Log Backup Settings as displayed in Figure 5.
Figure 5: Transaction Log Backup Settings and options. (Click on image for enlarged view.)
There are two options to backup the transaction log to:
Network path
Local path
If you are backing up to a network path, there will be some increase in the transaction log backup times and you will decrease the space requirements on your primary server. The network path should go to the location where you want store the transaction log backups on the secondary server. Most DBAs use a network share to back up the files to

Because they want the transaction log backup files on the secondary server to protect them in the event that the primary server goes down.
The local path option will back up the transaction logs to a path on your primary server. Make sure the transaction log backups are not backed up to the same physical drive where your database data files or log files exist. Should they be backed up to the same physical drive, it would cause I/O contention and overall SQL Server performance degradation.
Figure 6 illustrates a completed Transaction Log Backup Settings dialog. Note that the share name has a dollar sign after it. This has the effect of hiding the share from users, and only users who know the share name will be able to access it. This is considered a good security practice.
Figure 6: Transaction Log Backup Settings illustrating a hidden share for the network path. (Click on image for enlarged view.)
There is an option for how long to retain your transaction log backups and an option for the threshold to raise an alert if there is no backup within a specific period. I've found the default for transaction log backup retention of three days to be adequate. I normally set a much smaller alert, like 20 minutes, but while a database backup is occurring, no log backups can occur, so if your database backups take more than an hour, you will get frequent alerts. Set this to a value that works for you. And remember: Too frequent alerts will result in your ignoring critical ones.
Once you have set the transaction log backup location, the retention period, the alerting threshold and the schedule, click OK. You will return to the Database Properties dialog box as illustrated in Figure 4. Click on the Next button to display the secondary Database Settings dialog displayed in Figure 7.
Figure 7: The secondary database settings. (Click on image for enlarged view.)
In this dialog box, you can select the secondary server (the server you'll be copying the transaction logs to) as well as the secondary database (the name of the database you will be log shipping to). Notice these options for Initialize Secondary Database:
Yes, generate a full backup of the primary and restore it into the secondary database. This option creates a backup of the database you wish to log ship and restores it on the secondary server.
Yes, restore an existing backup of the primary database. Use this option if you want to use a previously backed up database. There is an option to select the path and backup name.
No, the secondary database is initialized. Use this option if:
A copy of the database you wish to log ship has already been restored on the secondary.
The primary database is already in the full or bulk logged recovery model.
No transaction log backups have been done on the primary database since the backup was done, or if they have been done, they have been restored on the secondary.
The secondary database has been restored with the no-recovery option.
Once you have configured the options that work best for you, click on the Copy Files tab, to display the dialog, as illustrated in Figure 8.
Figure 8: Copy Files dialog. (Click on image for enlarged view.)
In the Copy Files tab, select where you copied the files from primary to the secondary. If you selected a network path in the Transaction Log Backup Settings (as illustrated in Figure 1), the network path should map to a physical location on your secondary server, and you should enter that path here (i.e., if \\ServerName\ShareName$ is the share name for the local path C:\Backup, enter this path here.)
You can also use a network path where the transaction log backups are stored on the primary server. Select how long you wish to retain the transaction log backups – understanding, of course, that the retention period might conflict with what you set in the Transaction Log Backup Settings dialog box (Figure 1).
You can also select how frequently you want the logs to be copied to the secondary server.
Once you have configured the copying of the transaction log backup files, click on the Restore Transaction Log tab as illustrated in Figure 9.
Figure 9: Restore Transaction Log tab. (Click on image for enlarged view.)
The database recovery state options are:
No Recovery Mode – This is the default option. In this option, the destination database will be inaccessible.
Standby Mode – In this option, the destination database will be read only until the next transaction log backup is applied. After the transaction log is applied, the database will be returned to read-only mode again. This allows read-only access and users will not be able to make any changes to the database (i.e., create indexes) and they will be disconnected when the next database backup is applied.
There is also an option to delay restoring the transaction log backups by a set number of hours or minutes. Some enterprises like to keep their standby server several hours out of sync from their source server.
By default, if the transaction logs are not restored within the interval defined in the "Alert if no restore occurs within" setting, an alert will be raised. Most frequently this alert is raised during a backup operation on the primary.
You also have an option to set how frequently the transaction log restores will take place. These settings are made in the Restore job dialog.
Once you make those settings, click OK and you'll see the Transaction Log Shipping dialog of the Database Properties dialog box (as illustrated in Figure 10).
Figure 10: Transaction Log Shipping tab. (Click on image for enlarged view.)
In this dialog, notice how we have configured server F as our secondary server and that database p2p3 is the database we are log shipping the Northwind database to. You can also log ship to a second secondary/standby server – this could be another secondary server in your DR site.
In any enterprise with a large number of log ship databases, you may want to create a monitor server. To create a monitor server, select "Use a monitor server instance" (Figure 10) and click on the settings button to configure a Log Shipping Monitor server. That option is illustrated in Figure 11.
Figure 11: Create a monitor server. (Click on image for enlarged view.)
In the connect button, connect to the server you wish to use as your monitor. Define how to make the connection, either through Windows authentication or through a SQL Server login. You can define the job history retention and the default is normally a good choice, and then defines how you want to send the alerts. The default option (Start Automatically when SQL Server Agent starts) is the best choice as alerts will be near real time. You can alternatively select an hourly schedule or whatever time interval you choose.
Summary
This completes our look at using the log shipping dialog in SQL Server 2005. This wizard has many options to it and can be quite bewildering to the new user. But the defaults are generally optimal in most cases and I've noted the cases where it's best to use non-

A post for the beginners, How to create a database?

2009-01-07T17:43:00.000-08:00

Creating a Database

This is a very simple task, however, one must ask themselves a few questions before doing this.

how much space do we need?

How many concurrent users do we expect on the server?

Always look at more users that you think or will use the database for expation.

What is the DB going to be used for?

I.E Office, Intranet, Internet,

If its going to be used for the internet, intranet we need to think about security because we'll need to utilze predefind zones within the firewall.

Do we need to configure IPSEC or SSL?

Do we know when we can do maitanance esp if the db needs to be up all the time?

Backing up the database?

Creating a db with sql management studio

select databases then right click and click create

you will now see this screen after you cleck create

When creating a database one rule you should always follow is the database should not contain spaces. Putting spaces in the name requires surrounding it with brackets [My Database] in TSQL to avoid syntax errors and I prefer not to have to use the brackets. I follow that by almost always setting the owner of the database to SA. In most cases on systems I administer changes will always be made by someone in the sysadmin group, rarely we might put someone into the db_owner role to let them make whatever changes are needed. The only value derived from being the database owner rather than a member of db_owner is that the owner is also dbo, which means that if they don't qualify objects with a schema when created they will be owned by dbo by default. Members of db_owner can use two part syntax to put objects into the dbo schema (create table dbo.employess...). Note that you can always change the database owner later using sp_changedbowner.
Full text indexing isn't commonly used, but we can activate now if we know it will be needed, or it can activated later.
As you can see in the next image I'm going to call my database Test, and it has automatically generated the logical names for the two files that get created by default (one MDF and one LDF). I never change these names as the generated one works fine. Moving to the right I've taken a guess at my database usage and increased the database size to 100 MB and the log size to 10 MB. It's just a guess though, so why bother? Mainly to avoid fragmentation by allocating a good chunk of space at once.

Once you have selected the databases name in this case you will need to change the default values for autogrow to do this click on the 3 dots next to the autogrowth it will look like bt 1mb, unrestricted growth ... you will then see this screen

I rarely turn autogrow off, and contrary to what many recommend I typically let the database grow if and when it's needed, not deliberately resizing the database to maintain a given amount of free space. The advantage to doing it deliberately is that you avoid the performance hit of waiting for the file to grow if it happens during production hours, but that penalty has been greatly reduced in SQL 2005 with instant initialization, a feature change that causes SQL to initialize pages on first write instead of during the auto grow. The advantage of doing it my way is less time spent on a very mundane task. The more important decision is whether to grow in percent or megabytes, and how much. There's no right answer, but in general we want to grow in as large a chunk as makes sense to avoid physical fragmentation on the drive. For this example I'm going to set both to grow at 10%, and leave file growth unrestricted.

Click on the tab next to path
and change the db bath where you would like the data and log to go and the click cilck ok this will create the database you can also change the name of the file here if you like.

Here is also the code to create this db.

REATE DATABASE [SCD] ON PRIMARY ( NAME = N'SCD', FILENAME = N'G:\MSSQL2K5\MSSQL.1\MSSQL\DATA\SCD.mdf' , SIZE = 102400KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'SCD_log', FILENAME = N'G:\MSSQL2K5\MSSQL.1\MSSQL\DATA\SCD_log.ldf' , SIZE = 10240KB , FILEGROWTH = 10%)GOEXEC dbo.sp_dbcmptlevel @dbname=N'SCD', @new_cmptlevel=90GOALTER DATABASE [SCD] SET ANSI_NULL_DEFAULT OFF GOALTER DATABASE [SCD] SET ANSI_NULLS OFF GOALTER DATABASE [SCD] SET ANSI_PADDING OFF GOALTER DATABASE [SCD] SET ANSI_WARNINGS OFF GOALTER DATABASE [SCD] SET ARITHABORT OFF GOALTER DATABASE [SCD] SET AUTO_CLOSE OFF GOALTER DATABASE [SCD] SET AUTO_CREATE_STATISTICS ON GOALTER DATABASE [SCD] SET AUTO_SHRINK OFF GOALTER DATABASE [SCD] SET AUTO_UPDATE_STATISTICS ON GOALTER DATABASE [SCD] SET CURSOR_CLOSE_ON_COMMIT OFF GOALTER DATABASE [SCD] SET CURSOR_DEFAULT GLOBAL GOALTER DATABASE [SCD] SET CONCAT_NULL_YIELDS_NULL OFF GOALTER DATABASE [SCD] SET NUMERIC_ROUNDABORT OFF GOALTER DATABASE [SCD] SET QUOTED_IDENTIFIER OFF GOALTER DATABASE [SCD] SET RECURSIVE_TRIGGERS OFF GOALTER DATABASE [SCD] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GOALTER DATABASE [SCD] SET DATE_CORRELATION_OPTIMIZATION OFF GOALTER DATABASE [SCD] SET PARAMETERIZATION SIMPLE GOALTER DATABASE [SCD] SET READ_WRITE GOALTER DATABASE [SCD] SET RECOVERY FULL GOALTER DATABASE [SCD] SET MULTI_USER GOALTER DATABASE [SCD] SET PAGE_VERIFY CHECKSUM GOUSE [SCD]GOIF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [SCD] MODIFY FILEGROUP [PRIMARY] DEFAULTGO
go
sp_changedbowner 'sa'
go

What to do if your transaction log feels up

2009-01-07T17:16:00.000-08:00

In MSSQL your Tranlog will only fill out if your hard drive is full

Before I show you how to shrink the Tran log I will explain a little about it and what the tranlog is for.

The Transaction Log is Probably the most underutilized and underpperciated part of SQL. this is probably due to its simplicity.

The transaction log can actually help to lower the server’s resources, increase productivity, all the while providing a method of an additional safety layer for you to fall back on!

Why does the tran log exist?

This history of the existence of the transaction log is difficult to trace; and is to a small degree controversial due to certain aspects are based on other applications. An example is the method to use transaction logs to restore databases is primarily based on proven methods used in Microsoft Access, which in turn is based on other application(s) and standards. This means there is no single document that states the history of Transaction Logs and most current documentation is partially based on personal experience, knowledge, and/or interpretations.
I’ll make no attempt to determine the exact history of transaction logs. I just want to point out that transaction logs are largely based on Microsoft Access methods to store information for transactions. This is important to know because it has become a common misunderstanding that using the TRUNCATE command should not only remove old transaction information, but also should SHRINK the file down. This is absolutely, without a doubt…WRONG! I’ll get into this more as this article progresses. First, to be able to understand why TRUNCATE will not make the log smaller in physical size you will need to understand how the transactions are inserted into the log; both physically and logically.
The primary purpose the transaction log is to provide a method to be able to restore a database to a point-in-time when necessary. This can include rolling back transactions to a certain time, or to roll forward transactions from a full backup restoration.
In recent years the transaction log is starting to be used for additional purposes. They are being used in mirroring databases, quickly importing data from old database versions to a new database version, and security audits. This is just the tip of the iceberg for the way these logs are being utilized to gain “useful” information. The last point, security audits, is a more relatively new and undocumented (officially, that is) use of transaction logs. Information stored within transaction logs can be very powerful, but also tricky to learn at first while the structure and information being stored is new to you. If you take your time to learn what you can get out of the transaction log, you can produce very useful information that can help to increase your productivity (especially in the Security Audits area)!
An example of how to use these logs for security audits is that you can view a transaction log and be able to detect transactions that have occurred and whom (what user or system account) had produced the transaction (this is no trivial task to accomplish); and if you know your database very well you can quickly deduce if someone is accessing parts of the database they shouldn't’t be; or even worse, if an unauthorized user has accessed your database! The techniques to use the transaction logs for security audits go beyond the scope of this article; the preceding is intended to provide a broader understanding of methods being used to obtain useful information. You can search sites such as MSDN, SQLServerCentral.com, and Google for discussions and articles detailing many different ways to audit the logs and obtain additional useful information. It is also worthwhile to note that there are 3rd party applications that utilize the transaction logs for security auditing, data movement, and many other purposes. As always, evaluate any 3rd party application when possible; and test on TEST servers before ever using new techniques or applications in a production environment.

How the Transaction Log Works
As mentioned in the “Logical Architecture (Concept)” portion, transactions are recorded into the log in a sequential manner. The LSN for each transaction will have a value that is higher than the previously written transaction. It’s actually just that simple; no complicated mathematical methods are used. There is no clever programming to add new transactions. It’s literally the same as taking a piece of paper and writing in each event that has happened in your day and using an ID number starting with 1 and increasing by 1 for each event occurring to give the order of the sequence. And just like your day, the next event that occurs to you must happen after the previous event; thus you’d give the next event a higher ID number than the previous. It’s safe to say that most of the following events have happened in this order for you to read this article:
1) You told your computer to go to the link, or to open a document, containing this article.
2) Your device displayed the article on your display, or printed the article to another format to be viewed.
3) You started reading this article.
As with these events, you can’t have the 2nd and 3rd events occur without first event obtaining the article, and you can’t read the article unless you view/print it (thus #3 can’t occur without #2 occurring first). As you can see, you can’t have these steps logged out of order to recreate this process; so each step has a higher ID number than the previous step.
A transaction log is just exactly that in concept, a journal of events that occur within your database. The main points to remember here is that each event is given a LSN that is higher than the previous LSN (to keep track of what event happened in what sequence), and that there is enough information being stored so that SQL Server may recreate the event should a restore of the database be required.

Microsoft Recommendations
The Transaction Log is recommended, by Microsoft, to be located on a mirrored drive in a fault-tolerant storage system. It is also recommended, by many experts, to ensure that the log is located on its own physical hard drive. These two recommendations are based on the idea that should the database (or the hard drive holding the database) become corrupt that you can ensure that you have access to the Transaction Log during your recovery operation. Also, this setup will help to ensure you have minimal impact on your database performance due to the consistent writing to the Transaction Log; obviously to have a hard drive write to the database and then to write to the Transaction Log would require it to physically write twice. If the database and hard drive are on separate drives then each drive can only write once, thus improving your performance

My recommendations

I would recommend that you create a database and run allot of tests with large and small amounts of data to mimic a real life situation.
I’d recommend starting off with a modest amount of space for your transaction log (ranging from 5% to 10% of the database size). The objective is to get a picture of how big the transaction log will need to be from regular usage. Assuming there are not any drastic unusual amounts of data being passed through the system, after about 3 full backups (using your regularly scheduled intervals) with NOT shrinking the log file, you can feel fairly confident in knowing the size the transaction log needs to be.

It is common to use the log size being produced after the database has had enough regular usage data passed through plus another 10 – 20% of the transaction log size for small abnormal transactions and maintenance; such as a small number of records being archived (if archiving is done on a more regular basis, such as monthly or quarterly), additional tables are added later to hold new data requirements, or reports are being created and stored within the database. These are all concerns that are best addressed earlier in the development cycle; however, if the transaction log needs to be increased later that can always be accomplished by the administrator (or by SQL Server if left at defaults).
Keep track of the log file size throughout the life span of the database; if you notice it grow suddenly then look into the cause. The highest concern would be a massive amount of data being modified (either from archiving, or even worse deleting); if either of these are the case then make sure that the transaction log is not truncated. You may find that you will need this to rollback that large transaction, if this occurred during a non-planned time (such as a misuse of the DELETE or DROP commands by a database user). This is where the transaction log can literally save your job!
The last and probably most helpful recommendation I can give is to take a proactive approach to sizing your database’s transaction log file. Be generous in the amount of space you reserve for the transaction log. And most importantly if you expect (or even suspect) the log will need to be increased do this as soon as possible during off-peak hours; this will have the least amount of impact on your system and will result in the optimal performance of the system (while avoiding any unexpected performance degrading that might occur during peak hours!); remember that the less resources you utilize means the more resources that can be given to help your queries and reports!

Ok so now you are thinking how do I shrink the log down here is how you do it.

firstly you will need to kill the processes that are attached to the db.

The process for clearing this is as follows

1) dbcc opentran(database) - this will show the oldest open transaction

2) Kill this process

3) backup log database with no_log

4) use database

5) dbcc shrinkfile(database,100) - shrinks the log back to 100mb

additional resources if needed
TechNet: Working with Transaction Log Backups - http://technet.microsoft.com/en-us/library/ms190440.aspxTechNet: Restoring a Database to a Point Within a Backup - http://technet.microsoft.com/en-us/library/ms190244.aspxTechNet: Recovering to a Log Sequence Number (LSN) - http://technet.microsoft.com/en-us/library/ms191459.aspxTechNet: Best Practices for Recovering a Database to a Specific Recovery Point - http://technet.microsoft.com/en-us/library/ms191468.aspxTechNet: Security Considerations for Backup and Restore - http://technet.microsoft.com/en-us/library/ms190964.aspxMSDN: DBCC SHRINKFILE (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms189493.aspxMSDN: DBCC SQLPERF (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms189768.aspx

SQL with Powershell

2009-01-07T17:03:00.000-08:00

In this blog I will show you how you can connect to all your instances of sql within powershell.

Powershell is nice and seems to be popular these days. So I wrote a handy little tool to connect to instances through it. I think it is much quicker and easier to use this tool rather than remote desktop or open enterprise manager or sql server manager if you are just checking a server.

here is the code

$file = get-content "file.txt"
# checks file if not found then terminates
if (-not($file)) { write-error($file+ " not found. Terminating .... "); exit}# matches the string ie type in any string for yout instance
$match = $file Select-String (Read-Host "search string") # if the match is found then this will run the select if ($match.count -gt 0) { $inst = @() for ($i = 0; $i -lt $match.count; $i++) { $temp = "" $line = $match[$i].tostring() for ($j = $line.length-1; $j -ge 0; $j--) { if ($line[$j] -eq ":") { break } $temp = $line[$j] + $temp } $inst = $inst + $temp write-output ("Press "+($i+1)+" for : " + $inst[$i]) } $inp = "" $flag = "true" do { $inp = read-host("Enter choice : 1 - "+$match.count) if ($inp -lt 1 -or $inp -gt $match.count) { $flag = "false"; write-error ("Invalid entry. Enter between 1 - "+$match.count) } else { $flag = "true"; } } while ($flag -eq "false") write-output ("Logging on to the SQLSERVER you requested: "+$inst[$inp-1]) if ($inst[$inp-1] -ne "") { $inst[$inp-1] + ""; sqlcmd -S $inst[$inp-1] -E } }

Instructions

This Powershell script well allow you to search for any instance or sql server you have or have enterned in to the file. once you run this powershell script it will ask you for a string type in what ever instance you want it mssql then it will give you a number of instances you can then press 1 to what ever number the server is on and it will log you in.

PS C:\CheckSQLServer\sql> ./loginsql.ps1search string: IBTKYPSP32Press 1 for : IBTKYPSP32A006Press 2 for : IBTKYPSP32A017Press 3 for : IBTKYPSP32A021Press 4 for : IBTKYPSP32A026Press 5 for : IBTKYPSP32A028Press 6 for : IBTKYPSP32A031Press 7 for : IBTKYPSP32A100Press 8 for : IBTKYPSP32B017Press 9 for : IBTKYPSP32B026Press 10 for : IBTKYPSP32B031Press 11 for : IBTKYPSP32A034Press 12 for : IBTKYPSP32B034\HOSTED_P1Press 13 for : IBTKYPSP32A036\HOSTED_P1Press 14 for : IBTKYPSP32B037\HOSTED_P1Enter choice : 1 - 14: 14Logging on to the SQLSERVER you requested: IBTKYPSP32B037\HOSTED_P1IBTKYPSP32B037\HOSTED_P11> sp_who2> gospid ecid status loginamehostnamecmd requ------ ------ ------------------------------ -------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------- ----1 0 background sa
RESOURCE MONITOR2 0 background sa
RESOURCE MONITOR3 0 background sa
RESOURCE MONITOR4 0 background sa
RESOURCE MONITOR5 0 background sa