Shrinking a large database with limited outage windows — SQL Studies

Quote

Yes, I realize you shouldn’t shrink your database (data files or log files), but I recently had a case where it was completely appropriate to do a shrink. We had a table that was almost a terabyte on its own. It was decided to put in an archive process that got rid of 3/4 of […]

via Shrinking a large database with limited outage windows — SQL Studies

Microsoft Explorer in SSMS

SQL Server Management Studio (SSMS) contains several tools that many people are not aware are available and can make you more productive in your job.  For instance, did you know that SSMS has a version of Microsoft Explorer that will open in a query tab?

While working in SSMS, there may be a need to refer to or browse the internet to answer questions about something you are working on, locate syntax or existing queries that will assist in resolving an issue you have or maybe view MSDN Library help topics.  Rather than leaving SSMS and opening a web browser, users can open a web browser page in SSMS.  It will open to the default Microsoft SQL Server website.  There are a few options you can use to open a web browser from inside SSMS:

  • From the keyboard: CTRL+ALT+R
  • From the Web Browser icon on the toolbar

WebBrowser

 

  • From the View Menu

WebBrowser2

 
So, what are some of the uses for a web browser in SSMS that can help you be more efficient?  One handy thing you can do is embed links in your code that you may need to reference.  You can hover over the link, select CTRL, and click on the link to open up the site.

CTRLClick

 

This is a great tool if you are a new DBA or are working on something that you do not have a lot of experience with.  One simple example, when creating a new table using T-SQL, have the data type web page open next to your code to make it easier to reference the various data types.  This will make you much more efficient that going back and forth between SSMS and your web browser outside of SSMS.

VerticalExample

 

There are many other ways you can use this tool.  Sometimes you might have something that you need to search the internet for information.  Rather than switching back and forth between SSMS and your external web browser, it is all right there for you to access.

 

Results to Grid

What is Results to Grid and what can it do for you?  Results to Grid are Query Results options in SQL Server Management Studio (SSMS) that can help users customize their query results in a variety of ways that can help make users more efficient.  Some of these might be little changes, but when used often throughout the day, they can make a big difference.  Once you change the setting, you will need to open a new query window for the change to go into effect.  These changes can be made at:

Tools > Options > Query Results > Results to Grid

ResultsToGrid

 

Include Query in the Result Set

The default for Include Query in the Result Set is off.  When toggled on, the query is included in the messages tab of the results.

QueryInResult

Include Column Headers When Copying or Saving the Results

The default for Include Column Headers When Copying or Saving the Results is off.  When it is toggled on, the column headers are copied or saved with the output.  To copy the results, you would highlight the upper left corner of the result set and right click.  You can select Copy or Copy with Headers.  (You can also use CTRL+C (copy) or CTRL+SHIFT+C (copy with headers).)  Either way, the column headers will be copied along with the data.

When you save the query results (Save Results As…), there is not an option to include headers from the menu.  To include columns headers when saving data, this feature must be toggled on and it must be turned on prior to running the query for the Column Headers to be included in the output.

ColumnWhileCopying

 

Retain CR/LF on Copy or Save

When copying information into Notepad, the carriage return and line feeds from the text are not always carried over.  In the example below, I will show the difference in the results when this option is toggled on and off.

Note the two queries.  They are slightly different to make it easier to identify the results.  The query on the left has the Retain CR/LF on Copy or Save toggled on, the one on the right has the feature toggled off.  Looking at the results of each query, they are the same in the Results tab.  The difference occurs when you copy the data into another format, such as Notepad.

CR_LF

Discard Results After Execution

Really…discard the results after execution of a query.  Isn’t the point of running the query to get the results of the query?  Not always.  Suppose you need the results of the execution plan and not necessarily that of the data.  That would be one situation where you would not care about the result set.  By toggling on this option, you will save on memory but not CPU or IO.  If you are working with a large data set, this could also save you time as you do not have to wait for the dataset to load before getting the execution plan.  This is primarily a tool to use for troubleshooting.

DiscardResults

Display Results in a Separate Tab

