TSQL DMV Queries

DMV queries for Blocking, DiskIO, Memory, Running Queries, Indexes...

http://sqlblogcasts.com/blogs/thepremiers/archive/2008/07/28/more-useful-dmv-queries.aspx

(Wish I'd found them before I spent hours writing my own)

Reattach Suspect Database

Simulating recovery of a database damaged by a crash over on Paul S Randal's blog...
http://www.sqlskills.com/blogs/paul/CategoryView,category,CHECKDB%2BFrom%2BEvery%2BAngle.aspx

SQL Server Versions : Performance Comparison

Joe Chang has published some performance statistics on SQL versions, including 2008...

http://sqlblog.com/blogs/joe_chang/archive/2008/08/17/large-query-performance-from-sql-server-2000-to-2008-32-64-bit.aspx

Get Date from UTC (Coordinated Universal Time) String

Get Date from UTC (Coordinated Universal Time) String

DECLARE @INPUTDATE VARCHAR(12),
  @OUTPUTDATE VARCHAR(12)

SET @INPUTDATE = '2006-7-29T13:55:6+0:0'

-- NEGATE TIME >
SELECT CHARINDEX('T',@INPUTDATE)
SELECT @INPUTDATE = LEFT(@INPUTDATE,CHARINDEX('T',@INPUTDATE)-1)
SELECT @INPUTDATE 

-- SEPARATE OUT YEAR, MONTH, DAY, PAD SINGLE DIGIT VALUES WITH ZEROES AND ADD BACK INTO SINGLE STRING WHICH CAN BE CONVERTED TO DATETIME
SELECT @OUTPUTDATE = LEFT(@INPUTDATE,4) + RIGHT('0' + REPLACE(SUBSTRING(@INPUTDATE,6,2),'-',''),2)+ RIGHT(REPLACE('0' + SUBSTRING(@INPUTDATE,8,3),'-',''),2)
SELECT @OUTPUTDATE

-- CONVERT TO DATETIME
SELECT CONVERT(DATETIME,@OUTPUTDATE,112)

CHECKSUM on Temporary tables (Temp db)

I was attempting to compare rows using CHECKSUM on sql 2005 when I came across this issue >
Storing my data in a #table (temporary table), I tried to use CHECKSUM to compare with the target table.
Despite the data being identical, different CHECKSUM values pervailed.

The good news is that a little googling proved it is sorted in sql 2008....


http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/23/checksum-and-tempdb.aspx

CTE Examples (Adventureworks 2008)

Simple CTE Example using AdventureWorks2008

WITH StaffBasicsCTE (FirstName, LastName, JobTitle, MaritalStatus , Gender , Age) 
AS (
 SELECT   FirstName, LastName,JobTitle, MaritalStatus , Gender ,DATEDIFF(YEAR,[BirthDate],GETDATE()) AS Age
 FROM [AdventureWorks2008].[Person].[Person] p
 INNER JOIN [AdventureWorks2008].[HumanResources].[Employee] e
 ON p.BusinessEntityID = e.BusinessEntityID)
  
SELECT * FROM StaffBasicsCTE
WHERE Gender = 'F'
AND MaritalStatus = 'S'
AND Age < 35

Recursive CTE Example using AdventureWorks2008 (Adapted from stored procedure dbo.uspGetEmployeeManagers)

WITH [EmployeeManagersCTE] ([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [RecursionLevel]) 
    AS (
        SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], 0 
        FROM [HumanResources].[Employee] e 
        INNER JOIN [Person].[Person] p 
        ON p.[BusinessEntityID] = e.[BusinessEntityID]

        UNION ALL

        SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], [RecursionLevel] + 1 -- Join recursive member to anchor
        FROM [HumanResources].[Employee] e 
        INNER JOIN [EmployeeManagersCTE]
        ON e.[OrganizationNode].GetAncestor(1) = [EmployeeManagersCTE].[OrganizationNode]
        INNER JOIN [Person].[Person] p 
        ON p.[BusinessEntityID] = e.[BusinessEntityID]
        )

    SELECT 
          m.[BusinessEntityID]
        , m.[FirstName]
        , m.[LastName] -- Outer select from the CTE
        , m.[RecursionLevel]
        , m.[OrganizationNode].ToString() as [OrganizationNode]
        , p.[FirstName] AS 'ManagerFirstName'
        , p.[LastName] AS 'ManagerLastName' 
    FROM [EmployeeManagersCTE] m 
        INNER JOIN [HumanResources].[Employee] e 
        ON m.[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
            INNER JOIN [Person].[Person] p 
            ON p.[BusinessEntityID] = e.[BusinessEntityID]
    ORDER BY [RecursionLevel], m.[OrganizationNode].ToString()
    OPTION (MAXRECURSION 25) 

Backup / Restore Corruption

" Server: Msg 3013, Level 16, State 1, Line 1 The backup data at the end of 'devicename' is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets. Server: Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally. "

The backup file being restored/written is damaged due to an error (write error during the backup or physical media error).
* This can occur when restoring from a network source too, hence maybe worth attempting to copy locally first.

If you're trying to backup to an existing file and don't care about the existing data (as you are overwriting it anyway) , replace it like this >

BACKUP DATABASE db1 TO DISK='C:\db1.bak' with FORMAT

If you're attempting a restore (gulp), it's a 2 step process.
1) Get the file number of the backup set within the backup file >

