Transfer SQL TempDB


How to transfer the TempDB.


MS SQL ran slow on 6Gb/s SAS hard drive (HHD).  Moved the user database (DB) to PCIe x4 (i.e., 31.52 Gb/s), MLC solid-state (SSD) storage -and it still runs slow!


Does the TempDB run on the old HHD drive or the new MLC SSD?  Consider how the TempDB can generate serious disk I/O.  Transfer the TempDB to the high-speed SSD as well.  N.B., this entire process gave new life to my old DPM2010 server.

  Step 1:  TSQL script to identify TempDB location:

Use master

name AS [LogicalName]
,physical_name AS [Location]
,state_desc AS [Status]
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
   TSQL output:

tempdev C:\Program Files\Microsoft DPM\SQL\MSSQL10.MSDPM2010\MSSQL\DATA\tempdb.mdf
templog C:\Program Files\Microsoft DPM\SQL\MSSQL10.MSDPM2010\MSSQL\DATA\templog.ldf

  Step 2:  TSQL script to move TempDB to new location.

USE master;

MODIFY FILE (NAME = tempdev, FILENAME = 'X:\SQL\Data\tempdb.mdf');

MODIFY FILE (NAME = templog, FILENAME = 'X:\SQL\Data\templog.ldf');

   TSQL output:

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

  Step 3:  Restart SQL services

  Step 4:  Verify Change - See Step 1.


SQL server runs quick with new storage.  Disk queue lengths, for all disks, are a thing of the past.  Individual results may vary based on load (duh).

That's It!


Resolve WSUS Database Error Timeouts


WSUS GUI times out while running back-end SQL queries.  WSUS typically times out when refreshing Updates:  All Updates, Critical Updates, Security Updates, etc.


WSUS runs slow.  WSUS IIS App pool crashes and stops.  WSUS problem requires administrator to reset server node in order to function again.  Clients cannot download WSUS updates when APP pool is down.


Large number of updates in the catalog grows over time.  The update catalog reaches a size that increases the load on WSUS.  The server runs out of available memory for the App pool.


Kent Agerlund does a good job describing the problem on his blog.  Agerlund also provides a solution using a SQL script.  However, Agerlund's fix does not work for all WSUS implementations.  The script only works with the full version of SQL -not the Windows Internal Database.  For example:

The specified option 'Ad Hoc Distributed Queries' is not supported by this edition of SQL Server


Talented programmer, SAK, updates the script to work with WSUS 2012 running a Windows Internal Database version of SQL:

  1. SQL script to identify obsolete updates on WSUS server:
    DECLARE @return_value int
    EXEC @return_value = [dbo].[spGetObsoleteUpdatesToCleanup]
  2. SQL script to delete all obsolete updates on WSUS.  N.B., the script provides feedback messages, after each update ID is deleted.
    DECLARE @minimumDeadDeploymentTime DATETIME
    DECLARE @revisionDeletionTimeThreshold INT
    SELECT @revisionDeletionTimeThreshold=RevisionDeletionTimeThreshold FROM dbo.tbConfigurationC
    IF @@ERROR <> 0
        RAISERROR('spGetObsoleteUpdatesToCleanup: failed to get RevisionDeletionTimeThreshold from dbo.tbConfigurationC', 16, -1)
    SET @minimumDeadDeploymentTime = DATEADD(day, 0 - @revisionDeletionTimeThreshold, getutcdate())
    declare @updateid int
    declare @msg varchar(255)
    declare cur CURSOR LOCAL for
    SELECT DISTINCT u.LocalUpdateID FROM  dbo.tbUpdate u
        INNER JOIN dbo.tbRevision r ON r.LocalUpdateID = u.LocalUpdateID
        INNER JOIN dbo.tbProperty p ON p.RevisionID = r.RevisionID
         p.PublicationState = 1 
         AND (p.ExplicitlyDeployable = 1 OR p.UpdateType IN ('Category', 'Detectoid'))
         AND p.ReceivedFromCreatorService <= @minimumDeadDeploymentTime
         AND NOT EXISTS (SELECT * FROM dbo.tbBundleDependency bd 
                         INNER JOIN dbo.tbRevision r1 ON bd.BundledRevisionID = r1.RevisionID
                         WHERE r1.LocalUpdateID = u.LocalUpdateID)
         AND NOT EXISTS (SELECT * FROM dbo.tbPrerequisiteDependency pd
                         INNER JOIN dbo.tbRevision r2 ON pd.PrerequisiteRevisionID = r2.RevisionID
                         WHERE r2.LocalUpdateID = u.LocalUpdateID)
         AND NOT EXISTS (SELECT * FROM dbo.tbDeployment d
                         INNER JOIN dbo.tbRevision r3 ON d.RevisionID = r3.RevisionID
                         WHERE r3.LocalUpdateID = u.LocalUpdateID
                             AND d.TargetGroupTypeID = 0
                             AND d.ActionID IN (0, 1, 3))
         AND NOT EXISTS (SELECT * FROM dbo.tbDeadDeployment dd
                         INNER JOIN dbo.tbRevision r4 ON dd.RevisionID = r4.RevisionID
                         WHERE r4.LocalUpdateID = u.LocalUpdateID
                             AND dd.TargetGroupTypeID = 0
                             AND dd.ActionID IN (0, 1, 3)
                             AND dd.TimeOfDeath > @minimumDeadDeploymentTime)
    ORDER BY u.LocalUpdateID DESC
    open cur
    fetch next from cur into @updateid
    while @@FETCH_STATUS = 0 BEGIN
        --execute your sproc on each row
        --exec uspYourSproc @field1, @field2
     set @msg = 'Processing update: ' + cast(@updateid as varchar)
     exec spDeleteUpdate @updateid
        fetch next from cur into @updateid
    close cur
    deallocate cur

