SQL Server Wait Types in SQL 2008

While working on an assignment of troubleshooting performance issue few month back I have come across a situation where I have to provide good information about the different waittypes and what they do, while researching a good documentation on wait types I have found the most authentic information on PSS Engineer’s blog where Bob Ward has put them nicely. This blog was published in 2009 but its good to keep handy as reference.

Most of the time wait type says us where the bottleneck are, if you wait types like IO_Completion, PAGE IO LATCH, this is indication for disk IO subsystem has some bottleneck. We have to use perfmon counter to verify this and then work around on resolving this issues.

Let me quote some links for the further reading:

http://blogs.msdn.com/b/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx

http://blogs.msdn.com/b/psssql/archive/2009/11/24/doctor-this-sql-server-appears-to-be-sick.aspx

http://support.microsoft.com/kb/822101

http://blogs.msdn.com/b/psssql/archive/2009/07/08/q-a-on-latches-in-the-sql-server-engine.aspx

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Read More

Overcome update conflict in P2P replication

When we have Peer to Peer (P2P) replication in place on SQL Server 2008, probably we would see a conflict

1) conflict of type 'Update-Update' was detected at peer 2 between peer 1 (incoming)

2) conflict of type 'Update-Delete’ was detected at peer 2 between peer 1 (incoming)

Microsoft has confirmed that this is known issue with P2P replication, and Microsoft has released a fix for this with Cumulative hot fix for SQL Server 2008 SP1. I am penning down some links for you as a point of reference for troubleshooting P2P conflict.

Paul Libson has wrote an excellent article on how to handle P2P replication

http://support.microsoft.com/kb/973223

http://technet.microsoft.com/en-us/library/bb934199.aspx

http://technet.microsoft.com/en-us/library/ms147366.aspx

http://technet.microsoft.com/en-us/library/ms151865.aspx

 

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Read More

Script to Configuring Auto Growth to Fix MB

Few month ago I have written a post about what is Auto Growth and what is the best practice for Auto Growth. In this post I have discussed about why should we have Auto Growth for databases set in a fixed MB instead in percentage and how it will impact us.


In this post, I am going to give you a script that will help you find the database files which have Auto Growth option set in percentage and alter them all to fixed MB in one go.

/* 
-- Created By: Hemantgiri S. Goswami
-- Date: 29th April 2011
-- Version: 1.0

*/

-- Creating the table to capture temporary data
IF EXISTS(SELECT name FROM sys.sysobjects WHERE name = N'ConfigAutoGrowth' AND xtype='U')
	DROP TABLE ConfigAutoGrowth
GO	
CREATE TABLE DBO.ConfigAutoGrowth
(
iDBID		INT,
sDBName		SYSNAME,
vFileName	VARCHAR(max),
vGrowthOption	VARCHAR(12)
)
PRINT 'Table ConfigAutoGrowth Created'
GO
-- Inserting data into staging table
INSERT INTO DBO.ConfigAutoGrowth
SELECT 
	SD.database_id, 
	SD.name,
	SF.name,
	--sf.fileid, 
	--SUSER_NAME(owner_sid),
	--recovery_model_desc,
	CASE SF.status & 0x100000
	WHEN 1048576 THEN 'Percentage'
	WHEN 0 THEN 'MB'
	END AS 'GROWTH Option'
FROM SYS.SYSALTFILES SF
JOIN 
SYS.DATABASES SD
ON 
SD.database_id = SF.dbid
GO

-- Dynamically alters the file to set auto growth option to fixed mb 
DECLARE @name VARCHAR ( max ) -- Database Name
DECLARE @dbid INT -- DBID
DECLARE @vFileName VARCHAR ( max ) -- Logical file name
DECLARE @vGrowthOption VARCHAR ( max ) -- Growth option
DECLARE @Query VARCHAR(max) -- Variable to store dynamic sql


