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...2012
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...Capture Index Usage Statistics for Better Analysis
Starting with SQL Server 2005, Microsoft introduced Dynamic Management Views to help DBAs see what’s going inside of SQL Server. One of my favorite is the sys.dm_db_index_usage_stats. It collects statistics for different types of index operations and the timestamp at which it was last performed. All the DMVs are great tools; however, they all have the same drawback. They are memory resident, which means the statistics are reset any time the SQL Server service is restarted, the database is attached/detached, or it’s restored. The best way to avoid losing this valuable data is to store it in a persistent table. ...
Continue reading...Which SQL Instance is Killing Your CPU?
You have 5 instances running. How do you know which one is killing your CPU? As you can see from Windows Task Manager, you have several instances of SQL installed and one of them is using nearly half of the CPU. To figure this out, you’ll need to know the PID (Process ID) number. To view the PID number in Windows Task Manager, from the menu select View, then Select Columns, and then check the box for PID. For our example, it’s Process ID 1212 that’s using nearly 50% of our CPU. So how do you correlate that PID...
Continue reading...
