Virtualbox : Copying & Converting Drive Images

VBoxManage is a command line tool that allows you to perform administrative functions on VirtualBox installations.

To create a copy of a drive, the command is -

 VBoxManage clonehd sourcefile targetfile . e.g Vboxmanage winxp.vdi winxpcopy.vdi

To change the format of the target, add the format parameter -

VBoxManage clonehd sourcefile targetfile --format VDI e.g.

VBoxManage clonehd c:\!RD\Virtualbox\w2003.vmdk w2003.vdi --format VDI

0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%

Clone hard disk created in format 'VDI'. UUID: 6a1ded22-edb4-4cef-b74a-871db2fcafd6

If you want to increase the logical size of an expanding volume, the following post shows how to use VBoxManage to create a larger drive.

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

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/

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

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')

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)

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 :)

TSQL : SQL Server Version detection

As part of rollout scripts I am automating the detection of of the SQL Server version.
The current use for this is to decide whether indexes should carry included columns (a 2005+ feature), but I can think of many more.

IF (CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1) AS INTEGER)) >=9
BEGIN
PRINT 'SQL 2005 or greater detected'
END

Other properties such as Edition could be used to determine whether edition specific features e.g Page Compression, Resource Governor (SQL 2008 Enterprise)

SELECT
 CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1) AS INTEGER) AS MajorVersion
,SERVERPROPERTY('ProductVersion') AS ProductVersion
,CASE LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1)
 WHEN 8 THEN 'SQL 2000'
 WHEN 9 THEN 'SQL 2005'
 WHEN 10 THEN 'SQL 2008'
END AS ProductVersion
,SERVERPROPERTY('Edition') AS Edition
,SERVERPROPERTY('ProductLevel') AS ProductLevel

Add Administrator from the command line

(This could be fatal is xp_cmdshell has been left open...)

C:\Users\->net user admintest p4ssw0rd /add
The command completed successfully.

C:\Users\->net localgroup administrators admintest /add
The command completed successfully.

Tsql : ROW_NUMBER, RANK, DENSE_RANK and PARTITION BY

ROW_NUMBER, RANK, DENSE_RANK and PARTITION BY

A script to demo all of the above, as a reminder of Windowing functions...

SELECT
  name
 ,type_desc
 ,COUNT(*) OVER(PARTITION BY NULL) AS CountAllRecords
 ,ROW_NUMBER() OVER (ORDER BY name) AS RowNumberByName
 ,RANK()  OVER (ORDER BY type_desc) AS RankbyType
 ,DENSE_RANK()  OVER (ORDER BY type_desc) AS DenseRankbyType
 ,RANK() OVER (ORDER BY LEFT(Name,1)) AS RankByFirstCharacterofName
 ,DENSE_RANK() OVER (ORDER BY LEFT(Name,1)) AS DenseRankByFirstCharacterofName
 ,ROW_NUMBER() OVER (PARTITION BY LEFT(Name,1) ORDER BY LEFT(Name,1)) AS RowNumberPartitionedbyLeft1
FROM sys.objects
ORDER BY name

Database Maintenance 2

Database Management

The items I listed back in the Database Maintenance Checklist are pretty standard. All necessary to keep a server healthy. How can we improve our efficiency and take the DBA role to the next level however?

Automate test restores

Restoring data can be scheduled by script and SQL Agent Jobs easily. Providing capacity is not an issue restores can be scheduled to a test environment out of core hours.

Configure Alerts

SQL can send emails on job failures and a wide range of configurable events warning of systems events. In addition to this I personally like to configure emails for Blocking Queries, Long Running queries and Server Restarts/Failovers.

Monitor Trends

Monitor the daily growth of backups, databases, data files or even tables. This can be done into a tools database and stored on server with a relatively small overhead.
Leaving this running will provide you with accurate figures for capacity planning.

Indexes 

Periodically review database indexes. Identifying and removing duplicated and unused indexes will save space and reduce the overhead of maintaining them. Similarly running scripts or the Database Tuning Advisor may help identify new indexes that are needed.

Index maintenance jobs may hide an inappropriate index e.g one that quickly fragments. Recording the index fragmentation level before running index maintenance can help identify this. Another use for my tools database, a collection of scripts I have on each machine.

osql & sqlcmd

Osql has long been deprecated, but it still exists on those pesky sql 2000 servers...