RESTORE HEADERONLY FROM DISK='C:\db1.bak'

2) Having identified a backup set (in this case number 3), try and restore it from the backup set manually >

RESTORE DATABASE db1 FROM DISK='C:\db1.bak WITH FILE = 3

Automatic USB Stick Backup

A little something i've been meaning to do for a while, automatically backing up my USB stick when i plug it in.

The obvious solution is to create an autorun.inf file on the stick to run a backup script from the device. Autorun is not always available however and I want control over where my data (for the record, encrypted by Truecrypt) is copied.

I have found 'USB Detect and Launch' by Matt Collinge which once installed allows you to run scripts when devices are plugged in, allowing different actions depending on the volume label.
The software is free for personal use or $40 for business use.

1 ) Download & Install tool from >

http://mattcollinge.wordpress.com/software/usb-detect-and-launch/

2 ) I created a directory for my backups >

D:\!USB_BACKUP\R5D4

3 ) I created D:\!\backup-r5d4.cmd (a 1 line backup command) as >

XCOPY %1\*.* D:\!USB_BACKUP\R5D4 /e /i /h /y /d

Usb Detect & Launch Configuration >

4 ) In 'Main Settings' I created one entry.
This runs my backup command whenever it sees a usb stick with a label of R5D4 >

label:R5D4 exec:d:\!\backup-r5d4.cmd %1

5) In 'Main Settings' I changed the poll interval to 60 seconds.


Results :
Bingo! Plugged in my USB drive and up popped the command window which proceeded to copy my files.

Hyper-V : VHD size optimization

I previously blogged about compressing Hyper-V VHD images as they can 'bloat' and eat disk space. Unhappy with the results, I set about trying to squeeze one of my images more...

The following was performed with a VHD of Vista Ultimate with VS2008 & Office 2007 installed.

action	bytes
original image	40,212,793
set system restore to 1gb max, compressed vhd	14,006,618
ran windows defragmenter, compressed vhd	15,456,481
ran flexomiser defragmenter, compressed vhd	13,903,606

Well worth reviewing VHD file sizes periodically I think...
r

SQL 2005 CU9 Released

http://support.microsoft.com/kb/953752/

Hyper-V : Error after removing VM

On browsing the Hyper-V Server Manager, I noticed some errors that I had not spotted before >

The Virtual Machines configuration (guid) at 'C:\hyper-V-Path\' is no longer accessible.

For some reason, my attempts at tidying up and moving the vm image files to a new location had not been 100% successful, leaving HyperV to periodically look for the image in the old location (despite the VM working fine from it's new location).

To rectify the situation, this technet post came in rather useful.

Basically >

1 ) Navigate to C:\ProgramData\Microsoft\Windows\Hyper-V\Virtual Machines (if you don't see anything, unhide files and folders via tools > folder options > view > 'show hidden files & folders')

2) Locate the link file that matches the GUID number in your original message.

3) In Hyper-V, click 'Stop Service' (turns off VMMS)

4) Delete the file

5) In Hyper-V, click 'Start Service' to restart the VMMS.

Errors should no longer appear every 2 minutes now.

Note : VMMS is the 'Virtual Machine Management service' and just controls your ability to manage VMs, they continue running even when VMMS has been shut down.

SQL 2008 : Installation on Windows 2008 (VM)

Screen walkthrough of SQL 2008 installation process >

SQL 2008 : Installer

At first glance, the installer (Enterprise edition pictured) looks a lot more friendly.
More administration functions are available (and are explained) >

