SQL SERVER – CTRL + R Shortcut Does Not Work in SSMS

I just formatted my computer and installed fresh SQL Server 2014. The very first thing which I did was to open SQL Server Management Studio and execute a sample query. As soon as I executed the query, it returned me some result. Once I did not need the result, my next instinct was to hide the result as I did not need it any further. I am very used to the keyboard shortcut CTRL + R to hide the result. As soon as I typed in the CTRL + R the result pane was still visible. My pressing CTRL+R did not do anything further.

I suddenly remembered that last time when I had installed SQL Server 2012 I had faced the similar situation when a few of my shortcuts did not work. Here is what I did to make my shortcut work again.

Steps to Get Shortcut working again:

  1.  Go to Tools Menu in SSSMS
  2. Click on Option
  3. Go to Keyboard and expand the menu
  4. Click on option Keyboard again
  5. Keep the option on the drop down – Apply the following additional keyboard mapping scheme as (Default)
  6. Click on Reset

and you are done!

Well, on the internet, there are many different solutions and many of them are valid in different scenarios. In my scenario following above steps had fixed my issue.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

SQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column Value of the Table with Max Value of Datatype – Part 2

Seven years ago, I wrote a blog post about – Query to Find Seed Values, Increment Values and Current Identity Column value of the table. It is quite a popular blog post and lots of people like it as it gives immediate details about Identity Values. SQL expert Mark Hickin has further improved this query and posted an interesting modification where the query also display upper limits of the data type used in the query. You can read the blog post over Query to Find Seed Values, Increment Values and Current Identity Column Value of the Table with Max Value of Datatype.

However, this query has a small limitation that it only works in those cases when increment value is 1 and seed is 1. However, if you have any other value as identity or seed, you will need a bit more modification in the script. SQL Server Expert and Guru Harsh has provided amazing script where he has provided query with the said adjustment.

SELECT Seed,Increment,CurrentIdentity,TABLE_NAME,DataType,MaxPosValue ,
FLOOR((MaxPosValue -CurrentIdentity)/Increment) AS Remaining,
100-100*((CurrentIdentity-Seed)/Increment+1) / FLOOR((MaxPosValue - Seed) /Increment+1) AS PercentUnAllocated
FROM (
SELECT IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) AS Seed ,
IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) AS Increment ,
IDENT_CURRENT(TABLE_SCHEMA + '.' + TABLE_NAME) AS CurrentIdentity ,
TABLE_SCHEMA + '.' + TABLE_NAME AS TABLE_NAME ,
UPPER(c.DATA_TYPE) AS DataType ,
FLOOR(t.MaxPosValue/IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME)) * IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) AS MaxPosValue
FROM INFORMATION_SCHEMA.COLUMNS AS c
INNER JOIN ( SELECT name AS Data_Type ,
POWER(CAST(2 AS VARCHAR), ( max_length * 8 ) - 1) AS MaxPosValue
FROM sys.types
WHERE name LIKE '%Int'
) t ON c.DATA_TYPE = t.Data_Type
WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME,
'IsIdentity') = 1
)T1
ORDER BY PercentUnAllocated ASC

Here is the screenshot of the image. Thanks Harsh- a very nice query.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Detecting Corruption with Suspect Pages Table – Notes from the Field #043

[Note from Pinal]: This is a new episode of Notes from the Fields series. I often hear about database corruption and I like to stay away from the problems related to corruption. When database gets corrupted, there are not enough expert out in the world who can fix it properly to its original state. John who is my good friend explains a very interesting situation in this blog post where we can learn about corruption with the help of suspect pages table. It is indeed a great tip for everyone to learn even though your database is not corrupted.

In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very common issue DBAs and Developer faces related to Detecting Corruption with Suspect Pages Table. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.


When I get asked to review a database server instance, a very important check that I perform is to review if corruption has occurred with the existing databases. In today’s tip from the field we are going to go over a simple query that will identify existing corrupted pages without running DBCC CHECKDB.

In SQL Server you will not be notified of corruption until an attempt to read a corrupt page occurs. Therefore a page could have been corrupted weeks ago and no one would have noticed if the page was never accessed since the corruption occurred. We strongly recommend using an automated schedule to check database integrity by running DBCC CHECKDB.

DBCC CHECKDB is a very I/O intensive operation so you shouldn’t run the command at will. It should be executed during defined maintenance windows. Today, you are going to see that we can track down corrupt pages with the query below.

SELECT * FROM msdb.dbo.suspect_pages

Ideally, the result set will return zero rows. Zero rows means that no pages have been identified as being corrupt. It doesn’t guarantee that you don’t have corrupt pages. It just means no corrupt pages have been accessed. If you have results then you just identified pages within database files that have a history of corruption. The error count will let you know how many times the page(s) have been accessed since it was added to the suspect pages table. The event type column will identify the type of corruption.

NOTE: The suspect_pages table in msdb database will only keep 1,000 pages. Hopefully you will not have any rows yet alone more than 1,000 corrupt pages. If you do have more than 1,000 than you would only see the last 1,000 pages in this table.

Keep in mind that the suspect_pages table will only include marked corrupted pages. Corrupt pages are only classified as marked if they have been accessed since the corruption occurred. This is why you need to include DBCC CHECKDB in your maintenance plans.

CALL TO ACTION:

  1. Be proactive and monitor suspect_pages table in msdb database
  2. Implement DBCC CHECKDB in your maintenance plans.

