SQL Server Quick Check - Notes

Checking a SQL Server over? Some notes on how to approach this.
Examine aspects in this order (Memory > Storage > CPU) as one issue can be a symptom of another.

Memory

Examine using Performance Monitor (Perfmon). Look at these counters -

Memory:Available MBytes
How much memory is available for Windows?
Stop SQL consuming too much by setting maximum memory (recommended tip).

SQLServer:Memory Manager/Target Server Memory (KB)
How much memory SQL wants.

SQLServer:Memory Manager/Total Server Memory (KB)
How much memory SQL has.
If this is less than the value it wants then more memory is needed to be allocated or installed.

SQLServer:Buffer Manager:Page Life Expectancy
300 seconds (5 minutes) for an OLTP system.
90 seconds (1 & 1/2 minutes) from a data warehouse.

The old method was SQLServer:Buffer cache hit ratio (ideal of 99%) but data volumes mean this counter has become meaningless now.


Storage

Examine these Windows counters using Perfmon for the Windows view of Storage performance

LogicalDisk:Avg.Disk sec/Transfer
Should be < 20ms (0.020 seconds) for volumes hosting sql data files

For further problems look for differences between
LogicalDisk:Avg.Disk sec/Read & LogicalDisk:Avg.Disk sec/Write
Could show issues with controller or RAID (e.g. slow write on RAID5).


For a SQL view of Storage performance, some TSQL to help out ...

On SQL 2000 looking at I/O statistics is achieved using fn_virtualfilestats

SELECT
   d.name as DBName
   ,RTRIM(b.name) AS LogicalFileName
   ,a.NumberReads
   ,a.NumberWrites
   ,a.BytesRead
   ,a.BytesWritten
   ,a.IOStallReadMS
   ,a.IOStallWriteMS
   ,CASE WHEN (a.NumberReads = 0) THEN 0 ELSE a.IOStallReadMS / a.NumberReads END AS AvgReadTransfersMS
   ,CASE WHEN (a.NumberWrites = 0) THEN 0 ELSE a.IOStallWriteMS / a.NumberWrites END AS AvgWriteTransfersMS
FROM ::fn_virtualfilestats(-1,-1) a
INNER JOIN sysaltfiles b ON a.dbid = b.dbid AND a.fileid = b.fileid
INNER JOIN sysdatabases d ON d.dbid = b.dbid
ORDER BY a.NumberWrites DESC

For SQL 2005+, sys.dm_io_virtual_file_stats is a available.
This is a dynamic management function that shows how SQL is using the data files.

SELECT
    DB_NAME(filestats.database_id) AS DBName
    ,files.name AS LogicalFileName
    ,num_of_reads AS NumberReads
    ,num_of_writes AS NumberWrites
    ,num_of_bytes_read AS BytesRead
    ,num_of_bytes_written AS BytesWritten
    ,io_stall_read_ms AS IOStallReadMS
    ,io_stall_write_ms AS IOStallWriteMS
   ,CASE WHEN (num_of_reads = 0) THEN 0 ELSE io_stall_read_ms / num_of_reads END AS AvgReadTransfersMS
   ,CASE WHEN (num_of_writes = 0) THEN 0 ELSE io_stall_write_ms / num_of_writes END AS AvgWriteTransfersMS
FROM sys.dm_io_virtual_file_stats(-1,-1) filestats
INNER JOIN sys.master_files files
        ON filestats.file_id = files.file_id
        AND filestats.database_id = files.database_id
ORDER BY num_of_writes DESC

Further info on here c/o David Pless

If Disk Performance is an issue, consider these aspects

• Auditing disk configuration
• Physical file fragmentation - contig.exe 
• Is the RAID configuration appropriate for volume of transactions.

CPU

Performance Monitor (Perfmon) counters to determine processor use are -

Processor:% Processor Time

How busy is the processor?
Should be < 70%

Processor:% Interrupt Time 

Percentage of time spent servicing hardware interrupt requests.
Should be < 20%

Processor:Processor Queue Length

How many tasks are waiting for processor time?
Should be < 4 per CPU

For Processor counters, it may be desirable to monitor separate instances (different cores) in addition to monitoring the _Total instance. The _Total instance provides average readings and therefore disguises individual overworked or under-utilized processors / cores.
The processor counters are for everything installed on the system, not just SQL Server.

Foreign Keys without Indexes

Here are some scripts that provide index creation statements for foreign keys without indexes. NB : I am not advocating creating indexes on every foreign key. Their use depends on application design, (the sql it runs) and whether other covering indexes are present. SQL 2000 Version

/*
adapted from http://stackoverflow.com/questions/1406119/how-can-i-find-unindexed-foreign-keys-in-sql-server
Uses my NCI_tablename-indexname index naming convention
*/

DECLARE 
    @SchemaName varchar(255),
    @TableName varchar(255),
    @ColumnName varchar(255),
    @ForeignKeyName sysname

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE FKColumns_cursor CURSOR Fast_Forward FOR
SELECT  cu.TABLE_SCHEMA, cu.TABLE_NAME, cu.COLUMN_NAME, cu.CONSTRAINT_NAME
FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS ic 
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu ON ic.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
WHERE   ic.CONSTRAINT_TYPE = 'FOREIGN KEY'

CREATE TABLE #temp1(    
    SchemaName varchar(255),
    TableName varchar(255),
    ColumnName varchar(255),
    ForeignKeyName sysname
)

OPEN FKColumns_cursor  
FETCH NEXT FROM FKColumns_cursor INTO @SchemaName,@TableName, @ColumnName, @ForeignKeyName

WHILE @@FETCH_STATUS = 0  
BEGIN

    IF ( SELECT COUNT(*)
    FROM        sysobjects o    
        INNER JOIN sysindexes x ON x.id = o.id
        INNER JOIN  syscolumns c ON o.id = c.id 
        INNER JOIN sysindexkeys xk ON c.colid = xk.colid AND o.id = xk.id AND x.indid = xk.indid
    WHERE       o.type in ('U')
        AND xk.keyno <= x.keycnt
        AND permissions(o.id, c.name) <> 0
        AND (x.status&32) = 0
        AND o.name = @TableName
        AND c.name = @ColumnName
    ) = 0
    BEGIN
        INSERT INTO #temp1 SELECT @SchemaName, @TableName, @ColumnName, @ForeignKeyName
    END


    FETCH NEXT FROM FKColumns_cursor INTO @SchemaName,@TableName, @ColumnName, @ForeignKeyName
END  
CLOSE FKColumns_cursor  
DEALLOCATE FKColumns_cursor 

SELECT 'IF NOT EXISTS (SELECT * FROM sysindexes WHERE name = '''
       + 'NCI_' + TableName + '_' + ColumnName + ''') '
       + ' CREATE INDEX [NCI_' + TableName + '_' + ColumnName + '] ON [' + SchemaName + '].[' + TableName + ']([' + ColumnName +'])'
FROM #temp1 
ORDER BY TableName

DROP TABLE #temp1 

SQL 2005 Version

/*
adapted from http://encodo.com/en/blogs.php?entry_id=173
Uses my NCI_tablename-indexname index naming convention
Have added table schemas and 'IF EXISTS' checks to detect if index is already present
*/

SELECT
'IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''['
+ IndexSchemas.[name]
+ '].['
+ IndexTables.[name]
+ ']'') AND name = N''IX_'
+ IndexSchemas.[name] + '_' + IndexTables.[name] + '_' + IndexColumns.[name]
+ ''') '
+ 'CREATE NONCLUSTERED INDEX [IX_'
+ IndexSchemas.[name] + '_' + IndexTables.[name] + '_' + IndexColumns.[name]
+ '] ON ['
+ IndexSchemas.[name] + '].[' + IndexTables.[name] 
+ ']( ['
+ IndexColumns.[name]
+ '] ASC ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]'
FROM sys.foreign_keys ForeignKeys
INNER JOIN sys.foreign_key_columns ForeignKeyColumns
  ON ForeignKeys.object_id = ForeignKeyColumns.constraint_object_id
INNER JOIN sys.columns IndexColumns
  ON ForeignKeyColumns.parent_object_id = IndexColumns.object_id
  AND ForeignKeyColumns.parent_column_id = IndexColumns.column_id
INNER JOIN sys.tables IndexTables
  ON ForeignKeyColumns.parent_object_id = IndexTables.object_id
INNER JOIN sys.schemas IndexSchemas
  ON IndexTables.schema_id = IndexSchemas.schema_id
ORDER BY IndexTables.[name], IndexColumns.[name]

Enabling/Disabling constraints

-- Enable Constraints
ALTER TABLE tablename CHECK CONSTRAINT constraintName
ALTER TABLE tablename CHECK CONSTRAINT ALL 

-- Disable Constraints
ALTER TABLE tablename NOCHECK CONSTRAINT constraintName
ALTER TABLE tablename NOCHECK CONSTRAINT ALL

-- Add constraint, without checking existing data -
ALTER TABLE tablename WITH NOCHECK ADD CONSTRAINT PK_tablename_id PRIMARY KEY(id) 

Solace : Disabling All Constraints (use with caution, e.g for data loading)
Ode to Code : SQl Server Constraints
Joe Webb : Disable constraints on a table

Deprecated column types

Looking to the future, I'm examining an application for deprecated features. Data types text, ntext & image are marked for deprecation in a future version of sql (although they still appear to be present in SQL 2012).

Finding them is easy via the information_schema views -

