What’s new in SQL Server RC0 setup

Datacentre Edition is no longer available as a MS SQL Server 2012 RC0 candidate, Read More MS SQL Server 2012 RC0 includes new edition – SQL Server Business Intelligence,  Read More Service Pack 1 is the minimum requirement for Windows 7 and Windows Server 2008 R2 operating systems, Read more Data Quality Services can be installed using SQL Server 2012 RC0 Setup, Read more Product update is the new feature available with MS SQL Server 2012 RC0, this will integrate latest update...

Read More

Revamping Surat User Group

Dear Friends, we had a user group meeting last week – I and Vinay Pugalia was  here. We have discussed many things to revamp and re-launch Surat User Group. Here are the minutes of meeting: 1) Regular Meetings -> Monthly, Proposed schedule :Saturday evening, 1900 to 2000 2) Regular Events -> 1 every 2 months, 1 workshop every quarter 3) Decide and form SUG Website, with Blog,Forums section, Facebook page and twitter handle 4) Press Notes after every event 5) Help from CSI Surat -> Have to meet, discuss and encourage...

Read More

Maintenance Plan mystery

Last week I had interesting (or I can say weird ? ) case of Maintenance Plan. One of the server that we monitor has reported job failure – this was a part of maintenance plan. Looking at the job history and log file I came to know that this job was failing because of some database which wasn’t available – this database was dropped few days back, and, the maintenance plan has it’s reference stored somewhere. I have search around system tables etc. but nothing unusual or nothing helpful was available that indicates the source of this database....

Read More

Using NOLOCK hint

Use NOLOCK hint to avoid block - this is what I have often heard/see in many forums I participate, during local user group events and meeting. I have always advised that its not that good idea to use hints, as it may cause data corruption and blocking. And, anyways, there are many things that you can do to avoid blocking, like: use sp for everything (almost)   try to avoid using cursor transaction shouldn't be too big etc and, use READPAST hint , I will still say, use this only when you don't have choice But these all comes from...

Read More

Download SQL Server 2008 SP3 CU2

MS has recently released SQL Server 2008 Service Pack 3 Cumulative Update 2 Released‏ which has fixes reported after SQL Server 2008 SP 3, this build version is  10.00.5768.00. Below are the bugs that have been fixed in this CU: FIX: A backup operation on a SQL Server 2008 or SQL Server 2008 R2 database fails if you enable change tracking on this database FIX: CLR stored procedure returns NULL after it is recompiled in SQL Server 2008 R2 FIX: Access violation when you insert a record into a new empty partition in SQL Server 2008 There...

Read More

Download SQL Server 2012 Developer Training Kit

Microsoft has released SQL Server 2010 Developer Training Kit which includes : Labs Demos and, Presentations This kit will greatly help you learning Developer and BI Solutions. One will require to have Windows 7 or Windows Server 2008 R2 to install this kit. Once you have download the kit, launch the installer and it will ask you which lab, demos and presentations you want to include and install them based on your choice. Sounds interesting? Grab it from the link -- Hemantgiri S. Goswami (http://www.sql-server-citation.com...

Read More

Resolving 701 There is insufficient system memory to run this query

In recent past while working on an assignment I have encounter an error 701 There is insufficient system memory to run this query . I had a quick look at the server and noticed that server is not configured proper for max memory, and have suggested client to make changes to the max memory settings for the server. While I've suggested changes I have quote two articles, thought they would be a help to you as well to understand better on how SQL Server Memory managed. An article by SQL Server MVP Jonathan Kehayias will help you...

Read More

DATABASE_OBJECT_CHANGE_GROUP do not audit SP or other object

Deepak Kumar (friend of mine and founder of http://www.sqlknowledge.com )  were chatting yesterday. We were discussing about audit feature in SQL 2008, Deepak has enabled this feature for one of his client since month. And when he was looking at the log he found that there were entries but they are related to Tables only, and not other objects like SP(s).  We were discussing and google about the same and found an entry in connect where in it was answered.   If we need the other objects to be audited we have to add SCHEMA_OBJECT_CHANGE_GROUP...

Read More

Refresh QA Database with Manual Scripts

Couple of week back Megha Sharma send me an email with a document attached, this document is all about how to refresh QA database with manual script. Here is the preview of the document, and the reason why we should follow the method in her own words: QA environment frequently needs Database refresh and hence space on its disks. QA Database is refreshed from Production followed by a data purge, which leaves a lot of free space in the database. In order to release the free space, we follow database shrink command, a lengthy & single thread...

Read More

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

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

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

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

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

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

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

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

Read More

welcome 2011

Dear Readers, Wishing you all very happy and prosperous new year, may the new day bring you joy and happines...

Read More