DECLARE db_cursor CURSOR FOR
SELECT 
idbid,sdbname,vfilename,vgrowthoption
FROM configautogrowth
WHERE sdbname NOT IN ( 'master' , 'model' , 'msdb' , 'tempdb' ) 
AND vGrowthOption  = 'Percentage'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbid,@name,@vfilename,@vgrowthoption  
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT 'Changing AutoGrowth option for database:- '+ UPPER(@name)
	SET @Query  = 'ALTER DATABASE '+ @name +' MODIFY FILE (NAME = '+@vFileName+',FILEGROWTH = 500MB)'
	EXECUTE(@Query)

FETCH NEXT FROM db_cursor INTO @dbid,@name,@vfilename,@vgrowthoption  
END
CLOSE db_cursor -- Closing the curson
DEALLOCATE db_cursor  -- deallocating the cursor

GO
-- Querying system views to see if the changes are applied
SELECT 
SD.database_id, 
SD.name,
SF.name,
--sf.fileid, 
--SUSER_NAME(owner_sid),
--recovery_model_desc,
CASE SF.STATUS 
& 0x100000
WHEN 1048576 THEN 
'Percentage'
WHEN 0 THEN 'MB'
END AS 'Growth_Option'
FROM SYS.SYSALTFILES SF
JOIN 
SYS.DATABASES SD
ON 
SD.database_id = SF.dbid
GO

--Dropping the staging table
DROP TABLE ConfigAutoGrowth 
GO

Hope this helps!!


-- Hemantgiri S. Goswami

Read More

Troubleshooting Oracle Link Server Issue

Most of the time, due to different business requirements we do have to work on various RDBMS systems, Oracle and MS SQL Server are the widely used and popular RDBMS. Sometimes we need to import/export data from/to SQL Server and for that we’ve to use Link Server feature of MS SQL Server.

In one of my recent project, a critical application has a job that pulls in the data from an Oracle database, we have a DTS Package and some job scheduled for this task. since couple of days we’ve observed below error message in the job history and while running ad-hoc queries. I am penning down the error message and the solution (rather, temporary workaround) we have found out :

1) The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.

==> In this case, rebooting MS DTS service  helped us.

2) The maximum number of active transactions that the MS DTC log file can accommodate has been exceeded.  You must increase the size of the MS DTC log file if you wish to initiate more concurrent transactions.

==> In this case, increasing Log file size helped us.

3)Server: Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDAORA" for linked server "OraLnkSvr" reported an error. The provider ran out of memory.

4) Server: Msg 7330, Level 16, State 1, Line 1
Cannot fetch a row from OLE DB provider "MSDAORA" for linked server "OraLnkSvr".
OLE DB provider "MSDAORA" for linked server "OraLnkSvr" returned message "Out of memory.".

==> In case 3 & 4, rebooting SQL Server service helped us.

 

Hope this helps.

 

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Read More

Checking DB Mirroring Status

Often I see a question in community on how to quickly check the status of the database mirroring, sometime in busy environment and busy server launching database mirroring keep us waiting for a while, so is there a way we can check database mirroring status ?


Yes, of course we do have; execute below statement and you will have a status of the database mirroring for all the database you have configured mirroring on :

SELECT DB_NAME(database_id),   
mirroring_role_desc,    
mirroring_state_desc    
FROM sys.database_mirroring    
WHERE mirroring_guid IS NOT NULL; 

Let me know if you are looking some specific code, and I will try to post it here!!


- Hemantgiri S. Goswami (http://www.sql-server-citation)

Read More

Accessing SSIS on a Remote Server

In one of my recent assignment I was asked to help developers who were not able to work on SSIS on a remote computer, whenever they made an attempt to connect to SSIS on Remote server they were getting “Access is Denied” error. Researching bit on this I come across an article on MSDN which help me resolve this issue – this issue is all about a DCOM Permission. 
Here are the steps which helped me resolve this issue by granting necessary permission and adding developer user[s] or group for DCOM:
1) Open Component Services MMC Snap-in by Open Control Panel –> Administrative Tools –> Component Services
2) Expand Component Services node –> Expand Computers –> Expand My Computer and click DCOM Config
3) Select DCOM Config and it will display the sub list in the right pane, select MsDTSServer100