SELECT table_catalog, table_name, column_name, data_type
FROM information_schema.columns
WHERE data_type IN ('text','ntext','image')

If you want more information you can use the system tables directly. -

For SQL2005+ -

SELECT a.name name, b.name, c.name, b.max_length, *
FROM sys.objects a
INNER JOIN sys.columns b ON a.object_id = b.object_id
INNER JOIN sys.types c ON b.system_type_id = c.system_type_id
WHERE a.type='u'  
AND c.name IN ('text','ntext','image')
ORDER BY 1,2,3

For SQL 2000 -

SELECT a.name tablename, b.name columnname ,c.name datatype, b.length, *
FROM sysobjects a  
INNER JOIN syscolumns b  ON a.id=b.id  
INNER JOIN systypes c  ON c.xtype=b.xtype
WHERE a.xtype='u'  
AND c.name IN ('text','ntext','image')
ORDER BY 1,2,3

TSQL : Which protocols are being used by active connections?

-- what protocols are being used by active connections

SELECT DISTINCT net_library FROM sysprocesses

SELECT DISTINCT net_transport FROM sys.dm_exec_connections

TSQL : Fetch Service Account name

TSQL to get the SQL Server service account.

DECLARE @MSSqlService VARCHAR(50)
EXECUTE master.dbo.xp_instance_regread
 N'HKEY_LOCAL_MACHINE'
,N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER'
,N'ObjectName'
,@MSSqlService OUTPUT
,N'no_output'
SELECT @MSSqlService AS SQL_Service_Account

TSQL : TOP N PER GROUP using RANK()

An Adventureworks quick script to demonstrate top items in a group. It isn't the query I wrote today, but it is one I can publish here!

-- Sales Order ID, Name and Price for the 3 most expensive items in each order
-- We join to a derived table utilising the RANK function to order the items in each order by expense.
-- The WHERE clause then restricts the results according to the output of the RANK function
SELECT 
  S.SalesOrderID
 ,P.Name
 ,D.UnitPrice
FROM Sales.SalesOrderHeader S
INNER JOIN
 (SELECT 
  RANK() OVER (PARTITION BY SalesOrderID ORDER BY UnitPrice DESC) ItemCount
  ,* 
  FROM Sales.SalesOrderDetail)  D
ON S.SalesOrderID = D.SalesOrderID
INNER JOIN Production.Product P
ON P.ProductId = D.ProductId
WHERE ItemCount <= 3
ORDER BY SalesOrderID, Name, UnitPrice 

Solace : Tsql : ROW_NUMBER, RANK, DENSE_RANK and PARTITION BY

Luke Hayler : Rediscovering RANK

Windows Scheduled Tasks - Command Line Options

Delete that annoying task that keeps being recreated -

SCHTASKS /Delete /TN "Annoying Scheduled Application" /F

The rest of the options can be displayed like this -

H:\>SCHTASKS /?

SCHTASKS /parameter [arguments]

Description:
    Enables an administrator to create, delete, query, change, run and
    end scheduled tasks on a local or remote system. Replaces AT.exe.

Parameter List:
    /Create         Creates a new scheduled task.

    /Delete         Deletes the scheduled task(s).

    /Query          Displays all scheduled tasks.

    /Change         Changes the properties of scheduled task.

    /Run            Runs the scheduled task immediately.

    /End            Stops the currently running scheduled task.

    /?              Displays this help/usage.

Examples:
    SCHTASKS
    SCHTASKS /?
    SCHTASKS /Run /?
    SCHTASKS /End /?
    SCHTASKS /Create /?
    SCHTASKS /Delete /?
    SCHTASKS /Query  /?
    SCHTASKS /Change /?

Wait Stats (Quick Summary)

In SQL 2000 - 

DBCC SQLPERF(waitstats)

In SQL 2005+ 

SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms  
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC

To clear Wait Stats -

dbcc sqlperf(waitstats,clear)

Some you can safely ignore -

CLR_SEMAPHORE
LAZYWRITER_SLEEP
RESOURCE_QUEUE
SLEEP_TASK
SLEEP_SYSTEMTASK
SQLTRACE_BUFFER_FLUSH
WAITFOR
LOGMGR_QUEUE
CHECKPOINT_QUEUE
REQUEST_FOR_DEADLOCK_SEARCH
XE_TIMER_EVENT
BROKER_TO_FLUSH
BROKER_TASK_STOP
CLR_MANUAL_EVENT
CLR_AUTO_EVENT
DISPATCHER_QUEUE_SEMAPHORE
FT_IFTS_SCHEDULER_IDLE_WAIT
XE_DISPATCHER_WAIT
XE_DISPATCHER_JOIN
BROKER_EVENTHANDLER
TRACEWRITE
FT_IFTSHC_MUTEX
SQLTRACE_INCREMENTAL_FLUSH_SLEEP
SLEEP

Management Studio Error : mscorlib.tlb could not be loaded

For testing purposes, I've got a VM with SQL 2000, 2005 & 2008 all as named instances. Yes I know it sucks performance wise but it is only for testing deployment scripts!

Anyway, after installing it and applying the various service packs, I got this error :

mscorlib.tlb could not be loaded

The 'Microsoft Fix it 50701' hotfix downloadble here did not work for me, but scroll down that page and you'll find instructions to modify the registry. It points Management Studio at the correct version of the .NET framework. These did work, the only problem being I'm reportedly going to have to do this each time I patch SQL :(

Could not access Maintenance Plans in Management Studio

Error :
Method not found: 'Void Microsoft.SqlServer.Management.DatabaseMaintenance.TaskUIUtils..ctor()'. (Microsoft.SqlServer.MaintenancePlanTasksUI)

Investigation : 
SQL Server 2005 Management Studio is unpatched (9.00.1399)
Running SELECT @@Version shows the database engine is to be at Cumulative Update 9 (9.00.4294).

Solution :
Apply Service Pack to Management Studio

Ref : http://mbsturk.blogspot.com/2010/03/method-not-found-void.html

Fragmentation 101

Have had to explain this a number of times recently, hence ....

Inside the database -
 
SQL External fragmentation
External fragmentation is also known as logical fragmentation.
It means the index is jumbled up, is in the wrong order! Accessing the index is no longer a sequential operation.

If the index fill factor does not leave space for new leaf pages, page splits happen and external fragmentation occurs. External Fragmentation becomes more of a problem returning larger or ordered results sets.


Schedule regular index maintenance to prevent this.

SQL Internal fragmentation
Internal fragmentation is the reverse scenerio i.e there is too much free space in the index pages.
Extra reads are issued to return all the data, hence it takes longer to fulfil the I/O operation.



Look at setting index Fill Factor options and rebuild indexes to correct this.


Outside the database -

File system Fragmentation

This is caused by growth of physical data and log files.

As a DBA you manage growth manually. If you don't, the scenario described in Bad Autogrowth happens, i.e. physical fragmentation of sql server files.

Resolving this requires -

1. Resizing your data files appropriately
   and either -
2. Taking SQL offline to run defraggler or similar
   or
3. Taking SQL offline and moving the files off and back onto the drive.

In my recent experience option 3 is quickest.

Renaming SQL Server Instances

Working with virtual machines frequently now I often duplicate entire systems for testing purposes and rename the newly created machine. When SQL Server is included in these images, the new copy is left not knowing what it's own name is.

To tell SQL it has been renamed, you need to run the following.

sp_helpserver
select @@servername
go
 
sp_dropserver 'OLDSNAME'
go
sp_addserver 'NEWNAME','local'
go
 
sp_helpserver
select @@servername
go

Remember you will need to restart the SQL Server service after executing this. You could even do it automatically using this Rename SQL Installation script from back in the day. It will detect the names automatically.

SQL Server : What is running right now?

SQL 2000

select * from master.dbo.sysprocesses where status <> 'sleeping'

SQL 2005+

select * from sys.sysprocesses where status <> 'sleeping'

Easier by far, use Adam Machanic's 'Who is Active'

• sp_whoisactive 

PS : I've told you twice now!

SQL People Interview

My SQLPeople Interview was published today. You can read it here -

http://sqlpeople.net/admin/2011/10/03/richard-doering/

SQLPeople is the brainchild of SSIS Guru Andy Leonard (who I met when he flew over to the UK last week to present at SQLBits 9). It is a community project running events in the US (as well as the website). I found the site recently and enjoyed reading the DBA interviews. More proof if it were needed of the wide range of roles the term 'DBA' can encompass.

r

SQL Server in the Evening (4th Event)

This evening was a first for me. Fresh (knackered) from helping out at SQLBits at the weekend I faced one of my demons and made my debut at public speaking.

The opportunity was provided to me by Gavin Payne (@GavinPayneUK)  and it was tonight at his 'SQL Server in the evening' event that i took the beginners slot. The event is a SQL Server user group that runs in West Surrey. Tonight's venue was the Ramada Hotel on the Hog's Back in Farnham.

My session was first (no pressure) and was entitled CSI SQL : Auditing SQL Server. I felt I did ok but was initially rather nervous. My content addressed the way I have recently approached a large number of audits of SQL Server installations. It had quite a wide scope and was more of a memory jogger than a technical indepth talk. I finished by presenting the spreadsheets and scripts I developed to quickly audit systems. On a side note I loved the usb powerpoint clicker and have since put one on my birthday wish list (should I get the whim to present again!). I enjoyed doing it and found the positive feedback extremely encouraging.

After an delicious but delayed buffet (my fault for overrunning my time slot) we were treated to 2 further presentations. The first was an inspiring deep dive into PowerShell by Pete Rossi   (@RossiPete).  Powershell is high on my 'must play with' list for automating administration so I found this quite an eye opening session. Finally AlwaysOn functionality in Denali was the topic for Microsoft Certified Master Christian Bolton. Christian spent an hour explaining the how High Availability in SQL Server 2012 has improved.

A great evening overall and a large weight lifted for me by public speaking.

r

Windows 2003 : Reviewing Cluster Logs

Spent some time corresponding SQL events with the cluster event log today. A cluster failing over seemingly without good cause. Look for - Source - Clussvc Category - Failover Manager

The cause will be another blog post!

SQLBits 9

The 9th SQLBits was held in Liverpool this weekend. Despite it being 226 miles away from home that was no deterant for a few days of quality SQL content, company and beer.

This was my 5th SQLBits and I volunteered to help. This meant room monitoring (helping spearkers, tidying up etc) , helping attendees (mainly find rooms) and other light duties. For me volunteering gave me a purpose between sessions. Knowing the distributed nature of my work meant I was not able to furfil purchases from the exhibiting vendors I prefered not to add myself (once again) to their mailing lists. There is also a limit to how many conference freebies even my kids would appreciate!

The volunteering meant some of my sessions were allocated for me. This was fine as volunteers had given session preferences so they wouldnt miss content they needed. Ensuring everything went smoothly was expertly organised by Annette Allen (@Mrs_Fatherjack). I'm already looking forward to SQLBits 10 and would encourage SQL Professionals to make this a regular date. As a learning and social experience, SQLBits (and the SQLBits community) rocks!

The sessions I attended this time round, were -

Performance tuning from the field
Simon Sabin

Lightning Talks
Various Speakers    

Myths and Monsters of Flash
Fusion-io

Advanced SQL Server 2008 Troubleshooting
Klaus Aschenbrenner

The Art of War-Fast Track Data Warehouse & Fragmentation
James Rowland-Jones

Designing an SSIS Framework
Andy Leonard

Building a SSMS Add-in; The Agony and Ecstasy
Mark Pryce-Maher

Understanding SQL Server Execution Plans
Klaus Aschenbrenner    
   
Through the virtual looking glass – monitoring virtualised SQL
Gavin Payne

Whats new in Denali-TSQL
Dave Ballantyne    


Other SqlBits 9 Coverage :

• Richard Douglas
• Victoria Holt
• Mark Broadbent

Update, November 2011 : 
The conference videos have been uploaded in record time and are now available at sqlbits.com

Are my databases being used?

I came across a SQL 2005 server recently where it's administrator had forgotten which databases were in use (among other things). With the goal of wanting to perform a tidy up on the server, I wrote this script to prevent a lenghty, painful manual process.

The script fetches data about when the databases were last used and returns the following

• DatabaseName (Obvious really) 
• LastReadOperation - aggregated from index information in sys.dm_db_index_usage_stats
• LastWriteOperation - aggregated from index information in sys.dm_db_index_usage_stats
• DataFileDateStamp - file system date stamp 
• LogFileDateStamp - file system date stamp 

It is based on 'When were my databases last accessed ?' and fetches the file date information from the file system by using dbo.get_file_date. To fetch the file system information, it does use xp_cmdshell , which may be a security concern depending on your environment. Becauase it uses the file system, you need to run it in Management Studio on the server you are auditing.

USE master
GO

exec sp_configure 'show advanced options', 1;
reconfigure;
exec sp_configure 'xp_cmdshell',1;
reconfigure;
GO
IF OBJECT_ID('tempdb..##Results') IS NOT NULL
 BEGIN
 DROP TABLE ##Results
 END

CREATE TABLE ##Results ([DatabaseName] sysname NULL, [FileName] sysname NULL, [PhysicalName] NVARCHAR(260) NULL,  
    [FileType] VARCHAR(4) NULL, [DateFileSystem] DATETIME ) 