The default setting in Query Editor is with Editor pane at the top and the Results and Messages pane at the bottom of the window.  Users can also set up the Results and Messages panes to appear at the top of the query window, allowing more space for results.  With the results in a separate tab, the tabs are on the same horizontal plane and it is easier to switch from the Editor to the Results to the Messages because they are all together.  To make this change, go to the following and toggle on ‘Display Results in a separate tab’.

The images below show the default Results pane at the bottom of the Query Window (Left) and with the results in a separate tab (Right).

SeparateTab

 

There is a setting under the Display Results in which you can toggle on: ‘Switch to the results tab after the query executes’.  With the display panes in separate, horizontal tabs, you can’t see the results after running a query like in the default format without switching to the Results pane.  This feature will save you that step of running the query then tabbing to the Results pane.  The Results tab will automatically open when executing the query.  The two options really go hand-in-hand.

 

AutoRecover in SSMS

 

Periodically there is a crash, power surge, or sudden reboot of your computer.  Of course, you had SQL Server Management Studio (SSMS) open and you were working on something important.  That seems to be the only time there is a crash/reboot.  You can lose work that that was open in SSMS but has not saved.  There is an AutoRecover feature in SSMS so all may not be lost.  The AutoRecover default settings in SSMS are:

Save AutoRecover information every 5 minutes

Keep AutoRecover information for 7 days

With the default settings, you can lose up to 5 minutes of work on a sudden reboot.  The recovered information will be kept for 7 days so there is some time to get your files.  These settings can be modified, changing how often auto recover happens and how long the information is retained.  It depends on the criticality of things that you work on as to how often you would want AutoRecover to save your scripts.  This feature can be turned off but it is not recommended.  To change the settings, go to:

Tools -> Options -> Environment -> AutoRecover

AutoRecover

 

When opening SSMS after that unexpected shutdown, SSMS tries to recover the unsaved queries and prompts a dialog box to the user.  You have the choice to recover files at that time.  If you skip over that step or the dialog box does not appear on re-open, the AutoRecovered files can be found on the server.  Navigate to the folder where the backup files are located and recover files
from there.

AutorecoverLocation

 

The settings in SSMS will determine what is available to recover and when it was last saved.  You don’t want AutoRecover to constantly run, but it needs to be set to a time that is appropriate for you and your job.

Running Away with Databases: T-SQL Tuesday #113

 

Todd Kleinhans (b/t) is the host for T-SQL Tuesday this month.  He has asked us to discuss how we use databases in our everyday life.  I have been asked by friends to help them with databases covering a wide variety of topics.  For myself, I haven’t created many personal databases, that is until I started doing destination runs.

There are two things that I like to do in my free time: running and vacationing.  I am an avid runner and I am always looking for new runs, usually half marathons, to do in different cities and countries around the world.  A few years ago, I decided to do a half marathon and started searching on the internet for good runs in fun locations.  That is when I fell in love with the destination run.

It makes for a great way to see new places and I can pair that with a run.  I love to wander through new cities and explore.  Most runs take you past historic places or beautiful scenery.  Plus, after running a half marathon, it is a lot easier to explore local restaurants and bakeries without feeling too guilty.  That way I get a real taste of the area.

 

Picture1

 

After my first destination run, I created an Excel spreadsheet to track runs I might want to do in the future.  I found that I needed a way to organize the runs of interest for future reference.  There are so many interesting runs that it is hard to remember them when I am ready to register for a new run.  I get recommendations for runs from Twitter friends, top runs provided by running magazines and websites, local running groups, along with many other places.  I needed help keeping them organized.

Keeping track of those runs couldn’t be enough.  That spreadsheet has grown into a SQL Server database.  The database helps me keep organized and helps me pick my next vacation location.  Deciding on my next run takes a lot of time.  But it is not stopping there.  Currently, this is a very simple database but I am in the process of expanding it to include local races, training miles, as well as health stats.  This will eventually become my training partner database that will be customized to track everything that I want to track.  There are apps to track some of this information, but not everything that I want in one place.