How to reduce index size and free up space in a SQL database.

First, run this script to figure out the worst offenders:

create table #t
  name nvarchar(128),
  rows varchar(50),
  reserved varchar(50),
  data varchar(50),
  index_size varchar(50),
  unused varchar(50)

declare @id nvarchar(128)
declare c cursor for
select name from sysobjects where xtype='U'

open c
fetch c into @id

while @@fetch_status = 0 begin

  insert into #t
  exec sp_spaceused @id

  fetch c into @id

close c
deallocate c

select * from #t
order by convert(int, substring(data, 1, len(data)-3)) desc

drop table #t

Continue reading “How to reduce index size and free up space in a SQL database.”


Permanently erasing sensitive data or recovering formatted information.

I have recently come upon this post on  and decided to give it a try. What could be easier than writing a .NET application which will write 0’es on the free space of a hard drive, and then I would try to recover any bits of files that were previously deleted from the space in question. I know, the challenge is different but this is what I want to convert it to.

I don’t have UNIX nor the “dd” library but I do have a mind set to help the police recover deleted/formatted information from hard drives or maybe help a good company forever erase sensitive data. Wherever this quest might lead me, it will definitely help someone.

To start, let’s say I have a sensitive file I need to erase from a hard drive.

I have a Windows 7 system (pro), all the coding tools I need and a 5GB file (a DVD Movie I copied on my hard drive and then deleted it). I will try to recover it after I try to erase every trace of it.

This started off easy but then I hit my first road block. All the code samples I have found were written in C, C++ using low-level handles. I quickly found a C# implementation of a function which would work with the Defragmentation API and show a quick method to access the VolumeMap (to find the free space). After converting it to VB.NET (my preferred language) and fixing the few casting errors that appeared I found myself having a bit of a struggle to obtain the hard drive descriptior.

Using the file path as ?c: as was recommended by MSDN, brought on WIN32 error number 87. Now, this does not say much so I tried to implement a FormatMessage function using the Windows API and failed miserably. Upon digging a bit forward, I found out that to get the message from the windows error I would have to change this line

Throw New Exception(Marshal.GetLastWin32Error().ToString())


Throw New Win32Exception()

This will convert the number to a string that makes sense. In my case, the error was “Invalid Argument”. So I changed the path format to “C:” and this time I got the dreaded “Access Denied” error (code 5).

At this point I started pulling my hair. How could the Windows security settings be the ones not allowing me to perform dangerous functions with my hard drive. 🙂 I know.

After reading on this issue a little bit online, I found this nice post explaining everything there is to know about elevation of rights in .NET so I can execute my program as an administrator.

After adding an extra piece of code on my form to elevate the rights on the program being executed, I had my handle but when it came to opening the handle and obtaining the hard drive image,

Dim fResult As Boolean = DeviceIoControl(hDevice, _
CUInt(Marshal.SizeOf(i64)), pDest, q, _
             size, IntPtr.Zero)

The error I got was “Handle is Invalid!”. The handle number was 0. I had a read again on the FSCTL_GET_VOLUME_BITMAP function:


 This is generally the first FSCTL that is used by a
 defragmenter, because it is required to get a map of the
 clusters that are free and in use on a volume. Each bit
 in the bitmap returned in the output buffer represents
 one custer, where a 1 signifies an allocated cluster and
 a 0 signifies a free cluster.


  The file handle passed to this call must represent a
  volume opened with the GENERIC_READ flag. A volume can be
  opened with a name of the form, ".X:", where 'X' is
  the volume's drive letter.

I shall stop here and continue later. There must be something simple to do about this.