DECLARE @SQL VARCHAR(MAX)
 SELECT @SQL =  
 'USE [?] INSERT INTO ##Results([DatabaseName], [FileName], [PhysicalName],  
 [FileType])  
 SELECT DB_NAME(), 
 [name] AS [FileName],  
 physical_name AS [PhysicalName],  
 [FileType] =  
 CASE type 
 WHEN 0 THEN ''Data'''  
 +  'WHEN 1 THEN ''Log''' 
 +  'END
 FROM sys.database_files  (NOLOCK)
 ORDER BY [FileType], [file_id]' 
 
EXEC sp_MSforeachdb @SQL 
 
DECLARE @file_date_op datetime 

DECLARE  @db SYSNAME
DECLARE @filename NVARCHAR(260)
DECLARE tablecursor CURSOR FORWARD_ONLY FOR 
SELECT databasename, physicalname 
FROM   ##results

OPEN tablecursor 

WHILE (1 = 1) 
BEGIN 
FETCH NEXT FROM tablecursor 
INTO @db,@filename 

IF @@FETCH_STATUS <> 0 
BREAK; 
SET @file_date_op = null
print @filename
exec master.dbo.get_file_date 
  @file_name = @filename
 ,@file_date = @file_date_op OUTPUT
UPDATE ##Results 
SET  DateFileSystem = @file_date_op
WHERE   physicalname = @filename 
END 
CLOSE tablecursor 
DEALLOCATE tablecursor 

SELECT
 Name AS DatabaseName
   ,REPLACE(CONVERT(VARCHAR(30),MAX(CASE 
  WHEN ISNULL(last_user_seek,0) >= ISNULL(last_user_scan,0) AND ISNULL(last_user_seek,0) >= ISNULL(last_user_lookup,0) THEN ISNULL(last_user_seek,0)
  WHEN ISNULL(last_user_scan,0) >= ISNULL(last_user_lookup,0) THEN ISNULL(last_user_scan,0)
  ELSE ISNULL(last_user_lookup,0)
  END),121),'1900-01-01 00:00:00.000','') AS LastReadOperation
   ,REPLACE(CONVERT(VARCHAR(30),ISNULL(MAX(last_user_update),0),121),'1900-01-01 00:00:00.000','')  AS LastWriteOperation
   ,DataFile.DateFileSystem AS DataFileDateStamp
   ,LogFile.DateFileSystem AS LogFileDateStamp
FROM sys.databases d
LEFT JOIN sys.dm_db_index_usage_stats s
ON s.database_id = d.database_id 
LEFT JOIN ##Results DataFile
ON DataFile.DatabaseName = DB_NAME(d.database_id)
LEFT JOIN ##Results LogFile
ON LogFile.DatabaseName = DB_NAME(d.database_id)
WHERE DataFile.FileType = 'Data'
  AND LogFile.FileType = 'Log'
GROUP BY 
 d.name
   ,DataFile.DateFileSystem
   ,LogFile.DateFileSystem

GO

When were my databases last accessed?

This script uses sys.dm_db_index_usage_stats to get timestamps for the last read and write operations for a database.

SELECT
 DB_NAME(database_id) AS DatabaseName
   ,MAX(CASE 
  WHEN ISNULL(last_user_seek,'1900-01-01') >= ISNULL(last_user_scan,'1900-01-01') AND ISNULL(last_user_seek,'1900-01-01') >= ISNULL(last_user_lookup,'1900-01-01') THEN ISNULL(last_user_seek,'1900-01-01')
  WHEN ISNULL(last_user_scan,'1900-01-01') >= ISNULL(last_user_lookup,'1900-01-01') THEN ISNULL(last_user_scan,'1900-01-01')
  ELSE ISNULL(last_user_lookup,'1900-01-01')
  END) AS LastReadOperation
   ,MAX(last_user_update) AS LastWriteOperation
FROM sys.dm_db_index_usage_stats
GROUP BY 
 DB_NAME(database_id)

dbo.get_file_date

This procedure gets the date a file was updated, as reported by the file system.

exec sp_configure 'show advanced options', 1;
reconfigure;
exec sp_configure 'xp_cmdshell',1;
reconfigure;
go

use master
go

