Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 1,882,455 hits
  • Select GETDATE()

    May 2021
    M T W T F S S
     12
    3456789
    10111213141516
    17181920212223
    24252627282930
    31  

Posts Tagged ‘SQL Server Management Studio’

SSMS Tip: What happened to my keyboard shortcuts? Ctrl+R (Show/Hide Result) and Ctrl+E (Execute) not working!

Posted by blakhani on May 8, 2014


Long back I installed SQL Server 2014 to learn new features and of course share the knowledge. I started from CTP1, CTP2 and finally came to RTM. Earlier I was installing them on Virtual Machines and recently I installed SQL 2014 on my laptop which already has SQL Server 2012 installed.

While using SSMS of SQL Server 2014, I noticed that earlier keyboard shortcuts were not working as they used to work earlier (in SQL 2012 SSMS). Few examples are as below:

  • Ctrl + R should show/hide result pane.
  • Ctrl + E should execute the query.

If I use above, I was getting below message in the left bottom of SSMS. “(Ctrl+R) was pressed. Waiting for second key of chord…

image

Pressing Ctrl+R again I get message as “The key combination (Ctrl+R, Ctrl+R) is not a command

Same was the case with Ctrl+E command. “(Ctrl+E) was pressed. Waiting for second key of chord…” . If I take my mouse to “Execute” button, it guided me that shortcut to execute command is Ctrl+Shift+E. And it works as well.

image

But I always preferred to go back to earlier settings. There are multiple ways to achieve it.

Short Route

If you have not done any customization in SSMS and you are OK to reset all the settings then you can use this. In Management Studio menu, go to Tools –> Options –> Environment –> Keyboard –> Keyboard –> Apply the following additional keyboard mapping scheme” and choose “Reset”. You would get a confirmation pop-up, click OK there.

image

Long Route

This route would be preferred if you have done some customization with the settings and want to retain those setting. you can provide shortcuts to any action. Go to Management Studio Tools menu and Tools –> Options –> Environment –> Keyboard –> Keyboard Over there, you can choose the command and assign the “shortcuts for selected command”. The list of command can be searched. For example, I have entered “Window.ShowResultsPane” and assigned Ctrl+R to it as below. Make sure to click on “Assign” button before hitting OK.

image

In Same way , Ctrl+E can be assigned to “Query.Execute

Documentation: http://technet.microsoft.com/en-us/library/ms174205(v=sql.120).aspx (SQL Server Management Studio Keyboard Shortcuts)

More reading: http://blogs.msdn.com/b/managingsql/archive/2011/07/13/enhanced-keyboard-shortcuts-in-ssms-in-denali.aspx

Hope this helps! Please write comment and let me know your feedback.

Cheers,
Balmukund

Posted in SQL Server Management Studio, SSMS | Tagged: , , , , , , | 15 Comments »

SSMS Tip: Merging ERRORLOGs from different machines using Log File Viewer

Posted by blakhani on March 6, 2014


For those who don’t know what/where is SQL Server ERRORLOG, please read this

Log File Viewer is an interesting feature of Management Studio and I have been ignoring it (you too??). This tool as saved a lot of time for me so thought of sharing some cool features of this tool. This can load various logs including system event log, application event log, DB Mail Log and SQL Agent log.

As a part of my regular work, I need to look at SQL Server ERRORLOG to look for various information about environment like SQL version and Edition, Processors and memory etc. If you want to know where ERRORLOG is located, then read here.

While working on a case on AlwaysOn Availability Group, customer provided us errorlog from two machines. To complete my analysis, I have to merge the file from both servers so that I can look at series of event happening across the server. Since customer was not having access to physical machine, he used management studio to “export” the errorlog.

Here are the steps to export the log using “Log File Viewer”

image

Once the viewer is opened, it opens current ERRORLOG file from the server. If we want to see more we can select checkbox on the left hand tree as highlighted below.