osql	sqlcmd
osql usage : osql –e –q”SELECT column FROM table” osql -Uusername -Ppassword -Sservername -iC:\script.sql -oc:\output.txt	sqlcmd usage : sqlcmd –e –q”SELECT column FROM table” sqlcmd -Uusername -Ppassword -Sservername -iC:\script.sql -oc:\output.txt
parameters -e Use Trusted Connection (windows Authentication) -u User name -p Password -s Server name -i Script name -q Query -o Output file -n remove numbering	parameters as osql parameters , with new additions including - -b batch abort -f Codepage -u Unicode output -v Pass Variables
To Get values out from osql , use the EXIT command like this - C:\>osql -e -n -q "EXIT (SELECT 47)" 47 Or in a batch file like this... osql -e -n -q "EXIT (SELECT COUNT(*) FROM sysobjects)" SET SqlObjectCount=%ErrorLevel% ECHO %SqlObjectCount%	To return values out from sqlcmd , use the EXIT command like this - C:\>sqlcmd -Usa -Ppassword -q"EXIT(SELECT 101)" 101 Or in a batch file like this... sqlcmd -Usa -Ppassword -q "EXIT (SELECT COUNT(*) FROM sysobjects)" SET SqlObjectCount=%ErrorLevel% ECHO %SqlObjectCount%
To Pass parameters in to OSQL, build a sql script file on the fly, and load it ... SET currentobject=table1 ECHO EXIT(SET NOCOUNT ON;SELECT COUNT(*) from sysobjects WHERE [name] = '%currentobject%') >%currentobject%_exists.sql OSQL -Usa -P%password% -S %server% -d %db% -f 65001 -i %currentobject%_exists.sql	sqlcmd supports variables via the –v switch pass.sql SET NOCOUNT ON; EXIT(SELECT 'filename passed is $(file)') pass.bat @echo off for %%f in (*.bat) do ( sqlcmd -v file="%%f" -i "pass.sql" ) ) Results - B:\test2>pass ---------------------------- filename passed is check.bat -------------------------------- filename passed is check_old.bat --------------------------- filename passed is pass.bat ------------------------------ filename passed is rollout.bat

MSDN : osql
MSDN : sqlcmd
Using the SQLCMD command line utility for SQL SERVER
Mohamad Shehadeh's Blog : SQLCMD VS OSQL

(the deprecated) TEXT datatype

Argh! Have come up against the deprecated text datatype in a database i’m reporting on.
It’s a migrated SQL 2000 product in 8.0 Compatibility mode!

I cannot use equals (=) in a WHERE clause against the text column

So when I want to write

SELECT * FROM ProductUpdates WHERE Build = ‘9.71’

I get ...

Msg 402, Level 16, State 1, Line 1
The data types text and varchar are incompatible in the equal to operator.

I have to use PATINDEX to get around it, like this –

SELECT * FROM ProductUpdates  WHERE PATINDEX('9.71',Build) > 0

Or CAST the column like this

SELECT * FROM ProductUpdates  WHERE CAST(Build AS VARCHAR(MAX)) = ‘9.71’

Although this second approach would ruin the sargability of the query, i.e. the ability to use an index seek on the column.

Link : Using equal operator in transact-SQL for ntext datatype column

Codeplex : ExportSqlScript

I'm currently looking a replacing a product scripting system. It is command line driven (not a bad thing), but suffers from being overly complex. To apply changes, they need to be scripted to 3 places which is far from ideal. I need to create database build scripts that will support multiple versions, service packs and patches for a core product.

Redgate's SQL Compare is the obvious answer to see the initial state of play, but I cannot roll that out to the deployment team and tell them to get on with it. As much as I'd like to.

For this reason, some googling had led me to the perfect starting block, a codeplex project called 'ExportSqlScript'.

http://exportsqlscript.codeplex.com/

It is command line driven, and runs like this

ExportSQLScript.exe localhost adventureworks /ot:Tree /of:CreationOrder.txt /od:"b:\test2\ExportSqlScript" /ssq

This creates a folder for each object type (screenshot below) as well as CreationOrder.txt

CreationOrder.txt can be used to recreate the database as it takes into account object dependencies i.e. it creates views and constraints AFTER dependent tables and a functions / stored procedure after related tables / views. In theory it should match Jamie Thomon's Dependency Order script, that I always keep handy.

You can run scripts in order like this -
for /F "" %i in (CreationOrder.txt) do osql -Uuser -Ppassword -S localhost -d databasename -f 65001 -i %i

(Change %i for %%i inside a batch file)