Tuesday, November 27, 2012

DTS Host Failed Error



Error message when you try to modify a SQL Server 2000 DTS package in SQL Server 2005: "The DTS host failed to load or save the package properly"

Method 1

Warning The following information contains steps to modify a file. Make sure that you back up the file before you modify it.
Open the SQLInstall:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE folder.
Note SQLInstall is a placeholder for the drive where SQL Server 2005 is installed.
Open the Sqlwb.exe.manifest file in Notepad or in another text editor.
Remove the following lines of code from the Sqlwb.exe.manifest file.
<dependency>
<dependentAssembly>
<assemblyIdentity type="win32" name="Microsoft.Windows.Common-Controls" version="6.0.0.0" processorArchitecture="x86" publicKeyToken="6595b64144ccf1df" language="*"></assemblyIdentity>
</dependentAssembly>
</dependency>
Save the changes, and then exit Notepad or the text editor.
Try to modify the SQL Server 2000 DTS package in SQL Server 2005


Method 2

Install SQL Server Enterprise Manager on the computer that is running SQL Server 2005. To do this, click Client Tools Only when you install SQL Server 2000.
Open the DTS package in SQL Server Enterprise Manager, and then save the DTS package. To do this, follow these steps:
Open SQL Server Enterprise Manager, and then connect to an instance of SQL Server 2000.
In SQL Server Enterprise Manager, expand the instance of SQL Server 2000, right-click the SQL Server Data Transformation Services folder, and then click Open Package.
Locate the .dts file, click Open, select the package, and then click OK to open the DTS package in SQL Server 2000 DTS Designer.
In SQL Server 2000 DTS Designer, click Save on the Package menu.
Modify the SQL Server 2000 DTS package in SQL Server 2005
Method 3
Uninstall Backward Compatibility components
Uninstall DTS Designer Components
Reistall it again

Wednesday, November 21, 2012

SQL Server Frequently Used Procedures and Functions



Use DataBase
Go
Sp_helpuser   -- Displays complete user details along with DB roles of the current database

Object_Name (‘Object_Id’) – Returns name of the object

DB_Name (‘DBID’) – Returns name of the database

Schema_Name(‘Schema_ID’) – Returns name of the schema

Select @@servername -- Returns ServerName

Sp_helpdb <database_name> -- Returns information about the given database.

Sp_helpserver – Displays current server details

SELECT SERVERPROPERTY('Edition') – Displays current SQL Server edition

