SQL Server Default Backup Location

SQL Server makes it easy to set a server's default data and log locations. In SQL Server Management Studio, simply right click on the top level server node in the Object Explorer and select Properties. Then click on the Database Settings page selector on the left side of the screen. At the bottom of this screen there are text boxes for entering the default Data and Log locations.

Unfortunately it's not as easy to set the default backup location. To do this you need to modify the server's registry. Open the Registry Editor by typing regedit either on a command line or in the Search programs or files box at the bottom of the Windows Start Menu. Once you're in the Registry Editor navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\YourInstanceName\MSSQLServer. Now enter the directory you'd like to be your default. Save and close the Registry Editor.

The next time you attempt to backup or restore a database you will be presented with your new default backup location. No more navigating to a different location over and over again.

Querying System Views for Column Names

I often find myself needing a list of columns from a table or a view for a query, documentation, troubleshooting, etc. Easy enough to drag and drop them into a query window using SSMS but that's a pain. I have better things to do with my time. All of this information is available through SQL Server system views. The following query is also ANSI compliant.

SELECT

  ORDINAL_POSITION
  ,COLUMN_NAME
  ,DATA_TYPE
  ,CHARACTER_MAXIMUM_LENGTH
  ,IS_NULLABLE
  ,COLUMN_DEFAULT
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  TABLE_NAME = 'Customer'
ORDER BY
  ORDINAL_POSITION ASC;

You can do something like the following to give you a comma separated list that can then be used for a query.

SELECT

  COLUMN_NAME + ','
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  TABLE_NAME = 'Customer'
ORDER BY
  ORDINAL_POSITION ASC;

Simple. Straightforward. Time-saver.

How To: Shrink a Log File

Are you in a dev environment and by screwing around with large dml statements you've created a monsert log file that you need to truncate and are not worried about loss of data? Is your database in Simple Recovery Mode? If you answered yes to these questions . . . fire away:

DBCC SHRINKFILE (<Log Name>, 0, TRUNCATEONLY)

If you get an error stating that the log file is in use but you have an immediate need to truncate the log file and don't want to wait you can also do the following:

1) Detach the database
2) Delete the log file from the file system
3) Attach the database and remove the the reference to the log file prior to completing the process in the UI

Upon attach, SQL Server will create a new log file at the minimum size specified.

Don't Forget to Clear Your Buffer!

Sounds like a Colon Cleanse pitch no?

Well it's not. One of the most important things you can do when performance tuning is to remember to clear the procedure cache between test cases.

Don't be fooled by the word procedure. The procedure cache deals with almost all queries submitted to the SQL Server engine, not just those that live in stored procedures. It stores compiled execution plans for later use which helps speed things up by not having to regenerate execution plans when one is already available.

Back to clearing . . .

It's important to clear the cache between query submissions so that we start from scratch when we submit our new query. If we have "left-over" plans in the cache the optimizer won't necessarily create a newly optimized plan thus corrupting your tuning/testing efforts.

DBCC FREEPROCCACHE will clear all execution plans from the cache causing all subsequent SQL statements, stored procs or not, to be recompiled the next time they run.

It's also helpful to run DBCC DROPCLEANBUFFERS to clear the data buffers. This will ensure more accurate testing as all queries will have to retrieve their data from disk. Running CHECKPOINT prior to DROPCLEANBUFFERS will move all dirty data pages to disk, even further ensuring accurate testing results.

http://www.devx.com/tips/Tip/14401

Process More Than One SSAS Object At a Time in SSMS

Durrr . . . sometimes I discover something so simple all I can say is "durrrrrr". I've been working with SSAS for over 2 years now and never knew that you could process more than one SSAS object at a time using SQL Server Management Studio. In case anyone else is as dense as me . . .

Simply open the Object Explorer Details window if it's not already open. Click on the folder containing the objects to be processes. Select multiple objects in the browser and initiate processing. That simple. I'm not sure why SSMS doesn't allow the selection of multiple objects in the Object Explorer tree control.

Is Everything OK?

Is it just me or is there still a bit of a lack of an overall SQL Server BI ecosystem? Don't get me wrong, I am a big proponent of SQL Server BI stack and there are a lot of nice integration points but I think there is still a lot of work to be done to tie it all together.

One of my biggest gripes is related to where to look when there seems to be an issue with a BI solution that uses SSIS, SSAS, and SSRS. And what if you just want to know if things have been humming along. We can implement all the email notifications in the world but what if your email system is unreliable (as it was with a recent client). Hence the question, "Is everything OK?"

I'm pretty well versed in exception handling and logging and can probably set my brain to autopilot and find an error message in a log on one of 10 servers without actually thinking about it but what about everyone else? There are other stakeholders such as more junior developers or even senior level people that are unfamiliar with SQL Server. How about the business? An analyst might see something fishy in the data and want to know if last night's load was successful. Do they have to call me or someone else on the development team? In many cases yes. But why? That kind of a dependency is unhealthy.

That's where the SQL Server BI Health Monitor Dashboard comes in. The SQL Server Health what? Don't fret if you haven't heard of this. I'd be suprised, no maybe disturbed, if you had. The SQL Server Health Monitor Dashboard was something I recently put together for a client that was unable to obtain a seasoned SQL Server BI developer but needed to continue to support the solution I built for them without having to rely on getting a hold of me.

The dashboard uses all out-of-the-box data sources including some system tables, the SSIS log table created by the SQL Server log provider, and the SSRS tables found in the ReportServer database to tie together a majority of the data needed to determine where in the SQL Server BI ecosystem your problem lives thus helping fix the problem that much faster.

I've posted the source code on Codeplex. You can find it @ http://bihealthdashboard.codeplex.com/. It's not exactly plug and play at this point. Connection strings need to be modifed and you might have to create the DimDate table if you don't already have a table of dates accessible. It's also geared towards a BI solution that has a single SQL Server Agent job step that calls a master SSIS package that in turn calls all other SSIS packages and a single SQL Server Agent job step that process your SSAS database. If that doesn't sound like your system, the solution can still be valuable in that it shows you how to extract very useful process metadata from your SQL Server BI Solution.

Which leads me to my next endeavor  ->  The SQL Server Metadata Manager . . .