SQL Server database space usage
Comments(0)Ever wanted to know what the biggest table in your database is, and what the other space hogs are? There is the sp_spaceused system stored procedure that either shows headline stats for the database as a whole, or those for a particular table. Annoyingly it doesn’t tell you which table is taking up all the space relative to all the others. If you have SQL Server SP2 installed then it ups SQL Management Studio
So, here’s a T-SQL batch that shows all the space taken up by tables in the current database. The main column to look at is “Total size”, which is a sum of the Data size, Index size and Unused columns.
SET NOCOUNT ON
DBCC UPDATEUSAGE(0)
CREATE TABLE #spaceused
(
Name nvarchar(255),
Rows int,
[Total size] varchar(50),
[Data size] varchar(50),
[Index size] varchar(50),
Unused varchar(50)
)
DECLARE @table_name nvarchar(255)
DECLARE tables_cursor CURSOR LOCAL FORWARD_ONLY FOR
SELECT name FROM sysobjects WHERE type='U'
OPEN tables_cursor
FETCH NEXT FROM tables_cursor INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #spaceused EXEC sp_spaceused @table_name
FETCH NEXT FROM tables_cursor INTO @table_name
END
SELECT * FROM #spaceused
ORDER BY CONVERT(int, LEFT([Total size],
CHARINDEX(' KB', [Total size]))) DESC
DROP TABLE #spaceused
CLOSE tables_cursor
DEALLOCATE tables_cursor
Of note is the very useful INSERT INTO table EXEC proc technique for capturing the output of a stored proc.
The script works on SQL Server 2000 and 2005 (and possibly later).
Exchange 2007 - Custom IP Block List Provider Error Message
We use Microsoft Exchange 2007 at InfoBasis for email, and for my sins I have to manage it. It seems a little enterprisey unwieldy at times for an organisation as small as ours but the Outlook calendaring integration is a must for us and I’ve mostly worked out how it works and how to keep it running happily (thanks to Brian Reid of C7 Solutions who helped us implement it).
We use the built-in anti-spam filtering - specifically the IP Block Listing, aka “DNS blacklist” (or DNSBL) to block emails from known spam sources. I won’t explain how to set it up here as there are plenty of other resources that cover this. Because occasionally we’d get a false positive (eg. from a legitimate AOL or Yahoo account) I was investigating how to write Transport/Edge providers to provide some sort of dynamic spam white list functionality which didn’t seem to be provided out of the box. In so doing I discovered that most of the Exchange 2007 code base seems to have been re-written in .NET (which may explain why some feel it was released unfinished/too early). So I pointed Lutz Roeder’s .NET Reflector (which reverse-engineers .NET DLLs) at a few of the Exchange Server DLLs in Program Files\Microsoft\Exchange Server\Bin and had a snoop around. Thankfully, I eventually stumbled on the Update-Safelist PowerShell cmdlet which does white listing based on Outlook’s Safe Senders - so I didn’t have to write any code after all.
Anyway, on my travels I came across ConnectionFilteringAgent.BlockListDnsCallback (in the Microsoft.Exchange.Transport.Agent.ConnectionFiltering namespace) in Microsoft.Exchange.Transport.Agent.Hygiene.dll. This method formats the message that gets returned to the senders mail server should they be listed by a DNSBL. This was of interest because in Exchange 2003 you could use format codes in the error message (e.g. “The IP address %0 was rejected by black list %2″) to help legitimate email senders work out why their email was blocked (as documented in KB 823866). But, I couldn’t find any documentation on how to use similar format codes in Exchange 2007 - but here in BlockListDnsCallback was this line of code:
message = string.Format((string) provider.RejectionResponse, this.queryData.hostIP, this.queryData.provider.Name, this.queryData.provider.LookupDomain);
Which means you can use the following sort of custom error message in Exchange Management Console > Organization Configuration > Hub Transport > Anti-spam > IP Block List Providers > Edit > Error Messages:
Your IP address {0} has been found on '{1}' (looked up on {2})
Hope that helps!
Changing the font in Google Reader
I love Google Reader for reading blogs, but one thing that bugs me is the rather dull choice of font: sans-serif (Arial if you’re on Windows). If you have Vista or Office 2007 installed then you have a much better choice of fonts, one of my favourites being Calibri. If you don’t have Calibri, Microsoft ’s trusty Verdana font is more readable online. Unfortunately, there’s currently no way to change the font in Reader’s settings pages. But, if you’re using Mozilla Firefox you can take advantage of user CSS and style any website however you choose, as long as you understand CSS.
Firefox’s user CSS lives in a file called userContent.css in the chrome directory within your Firefox profile which on Windows is somewhere like: %APPDATA%\Mozilla\Firefox\Profiles\<some alphanumeric id>.default\chrome\userContent.css.
Create the userContent.css file if it isn’t there already (there will just be a couple of example files there initially) and add the following:
@-moz-document url-prefix(http://www.google.com/reader/)
{
body
{
font-family:Calibri !important;
font-size:105% !important;
}
}
Tweak the font and size to taste.
Internet Explorer also allows user stylesheets, but there’s no ability to target certain styles for specific sites. It’s made possible in Firefox by the “@-moz-document” and “url-prefix” psuedo-selectors.
It turns out there’s quite a community with the same idea: that they can tweak sites to their own tastes. So check out http://userstyles.org/ and the Stylish extension, which makes it even easier to apply user CSS, and a huge repository of ready-prepared CSS for popular sites, which you can use if you’re not CSS-literate. Daniel Cazzulino has a great example of a pimped-out GMail courtesy of Stylish.
Adobe AIR and HTML Applications
My initial impression of Adobe AIR was that it was some sort of “Flash for the desktop” deal. Developing apps in Flash didn’t really appeal to me so I dismissed it (bad memories Flash development using early versions of Macromedia’s tools).
Fast forward to a few weeks ago, I started experimenting with Twitter (basically publicly broadcasted instant messages). In so doing I looked at a number of desktop clients, of which it turns out there are a several that run on the Adobe AIR runtime. One of the first AIR twitter clients I tried was Jonathan Snook’s Snitter. Being a Snook creation it looks great (and of course, lime green by default). It’s even got a little notification area (née systray) icon for when it’s sitting in the background minimised.
There’s nothing that belies the fact that this is really just an AJAX-y web application without the browser. It consists of just a bunch of HTML, CSS and JS files sitting in my Program Files directory (C:\Program Files\Snitter to be exact). I had a bit of fun opening them up in a text editor and hack around with them and was pleased to see that Mr Snook is a also a jQuery fan.
So an AIR app is just a client-side web app: HTML and CSS for layout (oh and Flash if you want as well), JavaScript for your logic plus some platform interop library-type stuff provided by the AIR runtime all hosted in an implementation of WebKit, the standards-compliant rendering engine also used by Apple’s Safari. Oh and it’s cross-platform: running on Windows, Mac and Linux (eventually).
None of this is new though: Microsoft had the same idea about 10 years ago that you could develop desktop applications in DHTML, CSS and JavaScript/VBScript by hosting the Internet Explorer rendering engine in a bare window and allowing the script access to the host operating system. This technology was called HTML Applications, or HTAs. Many of the Control Panel applets in Windows XP (e.g. User Accounts, Help and Support Center) are HTAs and I think at some point even some versions of Microsoft Money were too. Interestingly, with HTAs, you can if you choose, package all the HTML, CSS, JS, images etc inside an EXE or DLL as resources (served up using the “res://” protocol) rather than having them lying around loose on the file system. To create an HTA: create a web page, rename it to have an *.hta file extension then double click it. It will be opened up by MSHTA.exe - essentially Internet Explorer without any menus or buttons and with a more relaxed security policy (insert cheap IE security jibe here). HTAs have been standard equipment on all versions of Windows since at least Windows 2000, so are a good choice for creating things like custom setup programs when you can’t be sure if the target system has something like .NET installed on it. Plenty of Microsoft’s product CDs use HTAs for their AutoRun launchers, indeed we use them for our main product installer here at InfoBasis.
It transpires that Mozilla are also now getting in on the act with Prism, which makes sense as Firefox itself is a kind of an HTML application. Go to Program Files\Mozilla Firefox\chrome crack open the browser.jar file with a ZIP utility and you’re presented with a bunch of markup (XUL rather than HTML), CSS and JavaScript.
AIR or HTAs give you HTML as your control surface which is so much more flexible than Windows Forms and without the learning curve and heavyweight runtime requirements of WPF. For the application logic: JavaScript is a great, flexible language which when coupled with something like jQuery is incredibly powerful for doing UI manipulation.
It seems that AIR really picks up from where HTAs left off with the added promise of cross-platform compatibility. The Adobe AIR runtime is impressively small (10MB), fast and easy to install. (This highlights a bugbear I have with the .NET Framework these days: it really needs to go on a diet, or become less monolithic. The .NET 3.5 runtime is over 100MB in total, whereas .NET 1.x was about 20MB.)
Anyway I digress. If you want to dig into creating AIR apps in more detail, Jonathan Snook has just written seasonal overview, which goes into topics such as data storage, file system access and application packaging.
Editing and testing scripts in SciTE
SciTE (the Scintilla Text Editor) is my text editor of choice. It’s fast, simple and lightweight. Not only that but it’s based on the Scintilla editor component which can be used in your own apps. There are a couple of versions available for download, my preferred one is Bruce Dodson’s version which amongst other things includes a Windows Explorer shell extension that adds an “Edit with SciTE” command for all file types.
There are plenty of other text editors available that are based upon the Scintilla core, such as Notepad2 and Notepad++, but they seem to lack the main feature that keeps me using SciTE. That is the ability to hit F5 when editing a script and have the console output appear in a separate pane in the editor window. I do plenty of batch file, VBScript, JavaScript scripting and with SciTE I can hit F5 to do a quick test. If any error occurs then the output window will jump to the offending line with a double-click if the error message follows the pattern:
<filename>(<line>, <col>) <error message>
…which is a fairly standard output format for most compilers and script interpreters.
The output window also supports standard input - which means if you have prompts in your scripts for input you can type them right into the output window.
SciTE can be extensively customised, as long as you don’t mind editing (albeit quite straightforward) configuration files. You can edit the core *.properties files in %ProgramFiles%\Scintilla Text Editor, but you’d be better off opening your user options file which is stored at %USERPROFILE%\SciTEUser.properties. I say this because there’s a better chance you’ll back up this file along with your user profile when you next pave your machine. There’s a shortcut to open or create a new the SciTEUser.properties file on the Options menu: Open User Options File.
Anyway, here’s what’s in my options file to help me develop Windows Scripting Host (*.vbs, *.js) scripts as well as good old batch files (*.cmd, *.bat) in SciTE. The key is to set the subsystem property to 0 so that all console input and output (stdin, stdout, stderr) end up in SciTE’s output window:
# *.vbs files command.go.$(file.patterns.wscript)=cscript.exe //nologo "$(FilePath)" command.go.subsystem.$(file.patterns.wscript)=0 # *.js files command.go.*.js=cscript.exe //nologo "$(FilePath)" command.go.subsystem.*.js=0 # Run batch files inside SciTE command.go.$(file.patterns.batch)="$(FilePath)" command.go.subsystem.$(file.patterns.batch)=0
Hope that’s useful for someone else.