Are your servers running at optimal speed or are you facing any SQL Server Performance Problems? If you want to get started with the help of experts read more over here: Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column Value of the Table with Max Value of Datatype

Seven years ago, I wrote a blog post about – Query to Find Seed Values, Increment Values and Current Identity Column value of the table. It is quite a popular blog post and lots of people like it as it gives immediate details about Identity Values. SQL expert Mark Hickin has further improved this query and posted an interesting modification where the query also display upper limits of the data type used in the query.

SELECT IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) AS Seed ,
IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) AS Increment ,
IDENT_CURRENT(TABLE_SCHEMA + '.' + TABLE_NAME) AS CurrentIdentity ,
TABLE_SCHEMA + '.' + TABLE_NAME ,
UPPER(c.DATA_TYPE) AS DataType ,
t.MaxPosValue,
t.MaxPosValue -IDENT_CURRENT(TABLE_SCHEMA + '.' + TABLE_NAME) AS Remaining,
((
t.MaxPosValue -IDENT_CURRENT(TABLE_SCHEMA + '.' + TABLE_NAME))/t.MaxPosValue) *100 AS PercentUnAllocated
FROM INFORMATION_SCHEMA.COLUMNS AS c
INNER JOIN ( SELECT name AS Data_Type ,
POWER(CAST(2 AS VARCHAR), ( max_length * 8 ) - 1) AS MaxPosValue
FROM sys.types
WHERE name LIKE '%Int'
) t ON c.DATA_TYPE = t.Data_Type
WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME,
'IsIdentity') = 1
ORDER BY PercentUnAllocated ASC

Here is the screenshot of the image. Thanks Mark – a very nice query.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Fix Error 7202 Could not find server in sys.servers. Verify that the correct server name was specified

The other day I received following error when I tried to restore a database from one server to another server.

Msg 7202, Level 11, State 2, Line 1
Could not find server ‘MyLinkedServer’ in sys.servers. Verify that the correct server name was specified.

Well, this is quite a popular error one receive when they attempt to restore database containing references of the linked server. The solution is to create a link server and restore database. Here is the quick script which can fix your error.

EXEC sp_addlinkedserver @server='MyLinkedServer'
EXEC sp_addlinkedsrvlogin 'MyLinkedServer', 'false', NULL, 'MyUserName', 'MyPassword'

Well, that’s it. Sometimes the solution is much simpler, even though the original problem looks complicated.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL Authority News – Secret Tool Box of Successful Bloggers: 52 Tips to Build a High Traffic Top Ranking Blog

When I started this blog, it was meant as a bookmark for myself for helpful tips and tricks.  Gradually, it grew into a blog that others were reading and commenting on.  While SQL and databases are my first love and the reason I started this blog, the side effect was that I discovered I loved writing.  I discovered a secret goal I didn’t even know I wanted – I wanted to become an author.  For a long time, writing this blog satisfied that urge.  Gradually, though, I wanted to see my name in print.

12th Book

Over the past few years I have authored and co-authored a number of books – they are all based on my knowledge of SQL Server, and were meant to spread my years of experience into the world, to share what I have learned with my community.  I currently have elevan of these “manuals” available for sale.  As exciting as it was to see my name in print, I still felt that there was more I could do as an author.

That is when I realized that I am more than just a SQL expert.  I have been writing this blog now for more than 10 years, and it grew from a personal bookmark to a thriving website with over 2 million views per month.  I thought to myself “I could write a book about how to create a successful blog!”  And that is exactly what I did.  I am extremely excited to share with all of you my new book – “Secret Toolbox of Successful Bloggers.”

A Labor of Love

This project has been a labor of love for me.  It started out as a series for this blog – I would post one article a week until I felt the topic had been covered.  I found that as I wrote, new topics kept popping up in my mind, and eventually this small blog series grew into a full book.  The blog series was large enough to last a whole year, so I definitely thought that it could be a full book.  Ideas on how to become a successful blogger were so frequent that, I will admit, I feel like there is so much I left out of this book.  I had a lot more to say than I originally thought!

I am so excited to be sharing this book with all of you.  I am so passionate about this topic, and I feel like there are so many people who can benefit from this book.  I know that when I started this blog, I did not know what I was doing, and I would have loved a “helping hand” to tell what to do and what not to do.  If this book can act that way to any of my readers, I feel it is a success.

Rules of Thumb

If you are interested in the topic of becoming a blogger, as you read this book, keep in mind that it is suggestions only.  Blogging is so new to the world that while there are “rules of thumb” about what to do and what not to do, a map of steps (“first, do x, then do y”) is not going to work for every single blogger.  This book is meant to encourage new bloggers to put their content out there in the world, to be brave and create a community like the one I have here at SQL Authority.  I have gained so much from this community, I wanted to give something back, and this book is just one small part.

I hope that everyone who reads this books finds at least one helpful tip, and that everyone can experience the joy of blogging.  That is the whole reason I wrote this book, and what I hope everyone takes away from it.

Where Can You Get It?

You can get the book from following URL:

Kindle eBook | Print Book

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – How to See Active SQL Server Connections For Database

Another question received via email -

“How do I I know which user is connected to my database with how many connection?”

Here is the script which will give us answer to the question.

SELECT DB_NAME(dbid) AS DBName,
COUNT(dbid) AS NumberOfConnections,
loginame
FROM    sys.sysprocesses
GROUP BY dbid, loginame
ORDER BY DB_NAME(dbid)

Here is the resultset:

Reference: Pinal Dave (http://blog.SQLAuthority.com)