4) Right click on MsDTSServer100 and select Security tab

5) As shown in the above snapshot assign proper permission to user[s] or user group by clicking on Edit button under (you have to select Customize before you click Edit button)

a) Launch and Activation Permissions
     a.1) Local Launch
    a.2)  Remote Launch
    a.3) Local Activation
    a.4) Remote Activation
b) Access Permissions
    b.1) Local Access
   b.2) Remote Access
c) Configuration Permissions
   c.1)  Full Control
   c.2) Read
    c.3) Special Permission

6) Assign the appropriate permission on “My Computer” under Component Services
7) In MSDB database grant db_dtsoperator permission to user[s] or user group
8) Restart the Integration Services services, and you are done.
source : http://msdn.microsoft.com/en-us/library/aa337083.aspx

Read More

Winner of the contest

Dear Readers,

I am very please to announce winner of the contest about describing best HA & DR solution. The contest was successful, there are about  5 entries I have get from various  sources like linkedin and email, I would like to thank all the participant and readers. The winner is Dave Thomas.

My apologies, I suppose to declare winner on 25th April but could not able to make it.

Read More

MVP Again year 2011

Hi,
 

I have a good news to share with you all - my MVP award is renewed one more time, this is the 4th year of receiving this prestigious award (2nd time in the row) . As you all aware MVP award requires renewal every year. On the 1st of April I received from Microsoft which reads: 

Read More

Describe HA and DR strategy and win

Dear Readers,

I am very please to made an announcement about the contest.  As you aware that I have wrote a book on SQL Server 2008 High Availability which was published on 24th January 2008 2011 by Packt Publication. Now you have a chance to win a subscription worth £150 of digital library at Packt Publication and a Paper book of SQL Server 2008 HA, here is the information.

Who this book is for?

This book is written for the System Administrator, experienced SQL Developers who want to learn about the topic – SQL Server High Availability, Aspiring DBAs. That means, this book is having a step-by-step instructions, pre-requisite with plenty snapshots to get you through the installation of SQL Server High Availability options like Cluster, Replications ( Snapshot, Transactional, Peer-2-Pee and Merge), Log Shipping and Database Mirroring.

I have tried to include external reference for further study on that particular topic if you wish to read some advance information, I have also include few common issues and how to resolve them for every topic i.e. How to troubleshoot common issues for Clustering, Replication, Log Shipping and Database Mirroring.

Here is the detailed index for every chapter of this book at - http://www.sql-server-citation.com/2011/01/sql-server-2008-high-availability-book.html

Where to buy?
One can purchase my book SQL Server 2008 High Availability from Packt Publication in Paper and eBook format at https://www.packtpub.com/microsoft-sql-server-2008-high-availability/book or one can purchase this from Amazon at http://www.amazon.com/Microsoft-Server-2008-High-Availability/dp/1849681228/ref=sr_1_1?ie=UTF8&s=books&qid=1299390127&sr=8-1



Contest Information:

Describe the best HA and DR solution you have designed or worked upon, especially in case of physically dispersed location, and the reason why?

The best answer will win a  subscription of digital library worth £150 at Packt Library for 1 year and 2nd best answer will win Paper book of SQL Server 2008 HA.

The best answer will be judged by me and Satya Shyam K Jayanty (whom I admired as Guru.), we will announced the winner by 25th April 2011.

Rules:
1. Their will be two winners - 1 Subscription and 1 Paper book of SQL Server 2008 HA
2. The contest will remain open until 5th of April 2011
3. The Packt Pub Subscription will be free for 1 year

Update:
By mistake I have mentioned that my book was published on 24th January 2008, I did not noticed it until Ashish Sharma draw my attention, I have corrected it now. Thank you Ashish :)

