Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooing Skills

Archive for the ‘SQL Server’ Category

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 | 16 Comments »

Help : I purchased proper media to upgrade SQL version, still setup thinks that its Evaluation. What’s wrong?

Posted by blakhani on February 12, 2012


Have you came across the scenario where you are performing a version upgrade of SQL Server as per supported path described here but still you are facing below error message during rules check?

<Error>
Error:
========
Rule “SQL Server 2008 R2 Feature” failed.

The specified edition upgrade is not supported. For information about  supported upgrade paths, see the SQL Server 2008 R2 version  and edition upgrade matrix in books online.
</Error>

If you look at Detail.txt file created by setup, search for “FeatureUpgradeMatrixCheck” and have a closer look at the log. From the highlighted you can easily understand the source and destination version+edition. So as per below log, setup is trying to upgrade from SQL 2000 Standard to Evaluation Edition (which is not in the link I provided earlier)

2011-01-27 22:53:34 Slp: Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.SkuUpgradeRule
2011-01-27 22:53:34 Slp: — SkuUpgradeRule : Rule ‘FeatureUpgradeMatrixCheck’ looking for previous version upgrade data for feature package ‘sql_as_Cpu32′.
2011-01-27 22:53:34 Slp: — SkuUpgradeRule : Rule ‘FeatureUpgradeMatrixCheck’ feature package ‘sql_as_Cpu32′ found no upgrade features.
2011-01-27 22:53:34 Slp: — SkuUpgradeRule : Rule ‘FeatureUpgradeMatrixCheck’ looking for previous version upgrade data for feature package ‘sql_engine_core_inst_Cpu32′.
2011-01-27 22:53:34 Slp: — SkuUpgradeRule : Found feature package  ‘Sql2000Ent_Cpu32′ with SkuValue=STANDARD ProductVersion=8.00.2039
2011-01-27 22:53:34 Slp: — SkuUpgradeRule : Rule ‘FeatureUpgradeMatrixCheck’ found sourceVersion 8.0 and edition STANDARD for feature package ‘sql_engine_core_inst_Cpu32′.
2011-01-27 22:53:34 Slp: — SkuPublicConfigObject : ValidateSkuMatrix checking sku matrix for sourceVersion=8.0 sourceEdition=STANDARD sourceArchitecture=X86 targetEdition=EVAL targetArchitecture=X86
2011-01-27 22:53:34 Slp: — SkuPublicConfigObject : ValidateSkuMatrix source and target architecture match.
2011-01-27 22:53:34 Slp: — SkuPublicConfigObject : ValidateSkuMatrix did not find a match in sku matrix .
2011-01-27 22:53:34 Slp: — SkuUpgradeRule : Rule ‘FeatureUpgradeMatrixCheck’ feature package ‘sql_engine_core_inst_Cpu32′ is blocking upgrade.
2011-01-27 22:53:34 Slp: — SkuUpgradeRule : Rule ‘FeatureUpgradeMatrixCheck’ detection result: IsValidFeatureUpgrade=False
2011-01-27 22:53:34 Slp: Evaluating rule        : FeatureUpgradeMatrixCheck
2011-01-27 22:53:34 Slp: Rule running on machine: SQLSERVER
2011-01-27 22:53:34 Slp: Rule evaluation done   : Failed
2011-01-27 22:53:34 Slp: Rule evaluation message: The specified edition upgrade is not supported. For information about supported upgrade paths, see the SQL Server 2008 R2 version and edition upgrade matrix in Books Online.
2011-01-27 22:53:34 Slp: Send result to channel : RulesEngineNotificationChannel
2011-01-27 22:53:34 Slp: Initializing rule      : Incomplete upgrade
2011-01-27 22:53:34 Slp: Rule applied features  : SQL;SQLEng

On a side note, Do you know the media difference for Standard and Evaluation edition? Well, there is no difference! Its the PID (Product ID) in below screen which decides the edition to be installed. The value of PID is picked from DefaultSetup.Ini file located on media. If there is no such file on Media then it would be enterprise evaluation edition of SQL Server.

