SQL Server – BulkCopy ( BCP ) – Which data file?

Background

Using BCP, we are churning though quite a bit of files.

Unfortunately, the DOS batch file that I quickly stitched together missed an importantly functionality.

And, that functionality is to log the current file being processed.

Diagnostic

Microsoft

Resource Monitor

I am a big fan of Microsoft’s Resource Monitor.

Let us use it.

Preparation

We remote connect to the source computer and launched task Manager; from Task Manager accessed Resource Monitor.

Resource Monitor – Tab – Memory

Image

ResourceMonitor_Tab_CPU_ProcessesAndAssociatedHandles_20180706_0850AM.png

Explanation
  1. When we filter on the bcp.exe process, we see our data file as one of the files mentioned in the “Associated Handles” tab

 

Resource Monitor – Tab – Memory

Image

ResourceMonitor_Tab_Memory_20180706_0852AM.png

Explanation
  1. The BCP process is using about 14 MB

 

Resource Monitor – Tab – Disk

Image

ResourceMonitor_Tab_Disk_20180706_0907AM.png

Explanation
  1. sqlserver,exe is mentioned
  2. But, not our bcp.exe file

 

Summary

To get a reliable insight into which files are being accessed, please consider Microsoft’s own Resource Monitor; specifically the CPU tab.

 

 

SQL Server – Files In use – Day 1

Background

One of the many areas that that one needs to keep an eye one when monitoring database engines  is which files are opened, how they are opened ( exclusively, read only), what other processes are competing for them, etc.

Day 1

This is Day 1 and so we will start off with the basic tools.

 

Microsoft

Lineage

Microsoft really did a very job with Resource Monitor.

Prior to Resource Monitor, Task manager was the go to quick tool.

Task Manager

Here is what Task Manager exposes:

unfortunately, it only exposes information at process level.

 

Resource Monitor

Tab – Disk

Here we see the active Disk Activities.

Explanation

  1. We are able to filter by Process
  2. And, we can order by
    • Process Name
    • File name
    • Reads/Writes/Totals

Tab – CPU

Tab – CPU – Original

When we switch over to the CPU Tab, here is what we see

Explanation

We are forced to choose a process.

Tab – CPU – Process = sqlservr.exe

Once we chose sqlservr.exe from the list of processes:

Tab – CPU – Process = sqlservr.exe = Drive C:

Explanation:
  1. Unfortunately, we were getting really bad storage utilization a few months ago, and had to move to System Drive C: till more storage was allocated
  2. Will come back and move the rollover data and log files
Tab – CPU – Process = sqlservr.exe = Drive D & E:

Explanation:
  1. Most of our SQL Server Data and log files are on Drive D & E:
  2. Will come back and segregate them

SysInternals

I real like the straightforwardness of SysInternals tools.

Handle

Here is how to use handle.

Scripts

Look for file handles

Here we ask for a specific process:

  1. -p = sqlservr
  2. type = file
Code

handle -nobanner -p sqlservr | find "File" | more

Output

Look for file handles – File  – Extension [ mdf, ndf, ldf]

Here we ask for files that have have df in their names.

  1. -p = sqlservr
  2. type = file
Code

handle -nobanner -p sqlservr | find "File" | find "df" | more

Output

 

SQL Server

Sql Server Management Studio ( SSMS )

You can also use SSMS Activity Monitor.

But, to me it is a big hammer to what one really needs.

 

Dedicated

Can’t go anywhere else but to SysInternals, Mark Russinovich & Bryce Cogswell.

To me they made it cool to want to look into things, Windows Internals anyone.