Backup SQL 2012 to DPM 2010

 By Steven Jordan on March 26th, 2013. 

Takeaway:   DPM 2010 can backup SQL 2012 with this simple permission change.

Problem:  DPM 2010 cannot backup SQL 2012 databases.

  • DPM 2010 Recovery Point Errors:  Recovery point creation jobs for SQL Server 2012 database SERVER\NAME have been failing.  The number of failed recovery point creation jobs = x.
  •  DPM 2010 Replica is inconstant Alerts:  The replica of SQL Server 2012 database SERVER\NAME is inconsistent with the protected data source. 

Solution:  NT AUTHORITY\System must be granted Sysadmin permissions on the SQL 2012 database instance.

Background:    By default, SQL 2010 includes the Sysadmin permissions for the NT Authority\System account.  However, SQL 2012 does not include the Sysadmin permissions for the NT Authority\System account by default.   

Fix DPM 2010 consistency errors for SQL 2012 databases by adding the Sysadmin server role to the SQL 2012 NT AUTHORITY\System:

1.   Modify the NT Authority\System login:

2.  Add Sysadmin server role

O #DPM #Consistency #SQL O


Move the Data Protection Manager (DPM) SQL Databse to a New Location

By Steven Jordan on March 7th, 2013.

Takeaway:  This walk through provides instructions on how to move the DPM SQL database to a new drive or directory location.

Problem:  The system drive on the DPM server is consistently low on drive space. Low disk space creates havoc for Windows Updates and Volume Shadow Service (VSS) snapshots.

Background:  The DPM system drive resides on a 64GB NTFS volume.  The volume is nested on a Raid 1 partition which mirrors 2 x 64GB hard drives.

The system drive contains the DPM protection group mounting points; and uses iSCSI to connect to a SAN which stores the protection groups replica data.

The server was installed in 2010, and at the time 64GB seemed appropriate.  Overtime, the limited space proved insufficient for our growing network.  

Solution:  Add storage and make configuration changes.
  • Add new drives (2 x 146GB, RAID 1)
  • Create new data volume.
  • Move the DPM SQL database to the new volume.
  • Complete additional DPM maintenance steps.

  1. The Dell Open Manage Administrator makes adding additional drives a snap.  Simply add the drives and configure the virtual disks for a new RAID 1 partition in the OMSA web GUI -no need to power off the server.

    Use the Windows Disk Management to enable the disk and create a new NTFS volume.

  2. It's time to put that extra storage to good use.  The DPM SQL database is close to 30GB in size, that's nearly half of the entire system drive!  The DPM SQL database will be the first thing to go. 

    Let's stop the DPM services before we move the database:  Server Manager → Configuration → Services → Stop the DPM services.

  3. Open the Microsoft SQL Server Management Studio to detach the DPM database.  SQL Server Management Studio → Databases → right click the DPMDB database → left click on Detach.

       Note that the DPMDB is no longer listed under the available databases.

  4. The DPM database is located in \\C:\Program Files\Microsoft DPM\DPM\DPMDB.  Move the DPMDB directory to the new location.

  5. Attach the DPMDB database using Server Management Studio.   SQL Server Management Studio → right click Databases →left click on attach → browse to the new DPMDB location.

    Note the DPMDB is available in the Management Studio list of databases.

  6. Start the DPM service from the Server Manager (e.g., step 3).

  7. Use the DPM Management Shell to synchronize the database:  DPMSync -sync.

  8. Start the DPM Administrator Console.  N.B., all DPM replicas are in an inconsistent state.

    Perform consistency check on every replica.  The process can be manually performed or use PowerShell to a issue constancy check for all replicas.

  9. The primary DPM server will work great after the consistency check.  The system drive now has nearly 30GB free.

  10. Run the DPMSync -sync on any other secondary DPM server and follow with consistency check to complete the process. 

    N.B., My first consistency checks failed with the following error:  The VSS application writer or the VSS provider is in a bad state.  The secondary DPM server's off-site protection group synchronized after I manually started the DPM Writer service on the primary DPM server.
  11. If the secondary DPM server protects the primary DPM server with a full metal backup the WindowsImageBackup location may be optionally changed to the new drive space. 

    The DPM server uses Windows Backup Server to backup its own System State.  DPM runs the system state backup (SSB) on the system drive.  Change the WindowsImageBackup location as follows: 

    • On the primary DPM server, navigate to c:\Program Files\Microsoft Data Protection Manager\DPM\Datasources.
    • Edit PSDatasourceConfig.XML with notepad.
    • Change the value from %systemdrive% to the new location.
    • Save the file.
    • On the secondary DPM server, run a consistency check on the system state backup protection group, which protects the primary DPM server.
    • Wait for the check to fail.  In the alerts, click Modify protection group, and complete the wizard steps.
    • Perform consistency check on the same group.

    That's It!
O #DPM #Consistency  O