workaround for orphaned users

The server principal  is not able to access the database  under the current security context, is the error message appears for those users who was able to access the database / application earlier especially after refreshing database from production to dev, test or uat enviornment when the correspondent login is dropped.
The reason is that, when we restore the database the the SID for the user mismatches, and it became orphaned. The workaround for this issue is very simple, you will have execute the below code

 -- This part will report if there are orphaned users    
USE 'yourdbname'    
GO    
sp_change_users_login     
    @Action='Report'    
GO    
-- This part will fix orphaned users    
USE 'yourdbname'    
GO    
sp_change_users_login     
    @Action='update_one'    
    ,@UserNamePattern='youruser'    
    ,@LoginName='yourlogin'    
GO

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

Read More

SQL 2008 R2 Administration Cook Book

Couple of week back I have received a special gift, from the person whom I admire as a elder bro and mentor – Satya SkJ. Recently he has written a cook book on SQL Server 2008 R2 Administration, forward written by MVP Brad McGehee, and reviewed by Vinod Kumar M – the most admirable person in SQL Server Community in INDIA. I am very excited to read this book thoroughly, the thing I like most in every chapter is “How it works”  section. Very soon I will post detailed review for this book.

You can find the detailed information about each chapter at the below location :
                                                  Chapter 1: Getting Started with SQL Server 2008 R2
Chapter 2: Administrating the Core Database Engine
Chapter 3: Managing the Core Database Engine
Chapter 4: Administering Core Business Intelligence Services
Chapter 5: Managing Core SQL Server 2008 R2 Technologies
Chapter 6: Improving Availability and enhancing Programmability
Chapter 7: Implementing New Manageability Features and Practices
Chapter 8: Maintenance and Monitoring
Chapter 9: Troubleshooting
Chapter 10: Learning the Tricks of the Trade
Appendix: More DBA Manageability Best Practices



Sounds interesting ? You can purchase the one for you at Link => https://www.packtpub.com/microsoft-sql-server-2008-r2-administration-cookbook/book#in_detail



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

Read More

CU 16 for SQL Server 2008 SP1

 

Microsoft has recently released a Cumulative Update (CU) # 16 for SQL Server 2008 SP1 which contains hotfixes which were resolved after SQL Server 2008 SP1; once you will install / apply this CU the server version would be 10.00.2850.00

As always it is recommended that you test this CU in the development/test/QA environment first before you roll it out in production environment. Once you apply this CU, you might have to restart you server.

Download => Cumulative Update # 16

Find KB article which explains what is a different model for Incremental Servicing Model (ISM) like Critical On-Demand known as COD, On Demand known as OD, Cumulative Update known as CU and General Distribution Release known as GDR. Here is the reference link => Incremental Servicing Model

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

Read More

SQL Server DBA Quiz on BeyondRelational

Friend of my mine and fellow MVP Jecob Sebastian is running SQL Server DBA Quiz 2011 on his community web site – BeyondRelational.  He asked me to submit question as a Quiz Master, so, here is my question for you all :

You have an SQL Server Integration Service (SSIS)  installed on some machine and you have client tool installed on your local desktop, when you tried to connect to the machine you have SSIS service running on from your local desktop you will get an error “RPC server is unavailable”

You will have to let us know what the root cause of this issue and how to resolved it.

Know the answer already?

Click Here to Submit an Answer

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

Read More

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