Here is the little test which would prove the theory. I swear to god that I have not hit cancel while doing the test. Its the same media used for all the screen (Evaluation Media) All I did was change value in Enter the Product Key and hit next> or previous<.

VersionUpgrade_01imageVersionUpgrade_02

VersionUpgrade_03imageVersionUpgrade_04

VersionUpgrade_05imageVersionUpgrade_06

 

Since I had media for various editions (How? Did I tell you that I work with Microsoft SQL Support team.. lol!), I copied/pasted the PID from DefaultSetup.ini file and rest can be seen above.

You might say, Nice to know what’s happening behind the scene but what you need to do if you run into such situation. In my scenario customer informed that they purchased Standard edition of SQL Server 2008 R2 still its taking EVAL in targetEdition and there has to be flaw somewhere. Later we found that screen was selecting Enterprise Evaluation by default (not sure why) So once he entered the Product Key, it worked like a charm!

Now, go ahead with the setup and post a comment here if this helped.

Cheers,
Balmukund Lakhani
Twitter @blakhani

Posted in Edition Upgrade, Error, Evaluation, Images, PID, Product Key, Screenshot, SQL Server, Step by Step, Version Upgrade | 1 Comment »

Help : I lost sa password and no one has System Administrator (SysAdmin) permission. What should I do?

Posted by blakhani on February 8, 2012


If you are thinking that I am going to show you black magic to recover sa password or other login’s password then you have hit the wrong blog post. go back to search engine and search for better tool/utility. Smile

After spending many years with SQL Server product, I have seen situations where someone wants to get in to SQL Server as system administrator as someone recently left company who had System Administrator permission (or hundred other reasons). Till SQL 2000 days, it was impossible to solve such problem other than reinstalling SQL Server. Here are the typical questions I saw in forum:

  • Only sysadmin user is SA and I Lost SA password.
  • I am locked out of SQL server i.e. that no windows users are added (or removed them) as sysadmin and I forgot the password for sa
  • I am windows admin. How can I get sys admin privileges on SQL server express as I removed all sysadmin accounts from SQL.

Here are the various error you might see

  • Unable to create new database. This is generic error which means that you are not having permission.

SysAdminLost_1

TITLE: Microsoft SQL Server Management Studio
——————————
Create failed for Database ‘SQLServer-Help’.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1901+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
CREATE DATABASE permission denied in database ‘master’. (Microsoft SQL Server, Error: 262)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=262&LinkId=20476
——————————
BUTTONS:
OK
——————————

  • When you login to Management Studio, you would see only your own account and ‘sa’ as shown below

SysAdminLost_2

  • When you attempt to change password of ‘sa’ you might see below error.

SysAdminLost_3

TITLE: Microsoft SQL Server Management Studio
——————————
Change password failed for Login ‘sa’.  (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1901+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Change+password+Login&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Cannot alter the login ‘sa’, because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15151)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=15151&LinkId=20476
——————————
BUTTONS:
OK
——————————

 

All error messages appear because your account is not a System Administrator of SQL Server Instance.

So, what should you do now? Answer is simple, get yourself added as System Administrator. I know, you would say “don’t you think I have tried that as I got this error!”

SysAdminLost_4

