How to remove your Virtual machine’s BLOAT

Posted on March 14, 2012 by

Well I guess its been a long time since I have offered you a useful nugget of information or help, so I have decided to haul myself to the keyboard and provide a useful tip that I have been saving for a rainy day. Regular readers to this blog will certainly be no stranger to my positively strong feelings towards Oracle’s VirtualBox and I have been a user of this product for more years than I can remember. The tip I am about to describe will use VirtualBox in the examples but it should be transferable to any virtualisation product that has the ability to shrink back virtual disks.

Of course in the world of most virtualisation products there is a concept of dynamically expanding virtual disks and fixed-size virtual disks, and VirtualBox is no different having had this ability for many years. There was always the question of whether to choose the dynamic disk to save real disk space or go for the fixed-size for performance gains. In actual fact this argument is a little bit of a red herring these days (more perhaps on this another time) and the reality is that unless you have a virtual disk which is frequently having to allocate space and expand (in the same way database or page files can) you are always better going for the dynamically expanding virtual disks.

Using a dynamic disk provides the ability to have a virtual disk that is only the size required for the files (and some overhead) that it contains. But what happens should your virtual machine disk allocate lots of space to allow for that 10GB SQL Server Database that you were playing around with (and then dropped)? The biggest problem with a dynamic disk is that it slowly creeps up in physical size over time in very much the same way that transaction logs can do in SQL Server if not truncated regularly by taking frequent log backups.

In VirtualBox and competing products, there is the ability to shrink back these dynamic disks to reclaim space back to your host machine. For those of you who have tried doing so will already know that it is usually a hit and miss affair and leaves you scratching your head wondering why your virtual disk shrink has only given back 1GB of a possible 10GB of free space. One of the problems with this type of operation is that the shrink operation will only release space that it knows is free and this is not always a clear cut thing if the disk block has at one time had writes. The Operating System knows they are reusable but the virtualisation shrink may not.

Lets take a quick look at the space consumption of my virtual machine’s virtual drive sizes…

wonko is a bit big...

So the largest virtual drive belongs to a virtual machine I call wonko and it stands out to me straight away as being an oddity because it (like two other virtual machines) is running on Windows Server Core but is around twice the size of the others. After I take a look inside Wonko, it becomes very apparent to me why it is so large. When I reinstalled Windows Server Core on this VM, I forgot to reformat the hard drive. The installer therefore decided to rename the old Windows directory for safe keeping…. Oh did I mention that I did this operation twice since my first re-install was wrong!

After completely removing the two offending archived Windows directories I take another look at the virtual disk space consumption…

My goodness what a lot of free space!

Ok so this is now where the magic happens…

There is a rather fabulous tool from those wonderful Sysinternals chaps called sdelete which will help you mark all your free space as clean. This makes it easier for the virtualisation shrink operation to see that the space can be reclaimed back and the virtual disk can be shrunk.

Running a sdelete /? from the command line gives us the following list of options :-

SDelete – Secure Delete v1.6
Copyright (C) 1999-2010 Mark Russinovich
Sysinternals – www.sysinternals.com

usage: sdelete [-p passes] [-s] [-q] <file or directory> …
       sdelete [-p passes] [-z|-c] [drive letter] …
   -a         Remove Read-Only attribute
   -c         Clean free space
   -p passes  Specifies number of overwrite passes (default is 1)
   -q         Don’t print errors (Quiet)
   -s or -r   Recurse subdirectories
   -z         Zero free space (good for virtual disk optimization)

And the option we want is pretty obvious isn’t it? Yes let’s choose the z parameter…

Right, now our virtual disk’s free space is now zeroed out, it is time for us to run our shrink operation. In VirtualBox this can be performed by running VBoxManage modifyhdd <virtualdisk> –compact and doing so takes several minutes to run through (on my SSD).

Let us take one final look at my host drive and in particular see the size of the wonko virtual disk…

Wow!

I think the results are quite evident and staggering. The wonko disk is now 18GB or so lighter. Time to load up my SSD with lots more VMs!!!

Posted in filesystem, maintenance, setup, sql, SQLServerPedia Syndication, storage, virtualization, windows | Leave a comment

I can speak English, I learn it from a book

Posted on February 16, 2012 by

Many many years ago when I was still in short trousers I started to take an interest in Movies. My parents had completely different sleeping patterns and my Father was an irritatingly early riser -a fact that would irritate me right into my adolescence and beyond. My mother was the opposite and (like me) very much a night owl. This resulted in there being a period of around 3 to 4 hours before my Mother went to bed, where the Television would be switched to whatever channel she wanted to watch -in other words not Sport.

I come from Barcelona!

As I approached the age that staying up beyond 9 pm. was acceptable I tended to sit with her and watch whatever she was watching. Her tastes were very varied and cosmopolitan and we would tend to watch different film genres over seasons. One of the first genres that I remember watching with her were the 1930s and 1940s horror Movies where together we absorbed every Frankenstein (starring Boris Karloff), every Dracula (starring Bela Lugosi) and every Wolfman (starring Lon Chaney Jr.) not to mention the sequels starring Abbott and Costello!

Over a very long time we watched an enormous and varied collection of films and since her tastes where not just limited to English language films; we watched films from France, Belgium, Germany, Japan and any other country we could get on our Gogglebox. Over time reading the subtitles whilst watching each film became as natural as the spoken word to us.