SELECT SERVERPROPERTY(‘ProductLevel') – Displays current SQL Server Product Level

SELECT SERVERPROPERTY(‘ProductVersion') – Displays current SQL Server Product Version

SELECT SERVERPROPERTY(‘ServerName') – Displays current SQL Server Product Version

SELECT 'kill ' + CAST(spid AS VARCHAR) + '' from sys.sysprocesses where db_name(dbid)
='msdb'  -- Kill connections for a particular database.

Alter Database database_name set single_user with rollback immediate – To keep database in a
single user mode.

Alter Database database_name set multi_user  -- To keep database in multi user mode.

Use <db_name>
go
Dbcc open_tran  -- Displays are there any open transactions in the current database.

Select * from sys.sysprocesses where open_tran>0 – displays open transactions from all the databases on the server.

Sp_who 2  -- Displays list of processes that are currently present in the server.

To rename SQL Server
Sp_dropserver ‘servername’
Sp_addserver ‘newname’,local
Restart the sql service
To know the dependencies of an object
SELECT * FROM sys.dm_sql_referenced_entities('dbo.uspGetManagerEmployees','object')

Sp_depends ‘stored_proc’ – It doesn’t give accurate information and it also deprecated.

 

Tuesday, November 20, 2012

Database diagram support objects cannot be installed

Database diagram support objects cannot be installed

To fix the issue
Use databasename
go
Alter Authorization on database::database_name to login_name

Alter Authorization on database::kalyandb to kalyan_ro

Friday, November 16, 2012

SQL Server Data Pages, Extents etc


The fundamental unit of data storage in SQL Server is pages. All the data that stored inside the SQL Server is on data pages. The size of data page is 8Kb. Data is stored in a data file with extension .mdf.  

A page is an 8kb (8192 bytes) and 132 bytes are used for management and 8060 is the actual data page

1. Page Header (96 bytes)
2. Page Data
3. RowOffset –(36 bytes) It contains address of all records that are stored in data page.

The disk space is logically divided into pages and the pages are numbered contiguously from 0 to n. Disk Input and output operations are performed at page level.

Pages are grouped together into extents. An extent is a collection of 8 physical contiguous pages. An extent size is 64 kb. Data rows cannot span across pages.

There are different types of pages
Data Page -- Data rows with all data, except text, ntext, image, varchar(max), varbinary(max), and xml data.
Index Page -- Index Entries
Text / Image -- Large object data types : text, ntext, image, varchar(max), xml etc.,
Variable length columns when data row exceeds 8k ie., varchar, nvarchar, varbinary and sql_variant
Global Allocation Map, Shared Allocation Map -- Information whether extents are allocated.
Page Free Space -- Information about page allocation and free space available on pages.
Index Allocation Map – Information about extents used by a table or index per allocation unit.
Bulk Changed Map – Information about extents modified by bulk operations since the last Backup log statement per allocation unit.
Differential Changed Map – Information about extents that have changed since the last backup database statement per allocation unit.

Rows cannot span across pages, the maximum amount of data stored in a row is 8060 bytes, when a total row size exceeds 8060 bytes, SQL Server will dynamically move large size columns into ROW_OVERFLOW_DATA allocation unit of other page and maintains a 24 byte pointer on a original page in the IN_ROW_DATA allocation unit. This is done whenever Insert or update operations increase size of row beyond 8060 bytes. SQL Server will dynamically move columns back to data pages when size decreases.

Extents
An extent is 8 physically contiguous pages. There are two types of extents a) Uniform Extent and b) Mixed Extents

Uniform Extents are owned by a single object.
Mixed Extents are owned and used by different objects.

GAM & SGAM allocation maps are used for extent allocation.
GAM (Global Allocation Map) – It uses 1 bit to tell whether an extent is free or not. If the bit is 1, then extent is free, if the bit is 0 then extent is allocated. Each GAM covers 64000 extents, almost 4 GB of data.
SGAM (Shared Global Allocation Map) – It uses 1 bit to tell whether a mixed extent and also have at least one free page, If the bit is 1 then it is a mixed extent and has one free page, if the bit is 0 then extent is not used as mixed extent or it is a mixed extent and all pages are being used. Each SGAM covers 64000 extents, almost 4 GB of data.

PFS (Page Free Space) – PFS records the allocation status of each page, whether the page has been allocated or the amount of free space left. The PFS has one byte for each page, recording whether the page is allocated and if so, whether it is empty or 1 to 50 percent full, 51 to 80 percent full or 96 to 100 percent full.

After an extent has been allocated to an object, the database engine uses PFS to record which pages in the extent are allocated or free. This information is used when the database engine has to allocate new page. DB Engine uses PFS to find a page with free space for newly inserted row.
A PFS is the first page after the file header in a data file (page number 1), and GAM (page number 2), and then SGAM (page number 3).


SQL Server uses two internal data structures to track extents that are modified by bulk copy operations and extents modified since last full backup.

DCM – Differential Changed Map – This tracks the extents that have changed since the last backup database statement. If the bit for an extent is 1 then it is modified, if it is 0 then not modified. This will reduces that number of pages that differential backup must scan. The length of time that a differential backup runs depends on the extents that are modified since last backup but not size of database.
BCM – Bulk Changed Map – This tracks the extents that are modified since the last backup log statement. If the bit is 1 then the extent is modified by bulk logged operation, if it is 0 then it is not modified.

Thursday, November 15, 2012

Negative SPIDs in SQL Server

How to deal with negative SPIDs in SQL Server?

Some times we found negative SPIDs causing blocking other processes in SQL Server. The source of a negative SPID is -2. So how to kill it?

KILL -2
Msg 6101, Level 16, State 1, Line 1
Process ID -2 is not a valid process ID. Choose a number between 1 and 2048

Kill wont allow you to pass that negative SPID, So how can we do this?

Run the following query
select req_transactionUOW from master..syslockinfo where req_spid = -2

This will return a 24 character GUID (Unit of work ID UOW) number, something like ‘EDF78022-8990-5757-E147-FA85B21C3654’

Kill ‘EDF78022-8990-5757-E147-FA85B21C3654’
After that run sp_who2 that negative spid has disappeared.

What actually that negative SPID is?
 
That is a distributed transaction SPID. An orphan distributed transaction SPID that involves Microsoft Distributed Transaction coordinator. In a nutshell a distributed transaction that involves more than one database located on different servers.
 
In order to maintain transactional consistency these transactions needs to be coordinated, as the transaction involves two phase commit, first it needs to commit on one database and then commit the transaction on second database, if it commit at first database and fails to commit at second database then whole transaction is rolled back and no data gets committed by any database. The process is responsible for coordinating these transactions is Microsoft Distributed Transaction Coordinator or MSDTC
 
The Negative SPID problem arises for whatever reason MSDTC loses track on one of these transactions, then that transaction will marked as in-doubt. If this happens, SQL Server will lock the SPID bound to that transaction but SPID will be -2.
 
This means it is no longer an internal process of SQL Server, and SQL Server doesn’t know whether it can commit or rollback so it causes blocking by holding locks on the tables that transaction is accessing. Sometimes it happens when MSDTC is in hung state or terminated.
 
 
 

Wednesday, November 14, 2012

The server principal owns one or more endpoint(s) and cannot be dropped


I encountered the below error while removing one of the domain user and fixed after executing the below command

USE master
go
Alter Authorization on Endpoint::Mirroring to sa
After tranferring ownership you can able to remove login.

Saturday, November 10, 2012

Triggers

A trigger is a special type of stored procedure that executes in response to action on a table like insert, update and delete. It is a database object bound to a table and executes automatically when event occurs. We can’t invoke triggers explicitly.


Types of Triggers
 These are classified into two categories
 After Triggers or For Triggers
 Instead of Triggers

After Triggers
These will run after completion of any DML event like Insert, Update and Delete on a table. These after triggers will work only with tables not with VIEWS. After Insert, After Update and After Delete.

Magic Tables
 There are two temporary tables INSERTED, DELETED created by database engine automatically   when Insert / Delete occurs on a table.

 Syntax
 Create trigger trigger_name on Table_Name After / For (Insert /Delete/Update) As
Begin
Statements
 End
Examples of After Insert , After Update & After Delete Triggers

CREATE TRIGGER trg_items ON items FOR INSERT as
 BEGIN
                 INSERT INTO Items_audit SELECT item_name, item_image, item_description FROM INSERTED

END

CREATE TRIGGER trg_updateitems ON items FOR UPDATE as
BEGIN
                 INSERT INTO Items_audit SELECT item_name, item_image, item_description FROM DELETED
 END 

Instead of Triggers 
These instead of triggers is used to perform any action before the event fires, we can create instead of triggers in tables and views, whereas we can create after triggers in views.

 Create trigger trigger_name on table / view instead of Insert / Update / Delete AS
 Begin
         Statements
 End

Example
 Create table tblInsert_Log (UserName varchar(250), description varchar(500), Action_Date datetime, SPID int)

Create trigger trg_trapInsert on Items instead of insert As
BEGIN
                 Insert into Items select Item_name,
                 Insert into tblInsert_Log VALUES (SUSER_NAME(),'Inserted New Record', GETDATE(), @@spid)
 END

Friday, November 9, 2012

DDL Triggers To Audit Server Level Events - SQL Server


In my earlier post I explained how to log DDL events, now we will see another example how to audit or log server level events.

CREATE TABLE DDL_ServerAudit_Log
(
       EventTime            DATETIME,
       LoginName            VARCHAR(250),
       UserName             VARCHAR(250),
       DatabaseName         VARCHAR(250),
       SchemaName           VARCHAR(250),
       ObjectName           VARCHAR(250),
       ObjectType           VARCHAR(250),
       DDLCommand           VARCHAR(MAX)
)

 

CREATE TRIGGER trg_AuditServerEvents ON ALL SERVER FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN, GRANT_SERVER  AS
DECLARE @data XML
SET @data = EVENTDATA()
INSERT INTO kalyandb..DDL_ServerAudit_Log VALUES
(       REPLACE(CONVERT(VARCHAR(50),@data.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/LoginName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/UserName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/DatabaseName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/SchemaName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/ObjectName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/ObjectType)')),
        CONVERT(VARCHAR(MAX),@data.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
)

To Verify 

USE master
CREATE LOGIN test WITH PASSWORD='Admin@123'
SELECT * FROM kalyandb..ddl_serveraudit_log


 Disable Trigger

DISABLE TRIGGER ALL ON DATABASE
GO
DISABLE TRIGGER ALL ON ALL SERVER

View triggers
select name from sys.triggers

select definition from sys.sql_modules where object_id = select object_id from sys.triggers where name = 'trigger_name')

Deleting triggers
drop trigger trigger_name on database


 

Thursday, November 8, 2012

DDL Triggers to Audit DDL Events - SQL Server

DDL Triggers are not only to restrict however we can even audit the DDL changes. In regular triggers we have two magic tables called INSERTED, DELETED which helps to track the changes when any event fires. But to track the DDL changes we have a function EVENTDATA() which helps to track all DDL changes.

Below is the example to track all the DDL changes in the current database.

CREATE TABLE DDL_Audit_Log
(
       EventTime            DATETIME,
       LoginName            VARCHAR(250),
       UserName             VARCHAR(250),
       DatabaseName         VARCHAR(250),
       SchemaName           VARCHAR(250),
       ObjectName           VARCHAR(250),
       ObjectType           VARCHAR(250),
       DDLCommand           VARCHAR(MAX)
)
ALTER TRIGGER Trg_LogDDLEvents ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS
DECLARE @data XML
SET @data= EVENTDATA()
INSERT INTO DDL_Audit_Log VALUES
(       REPLACE(CONVERT(VARCHAR(50),@data.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/LoginName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/UserName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/DatabaseName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/SchemaName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/ObjectName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/ObjectType)')),
        CONVERT(VARCHAR(MAX),@data.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
)

Wednesday, November 7, 2012

DDL Triggers - SQL Server

DDL Triggers are similar to regular triggers that fires in response to an event. Unlike DML Triggers they don’t fire in response to Update, Delete, and Insert statements on a table. They fire in response to DDL events like CREATE, ALTER, DROP etc.,
 
DDL Triggers helps to audit the database level changes or you can restrict to perform any kind of drop operation or to restrict to perform any kind of schema updates. DDL Triggers can fire in response to a DDL Event in the current database or in the current server.
 
Note : DDL Triggers do not fire in response to events that affect temporary tables.
 
The scope of the DDL Trigger is depends on the event, for example if we setup a Create Table trigger created to fire in response to a Create Table event that can be occurs in the database or in the server, if we take Create Login trigger then it will occur only at server level not database level.
 
Example 
USE kalyandb
GO
ALTER TRIGGER prevent_drop ON DATABASE FOR DROP_TABLE AS
 PRINT 'You must Disable the trigger "Prevent Drop" to drop this table'
 ROLLBACK

 

Viewing Triggers
 
SELECT * FROM SYS.triggers 

View Definition of a Trigger

SELECT DEFINITION FROM SYS.sql_modules WHERE OBJECT_ID = (SELECT OBJECT_ID FROM SYS.TRIGGERS WHERE NAME='PREVENT_DROP')
 



To Know the scope of trigger
 
SELECT type, name, parent_class_desc FROM sys.triggers
UNION
SELECT type, name, parent_class_desc FROM sys.server_triggers ;




  

Tuesday, November 6, 2012

Disable Loopback in Registry

How to disable loopback

1. Start - Run - Regedit - Ok

2. HKEY_LOCAL_MACHINE\SYSTEM\CURRENTCONTROLSET\CONTROL\LSA\

3. Right click - New - Dword - DisableLoopbackCheck - Enter

4. Double click on DisableLoopbackCheck - Click Modify

5. In value box - Type 1 - OK, Quit registry