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...

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....

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...

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...

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...

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...

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...

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...

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...

Read More