create procedure [dbo].[get_file_date](
@file_name varchar(max)
,@file_date datetime output
) AS 
BEGIN 
set dateformat dmy
declare @dir table(id int identity primary key, dl varchar(2555))
declare @cmd_name varchar(8000),@fdate datetime,@fsize bigint, @fn varchar(255)
set @fn=right(@file_name,charindex('\',reverse(@file_name))-1)
set @cmd_name='dir /-C "'+@file_name+'"'

insert @dir
exec master..xp_cmdshell @cmd_name

select @file_date=convert(datetime,ltrim(left(dl,charindex('   ',dl))),103) 
from @dir where dl like '%'+@fn+'%'

end
go

usage -

declare @file_date_op datetime 

exec master.dbo.get_file_date 
  @file_name = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\MSDBData.mdf'
 ,@file_date = @file_date_op OUTPUT

SELECT @file_date_op

SQL 2008 : Schema Gotcha!

A colleague and I wasted a good deal of time trying to debug a problem today.
Why did the application work, but running the same SQL in Management Studio did not!

It turned out that the application logged in as 'appuser' and had a default schema 'schema1'.
When running inside Management Studio he was logged in as sa (it's a developer's local machine).
sa being a sysadmin, it has the default schema dbo. The old code (ported from sql 2000, no schema given in the query) therefore didn't know to look in 'schema1' for the objects and failed.

Therefore, always prefix object names with the owner (sql 2000) or the schema (2005+)

It will also save SQL trying to find a schema.

http://serverfault.com/questions/203551/sql-server-2008-default-schema-not-being-respected

SQL 2005 : Maintenance Plan won't update, Agent job won't delete

I'm sure this is a documented bug somewhere, but I came across a Maintenance Plan / Agent Job issue today. The client was running SQL 2005 SP2 (Build 9.00.3042.00)

I created a Maintenance Plan (Transaction Log backups), scheduled it (for every 30 minutes) and it ran fine for a few hours. I came back to it today to find it no longer running. On examining the Maintenance Plan it would not let me view the schedule, returning to SQL Agent jobs I was unable to delete the job either.

The message I got was -

Trying the same from TSQL,

delete from sysmaintplan_subplans
where subplan_description = 'TL backups'

Produced the same -

Msg 547, Level 16, State 0, Line 2 The DELETE statement conflicted with the REFERENCE constraint "FK_sysmaintplan_log_subplan_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_log", column 'subplan_id'. The statement has been terminated.

This left me we with 3 options.

1. Disabling constraints in a system database in order to delete the record (not recommended) 
2. Working out the order to delete maintenance plan records from system tables in order to obey referential integrity 
3. Finding someone who has done it before.

Fortunately option 3 was just a few keystrokes away. Clay McDonald has already written a procedure and has published it in this awesome workaround : Can’t Delete Jobs (Microsoft SQL Server, Error: 547) 

Checking CHECKDB !

When reviewing some agent logs this morning it struck me that the maintenance plans had run a little quickly. The agent job hadn't failed, but the run duration seemed remarkably short (considering the database size). The plan concerned was one of those 'all maintenance in one step' jobs hence I couldn't quantify how the run duraton was comprised. Backups were present in their directory, so one of the other steps must have silently failed.

Running a server audit script picked up Integrity Checks as not having occurred. My audit script uses this piece of code to get the date.

CREATE TABLE #temp (        
       [ParentObject]    VARCHAR(255)
       , [Object]       VARCHAR(255)
       , Field          VARCHAR(255)
       , [Value]        VARCHAR(255) 
   ) 

INSERT INTO #temp EXECUTE ('DBCC DBINFO WITH TABLERESULTS')

SELECT DISTINCT Value AS DBCCDate FROM #temp WHERE Field = 'dbi_dbccLastKnownGood'

There are 2 caveats with this code

1. It only works on SQL 2005+
2. It needs DISTINCT in the select statement to run on SQL 2008 (where a bug means the date is reported twice).

So, having found that the DBCC checks had not occurred, i turned to google :/
It turns out the client were running SQL 2005 SP2, build 9.00.3042.
In this build there is a bug re; integrity checks failing in maintenance plans.

Top Tweets

Top 3 Tweets today! Linking these to read them later...

Jamie Thomson (@jamiet) tweets :
"Down with Primary Keys?" by David Portas. Always enjoy going back to this article.

Tony Rogerson (@tonyrogerson) tweets :
Database Design (Normalisation) rules poster

Joesph Sack (@josephsack) tweets :
Windows 8 to directly support ISO and VHD files

Online index rebuilds : Unsupported data types

I was attempting to rebuild some indexes ONLINE (using SQL 2005 Enterprise) when I got this error.

Msg 2725, Level 16, State 2, Line 4 Online index operation cannot be performed for index 'PKCI_MyTable_SearchIndex' because the index contains column 'ReportData' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.

The error tells us what the problem is, i.e. the inability to use certain data types when rebuilding indexes ONLINE. In my case, the offending column was a deprecated TEXT data type. Still, it reminded me to audit the database for such columns...

Finding deprecated data types-

SELECT
   sys_schemas.Name as [schema_name]
 , sys_tables.Name AS [table_name]
 , sys_columns.Name AS [column_name]
 , sys_types.Name AS [datatype_name]
FROM Sys.Tables sys_tables (nolock)
JOIN Sys.Schemas sys_schemas (nolock)
ON sys_schemas.Schema_Id = sys_tables.Schema_Id
JOIN Sys.Columns sys_columns (nolock)
ON sys_columns.Object_Id = sys_tables.Object_Id
JOIN Sys.Types sys_types (nolock)
ON sys_types.System_Type_Id = sys_columns.System_Type_Id
AND sys_types.Name IN ('text,'ntext','image')

See also Deprecated in SQL 2008 R2

Cleaning up MSDB

I've been working extensively with old SQL 2000 instances recently. The majority have been left to fend for themselves without DBA support. Whilst backups and index maintenance have for the most part been occuring, the msdb databases are bloated (and fragmented). This is due to some missing functionality in SQL 2000 , namely clearing down the history tables. This post summarises my previous ones on MSDB clearup.

1) Backup History

To determine the date of the oldest backup history record, run this -

SELECT TOP 1 backup_start_date FROM msdb.dbo.backupset WITH (NOLOCK) ORDER BY backup_set_id ASC

If there is significant history to remove, use the Ultimate Delete Backup History Script to remove it. To prevent it building up again in the future, regularly run (or schedule) -

DECLARE @BackupHistoryDeleteDate DATETIME
SET @BackupHistoryDeleteDate = DATEADD(m,-1,GETDATE())
EXEC msdb.dbo.sp_delete_backuphistory @BackupHistoryDeleteDate ;

2) Agent Job History

To determine the date of the oldest agent history record, use this -

DECLARE @agentstartdate DATETIME
SELECT @agentstartdate = CONVERT(datetime,CONVERT(VARCHAR(8),MIN(Run_date))) from msdb..sysjobhistory WITH (NOLOCK) OPTION (MAXDOP 1)

If there is significant history to remove, use the Clear Job History (Nibble Delete) Script to remove it.
To prevent it building up again in the future, regularly run (or schedule) -
For SQL 2000 -

DECLARE @JobHistoryDeleteDate DATETIME
SET @JobHistoryDeleteDate = DATEADD(m,-1,GETDATE())
DECLARE @datepart INT
SET @datepart = CONVERT(INT, CONVERT(VARCHAR, @JobHistoryDeleteDate, 112))
DELETE FROM msdb.dbo.sysjobhistory WHERE (run_date < @datepart)

For SQL 2005+ you can use sp_purge_jobhistory (it now takes a date parameter), hence regularly run -

DECLARE @JobHistoryDeleteDate DATETIME
SET @JobHistoryDeleteDate = DATEADD(m,-1,GETDATE())
EXEC sp_purge_jobhistory @oldest_date = @JobHistoryDeleteDate 

You can also set a row limit by using this registry entry.

3) Maint Plan History

To determine the date of the oldest maintenance plan record, use this -

DECLARE @mpstartdate DATETIME
SELECT @mpstartdate = MIN(end_time) FROM msdb.dbo.sysdbmaintplan_history WITH (NOLOCK) OPTION (MAXDOP 1)

If there is significant history to remove, use the Clear Maintenance Plan History (Nibble Delete) Script to remove it. To prevent it building up again in the future, regularly run (or schedule) -

DECLARE @MaintPlanHistoryDeleteDate DATETIME
SET @MaintPlanHistoryDeleteDate = DATEADD(m,-1,GETDATE())
DELETE FROM msdb.dbo.sysdbmaintplan_history WHERE (end_time < @MaintPlanHistoryDeleteDate 

To keep all 3 tables under control, here is a script I schedule on my SQL 2000 instances, to keep MSDB in shape

-- Keep MSDB Tidy
-- This script keeps 3 tables that have a tendency to bloat down to 2 weeks data
-- It also Cycles error logs when it is executed

DECLARE @HistoryDeleteDate DATETIME
SET @HistoryDeleteDate = DATEADD(week,-2,GETDATE())

-- 1) Backup History
EXEC msdb.dbo.sp_delete_backuphistory @HistoryDeleteDate ;

-- 2) Agent Job History
DECLARE @datepart INT
SET @datepart = CONVERT(INT, CONVERT(VARCHAR, @HistoryDeleteDate, 112))
DELETE FROM msdb.dbo.sysjobhistory WHERE (run_date < @datepart)

-- 3) Maint Plan History
DELETE FROM msdb.dbo.sysdbmaintplan_history WHERE (end_time < @HistoryDeleteDate) 

-- 4) Cycle SQL Server Error logs
EXEC master.dbo.sp_cycle_errorlog;

Clear Maintenance Plan History (nibble delete)

Similar to Clear Backup History (nibble delete) and Clear Job History (nibble delete), this third script applies the same technique to the maintenance plan history table.

-- Maintenance Plan History

USE MSDB
GO

DECLARE @OldestJobHistoryDate DATETIME
DECLARE @DaysToLeave INT
DECLARE @DaysToDeleteAtOnce INT
DECLARE @DeleteDate DATETIME
DECLARE @Counter INT
DECLARE @CounterText VARCHAR(30)

SELECT @OldestJobHistoryDate = end_time 
FROM msdb.dbo.sysdbmaintplan_history 
WHERE sequence_id = (select MIN(sequence_id) FROM msdb.dbo.sysdbmaintplan_history )

SELECT @OldestJobHistoryDate
SET @DaysToLeave = 30
SET @DaysToDeleteAtOnce = 1

SELECT @Counter = DATEDIFF(DAY,@OldestJobHistoryDate,GETDATE())

