Query all database files that have potential disk bottlenecks

Overview

Microsoft generally recommends you disk io latency for sql server should average under 20ms for data and 15ms for Logs.

This query should show you all database files on your SQL Server instance that exceed the recommended thesholds.

According to SQL Server Central [http://www.sqlservercentral.com/articles/ssc+clinic/74097/]

• Less than 10 milliseconds (ms) = very good
• Between 10-20 ms = okay
• Between 20-50 ms = slow
• Greater than 50-100 ms = potentially serious IO bottleneck
• Greater than 100 ms = definite IO bottleneck

T-SQL Script

/*
 For SQL Versions older than 2012.. replace
sys.dm_io_virtual_file_stats(NULL, NULL)
with
sys.dm_io_virtual_file_stats(-1, -1)

*/
IF OBJECT_ID('tempdb..##dbFiles') IS NOT NULL 
    DROP TABLE ##dbFiles
GO

/* 
 table to hold all database files 
*/
CREATE TABLE ##dbFiles (
      DBName SYSNAME ,
      name VARCHAR(200) ,
      physical_name VARCHAR(2000) ,
      type_desc VARCHAR(200) ,
      state_desc VARCHAR(200) ,
      FILE_ID INT ) 
GO

/* 
 get all the database files for the sql instance
 into a table
*/
EXEC sp_msforeachdb 'use [?]
      insert into ##dbFiles
      select ''?'' as [DBName],name , physical_name,type_desc, state_desc,file_id
      from sys.database_files'

/*
 FIND FILES THAT HAVE UNACCEPTABLE DISK LATENCY
 AND JOIN TO DATABASE_FILES TABLE TO GIVE FRIENDLY NAMES
 THE VIRTUAL FILE STATS ARE FROM WHEN SQL SERVER WAS LAST RESTARTED

  > 20ms data and > 15ms logs
*/
DECLARE @DataReadLatency_ms INT,@DataWriteLatency_ms INT
DECLARE @LogReadLatency_ms INT,@LogWriteLatency_ms INT

SET @DataReadLatency_ms = 20
SET @DataWriteLatency_ms = 20
SET @LogReadLatency_ms = 15
SET @LogWriteLatency_ms = 15

SELECT  DB_NAME(database_id) AS 'db' ,
        df.file_id ,
        io_stall_read_ms / NULLIF(num_of_reads,0) AS 'AVG READ TRANSFER/SEC' ,
        io_stall_write_ms / NULLIF(num_of_writes,0) AS 'AVG WRITE TRANSFER/SEC' ,
        size_on_disk_bytes ,
        df.name ,
        df.physical_name ,
        df.type_desc
FROM    sys.dm_io_virtual_file_stats(NULL, NULL) stat
        JOIN ##dbFiles df ON stat.file_id = df.FILE_ID
                             AND df.DBName = DB_NAME(stat.database_id)
WHERE   num_of_reads > 0
        AND num_of_writes > 0
  -- ONLY INCLUDE UNACCEPTABLE LATENCY AND NOT DIV/0
        AND (( 
     ISNULL((io_stall_read_ms / NULLIF(num_of_reads,0)),0) > @DataReadLatency_ms AND type_desc = 'ROWS'
    OR 
              ISNULL((io_stall_write_ms / NULLIF(num_of_writes,0)),0) > @DataWriteLatency_ms AND type_desc = 'ROWS'
            )
   OR
   ( 
     ISNULL((io_stall_read_ms / NULLIF(num_of_reads,0)),0) > @LogReadLatency_ms AND type_desc = 'LOG'
    OR 
              ISNULL((io_stall_write_ms / NULLIF(num_of_writes,0)),0) > @LogWriteLatency_ms AND type_desc = 'LOG'
            ))
             
-- clean up
DROP TABLE ##dbFiles

Example of results returned by the script

Printer Setup Pop-up When Opening Excel File

Overview

I had a problem when trying to open a specific excel file it would prompt the user with a Printer Setup window.

This was a problem because we were automating the excel workbook to refresh overnight and the prompt was stopping the automation from working.

This prompt also came up regardless of setting Application.DisplayAlerts = false in the controlling code behind

Here is an example of what the prompt looks like:

Solution

The issue for me was that one of the sheets in my workbook had been saved with the workbook view "Page Break Preview". This seems to be saved on a per worksheet basis rather than per workbook. So make sure you change it to normal mode for all worksheets within the workbook.

Once i changed this back to Normal mode (as below) and then saved the workbook. I was no longer prompted with the Printer Setup screen.

T-SQL View all database files on a SQL Instance

Overview

Sometimes you want to be able to see where all your database files are by doing a query. You can use the sys.database_files table to tell you on an individual database level but if you want to see it for all databases then the following script can help.

T-SQL Script

CREATE TABLE ##dbFiles
    (
      DBName SYSNAME ,
      name VARCHAR(200) ,
      physical_name VARCHAR(2000) ,
      type_desc VARCHAR(200) ,
      state_desc VARCHAR(200)
    )
go

-- executes a command on every database on the instance. The ? denotes the database name each pass through the loop
EXEC sp_msforeachdb '
use [?]
insert into ##dbFiles
select ''?'' as [DBName],name , physical_name,type_desc, state_desc
from sys.database_files
'

SELECT  *
FROM    ##dbFiles

-- clean up
DROP TABLE ##dbFiles

Example of results returned by the script

You can see the physical_name column returned by the query indicates where the database file is stored on the server.

Dropping active connections before restoring a database

When restoring test environment databases sometimes you just want to ignore all the current connections to the database and just restore it. 

You will normally see the message:

Msg 3702, Level 16, State 3, Line 1
Cannot drop database “AdventureWorks” because it is currently in use.

Some people use the approach of running and sp_who2 to see the active connections to that database and then run the KILL <SPID> command to drop the connections. This can be a bit tedious especially if there are a lot of connections and you need to kill each spid individually

To get around this you can drop the existing connections to your database using the command 
ALTER DATABASE [<YourDatabaseNameHere>] 
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

T-SQL - AdventureWorks Example

The following is an example of dropping the AdventureWorks connections and then restoring the database

-- this command will drop the existing connections to the database
USE [master]
GO
ALTER DATABASE [AdventureWorks] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

-- normal restore command
RESTORE DATABASE [AdventureWorks] 
FROM  DISK = N'D:\Backups\AdventureWorks.bak' 
WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO