Friday, 29 April 2011

TSQL : Identity Columns that do little else!

Tables with Identity Columns, but NO clustered index
SELECT   SCHEMA_NAME(schema_id) AS SchemaName 
  ,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'TableHasIdentity') = 1
  AND OBJECTPROPERTY(object_id,'TableHasClustIndex') = 0
ORDER BY 1, 2

Tables with Identity Columns, but NO primary key
SELECT   SCHEMA_NAME(schema_id) AS SchemaName 
  ,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'TableHasIdentity') = 1
  AND OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
ORDER BY 1, 2

Thursday, 28 April 2011

Virtualbox : Increase size of Virtual Drive (.VDI file)

To resize an existing VDI image, we turn to command line tool VBoxManage once again.
Rather than resizing, we follow a 3 step process -

1) Use Virtualbox to create a new blank drive of the desired size.
2) Copy your existing drive onto the new drive. For me, the command looked like this -

C:\!RD\VirtualBox>"C:\Program Files\Oracle\VirtualBox\vboxmanage.exe" clonehd --
existing xppro.vdi newharddisk1.vdi
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Clone hard disk created in format 'VDI'. UUID: b5e98bc3-80c7-476f-976b-725348c5c
e84

C:\!RD\VirtualBox>

3) Rename the files so that VirtualBox mounts your new bigger drive.


Update : June 2011
Apparently you can now resize directly without creating a copy -
VBoxManage modifyhd YOUR_HARD_DISK.vdi –resize SIZE_IN_MB 


VirtualBox : Compacting / Shrinking a Virtualbox drive file

Using Hyper-V over the past couple of years, I blogged on size optimization of the VHD files.
(They get bloated and consume more space than necessary)

Now I'm using VirtualBox for a personal SQL Server test environment, here is how to do the same for VirtualBox drives.

Compacting a VirtualBox Drive
  1. Delete rubbish from your virtual machine (clear temporary internet files, windows installer files, unwanted data, empty recycle bin etc)

  2. Defragment the drive (Use the OS’s defragmenter or third party FREE tool Defraggler)

  3. Zero out the unused data space in the guest OS

    You have 2 options here -
    i. Use Precompact.exe (details in Hyper-V post) or
    ii. Use Microsoft’s SDelete  e.g. SDELETE –c C:  (a lot simpler)

  4. Shrink the hard drive file from the host OS using VboxManage.exe
    vboxmanage modifyhd filename.vdi compact

    I found that Vboxmanage only runs from it’s install directory (dependency on DLL files) hence run it from there, e.g.

    C:\> cd C:\Program Files\Oracle\VirtualBox

    C:\Program Files\Oracle\VirtualBox> VBoxManage modifyhd c:\!RD\Virtualbox\w2003.vdi compact
This screenshot shows the space i was able to reclaim from one of my VMs...

Resources
http://www.dedoimedo.com/computers/virtualbox-shrink-expand-disks.html
http://www.plinky.it/blog/2009/05/18/virtualbox-compact-the-vdi-hard-disk-file/

Wednesday, 27 April 2011

HEAPS of fun : Tables WITHOUT clustered indexes

Tsql to list Tables WITHOUT clustered indexes (aka HEAPS)

SQL 2008/2005 Version
SELECT   SCHEMA_NAME(schema_id) AS SchemaName 
  ,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'TableHasClustIndex') = 0
ORDER BY 1, 2
or
select name from sys.tables  
where object_id not in (select object_id from sys.indexes where type_desc = 'CLUSTERED')

SQL 2000 Version
SELECT DISTINCT 
  o.name  AS TableName
FROM sysindexes i
INNER JOIN sysobjects o
ON i.id = o.id 
WHERE indid = 0
AND o.type = 'U'
ORDER BY 1
or
SELECT name
FROM sysindexes
WHERE indid = 0
AND OBJECTPROPERTY(id,'IsUserTable') = 1
ORDER BY 1
HEAP tables and the number of records they contain.
SELECT sysobjects.name, sysindexes.rows
FROM sysobjects
INNER JOIN sysindexes
ON sysobjects.id = sysindexes.id
WHERE sysobjects.xtype = 'u'
AND sysindexes.indid < 2
AND sysobjects.name IN 
(    SELECT DISTINCT   
      o.name  AS TableName  
    FROM sysindexes i  
    INNER JOIN sysobjects o  
    ON i.id = o.id   
    WHERE indid = 0  
    AND o.type = 'U'  

)
order by sysindexes.rows desc

Tuesday, 26 April 2011

DBCC UPDATEUSAGE

Had a DBCC Error on a live system today on a scheduled DBCC check.
(NB; Post content is anonymized)

Executing the query "DBCC CHECKDB WITH NO_INFOMSGS
" failed with the following error: "The In-row data RSVD page count for object "ContactDetail", index ID 0, partition ID 130617058000896, alloc unit ID 130617058000896 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'ContactDetail' (object ID 1993058136).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'LiveData'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The problem occurs when there are inconsistencies between the data and what is reported in sysindexes for a clustered index (sysindexes holds rows, used, reserved, and dpages columns).

The error message gave us names for the database and table, the parameters for DBCCUPDATEUSAGE...

DBCC UPDATEUSAGE('LiveData','ContactDetail')

DBCC UPDATEUSAGE: counts updated for table 'ContactDetail' (index 'ContactDetail', partition 1):
RSVD pages (In-row Data): changed from (-501) to (25) pages.
DBCC UPDATEUSAGE: counts updated for table 'ContactDetail' (index 'IDX_ContactDetail', partition 1):
RSVD pages (In-row Data): changed from (16) to (17) pages.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Problem solved!

Reference -
MSDN : DBCC UPDATEUSAGE


Update! You can run it on the whole database too! -  DBCC UPDATEUSAGE('DatabaseName')

Saturday, 23 April 2011

Script : Rename Unnamed Primary Keys (sql 2000 compatible)

Everyone has done it, created a constraint without naming it.
On the surface it doesn't matter. It's the name of an internal object.
The problem surfaces when you use schema comparison tools and find your live and uat environments are incorrectly reported as being different.

To demonstrate -
ALTER TABLE [dbo].[mytable] ADD PRIMARY KEY CLUSTERED 
(
 [id] ASC
)
SQL randomly names the Primary Key PK__mytable__3213E83F0BC6C43E
Deleting the key and adding it again yields a name of PK__mytable__3213E83F0EA330E9

To prevent this behaviour, explicitly name the constraint like this -
ALTER TABLE [dbo].[Table_2] ADD CONSTRAINT [PKCI_id] PRIMARY KEY CLUSTERED 
(
 [id] ASC
)

If you've already had these keys get into production, you can locate and rename them programatically like this -
set @tablename = 'mytable'
set @newconstraintname = 'PKCI_myid'
 
-- find constraint name
select @constraintname = O.name
from sysobjects AS O
left join sysobjects AS T
    on O.parent_obj = T.id
where T.name = @tablename
  and O.xtype = 'PK'
 
SELECT @constraintname
 
-- rename if found
if not @constraintname is null
begin
    set @sql = 'sp_rename ''' + @constraintname + ''' , ''' + @newconstraintname + ''' , ''OBJECT'' ;'
    select @sql
    execute sp_executesql @sql
end

NB : This post is deliberately targeted at SQL 2000 (hence the use of the sysobjects table)

Thursday, 21 April 2011

Contig.exe - File Defragmenter

I'm a fan of a free tool by Piriform called Defraggler. It is a visual degragmentation tool (like the OSs used to include!) Recently I had cause to investigate fragmentation on a very overloaded drive with hundreds of thousands of text files.

Defraggler took 45 minutes to build a picture of the drive, a wait I don't want to repeat. Given my primary concern is the sql data files, I wanted to view those results first. Enter 'contig', now on Technet - formerly part of the sysinternals project.

Running Contig without parameters helpfully 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

Pretty obviously, the above is BAD. Fragmentation of the SQL datafiles on the drive caused by autogrowth. Oh, and the filenames have been changed in the example above for confidentiality. I wouldnt contemplate mixing all those systems in the real world :)