Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,156,129 hits
  • Select GETDATE()

    February 2026
    M T W T F S S
     1
    2345678
    9101112131415
    16171819202122
    232425262728  

Archive for the ‘SQL Server’ Category

Now Available @ Amazon : My Book on SQL Server 2012 AlwaysOn

Posted by blakhani on August 5, 2013


It might come as surprise to many of you. Finally, book authored by me is available for public on Amazon. (paperback and kindle). Those who follow Vinod’s (b|t) blog have seen this announcement already.

It has been close to an year of writing and multiple reviews, my book on “SQL Server 2012 AlwaysOn Joes 2 Pros®: A Tutorial for Implementing High Availability and Disaster Recovery using AlwaysOn Availability Groups” is finally available on Amazon.

Reason of this blog is to answer two common questions which would be asked by my friends – “How?” and “Why?”

“How” –

A little spark was ignited when me, Vinod (b|t). and Pinal (b|t) were sitting together after a User Group meeting and thinking – what can be done to share the knowledge. Pinal and Vinod were already a well-known blogger and author. I said in feeble voice – I may have some additional knowledge on AlwaysOn, how about a book on this topic? … all of us agreed and journey started.
Thanks to Vinod and Pinal for the spark of inspiration to set me off into looking things from different perspective. I wouldn’t have thought to become an “author” without your support. 

After take off of the project, table of content of 5 chapters were prepared and we have taken task to write everything about the feature. I got my home PC upgraded to better RAM so that I can use my home PC for Hyper-V images which were needed for the book. We targeted 100 page and during weekly checkpoint meeting we always thought “something is missing” … after reaching 500+ pages, we felt that we have covered almost everything about the feature which can bring a starter to intermediate level.  Decision was taken to increase the page size and trim down the number of pages without compromising quantity and quality of knowledge. Lots of hours spent on emails, phone calls, chat, internet research, books-online reading, blogs reading to make fine prints. Finally, after an year of hard efforts, book was ready to be published. In paperback as well as kindle version.

“Why” –

It feels good when someone meets and says – “You blog helped me in fixing a problem”. Being a part of SQL Server technical support team, I felt that there is not much “organized” content on AlwaysOn feature. I spoke to many folks and sensed a “fear” of this new feature. There are various blogs on the same topic and books online also has lots of content. By writing this book and sharing my knowledge , I feel that I am doing my part for the community which is driven by passionate people and MVPs.  I love technology, SQL Server and its features. There were many questions asked and being of a very tidy disposition, I liked to have them answered. That’s why we added a new chapter in the book for FAQs along and there were around 70+ questions.

What we have in the book?

Here is the crisp summary of the book.

clip_image001

18 Chapters at a Glance

Chapter 1. High Availability and Disaster Recovery Concepts
Chapter 2. Existing High Availability and Disaster Recovery Options
Chapter 3. What is AlwaysOn?
Chapter 4. Understanding Quorum Models
Chapter 5. AlwaysOn Availability Groups
Chapter 6. Availability Group (AG) Actions on the Secondary
Chapter 7. Deploying AlwaysOn Availability Groups
Chapter 8. Features of AlwaysOn Availability Groups
Chapter 9. AlwaysOn Monitoring and Troubleshooting
Chapter 10. AlwaysOn Diagnostics
Chapter 11. AlwaysOn Advanced Monitoring
Chapter 12. Deployment Variations of AlwaysOn
Chapter 13. AlwaysOn Common Issues
Chapter 14. Availability Group Failover
Chapter 15. Migrating from Previous High Availability Scenarios
Chapter 16. AlwaysOn Availability Group Maintenance Activities
Chapter 17. AlwaysOn – FAQs
Chapter 18. SQL Server 2014 – AlwaysOn Enhancements Teaser

 

How can you get it?

At this moment we don’t have any other channel to publish this book in India. Our publisher is in constant pursuit to look for alternatives, till then Amazon will the only channel.
Print Book: Amazon
e-Book: Kindle

You can read first two chapters for free on amazon via LOOK INSIDE feature on kindle edition.

image

 

I truly hope that it would help many DBAs in the world to get started with AlwaysOn Availability Group feature and deploy as high availability + disaster recovery in the environment.

Before I conclude this blog post, I want to specifically call out name of few people from my management team who have backed me in this adventure of learning and sharing via this book – Sri Krishna Jagannath (Team Manager – SQL Support), Rahul Jacob (Team Manager – SQL Support), Xavier S Raj (Escalation Manager – SQL Support), Ranjan Bhattacharjee (Group Manager – Developer Support). It would have been impossible to deliver this book without their unconditional support.

Last, but not the least, Special Thanks to my wife and daughter for being supportive through out this journey!

 

Cheers,
Balmukund Lakhani
Twitter @blakhani

Posted in AlwaysOn, Denali, Introduction, SQL Server, SQL Server 2012, Step by Step, TechEd | 11 Comments »

Coverage Report: SQL Server Bangalore User Group Meeting # 2 – @ 22 December 2012

Posted by blakhani on December 23, 2012