TITLE: Microsoft SQL Server Management Studio
——————————
Add member failed for ServerRole ‘sysadmin’.  (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1901+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Add+member+ServerRole&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
User does not have permission to perform this action. (Microsoft SQL Server, Error: 15247)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=15247&LinkId=20476
——————————
BUTTONS:
OK
——————————

 

Okay, here is the step by step guide to add any account as System Administrator of SQL Server. This is documented and completely supported way to gain back the rights.  To log into SQL Server as SysAdmin, you need to have Local Administrator permission on the windows which is hosting SQL Server. If you don’t have that also then you may want to check with your windows team to get access (I am not a windows guy)

Steps to login to SQL Server as System Administrator. [Provided you are having windows local administrator permissions]

  • Stop the SQL Server Service using ANY of below command.
    • Net Stop MSSQLServer (for default instance) / Net Step MSSQL$<InstanceName> If you want to know instance name, refer my earlier blog
    • Use SQL Server Configuration manager and stop the SQL service. [Start>Programs>Microsoft SQL Server 2005>Configuration Tools>SQL Server Configuration Manager]
    • Use Services console [ Start > Run > Services.msc] and locate the SQL instance you want to stop.
  • Start SQL Server in Single User mode. You need to use start-up parameter m to start SQL Service in single user mode. I prefer command line but its your choice.
    • Using command line
      • net start MSSQLServer /m SQLCMD        [For default instance]
      • net start MSSQL$<InstanceName> /m SQLCMD     [For named instance]
    • Using configuration Manager
      • Locate the service which you have stopped earlier. Go to its properties, “Advanced”, click on drop down at “Startup Parameters”  and add ;-mSQLCMD as shown below

SysAdminLost_5

You might notice that I have use SQLCMD after m. That’s not a typo. Many times, when you start SQL Server in single user mode, application grabs connection before you could. SQLCMD ensures that only SQLCMD program can connect to SQL Server when its running in single use mode. Here is the error you might see if above happens. SQLCMD should be in UPPERCASE. else that would also show same error. Please make sure there is no space between “;” and “-m”, the registry parameter parser is sensitive to such typos. You should see an entry in the SQL Server ERRORLOG file that says “SQL Server started in single-user mode.”

TITLE: Connect to Server
——————————
Cannot connect to (local)\SQL2k8R2.
——————————
ADDITIONAL INFORMATION:
Login failed for user ‘Contoso\demouser’. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18461&LinkId=20476
——————————
BUTTONS:
OK
——————————

  • Connect to SQL Server and add desired account in SysAdmin role.
    I normally prefer to do it from SQLCMD (that’s why I added SQLCMD after m)
    • Open administrator command prompt. (i.e. right click on command prompt shortcut and choose “Run As Administrator”
    • Type sqlcmd –S <complete instance name> For example
      • sqlcmd –S. (for default instance)
      • sqlcmd –S.\MyInstance
        For getting exact name, your my earlier blog
    • You are connected as System Administrator, because you are part of local administrator group in windows.
    • At this point you can add any account to sysadmin because you are connect as sysadmin. Here is the script I normally use to add local administrator group as a part of SysAdmin group in SQL Server Instance. You may want to tweak this as per your needs because I am adding all local admin as sysadmin which is not a good practise.

USE [master]
GO
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
EXEC master..sp_addsrvrolemember @loginame = N’BUILTIN\Administrators’, @rolename = N’sysadmin’
GO

  • Here is how it would look on sqlcmd command prompt

SysAdminLost_6

  • Stop SQL Server Service. You can use any method as described in first step
  • Start SQL Service normally. This means that you need to remove startup parameter

Here is the MSDN reference for above, just to show that its documented.

So, to conclude, I have not shown any trick to recover any password. Just showed you detailed steps to gain sysadmin access provided you have windows admin rights.

DISCLAIMER: Use the method that is described in this article only as a failure recovery mechanism

Posted in Configuration Manager, ERRORLOG, forgot sa password, Forgot the SA password in Sql Server 2005, sa password lost, screen shot, Screenshot, Security, SQL Server, Step by Step | 9 Comments »

Help: Getting Visual Studio Service Pack Error while installing SQL Server 2012 Release Candidate 0 (RC0)

Posted by blakhani on December 8, 2011


To make my AlwaysOn demo up-to-date with latest release, I uninstalled SQL Server 2012 (a.k.a. Denali) CTP3 from my machine(s) and and started to install SQL Server 2012 RC0.

Setup started fine and later after doing feature selection and then I received this error. Interestingly I didn’t receive error another machine both were made from same image.

01_VSErrorBlog

Here is the text of the error message for better search.

—————————
Rule Check Result
—————————
Rule "Prior Visual Studio 2010 instances requiring update." failed.

This computer has an installation of Visual Studio 2010 that requires a Service Pack 1 update that is needed for a successful installation of SQL Server based on your feature selection. To continue, install the required Visual Studio 2010 Service Pack 1 from SQL Server media or from http://go.microsoft.com/fwlink/?LinkID=220649.
—————————
OK  
—————————

one might ask, I don’t have Visual Studio product installed, why do I get this error. Well, SQL Server Tools (SSMS, BIDS) uses Visual Studio Shell. But, I thought that why setup didn’t do this check on earlier screens. Later, looking at the code I realized that we do this check if we install components which need Visual Studio Shell (like SQL Server Management Studio)

Here is the public proof of my theory from setup logs. You will also understand what all we check over here.

<Detail.txt>
(07) 2011-12-07 22:46:51 Slp: Loading rule: DEV10RTMDetected
(07) 2011-12-07 22:46:51 Slp: Creating rule target object: Microsoft.SqlServer.Configuration.SetupExtension.DetectInstalledProductsFacet
(07) 2011-12-07 22:46:51 Slp: Rule applied features  : BIDS;SSMS
…
…

(12) 2011-12-07 22:46:51 Slp: Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.DetectInstalledProductsFacet
(12) 2011-12-07 22:46:51 Slp: Package ID VSIntShellRTM_Cpu32: NotInstalled
(12) 2011-12-07 22:46:51 Slp: Package ID VSIsoShellRTM_Cpu32: Installed – Version: 10.0.30319
(12) 2011-12-07 22:46:51 Slp: Package ID VBExpress_Cpu32: NotInstalled
(12) 2011-12-07 22:46:51 Slp: Package ID VCSExpress_Cpu32: NotInstalled
(12) 2011-12-07 22:46:51 Slp: Package ID VCExpress_Cpu32: NotInstalled
(12) 2011-12-07 22:46:51 Slp: Package ID WPExpress_Cpu32: NotInstalled
(12) 2011-12-07 22:46:51 Slp: Package ID VSPremium_Cpu32: NotInstalled
(12) 2011-12-07 22:46:51 Slp: Package ID VSPro_Cpu32: NotInstalled
(12) 2011-12-07 22:46:51 Slp: Package ID VSTE_Cpu32: NotInstalled
(12) 2011-12-07 22:46:51 Slp: Package ID VSUltimate_Cpu32: NotInstalled
(12) 2011-12-07 22:46:51 Slp: Package ID VSLabAgent_Cpu32: NotInstalled
(12) 2011-12-07 22:46:51 Slp: Package ID VSTestAgent_Cpu32: NotInstalled
(12) 2011-12-07 22:46:51 Slp: Package ID VSTestController_Cpu32: NotInstalled
(12) 2011-12-07 22:46:51 Slp: Package ID VWDExpress_Cpu32: NotInstalled
(12) 2011-12-07 22:46:51 Slp: The package ‘VSIsoShellRTM_Cpu32′ is installed.
(12) 2011-12-07 22:46:51 Slp: Rule ‘DEV10RTMDetected’ detection result: AreAnyProductsInstalled= True
(12) 2011-12-07 22:46:51 Slp: Evaluating rule        : DEV10RTMDetected
(12) 2011-12-07 22:46:51 Slp: Rule running on machine: ALWAYSON-SRV4
(12) 2011-12-07 22:46:51 Slp: Rule evaluation done   : Failed
(12) 2011-12-07 22:46:51 Slp: Rule evaluation message: This computer has an installation of Visual Studio 2010 that requires a Service Pack 1 update that is needed for a successful installation of SQL Server based on your feature selection. To continue, install the required Visual Studio 2010 Service Pack 1 from SQL Server media or from http://go.microsoft.com/fwlink/?LinkID=220649.
</Detail.txt>

DEV10RTMDetected is the rule name and it would fire only if you select BIDS and/or SSMS, we would fire the rule and block installation on failure. Now why it did not come on another machine because I didn’t select tools there.

Okay, now you may ask – what I need to do now? Well, have a look at error message. It tells you the action needed. So here are the solutions.

  • Go to http://go.microsoft.com/fwlink/?LinkID=220649 and download SP1. As per note on the link

    ”This web installer downloads and installs Visual Studio 2010 Service Pack 1. An Internet connection is required during installation”

  • Other better way is to use the setup media. Yes, you read is correctly, SQL Server 2012 RC0 setup media. Below is the location of the package to install Visual Studio 2010 SP1

 
02_VSErrorBlog

Once you start installing this patch, below is what you would see.

03_VSErrorBlog

If you have not cancelled the setup after seeing the message, you can use “Re-run” button to run the check again

04_VSErrorBlog 

And you are green!!

05_VSErrorBlog

Now, go ahead with the setup and post a comment here if this helped.

Cheers,
Balmukund Lakhani
Twitter @blakhani

Posted in Denali, Error, Installation, RC0, screen shot, Screenshot, SQL Server 2012, SQL Server RC0, Visual Studio | 14 Comments »

Virtual TechDays Slides on AlwaysOn and Good Practices

Posted by blakhani on September 29, 2011


Today I was fortunate enough to deliver two online sessions via virtual techdays. I have been asked to share the slide deck. So here I am uploading the presentation.

1. Tips for Successful SQL Server Deployment in Enterprise Environment.pptx
Abstract : SQL Server performs best when it is setup keeping all the best practices in consideration. In this session we will learn various deployment tricks and tips which will SQL Server to perform at its best all the time. Presenter will demonstrate few tricks which are not common but can improve performance dramatically.

2. High Availability – A Story from Past to Future.pptx

In this session we will go over various aspect of High Availability. This feature has moved a long way and have taken different shape during the years. We will understand the features past capabilities and how it has been enhanced with new versions ‘Denali’. Here, I have covered below topics
a. Log-Shipping
b. Database Mirroring
c. AlwaysOn features like Backup on Secondary, Readable Secondary, Multiple Secondary to name a few.

The recording and ppt would be officially available after some days on www.VirtualTechdays.com

Hope you would enjoy it!

Regards,
Balmukund Lakhani
Twitter @blakhani

Posted in AlwaysOn, Denali, SQL Server | Leave a Comment »

Help : Where is SQL Server ErrorLog?

Posted by blakhani on June 26, 2011


While helping unknown faces via MSDN SQL Server Forum, I have asked many times to share the “SQL Server ErrorLog”. Most of the time, I need to explain where the ErrorLog(s) are located. so this post is to help those who are new to SQL Server. Well, if you ever call Microsoft SQL Support for any problem with SQL, they (including me) would ask SQL ErrorLog.

You may ask “What is ErrorLog file?”
SQL Server maintains its own error logs that contain messages describing informational and error events. I personally don’t like name of the file “ErrorLog” as this file does not contains only errors, it also contains information messages. This is the reason we have added additional text in information message called “This is an informational message only. No user action is required”.

  • Location of Errorlog when SQL Server is running and you are able to connect:
    • Connect to SQL Server using SQL Server Management Studio by providing correct name. To find the name to connect, refer my earlier post
    • In Query window run below command
      sp_readerrorlog
    • Here is the output

    ERRORLOG

    • Highlighted area “Logging SQL Server messages in file ‘D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2K8R2\MSSQL\Log\ERRORLOG’” is current ERRORLOG file 
    • If you look at folder D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2K8R2\MSSQL\Log\ here are files.

                          LOG Folder

          As you can see above that LOG folder contains many files. I have provided little information about them in picture.

  • Location of Errorlog when SQL Server is running and you are NOT able to connect:

    There could be situations where you are not able to connect to SQL Server because SQL Server service is not getting started. In those situations, sp_readerrorlog can’t be run. Now, we have to find ERRORLOG file using other technique. Here we go.

    • Open SQL Server Configuration Manager:
      Go to Start > All Programs > Microsoft SQL Server 2005 (or 2008) (or 2008 R2) > Configuration Tools > SQL Server Configuration Manager
      SQL Server Configuration Manager
    • Once you open Configuration Manager, you would get below screen. There you need to locate your SQL Server Instance, right click and properties.

      Configuration Manager

    • Here is the Properties window. Come to Advanced tab & Click on drop down for “Startup Parameters” value and you would see three parameters (can be more also). As shown below there would be –d, –e and –l parameters. Value after –e is your SQL Server Error Log location.

      SSCM Properties

Now if someone is asking ERRORLOG file, please provide using above two methods. You can also get it from SQL Server Management Studio, as described here 

Hope this would help someone in the world!

Cheers,
Balmukund Lakhani
Twitter @blakhani

Posted in Configuration Manager, ERRORLOG, SQL Server | 11 Comments »

Help : What’s my SQL Server Name?

Posted by blakhani on June 19, 2011


“What’s in a name? That which we call a rose by any other name would smell as sweet.” – William Shakespeare

Well, SQL Server connectivity did not know about Shakespeare Hot smileWhile working on the forum, one of the most common error posted is below:

TITLE: Connect to Server
——————————
Cannot connect to <Shakespeare>.
——————————
ADDITIONAL INFORMATION:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476
——————————
BUTTONS:
OK
——————————

Let’s look closely at the error message.
1. The server was not found or was not accessible.
2. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
3. error: 26 – Error Locating Server/Instance Specified.

All of the above are shouting out loud that “Damn! you have given wrong name to connect”. If you have given right name then check if SQL is up and running and then whole troubleshooting starts. The purpose of this post is to help the beginners who don’t care about troubleshooting because they are Visual Studio developers and they are connecting to local SQL Express most of the time.

Okay my dear developers, don’t worry… take a deep breath and start…

To get the SQL Server Name, the first thing you need to do is to find the instance name. On a single machine you can install 16 instances of SQL Servers. Each would have some name. To find the instance name of a SQL Server instance, please follow the steps below:

  • Open SQL Server Configuration Manager (SSCM) from Start – > All Programs -> Microsoft SQL Server 2005/2008/2008 R2 -> Configuration Tools.
  • In the SSCM, click SQL Server Services under SQL Server Configuration Manager (Local), you could find list like SQL Server (InstanceName),
  • The InstanceName is what we need to find, instance name. If you install multiple instances on the server, you will see more than one SQL Server (InstanceName) listed.

image

In above picture you can see two “SQL Server” in “Service Type” column. It means I have two instances of SQL Server. For default instance you would see Name as SQL Server (MSSQLServer). It means on my machine I have two “named” instances of SQL Server. Keep in mind that you can have only only default instance on a machine.

Okay great! Now you know that your machine has default instance or named instance. And if it’s named then you know its name. Correct? Now lets quickly see what name you should use to connect. Before that you need to find hostname or computer name. Go to Start > Run > cmd This would open command prompt. Type hostname and you should see your machine name as below.

image

Default Instance Named Instance Comments
BLAKHANI BLAKHANI\SQLEXPRESS
BLAKHANI\SQL2K8R2		 this would work from remote machine also.
(LOCAL) (LOCAL)\SQLEXPRESS
(LOCAL)\SQL2K8R2		 local only
. .\SQLEXPRESS
.\SQL2K8R2		 local only
LOCALHOST LOCALHOST\SQLEXPRESS
LOCALHOST\SQL2K8R2		 local only
BLAKHANI.MYDOMAIN.COM BLAKHANI.MYDOMAIN.COM\SQLEXPRESS
BLAKHANI.MYDOMAIN.COM\SQL2K8R2		 Remote connections should be allowed as mentioned in http://support.microsoft.com/kb/914277 (

How to configure SQL Server 2005 to allow remote connections)
TCP:221.222.223.224,1433 TCP:221.222.223.224,9999 IP and Port can be found by reading ERRORLOG of SQL Server.

 

If you don’t see configuration manager then you can also get the instance name from services applet. To do this, go to Start > Run > Services.msc
Here is what i see on my screen

image

Hope this would help someone in the world!

Cheers,
Balmukund Lakhani
Twitter @blakhani

Posted in Connectivity, SQL Server | 18 Comments »

Help : Can I Upgrade SQL Edition without reinstall?

Posted by blakhani on June 3, 2011


And answer is “it depends”. Well, I have seen posts in forum asking steps to upgrade SQL Standard to SQL Enterprise OR SQL Developer to SQL Standard etc. So I am writing this blog to bring some clarity in steps to be performed.
First of all, refer below matrix to know if in place edition upgrade is possible or not.

http://msdn.microsoft.com/en-us/library/ms143393.aspx (SQL Server 2008 R2)
http://msdn.microsoft.com/en-us/library/ms143393(v=SQL.100).aspx (SQL Server 2008)
http://msdn.microsoft.com/en-us/library/ms143393(v=SQL.90).aspx (SQL Server 2005)

Above links are for version upgrade as well as edition upgrade. Now, if matrix says you can upgrade from edition X to edition Y then steps are pretty simple.
For SQL 2008 and SQL 2008 R2:

  • Run setup.exe from media.
    • If you have SQL already installed then you can also launch “Installation Center” by going to Start->Programs ->Microsoft SQL Server 2008[R2]->Configuration Tools-> SQL Server Installation Center[(64-bit)].
  • You would get below screen titled “SQL Server Installation Center”
  • Go to “Maintenance” and click on “Edition Upgrade”

  • Go through the Wizard and choose the Instance you want to upgrade.

It would hardly take few minutes to upgrade and I have done with many customer.

One question which was asked many times is where the hell is the key which i should enter. If you have media, look for DefaultSetup.ini file and you would find PID there.

For SQL 2005:
I would update this post once I take screenshot from SQL 2005 setup.

Cheers,
Balmukund Lakhani

Posted in Edition Upgrade, SQL Server, Upgrade | 3 Comments »

Help: SQL Server Evaluation Edition Expired!

Posted by blakhani on June 1, 2011


Many time in forums, I have seen question about Enterprise Evaluation Edition expiration. So idea of this blog post is to avoid repetitive screenshots (which i love to put in my replies as they are more powerful). So here we go…

If you have SQL Server 2005/2008/2008 R2 Enterprise Evaluation edition and its already expired then you may see below error message when you open SQL Server Management Studio.

Here is the text of the message for better search.

Microsoft SQL Server Management Studio:
Evaluation period has expired. For information on how to upgrade your evaluation software please go to http://www.microsoft.com/sql/howtobuy

There could be two possibilities.

  • SQL Server is really really expired (This is most common question in forum)
  • SQL Server 2008 has been upgraded to full version but tools are not working. This is a known issue which i have blogged here

Now, if SQL 2005 is expired then you have no choice other than uninstall/reinstall SQL Server (of course you can reuse mdf and ldf files). In this blog I am not going to cover SQL 2005 upgrade (come on, its 6 years old product when I was writing this post). If SQL 2005 is not expired then you can perform edition upgrade by putting CD/DVD of new edition and choosing proper instance .

From SQL 2008 onwards, you can perform edition upgrade even if its expired. Cool! Isn’t it? Well, this feature was introduced by hearing feedback from customers. Here are the steps to do it.

  • Launch the Setup.exe from the DVD/Shared Location. You would get below screen titled “SQL Server Installation Center” .
    • If you have SQL already installed then you can also launch “Installation Center” by going to Start->Programs ->Microsoft SQL Server 2008[R2]->Configuration Tools-> SQL Server Installation Center[(64-bit)].

image

  • Click on “Maintenance” and choose “Edition Upgrade”
  • Go through the Wizard and choose the Instance you want to upgrade.

Simple, huh?

Fewer times I have been asked if I need to download mount/extract complete ISO to run edition upgrade? Well, here is the trick. Media for all the edition is same. Its the PID which decides which edition to be installed. PID is picked from a file called DefaultSetup.Ini (DVD\x86 or x64 or ia64 folder). It essentially means that you can extract that file from ISO image, run setup of Evaluation media and in below screen type the PID manually.

image

Hope this helped someone today!

Cheers,
Balmukund Lakhani

Posted in Evaluation, Expired, SQL Server | 8 Comments »

 
Follow

Get every new post delivered to your Inbox.

Join 858 other followers