To this very day I meet people who refuse to watch anything that is not spoken in their own language (for whatever reason) and fail to realise just how many amazing films they are missing out on. A long time before Japanese Horror film remakes by Hollywood was the “norm”, I sat with a friend who held the believe that subtitled films were impossible to watch but convinced them to join me for the evening to watch a certain Japanese Horror Classic. The evening was a rather scarily enjoyable experience and the film in question was called Ringu -you will by now know its remake as “The Ring”. Let me tell you that even if you have seen the somewhat half-hearted Hollywood effort, the original film in its natural form is quite something to behold. Possibly one of the scariest films of all time and guess what? I watched it years and years before most Westerners (and perhaps even now the majority will not have watched the subtleties of the original).

Time to start running

I am sure by now you have been asking yourself “What is the point to all this?”  and “How does it relate to SQL Server?”. Well let me tell you…

I have just been speaking privately with Rob Farley (blog|twitter) and have found out about the plans for the next iteration of 24 Hours of PASS. What he has told me is that each session will have Live Closed Captioning in 15 different languages!!! Can you believe that? This means that all the sessions will be accessible to people who speak other languages other than English and makes 24 Hour of PASS a truly international event for the very first time. As Rob put it “if someone in China asks you a question in Chinese… you’ll be able to read it in English and they’ll read your answer in Chinese”.

If you are one of those non-English language SQL professionals that has ’till now not been able to view the English language 24HOP (you are obviously reading this blog using Google Translate :) ) and want to have access to valuable, technical, up to date material -then come and join the party. And remember my story, please don’t forget to introduce a friend!

To register for all 24 sessions go click here without delay, and don’t forget to join me for my session “Moves like Jagger – Upgrading to SQL Server 2012“. You should also check out Rob’s post about these excellent changes by clicking here.

Posted in 24HOP, humour, publicspeaking, sqlpass, SQLServerPedia Syndication | 2 Comments

What are the impersonation rights for my database users?

Posted on January 30, 2012 by

From time to time I get thrown the odd request to provide various bits of information from the SQL Server environment and most recently I was asked about a group of databases that required migrating to another instance. For some reason there had been a little bit of a panic about the impersonation rights in each of the databases and their configuration. The request was *ahem* kindly passed to me. The number of database users was huge in each database and performing a manual lookup through the SSMS GUI got rather tiresome very quickly (after about the second user).

My reply was that as long as we ensured each database was migrated with associated logins and mapped correctly (to prevent orphaned users) we didn’t really need to worry about the impersonation, since it would be contained within the databases and unaffected by the move. Unfortunately, there was no getting away from it, they still wanted a report.

I first decided to perform a web search for the term “list impersonate rights” which led my to quite a useful post by Kendal Van Dyke (blog|twitter) called “Hey Mr. DBA, What Permissions Do I Have On This Database?” but I was already familiar with the examples listed, having previously come across the fn_my_permissions and fn_builtin_permissions functions in Books Online. They did what I wanted, but really the wrong way around. To use them I would have to write a cursor based solution which would be a little long winded.

Thankfully most SQL Server problems are relatively easy to solve by applying a very small amount of grey matter to them. I decided to turn my attention to the system catalog views and started off with sys.database_principals. Using the Microsoft SQL Server 2008 R2 System Views poster which is helpfully stuck to my office wall I focused in on this view and my attention was drawn to the sys.database_permissions catalog directly underneath. Surely this would be the most likely candidate for the IMPERSONATE grants?

Wall posters can come in handy!

After a very quick query of several of the databases provided to me I noticed that in one of them under the column permission_name, several rows contained the entry “IMPERSONATE” with a state_desc of “GRANT”. Bingo! That was just what I wanted. Time to write the query….

So we can test the query, let us first create a new database, several logins, several users from the logins and grant impersonation rights to some of the users to some of the others:-

CREATE DATABASE Impersonation;
GO
USE Impersonation;
GO

CREATE LOGIN login1 WITH PASSWORD = 'Password1';
CREATE LOGIN login2 WITH PASSWORD = 'Password2';
CREATE LOGIN login3 WITH PASSWORD = 'Password3';
CREATE LOGIN login4 WITH PASSWORD = 'Password4';

CREATE USER user1 FROM LOGIN login1;
CREATE USER user2 FROM LOGIN login2;
CREATE USER user3 FROM LOGIN login3;
CREATE USER user4 FROM LOGIN login4;

GRANT IMPERSONATE ON USER::user4 TO user1;
GRANT IMPERSONATE ON USER::user4 TO user2;
GRANT IMPERSONATE ON USER::user1 TO user3;
GRANT IMPERSONATE ON USER::user1 TO user2;

Next we should connect under login1 and change context to our Impersonation database; then attempt to impersonate user4 to test that everything works:-

user4 impersonation works

USE Impersonation
GO
EXECUTE AS USER = 'user4';
SELECT USER_NAME();

OK so we know all this works and we know how the rights are assigned. Lets write our query:-

USE Impersonation
GO
SELECT DB_NAME() AS 'database'
	,pe.permission_name
	,pe.state_desc
	,pr.name AS 'grantee'
	,pr2.name AS 'grantor'
FROM sys.database_permissions pe
	JOIN sys.database_principals pr
		ON pe.grantee_principal_id = pr.principal_Id
	JOIN sys.database_principals pr2
		ON pe.grantor_principal_id = pr2.principal_Id
WHERE pe.type = 'IM'

This query produces a rather simple result set as you see in the next picture:-


And there we have it -all impersonation rights are listed for the database in question. I was able to go onto query the five databases in question and provide a very simple report which would have taken me a very long time to perform using the SSMS GUI.

Whilst nothing I have demonstrated in this article is particularly difficult, it does highlight the multitude of different ways to perform operations in SQL Server. Just make sure you choose the easiest route!

Posted in database, dmv, reporting, security, SQLServerPedia Syndication, tsql | Leave a comment