What are you waiting for, start participating and be a lucky winner!!!



Regards
-- Hemantgiri S. Goswami | www.sql-server-citation.com

Read More

Cannot resolve the collation conflict

Recently when I was referring to SQL Server Forums I saw a question that reads like:
I get an error  Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

What is a Collation?
In simple words Collation is the set of rules that ensure proper use of characters for Language or Alphabet. Here are some recommendation from books online about choosing collation:

• Select a BINARY2 collation if binary code point based ordering is acceptable.
• Select a Windows collation for consistent comparison across data types.
• Use a new 100-level collation for better linguistic sorting support.
• If you plan to migrate a database to the upgraded instance of SQL Server, select the collation that matches your existing collation of the database.

Solution:

The error that we are referring here shown up because the two databases have different collation for some reason, now, to overcome this issue one may use COLLATE clause, but do remember that COLLATE clause will work with CHAR,VARCHAR,TEXT, NCHAR, NVARCHAR and NTEXT data type.

The Syntax :

SELECT * 
FROM 
SYS.databases SD
JOIN
SOMEDB.DBO.SOMETBL JC    
ON 
SD.database_id  = JC.DBID  COLLATE Latin1_General_CI_AI

Here are some external resource for further reading




http://msdn.microsoft.com/en-us/library/ms143508.aspx>




http://blogs.msdn.com/b/qingsongyao/archive/2008/08/25/sql-server-2008-katmai-s-new-collations.aspx

photo credit: mdanys via photopin cc

Read More

Tip for Backing up User Databases

We all take a database backup as a first resort to recover from a damage occur during the disaster / server crash / for any other reason if require to recover or restore our data back to its previous stat as it was before the database server was crashed!! And for this we schedule a job or make use of database maintenance plan which will take a database backup be it Full / Differential or Log, and we all take a special care and consideration when we design the backup strategy!!

But what happened if one fine day when you need your database backup file to work well and it doesn’t!! What if it is corrupted!! And you have only a Full backup for that day?

Today I am going to discuss about the script that I have posted in the download section couple of days back; the script will not only perform a full database backup of all user databases but it will also verify the validation of the backup file!  It's a must to have a database backup strategy where in we do not keep multiple copies of database backup  and if one have one or two days retention policy. 

declare @int int ,@dbname varchar(22),@maxdbid int
declare @int int ,@dbname varchar(22),@maxdbid int
declare @bkpath varchar(25),@path varchar(50)
select @maxdbid= max(dbid) from master..sysdatabases
set @int = 0
set @bkpath='C:\SQLDB\backup\'
USE master
while (@int < @maxdbid)
begin
set @dbname = (select [name] from master..sysdatabases where dbid=@int and status!=1073807392)  
if (@dbname != 'tempdb') 
 begin
  select @int as 'DBID',@dbname as 'Database'  
  select @path=@bkpath+@dbname+CONVERT(VARCHAR(12),GETDATE(),112)
  BACKUP DATABASE @dbname TO DISK = @path  
  print @path
  print @bkpath
  restore verifyonly from disk=@path
  end  
 set @int = @int + 1  
 continue 
 break
end

Check the line # 17, restore verifyonly from disk = @path is the line that will help us to overcome above issue. This is not a hidden code lying in a secret place somewhere but this is something that we may have missed in our code – I know most of you have already using this option for your database backup validation, how many of you are using this option? Or out of curiosity any other option!!

PS: This code is the very basic code that I have wrote initially, you may modify and use the script according to your requirement without any obligation.

Your suggestions welcome!

Regards

- Hemantgiri S. Goswami

Read More

SQL Server 2008 High Availability – A Book

