Friday 29 July 2011

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.

Tuesday 26 July 2011

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

Tuesday 19 July 2011

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


Friday 15 July 2011

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 

Friday 8 July 2011

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

Thursday 7 July 2011

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

Saturday 2 July 2011

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'