Steven M. Jordan

Home SQL 2012

Showing posts with label SQL 2012. Show all posts

Task:

How to transfer the TempDB.

Problem:

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!

Solution:

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
GO

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

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;
GO

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

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

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.

Conclusion:

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!

References:

http://dbadiaries.com/tempdb-best-practices
http://dbadiaries.com/sql-server-tempdb-whats-it-for
http://www.mytechmantra.com/LearnSQLServer/How-to-Move-TempDB-to-New-Drive-in-SQL-Server/

Problem:

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

Symptoms:

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.

Reason:

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.

Background:

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

Solution:

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

SQL script to identify obsolete updates on WSUS server:
USE [SUSDB] GO DECLARE @return_value int EXEC @return_value = [dbo].[spGetObsoleteUpdatesToCleanup] GO

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
BEGIN
    RAISERROR('spGetObsoleteUpdatesToCleanup: failed to get RevisionDeletionTimeThreshold from dbo.tbConfigurationC', 16, -1)
END
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
WHERE
     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)
 RAISERROR( @msg,0,1) WITH NOWAIT
 
 exec spDeleteUpdate @updateid
 

    fetch next from cur into @updateid
END

close cur
deallocate cur

References:

http://blog.coretech.dk/kea/house-of-cardsthe-configmgr-software-update-point-and-wsus/

http://kreelbits.blogspot.com/

Transfer SQL TempDB

Task:

Problem:

Solution:

Conclusion:

References:

Resolve WSUS Database Error Timeouts

Problem:

Symptoms:

Reason:

Background:

Solution:

References:

Backup SQL 2012 to DPM 2010

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

My Instagram