Sugesh's BLOG

Get Details for Month for any given Date

2011-01-16T11:58:00.004-05:00

The given script below will give details for a given date as below.

1. First Day of the month along with the Weekday
2. Weekday of the Given date
3. Last day of the month along with the Weekday

Script:

DECLARE @date DATETIME
SELECT @date = GETDATE() --INPUT YOUR DATE HERE
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@date)-1),@date),101) AS DateofMonth,datename(dw,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@date)-1) ,@date),101)) as Week_Day,
'First Day of Month for Date Given' AS DayofMonth
UNION
SELECT CONVERT(VARCHAR(25),GETDATE(),101) AS DateofMonth, DAtename(dw,CONVERT(VARCHAR(25),GETDATE(),101)) as Week_Day,'Date Given' AS DayofMonth
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@date))),DATEADD(mm,1,@date)),101) ,DAtename(dw,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@date))) ,DATEADD(mm,1,@date)),101)) as Week_Day,
'Last Day of Month for Date Given'

Result:

DateofMonth Week_Day DayofMonth
01/01/2011 Saturday First Day of Month for Date Given
01/16/2011 Sunday Date Given
01/31/2011 Monday Last Day of Month for Date Given

Back to Blogging

2011-01-16T10:52:00.003-05:00

Life's changes happen so sooner than expected. It was the same period last year that I decided that I should minimize my blogging and professional network to spend more personal time as I anticipated busy personal and professional work.

It's this year that I know that I can spend few hours every week without affecting my personal or professional life, thanks to my understanding family and friends who would like to see me back to those good days. The difference was simple, attitude.

"I am too tired spending time learning, reading and writing. why not take sometime off and feel the personal life?, this was the one for last year and for this year it is "I have a understanding family, friends and people around me to help and keep me happy through my personal life, why not spend some time for the community?"

This difference made things easier for me to start writing back. You will see more and more blogging, discussions and posts from me. Keep me posted if you need anything specific.

At this moment, I would love to say sincere thanks to my understanding PARENTS and WIFE. Encouragement comes from nowhere, it comes from your heart, these are people who live there and that pushes me to move forward quicker.

If these wouldn't have happened

2010-11-08T18:35:00.002-05:00

Ten things that had changed my life dramatically.

1. A back injury when I was in 11th grade that kept my away from playing Volley Ball. If I would have continued playing, who knows I would have end up screwing my academics. With help of the injury, I made a better score in 12th Grade and got admitted to one of the best engineering colleges in Tamilnadu.

2. A playful college life that left me among millions of people searching for IT job. But, it was this place that gave me friends for life time.

3. My first job in Covansys, I was moved to Covansys Bangalore along with the entire batch of Freshers and placed in PeopleSoft project. Thanks to the management for putting me in Infrastructure Admin role. My first job was kind of Windows Admin/PeopleSoft DBA.

4. Things changed again forcing move to Quit Covansys and move to Chennai. Got a job in Hexaware, Chennai as PeopleSoft/SQL Server DBA in the internal systems Project. A role that I will remember for lifetime, it was here that I nurtured from an amateur Professional to an expert.

5. An unwanted argument that led me to decide on quitting Hexaware. Thanks to Lord, if this would not have happened I would have still been with Hexaware and would not have been what I am now.

6. Life @ CITIGROUP, a short-lived one but changed the most of my professional career. I have dreamt of being a Microsoft Valuable Professional, it was here that I made friendship with Vidhyasagar and Deepak, both of them are current MVPs to bring something out of us to become one of India's renowned SQL Server MVPs.

7. Help from my Schoolmate Vijay, who guided me to get H1B visa to USA. Again, a lifetime dream that was accomplished with some hurdles here and there.

8. My current job in ThomsonReuters. I am glad to work with expertise SQL Server team and with help of people around me here, I can assure that I would one day realize my dream of seeing me as Vice-President of Operations, IT for some company around this little world.

9. One that often disturbs me now and then, an unexpected surgery to my father that took him out of being in his usual activeness and one that keeps us bothering now and then.

10. Long-awaited one, my marriage as the famous saying goes "In the opinion of the world, marriage ends all, as it does in a comedy. The truth is precisely the opposite: it begins all". We are planning to make the beginning. At the end of road, we wish to hear "Better Half" related to us and Yes, It's "We Wish"

Thank You ALL for making SQL Data Camp @ Chennai a GRAND SUCCESS

2010-07-18T20:58:00.001-04:00

Organizing an event is a tough job and making it a great success is toughest job. I hope, Chennai SQL Server User Group did what it makes to say SQL data camp a GRAND SUCCESS,
At this moment being the chairperson of the organizing committee, I would like to thank people who were involved in bringing success to the first ever day long SQL Server event in India.

Speakers:
I would like to thank all the speakers for accepting our invitation to deliver a session.

Pinal, travelling from Malaysia to Hyderabad and to Chennai only to make for this event and delivered two excellent sessions on indexing.

Madhu, Sudhir Mahendru and Basker Krishnamoorthy(members of Data Architecture Center of Expertise (DACoE) of Cognizant Technology Solutions) - This team was impressive in making clear to audience why DATA DESIGNING is so important to any application development lifecycle.

Venkatesan Prabhu, I have to make a special mention about him. He made great changes to his vacation calendar just to make for this event and was pleasant to have him travelling from Australia at the right time.

Raymond, from Doubletake software presented one of the most exciting sessions on high availability with Doubletake. I have never seen an UG giving one full session to a sponsor and we are proud of making our sponsor a part of our team.

Sponsors:
If speakers were driving the event throughout the day, it was our sponsors you were with us from the day we planned till this last moment bringing energy and enthusiasm among us. The following are the persons who drove us through success.

Blythe Morrow, Sujatha Metha, Sanj Gaddam of PASS.
Doubletake Software
Redgate software
Sql-articles.com
Jacob Sebastian of BeyondRelational.com

My User Group Colleagues - Vidhyasagar and Deepak:
These both, I have no words to thank them; I am New York and was instructing them to do what needs to be done. And these guys have accomplished more than what I asked them. They have always been part of my community journey and this one is next stepping stone for us. We together have set a new milestone of conducting an event for SQL Server and we will make sure we will better this one in future events.

User Group Members:
This group has been one of the most active SQL server user groups around India and the members have been most energetic lot. They are the one who bring us enthusiasm and energy for us to take steps forward.

Personally, I was so proud to hear from Pinal, Vidhyasagar and Deepak that there so many attendees wanting to meet and chat with me. I apologize for not turning to be there. But I will make sure that I will meet everyone when I am in India for vacation.

Nagesh, Selva and Senthil:
These three need a special mention here. They have no association with either this User Group or SQL Server but were kindhearted enough to help us from designing the Logos, website, arranging things, coordinating with vendors, loaning their personal car for shuttle and what not. Thank You guys. Without you we would have had tough time managing things around us.

I would once again take this opportunity to Thank everyone for making this event a GRAND SUCCESS.

If I would have missed someone, please apologize me.

Thank you All.

SQL Server 2008 R2 by MAY 2010

2010-01-20T02:48:00.002-05:00

Microsoft has announced the SQL Server 2008 R2 will be released to manufacturing in May 2010. The official statement is in the link below.

http://blogs.technet.com/dataplatforminsider/archive/2010/01/19/sql-server-2008-r2-gets-an-official-date.aspx

Consolidating our blogs into one website

2009-11-28T18:26:00.002-05:00

Folks,

Me, Vidhyasagar and Deepak rangarajan, we have decided that we would be posting our all articles, reviews, scripts and suggestions in SQL-ARTICLES website moving forward. I will be moving all articles from the blog to the website. The blog will be active for next few weeks before I finally call for SHUTDOWN.

I would be happy to redirect all my users to our websites, Please find useful links below.

SQL-ARTICLES - http://www.sql-articles.com

Forums - http://www.sql-articles.com/forums

Suggestions - http://www.sql-articles.com/suggestions

Thanks for being a reader of my blog and request you to visit our site. Your constant encouragement would help us grow more efficiently.

Thanks

Sugeshkumar Rajendran

SQL Server MVP

The Unhealthy Argument

2009-10-10T19:59:00.002-04:00

This post of mine is 100 percent a personal opinion and was written not to hurt anyone.

Off late, I have been hearing or knowing incidents which have involved 2 or more persons going into an argument on deciding which technology is the best is it WINDOWS or UNIX, ORACLE or SQL SERVER, .Net or JAVA, IE or Chrome, etc. There are few incidents that came to my notice which I would like to share with you all.

1. This was between friends of mine who worked in my previous company; they both work in the same team providing support to one of their clients. One is an ORACLE DBA and other is SQL Server DBA. They have argued hardly to decide which Database systems is the best and have ended up criticizing Larry Ellison and Bill Gates.

2. The next is between the web browsers, I have read a numerous articles in the blogs and other websites saying IE is the best or Chrome is the best or Firefox is the best, etc. The same has happened between my known persons where they have ended using abusive languages. I am sorry to say this, but when people lose temper that is the end result.

3. Again, between my old colleagues, about WINDOWS or UNIX, each of them have had strong points to prove that they work on the latest technology of the server side Operating systems but failed to convince others. Thank God, NO blows or words were exchanged in the incident.

Now, let me come to say this, each technology or application is designed for a purpose and has its own pros and cons with it. Thanks to my patience in the recent past that I have not had such an Unhealthy Argument like this. In my opinion, a Database or an Operating system is chosen with the needs of the application or the system to be designed. It comes as a decision on that has to be compatible with the end application to be designed. The architect who decides on what needs to be used is not a biased person, he/she knows that he has to be committed to his company's core business values and chooses one deciding on it.

So, people going forth I request each of you not to have a discussion or argument to decide which the best is. We all work on the latest technologies of what we have now and should be proud that most of us are redefining technology that has got us in the past.

Again, this is a personal opinion and was written not to hurt anyone. Thanks for taking time to read this blog. Your comments on the same are welcome.

Back to Blogging

2009-10-01T10:43:00.002-04:00

It has been almost 40 days since my last blog. I am back to work and people can expect more SQL Server related stuff on this blog again. If you need any specific interests, Please do send me a note so that the same can be put as an article.

Many thanks for reading.

Out for Vacation

2009-08-18T01:38:00.002-04:00

For those who are trying to mail me or looking out reasons finding why I am not online in any of the messangers. The answer is here. I will be out for vacation till mid of september. I will check mails few times a week but no more than that. If i don't write back to you, Please wait. Many thanks.

Virtual TechDays: FREE Technical Training Opportunity

2009-08-13T07:14:00.002-04:00

Microsoft is Offering Free SQL Server and other products Techinical training in India. This is an online event. Please use the link below to register.

Click Here to Register

Chennai SQL Server User Group Meeting

2009-08-04T06:46:00.002-04:00

We held our first SQL Server User Group meeting successfully. The deatils of what happened on the event and the related presentations can be downloaded from the link below. Please feel free to touch base with me if you are interested to be part of this wonderful user group.

http://cssug.blogspot.com/2009/07/chennai-sql-server-user-group-meeting.html

Awarded MVP Again

2009-07-01T06:50:00.003-04:00

I am Happy to say that I have been Awarded MVP for this year too. Adding the TAG MVP to my name had always distinguished me from other technologists near me. It has been a pleasant experience and happy to be on those same lines again for another year. And it's Thanking time again for those who have made a difference in my life and for those who have been with me during my tough days this year.

Krishna Gopalan - KG, I wish to be near you to share the joy this moment. I can still hear you saying "Sugesh, I know you are the right person to do this job", whenever I heard that gave me confidence to enrich my skills as a DBA helping me to achieve near expertise.

Stephen Seth, Vidhya Sagar and Deepak - It is with these guys that I grew up enriching my SQL Server skills. Without them, I would still be a DBA not a MVP.

I would like to thank my friends Dalavai Shanmugha Sundaram, Mr&Mrs. Stephen Seth, Mr&Mrs. Arun Kamaraj, Chakravarthi Elangovan and Poornima rangaswamy for being with me during this tough days of personal and professional career.

A special mention of thanks to Rangaswamy and Rani Thilagam Rangaswamy for shouldering my responsibilities in India when I stay in USA.

And my parents and sister, I have no words to thank them. Without thier support and encouragement I would not have got this again.

Creating System Objects in SQL Server

2009-06-10T17:57:00.002-04:00

You can create an object in SQL Server and convert it to a system object just by marking the object as given below.

sp_MS_marksystemobject 'object_name'

Are You Certifiable???

2009-05-09T08:40:00.000-04:00

"www.areyoucertifiable.com"

Drive Details using TSQL

2009-04-21T11:17:00.002-04:00

SET NOCOUNT ON

DECLARE @res INT,

@objdets INT,

@drive INT,

@getdrive VARCHAR(13),

@totalsize VARCHAR(50),

@freespace VARCHAR(50),

@DriveLetter char(1)

CREATE TABLE ##Drivespacedetails

(

DriveLetter CHAR(1),

TotalSpace bigint,

FreeSpace bigint,

)

INSERT INTO ##Drivespacedetails(DriveLetter,FreeSpace) EXEC master.dbo.xp_fixeddrives

DECLARE drivedetails CURSOR FOR SELECT DriveLetter FROM ##Drivespacedetails

OPEN drivedetails

FETCH NEXT FROM drivedetails INTO @DriveLetter

WHILE (@@fetch_status <> -1)

BEGIN

SET @getdrive = 'GetDrive("' + @DriveLetter + '")'

EXEC @res = sp_OACreate 'Scripting.FileSystemObject', @objdets OUTPUT

IF @res = 0

EXEC @res = sp_OAMethod @objdets, @getdrive, @drive OUTPUT

IF @res = 0

EXEC @res = sp_OAGetProperty @drive,'TotalSize', @totalsize OUTPUT

IF @res = 0

EXEC @res = sp_OAGetProperty @drive,'FreeSpace', @freespace OUTPUT

IF @res <> 0

EXEC sp_OADestroy @drive

EXEC sp_OADestroy @objdets

SET @totalsize = (CONVERT(BIGINT,@totalsize) / 1048576 )

SET @freespace = (CONVERT(BIGINT,@freespace) / 1048576 )

update ##Drivespacedetails set TotalSpace=@totalsize,FreeSpace=@freespace where DriveLetter=@DriveLetter

FETCH NEXT FROM drivedetails INTO @DriveLetter

END

CLOSE drivedetails

DEALLOCATE drivedetails

SELECT @@servername as ServerName,DriveLetter,FreeSpace AS [FreeSpace MB], (TotalSpace - FreeSpace) AS [UsedSpace MB], TotalSpace AS [TotalSpace MB], ((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) AS [Percentage Free]

FROM ##Drivespacedetails ORDER BY [DriveLetter] ASC

DROP TABLE ##Drivespacedetails

Determine which objects exist in a particular filegroup

2009-04-16T10:04:00.000-04:00

SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]

FROM sys.indexes i

INNER JOIN sys.filegroups f

ON i.data_space_id = f.data_space_id

INNER JOIN sys.all_objects o

ON i.[object_id] = o.[object_id]

WHERE i.data_space_id = f.data_space_id --* New FileGroup*

How to find startup procedures in sql

2009-04-14T10:13:00.000-04:00

SELECT ROUTINE_NAMEFROM MASTER.INFORMATION_SCHEMA.ROUTINESWHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'ExecIsStartup') = 1

USE MASTER
GO
SELECT VALUE, VALUE_IN_USE, DESCRIPTION FROM SYS.CONFIGURATIONS WHERE NAME = 'scan for startup procs'GO

SQL Server 2008 SP1 released

2009-04-08T11:44:00.003-04:00

SQL Server 2008 SP1 has been released to user community. Please find the download details given below.

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19

Drop Indexes in a Database

2009-04-08T09:59:00.002-04:00

set nocount on

DECLARE @sql VARCHAR(2000),@DB VARCHAR(100),@count int,@TableName varchar(200),

@Index_Name varchar(200),@ConstraintName varchar(200)

SET @DB = db_name()

if exists(select * from msdb..sysobjects where name =

'DropIndexes_AllTables')

drop table msdb.dbo.DropIndexes_AllTables

CREATE TABLE msdb.dbo.DropIndexes_AllTables(tablename

varchar(200),ConstraintName varchar(200),Index_Name varchar(200),type

varchar(10))

SELECT @SQL = 'USE '+@db+char(13)+'SELECT object_name(parent_object_id)

TableName,sobj.Name ConstraintName,sidx.name Index_Name,type FROM sys.objects

sobj

join '+@db+'.dbo.sysindexes sidx on sobj.parent_object_id = sidx.id

where indid > 0 and type in

(''u'',''PK'',''UQ'') and

