Delete servers from the list on SQL Server 2012 - Logon Screen

You might have seen below screen countless times and you might wonder what is there isblog about in this simple screen. Well, continue reading and you would get the answer.

Many times, DBA have to login to production server from non-regular machine, may be a developer’s workstation. Once you login to SQL, do your work and close the management studio. Do you know that your server name is saved in management studio? Of course, very useful feature because you may not like to type server name/IP address every time. Whatever servers you have connected, it would be stored by management studio. Butsometime, it’s annoying!

What you would do if you want SQL Server Management Studio to forget “all” the servers listed in drop down of Server name? To do that, you need to know how and where it’s stored. You can use one of my favorite tool from sysinternals called Process Monitor (also known as ProcMon) and easily figure out that this is stored in a file under your windows user profile.

Below is the file in SQL 2008 R2 Management Studio.

%appdata%\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin

For SQL Server 2012, here is what we can see in ProcMon

So, the path is

%appdata%\Microsoft\Microsoft SQL Server\110\Tools\Shell\SqlStudio.bin

So far, you might wonder, where is the new feature? I have been asked by many users to delete entries from SSMS “Connect to Server” server name list. Well, unofficially, you can delete the file directly which we found via ProcMon. Note that delete file to get rid of server list is not officially supported by Microsoft.

Better way to achieve this is provided in SSMS 2012. To delete the servers from the list, highlight the name we want to delete (via keyboard or mouse) and then press delete key via keyboard. We can’t be multi-select and has to be done one by one. We can delete as many entries we want. I have delete few from first screenshot taken and here is the modified version.

This is not available in SQL 2008 R2 and its previous version. This came from feedback given to SQL Server Product group.

Hope you have learned something new today!

SQL SERVER – Reseed Identity of Table – Table Missing Identity Values – Gap in Identity Column

Hi Friends,

The search of this article was led by while I was finding an answer to my problem. [Which I have already posted here.]

Sometimes it is required to remove gaps in identity column series. In such situations this article can help people.

Read full article here...

Thanks,
Vihang Shah.

SQL SERVER – DBCC RESEED Table Identity Value – Reset Table Identity

Hi Guys,

Today I faced an issue, when I migrated data from 1 DB to another DB using simple insert queries on cross DBs via SQL Server Management Studio. The identity column created an issue for my case here. Then I realized to truncate table and start inserting new records, which leads me to a surprise of "IDENTITY NOT GETTING RESET" with new records.

Then, my surf starts and finally Mr. Pinal Dave answered me with his fantastic blog post here. Which taught me a good thing today of RESEEDING Table Identity Value.

I am writing this article just to share this to all my blog lovers & friends, so that this may help you, too.

Hope this will help all my friends,

Update:
Guys, I have also written another article on this topic here. May this also help you out.

Thanks,

Vihang Shah.

SQL SERVER – Script to Update a Specific Column in Entire Database

“Pinal,

In our database we have recently introduced ModifiedDate column in all of the tables. Now onwards any update happens in the row, we are updating current date and timeto that field.

Now here is the issue, when we added that field we did not update it with a default value because we were not sure when we will go live with the system so we let it be NULL. Now modification to the application went live yesterday and we are now updating this field.

Here is where I need your help. We need to update all the tables in our database where we have column created ModifiedDate and now want to update with currentdatetime. As our system is already live since yesterday there are several thousands of the rows which are already updated with real world value so we do not want to update those values. Essentially, in our entire database where ever there is a ModifiedDate column and if it is NULL we want to update that with current date time? 

Do you have a script for it?”

For reply read here...

SQL SERVER – Select and Delete Duplicate Records

Developers often face situations when they find their column have duplicate records and they want to delete it. A good developer will never delete any data without observing it and making sure that what is being deleted is the absolutely fine to delete. Before deleting duplicate data, one should select it and see if the data is really duplicate.

A very nice post & video. Read more here.