WHILE @Counter >= @DaysToLeave  
BEGIN   
 SET @CounterText = CONVERT(VARCHAR(30),GETDATE(),21) + ' processing ' + CONVERT(VARCHAR(30),DATEADD(DAY, -@Counter,GETDATE()),21)
 SELECT @DeleteDate = CONVERT(VARCHAR(30),DATEADD(DAY, -@Counter,GETDATE()),21) 
 RAISERROR (@CounterText , 10, 1) WITH NOWAIT   
 DELETE FROM msdb.dbo.sysdbmaintplan_history WHERE end_time < @DeleteDate 
 SELECT @Counter = @Counter - @DaysToDeleteAtOnce  
END 
GO

Maintenance Plan will not SAVE

Came across this issue, where a Maintenance Plan would not save...

Solution :

To resolve this, either apply the latest SQL Service pack

or

Open a command prompt, and re-register 2 DLLs -

regsvr32 msxml3.dll
regsvr32 msxml6.dll

Loop : Remove Auto Shrink from all databases!

SQL 2000 Version :

DECLARE @databasename varchar(100)
DECLARE @sqlAlterStatement varchar(500)

DECLARE NastyCursorThing CURSOR READ_ONLY FOR
SELECT Name FROM sysdatabases
WHERE DBID > 4 AND DATABASEPROPERTYEX(name, 'IsAutoShrink') = 1

OPEN NastyCursorThing
FETCH NEXT FROM NastyCursorThing INTO @databasename
WHILE @@FETCH_STATUS = 0
	BEGIN
	SET @sqlAlterStatement ='ALTER DATABASE [' + @databasename + '] SET AUTO_SHRINK OFF WITH NO_WAIT' + CHAR(10)
	print @sqlAlterStatement
	EXEC(@sqlAlterStatement)
	FETCH NEXT FROM NastyCursorThing INTO @databasename
	END

CLOSE NastyCursorThing
DEALLOCATE NastyCursorThing

SQL 2005/2008 Version :

DECLARE @databasename varchar(100)
DECLARE @sqlAlterStatement varchar(500)

DECLARE NastyCursorThing CURSOR READ_ONLY FOR
SELECT name FROM sys.databases
WHERE database_id >  4 AND DATABASEPROPERTYEX(name, 'IsAutoShrink') = 1

OPEN NastyCursorThing
FETCH NEXT FROM NastyCursorThing INTO @databasename
WHILE @@FETCH_STATUS = 0
	BEGIN
	SET @sqlAlterStatement ='ALTER DATABASE [' + @databasename + '] SET AUTO_SHRINK OFF WITH NO_WAIT' + CHAR(10)
	print @sqlAlterStatement
	EXEC(@sqlAlterStatement)
	FETCH NEXT FROM NastyCursorThing INTO @databasename
	END

CLOSE NastyCursorThing
DEALLOCATE NastyCursorThing

Bookmark : How can you tell if an index is REALLY a duplicate?

Another excellent post from Kimberley Tripp...
sqlskills  : How can you tell if an index is REALLY a duplicate?

SQL Server Support Dates

As an exercise to see how up to date clients are with patching, I produced the following list.
So, (as of August 2011) -

SQL 2000 (yes some organisations still have it)

• SP4 Released May 2005. Support ended April 2008
• SP3 Released Jan 2003. Support ended July 2007
• SP2 Released Nov 2001. Support ended Apr 3003
• SP1 Released Jun 2001. Support ended Feb 2002
• RTM Released Nov 2000. Support ended Dec 2001

SQL 2005

• SP4 + Updates (Build > 9.0.00.5254). Current.
• SP4 Released Dec 2010. Support ended April 2011.
• SP3 Released Nov 2008. Support ended Apr 2011.
• SP2 Released Jan 2007. Support ended Jan 2010.
• SP1 Released Apr 2006. Support ended Apr 2008.
• RTM Released Jan 2006. Support ended July 2007.

SQL 2008

• SP2 Released Sep 2010. Current.
• SP1 Released Apr 2009. Current. Support ends Oct 2011
• RTM Released Aug 2008. Support ended Apr 2010

SQL 2008 R2

• RTM Released Apr 2010. Current. Support ends Jan 2014

Links

• Important SQL Server and Windows “End of Support” Dates you should know about….
• SQL 2000 Release Date Calendar
• Cumulative list of the hotfixes that are available for SQL Server 2000 SP4
• Lifecycle Supported Service Packs

SQL Management Studio : Start Clean!

When managing multiple servers I find this to be an essential tip.

From the menu bar select 'Tools > Options '.
Then from the 'General' pane (shown below), select 'Open empty environment' in the 'At Startup' drop down menu.

This means you will no longer be prompted to connect to the last server you were using when you start Management Studio.

Use this together with the other SSMS speedup tips listed here to speed up your Management Studio experience.

File Fragmentation : Contig.exe

Contig is a free command line defragmentation tool.
Now on Microsoft Technet it was formerly part of the sysinternals project.
It can be used to analyze fragmentation without performing any defragmentation with the -a switch.
The advantage of the tool is that you can specify individual files to analyse. You do not have to wait on results for an entire drive to be analysed.
Running Contig without parameters tells you how to use it -

D:\MSSQL\Data>contig

Contig v1.6 - Makes files contiguous
Copyright (C) 1998-2010 Mark Russinovich
Sysinternals - www.sysinternals.com

Contig is a utility that defragments a specified file or files.
Use it to optimize execution of your frequently used files.

Usage:
contig [-a] [-s] [-q] [-v] [existing file]
or contig [-f] [-q] [-v] [drive:]
or contig [-v] -n [new file] [new file length]

-a: Analyze fragmentation
-f: Analyze free space fragmentation
-q: Quiet mode
-s: Recurse subdirectories
-v: Verbose

Contig can also analyze and defragment the following NTFS metadata files:
$Mft
$LogFile
$Volume
$AttrDef
$Bitmap
$Boot
$BadClus
$Secure
$UpCase
$Extend
 

To view fragmentation, use the -a switch like this...

D:\MSSQL\Data>contig -a *.mdf

Contig v1.6 - Makes files contiguous
Copyright (C) 1998-2010 Mark Russinovich
Sysinternals - www.sysinternals.com

D:\MSSQL\Data\Accounting.MDF is defragmented
D:\MSSQL\Data\Accounting_UAT.MDF is in 14 fragments
D:\MSSQL\Data\AuditPC.mdf is in 7 fragments
D:\MSSQL\Data\DataStore.mdf is in 34 fragments
D:\MSSQL\Data\FakeDb.MDF is in 5 fragments
D:\MSSQL\Data\Personel.mdf is in 4 fragments
D:\MSSQL\Data\master.mdf is in 3 fragments
D:\MSSQL\Data\model.mdf is in 2 fragments
D:\MSSQL\Data\msdbdata.mdf is in 5182 fragments
D:\MSSQL\Data\northwnd.mdf is in 2 fragments
D:\MSSQL\Data\pubs.mdf is in 2 fragments
D:\MSSQL\Data\Software.mdf is in 4 fragments
D:\MSSQL\Data\Software_UAT.mdf is in 9 fragments
D:\MSSQL\Data\Telecoms.mdf is in 17 fragments
D:\MSSQL\Data\tempdb.mdf is in 42 fragments
D:\MSSQL\Data\tools.mdf is in 5 fragments
D:\MSSQL\Data\Weblogs.MDF is in 89 fragments

Summary:
Number of files processed : 17
Average fragmentation : 176.03 frags/file


Download Link : http://technet.microsoft.com/en-us/sysinternals/bb897428

Bookmark : How much memory is each database using

John breaks from this SFTW series with a script  to gauge memory use -
http://www.johnsansom.com/how-much-memory-is-each-sql-server-database-using/

SQL Server : Auditing disk configuration

Disks or SAN volumnes presented for SQL Server are best formatted and aligned for optimal use.
Auditing an existing SQL server however, you will want to check whether this is the case.

Checking Cluster Size (File Allocation unit)
Use FSUTIL, like this -

C:\>fsutil fsinfo ntfsinfo s:
NTFS Volume Serial Number :       0x385ed1bb5ed171dc
Version :                         3.1
Number Sectors :                  0x00000000207fcb54
Total Clusters :                  0x00000000040ff96a
Free Clusters  :                  0x00000000017b10dc
Total Reserved :                  0x0000000000000000
Bytes Per Sector  :               512
Bytes Per Cluster :               4096
Bytes Per FileRecord Segment    : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length :           0x000000022cfc0000
Mft Start Lcn  :                  0x00000000000c0000
Mft2 Start Lcn :                  0x000000000207fcb5
Mft Zone Start :                  0x00000000002a9720
Mft Zone End   :                  0x00000000008dff40

Bytes Per Cluster is the figure we are after. In the example above it is 4096 bytes (4K). This is NTFS default and is less than optimal for SQL Server (64K reads & writes).


Checking Partition Alignment
There are 2 methods of doing this. The first is WMIC -

C:\>wmic partition get BlockSize, StartingOffset, Name, Index
BlockSize  Index  Name                   StartingOffset
512        0      Disk #0, Partition #0  16384
512        0      Disk #1, Partition #0  32256
512        0      Disk #2, Partition #0  32256
512        0      Disk #3, Partition #0  32256
512        0      Disk #4, Partition #0  32256
512        0      Disk #5, Partition #0  32256

If WMIC is not available, DISKPART commands can reveal how partition alignment is configured.

C:\> DISKPART

DISKPART> select volume s:

Volume 5 is the selected volume.

DISKPART> list partition

Partition ### Type Size Offset

------------- ---------------- ------- -------