Useful tool : FreeUndelete

FreeUndelete : A file recovery app that is FREE (and works well).

http://www.officerecovery.com/freeundelete/

(It rescued me today when my clumsy fingers deleted a folder on my usb stick)

Management Studio Speedup : Shared Memory

If your client and server are the same server (screams silently), for instance in a local development environment then Shared Memory can help.

As of SQL 2005 it is enabled by default, but incase it isn't or you need to check -

1) Set Shared Memory as Enabled for the sql server -

2) Set Shared Memory as Enabled in the client protocols -

Ref : Default SQL Server Network Configuration

Table Design Optimization : Column Sizes Script

I was called on to help with a data problem today.
The developer concerned had misjudged data sizes and hence defined a table that she couldnt import into.
My approach was to define a table with generous text column sizes with a view to reducing them later.

This script shows defined column sizes along with the size of the largest data for that column.
Change the @SCHEMA and @TABLE variables at the top of the script to point it at the table of your choice >

SET NOCOUNT ON 
SET NOCOUNT ON 
SET ANSI_WARNINGS ON
DECLARE @SCHEMA  VARCHAR(50)
DECLARE @TABLE  VARCHAR(50)

SET @SCHEMA = 'DBO'
SET @TABLE  = 'spt_values'

DECLARE  @CURRENTROW INT
DECLARE  @TOTALROWS INT
DECLARE  @COLUMNMAXSIZE INT
DECLARE  @SQLSTRING NVARCHAR(MAX)
DECLARE  @PARAMETER NVARCHAR(500);
DECLARE  @TABLEDETAILS 
  TABLE(UNIQUEROWID     INT   IDENTITY ( 1,1 ),
     TABLE_SCHEMA    VARCHAR(255),
     TABLE_NAME      VARCHAR(255),
     COLUMN_NAME     VARCHAR(255),
     COLUMN_TYPE     VARCHAR(255),
     MAX_LENGTH      INT,
     MAX_DATA_LENGTH INT)
       
INSERT INTO @TABLEDETAILS
          (TABLE_SCHEMA,
           TABLE_NAME,
           COLUMN_NAME,
           COLUMN_TYPE,
           MAX_LENGTH)

SELECT SCHEMA_NAME(O.SCHEMA_ID)  AS TABLE_SCHEMA,
      OBJECT_NAME(O.OBJECT_ID)  AS TABLE_NAME,
      C.NAME                    AS COLUMN_NAME,
      T.NAME                    AS COLUMN_TYPE,
      C.MAX_LENGTH
FROM   SYS.TABLES O
INNER JOIN SYS.COLUMNS C
       ON C.OBJECT_ID = O.OBJECT_ID
INNER JOIN SYS.TYPES T
       ON C.SYSTEM_TYPE_ID = T.SYSTEM_TYPE_ID
      AND T.NAME IN ('CHAR','VARCHAR','NCHAR','NVARCHAR')
WHERE SCHEMA_NAME(O.SCHEMA_ID)  <> 'sys'
AND OBJECT_NAME(O.OBJECT_ID) = @TABLE
AND SCHEMA_NAME(O.SCHEMA_ID) = @SCHEMA
                                
SELECT @TOTALROWS = COUNT(*) FROM   @TABLEDETAILS
SELECT @CURRENTROW = 1
WHILE @CURRENTROW <= @TOTALROWS   
BEGIN     
SET @COLUMNMAXSIZE = 0      
SELECT @SQLSTRING = 'SELECT @COLUMNSIZEMAX = MAX(LEN([' + COLUMN_NAME + '])) FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'     FROM   @TABLEDETAILS     WHERE  UNIQUEROWID = @CURRENTROW                               
SET @PARAMETER = N'@COLUMNSIZEMAX INT OUTPUT';          
EXECUTE SP_EXECUTESQL       @SQLSTRING 
     ,       @PARAMETER 
     ,       @COLUMNSIZEMAX = @COLUMNMAXSIZE OUTPUT      
UPDATE @TABLEDETAILS     
SET    MAX_DATA_LENGTH = @COLUMNMAXSIZE     
WHERE  UNIQUEROWID = @CURRENTROW                               

-- DISPLAY PROGRESS (May exceed max results sets if uncommented)     
-- SELECT * FROM @TABLEDETAILS WHERE  UNIQUEROWID = @CURRENTROW       
SET @CURRENTROW = @CURRENTROW + 1   
END      