image

Export button can be used to Save the content on local machine. This is useful when we don’t have access to physical server to get file.

To demonstrate “merge”, I have exported current ERRORLOG from three servers and saved them as ERRORLOG_SQLPAPA, ERRORLOG_SRV1 and ERRORLOG_SRV2. I have kept all files in same machine and launched SSMS there. First, we need  to load the log as shown below.

image

One all are loaded, we can see them on left hand tree. Select the checkbox if it has to be loaded on right side grid.

image

I have selected all three files. Now, in the grid there is one interesting column, Log Source which shows the file which is loaded. I have dragged the column header and moved to visible location.

image

If we sort by date column, we can easily see the series of message in errorlog based of time. Since we have “Log Source” column, it possible to find server name as well. That’s why I have given File Name which is meaningful. In above screenshot I can see that top 3 messages are from different servers and I know the time as well.

You can be more creative and use “Filter” to load only specific data which you are interested.

Hope you liked this cool feature. Please provide feedback via comments.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in SQL Server Management Studio, SSMS | Tagged: , , , | 1 Comment »

    Troubleshooting: Cannot execute as the database principal and Property Owner is not available for Database

    Posted by blakhani on March 4, 2014


    No one likes errors in the world. As a part of my work with Microsoft SQL Support Services, I get to see various errors, find cause and provide solution to get rid of them. Today someone reported below error to me.

    Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

    This error message was caused because SQL Server was not able to find context information for the security logon we were attempting to impersonate. In this case it was “dbo”. Going with common sense, I went ahead to check the owner of the database, which is generally visible in database > right click > properties. As soon as I attempted it, I was welcomed with next error message.

    image

    TITLE: Microsoft SQL Server Management Studio
    ——————————
    Cannot show requested dialog.
    ——————————
    ADDITIONAL INFORMATION:
    Cannot show requested dialog. (SqlMgmt)
    ——————————
    Property Owner is not available for Database ‘[SQLServerHelp]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1746.0+((KJ_RTM_QFE-CU).101005-1216+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=Owner&LinkId=20476
    ——————————
    BUTTONS:
    OK
    —————————— 
     

    Okay. This is an interesting message. Notice the exception event source is “PropertyCannotBeRetrievedExceptionText” and exception event id “Owner”. The message means that on SMO, the value of “owner” property is not available. This means something is not right with owner of the database?

    Next step was to find out who the current owner (without management studio). There are multiple ways to do it.

    • sp_helpdb

    image

    • sp_helpdb ‘DatabaseName’

    image

    • T-SQL which is used by sp_helpdb
    select name, isnull(suser_sname(sid),'~~UNKNOWN~~'), 
    convert(nvarchar(11), crdate),
    dbid, cmptlevel from master.dbo.sysdatabases
    

     

    image

    As we can see above, “~~UNKNOWN~~” is not something which is not expected and that’s the reason SSMS was showing exception to get owner property.

    How to fix it?

    Of course, change the owner of the database so that value of SID column holds valid SID. Since we are not able to use UI, we have to use sp_changedbowner (change db owner with no spaces). This has be to run in the context of the database which has problem, as shown below. I have given sa as an example for my database.

    image

    Root Cause/Repro of the issue

    If a database is owned by a login, we would not be able to drop the login. The possible situation which I can think of are:

    • Database owned by windows login which was part of any Windows Group. And now, windows account was deleted from active directory. 
    • Database was restored and domain controller was not available to resolve the SID to a validate login.

    In my repro, I was able to get into same situation by step 1.

    Important: I was NOT able to reproduce the “property owner” error in SQL Server 2014 Management Studio. It doesn’t throw exception of owner property and it can be blank in the UI.

    image

    This also means that owner can be changed via management studio by going to Properties  > Files tab.

    image

    Hope you have learned something new.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in Error, SQL Server, SSMS | Tagged: , , , , , | Leave a Comment »