* Partition 1 Primary 37 GB 32 KB

DISKPART> exit


In the examples above the majority of partitions are aligned to 32K (32256 bytes), again less than ideal.

See Disk Formatting & Partition Alignment for SQL Server

TSQL : Tidy logical filenames

Works for the first data and log file for a database, renaming the logical filenames to match the database name with either _Data or _Log appended as appropriate.

-- Sort Logical filenames
DECLARE  @dbname NVARCHAR(255) 
DECLARE  @groupid INTEGER 
DECLARE  @logicalfilename NVARCHAR(255) 
DECLARE  @newlogicalfilename NVARCHAR(255) 
DECLARE  @tsql VARCHAR(1000)
DECLARE databasefiles CURSOR FORWARD_ONLY FOR 
SELECT 
	 DB_NAME(dbid) AS DBName
	,groupid 
	,RTRIM(name) AS LogicalFileName
FROM sysaltfiles
WHERE DB_NAME(dbid) NOT IN ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')
AND groupid IN (0,1) AND fileid IN (1,2)

OPEN databasefiles 
WHILE (1 = 1) 
	BEGIN 
	FETCH NEXT FROM databasefiles 
	INTO @dbname, @groupid, @logicalfilename

	IF @@FETCH_STATUS <> 0 
	BREAK; 
	IF @groupid = 1 SET @newlogicalfilename = @dbname + '_Data'
	IF @groupid = 0 SET @newlogicalfilename = @dbname + '_Log'
	 
	IF (@logicalfilename <> @newlogicalfilename) AND (DATABASEPROPERTY(@dbname,'IsOffline')=0)
	BEGIN
		SET @tsql = 'ALTER DATABASE [' + @dbname + '] MODIFY FILE (NAME=N''' + @logicalfilename +''', NEWNAME=N'''+ @Newlogicalfilename +''');'
		RAISERROR (@tsql , 10, 1) WITH NOWAIT 
		EXECUTE (@tsql)
	END
	
	END 

CLOSE databasefiles 
DEALLOCATE databasefiles 

Ultimate Delete Backup History Script! (nibble delete)

Following my musings on Backup History Performance, i.e. creating additional indexes in MSDB and nibble deleting those records I have found a further trick.

I've created solace_delete_backuphistory which manually deletes from the msdb tables, only with NOLOCK hints too. Much faster than sp_delete_backuphistory, and it is listed here along with the supporting indexes and loop...

-- Create Indexes
USE msdb
GO
CREATE INDEX NCI_backupset_backup_set_id ON backupset(backup_set_id)
GO
CREATE INDEX NCI_backupset_backup_set_uuid ON backupset(backup_set_uuid)
GO
CREATE INDEX NCI_backupset_media_set_id ON backupset(media_set_id)
GO
CREATE INDEX NCI_backupset_backup_finish_date ON backupset(backup_finish_date)
GO
CREATE INDEX NCI_backupset_backup_start_date ON backupset(backup_start_date)
GO
CREATE INDEX NCI_backupmediaset_media_set_id ON backupmediaset(media_set_id)
GO
CREATE INDEX NCI_backupfile_backup_set_id ON backupfile(backup_set_id)
GO
CREATE INDEX  NCI_backupmediafamily_media_set_id ON backupmediafamily(media_set_id)
GO
CREATE INDEX  NCI_restorehistory_restore_history_id ON restorehistory(restore_history_id)
GO
CREATE INDEX NCI_restorehistory_backup_set_id ON restorehistory(backup_set_id)
GO
CREATE INDEX  NCI_restorefile_restore_history_id ON restorefile(restore_history_id)
GO
CREATE INDEX NCI_restorefilegroup_restore_history_id ON restorefilegroup(restore_history_id)
GO

-- Custom delete backup history
USE MSDB
GO
CREATE PROC solace_delete_backuphistory (@cleardate DATETIME)
AS
BEGIN
SET NOCOUNT ON
DELETE FROM msdb..restorefile
FROM msdb..restorefile rf WITH (NOLOCK)
INNER JOIN msdb..restorehistory rh WITH (NOLOCK) ON rf.restore_history_id = rh.restore_history_id
INNER JOIN msdb..backupset bs WITH (NOLOCK) ON rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date <= @cleardate

DELETE FROM msdb..restorefilegroup
FROM msdb..restorefilegroup rfg WITH (NOLOCK) 
INNER JOIN msdb..restorehistory rh WITH (NOLOCK) ON rfg.restore_history_id = rh.restore_history_id
INNER JOIN msdb..backupset bs WITH (NOLOCK) ON rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date  <= @cleardate

DELETE FROM msdb..restorehistory
FROM msdb..restorehistory rh WITH (NOLOCK) 
INNER JOIN msdb..backupset bs WITH (NOLOCK) ON rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date  <= @cleardate

DELETE FROM msdb..backupfilegroup
FROM msdb..backupfilegroup bfg WITH (NOLOCK) 
INNER JOIN msdb..backupset bs WITH (NOLOCK) ON bfg.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date  <= @cleardate


DELETE FROM msdb..backupfile
FROM msdb..backupfile bf WITH (NOLOCK) 
INNER JOIN msdb..backupset bs WITH (NOLOCK) ON bf.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date  <= @cleardate

DELETE FROM msdb..backupset 
WHERE backup_finish_date  <= @cleardate
SET NOCOUNT OFF
END
GO
 
-- Nibble Delete Backup History with loop
USE MSDB
GO
DECLARE @OldestBackupDate DATETIME
DECLARE @DaysToLeave INT
DECLARE @DaysToDeleteAtOnce INT
DECLARE @DeleteDate DATETIME

DECLARE @Counter INT
DECLARE @CounterText VARCHAR(100)

SELECT  @OldestBackupDate = MIN(backup_start_date) FROM msdb..backupset  
SELECT  @OldestBackupDate
SET @DaysToLeave = 30
SET @DaysToDeleteAtOnce = 1

SELECT @Counter = DATEDIFF(DAY,@OldestBackupDate,GETDATE())

WHILE @Counter >= @DaysToLeave  
BEGIN   
 SET @CounterText = CONVERT(VARCHAR(30),GETDATE(),21) + ' processing ' + CONVERT(VARCHAR(30),DATEADD(DAY, -@Counter,GETDATE()),21) 
 SELECT @DeleteDate = CONVERT(VARCHAR(30),DATEADD(DAY, -@Counter,GETDATE()),21) 
 RAISERROR (@CounterText , 10, 1) WITH NOWAIT   
 --EXEC sp_delete_backuphistory @DeleteDate ;
 EXEC solace_delete_backuphistory @cleardate = @DeleteDate
 SELECT @Counter = @Counter - @DaysToDeleteAtOnce  
END 

GO

SuperSocket Info: Bind failed on TCP port 1433

Had this error today on a relatively busy clustered SQL 2000 configuration.

SuperSocket Info: Bind failed on TCP port 1433

SQL was still accessable via Named Pipes fortunately.

The link contains information on a registry key (TcpAbortiveClose) fix
http://support.microsoft.com/kb/307197/EN-US

Preparing a Windows 2008 R2 / Windows 7 USB Installer

Using my Windows 2008 R2 disc (dvd - drive E:) and a  blank usb stick (drive X: - formatted to NTFS), I launched a command prompt and ran...

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\->e:

E:\>cd boot

E:\boot>bootsect /NT60 x:
Target volumes will be updated with BOOTMGR compatible bootcode.

X: (\\?\Volume{3b02ba7d-a4d3-11df-88f8-001377adfda5})

    Successfully updated FAT32 filesystem bootcode.

Bootcode was successfully updated on all targeted volumes.

E:\boot>xcopy e:\*.* /s /e /f x:\



994 File(s) copied

E:\boot>

Ref : Shane Milton's Technology Blog - Installing Windows 7 or Windows Server 2008 R2 from USB Stick

TSQL : Renaming Foreign Keys

I ran into this today, and found Richard Dingwall's blog post where he had found the same.

If you need to rename a foreign key -

Qualify the foreign key name by prefixing it with the schema name.

sp_rename 'Shopping.FK_Product_Caetgory', 'FK_Product_Category', 'OBJECT'

SQL 2005 : Database is in transition...Error 952

I detached a database in SQL 2005 today, doing so using sp_detach. 
The operation should have been pretty instant, but after a few minutes I realised something was up.


The database was stuck 'in transition', or so it appeared to Management Studio. I contemplated what to do. I wanted to avoid restarting the service and leaving the db in an inconsistent state, and hence googled the following...


Error 952 Database is in Transition


As it happens, the database isn't stuck at all, it's just Management Studio thinks it is.
The solution. RESTART MANAGEMENT STUDIO.   Argh!

SQL Sysadmin : Clear Job History (nibble delete)

This post is a minor change to my Clear Backup History (nibble delete) script to apply the same technique to Deleting Sql Agent Job history. If you find a server where no one has done this for a while (ever?) or where maintenance plans are missing, you'll need this script. Once again, nibble deleting history tables prevents large transactions that could cause server slowdowns. This works, 1 day at a time, starting with the oldest record. If you are wondering why I do this rather than looping sp_purge_jobhistory, that is because it only takes a date parameter on sql 2005+

-- Nibble Delete Job History

USE MSDB
GO

DECLARE @OldestJobHistoryDate DATETIME
DECLARE @DaysToLeave INT
DECLARE @DaysToDeleteAtOnce INT
DECLARE @DeleteDate DATETIME
DECLARE @Counter INT
DECLARE @CounterText VARCHAR(100)
DECLARE @datepart INT

SELECT @OldestJobHistoryDate = convert(datetime,rtrim(run_date)) 
FROM msdb..sysjobhistory
WHERE instance_id = (select MIN(instance_id) FROM msdb..sysjobhistory)

SELECT @OldestJobHistoryDate
SET @DaysToLeave = 30
SET @DaysToDeleteAtOnce = 1

SELECT @Counter = DATEDIFF(DAY,@OldestJobHistoryDate,GETDATE())

WHILE @Counter >= @DaysToLeave  
BEGIN   
 SET @CounterText = CONVERT(VARCHAR(30),GETDATE(),21) + ' processing ' + CONVERT(VARCHAR(30),DATEADD(DAY, -@Counter,GETDATE()),21)
 SELECT @DeleteDate = CONVERT(VARCHAR(30),DATEADD(DAY, -@Counter,GETDATE()),21) 
 RAISERROR (@CounterText , 10, 1) WITH NOWAIT   
 SET @datepart = CONVERT(INT, CONVERT(VARCHAR, @DeleteDate, 112))
 DELETE FROM msdb.dbo.sysjobhistory WHERE (run_date < @datepart)
 SELECT @Counter = @Counter - @DaysToDeleteAtOnce  
END 
GO

Shrink Current Log file

Useful in lengthy deployment scripts in conjunction with CHECKPOINT commands

/*
Shrinks log file for current database
Useful for index deployment script
*/
DECLARE @logfilename NVARCHAR(50)
SELECT @logfilename = name FROM sysfiles WHERE fileid = 2
DBCC SHRINKFILE (@logfilename , 0, TRUNCATEONLY)

Windows 2000 : Support for disks over 128GB

Yes I know that Windows 2000 is ancient, rarely used nowadays etc but I've had cause to build a VM to perform some testing.
That's why I've come accross this long forgotten nugget, how to get Windows 2000 to support drives over 128GB in size!

It's a registry entry. Either enter it manually of paste the content below into a .reg file and dounle click the file.

REGEDIT4

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\atapi\Parameters]

"EnableBigLba"=dword:00000001

Reboot the box, and hey presto...

http://www.lazyllama.com/misc/bigdisk.html

Backup History Performance

I recently has cause to cleanup the backup history on a SQL 2000 box. Not an issue, standard DBA fare really. SQL 2000 never had history cleanup options in the maintainence plans. Therefore we have to create TSQL jobs to remove backup history.

Given there was several years of backup history I started to clear backup history using a nibble delete approach. This proved to be slower than I anticipated so I started investigating further.

The quickest option would have been to directly modify the system tables, truncating them. Whilst constraints prevent this, there are solutions out there that tell you in which order to clear down the underlying tables. Pradeep Adiga is one of many to blog on pruning backup history and the fact that only 4 indexes exist on the 8 backup tables in MSDB.

Of all the scripts for additional MSDB indexes, Geoff Hiten provides the most complete solution in his post MSDB Performance Tuning. A script for MSDB indexes is located there which greatly improves the time to perform housekeeping on the backup tables.

I should include it in my build scripts really, and roll the indexes out as standard.

TSQL : Correct Compatibility Levels

I'm always finding databases on sites that were not put into the correct compatibility mode when server migrations/upgrades occurred.
This script sorts them all out at once.

DECLARE @ServerVersion INT
SELECT @ServerVersion = 10 * (CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1) AS INTEGER))

-- loop databases setting compatibility mode correctly
DECLARE GET_DATABASES CURSOR
READ_ONLY
FOR SELECT NAME FROM SYS.DATABASES WHERE COMPATIBILITY_LEVEL != CAST(@ServerVersion AS VARCHAR(10))
DECLARE @DATABASENAME NVARCHAR(255)
DECLARE @COUNTER INT
SET @COUNTER = 1
OPEN GET_DATABASES
FETCH NEXT FROM GET_DATABASES INTO @DATABASENAME
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- change database compatibility
EXECUTE sp_dbcmptlevel @DATABASENAME , @ServerVersion
PRINT  @DATABASENAME + ' changed'
SET @COUNTER = @COUNTER + 1
END
FETCH NEXT FROM GET_DATABASES INTO @DATABASENAME
END
CLOSE GET_DATABASES
DEALLOCATE GET_DATABASES

adapted from 'Database Compatibility Levels : How to change all at Once' to detect the server version

Using NOEXEC for conditional processing

NOEXEC allows you to do conditional processing even if you have GO statements splitting your script into batches.

DECLARE @runit INT
SET @runit =0

SET NOEXEC OFF

PRINT '1'

IF @runit = 0
 BEGIN
 PRINT  'Skipping Next Section'
 SET NOEXEC ON
 END
GO

PRINT '2'
GO
PRINT '3'
GO
PRINT '4'
GO

SET NOEXEC OFF
PRINT '5'
GO

Run the script twice, changing the value of @runit to 1 the second time. Cool huh?

MSDN : NOEXEC

TSQL : Updating jobs that originated from an MSX server

If you try to update a SQL Agent Job or Maintainence plan that was set up using an MSX server (Master/Target environment) you get this error.

Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.

If an MSX server has been decommissioned, renamed or is unrecoverable you have a genuine need to override this message. Providing you have permissions on the local server, you can update the entries in msdb directly.

This query updates all jobs, making their 'owner' (originating_server). the local server.

UPDATE msdb.dbo.sysjobs
SET originating_server = CONVERT(nvarchar, SERVERPROPERTY('servername'))
WHERE originating_server <> CONVERT(nvarchar, SERVERPROPERTY('servername'))

Once run, you can update the jobs once more.

ref : MSX Error

TSQL : Development / UAT Server Prep

A simple script to loop all databases, setting them to SIMPLE recovery mode and SHRINKing any logs.

Very useful for restoring UAT / DEV databases from live environments.

sp_msforeachdb @command1 = '
 USE [?];
 IF DB_NAME() <> ''tempdb''
 BEGIN
  PRINT ''---''
  PRINT DB_NAME()
  PRINT ''---''
  DECLARE @databasename VARCHAR(1000)
  SET @databasename = DB_NAME()
  
  DECLARE @sqlcmd NVARCHAR(1000)
  SET @sqlcmd = ''ALTER DATABASE ['' + @databasename+ ''] SET RECOVERY SIMPLE ''
  EXECUTE (@sqlcmd)
  
  DECLARE @logfilename VARCHAR(1000)
  SELECT @logfilename = RTRIM(name) from sysfiles where fileid = 2
  SELECT @logfilename
  DBCC SHRINKFILE (@logfilename , 0, TRUNCATEONLY)
 END '

TSQL : SQL 2000 : List Primary Key Columns

SELECT
sysobjects.name AS TableName
,sysindexes.name AS PKName
,syscolumns.colid AS ColumnOrder
,index_col(object_name(sysindexes.id), sysindexes.indid,syscolumns.colid) AS ColumnName
FROM sysobjects 
INNER JOIN sysindexes
ON sysobjects.id = sysindexes.id 
INNER JOIN syscolumns
ON sysindexes.id = syscolumns.id
WHERE syscolumns.colid <= sysindexes.keycnt
AND sysindexes.indid = 1
--AND sysobjects.name = 'tablename'
ORDER BY sysobjects.name ,sysindexes.name  

SQL 2000 : Useful TSQL

What is the name of the primary key?

DECLARE @VALUE NVARCHAR(255)
SET @VALUE= (SELECT NAME
               FROM SYSOBJECTS
              WHERE XTYPE = 'PK'
                AND PARENT_OBJ = (OBJECT_ID('MY_TABLE'))
            )
SELECT @VALUE

Check for the existence of column? (With drop statement too!)

IF EXISTS ( SELECT a.name, b.name
                  FROM sysobjects a
                  INNER JOIN syscolumns b
                  ON a.id=b.id
                  WHERE a.xtype='u'
                  AND a.name = 'MY_TABLE'
                  AND b.name= 'MY_COLUMN'
BEGIN
      ALTER TABLE MY_TABLE DROP COLUMN MY_COLUMN
END

Free Tool : JKFragmenter

JKFragmenter is a free tool that allows you to deliberately break up your files, scattering them all over the drive! I'm glad I found it, as I can emulate the situation on some servers I have come across recently.

Basically the data files on the forementioned servers were not presized so along with some bad autogrowth settings. To illustrate the performance impact this had on sql, I set up a database and deliberately fragmented the .MDF and .LDF files on the filesystem (I set the DB offline whilst I did so).

It is a command line tool, taking the form -
jkfragmenter -p 100 -s 500 filename
-p = fragment count to split file into. Default is 10. Use 0 or 1 to defragment.
-s = size in kilobytes, Default is 1000Kb (approx 1Mb)

Output and screenshot of usage -

c:\tools>jkfragmenter -p 10 "C:\Data\sql_tools.mdf"
Fragmenter v1.2, 2008 J.C. Kessels

Commandline argument '-p' accepted, parts = 10

Processing: C:\Data\sql_tools.mdf
File already exists.
Fragment list (before):
Extent 1: Lcn=17248693, Vcn=0, NextVcn=250
250 clusters, 1 fragments.
Fragmenting:
Largest gap: 28827794 - 29342048 (514254 clusters)
Moving 25 clusters from offset=0 to LCN=29084908
Largest gap: 29084933 - 29342048 (257115 clusters)
Moving 25 clusters from offset=25 to LCN=29213478
Largest gap: 28827794 - 29084908 (257114 clusters)
Moving 25 clusters from offset=50 to LCN=28956338
Largest gap: 12795714 - 12978718 (183004 clusters)
Moving 25 clusters from offset=75 to LCN=12887203
Largest gap: 13031538 - 13212410 (180872 clusters)
Moving 25 clusters from offset=100 to LCN=13121961
Largest gap: 28956363 - 29084908 (128545 clusters)
Moving 25 clusters from offset=125 to LCN=29020623
Largest gap: 29084933 - 29213478 (128545 clusters)
Moving 25 clusters from offset=150 to LCN=29149193
Largest gap: 29213503 - 29342048 (128545 clusters)
Moving 25 clusters from offset=175 to LCN=29277763
Largest gap: 28827794 - 28956338 (128544 clusters)
Moving 25 clusters from offset=200 to LCN=28892053
Largest gap: 3669806 - 3794003 (124197 clusters)
Moving 25 clusters from offset=225 to LCN=3731892
Fragment list (after):
Extent 1: Lcn=29084908, Vcn=0, NextVcn=25
Extent 2: Lcn=29213478, Vcn=25, NextVcn=50
Extent 3: Lcn=28956338, Vcn=50, NextVcn=75
Extent 4: Lcn=12887203, Vcn=75, NextVcn=100
Extent 5: Lcn=13121961, Vcn=100, NextVcn=125
Extent 6: Lcn=29020623, Vcn=125, NextVcn=150
Extent 7: Lcn=29149193, Vcn=150, NextVcn=175
Extent 8: Lcn=29277763, Vcn=175, NextVcn=200
Extent 9: Lcn=28892053, Vcn=200, NextVcn=225
Extent 10: Lcn=3731892, Vcn=225, NextVcn=250
250 clusters, 10 fragments.

Finished, 1 files processed.

Perfmon : Monitoring File Fragmentation

Fragmentation seems like such a simple problem. Take systems offline and DEFRAGMENT the drive with any one of a number of free tools. In the SQL server world, hopefully you're presizing data/log files, eliminating the need for autogrowth and fragmentation in the first place.

Defraggler or Conrig.exe can both show the fragmentation status of files, without performing the defragementation hence you can see if a drive is fragmented.

You can tell if fragmentation is affecting your disk throughput by monitoring the following counters -
LogicalDisk\Split IO/sec or PhysicalDisk\Split IO/sec

Technet : Examining and Tuning Disk Performance

Out of Memory Oddity

This function (well, a similar non obfuscated one) caused me issues today.

CREATE FUNCTION [dbo].[history] (@id INT) 
RETURNS @history TABLE  (row_num INT IDENTITY(0,1)
            ,history_id AS id + '|' + CONVERT(VarChar(20), row_num)
          ,history_title  VARCHAR(50))
AS 
BEGIN 

INSERT INTO @history(history_id,history_title)

SELECT a.value1 AS history_id
      ,a.title  AS history_title
FROM  mytable a
WHERE a.id = @id

RETURN

END

GO

It would throuw 'Out of memory' errors on SQL 2008. How could one function achieve this on a box with 8GB of memory? After a great deal of investigation, the below solution turned out to be the fix. The alculated column in table definition of the orignal was the issue so my reolution was to use 2 table variables. The fix had to work for clients running SQL versions 2000 - 2008.

CREATE FUNCTION [dbo].[history] (@id INT) 
RETURNS @history TABLE  (row_num  INT 
            ,history_id VARCHAR(50)
          ,history_title  VARCHAR(50))
AS 

DECLARE @histtemp TABLE (row_num  INT IDENTITY(0,1)
            ,history_id AS id + '|' + CONVERT(VarChar(20), row_num)
          ,history_title  VARCHAR(50))


INSERT INTO @histtemp (history_id,history_title)
SELECT a.value1 AS history_id
      ,a.title  AS history_title
FROM   mytable a
WHERE a.id = @id

INSERT INTO @history(row_num, History_id, history_id) 
SELECT row_num, History_id, history_id
FROM @histtemp 

RETURN

END

GO

SQL 2008 + : Prevent saving changes that require table re-creation

Adding a new column for a clustered primary key (don’t ask..) I came up against this on my development box.

This is sensible. After all, adding a new clustered primary key is going to need the whole table to change, a potentially lengthy operation.

Generating the script for the operation proves this as it -

1. renames the existing table
2. creates the new one with the additional column
3. copies the data back

(and deals with removing and recreating indexes and constraints before and after respectively)

The option to control Management Studio’s behaviour is here -

Tools -> Options > Designer > ‘Prevent saving changes that require table re-creation’

ref; Brian Knight : Sql 2008 Designer Behaviour Change

ALTER USER ... WITH LOGIN to fix orphaned users

sp_change_users_login is deprecated.

From sql 2005 SP2, ALTER USER .... WITH LOGIN comes into play to achieve the same, i.e. remapping orphaned users to logins

ALTER USER Username WITH LOGIN = LoginName

I like to keep usernames and logins name the same where possible, hence -

ALTER USER Doermouse WITH LOGIN = Doermouse

MSDN : ALTER USER


Here is what works in SQL 2000 / 2005 -

Lists usernames that are not mapped to logins
exec sp_change_users_login 'report'

Map db username to server login if names match -

exec sp_change_users_login 'update_one', 'username'

Maps db username to server login if names match, If no login exists, it creates one with the password given.

exec sp_change_users_login 'auto_fix', 'username' , 'password'

Links -

USP_FixUsers - Works for all users in a db
USP_FixOrphans - Works for all users in all dbs on a server
Mapping SQL Server Logins to Database Users
Fix Orphaned Users SQL 2005
MSDN : Sp_change_users_login
MSDN : Deprecated Database Engine Features in SQL Server 2008 R2

Virtualbox : Increasing the size of a Virtualbox drive

How to increase the size of a virtualbox drive (.vdi file). -

C:\VirtualBox> \"program files"\oracle\virtualbox\vboxmanage modifyhd xppro_workbench.vdi --resize 20000 
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%

C:VirtualBox>

Then have to go into VM OS and create a new partition in the space or expand a partition if the OS supports this (not the drive hosting the OS itself).

VirtualBox : Network Types

You have 4 types of Network in VirtualBox. But how to chose?
Here are the basics...

NAT
NAT (Netork Address Translation) means the virtual machines will have private IP addresses on the virtualBox virtual network
VMs are not available from other pcs networking but can initiate connections to outside resources (servers, internet etc)

Host :

Guest :

C:\>ipconfig Windows IP Configuration Wireless LAN adapter Wireless Network Connection: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::8c63:1859:487c:f577%10 IPv4 Address. . . . . . . . . . . : 192.168.0.9 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 192.168.0.1 Ethernet adapter VirtualBox Host-Only Network: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::7804:1a06:c47b:f37d%17 IPv4 Address. . . . . . . . . . . : 192.168.56.1 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . :

C:\>ipconfig Windows 2000 IP Configuration Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : IP Address. . . . . . . . . . . . : 10.0.2.15 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 10.0.2.2 C:\>

Bridged Adapter

Bridged Adapter connects virtual machines via the host network card.
They are on the same subnet and get an IP from the same router/dhcp server that the host does.
VMs are effectively on the physical network.

Host :

Guest :

C:\>ipconfig Windows IP Configuration Wireless LAN adapter Wireless Network Connection: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::8c63:1859:487c:f577%10 IPv4 Address. . . . . . . . . . . : 192.168.0.9 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 192.168.0.1 Ethernet adapter VirtualBox Host-Only Network: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::7804:1a06:c47b:f37d%17 IPv4 Address. . . . . . . . . . . : 192.168.56.1 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . :

C:\>ipconfig Windows 2000 IP Configuration Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : IP Address. . . . . . . . . . . . : 192.168.0.3 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 192.168.0.1

Host-only Adapter

VirtualBox acts as the router (via adapter vboxnet0) and allocates IP addresses to VMs
By default this is a private network although you can assign a gateway (to grant internet access) making a a Bridged Adapter anyway.

Host :

Guest :

C:\>ipconfig Windows IP Configuration Wireless LAN adapter Wireless Network Connection: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::8c63:1859:487c:f577%10 IPv4 Address. . . . . . . . . . . : 192.168.0.9 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 192.168.0.1 Ethernet adapter VirtualBox Host-Only Network: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::7804:1a06:c47b:f37d%17 IPv4 Address. . . . . . . . . . . : 192.168.56.1 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . :

C:\>ipconfig Windows 2000 IP Configuration Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : IP Address. . . . . . . . . . . . : 192.168.56.101 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . :

Internal Network

VMs can see each other, but not the host or the outside world :(

Host :

Guest :

C:\>ipconfig Windows IP Configuration Wireless LAN adapter Wireless Network Connection: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::8c63:1859:487c:f577%10 IPv4 Address. . . . . . . . . . . : 192.168.0.9 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . : 192.168.0.1 Ethernet adapter VirtualBox Host-Only Network: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80::7804:1a06:c47b:f37d%17 IPv4 Address. . . . . . . . . . . : 192.168.56.1 Subnet Mask . . . . . . . . . . . : 255.255.255.0 Default Gateway . . . . . . . . . :

C:\>ipconfig Windows 2000 IP Configuration Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : Autoconfiguration IP Address. . . : 169.254.21.26 Subnet Mask . . . . . . . . . . . : 255.255.0.0 Default Gateway . . . . . . . . . :