After missing first SQL Server Bangalore UG meet due to team outing, I was ready to meet all my old friends at second SQL Server UG meet yesterday. First of all, Thanks to Vinod (B|T) for starting the idea of SQL Server Bangalore User Group and making it popular on social networking (Facebook www.facebook.com/groups/SQLBangalore and Twitter #SQLBangUG) I have been active on facebook page to help someone who is in need. Feel free to join the group.

As expected, I was able to meet Kamlesh (B|T), Manas (b|t), Sahal (b|t), Sandip (b|t), Sudeepta (b|t) (names are in alphabetical order) from the community. We missed Pinal (b|t)this time as he was not available in town.

Here is the highlights of the whole event as tweeted by me and others. Series of back to back session were suppose to start at 10:30 AM IST and room was getting full. Due to change in venue (from Signature building to Embassy building) there were some folks who were little late.

image

Myself and other speakers were sitting at last bench.

image imageimage

First session was by one of our expert, Kamlesh. His session “Better together: Excel + SQL for Database Developers” was full of demo.

imageimage

image

Second session was by Microsoft Premier Field Engineer, Sourabh Agarwal (b|t). His session, Intro to Indexing – Basics rediscovered, was really to cover the basics and show the internals.

  • @napsterreturns presenting on Indexes (11:02 AM)

imageimage

  • @napsterreturns talking about IAM page (11:04 AM)
  • @napsterreturns talks about data page and index page types. If you didnt know IAM is pagetype 10.(11:10 AM)
  • IAM page id is 10, data page id is 1, index page id is 2 (11:10 AM)
  • @napsterreturns showing DBCC IND and DBCC PAGE outputs.#SQLBangUGmeeting … Mixed extends discussed now (11:12 AM)
  • demo on uses of DBCC IND@napsterreturns (11:12 AM)
  • Now Non-Clustered Index definition. How is structure for Clustered Index and Heap rows. @napsterreturns (11:20 AM)
  • what is plan for queries written?@napsterreturns discusses the basics of Clustered, Non-Clustered and Heap using queries … (11:27 AM)
  • SQL is a cost based optimizer @napsterreturns the basics of INDEX simplified with lots of DEMOs. (11:30 AM)
  • What a perfect timing.. at 11:30 – session was ended. (11:30 AM)

Again, a big round of applauses and Vinod introduced our next speaker, Amit Banerjee (b|t) and his session was “Knowing sp_server_diagnostics output” A feature which is not known to mant DBA. He showed some fantastic demos and shared his nice reports to read that information.

imageimage

  • @banerjeeamit started his session at #SQLBangUG"System Health Session” (11:36 AM)
  • @banerjeeamit shwoing the System health check using "Extended Events" (11:37 AM)
  • @banerjeeamit shows the Extended Events for System Health. Explains the details.#SQLBangUG. Blocking using Extended Events … Demo !!!(11:38 AM)
  • @banerjeeamit talks about Logon errors, Netowrk erros, Lock Waits using default System Health session. (11:47 AM)
  • How can we find the CPU and Memory of SQL Server 15 mins back? @banerjeeamit is making #SQLBangUGmembers think !!! It is Ring Buffer DMV. (11:49 AM)
  • @banerjeeamit showing magical report generated with system health session#SQLBangUG learning Free monitoring!! (11:52 AM)
  • Corrupt pages can also be tracked via sp_server_diagnostics.@banerjeeamit in#SQLBangUG. Now showing failed IO sub system. (11:56 AM)
  • When the issue happened, long IO, which file …SQL Server Health Session Dashboard …. #SQLBangUGby @banerjeeamit (11:56 AM)
  • Great session by@banerjeeamit#SystemHealthSessions (12:01 PM)

And then, I was the next one to speak about, Basics of Backups with SQL Server. My topic was not so new to deliver and to make it more interesting I added few interesting slides. I was able to keep audience awake after 3 heavy sessions. Here is the commentary of my session.

imageimageimage

After my session, we had enough time left before close-out so Vinod did a session about Excel feature Flash Fill. All in the room were shocked and surprised with this feature. Here are the highlights of his amazing session.

 

imageimage

 

Here are the tweets which came in after the meeting.

imageimage

Presentation content for the session is here:

Amit – Refer his blog
Balmukund – Basics of Backups with SQL Server
Kamlesh – Better together: Excel + SQL for Database Developers
Sourabh – Intro to Indexing – Basics rediscovered
Vinod – no ppt only live demos.

That’s all from my side. This was Balmukund, reporting on wordpress about SQLBangUG#2. Hope you have enjoyed it!

Cheers,
Balmukund

Posted in backup, download, Events, ppt, presentation, SQL Server, SQL Server User Group, SQLBangUG | 15 Comments »

Help: Getting error “Value cannot be null” while opening management studio. Which value?

Posted by blakhani on July 23, 2012


One of my friend emailed me today to troubleshoot a problem with SQL Server Management Studio. Here is the problem statement in his own words.

Hey Balmukund,
I know you are the one you can save me now.

I have a problem with my production SQL Server. When I am trying to connect via management studio, I am getting below error

clip_image001

Once I hit OK, I can get in to management studio but can’t expand any node completely.

Using Bingoogle, I found that this can be rectified by tweaking some registry entries. I do not want to take that route, instead want to uninstall only SSMS and install it back.

One of your reply on MSDN shows that you were not able to reproduce the problem. Let me know if you want to troubleshoot this live with me.

Friend:waiting your comments,
<Name not displayed>

For better search here is the text of the message

TITLE: Microsoft SQL Server Management Studio
——————————
Value cannot be null.
Parameter name: viewInfo (Microsoft.SqlServer.Management.SqlStudio.Explorer)
——————————
BUTTONS:
OK
——————————

Once I reached office, I pinged him today and started my day! I followed my usual ways of troubleshooting with a series of question to nail down the problem. Here we go.

<<< Start of Chat Session>>>

Balmukund: Hey.. Just came to office and saw your email. Are you ready to work now?
Friend: Hey Balmukund, Thanks for pinging back. I am ready to work now.

Balmukund: Are you able to connect the same SQL instance from other machines?
Friend: Never tried, let me try right now! (1 min pause) yes, I can.

Balmukund: Could you please share your desktop and let’s see what’s going on?
Friend: Sure!

Balmukund: Okay, I can see your desktop now. Show me the error again.
Friend: There you go! As you can see, I am unable to expand any node completely. It ends up in same error.

Balmukund: Could you please open “New Query” window?
Friend: Let me try as I never did that after seeing this error. (5 second pause). Ouch, another error!

clip_image002

Balmukund: Hmm.. Please highlight the windows and hit Ctrl + C on your keyboard and then paste in chat windows.
Friend: What that would do? Copy image?

Balmukund: No, copy the text of the message.
Friend: Wow. that’s interesting. Here it is

—————————
Microsoft SQL Server Management Studio
—————————
The filename, directory name, or volume label syntax is incorrect. (Exception from HRESULT: 0x8007007B)
—————————
OK
—————————

Balmukund: Thanks. This sounds interesting.
Friend: Really! What’s next?

Balmukund: Since you confirmed that the issue is happening with the specific machine and you are able to connect to same SQL instance via SSMS on other machine then the error is specific to this machine settings. Agree?
Friend: Absolutely.

Balmukund: Now, lets download process monitor and run that tool. Here is the link http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx or you can go to http://live.sysinternals.com and search for procmon to download exe directly.
Friend: Done. Let me run the tool now.

Balmukund: Yes please. Start the data capture, reproduce the error and stop data capture.
Friend: Okay. Done. The is a lot of information. How would you find problem with this?

Balmukund: Lets apply the filter for process name = ssms.exe using Filter in menu bar

clip_image003

Friend: Okay. that has bought down the information captured to only ssms.exe.

Balmukund: Do you see any access to path which is not valid? Do you see “Name Invalid” in Results column?
Friend: Nope.

Balmukund: Okay. Click on New query windows and get other error. Do you see it now?
Friend: Yes. Its trying to access the path “C:\Users\demouser\AppData\Local\Temp;x:\SQL”

Balmukund: Okay. Let me take a screenshot.
Friend: Sure. I am sure you are going to write a blog.

Balmukund: Yes, of course. I have found earlier that few have hit the same null error.

 
ProcMon_NameInvalid

Balmukund: It says temp in the path and also X drive. Do you have that drive?
Friend: No, its not there.

Balmukund: Okay. Could you please open command prompt and type “Set T” and show me the output?
Friend: Sure.

image

Balmukund: I think that explains the issue. TEMP path of your environment variable is incorrect. Can you please correct it?
Friend: Can you tell me steps to change?

Balmukund: Sure.  My Computer > Right Click > Properties. And then choose “Advanced”. Then click on “Environment Variables”

image

Balmukund: Now, change TMP and TEMP to valid location. On my machines its “%USERPROFILE%\AppData\Local\Temp”
Friend: Done.

Balmukund: Now, logoff and log back in.
Friend: Sure. Let me Try…

After 2 min….

Friend: You are the man!
Balmukund: I know <smiley>  Is that working now?

Friend: Of course, Yes!!!! Thanks a ton! You saved me from uninstalling SQL Management Studio.
Balmukund: Anytime! Glad I could help.

<<< End of Chat Session>>>

Later, I search on internet and found various peoples getting same error after installing few softwares. Hopefully this blog will help someone in the world! Here are the few items:
http://connect.microsoft.com/SQLServer/feedback/details/573771/value-cannot-be-null
http://social.msdn.microsoft.com/Forums/en/sqltools/thread/7e0287b3-cd37-408e-8145-4b9dba8c7811 
http://connect.microsoft.com/SQLServer/feedback/details/742857/ssms-studio-reports-value-cannot-be-null-when-attempting-to-copy-headers-with-no-rows

Hopefully this would help someone in the world.

Cheers,
Balmukund Lakhani
Twitter @blakhani

Posted in Screenshot, SQL Server, SQL Server Management Studio, SSMS | 32 Comments »