SELECT   TABLE_SCHEMA
  ,TABLE_NAME
  ,COLUMN_NAME
  ,COLUMN_TYPE
  ,CASE MAX_LENGTH WHEN -1 THEN 'MAX' ELSE CONVERT(CHAR(10),MAX_LENGTH) END AS COLUMN_MAX_LENGTH
  ,MAX_DATA_LENGTH 
FROM @TABLEDETAILS 

TSQL : List System Stored Procedures

select * from msdb.sys.objects 
where type_desc = 'SQL_STORED_PROCEDURE' and is_ms_shipped = 1

Management Studio Speedup : Disable Error & Usage Reporting

I usually disable this when I perform an installation, but ocassionally I take over a server where it is still active.

Navigate to - Start > All Programs > Micrsoft SQL Server 200x >  SQL Server Error and Usage Reporting

Untick the settings as follows  >

Management Studio Speedup : Remove Splash Screen

Remove that pesky splash screen!

Simple this, change the shortcut that launches Management Studio to include the /NOSPLASH switch as shown below.

Reporting Services : Installing 32bit version on a 64bit server

SQL 2005's 'System Configuration Check' reported a version clash on my Windows 2008 box when trying to install Reporting Services.
" 64-bit ASP .Net is Registered. Required 32-bit ASP .Net to install Microsoft Reporting Service 2005 (32.bit). "
Sounds a bit backwards to me!
I needed to revert to 32-bit .NET on 64-bit IIS 7.0 (which is basically done by changing which DLL IIS is using)
Here's what to do...

1) from a command prompt >

C:\>cd C:\inetpub\AdminScripts

C:\inetpub\AdminScripts>cscript adsutil.vbs set w3svc/AppPools/Enable32bitAppOnWin64 1 Microsoft (R) Windows Script Host Version 5.7 Copyright (C) Microsoft Corporation. All rights reserved. Enable32bitAppOnWin64 : (BOOLEAN) True


2) Install Reporting Services (and SP2, and hotfixes... yawn...)

3) Configure Reporting Services (subject of another post, i'm sure...)


see also http://technet.microsoft.com/en-us/library/ms143293.aspx

Reporting Services : Installing IIS on Windows 2008

Installing Internet Information Services (IIS) on Windows 2008

This is a pre-requisite for Reporting Services >

1) Launch 'Server Manager'
2) Expand 'Roles'
3) Click 'Add Role' (over there on the right...)
4) Add ' Web Server(IIS) ' Role

When install is done >

5) Expand 'Roles'
6) Select 'Web Server (IIS) (right hand pane will change)
7) Scroll down to 'Role Services' in the pane on the right.
8) Click 'Add Role Services' and add the following roles >

> Web management tools
> IIS 6 Management Compatibility
> IIS 6 WMI Compatibility
> IIS Metabase and IIS 6 configuration compatibility

> World Wide Web Services
> Application Development Features
> ASP.NET
> ISAPI Extensions
> ISAPI Filters

> Common Http Features
> Default Document
> Directory Browsing
> HTTP Redirection
> Static Content

> Security
> Windows Authentication

Windows Uptime

Go to a command (dos) prompt -
Type 'net statistics server'

Look for the statistics since line - 

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\user>net statistics server
Server Statistics for \\MYPC-123


Statistics since 7/30/2008 10:44 AM


Sessions accepted                  1
Sessions timed-out                 0
Sessions errored-out               0

Kilobytes sent                     0
Kilobytes received                 0

Mean response time (msec)          0

System errors                      0
Permission violations              0
Password violations                0

Files accessed                     0
Communication devices accessed     0
Print jobs spooled                 0

Times buffers exhausted

  Big buffers                      0
  Request buffers                  0

The command completed successfully.


C:\Documents and Settings\user>


Link : How to find windows uptime?

10 Ultimate Rules for Effective System Administration

1. Keep it simple.

2. Backup regularly

3. Test your backup regularly

4. Proactive Monitoring

5. Document Everything

6. Plan and Execute it well.

7. Use Command Line more than GUI

8. Automate repetitive tasks

9. Support your users and developers

10. Keep learning and have fun.


The excellent, full post on the subject is here >

http://www.cyberciti.biz/tips/10-ultimate-rules-for-effective-system-administration.html

2008 : Technet

http://blogs.technet.com/alanlemarquand/archive/2008/07/30/the-latest-sql-server-2008-technet-event-content-posted.aspx