I have no words to explain my excitement and happiness to share news with you all - my book on SQL Server 2008 High Availability is published!!
I have started authoring this book in May 2010 and as the time passes and gradually I move to the final chapter of the book, I was feeling like aha finally it is done!!! – Do you think so, it was this easy?
No, every publication house has their own set of rules to be followed while authoring a book and a process to be followed before the chapter or entire book is ready to be published i.e. 1^st edit, 2^nd edit of the chapter until final edit and the Packt Publication is no exception.
Since this is the first time I was authoring book I was having little or no idea about authoring a book. The editors from Packt Publication helped me a lot by helping me time to time with proper answers to my queries. I must admit that I could not be able to complete this book without support and encouragement from my family including my 2 year old daughter Dhruti.
I hope that you all will enjoy reading and learning from this book as I did while authoring and contributed my little part for the growth of my beloved SQL Server J
Let me tell you about this book:
The term High Availability means that the servers or systems that host or run the business-critical applications should be highly available 24 X 7. As the word it-self defines how important it is to make these applications and data available for end-users as well as business users, if this data is not available for a short time, it will be a big problem for both sets of users. Imagine a bank spread across the country and having a huge customer base. One fine day, their server crashes! If the bank relies only on backups, then it might end up losing approximately 15 to 30 minutes of data, depending on the backup strategy. Now the HA options related to SQL Server such as clustering, replication, log shipping, and database mirroring will help overcome this situation.
In this book I have tried to cover every single piece of information that might requires for installing and configuring SQL Server HA option like Clustering, Replication, Log Shipping and Database Mirroring, below are the chapters:
Chapter 1: Understanding Windows Domains, Domain Users, and SQL Server Security
Chapter 2: Implementing Clustering
Chapter 3: Snapshot Replication
Chapter 4: Transactional Replication
Chapter 5: Merge Replication
Chapter 6: Peer-to-Peer Replication
Chapter 7: Log Shipping
Chapter 8: Database Mirroring
Appendix A: Troubleshooting
Appendix B: External References
If you are interested to read more about the detailed / contents of each chapter you may find them here https://www.packtpub.com/toc/microsoft-sql-server-2008-high-availability-table-contents
And, here is the link for the sample chapter https://www.packtpub.com/sites/default/files/1223-chapter-1-understanding-windows-domains-domain.pdf

Regards
Hemantgiri S. Goswami

Read More

Download SQL Server Training Kit

Couple of months back I have wrote a blog article about the availability of Microsoft SQL Server Developer Training Kit which is very helpful resource for the SQL Server Developers, trainers and professionals to understand the improvements comes with the release of SQL Server 2008.
Recently, 18th January 2011 Microsoft has released the updated version of Microsoft SQL Server Developer Training Kit which has

• # 31 Presentations (includes Slide decks, videos and transcripts)
• # 27 Demos (includes installer scripts, videos and transcripts)
• # 12 hands on labs (includes installer scripts)

The main highlight of the updated version is – BI Solution, yes you read it right. The version of developer training kit has the units for BI Solutions like:

• Build your first Microsoft BI Solution with SQL Server 2008 R2
• Introduction to BI Modelling Techniques

And all of this are free!!
Download the updated version of SQL Server Developer Training kit from here –> http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=fffaad6a-0153-4d41-b289-a3ed1d637c0d
You may want to check the complete list of what is included in SQL Server Developer Training Kit and System Requirements before you begin download. 
Regards
Hemantgiri S. Goswami

Read More

Download Section for Script and free ebook

Dear Readers,


Good News!!


I have created a new section on this site from where you can download various scripts I have created and uploaded on my community website. Currently you can download scripts for:

• To Find Missing Index
• To get various SQL Server Property details
• To search a given word from a string
• Script which will take backup and verifies the backup set as well
• Script which will give you an idea on how to perform transaction in smaller chunks

You may find them all at http://www.sql-server-citation.com/p/downloads.html, and I promise that I will surely upload some more scripts in coming future.

Regards
Hemantgiri S. Goswami | http://www.sql-server-citation.com/

Read More

welcome 2011

Dear Readers,

Wishing you all very happy and prosperous new year, may the new day bring you joy and happiness

Read More