Patrick Keisler

Get Performance Counters from within SQL Server

I think most DBAs know that you can use the Windows Performance Monitor to capture performance metrics for your SQL Server.  But did you know that you also have access to all the SQL Server performance counters from within SQL Server? By using using the DMV sys.dm_os_performance_counters, you can view that performance data.  As with all DMVs, there are a few drawbacks.  The data collected is only valid since the last time SQL Server was started, and this DMV does not allow access to any of the Window OS performance counters. For example, we can query the DMV to find...

Continue reading...

How to Use xp_delete_file to Purge Old Backup Files

Continuing on my recent trend of using undocumented stored procedures, this week I thought we can cover using xp_delete_file to purge old backup files from a folder.  This is stored procedure used by the maintenance plans to clean up old backup files, but it makes for a handy purge tool when creating your own backup scripts. Let’s suppose we have a simple script (taken from a recent post on stackoverflow.com) to backup all databases on a server. The script will create a new database backup file of every database on the server each time it’s executed.  However, the path may not...

Continue reading...

How to Use xp_dirtree to List All Files in a Folder

UPDATED — Dec 31, 2012 — Be sure to read Part 2 of this post discussing xp_dirtree. Last week I blogged about how to use an undocumented stored procedures to create folders.  This week we need to do the opposite.  We need to get a list of all files from a subfolder in order to process them.  In order to do that, we’ll use another undocumented extended stored procedure; master.sys.xp_dirtree.  This stored procedure will display a list of every folder, every subfolder, and every file for path you give it. Xp_dirtree has three parameters: directory – This is the directory...

Continue reading...

Dynamically Create a Folder for Each Database

If you’re a DBA like me then you’re probably pretty detail-oriented and like to keep things very organized. For example, when doing database backups, I like to have a specific folder structure to hold the backup files for each database. See below: D:\Backup\Server\Instance\MyDb1 D:\Backup\Server\Instance\MyDb2 D:\Backup\Server\Instance\MyDb3 In order to make this happen automatically, my TSQL backup jobs need to have code that will create folders when a new database is added or when the jobs are initially setup. SQL offers an undocumented extended stored procedures that allow us to create them on the fly; master.sys.xp_create_subdir. Master.sys.xp_create_subdir will create whatever folder listing...

Continue reading...

Customize the Management Studio Toolbar

Management Studio is great tool with almost everything you need right at your fingertips.  Notice I said “almost”.  I like to customize the toolbars to meet my needs of doing work every day.  Because being a DBA means you’re busy all day long, and you don’t have time to hunt through all the menus or memorize a keystroke for a special function.    The very first customization I always make is to add the “Hide Results Pane” button to the SQL Editor toolbar.  This option is found under the Window menu and acts a toggle button to either show or...

Continue reading...