sidx.name not like ''_WA_sys_%'''

print @sql

insert into msdb.dbo.DropIndexes_AllTables

exec(@sql)

create table #temp_baby(ConstraintName varchar(200),TableName varchar(150))

select ConstraintName,TableName, Index_Name,Type from

msdb.dbo.DropIndexes_AllTables

insert into #temp_baby(ConstraintName,TableName)

select distinct ConstraintName,TableName from msdb.dbo.DropIndexes_AllTables

select @count = count(*) from #temp_baby

while(@count > 0)

begin

select top 1 @ConstraintName = ConstraintName,@tablename = tablename from

#temp_baby

select @sql = 'USE '+@db+char(13)+'alter table '+@tablename+' drop

constraint '+@ConstraintName+''

print @sql

delete from #temp_baby where ConstraintName = @ConstraintName and tablename =

@tablename

select @count = count(*) from #temp_baby

end

insert into #temp_baby(ConstraintName,TableName)

select distinct ConstraintName,TableName from msdb.dbo.DropIndexes_AllTables

print '------'

delete from msdb.dbo.DropIndexes_AllTables where Index_Name in (select

ConstraintName from #temp_baby)

select @count = count(*) from msdb.dbo.DropIndexes_AllTables

while(@count > 0)

Begin

select top 1 @tablename = TableName,@Index_name = Index_Name from

msdb.dbo.DropIndexes_AllTables order by tablename

select @sql = 'USE '+@DB+';drop index

'+@tablename+'.'+@Index_name+''

print @sql

--print @tablename, @Index_name

delete from msdb.dbo.DropIndexes_AllTables where TableName = @tablename and

Index_Name = @Index_name

select @count = count(*) from msdb.dbo.DropIndexes_AllTables

end

drop table #temp_baby

Change Data Capture

2009-04-07T09:59:00.003-04:00

This article introduces to a new feature in SQL Server 2008, Change Data Capture. Change Data Capture records insert, update and delete activity in your table and the details of these changes are available in a format that can be easily consumed. The database must be enabled to Capture Changes to it. A member of sysadmin fixed role can enable the change. Once that is done, members of db_owner can enable tables for which change needs to be monitored. When the first table in the database is enabled, a capture process gathers changed data from the transaction log and inserts the change information in the associated change table. Additional metadata about each transaction is inserted in a metadata table that allows the captured changes to be placed on a conventional timeline.

Step to Enable and Configure Change Data Capture:
1. Create a database SQL Server 2008
create database CDC_TEST
go
2. Enable CDC in the database created.
USE SQL2008CDC
GO
EXEC sp_cdc_enable_db
GO
You can verify if CDC is enabled using the query below for the database.
select [name] as DBName,is_cdc_enabled from sys.databases where db_name(database_id)=DB_NAME()
3. You will be a new schema CDC and few system tables created under the database. Change Data Capture requires exclusive use of cdc user and schema.
4. Create a table to enable CDC
USE CDC_TEST
GO
CREATE TABLE dbo.CDC_TEST(
ID int Primary Key NOT NULL,
Name nvarchar(100) NOT NULL,
Email nvarchar(100) NOT NULL)
5. SQL Server agent must be started for CDC.
6. Enable CD for the table where you need to capture the change.
EXEC sp_cdc_enable_table 'dbo', 'cdc_test',
@role_name = NULL, @supports_net_changes =1
You can verify if CDC is enabled using the query below for the table.
use SQL2008CDC
go
select [name], is_tracked_by_cdc from sys.tables
7. Once CDC is enabled for a table, you will find a _CT table created by itself.
8. Now we will verify how CDC works by running a insert into the table CDC_TEST.
use SQL2008CDC
go
INSERT INTO dbo.cdc_test
values (1, N'sugeshkumar', N'sugeshkr@gmail.com')
9. Now update the row that we have inserted in the previous step.
use SQL2008CDC
go
update dbo.cdc_test set name='sugesh' where id = 1
10. The before image and after image of the row can be found on the table that was created with the name _CT.
The column _$operation has the value for the DML operation done, 1 is for delete, 2 for insert, 3 for before image, 4 for after image

You have seen how to enable and configure CDC for a database and table. The list below gives you all available Stored Procedures, DMVs, Functions and table list that is used by Change Data Capture.

Stored Procedures
sys.sp_cdc_add_job (Transact-SQL)
sys.sp_cdc_generate_wrapper_function (Transact-SQL)
sys.sp_cdc_change_job (Transact-SQL)
sys.sp_cdc_get_captured_columns (Transact-SQL)
sys.sp_cdc_cleanup_change_table (Transact-SQL)
sys.sp_cdc_get_ddl_history (Transact-SQL)
sys.sp_cdc_disable_db (Transact-SQL)
sys.sp_cdc_help_change_data_capture (Transact-SQL)
sys.sp_cdc_disable_table (Transact-SQL)
sys.sp_cdc_help_jobs (Transact-SQL)
sys.sp_cdc_drop_job (Transact-SQL)
sys.sp_cdc_scan (Transact-SQL)
sys.sp_cdc_enable_db (Transact-SQL)
sys.sp_cdc_start_job (Transact-SQL)
sys.sp_cdc_enable_table (Transact-SQL)
sys.sp_cdc_stop_job (Transact-SQL)

Dynamic Management Views
sys.dm_cdc_log_scan_sessions (Transact-SQL)
sys.dm_cdc_errors (Transact-SQL)
sys.dm_repl_traninfo (Transact-SQL)

Tables
cdc._CT
Returns one row for each change made to a captured column in the associated source table.

cdc.captured_columns
Returns one row for each column tracked in a capture instance.

cdc.change_tables
Returns one row for each change table in the database.

cdc.ddl_history
Returns one row for each data definition language (DDL) change made to tables that are enabled for change data capture.

cdc.lsn_time_mapping
Returns one row for each transaction having rows in a change table. This table is used to map between log sequence number (LSN) commit values and the time the transaction committed.

cdc.index_columns
Returns one row for each index column associated with a change table.

dbo.cdc_jobs (Transact-SQL)
Returns the configuration parameters for change data capture agent jobs

Functions
cdc.fn_cdc_get_all_changes_ (Transact-SQL)
sys.fn_cdc_has_column_changed (Transact-SQL)
cdc.fn_cdc_get_net_changes_ (Transact-SQL)
sys.fn_cdc_increment_lsn (Transact-SQL)
sys.fn_cdc_decrement_lsn (Transact-SQL)
sys.fn_cdc_is_bit_set (Transact-SQL)
sys.fn_cdc_get_column_ordinal (Transact-SQL)
sys.fn_cdc_map_lsn_to_time (Transact-SQL)
sys.fn_cdc_get_max_lsn (Transact-SQL)
sys.fn_cdc_map_time_to_lsn (Transact-SQL)
sys.fn_cdc_get_min_lsn (Transact-SQL)

Generate Create Index Scripts for a Database

2009-04-06T10:07:00.002-04:00

-- Get all existing indexes, but NOT the primary keys
DECLARE cIX CURSOR FOR
SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID
FROM Sys.Indexes SI
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON SI.Name = TC.CONSTRAINT_NAME AND OBJECT_NAME(SI.Object_ID) = TC.TABLE_NAME
WHERE TC.CONSTRAINT_NAME IS NULL
AND OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1
ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID

DECLARE @IxTable SYSNAME
DECLARE @IxTableID INT
DECLARE @IxName SYSNAME
DECLARE @IxID INT

-- Loop through all indexes
OPEN cIX
FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @IXSQL NVARCHAR(4000) SET @IXSQL = ''
SET @IXSQL = 'CREATE '

-- Check if the index is unique
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)
SET @IXSQL = @IXSQL + 'UNIQUE '
-- Check if the index is clustered
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
SET @IXSQL = @IXSQL + 'CLUSTERED '

SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON ' + @IxTable + '('

-- Get all columns of the index
DECLARE cIxColumn CURSOR FOR
SELECT SC.Name
FROM Sys.Index_Columns IC
JOIN Sys.Columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID
WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID
ORDER BY IC.Index_Column_ID

DECLARE @IxColumn SYSNAME
DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1

-- Loop throug all columns of the index and append them to the CREATE statement
OPEN cIxColumn
FETCH NEXT FROM cIxColumn INTO @IxColumn
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@IxFirstColumn = 1)
SET @IxFirstColumn = 0
ELSE
SET @IXSQL = @IXSQL + ', '

SET @IXSQL = @IXSQL + @IxColumn

FETCH NEXT FROM cIxColumn INTO @IxColumn
END
CLOSE cIxColumn
DEALLOCATE cIxColumn

SET @IXSQL = @IXSQL + ')'
-- Print out the CREATE statement for the index
PRINT @IXSQL

FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
END

CLOSE cIX
DEALLOCATE cIX

-- Get all existing primary keys
DECLARE cPK CURSOR FOR
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
ORDER BY TABLE_NAME

DECLARE @PkTable SYSNAME
DECLARE @PkName SYSNAME

-- Loop through all the primary keys
OPEN cPK
FETCH NEXT FROM cPK INTO @PkTable, @PkName
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = ''
SET @PKSQL = 'ALTER TABLE ' + @PkTable + ' ADD CONSTRAINT ' + @PkName + ' PRIMARY KEY CLUSTERED ('

-- Get all columns for the current primary key
DECLARE cPKColumn CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = @PkTable AND CONSTRAINT_NAME = @PkName
ORDER BY ORDINAL_POSITION
OPEN cPKColumn

DECLARE @PkColumn SYSNAME
DECLARE @PkFirstColumn BIT SET @PkFirstColumn = 1
-- Loop through all columns and append the sql statement
FETCH NEXT FROM cPKColumn INTO @PkColumn
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@PkFirstColumn = 1)
SET @PkFirstColumn = 0
ELSE
SET @PKSQL = @PKSQL + ', '

SET @PKSQL = @PKSQL + @PkColumn

FETCH NEXT FROM cPKColumn INTO @PkColumn
END
CLOSE cPKColumn
DEALLOCATE cPKColumn

SET @PKSQL = @PKSQL + ')'
-- Print the primary key statement
PRINT @PKSQL

FETCH NEXT FROM cPK INTO @PkTable, @PkName
END
CLOSE cPK
DEALLOCATE cPK

Common SQL Server Security Issues and Solutions

2009-04-03T11:25:00.003-04:00

This article is from Paul S Randal on securing SQL Server. This is a nice article on how to secure SQL Server.

http://technet.microsoft.com/en-us/magazine/2009.05.sql.aspx

Show/Hide divs and swap images with SkinID using Javascript

2009-04-02T17:11:00.035-04:00

The following example shows 2 ways of using javascript to show/hide divs (or any elements) on the click event of an image and also to swap the image that uses a skin id. Since skins are applied on the server side, you cannot change SkinID on client side using javascript. Though this is a crude way of doing it, it worked for developing my website since this functionality is used on only one page. I am sure there are more better and easier ways to do it, but these examples can be used in a simple website that uses asp.net or HTML.

Example 1: To Show only one div at a time.

ASP.Net Code

<div id="divContent">

<h2><a href="#" id="lnk1" onclick="return toggle('div1')">What is Test?</a></h2>
<div id="div1" style="display:none">
   <p>Test Test Test Test Test Test Test
   Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test
   Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test
   Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test
   Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test
   Test Test Test Test Test</p>
   <p>Test Test Test Test Test Test Test Test Test Test Test Test Test
   Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test
   Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test
   Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test
   Test Test Test Test Test Test Test Test Test Test Test Test Test Test</p>
</div>

<h2><a href="#" id="lnk8" onclick="return toggle('div8')">What is Sample?</a></h2>
<div id="div8" style="display:none">
   <p>Sample Sample Sample Sample Sample Sample Sample Sample Sample
   Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
   Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
   Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
   Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
   Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
   Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
   Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
   Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
   Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
   Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
   Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
   Sample Sample Sample Sample Sample Sample Sample Sample Sample </p>
</div>
</div>

JavaScript Code:


function toggle(divID)
{
   var divToShow = document.getElementById(divID); 
   var divUL = document.getElementById('dvfaq');
 var divs = divUL.getElementsByTagName('DIV');
 for(var i = 0; i < divs.length; i++)
   {
     divs[i].style.display = 'none';
     if(divToShow.style.display == 'none')
     {
      divToShow.style.display = 'block';
     }
   }
   return false;
}

Example 2: To Show multiple divs at a time and click to open and close.

ASP.Net Code


<a id="lnk0" href="#" onclick="return toggle('div0', 'lnk0')">
Q: What is Test? <asp:Image ID="imgToggle" SkinID="ArrowClose"
style="display: inline" runat="server" /><asp:Image ID="Image27"
SkinID="ArrowOpen" style="display: none" runat="server" /></a>
<br />

<div id="div0" style="display:none">
<p>A: Test Test Test Test Test Test Test Test Test Test Test Test Test
Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test
Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test
Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test
Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test
Test Test Test Test Test Test </p>
</div>

<a id="lnk1" href="#" onclick="return toggle('div1', 'lnk1')">
Q: What is Sample? <asp:Image ID="Image1" SkinID="ArrowClose"
style="display: inline" runat="server" /><asp:Image ID="Image28"
SkinID="ArrowOpen" style="display: none" runat="server" /></a>
<br />

<div id="div1" style="display:none">
<p> Sample Sample Sample Sample Sample Sample Sample Sample Sample
Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
Sample Sample Sample Sample Sample Sample Sample
Sample Sample Sample Sample </p>
</div>

JavaScript Code:


function toggle(divID, imgID)
{
var divToShow = document.getElementById(divID);
if(divToShow.style.display == 'none')
{
  divToShow.style.display = 'block';
}
else if(divToShow.style.display == 'block')
{
  divToShow.style.display = 'none';
}

var imgToShow = document.getElementById(imgID).getElementsByTagName('img')[0];
var imgToHide = document.getElementById(imgID).getElementsByTagName('img')[1];

if(imgToShow.style.display == 'none')
{   
imgToShow.style.display = 'inline';
imgToHide.style.display = 'none';
}
else if(imgToShow.style.display == 'inline')
{   
imgToShow.style.display = 'none';   
imgToHide.style.display = 'inline';
}
return false;
}

Skin Code:


<asp:Image runat="server" SkinID="ArrowClose" ImageUrl="images/arrowClose.gif"
width="9" height="9" />

<asp:Image runat="server" SkinID="ArrowOpen" ImageUrl="images/arrowOpen.gif"
width="9" height="9" />

Migrating Access Database to SQL Server

2009-04-01T11:07:00.004-04:00

I recently received a call from one of my friend asking me, what is the easiest way to migrate a database from ACCESS to SQL Server? I was curious on answering his question and just went for a search in internet to find it was much simple than what I expected. This article describes the steps to migrate a Access database to SQL Server.

To Migrate Access to SQL Server database, download the tool SQL Server Migration Assistant for Access

Install SQL Server Migration Assistant for Access on local computer

You need to have the access database closed while migrating to SQL Server.

Goto Start->;Programs->;Microsoft SQL Server Migration Assistant 2008 for Access->;Microsoft SQL Server Migration Assistant 2008 for Access from the computer where it's installed. You will see the welcome screen.

Click Next, Create a new project by entering the Name of the project and it's location. Click Next, You will be taken to a screen to add the Access database. Click on Add Databases button to specify the folder where your access database resides.
Click Next, Here you will have option to migrate only data and/or the queries too. Here, we are migrating both data and the queries.

Click Next, You will see the SQL Server connection Screen. Here specify the SQL Server instance where you want to move your Access database along with the destination Database name and credentials.

Click Next to Link tables. If you want to use your existing Access applications with SQL Server, you can link your original Access tables to the migrated SQL Server tables. Linking modifies your Access database so that your queries, forms, reports, and data access pages use the data in the SQL Server database instead of the data in your Access database.

Click Next to start Migration.

You will see the objects and queries to be migrated. Here If there are any errors or discrepencies will also notified.
Click Ok to complete migration.

Now, you can open your SQL Server database to see that the Access tables and Queries are migrated. The Queries will be migrated as Views and you can use them as you did in the Access tables. The migration task is simple than expected. Now the next task that you will learn from me on this is HOW TO MIGRATE ACCESS REPORTS TO SQL SERVER?

Database Size Info

2009-03-28T12:31:00.002-04:00

declare @dbname sysname

set nocount on

if @dbname is not null and @dbname not in (select name from sysdatabases (nolock))

begin

raiserror('Incorrect database name. ', 16, 1)

end

create table #datafilestats

( dbname varchar(25),

flag bit default 0,

Fileid tinyint,

[FileGroup] tinyint,

TotalExtents dec (8, 1),

UsedExtents dec (8, 1),

[Name] varchar(50),

[FileName] sysname )

declare @string sysname, @dbname1 sysname

set @string = ''

if @dbname is not null

set @dbname1 = @dbname

else

set @dbname1 = ''

set @dbname = ''

while 1=1

begin

select top 1 @dbname = name from master..sysdatabases where name > @dbname --order by name asc

if @@rowcount = 0

break

set @string = 'use ' + @dbname + ' DBCC SHOWFILESTATS with no_infomsgs'

insert into #datafilestats (Fileid, [FileGroup] , TotalExtents , UsedExtents , [Name] , [FileName]) exec (@string)

update #datafilestats set dbname = @dbname, flag = 1 where flag = 0

update #datafilestats set TotalExtents = (select sum(TotalExtents)*8*8192.0/1048576.0 from #datafilestats where dbname = @dbname)

where flag = 1 and Fileid = 1 and FileGroup = 1 and dbname = @dbname

update #datafilestats set UsedExtents = (select sum(UsedExtents)*8*8192.0/1048576.0 from #datafilestats where dbname = @dbname)

where flag = 1 and Fileid = 1 and FileGroup = 1 and dbname = @dbname

end

create table #sizeinfo

( db_name varchar(30) not null primary key clustered,

total dec (7, 1),

data dec (7, 1),

data_used dec (7, 1),

[data (%)] dec (7, 1),

data_free dec (7, 1),

[data_free (%)] dec (7, 1),

log dec (7, 1),

log_used dec (7, 1),

[log (%)] dec (7, 1),

log_free dec (7, 1),

[log_free (%)] dec (7, 1),

status dec (7, 1) )

insert #sizeinfo (db_name, log, [log (%)], status ) exec ('dbcc sqlperf(logspace) with no_infomsgs')

update #sizeinfo set

data = d.TotalExtents from #datafilestats d join #sizeinfo s on d.dbname = s.db_name where d.flag = 1 and d.Fileid = 1 and d.FileGroup = 1

update #sizeinfo set

data_used = d.UsedExtents from #datafilestats d join #sizeinfo s on d.dbname = s.db_name where d.flag = 1 and d.Fileid = 1 and d.FileGroup = 1

update #sizeinfo set

total = (data + log)

update #sizeinfo set

[data (%)] = (data_used * 100.0 / data)

update #sizeinfo set

data_free = (data - data_used)

update #sizeinfo set

[data_free (%)] = (100 - [data (%)])

update #sizeinfo set

log_used = (log * [log (%)] / 100.0)

update #sizeinfo set

log_free = (log - log_used)

update #sizeinfo set

[log_free (%)] = (log_free * 100.0 / log)

print ''

if @dbname1 = ''

begin

print 'Database size report on ' + @@servername + ' as of ' + convert(varchar(30), getdate(), 100) + char(10)

select db_name,

total,

data,

data_used,

[data (%)],

data_free,

[data_free (%)],

log,

log_used,

[log (%)],

log_free,

[log_free (%)]

from #sizeinfo order by db_name asc compute sum(total)

end

else

begin

print 'Database size report on ' + @@servername + '.' + @dbname1 + ' as of ' + convert(varchar(30), getdate(), 100) + char(10)

select db_name,

total,

data,

data_used,

[data (%)],

data_free,

[data_free (%)],

log,

log_used,

[log (%)],

log_free,

[log_free (%)]

from #sizeinfo where db_name = @dbname1

end

drop table #datafilestats

drop table #sizeinfo

Job Schedule Information for SQL Server 2005

2009-03-26T10:31:00.001-04:00

SELECT

@@servername as [Server], msdb.dbo.sysjobs.name as [Name]

, CASE

WHEN msdb.dbo.sysjobs.enabled = 0 THEN 'Disabled'

WHEN msdb.dbo.sysjobs.job_id IS NULL THEN 'Unscheduled'

WHEN msdb.dbo.sysschedules.freq_type = 0x1 -- OneTime

THEN

'Once on '

+ CONVERT(

CHAR(10)

, CAST( CAST( msdb.dbo.sysschedules.active_start_date AS VARCHAR ) AS DATETIME )

, 102 -- yyyy.mm.dd

)

WHEN msdb.dbo.sysschedules.freq_type = 0x4 -- Daily

THEN 'Daily'

WHEN msdb.dbo.sysschedules.freq_type = 0x8 -- weekly

THEN

CASE

WHEN msdb.dbo.sysschedules.freq_recurrence_factor = 1

THEN 'Weekly on '

WHEN msdb.dbo.sysschedules.freq_recurrence_factor > 1

THEN 'Every '

+ CAST( msdb.dbo.sysschedules.freq_recurrence_factor AS VARCHAR )

+ ' weeks on '

END

+ LEFT(

CASE WHEN msdb.dbo.sysschedules.freq_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END

+ CASE WHEN msdb.dbo.sysschedules.freq_interval & 2 = 2 THEN 'Monday, ' ELSE '' END

+ CASE WHEN msdb.dbo.sysschedules.freq_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END

+ CASE WHEN msdb.dbo.sysschedules.freq_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END

+ CASE WHEN msdb.dbo.sysschedules.freq_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END

+ CASE WHEN msdb.dbo.sysschedules.freq_interval & 32 = 32 THEN 'Friday, ' ELSE '' END

+ CASE WHEN msdb.dbo.sysschedules.freq_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END

, LEN(

CASE WHEN msdb.dbo.sysschedules.freq_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END

+ CASE WHEN msdb.dbo.sysschedules.freq_interval & 2 = 2 THEN 'Monday, ' ELSE '' END

+ CASE WHEN msdb.dbo.sysschedules.freq_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END

+ CASE WHEN msdb.dbo.sysschedules.freq_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END

+ CASE WHEN msdb.dbo.sysschedules.freq_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END

+ CASE WHEN msdb.dbo.sysschedules.freq_interval & 32 = 32 THEN 'Friday, ' ELSE '' END

+ CASE WHEN msdb.dbo.sysschedules.freq_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END

) - 1 -- LEN() ignores trailing spaces

)

WHEN msdb.dbo.sysschedules.freq_type = 0x10 -- monthly

THEN

CASE

WHEN msdb.dbo.sysschedules.freq_recurrence_factor = 1

THEN 'Monthly on the '

WHEN msdb.dbo.sysschedules.freq_recurrence_factor > 1

THEN 'Every '

+ CAST( msdb.dbo.sysschedules.freq_recurrence_factor AS VARCHAR )

+ ' months on the '

END

+ CAST( msdb.dbo.sysschedules.freq_interval AS VARCHAR )

+ CASE

WHEN msdb.dbo.sysschedules.freq_interval IN ( 1, 21, 31 ) THEN 'st'

WHEN msdb.dbo.sysschedules.freq_interval IN ( 2, 22 ) THEN 'nd'

WHEN msdb.dbo.sysschedules.freq_interval IN ( 3, 23 ) THEN 'rd'

ELSE 'th'

END

WHEN msdb.dbo.sysschedules.freq_type = 0x20 -- monthly relative

THEN

CASE

WHEN msdb.dbo.sysschedules.freq_recurrence_factor = 1

THEN 'Monthly on the '

WHEN msdb.dbo.sysschedules.freq_recurrence_factor > 1

THEN 'Every '

+ CAST( msdb.dbo.sysschedules.freq_recurrence_factor AS VARCHAR )

+ ' months on the '

END

+ CASE msdb.dbo.sysschedules.freq_relative_interval

WHEN 0x01 THEN 'first '

WHEN 0x02 THEN 'second '

WHEN 0x04 THEN 'third '

WHEN 0x08 THEN 'fourth '

WHEN 0x10 THEN 'last '

END

+ CASE msdb.dbo.sysschedules.freq_interval

WHEN 1 THEN 'Sunday'

WHEN 2 THEN 'Monday'

WHEN 3 THEN 'Tuesday'

WHEN 4 THEN 'Wednesday'

WHEN 5 THEN 'Thursday'

WHEN 6 THEN 'Friday'

WHEN 7 THEN 'Saturday'

WHEN 8 THEN 'day'

WHEN 9 THEN 'week day'

WHEN 10 THEN 'weekend day'

END

WHEN msdb.dbo.sysschedules.freq_type = 0x40

THEN 'Automatically starts when SQLServerAgent starts.'

WHEN msdb.dbo.sysschedules.freq_type = 0x80

THEN 'Starts whenever the CPUs become idle'

ELSE ''

END

+ CASE

WHEN msdb.dbo.sysjobs.enabled = 0 THEN ''

WHEN msdb.dbo.sysjobs.job_id IS NULL THEN ''

WHEN msdb.dbo.sysschedules.freq_subday_type = 0x1 OR msdb.dbo.sysschedules.freq_type = 0x1

THEN ' at '

+ CASE

WHEN msdb.dbo.sysschedules.active_start_time = 0 THEN '12:00'

WHEN msdb.dbo.sysschedules.active_start_time = 120000 THEN '12:00'

WHEN msdb.dbo.sysschedules.active_start_time <>

THEN STUFF(

LEFT( CAST ( msdb.dbo.sysschedules.active_start_time AS VARCHAR ), 3 )

, 2

, 0

, ':'

)

WHEN msdb.dbo.sysschedules.active_start_time <>

THEN STUFF(

LEFT( CAST ( msdb.dbo.sysschedules.active_start_time AS VARCHAR ), 4 )

, 3

, 0

, ':'

)

WHEN msdb.dbo.sysschedules.active_start_time <>

THEN STUFF(

LEFT( CAST ( msdb.dbo.sysschedules.active_start_time - 120000 AS VARCHAR ), 3 )

, 2

, 0

, ':'

)

ELSE STUFF(

LEFT( CAST ( msdb.dbo.sysschedules.active_start_time - 120000 AS VARCHAR ), 4 )

, 3

, 0

, ':'

)

END

+ CASE

WHEN msdb.dbo.sysschedules.active_start_time <>

ELSE ' PM'

END

WHEN msdb.dbo.sysschedules.freq_subday_type IN ( 0x2, 0x4, 0x8 )

THEN ' every '

+ CAST( msdb.dbo.sysschedules.freq_subday_interval AS VARCHAR )

+ CASE freq_subday_type

WHEN 0x2 THEN ' second'

WHEN 0x4 THEN ' minute'

WHEN 0x8 THEN ' hour'

END

+ CASE

WHEN msdb.dbo.sysschedules.freq_subday_interval > 1 THEN 's'

END

ELSE ''

END

+ CASE

WHEN msdb.dbo.sysjobs.enabled = 0 THEN ''

WHEN msdb.dbo.sysjobs.job_id IS NULL THEN ''

WHEN msdb.dbo.sysschedules.freq_subday_type IN ( 0x2, 0x4, 0x8 )

THEN ' between '

+ CASE

WHEN msdb.dbo.sysschedules.active_start_time = 0 THEN '12:00'

WHEN msdb.dbo.sysschedules.active_start_time = 120000 THEN '12:00'

WHEN msdb.dbo.sysschedules.active_start_time <>

THEN STUFF(

LEFT( CAST ( msdb.dbo.sysschedules.active_start_time AS VARCHAR ), 3 )

, 2

, 0

, ':'

)

WHEN msdb.dbo.sysschedules.active_start_time <>

THEN STUFF(

LEFT( CAST ( msdb.dbo.sysschedules.active_start_time AS VARCHAR ), 4 )

, 3

, 0

, ':'

)

WHEN msdb.dbo.sysschedules.active_start_time <>

THEN STUFF(

LEFT( CAST ( msdb.dbo.sysschedules.active_start_time - 120000 AS VARCHAR ), 3 )

, 2

, 0

, ':'

)

ELSE STUFF(

LEFT( CAST ( msdb.dbo.sysschedules.active_start_time - 120000 AS VARCHAR ), 4 )

, 3

, 0

, ':'

)

END

+ CASE

WHEN msdb.dbo.sysschedules.active_start_time <>

ELSE ' PM'

END

+ ' and '

+ CASE

WHEN msdb.dbo.sysschedules.active_end_time = 0 THEN '12:00'

WHEN msdb.dbo.sysschedules.active_end_time = 120000 THEN '12:00'

WHEN msdb.dbo.sysschedules.active_end_time <>

THEN STUFF(

LEFT( CAST ( msdb.dbo.sysschedules.active_end_time AS VARCHAR ), 3 )

, 2

, 0

, ':'

)

WHEN msdb.dbo.sysschedules.active_end_time <>

THEN STUFF(

LEFT( CAST ( msdb.dbo.sysschedules.active_end_time AS VARCHAR ), 4 )

, 3

, 0

, ':'

)

WHEN msdb.dbo.sysschedules.active_end_time <>

THEN STUFF(

LEFT( CAST ( msdb.dbo.sysschedules.active_end_time - 120000 AS VARCHAR ), 3 )

, 2

, 0

, ':'

)

ELSE STUFF(

LEFT( CAST ( msdb.dbo.sysschedules.active_end_time - 120000 AS VARCHAR ), 4 )

, 3

, 0

, ':'

)

END

+ CASE

WHEN msdb.dbo.sysschedules.active_end_time <>

ELSE ' PM'

END

ELSE ''

END AS Schedule

FROM msdb.dbo.sysjobs INNER JOIN

msdb.dbo.sysjobschedules ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobschedules.job_id INNER JOIN

msdb.dbo.sysschedules ON msdb.dbo.sysjobschedules.schedule_id = msdb.dbo.sysschedules.schedule_id

Database Details

2009-03-25T12:18:00.001-04:00

create table #dbdetails(name varchar(30), fileid int,filename varchar(1000), filegroup varchar(50), size varchar(30), maxsize varchar(30), growth varchar(30), usage varchar(30))

insert into #dbdetails

EXEC sp_MSforeachdb 'USE [?]; EXEC sp_helpfile;'

select * from #dbdetails

drop table #dbdetails

Job Status Script

2009-03-24T14:54:00.002-04:00

select b.name,a.step_name,

c.message,

case c.run_status when 0 then 'Failed'

when 1 then 'Succeeded'

when 2 then 'Retry'

when 3 then 'Canceled'

when 4 then 'In progress'

else 'NO STATUS' END as status

,substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),c.run_time), 6), 1, 2)

+ ':'

+ substring (

right (stuff (' ', 1, 1, '000000') + convert(varchar(6), c.run_time), 6) ,3 ,2)

+ ':'

+ substring (

right (stuff (' ', 1, 1, '000000') + convert(varchar(6),c.run_time), 6) ,5 ,2) as run_time

,substring (right (stuff (' ', 1, 1, '00000000') + convert(varchar(8),c.run_date), 8), 1, 4)

+ '/'

+ substring (

right (stuff (' ', 1, 1, '00000000') + convert(varchar(8), c.run_date), 8) ,5 ,2)

+ '/'

+ substring (

right (stuff (' ', 1, 1, '00000000') + convert(varchar(8),c.run_date), 8) ,7 ,2) as run_date

from msdb..sysjobsteps a, msdb..sysjobhistory c,msdb..sysjobs b

where a.step_id=c.step_id and a.job_id=c.job_id

and

a.last_run_date=c.run_date

and

a.last_run_time=c.run_time

and a.job_id=b.job_id

Script to Validate Subscribers for all articles and publication

2009-03-23T13:19:00.001-04:00

declare @pubdb nvarchar(50)
declare @artobj nvarchar(100)
declare @owner nvarchar(50)
declare @params nvarchar(100)
declare @cnt1 nvarchar(50)
declare @cmd nvarchar(1000)
declare @cmd1 nvarchar(1000)
DECLARE pub_validate CURSOR FOR
select publisher_db,source_owner,source_object from distribution.dbo.MSarticles
open pub_validate
fetch next from pub_validate into @pubdb,@owner,@artobj
while @@fetch_status=0
begin
select @cmd = 'select @cnt=count(*) from '+@pubdb+'.'+@owner+'.'+@artobj
select @params=N'@cnt varchar(100) OUTPUT'
exec sp_executesql @cmd,@params,@cnt=@cnt1 output
--select @cnt1
select @cmd1='use '+@pubdb+CHAR(10)+'go'+CHAR(10)+'sp_table_validation @table='+@artobj+',@expected_rowcount='+@cnt1
select @cmd1
--exec sp_executesql @cmd1
fetch next from pub_validate into @pubdb,@owner,@artobj
end
close pub_validate
deallocate pub_validate

Cross-Domain Windows Authentication for SQL Server databases

2009-03-23T11:40:00.000-04:00

There are many acquisitions these days and the DBA and sysadmins have a great tasks of doing a cross-domain authentication to their servers and databases. System admins have various options to connect interdomain where as DBA has to look up for it. Here is a solution that one can try using to access databases in cross-domain platform with windows authentication.
Consider one of the servers is in new york domain and Second is in boston domain. newyork domain can't talk to boston domain and vice versa..The idea is to make the windows user in newyork domain access database in boston domain using the same windows credentials. This can be achieved by following steps below.
1. Create a SQL login named sqluser in the secondary server and copy the sid of the login.
2. Create a login in the primary named sqluser with sid captured.
CREATE LOGIN sqluser with PASSWORD='sqluser1', SID = 0xB0B50710A1D1394D8E1B26CFA25AA3BE
--- copied from secondary server.
Assign the datareader permissions to primary server.
3. This user will be replicated to the secondary readonly db through log backup, being sid matches between the user and login named sqluser, it works.
It’s simple, no downtime required.
Windows Authentication: (user named newyork\sqluser)
It’s the same but we may have to work thru some tricky steps. Reason behind was that Windows domain account will have a unique SID value.
1. Create a windows login named newyork\sqluser in the secondary server. Copy the sid.
2. Create a login in the primary named sqluser with sid captured
CREATE LOGIN sqluser with PASSWORD='sqluser123', SID = 0x01050000000000051500000071416F0BD9618A1FC22E626EC6190003
it results with the below error..
Msg 15419, Level 16, State 1, Line 1
Supplied parameter sid should be binary(16).
3. Basic and logic helps here. Create a login named sqluser in the Primary with the datareader permissions.

CREATE LOGIN sqluser with PASSWORD='sqluser123'
4. Now the user named sqluser is replicated to the secondary readonly db but stands as a orphaned user. It’s not in sync with the windows login(newyork\sqluser).
5. whats the next option.. how do we bring the sid in sync now.
Use SQLCMD with DAC session on primary server
c:\> SQLCMD -A -d master
-A - options opens DAC(admin session)
SQLCMD>
Enable the allow updates option using sp_configure and update the sid of the sqluser login in sys.sysusers table with sid of windows login(newyork\sqluser)
This query results with an error because updates can be happened only when db is in single user mode with sql 2005.
C:\>sqlcmd -A
1> sp_configure 'allow updates'
2> go
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
allow updates 0 1 1 1
1> update sys.sysusers set sid = 0xB0B50710A1D1394D8E1B26CFA25AA3BE where name like 'sqluser'
2> Go
Msg 259, Level 16, State 1, Server servername, Line 1
Ad hoc updates to system catalogs are not allowed.
1>exit
sid can be captured in sys.sysxlgns table also which are similar to sysxlogins in SQL 2000. It can be viewed only when you open a session using DAC. In order to update the system tables we have to bring sql server in single user mode.

sqlservr -m -- helps you to bring sql server in single user mode.
Repeat step 5 but sql server should be started with single user mode.
C:\>sqlcmd -A
1> use demo;
2> go
Changed database context to 'Demo'.
1> update sys.sysusers set sid = 0xD67EDAA0627B944F997EB9C552DEE47A where name like 'sqluser';
2> go
(1 rows affected)
Warning: System table ID 27 has been updated directly in database ID 6 and cache coherence may not have been maintained. SQL Server should be restarted.
Now it works. recycle the SQL.Log backup of primary database will update the sid of the sys.sysusers in the secondary db. Now the windows user will have the datareader permissions on the table..
Now newyork\sqluser will have readonly access to secondary db

Validating Replication Subscriber using TSQL

2009-03-18T12:52:00.002-04:00

Last post from me on SQL Server replication was on How to validate subscribers in SQL Server Replication. This article shows how the same can be done using TSQL.

sp_publication_validation:

This system stored procedure does a validation for each and all articles in the specified publication in transactional replication. This stored procedure is executed against the publication database. The following is the syntax for this stored procedure.

sp_publication_validation [ @publication = ] 'publication'

[ , [ @rowcount_only = ] type_of_check_requested ]

[ , [ @full_or_fast = ] full_or_fast ]

[ , [ @shutdown_agent = ] shutdown_agent ]

[ , [ @publisher = ] 'publisher' ]

@publication is the name of the publication.

@rowcount_only is whether to return only the row count for the table.

0 for perform a SQL Server 7.0 compatible check sum

1 for perform rowcount only

2 for perform a row count and binary check sum.

@full_or_fast is used to calculate the row count.

0 does full count using count(*)

1 does a count using rows column in sysindexes table. Since this table is not updated frequently this might be wrong sometimes.

2 does a count using rows column in sysindexes table if there is a difference then it does a full count.

@shutdown_agent Specifies whether to shutdown distribution agent immediately after completing the validation.

@publisher specifies a non-sql server publisher.

You need to have sysadmin or db_owner role in the publication database to execute stored procedure.

sp_article_validation:

This system stored procedure does a validation for each article individually in the specified publication in transactional replication. This stored procedure is executed against the publication database. The following is the syntax for this stored procedure.

sp_article_validation [ @publication = ] 'publication'

[ , [ @article = ] 'article' ]

[ , [ @rowcount_only = ] type_of_check_requested ]

[ , [ @full_or_fast = ] full_or_fast ]

[ , [ @shutdown_agent = ] shutdown_agent ]

[ , [ @subscription_level = ] subscription_level ]

[ , [ @reserved = ] reserved ]

[ , [ @publisher = ] 'publisher' ]

@publication is the name of the publication in which the article exists.

@article is the name of the article to validate.

@rowcount_only specifies if only the rowcount for the table is returned.

0 for perform a rowcount and a Microsoft SQL Server 7.0 compatible checksum.

1 for perform a rowcount check only.

2 for perform a rowcount and binary checksum.

@full_or_fast is the method used to calculate the rowcount.

0 does full count using count(*)

1 does a count using rows column in sysindexes table. Since this table is not updated frequently this might be wrong sometimes.

2 does a count using rows column in sysindexes table if there is a difference then it does a full count.

@shutdown_agent Specifies whether to shutdown distribution agent immediately after completing the validation.

@subscription_level specifies whether or not the validation is picked up by a set of subscribers. If 0, validation is applied to all Subscribers. If 1, validation is only applied to a subset of the Subscribers specified by calls to sp_marksubscriptionvalidation in the current open transaction.

@reserved identified for informational purposes only.

@publisher specifies a non-Microsoft SQL Server Publisher.

You need to have SELECT ALL permissions on the source table for the article being validated

sp_table_validation:

This system stored procedure does a validation for each table individually in the specified publication in transactional replication. This stored procedure is executed against the publication database. The following is the syntax for this stored procedure.

sp_table_validation [ @table = ] 'table'

[ , [ @expected_rowcount = ] type_of_check_requested OUTPUT]

[ , [ @expected_checksum = ] expected_checksum OUTPUT]

[ , [ @rowcount_only = ] rowcount_only ]

[ , [ @owner = ] 'owner' ]

[ , [ @full_or_fast = ] full_or_fast ]

[ , [ @shutdown_agent = ] shutdown_agent ]

[ , [ @table_name = ] table_name ]

[ , [ @column_list = ] 'column_list' ]

@table is the name of the table to be validated.

@expected_rowcount specifies whether to return the expected number of rows in the table. If NULL, the actual rowcount is returned as an output parameter. If a value is provided, that value is checked against the actual rowcount to identify any differences.

@expected_checksum specifies whether to return the expected checksum for the table. If NULL, the actual checksum is returned as an output parameter. If a value is provided, that value is checked against the actual checksum to identify any differences.

@rowcount_only specifies what type of checksum or rowcount to perform.

0 for perform a rowcount and a Microsoft SQL Server 7.0 compatible checksum.

1 for perform a rowcount check only.

2 for perform a rowcount and binary checksum.

@owner is the name of the owner of the table.

@full_or_fast is the method used to calculate the rowcount.

0 does full count using count(*)

1 does a count using rows column in sysindexes table. Since this table is not updated frequently this might be wrong sometimes.

2 does a count using rows column in sysindexes table if there is a difference then it does a full count.

@shutdown_agent Specifies whether to shutdown distribution agent immediately after completing the validation.

@table_name is the table name of the view used for output messages.

@column_list is the list of columns that should be used in the checksum function.

My Interview in MVP Summit

2009-03-16T18:44:00.003-04:00

Mike Nash from c-sharpconsulting interviewed me for a short duration of 2 minutes during the MVP summit. The link for the video is given below.

http://www.c-sharpcorner.com/UploadFile/mahesh/Vs03122009175026PM/Vs.aspx

How to validate subscribers in SQL Server Replication

2009-03-16T00:21:00.003-04:00

There have been many SQL Server DBAs who would like to compare the row count between tables in replication Publisher and Subscriber. SQL Server provides an easy way of doing this and is known as validation subscribers in replication which involves doing a row count between the table that is published and the ones that are subscribed to it.

To Validate subscribers, you need to do the following.

Launch replication monitor.
Right click the publisher for which you want to validate the row count and click validate subscriptions.
Choose the subscribers that you want to validate, by default it chooses all subscribers for that publication.
You will be provided with the validation options.
Select the validation option that best suits you. It is advisable to choose, "Compute a fast row count: If differences are found, compute an actual row count"
Click ok.
Click ok.
Now you will be able to see the row count match are mismatch between the publisher and the subscriber, when you double click the subscriber. The next article would be how to perform the same validation using TSQL.

tablediff utility

2009-03-15T23:45:00.003-04:00

Tablediff is an inbuilt SQL Server utility that is used to compare data between two tables across same or different servers. This utility is invoked from command prompt and is usually found in C:\Program Files\Microsoft SQL Server\100\COM folder. The best use of this utility is exhibited when you use it to compare tables that are involved in replication. The following tasks can be performed with the utility.

A row by row comparison between a source table in an instance of Microsoft SQL Server acting as a replication Publisher and the destination table at one or more instances of SQL Server acting as replication Subscribers.
Perform a fast comparison by only comparing row counts and schema.
Perform column-level comparisons.
Generate a Transact-SQL script to fix discrepancies at the destination server to bring the source and destination tables into convergence.
Log results to an output file or into a table in the destination database.

Tablediff has various arguments that can be passed to it to get the desired results.

The parameters are discussed as follows.

-sourceserver source_server_name[\instance_name]

The name of the source server. You need to specify the source instance name to this argument.

-sourcedatabase source_database

The name of the source database.

-sourcetable source_table_name

The name of the source table being compared.

-sourceschema source_schema_name

The schema owner of the source table. By default, the table owner is dbo.

-sourcepassword source_password

The password for the login used to connect to the source server using SQL Server Authentication.

-sourceuser source_login

The login used to connect to the source server using SQL Server Authentication. If source_login is not supplied, the utility uses Windows Authentication to connect to the source server. Whenever possible, use Windows Authentication.

-sourcelocked

The source table is locked during the comparison using the TABLOCK and HOLDLOCK table hints.

-destinationserver destination_server_name[\instance_name]

The name of the destination server. You need to specify the source instance name to this argument.

-destinationdatabase subscription_database

The name of the destination database.

-destinationtable destination_table

The name of the destination table.

-destinationschema destination_schema_name

The schema owner of the destination table. By default, the table owner is dbo.

-destinationpassword destination_password

The password for the login used to connect to the destination server using SQL Server Authentication.

-destinationuser destination_login

The login used to connect to the destination server using SQL Server Authentication. If destination_login is not supplied, the utility uses Windows Authentication to the server. When possible, use Windows Authentication.

-destinationlocked

The destination table is locked during the comparison using the TABLOCK and HOLDLOCK table hints.

-b large_object_bytes

The number of bytes to compare for large object data type columns, which includes: text, ntext, image, varchar(max), nvarchar(max) andvarbinary(max). large_object_bytes defaults to the size of the column. Any data above large_object_bytes will not be compared.

-bf number_of_statements

The number of Transact-SQL statements to write to the current Transact-SQL script file when the -f option is used. When the number of Transact-SQL statements exceeds number_of_statements, a new Transact-SQL script file is created.

-c

Compare column-level differences.

-dt

Drop the result table specified by table_name, if the table already exists.

-et table_name

Specifies the name of the result table to create. If this table already exists, -DT must be used or the operation will fail.

-f [ file_name ]

Generates a Transact-SQL script to synchronize table that are being compared. You can optionally specify a name and path for the generated Transact-SQL script file.

-o output_file_name

The full name and path of the output file.

-q

Perform a fast comparison by only comparing row counts and schema.

-rc number_of_retries

Number of times that the utility retries a failed operation.

-ri retry_interval

Interval, in seconds, to wait between retries.

-strict

Source and destination schema are strictly compared.

-t connection_timeouts

Is the connection timeout period, in seconds, for connections to the source server and destination server.

Restrictions:

The source table in the comparison must contain at least one primary key, identity, or ROWGUID column. When you use the -strict option, the destination table must also have a primary key, identity, or ROWGUID column.

The Transact-SQL script generated to bring the destination table into convergence does not include the following data types:

varchar(max)
nvarchar(max)
varbinary(max)
timestamp
xml
text
ntext
image

Permissions Needed:

1. You need select permissions on the tables being compared.

2. To use the -et option, you must be a member of the db_owner fixed database role, or at least have CREATE TABLE permission in the subscription database and ALTER permission on the destination owner schema at the destination server.

3. To use the -dt option, you must be a member of the db_owner fixed database role, or at least have ALTER permission on the destination owner schema at the destination server.

4. To use the -o or -f options, you must have write permissions to the specified file directory location.

Disabling Windows Authentication in SQL Server????????

2009-03-05T17:51:00.003-05:00

I have seen lot of people asking in forums "Can I disable windows authentication in SQL Server and have only SQL Authentication?". The obvious answer that he/she is going to get is "NO". For SQL server experts, they know the reason why it cannot be disabled but when a person asks this kind of question we have to understand that he/she is in infact stage of learning SQL Server. The reason why Windows Authentication cannot be disabled is that SQL Server runs as a SERVICE in Windows Operating System and the service runs under a Windows Local/Domain account.

MVP Summit 2009 Photos

2009-03-02T11:59:00.014-05:00

I have posted the few photos that we took during the MVP Summit in my picasa. Please have a look of those photos.

http://picasaweb.google.com/sugeshkr/MVPSummit2009#

MVP Summit 2009

2009-03-02T11:58:00.000-05:00

This is my first MVP summit and it has been till now the most exciting event that i have attended on my professional journey. The event started with the registration, went on with keynotes from Toby Richards, Rich Kaplan from Microsoft and then a great networking with fellow MVPs around the globe. I have not dreamt of meeting people like Steve Jones, Andy Warren, Itzik Ben and have a discussion on the SQL Server and activities that we do to make it a great technology.
The scecond day of the event is now starting with the sessions on SQL Server and I am excited in attending all the session on SQL Server. Let me keep you updated as the event goes on. Happy Reading.

Introduction to SQL Server Trace Flags

2009-01-14T12:02:00.002-05:00

This article takes you to describing about the Trace Flags and its usage in SQL Server.

Common Terms Used:

DBCC TRACEON

DBCC TRACEOFF

DBCC TRACESTATUS

Trace flags can be enabled at session level or global level. Session level trace flags are available and active for that connection whereas Global Trace flags are available and active throughout the server.

Global Trace flag is enabled at server level at startup with a –T parameter used in it.

Session level Trace flags are enabled using DBCC TRACEON command, we can find the trace that are active using the DBCC TRACESTATUS command and can be disabled using DBCC TRACEOFF command.

Trace flag	Description
-1	Sets trace flags for all client connections, rather than for a single client connection. Because trace flags set using the -T command-line option automatically apply to all connections, this trace flag is used only when setting trace flags using DBCC TRACEON and DBCC TRACEOFF.
106	Disables line number information for syntax errors.
107	Interprets numbers with a decimal point as float instead of decimal.
205	Report when a statistics-dependent stored procedure is being recompiled as a result of AutoStat.
206	Provides backward compatibility for the setuser statement.
208	SET QUOTED IDENTIFIER ON.
242	Provides backward compatibility for correlated subqueries where non-ANSI-standard results are desired.
243	The behavior of SQL Server is now more consistent because nullability checks are made at run time and a nullability violation results in the command terminating and the batch or transaction process continuing.
244	Disables checking for allowed interim constraint violations. By default, SQL Server checks for and allows interim constraint violations. An interim constraint violation is caused by a change that removes the violation such that the constraint is met, all within a single statement and transaction. SQL Server checks for interim constraint violations for self-referencing DELETE statements, INSERT, and multirow UPDATE statements. This checking requires more work tables. With this trace flag you can disallow interim constraint violations, thus requiring fewer work tables.
257	Will invoke a print algorithm on the XML output before returning it to make the XML result more readable.
260	Prints the versioning information about extended stored procedure dlls.
302	Prints information about whether the statistics page is used, the actual selectivity (if available), and what SQL Server estimated the physical and logical I/O would be for the indexes. Trace flag 302 should be used with trace flag 310 to show the actual join ordering.
310	Prints information about join order. Index selection information is also available in a more readable format using SET SHOWPLAN_ALL, as described in the SET statement.
325	Prints information about the cost of using a nonclustered index or a sort to process an ORDER BY clause.
326	Prints information about the estimated and actual cost of sorts.
330	Enables full output when using the SET SHOWPLAN_ALL option, which gives detailed information about joins.
506	Enforces SQL-92 standards regarding null values for comparisons between variables and parameters. Any comparison of variables and parameters that contain a NULL always results in a NULL.
652	Disables read ahead for the server.
653	Disables read ahead for the current connection.
809	Limits the amount of Lazy Write activity in SQL Server 2000.
1180	Forces allocation to use free pages for text or image data and maintain efficiency of storage.
1200	Prints lock information (the process ID and type of lock requested).
1204	Returns the type of lock participating in the deadlock and the current command affect by the deadlock.
1205	Returns more detailed information about the command being executed at the time of a deadlock.
1206	Used to complement flag 1204 by displaying other locks held by deadlock parties
1609	Turns on the unpacking and checking of remote procedure call (RPC) information in Open Data Services. Used only when applications depend on the old behavior.
1704	Prints information when a temporary table is created or dropped.
1807	Allows you to configure SQL Server with network-based database files.
2505	Prevents DBCC TRACEON 208, SPID 10 errors from appearing in the error log.
2508	Disables parallel non-clustered index checking for DBCC CHECKTABLE.
2509	Used with DBCC CHECKTABLE.html to see the total count of ghost records in a table
2528	Disables parallel checking of objects by DBCC commands.
2701	Sets the @@ERROR system function to 50000 for RAISERROR messages with severity levels of 10 or less. When disabled, sets the @@ERROR system function to 0 for RAISERROR messages with severity levels of 10 or less.
3104	Causes SQL Server to bypass checking for free space.
3111	Cause LogMgr::ValidateBackedupBlock to be skipped during backup and restore operations.
3205	Disables hardware compression for tape drivers.
3222	Disables the read ahead that is used by the recovery operation during roll forward operations.
3502	Prints a message to the log at the start and end of each checkpoint.
3503	Indicates whether the checkpoint at the end of automatic recovery was skipped for a database (this applies only to read-only databases).
3602	Records all error and warning messages sent to the client.
3604	Sends trace output to the client. Used only when setting trace flags with DBCC TRACEON and DBCC TRACEOFF.
3605	Sends trace output to the error log. (If you start SQL Server from the command prompt, the output also appears on the screen.)
3607	Skips automatic recovery (at startup) for all databases.
3608	Skips automatic recovery (at startup) for all databases except the master database.
3609	Skips the creation of the tempdb database at startup. Use this trace flag if the device or devices on which tempdb resides are problematic or problems exist in the model database.
3626	Turns on tracking of the CPU data for the sysprocesses table.
3640	Eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. This is similar to the session setting of SET NOCOUNT ON, but when set as a trace flag, every client session is handled this way.
4022	Bypasses automatically started procedures.
4030	Prints both a byte and ASCII representation of the receive buffer. Used when you want to see what queries a client is sending to SQL Server. You can use this trace flag if you experience a protection violation and want to determine which statement caused it. Typically, you can set this flag globally or use SQL Server Enterprise Manager. You can also use DBCC INPUTBUFFER.
4031	Prints both a byte and ASCII representation of the send buffers (what SQL Server sends back to the client). You can also use DBCC OUTPUTBUFFER.
4032	Traces the SQL commands coming in from the client. The output destination of the trace flag is controlled with the 3605/3604 trace flags.
7300	Retrieves extended information about any error you encounter when you execute a distributed query.
7501	Dynamic cursors are used by default on forward-only cursors. Dynamic cursors are faster than in earlier versions and no longer require unique indexes. This flag disables the dynamic cursor enhancements and reverts to version 6.0 behavior.
7502	Disables the caching of cursor plans for extended stored procedures.
7505	Enables version 6.x handling of return codes when calling dbcursorfetchex and the resulting cursor position follows the end of the cursor result set.
7525	Reverts to the SQL Server 7.0 behavior of closing nonstatic cursors regardless of the SET CURSOR_CLOSE_ON_COMMIT state in SQL Server 2000.
8202	Replicates all UPDATE commands as DELETE/INSERT pairs at the publisher.
8206	Supports stored procedure execution with a user specified owner name for SQL Server subscribers or without owner qualification for heterogeneous subscribers in SQL Server 2000.
8207	Enables singleton updates for Transactional Replication, released with SQL Server 2000 Service Pack 1.
8599	Allows you to use a savepoint within a distributed transaction.
8679	Prevents the SQL Server optimizer from using a Hash Match Team operator.
8687	Used to disable query parallelism.
8721	Dumps information into the error log when AutoStat has been run.
8783	Allows DELETE, INSERT, and UPDATE statements to honor the SET ROWCOUNT ON setting when enabled.
8816	Logs every two-digit year conversion to a four-digit year.
260	Prints versioning information about extended Stored procedured dynamic link libraries
1204	Returns the resources and types of locks participating in a deadlock and also the current command affected.
1211	Disables lock escalation based on memory pressure, or based on number of locks. The SQL Server Database Engine will not escalate row or page locks to table locks.Using this trace flag can generate excessive numbers of locks. This can slow the performance of the Database Engine, or cause 1204 errors (unable to allocate lock resource) because of insufficient memory. If both trace flag 1211 and 1224 are set, 1211 takes precedence over 1224. However, because trace flag 1211 prevents escalation in every case, even under memory pressure, we recommend that you use 1224. This helps avoid "out-of-locks" errors when many locks are being used.
1222	Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema.
1224	Disables lock escalation based on the number of locks. However, memory pressure can still activate lock escalation. The Database Engine escalates row or page locks to table (or partition) locks if the amount of memory used by lock objects exceeds one of the following conditions:Forty percent of the memory that is used by Database Engine, exclusive of memory allocation using Address Windowing Extension (AWE). This is applicable only when the locks parameter of sp_configure is set to 0.Forty percent of the lock memory that is configured by using the locks parameter of sp_configure. If both trace flag 1211 and 1224 are set, 1211 takes precedence over 1224. However, because trace flag 1211 prevents escalation in every case, even under memory pressure, we recommend that you use 1224. This helps avoid "out-of-locks" errors when many locks are being used.Lock escalation to the table- or HoBT-level granularity can also be controlled by using the LOCK_ESCALATION option of the ALTER TABLEstatement.
2528	Disables parallel checking of objects by DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE. By default, the degree of parallelism is automatically determined by the query processor. The maximum degree of parallelism is configured just like that of parallel queries. For more information, see max degree of parallelism Option.Parallel DBCC should typically be left enabled. For DBCC CHECKDB, the query processor reevaluates and automatically adjusts parallelism with each table or batch of tables checked. Sometimes, checking may start when the server is almost idle. An administrator who knows that the load will increase before checking is complete may want to manually decrease or disable parallelism.Disabling parallel checking of DBCC can cause DBCC to take much longer to complete and if DBCC is run with the TABLOCK feature enabled and parallelism set off, tables may be locked for longer periods of time.
3205	By default, if a tape drive supports hardware compression, either the DUMP or BACKUP statement uses it. With this trace flag, you can disable hardware compression for tape drivers. This is useful when you want to exchange tapes with other sites or tape drives that do not support compression.
3226	By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If you create very frequent log backups, these success messages accumulate quickly, resulting in huge error logs in which finding other messages is problematic.With this trace flag, you can suppress these log entries. This is useful if you are running frequent log backups and if none of your scripts depend on those entries.
3608	Prevents SQL Server from automatically starting and recovering any database except the master database. Databases will be started and recovered when accessed. Some features, such as snapshot isolation and read committed snapshot, might not work. Use for Moving System Databases and Moving User Databases. Do not use during normal operation.
3625	Limits the amount of information returned in error messages. For more information, see Metadata Visibility Configuration.
4616	Makes server-level metadata visible to application roles. In SQL Server, an application role cannot access metadata outside its own database because application roles are not associated with a server-level principal. This is a change of behavior from earlier versions of SQL Server. Setting this global flag disables the new restrictions, and allows for application roles to access server-level metadata.
6527	Disables generation of a memory dump on the first occurrence of an out-of-memory exception in CLR integration. By default, SQL Server generates a small memory dump on the first occurrence of an out-of-memory exception in the CLR. The behavior of the trace flag is as follows:If this is used as a startup trace flag, a memory dump is never generated. However, a memory dump may be generated if other trace flags are used.If this trace flag is enabled on a running server, a memory dump will not be automatically generated from that point on. However, if a memory dump has already been generated due to an out-of-memory exception in the CLR, this trace flag will have no effect.
7806	Enables a dedicated administrator connection (DAC) on SQL Server Express. By default, no DAC resources are reserved on SQL Server Express. For more information, see Using a Dedicated Administrator Connection.

Policy Based Management in SQL Server 2008

2009-01-04T20:29:00.003-05:00

This article discusses the new feature named Policy Based Management in SQL Server 2008. Policy Based Management is an extensive management configuration that can be used to manage servers, databases and objects. Policy Based framework provides a way to define policies that apply to servers, databases and objects. With Policy based Management framework, you can enforce consistent policies across all servers.

Terms used in Policy Based Framework:

Facet: Is a management area within policy based environment.

Condition: Defines the permitted state of one or more properties in a defined facet.

Policy: Contains a single condition that is to be enforced.

Category: Contains one or more policies that you want to enforce.

Target: Defines the servers, databases or other database objects for which the polices are to be enforced.

Listed below is the evaluation modes that are available in Policy Based Management in SQL Server 2008.

On Demand - Evaluates the policy only when you directly execute the policy.(Manual)

On Change: Prevent - When nested triggers are enabled, uses data definition language (DDL) triggers to prevent policy violations by detecting changes that violate policy and rolling them back.(Automatic)

On Change: Log Only - Evaluates a policy when a relevant change is made and logs policy violations in the event logs.(Automatic)

On Schedule - Uses SQL Server Agent jobs to periodically evaluate policies. Logs policy violations in the event logs and generates a report.(Automatic)

Policies that are executed automatically are executed under the member of a sysadmin role and hence are written to the event logs. Policies that are executed manually are executed under the context of the current user. This user needs permission of ALTER TRACE to write to event logs.

Permission Needed to Configure Policy Based Management: You need PolicyAdministratorRole role in msdb database to configure Policy Based Management Settings.

SQL Server stores policy related data in msdb database. You must backup this database after changes to conditions, policies and categories.

SQL Server 2008 has predefined policies and they are listed below.

Asymmetric Key Encryption Algorithm :
Checks whether asymmetric keys were created by using 1024-bit or stronger encryption. As a best practice, you should use RSA 1024-bit or stronger encryption to create asymmetric keys for data encryption.

Backup And Data File Location:
Checks whether database files are on devices separate from the backup files. As a best practice, you should put the database and backups on separate backup devices.

CmdExec Rights Secured
Checks an instance of SQL Server 2000 to determine whether only members of the sysadmin server role can run CmdExec and ActiveX Script job steps, which is a recommended best practice.

Data And Log File Location
Checks whether data and log files are placed on separate logical drives. As a best practice, placing the files on separate drives allows the I/O activity to occur at the same time for both the data and log files.

Database Auto Close
Checks whether the AUTO_CLOSE option is set to OFF. When AUTO_CLOSE is set to ON, this option can cause performance degradation on frequently accessed databases because of the increased overhead of opening and closing the database after each connection. AUTO_CLOSE also flushes the procedure cache after each connection.

Database Auto Shrink
Checks whether the AUTO_SHRINK database option is set to OFF. Because frequently shrinking and expanding a database can lead to fragmentation on the storage device.

Database Collation
Checks whether user-defined databases are defined by using a database collation that is the same as the collation for the master and model databases, which is a recommended best practice. Otherwise, collation conflicts can occur that might prevent code from executing.

Database Page Status
Checks for user databases that have the database status set to Suspect. The Database Engine marks a database as Suspect when it reads a database page that contains an 824 error. Error 824 indicates that a logical consistency error was detected during a read operation, and it frequently indicates data corruption caused by a faulty I/O subsystem component.

Database Page Verification
Checks whether the PAGE_VERIFY database option is set to CHECKSUM. This recommended best practice helps provide a high level of data-file integrity by forcing the Database Engine to calculate a checksum over the contents of the whole page and store the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value that is stored in the page header.

File Growth For SQL Server 2000
Checks an instance of SQL Server 2000 for data files that are 1 gigabyte or larger and are set to autogrow by a percentage instead of a fixed size. As a recommended best practice, large databases should class=SpellE>autogrow by a fixed size. Otherwise, growing a data file by a percentage can cause performance problems with SQL Server because of progressively larger growth increments.

Guest Permissions
Checks whether the guest user has permission to access a user database. As a best practice, you should revoke the guest user permission to access a database if it is not required.

Last Successful Backup Date
Checks to ensure that a database has recent backups. Scheduling regular backups protects a database against data loss.

Public Not Granted Server Permissions
Checks whether the public server role has server permissions. Every login that is created on the server is a member of the public server role and will have server permissions.

Read-Only Database Recovery Model
Checks for read-only user databases that have recovery set to Full. As a best practice, these databases should use the Simple recovery model because they aren’t frequently updated.

SQL Server 32-Bit Affinity Mask Overlap
Checks whether the 32-bit instance of SQL Server has one or more processors that are assigned to be used with both the Affinity Mask and Affinity I/O Mask options. Enabling a CPU with both the affinity mask and the affinity I/O mask can slow performance by forcing the processor to be overused.

SQL Server 64-Bit Affinity Mask Overlap
Checks whether the 64-bit instance of SQL Server has one or more processors that are assigned to be used with both the Affinity Mask and Affinity I/O Mask options. Enabling a CPU with both the affinity mask and the affinity I/O mask can slow performance by forcing the processor to be overused.

SQL Server Affinity Mask
Checks whether the Affinity Mask option is set to 0. This is the default value, which dynamically controls CPU affinity. Using the default value is a recommended best practice.

SQL Server Blocked Process Threshold
Checks the Blocked Process Threshold option, and ensures it is set to 0 (disabled) or to a value higher than or equal to 5 (seconds). Setting the Blocked Process Threshold option to a value from 1 through 4 can cause the deadlock monitor to run constantly, and this state is desirable only when you are troubleshooting.

SQL Server Default Trace
Determine whether the Default Trace option is disabled. When this option is enabled, default tracing provides information about configuration and DDL changes to the SQL Server Database Engine.

SQL Server Dynamic Locks
Checks whether the Locks option is set to 0. This is the default value, which dynamically controls locks. Using the default value is a recommended best practice.

SQL Server Lightweight Pooling
Checks whether the Lightweight Pooling option is set to 0. This is the default value, which prevents SQL Server from using lightweight pooling. Using the default value is a recommended best practice.

SQL Server Login Mode
Checks the login security configuration to ensure Windows authentication is being used. Using Windows authentication is a recommended best practice because this mode uses the Kerberos security protocol, provides support for account lockout, and supports password expiration. For Windows Server 2003 and Windows Server 2008, Windows authentication also provides password policy enforcement in terms of complexity validation for strong passwords.

SQL Server Max Degree Of Parallelism
Checks whether the Max Degree Of Parallelism (MAXDOP) option is set to a value greater than 8. Because setting this option to a value larger than 8 often causes unwanted resource consumption and performance degradation, you’ll usually want to reduce the value to 8 or less.

SQL Server Max Worker Threads For SQL Server 2005 And Above
Checks the Max Worker Threads Server option for potentially incorrect settings. Setting the Max Worker Threads option to a small value might prevent enough threads from servicing incoming client requests in a timely manner. Setting the option to a large value can waste address space, because each active thread consumes 512 KB on 32-bit servers and up to 4 MB on 64-bit servers. For instances of SQL Server 2005 and SQL Server 2008, you should set this option to 0, which allows SQL Server to automatically determine the correct number of active worker threads based on user requests.

SQL Server Network Packet Size
Determines whether the network packet size of any logged-in user is more than 8060 bytes. As a best practice, the network packet size should not exceed 8060 bytes. Otherwise, SQL Server performs different memory allocation operations, and this can cause an increase in the virtual address space that is not reserved for the buffer pool.

SQL Server Password Expiration
Checks whether password expiration is enabled for each SQL Server login. As a best practice, you should enable password expiration for all SQL Server logins using ALTER LOGIN. Additionally, if SQL Server authentication is not required in your environment, you should enable only Windows authentication.

SQL Server Password Policy
Checks whether the Enforce Password policy is enabled for each SQL Server login. As a best practice, you should enable the Enforce Password policy for all the SQL Server logins by using ALTER LOGIN.

SQL Server System Tables Updatable
Checks whether system tables for SQL Server 2000 can be updated. As a best practice, you shouldn’t allow updates to system tables.

Symmetric Key Encryption For User Databases
Checks whether encryption keys that have a length of less than 128 bytes do not use the RC2 or RC4 encryption algorithm. As a best practice, you should use AES 128 bit or larger to create symmetric keys for data encryption. If AES is not supported by your operating system, you should use 3DES encryption.

Symmetric Key For master Database
Checks for user-created symmetric keys in the master database.

Symmetric Key For System Databases
Checks for user-created symmetric keys in the msdb, model, and tempdb databases. As a best practice, you should not create symmetric keys in the system databases.

Trustworthy Database
Checks whether the dbo role for a database is assigned to the sysadmin fixed server role and the database has its trustworthy bit set to ON. As a best practice, you should turn off the trustworthy bit or revoke sysadmin permissions from the dbo database role. Otherwise, a privileged database user can elevate privileges to the sysadmin role and then create and run unsafe assemblies that could compromise the system.

Windows Event Log Cluster Disk Resource Corruption Error
Checks the system event log for EventId 1066. This error can occur when a device is malfunctioning and also as a result of SCSI host adapter configuration issues.

Windows Event Log Device Driver Control Error
Checks the system event log for EventId 11. This error can be caused by a corrupt device driver, a hardware problem, faulty cabling, or connectivity issues.

Windows Event Log Device Not Ready Error
Checks the system event log for EventId 15. This error can be caused by SCSI host adapter configuration issues or related problems.

Windows Event Log Disk Defragmentation
Checks the system event log for EventId 55. This error occurs when the Disk Defragmenter tool cannot move a particular data element and as a result Chkdsk.exe is scheduled to run.

Windows Event Log Failed I_O Request Error
Checks the system event log for EventId 50. This error is caused by a failed I/O request.

Windows Event Log I_O Delay Warning
Checks the event log for error message 833. This message indicates that SQL Server has issued a read or write request from disk, and that the request has taken longer than 15 seconds to return. You can troubleshoot this error by examining the system event log for hardware-related error messages. Look also for hardware-specific logs.

Windows Event Log I_O Error During Hard Page Fault Error
Checks the system event log for EventId 51. This error is caused by an error during a hard page fault.

Windows Event Log Read Retry Error
Checks the event log for SQL Server error message 825. This message indicates that SQL Server was unable to read data from the disk on the first try. You’ll need to check the disks, disk controllers, array cards, and disk drivers.

Windows Event Log Storage System I_O Timeout Error
Checks the system event log for EventId 9. This message indicates that an I/O time-out has occurred in the storage system.

Windows Event Log System Failure Error
Checks for the system event log for EventId 6008. This event indicates an unexpected system shutdown.

Next topic will be How to configure Policay Based management in SQL Server 2008 with illustration.

Script to fix Orphans user in all Databases

2008-12-29T13:47:00.002-05:00

SET NOCOUNT ON
create table #fix_orphanusers(dbname varchar(50),username varchar(50),userid uniqueidentifier)

insert into #fix_orphanusers
Exec SP_MSForEachDB 'select ''?'' as DBName, name AS UserName, sid AS UserSIDfrom ?..sysuserswhere issqluser = 1 and(sid is not null and sid <> 0x0)and suser_sname(sid) is nullorder by name'

declare @dbname varchar(50)
declare @username varchar(50)
declare @cmd varchar(4000)

DECLARE fixoprhans_cursor CURSOR FOR
SELECT dbname,username from #fix_orphanusers

open fixoprhans_cursor
fetch next from fixoprhans_cursor into @dbname,@username

while @@fetch_status=0
begin
select @cmd = 'use '+@dbname+char(10)+'go'+char(10)+'sp_change_users_login ''update_one'','+''''+@username+''''+','+''''+@username+''''+char(10)+'go'
exec @cmd
fetch next from fixoprhans_cursor into @dbname,@username
end
close fixoprhans_cursor
deallocate fixoprhans_cursor

drop table #fix_orphanusers

Installing SQL Server 2008

2008-12-23T11:11:00.029-05:00

This article introduces how to install SQL Server 2008 RTM. With SQL Server 2008, you have new setup architecture and various options that can be used to easily guide you through for successful installation.

1. Once you click the setup executable you will be taken to the screen shown below. This gives various options and information on SQL Server installation. You can check the SQL Server 2008 software and hardware requirements, books online, documentation, etc

2. Click next and you will be taken to the installation options, you can choose t

he type of installation to be performed, you will also have options for upgrading

from previous versions of SQL Server.

3. You can also perform edition upgrades, repair on current installation and

remove nodes from SQL server cluster using the installation setup.

4. Advanced link has options with which you can create configuration file to

perform a unattended installation and also advanced cluster configurations.

Once you click the installation and perform a new installation, it will ask for the

Processor Type information. Choose the right processor type information and the

setup executable file path.

Click next, you will be taken to SQL Server installation tasks.

Installation checks and installs setup support files if needed.

Click install in Setup Support files screen to proceed with the installation.

Specify the edition which you wish to install and enter the product key in it.

Accept license agreement and click next to proceed.

Select all the features that you would like to install in you server.

Choose the instance name if named instance or leave it as default. Also mention

details of the installation root directory, this is where SQL server binaries will be

installed

Check and review the disk space requirements and click next to proceed if

everything is fine.

Specify Services account for all SQL server services, it’s advised to use domain accounts rather than using local or system accounts.

Select the type of authentication mode for SQL Server security.

Choose the default paths for data and log files for system, user and tempdb databases.

Choose option to enable FILESTREAM (this is to be discussed as a separate topic in other blog entry)

Configure for error and reporting while installation.

Check for the installation rules and if all succeeds then click next to complete installation.

Verify the details given for the SQL server installation, if need be make changes before proceeding to next step.

Check for installation progress.

SQL Server has been installed. Hope you had a wonderful experience installing SQL Server 2008. Lets discuss other features and theories from next blog.

SQL 2005 SP3 released

2008-12-17T12:00:00.001-05:00

Microsoft has released Service pack 3 for SQL Server 2005. The downlad details are given below.

http://www.microsoft.com/downloads/details.aspx?familyid=ae7387c3-348c-4faa-8ae5-949fdfbe59c4&displaylang=en&tm

Tools, Tools and More Tools........................

2008-09-17T11:21:00.002-04:00

I have been writing articles and scripts in my blog and website frequently. But there has been a lean patch for almost a month now. The reason was i was busy thinking on building on some useful tools that DBA can use on thier routine work life. As a result of it, I have come up with my own tool set and will be launching them one by one ASAP. I am not good in programming but have taken help of my few best friends that I have in that area. So you all can expect more interesting stuff from this blog and my website sooner. And your feedbacks on the tools that we are going to develop is what we expect from you. Many thanks. Keep reading.........

Life of being a DBA

2008-08-16T14:54:00.001-04:00

There has been enough said about being a DBA in a critical production environment in many websites, blogs and interviews. But today was the day that I had a taste of it. I had to join a web meeting to do a production migration early morning around 5:45AM. And all was expected to go well and the task to be completed within 4 hours. This meant that the expected time to complete the task was 4 hours which included testing the application after migration.

Unfortunate enough, the migration went well through all the steps and I was expecting a HAPPY SATURDAY as usual until we realized that we had a problem starting one of our services. That was a simple service that should have started without any issues from all sides. But things were going the other way what seemed to be simple became one of the complex tasks to handle and was bugging me for almost 3 hours.
I had to make my mind work at rapid pace to get things faster but with no luck. At last all I had to do to bring the service up and running was one of the funniest things. I had to reboot the server to have it running. Yes the simplest MicroSoft solution got rid of what seemed to be the complex issue.

I took almost 3 hours for us to take such a decision. Now I realize that being a DBA is not the easiest tasks that I could consider moving forward. I had literally gone through 4 hours for a simple task that got mine.

Life of being a DBA is something that people should love as if it’s part of their wholesome life. I love being a DBA in any cause and still love it more, more and more to go.

Data Partitioning

2008-08-15T21:30:00.004-04:00

Introduction:
This article takes introduces you to the concept of data partitioning in SQL server 2005. It also gives you an example of how to start from the scratch and also how to partition an already existing table.

Data Partitioning:
Partitioning is a very powerful feature in SQL Server 2005. It means that you can horizontally partition the data in your table, thus deciding in which filegroup each row must be placed.
This allows you to operate on a partition even with performance critical operation, such as reindexing, without affecting the others. In addition, during restore, as soon a partition is available; all the data in that partition are available for quering, even if the restore is not yet fully completed.

Illustration of Data Partitioning:
Before proceeding with the steps that are given below, create a database called datapart in your server.

1. Create the file groups in which you are going to place the data
Alter database datapart add filegroup data_part
Alter database datapart add filegroup data_part1

2. Create the files in which you are going to place the data and assign them to the filegroups
Alter database datapart add file (name = 'part1',
filename = 'f:\sugesh\data\part1.ndf') to filegroup data_part
Alter database datapart add file (name = 'part2',
filename = 'f:\sugesh\data\part2.ndf') to filegroup data_part1

3. Create a partition function that partitions the table data in interval of 500 which means that the values below 500 will be placed in partition 1 and others in partition 2.
create partition function part(int) as range right for values (500)

4. Create a partition scheme so that you can associate the partition function to the filegroups
create partition scheme part as partition part to (data_part, data_part1)

5. Create a table to test our data partitioning
create table datapart(name varchar(10), part int) on part(part)

6. Insert values into the tables to the right and left of your partition value.
insert into datapart values('sugesh',0)
insert into datapart values('kumar',499)
insert into datapart values('sugesh',500)
insert into datapart values('sugesh',10000)

7. Select the values from the table along with the partition file id to find where the data has been placed and confirm if the data has been placed as per data partition defined.
select *, $partition.part(part) from datapart

The values 1 and 2 that you see in the last column in the figure given above is the partition id field and as you see all values below 500 are placed in partition 1 and others in partition 2.
Now, what’s up for us next? I have been asked by some of my colleagues, friends and few others that, is it possible to partition a table that already has rows in it. Yes of course, given below are the steps that I have used to partition the data that we have in a table with the data partition feature in SQL Server 2005.

1. Create a table without specifying any data partition in the table.
create table datapart1(name varchar(10), part int) on part(part)

2. Insert values into your table such that the values are split across your partitions.
insert into datapart1 values('sugesh',499)
insert into datapart1 values('sugesh',999)

4. Create a partition scheme so that you can associate the partition function to the filegroups
Create partition scheme part as partition part to (data_part, data_part1)

5. Insert all the value that you have in the table into a backup table
select * into datapart1_2007jun25 from datapart1

6. Drop and re-create the table specifying the data partitioning that needs to be used.
drop table datapart1
go
create table datapart1(name varchar(10), part int) on part(part)

7. insert the values into the table that you have created with data partitioning from the backup table that has the original values
insert into datapart1
select * from datapart1_2007jun25

8. Select the values from the table along with the partition file id to find where the data has been placed and confirm if the data has been placed as per data partition defined.
select *, $partition.part(part) from datapart1

Alternate Suggestions:
Though there are other options of using the alter table…. Split partition, alter table…..switch partition they can’t be effectively used for a table that already has large number of rows in it with clustered and non-clustered indexes. This solution of mine worked a way to get them around.

Conclusion:
I hope you have enjoyed learning the way of partitioning the data in SQL Server 2005. The simplest way to partition the data and improve the performance of reads and writes for larges tables. Your opinions, views and suggestions are always welcome.

Clustering SQL Server Integration Services

2008-08-13T12:57:00.001-04:00

Until the release of SQL Server 2005 SP2, Clustering SQL Server Integration Services was a bit complex task But Microsoft made it a very simple task with the release of Service Pack 2. Given below are the steps to be followed to cluster SSIS.

1. Install SSIS in all node of cluster.
2. Move to %Program Files%\Microsoft SQL Server\90\DTS\Binn
3. Look for the file MsDtsSrvr.ini.xml
4. Look up for the Server Name class, Change it to point to the SQL Server Virtual Name
5. Restart SQL Server Integration services

SQL Server 2008 RTM

2008-08-07T08:02:00.002-04:00

Microsoft has come up with SQL Server 2008 release. SQL Server 2008 RTM was released on Aug 6th 2008 and is available for all customers and users.

http://www.microsoft.com/Presspass/press/2008/aug08/08-06SQLServer2008PR.mspx

Unusual Backup Error in SQL Server

2008-08-05T17:44:00.000-04:00

You might be wondering what is happening in your SQL Server when you see the error message given below. Its all simple, you need not panic.
Error Number: 18272
Error Message: I/O error on backup or restore restart-checkpoint file '%1'. Operating system error %2. The statement is proceeding but is non-restartable.
This happens when SQL server is not able to find the default backup directory. SQL Server needs to create a checkpoint file when a BACKUP or RESTORE operation happens. This happens when the RESTART option is specified. WITH RESTART tells SQL Server to start from the point where the operation was last aborted. When SQL Server searches for the file and its not available then this error is thrown.
How to avoid this error:

1. Check the default backup directory for the SQL Server and see if exists, if not create it.
2. Verify if the backup directory has enough space in it.
3. Verify if SQL Server service account has permissions to read and write into that directory.

Introduction to Notification Services

2008-08-01T22:39:00.004-04:00

I have been a Core Server Side SQL Server DBA for more than half a decade. This was the first application side task that I handled from the SQL Server front. I would like to thank my friends Chakravarthy and Poornima for giving me this wonderful opportunity to work on Notification Services. I don’t claim that this document would give you all that are needed to work with Notification Services. It just gives you a brief idea of what it is and how you can create a simple notification service application.

And before going into the discussion, let me say that Notification services is removed from SQL Server 2008 and though Microsoft Supports it in SQL 2005 it comes under the Support of Reporting Services feature and is not taken as an independent feature.

Microsoft Notification Services can be used to generate notifications and send it to the users. Notifications sent can be either event driven or scheduled. All subscribers who have subscribed for the subscription will be notified on the event.

Key Terms in Notification Services:

Instance Configuration File (ICF): This file contains parameter details for the Notification service instance. This is the primary file that is used to create the notification services.

Application Definition File (ACF): This File contains details of the application settings like the events, rules, subscribers, subscriptions, notification to be generated, etc.

Both of these files will be dealt in detail in the coming sections.

Subscriber: A person or application that subscribers to receive notifications.

Subscription: It’s the information that needs to be sent to the subscribers.

Event: It’s the piece of information that has to be captured and sent to the subscribers.

Notification: Notification is the message that contains the information related to the subscription.

Generator: It governs the rule processing for the notification services.

Distributor: It governs the notification formatting and delivery. It looks for notifications on a configured interval and sends the notifications to the subscribers in the event of an occurrence of the same.

Subscription Device: A subscription device can be anything like a e-mail, Phone, Message, File, etc

Notification Service Architecture:

Below is the simple architecture of how Notification service.

1. Subscribers subscribe to events.
2. Events are captured by the application.
3. Service matches subscriptions to the events.
4. Notification service generates a notification.
5. Notification Service formats and sends the notification to the subscribers.

Detailed Architecture of Notification Services.

1. Notification Services stores subscriber and subscription information on databases that are created for it. (A Notification service creates 2 databases when services are installed, we will be covering those in detail when we create the service). We can create a subscription management application using a programming language to manage subscriber and subscriptions. This provides the better subscription management for the subscriber (end users) to add/edit/view their subscription data.
2. Using the event providers that are defined in the Definition File, NS mines the database for events and stores the event data in the application database. There are various event providers that are available in NS. This event provider monitors a directory for XML event data. Using this event provider, you can drop XML event files in the directory, and the event provider reads the XML events and submits them to the application database. Another Advantage of Event providers is they can be run by the event provider host component, or they can run independently of Notification Services which gives it more robustness.
3. SQL Queries are written to get the necessary information passed to the notification services. Then generator matches the subscriptions and the events and generates notifications. According to the need of the application the administrator can configure how frequent this generator should run. This makes the application more efficient in time sensitive notifications.
4. The distributor distributes the notification after proper formatting using the content formatter described.

Creating a Sample Notification Service Application:
We are going to create a sample notification service application once you create the application you will find the following.
1. NS$instancename Services in the services applet.
2. InstanceNameApplicationName database in SQL Server.
3. InstanceNameNSMain database in SQL Server.

Steps to be followed:
1. Create an ICF file.
2. Create an ADF File.
3. Login to SQL Server management Studio, Right click Notification Services to create a new notification services instance.
4. Browse to the click ICF file and click OK
5. If it succeeds then you will see the above service and database created in the server.
6. Right click the Notification Service Instance Name, Goto Tasks->Register.
7. Check the “Create Windows Service” giving proper credentials and click OK.
8. Confirm this succeeds.
9. Right click Instance and select enable.
10. Right click Instance and select Start.

This starts SQL Server Notification Service and your notification service application starts to collect the events defined in the ADF file. I will cover how to use Notification Service to create and send notifications to subscribers in my next article on Notification Services.

Performance Counter for SQL Server

2008-07-31T13:21:00.002-04:00

Few of my friends who wish to do a performance tuning in their servers, they asked me what are the counters that they should check that gave me an idea to prepare this document and publish across.

Disk Related Counters:
Object Name: Physical Disk
Counters:
Current Queue Length: Sustained high queue for a period of time means that the IO subsystem has a problem.

Average Queue Length: If the Average Queue Length for the disk is high more than 2. Then this indicates a potential IO subsystem problem. This would also make the disk sec/read and disk sec/write high.

Disk Sec/Read: This counter should measure not more than 15ms in normal situation. Continuous values of more than 20ms indicate that the disks have a problem.
Under these conditions, consider the following
Move the files to additional drives.
Check for proper indexing on the tables.
Check for the CPU and memory counters to find any other potential bottlenecks.

Disk Sec/Writes: This counter should measure not more than 15ms in normal situation. Continuous values of more than 20ms indicate that the disks have a problem.
Under these conditions, consider the following
Move the files to additional drives.
Check for proper indexing on the tables.
Check for the CPU and memory counters to find any other potential bottlenecks.
Placing transactional log files in separate drives to remove additional pressure on the drives

Memory/Cache:
Object Name: Memory:
Counters:
Page Faults/Sec: Page faults occur when the page that is not requested is not found in the memory. There are 2 types of page faults. Hard page faults and Soft page faults. Hard page faults require a disk access and where as Soft page faults are ones where the page is found elsewhere in the memory. High number of page faults/sec indicates that there is a problem with memory used by SQL Server. Use this counter in correlation with SQL Server:Memory Manager counters to check if there is a memory pressure.

Pages/Sec: This counter is the actual value of the Hard faults and should be used in correlation with Page Faults/Sec and SQL Server memory Manager counters to find the memory pressure.

CPU:
Object Name: Processor
Counters:
%User Time: SQL Server runs in User mode. Privilege mode is used by Operating system to access hardware and memory components. This counter should have a value more than 70%. If it has a value less than that check the %privileged time and %processor time counters to see if there is a problem with the processor.

%Privileged Time: Operating system moves thread to Privileged mode to access services. This counter should value less than 20%.

%Processor Time: This counter tells the percentage of the time that CPU is executing over a period. Ideally it should value less than 70%.

Interrupts/Sec: Interrupts/sec is the average rate, in incidents per second, at which the processor received and serviced hardware interrupts.

System:Object Name: System
Counters:
Processor Queue Length: Is the number threads that are waiting to be processed by the processor. This counter is calculated by the actual value divided by the number of processors. Ideally value should be less than 2.

SQL Server:
Object Name: Access Methods
Counters:
Forwarded Records/Sec: Number of records fetched through forwarded record pointers. Tables with NO clustered index can. If you start out with a short row, and update the row creating a wider row, the row may no longer fit on the data page. A pointer will be put in its place and the row will be forwarded to another page.
Can be avoided by:
Using default values
Using Char instead of Varchar.

Full Scans/Sec: Either table or Index in fully scanned. This could be caused because of wrong or unhelpful indexes.

Page Splits/Sec: Number of page splits occurring as the result of index pages overflowing. Normally, this is associated with leaf pages of clustered indexes and non-clustered indexes. This can be avoided by having proper fill factor.

Object Name: Memory Manager
Counters:
Memory Grants Pending: Memory is needed to process each user request. If enough memory is not available then the user request waits for memory which cause performance hitch in executing the query.
This can be avoided by the following
Adding more memory to the server
Adding memory to SQL Server
Creating proper Indexes

Object Name: Buffer Manager
Counters:

Buffer Cache Hit Ratio: Percentage of time the pages requested are already in memory. It should be ideally more than 99% and if less than 95% indicates that SQL server has no enough memory and adding more memory would be beneficial.

Checkpoints/Sec: Pages written to the disk during the Checkpoint process freeing up SQL cache. Memory pressure is indicated if this counter is more than 300 Secs.

Lazy Writes/Sec: Pages written to the disk during the LazyWriter process freeing up SQL cache. Memory pressure is indicated if this counter is more than 300 Secs.

Page Life Expectancy: This is one counter that I would love to monitor to check the memory pressure. This would give you the time in seconds the data pages reside in the SQL Server cache. If the value is low indicates the following problems.
SQL Cache is cold(Discussed in Page Faults)
Memory Problems
Missing indexes.
If Checkpoints/Sec, Lazywriter/Sec and Page life expectancy together is less than 300 seconds then it indicates the cause of memory pressure and your server needs more memory.

Object Name: Databases
Counters:
Transactions/Sec: Indicates the number of transaction that occur per second in the server.

Object Name: General StatisticsCounters:
User Connections: Number of connections made to the SLQ Server.

Object Name: Latches
Counters:
Average Latch wait Time: Latches are short term light weight synchronization object. Latches are not held for the duration of a transaction. Typical latching operations during row transfers to memory, controlling modifications to row offset table, etc.
A high value here indicates potential problems with IO subsystem or Memory.

Object Name: Locks
Counters
Average Wait Time(ms), Lock Wait time(ms) and Lock waits/Sec: All these counters are related to the locks held by the SQL Server. Transaction should be as short as possible and should hold locks only for a short time without blocking other user queries, A high value here would indicate the following.
Memory Pressure
IO Subsystem problem
Improper indexes
Improper SQL structures
Improper placement of files in the disk subsystem
Incorrect usage of SQL Isolation levels

These are the major counters that I would wish to check. Apart from this you can also check the following counters.

Object Name Counter Name
SQL Statistics Compilations/sec
SQL Statistics Recompilations/sec
SQL Statistics Batch Requests/sec
SQL Server: Buffer Manager Readahead pages/sec

SQL Server Security Bulletin

2008-07-28T09:57:00.022-04:00

Introduction:

This document analyzes the information given in the Microsoft bulletin regarding the Security patch that needs to be applied in all SQL Servers version 7 and above.

Scope of the Document:

1. Information regarding the Bulletin
2. Are we affected?
3. Deployment Plans and Guidance
4. Other points to ponder

Bulletin Information:

Bulletin URL: http://www.microsoft.com/technet/security/bulletin/MS08-040.mspx
http://support.microsoft.com/kb/941203#appliesto

Software Affected: SQL Server 7, 2000 and 2005 (32 bit and 64 bit) and (GDR and QFE releases)

The bulletin describes that a security patch has been released for known vulnerabilities in SQL Server and its components. The known vulnerabilities are listed below.

Memory Page Reuse Vulnerability - CVE-2008-0085
Convert Buffer Overrun - CVE-2008-0086
SQL Server Memory Corruption Vulnerability - CVE-2008-0107
SQL Server Buffer Overrun Vulnerability - CVE-2008-0106

Memory Page Reuse Vulnerability:

With this vulnerability, a database user with access can read other user’s data with custom applications as SQL Server does not initialize the memory pages before reallocating memory to users.

Microsoft has not received any information from customers or other users stating that their system is exploited using this vulnerability and information regarding this vulnerability was brought up through responsible disclosure.

Installing this security path creates a new system stored procedure sp_clean_db_free_space that can be used to clear the residual pages in the memory. This procedure cleans up all files in the database.

Mitigation Factor: Only users with database access can exploit this vulnerability.

Any other Workarounds:

Microsoft Suggests to have the configuration option “Common Criteria Compliance” set to 1. But enabling this option would result in performance degradation of the system and application.

Convert Buffer Overrun Vulnerability:

This vulnerability exists in SQL convert function that allows authenticated user to gain elevated control over the system.

Microsoft has not received any information from customers or other users stating that their system is exploited using this vulnerability and information regarding this vulnerability was brought up through responsible disclosure.

Mitigation Factor: Only users with database access can exploit this vulnerability.

Any other Workarounds: None

SQL Server Memory Corruption Vulnerability:

This vulnerability exists in SQL server that allows authenticated user to gain elevated control over the system.

Microsoft has not received any information from customers or other users stating that their system is exploited using this vulnerability and information regarding this vulnerability was brought up through responsible disclosure.

Mitigation Factor: Only users with database access can exploit this vulnerability.

Any other Workarounds: None

SQL Server Buffer Overrun Vulnerability:

This vulnerability exists in SQL Server function that allows authenticated user to gain elevated control over the system.

Microsoft has not received any information from customers or other users stating that their system is exploited using this vulnerability and information regarding this vulnerability was brought up through responsible disclosure.

Mitigation Factor: Users with database access can exploit this vulnerability. With SQL Server 2005, services by default run on non-admin accounts. Also SQL Server 2005 prompts for a domain account for services.

Any other Workarounds: None

Are we affected?

We can use Microsoft Baseline Security Analyzer (MBSA 2.1) to determine the vulnerabilities in the System. MBSA 2.1 detects these vulnerabilities and reports of any other missing security updates too.

Download URL:

http://technet.microsoft.com/en-us/security/cc184923.aspx

Deployment Plan and Known Issues:

Deployment Instructions are available in http://www.microsoft.com/technet/security/bulletin/MS08-040.mspx.

Download links and impact instructions for SQL Server

Verification after Installation: Executing the query “Select @@version” should result with the version number given below.

Also the information can be found in the log file on the directory listed below. SQL Server 2000 - %WINDIR%\SQLHotfix SQL Server 2005 - %PROGRAMFILES%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix

Known Issues:

There are few known issues with this update as listed below.

1. Dependent services remain in stopped state even though SQL Services are started. We have to check and verify that all dependent services are started.
2. SQL Server services or components that are in disabled state will be updated. This security patch update requires that all services to be enabled and running so that the installer detects them and applies the necessary patches.
3. Upon unsuccessful completion, we need to reboot the system so that the changes are rolled back.

953743 (http://support.microsoft.com/kb/953743/) Supported method for applying updates to SQL Server 7.0
953742 (http://support.microsoft.com/kb/953742/) SQL Server 2000 and MSDE 2000 installers stop dependent services
953741 (http://support.microsoft.com/kb/953741/) SQL Server 2005 installers stop dependent services
953740 (http://support.microsoft.com/kb/953740/) How to identify and enable disabled SQL Server instances in SQL Server 2000
953739 (http://support.microsoft.com/kb/953739/) SQL Server 2005 installers do not update an instance of the SQL Server service that is in a
disabled state.

Other Points to Ponder:

This patch is included in SP3 of SQL Server 2005 which is scheduled to release in Third Quarter of 2008. By the time earning freeze is over, we should be having SP3 on which we can schedule to install so that this patch too is applied.

http://blogs.technet.com/dataplatforminsider/archive/2008/04/15/sql-server-2005-sp3-coming-soon.aspx
http://blogs.technet.com/beatrice/archive/2008/04/16/sql-2005-sp3-announced.aspx
http://blogs.technet.com/dataplatforminsider/archive/2008/01/25/microsoft-sql-server-2008-roadmap-clarification.aspx

Configuring Peer-to-Peer Replication

2008-07-23T11:19:00.005-04:00

I wanted to give a step by step illustatration here too for Peer-to-Peer replicatio. But unfortunately I had almost 26 screenshots in the article and all the images were not able to fit to the size limit of 1024MB. So I am giving you the link to my website where the original article resides. Thanks.

http://www.sql-articles.com/index.php?page=articles/P2P_replication_GUI.html

Moving System Databases - SQL Server 2005

2008-07-19T21:59:00.001-04:00

There are lots of articles, Links and sites that describe how to move system databases from the SQL default location to the location described by the DBA but still lot of users find difficult while moving them and land up in trouble. So I thought of wiriting an article for moving the databases so that the users can use the same and enjoy the task.

Tasks for moving system databases:

1.Moving tempdb databases.

a.) Execute the script below.

USE master;
GO
alter database tempdb MODIFY FILE (NAME = tempdev,FILENAME='NEW PATH');
GO
alter database tempdb MODIFY FILE (NAME = templog,FILENAME='NEW PATH');
GO

b.) Restart services.
c.) Confirm path of database files.

2. Moving model and msdb databases.

a.) Execute the script below.

USE master;
GO
alter database msdb MODIFY FILE (NAME = MSDBData,FILENAME='NEW PATH');
go
alter database msdb MODIFY FILE (NAME = MSDBLog,FILENAME='NEW PATH');
go

USE master;
GO
alter database model MODIFY FILE (NAME = modeldev,FILENAME='NEW PATH');
go
alter database model MODIFY FILE (NAME = modellog,FILENAME='NEW PATH');
go

b.) Stop services
c.) Copy the files to the new location
d.) Restart services.
e.) Confirm path of database files.

3.) Moving master database:

a.) Edit the startup parameters to reflect the new path for –d, –l and -e parameters.
b.) Stop the services.
c.) Move the master and resource database files to the new location
d.) Change the sql port to different one than usual to avoid excess connections and create an alias with that port
e.) Start the services using NET START MSSQLSERVER /f /T3608 (*MSSQLSERVER is for default instance, if you have installed named instance then you need to use NET START MSSQL$Instancename /f /T3608)
f.) Execute the script given below from sqlcmd

USE master;
GO
alter database mssqlsystemresource MODIFY FILE (NAME = data,FILENAME='NEW PATH\mssqlsystemresource.mdf');
go
alter database mssqlsystemresource MODIFY FILE (NAME = log,FILENAME='NEW PATH\mssqlsystemresource.ldf');
go
alter database mssqlsystemresource set READ_ONLY;
go

g.) Stop the services
h.) Change the port back to 1433
i.) Remove the alias
j.) Start sql services.
k.) Confirm if the data files and log files reside on desired path.

How Addicted to Blogging Are You?

2008-07-16T16:18:00.000-04:00

85%

Short Cut Keys for SQL Server 2000 and 2005

2008-07-14T12:20:00.001-04:00

I have always googled out for finding any shortcut key to be used in SQL Server that gave me an idea to post this. I have gathered all the SQL Server 2000 and 2005 shortcuts with the help of books online and internet and posted it in our website which can be accessed from the link below.

http://www.sql-articles.com/index.php?page=articles/shortcut_keys.htm

SQL Server 2008 in August

2008-07-10T14:06:00.002-04:00

Microsoft has announced that SQL Server 2008 will be in August price list. This means that SQL Server 2008 RTM will be launched as scheduled in Q3 2008. The blog entry from dataplatforminsider confirms this from Microsoft.

http://blogs.technet.com/dataplatforminsider/archive/2008/07/09/sql-server-2008-available-on-august-price-list.aspx

Thank You - My Friends

2008-07-09T17:51:00.003-04:00

These are the mails, messages that i got as wishes for my MVP. Thank you friend. With all your prayers and wishes, I would like to grow.

My Ex-Managers and Ex-Leads:

Thiyagarajan Jayaraman: (My Project Director @ Hexaware)

Congratulations Sugesh. Wish I never lost you. Happy to know that you have not stopped because of the setback. Hope you would not let such things happen again.

Wish you all the best. Thank you for remembering to keep me informed
regards
Thiyagu
-------------------------------------------------------------------
Eyalarasan: (My Team Lead @ CITIGROUP)

Hi Sugesh,

Congratz!

Just went through the site (www.sql-articles.com).. great!

You guys are doing great job... My hearty wishes!!

Regards
Eyal.s
***************************************************************************

Colleagues(Ex and Current):

Sriram Srinivasan

Great News!!! Congratulations now you have to further work hard to retain the title.

All the best
-------------------------------------------------------------------
Ajay

Hi Sugesh,

Great buddy... Hearty Congratulations on your SUCCESS... Ofcourse this is just the beginning.... Nice to know that you are one among the valuable professional's of MS. and I am proud to tell others that I know a MVP... I was checking out your site and it's really excellent and informative...
-------------------------------------------------------------------
Saravanan

Hi Sugesh,

My Hearty Congrats to you.

I know that you are working as a SQL SERVER DBA, and did not have much information other than that.

I am very happy to know that you have made into a number of handful people around the world.

All the Best for your future endeavours

Thanks
Saravanan.S
-------------------------------------------------------------------
Anandhi

congrates on this great achievement Sugesh.
Hats off to you and All the very best for your future as well...
-------------------------------------------------------------------
Roopa Jean:

Hi Sugesh,
congrats a billion Billion....
hey wow so happy for the great acheivement....yes you deserve it....
wish you many such acheivements in the fore coming days......
cheers
Roopa Jean
-------------------------------------------------------------------
Shomi Basu (Current DBA in my project @ Hexaware)

Dear Sugesh,

Hope you remember me. Congratulations for your success and hope to see you even higher. Will bug you again and again for all my doubts.

Regards
Shomik
-------------------------------------------------------------------
Naresh C. (Current DBA in my project @ Hexaware)

Congratulations Sugesh.
***************************************************************************
Friends:

Surabhya Rao:

Hey Sugesh!

Congrats man!! Chance illa....engayo poita...am sure lots of hard work must have gone into it! Very happy to know that all your efforts have got appreciated and acknowledged!!
Very happy to hear this news...congrats once again!! Keep it up :)

Regards,
Surabhya.
-------------------------------------------------------------------
Sriram V(SSN):

Sugesh,
Congratulations and I have already bookmarked the sql-articles page. Good luck with ur efforts!!
-------------------------------------------------------------------
Chakravarthy Kailash:

Machi i am so happy that as if i achieved it............ really so happy machi.......... congrats ........ and really you deserve it for all the hard work you have done so far................
-------------------------------------------------------------------
Viswanathan Swaminathan

Hey Sugesh,
Great to hear that you are one of the MVP. Congrats da!!!!
-------------------------------------------------------------------
Balakumar:

Hey Sugesh,

Way to go man...Congratulations and my Best wishes.

Thanks,
Bala
-------------------------------------------------------------------
Narendran Mohan:
It is great to see you among the top contributors around the globe. Very proud of you...All the best and let this be one more stepping stone for you..

With best regards,
Naren
-------------------------------------------------------------------
Dalavai

This is simply great … Keep going buddy…congrats and all the best.
-------------------------------------------------------------------
Swetha

Wow. Many congratulations Sugesh! That is a definitely a great achievement.
-------------------------------------------------------------------
Sasi:

I'm really proud to see this.. Keep going!! All the best Sugesh.
-------------------------------------------------------------------
Odaiyappan

Congrats Sugesh, Keep going.

Gone through those web pages of microsoft and ur sql site it is an excellent work, i am proud that i shared the class room with you at one point of my schooling.
-------------------------------------------------------------------
Samuel Ignatius:
Hi Sugesh,

many congrats, really good to hear that, specially when i know and have seen you that how much you desired to get this certification. keep in touch.

regards
Samuel
-------------------------------------------------------------------
Vinod Chidambaram
Congrats da Sugu....Very very very happy for you da....God will be always with you...Celebrate this occasion and enjoy life.
-------------------------------------------------------------------
Srividhya Seshan

Hi,
Hearty Congratulations!!! I am sure you will hold it forever.Be an MVP everywhere. Wish you the very best always.
Congrats Once again.

Thanks,
Srividhya.
-------------------------------------------------------------------
Rohini Bharath

Hi Sugesh,

Hearty Congratulationns!!!!!!!!My best wishes for your future achievements toooooooooo
-------------------------------------------------------------------
Dinesh

Congrats da mama.
All the very best. I explored the site a bit,looks clean n simple interface.
Good job,convey my wishes to chakra and seth.
-------------------------------------------------------------------
Rekha

Hey, Congratulations on your success! I feel happy to see a fellow student come so far in his professional life. I wish you success in whatever you do on your way to glory!
-------------------------------------------------------------------

Awarded Microsoft MVP

2008-07-01T14:45:00.002-04:00

I am coming with the happiest news of my professional career. For people who might know me for long this could be great news and for others it would be great to know this news.

I am now proud to say that I am part of few Microsoft Most Valuable Professional’s (MVP's) around the globe. I have been awarded this for my contributions towards SQL server and will be holding this title for a year from now. (I am sure; I will be going for a renewal too, no giving back again).

At this point, I would like to thank a few people who got me to this. The list might be small but these people mean a lot to me towards achieving this.

Krishna Gopalan - My manager at Hexaware. When I joined Hexaware, i was in infant stage of working with SQL Server. He was the one who had trust in me giving variety of tasks involving lot of SQL Server and other Microsoft products helping me to achieve near expertise. I could still hear him saying "Sugesh, I know you are the right person to do this job". That was the level of confidence that he had on me. I can never forget the ISA implementation that I worked on with his guidance. The hard to understand infrastructure application. Without him, i wouldn't have made it right here.

Stephen Seth - One of my best friends, He too works as a SQL Server DBA. i would literally pain him asking N number of questions and making comments out of everything so that we both were in right path of learning something that we were working on. Lot of my articles, scripts and comments has gone for his review before being published.

Vidhya Sagar and Deepak - My collegaues @ CITIGROUP. These are other 2 guys who have been with me together through out this journey. When we started our website (www.sql-articles.com) they both backed me up in designing the website which a person would like to read things about SQL Server. The great news here is they too are MVPs now.

And my parents and sister, I have no words to thank them. They have always stood besides me on whatever i do. Without their constant support and encouragement i would not have really achieved anything out here.

If i would have forgotten to name anyone here, please apologize me. Thanks for all others supporting me and would love to say
a BIG thanks again.

https://mvp.support.microsoft.com/profile=5888001B-22BC-4DFB-BEF1-F640063A9568

Configuring Peer-to-Peer replication Using TSQL:

2008-06-26T19:37:00.004-04:00

This article brings you the easiest way to configure P2P replication using TSQL. Also I assume that the database has been initialized in all nodes participating in the replication.

1. Configure Distributor:
This script needs to be executed against all servers that participates in the replication.

/* Check if distributor database already exists*/
declare @svrname as sysname
select @svrname = @@servername
declare @cnt int
Select @cnt=count(*) from sys.databases where name = 'distribution'
If @cnt>0
begin
Print 'distributor already exists'
End
Else
Begin
Exec sp_adddistributor @distributor=@svrname
Exec sp_adddistributiondb @database='distribution',@security_mode=1
End

2. Create Publication and Subscriber:
This script needs to be executed against all the publisher databases involved in the P2P replication instances. There are few parameters that you might need to change in this script which will be noted so edit the parameters before executing.

/*Add the publisher*/
declare @svrname as sysname
select @svrname = @@servername
/* Add the publisher server now*/

exec sp_adddistpublisher @publisher =@svrname,
@distribution_db = N'distribution',
@security_mode = 1,
@publisher_type = N'MSSQLSERVER'

/* Enable the database for publication*/
declare @dbname varchar(30)
declare @dbcheck sql_variant

/* Check if the database is set as publication*/
/*Set you database name*/
select @dbname = '------' /***** Set Your Database Name Here**************/
select @dbcheck = databasepropertyex(@dbname,'ispublished')
select @dbcheck
if @dbcheck = 0
begin
print 'Database not set as Publisher. Setting as Publisher'
exec sp_replicationdboption @dbname=@dbname,
@optname='publish',
@value='true'
end
else
begin
print 'Database already set as Publisher'
end

/* Add the Publication now*/
declare @pubname varchar(300)
select @pubname = '---------' /********Add Publication Name Here***********/
/*Execute this step against the database which you need to set as publication*/
exec sp_addpublication @publication=@pubname,
@restricted='false',
@sync_method='native',
@repl_freq='continuous',
@allow_push='true',
@allow_pull='true',
@immediate_sync='true',
@allow_sync_tran='false',
@autogen_sync_procs='false',
@retention=60,
@independent_agent='true',
@enabled_for_p2p='true',
@status='active',
@allow_initialize_from_backup='true'
Go

/* Add articles for this Publication*/
/**********This step needs to be executed for each article in the publication***************/
declare @artname varchar(300)
declare @artins varchar(300)
declare @artdel varchar(300)
declare @artupd varchar(300)
select @artname = '-------'/**********Add Article Name Here***************/
select @artins = 'CALL [sp_MSins_'+@artname+']'
select @artdel = 'CALL [sp_MSdel_'+@artname+']'
select @artupd = 'CALL [sp_MSupd_'+@artname+']'

/*Execute this step against the database which you need to set as publication*/
declare @pubname varchar(300)
select @pubname = '----------'/***********Add your publication name here*************/
exec sp_addarticle @publication = @pubname,
@article = @artname,
@source_owner = N'dbo',
@source_object = @artname,
@type = N'logbased',
@description = N'',
@creation_script = N'',
@pre_creation_cmd = N'none',
@schema_option = 0x00000000000044F7,
@identityrangemanagementoption = N'manual',
@destination_table = @artname,
@destination_owner = N'dbo',
@status = 16,
@vertical_partition = N'false',
@ins_cmd = @artins,
@del_cmd = @artdel,
@upd_cmd = @artupd
Go

/*Add the Subscriber*/
declare @pubname varchar(300)
select @pubname = '----------'/***********Add your publication name here*************/
declare @dbname varchar(30)
select @dbname = '----------'/***********Add your Subscriber database name here*************/
declare @subname varchar(30)
select @subname = '----------'/***********Add your Subscriber Server name here*************/

exec sp_addsubscription @publication = @pubname,
@subscriber = @subname,
@destination_db = @dbname,
@sync_type = 'replication support only'
Go

/*Add the Subscriber Agents*/
declare @pubname varchar(300)
select @pubname = '----------'/***********Add your publication name here*************/
declare @dbname varchar(30)
select @dbname = '----------'/***********Add your Subscriber database name here*************/
declare @subname varchar(30)
select @subname = '----------'/***********Add your Subscriber Server name here*************/

exec sys.sp_addpushsubscription_agent
@publication = @pubname,
@subscriber = @subname,
@subscriber_db = @dbname,
@subscriber_security_mode = 1,
@frequency_type = 64,
@frequency_interval = 1,
@frequency_relative_interval = 1,
@frequency_recurrence_factor = 0,
@frequency_subday = 4,
@frequency_subday_interval = 5,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_start_date = 0,
@active_end_date = 0,
@dts_package_location = N'Distributor'

Will Update Shortly

2008-06-14T18:03:00.004-04:00

Hey Guys,

I have come up with lot of articles on Replication, Performance, Architecture and High availability solutions of SQL Server 2005. I will be posting them shortly. Just to keep you updated that there will be lot of articles sooner in this blog.

SQL Server JOB deletion ERROR

2008-05-05T14:19:00.003-04:00

I have found in discussion forums regarding the error given below when you try to delete a job that was part of maintenance.

The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.
The statement has been terminated. (.Net SqlClient Data Provider)

This error is caused not in all systems in some and it depends. I have tried to replicate the error but it would be always unsuccessful. Anyways the solution to can be handled with the script given below.

SCRIPT

declare @jobname varchar(100)

select @jobname='PASS YOUR NAME OF THE JOB'

delete from msdb.dbo.sysmaintplan_log
where plan_id in(
select plan_id from msdb..sysmaintplan_subplans
where job_id in(
select job_id from sysjobs where name =@jobname))

delete from sysmaintplan_subplans
where job_id in(
select job_id from sysjobs where name =@jobname)

Then you can delete your job. Hope this post helps you.

Testing Cluster Configuration:

2008-04-09T10:09:00.001-04:00

Most people never like to test what they can done. Assuming that things have been done perfectly which results in a situtation of panic sometimes. So i thought that i could let you all know the steps to check after completing a cluster configuration.

1. Check if the Nodes are available and able to ping each other.
2. Check if you are able to reach the nodes from the client machines both with the names and IP address.
3. Try validating a failover and check if it failsover successfully. This can be done as given in the topic Validation Failover.
4. Check if all the disks are seen in the cluster group. This is the place most DBAs could go wrong. They might not have added the disks as dependencies in their configuration. To check and add disks as dependecies look on the topic Disk resources in cluster.
5. Verify error log and event viewer log.
6. Try connecting to the SQL Server management studio to check if it succedds.

Validating Failover:

1. Start Cluster Administrator.
2. In the left pane, expand Groups, right-click a cluster group, select the Move Group option, and finally select a node to move the resource group.
If the failover is functioning properly, the owner of the resources will change to the new node name.
3. Repeat step 2 to move the resource to all other nodes of the server cluster.

Disk resources in cluster:

1. Start Cluster Administrator.
2. Select the group that contains the disk resource you want to use with SQL Server Services.
3. Make sure the disk resource is owned by the node you are currently logged on to..
4. In the right pane, select the disk resource, right-click, select Change Group, and then select
the group that contains your SQL Server.
5. When prompted to confirm the move action, click Yes.
6. Once again, you will be prompted to confirm the disk resource move to the other group, Click Yes.
The disk will now be moved to the target group.
7. Take the SQL Server resource offline.
8. Right-click the SQL Server resource, and select Properties; alternatively,
double-click the resource. Select the Dependencies tab. Click Modify.
9. In the Modify Dependencies dialog box, select the new disk resource listed under Available
Resources. And move that resource to Dependencies;
10. The Dependencies tab will now show that the disk is added as a dependency of the
resource. Click Apply. Click OK.
11. Bring the offline resources online. SQL Server will be able to use the newly added disk.

Renaming a Failover Clustering Instance of SQL Server

2008-04-08T10:26:00.001-04:00

Can you rename the SQL virtual name in a Cluster? The answer for this in SQL Server 2005 is abviously Yes. New to SQL Server 2005 is the ability to rename a clustered instance of SQL Server without having to uninstall and reinstall it.

Follow these steps:

1. Start Cluster Administrator.
2. Select the resource group that has the SQL Server resources.
3. Take the SQL Server service offline.
4. Right-click the SQL Server Network Name resource, and select Properties; alternatively, double-click the resource.
5. Select the Parameters tab of the resource’s properties page
6. Enter the new name of the failover clustering resource and click Apply.
7. Select the General tab. Change the name of the resource to include the new name you configured
in step 6. Click OK.
8. Bring the SQL Server resources online.
9. Ping the new name of the SQL Server failover clustering instance. If the name cannot be
resolved, you will have to flush your DNS cache by issuing these three commands in succession:
ipconfig /flushdns
ipconfig /registerdns
and nbtstat –RR.
10. Start SQL Server Management Studio, and connect with the new instance name. If this succeeds,
the change has been done successfully.
11. For a final verification, you can also run a SELECT @@SERVERNAME query, which should reflect the name change.

Note:
You can only rename the SQL Server virtual Name and not the instance name and this operation involves some down time.

Getting Information on SQL Server Clustering

2008-04-07T09:34:00.004-04:00

SQL Server 2005 aruably gives the best resources to see if the instance is clustered or not. And if so, what are the properties of the cluster. The DMVs that are related to the Cluster can be used for this. These DMVs are very useful for a new DBA joining a company who can easily find out the environment and the disk details without needing for a proper document on place.

To check if the instance is clustered:

select SERVERPROPERTY('IsClustered')

If this returns 1 then your environment is clustered.

To check the computer name of the node owning the cluster

Select SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

The following DMVs would can be used for the cluster information.

sys.dm_os_cluster_nodes
This will return a row for each node in the failover cluster instance configuration. If the current instance is a failover clustered instance, it returns a list of nodes on which this failover cluster instance (formerly ‘virtual server’) has been defined. If the current server instance is not a failover clustered instance, it returns an empty rowset.

Sample Output:

SQLCLUSN1
SQLCLUSN2

sys.dm_io_cluster_shared_drives

This will return the information such as drive name of each of the shared drives if the current server instance is a clustered server. If the current server instance is not a clustered instance it returns an empty rowset.

Sample Output:

D
L
T
X

SQL 2005 Edition Comparision

2008-02-23T11:46:00.000-05:00

SQL Server 2005 comes with different editions with each having features that are either available in the other or not. This document lists the comparison of the features available between the different editions
http://www.sql-articles.com/index.php?page=articles/edcomp.htm

Isolation Levels

2008-02-23T11:45:00.000-05:00

This article provides an easy-to-understand view of what ‘Isolation Levels’ really mean and when to use which level. ‘Isolation Level’ is a setting that decides how data which is a part of an ongoing transaction is made visible to other transactions.

http://www.sql-articles.com/index.php?page=articles/isolevel.htm

Locks & Locking Hints

2008-02-23T11:43:00.002-05:00

Locking, Locks and Deadlocks are words that a developer/DBA does not want to hear in real life but still there are possibilities that we hear them because of some bad code written. This article takes you on to the type of locks available in SQL server and the ways of avoiding them using the hints available. Before getting into know the types of locks and the hints available, let us see what is meant by a lock and a deadlock.

http://www.sql-articles.com/index.php?page=articles/lhints.htm

Moving System Databases

2008-02-23T11:43:00.001-05:00

Upgrading to SQL Server 2005

2008-02-23T11:39:00.000-05:00

As we all know that Microsoft has released SQL Server 2005 sometime back and is planning to release the next version KATMAI before end of 2008. Also it has announced the end of life cycle support for SQL Server 2000. It’s high time that we start with upgrading our instances with SQL Server 2005 and upgrade before the life cycles support ends.

http://www.sql-articles.com/index.php?page=articles/usql.htm

Query Plans & Planguides

2008-02-23T11:37:00.000-05:00

Most developer DBAs have tough time tuning TSQL part of the database and application. And most people leave in middle of it either frustrated or can’t go beyond that. Here is a simple look on how to use the query hints and plan guides to tune the TSQL part of your database and application.

http://www.sql-articles.com/index.php?page=articles/query_plans.html

Memory Management in SQL Server

2008-02-23T11:36:00.000-05:00

I have heard people asking me how SQL Server manages memory. The answer to this was very difficult with SQL Server earlier versions. And with SQL Server 2005 it has been simpler to explain and understand. SQL Server by default installation manages memory dynamically and has settings that it can well negotiate with the operating system for the need of memory. Let us look into the depth of the SQL Server memory management.

Read more of this on http://www.sql-articles.com/index.php?page=articles/memory_management.html

Check Identity

2008-02-23T11:34:00.000-05:00

This will list out identity columns in a Database. This is compatible to SQL 2005 only.
http://www.sql-articles.com/index.php?page=Scripts/check_idenity.php

Connection Property

2008-02-23T11:33:00.001-05:00

This script will list out the current connection properties on SQL 2005.

http://www.sql-articles.com/index.php?page=Scripts/conn_prop.php

Database Details

2008-02-23T11:31:00.000-05:00

This will list out the Database properties of your database in sql server. This is compatible to SQL 2000 and 2005. Before you use the script make sure you have given the databasename for the parameter @dbname.

http://www.sql-articles.com/index.php?page=Scripts/db_details.php

Index Details for Tables

2008-02-23T11:30:00.000-05:00

This script will list out the details of the indexes in a table.This is compatible to SQL 2000 and 2005.
http://www.sql-articles.com/index.php?page=Scripts/index_details.php

Permission List

2008-02-23T11:29:00.000-05:00

This script gives a detailed information about permission for users in a database. This output lists Object name, Schema name, user name and permission. This is compatible only to SQL 2000.
http://www.sql-articles.com/index.php?page=Scripts/permission_list.php

Permission List SQL 2005

2008-02-23T11:28:00.001-05:00

This script gives a detailed information about permission for users in a database. This output lists Object name, Schema name, user name and permission. This is compatible only to SQL 2005.

http://www.sql-articles.com/index.php?page=Scripts/permission_list_2005.php

Scripting User level Permission - SQL2000

2008-02-23T11:04:00.002-05:00

This script gives a out script that can be used to script out the exising user permissions in a database.This is compatible only to SQL 2000.Don't forget to change the database name in paramert @dbname before using the script.

http://www.sql-articles.com/index.php?page=Scripts/scrip_user_00.php

Scripting User Level Permission SQL2005

2008-02-23T10:55:00.002-05:00

This script gives a out script that can be used to script out the exising user permissions in a database.This is compatible only to SQL 2005.

http://www.sql-articles.com/index.php?page=Scripts/scrip_user_05.php

Server Property

2008-02-23T10:43:00.006-05:00

This will list out the server properties of your sql server including the account under which your services run. This is compatible to SQL 2000 and 2005.

http://www.sql-articles.com/index.php?page=Scripts/ServerProperties.php

Table Space Details

2008-02-23T10:41:00.001-05:00

This script will list out the details of the space used by each in a database.This is compatible to SQL 2000 and 2005.

http://www.sql-articles.com/index.php?page=Scripts/sp_details_tbl.php

Table and Column Details

2008-02-23T10:40:00.001-05:00

This script gives a detailed information about tables and thier associated column properties for each database. This lists out Database name, owner name, table name, column name, data type, collation, etc. This is compatible to SQL 2000 and 2005.

http://www.sql-articles.com/index.php?page=Scripts/tbl_col_det.php

Trigger Status

2008-02-23T10:38:00.001-05:00

This will list out status of the trigger in a Database. This is compatible to SQL 2000 and 2005.

http://www.sql-articles.com/index.php?page=Scripts/trigger_status.php

List orphan IDs

2007-12-03T00:00:00.000-05:00

This script displays the list of orphan SQL Server accounts from all the databases

Exec SP_MSForEachDB 'select ''?'' as DBName, name AS UserName, sid AS UserSID
from ?..sysusers
where issqluser = 1 and
(sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name'

Estimated time for backup/restore

2007-12-02T23:59:00.002-05:00

This script can be used to find the estimated time of backup and restore that is on progress in your SQL server.

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(6,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(6,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(6,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(100),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

Check if /3GB is configured or not

2007-12-02T23:57:00.001-05:00

This script is used to check whether the 3gb switch is configured or not in the boot.ini file.

If(Select Virtual_Memory_In_Bytes/1024/(2048*1024) from Sys.dm_os_Sys_Info) < 1

Begin

PRINT '/3GB Switch Not Configured in Boot.Ini (CHECK)'

End

Else

Begin

PRINT '/3GB Switch Configured in Boot.Ini'

End

Create Windows network shares

2007-05-30T05:32:00.001-04:00

This procedure can be used to share any windows folders with default permissions
This procedure accepts 3 parameters
-- @path - The path of the share folder
-- @sharename - The sharename to be given
-- @type - This accepts 2 vaules either to create or revoke if create a new share is created if revoke the share ie removed

Script:

create procedure windowsshare (@path varchar(200), @sharename varchar(50),@type varchar(10))
as
begin
declare @cmd varchar(100)
if lower(@type) = lower('create')
BEGIN
set @cmd = 'net share '+@sharename+'='+@path
print @cmd
exec master..xp_cmdshell @cmd,no_output
PRINT ' SHARE '+@sharename+' HAS BEEN CREATED SUCCESSFULLY'
end
ELSE
BEGIN
set @cmd = 'net share '+@sharename+' '+@path+' /delete'
exec master..xp_cmdshell @cmd,no_output
PRINT ' SHARE '+@sharename+' HAS BEEN REMOVED SUCCESSFULLY'
end
END

Give permission to Windows NTFS objects

2007-05-29T07:16:00.002-04:00

This procedure can be used to provide the security options in windows NTFS folders with default permissions.
This procedure accepts 5 parameters
-- @path - The path of the share folder
-- @traverse - To specify if the permission needs to be traversed to sub folders or not
-- Y - to give permissions to subfolders also
-- N - to not give permissions to sub folders
-- @type - This accepts 4 vaules
-- g - to grant access to the specified folder and/or sub-folders
-- p - to modify access to the specified folder and/or sub-folders
-- r - to revoke access to the specified folder and/or sub-folders
-- d - to deny access to the specified folder and/or sub-folders
-- @user - the user to whom access needs to be specified
-- @perm - type of permission to be given and this accepts 4 parameters
-- N - No Permissions
-- R - Read
-- C - Change
-- F - Full Control

Script:
create procedure windowssec (@path varchar(200), @traverse char,@type char,@user varchar(50),@perm char)
as
begin
declare @cmd varchar(100)
if (upper(@traverse) <> 'Y')
begin
set @cmd = 'cacls '+@path
end
else
begin
set @cmd = 'cacls '+@path+' /t '
end
if (lower(@type) = 'r' or lower(@type) = 'd')
begin
set @cmd = @cmd+' /e /'+@type+' '+@user
exec master..xp_cmdshell @cmd
end
else
begin
set @cmd = @cmd+' /e /'+@type+' '+@user+':'+@perm
exec master..xp_cmdshell @cmd
END
end