I must confess, 2005 was an odd year. Perhaps you remember a column a few months back, describing the wretched flight with four cats and an ill seatmate, moving the household from from the left coast to the right. One cross-country move in a lifetime should be enough for anyone; for us, however, two per this year seem to be required.
It’s awfully embarrassing when I run into friends, and they say “So, I hear you moved from California to Florida. How’s that working out for you?” (There’s often a slight hint of schadenfruede in the tone, as well-hurricanes have been in the news for the past two years.) The embarrassing part is when I respond with “Well, we did move to Florida, but it didn’t work out. We’re back in California again.” It’s true-condo life wasn’t for me. Loved being on the beach. Loved having friends and business partners Andy Baron and Mary Chipman 200 yards away. Didn’t so much love the neighbors who arrived six months into the experience. They turned out to be lovely people, but they were accustomed to living in much larger quarters. Their 6000 sq ft house had been devastated by a hurricane, and they decided to relocate and downsize, somewhat begrudgingly, into a condo on the beach (go figure). We survived door slamming, yelling and histrionics at 2AM, laundry being done loudly at 4AM, and more door slamming all day and night. The building shook at the whim of the neighbor’s comings and goings. Being inherently quiet and private folks, this didn’t sit well on our side of the paper-thin walls. Nerves jangling, we discussed it with the offenders, who promised to try to be better. Two weeks later, with no real improvement in sight, we looked at each other, and confessed simultaneously: “We have to move.”
As you may remember, I requested suggestions on great places to live in this spot four months ago. I did get some very sincere responses, and for those, I am grateful. Luckily, the neighbors in Florida had been lusting after our real estate, wanting to expand into our square footage, and were willing to pay full price to get the space. We were willing to take full price to get the quiet. The fortuitous deal executed, we again fled across the country. (For those who have the slightest interest, we transported the animals this time by flying twice, taking two at a time. I’m guessing no one really has that interest, so we’ll just move on.)
And where did we end up? NoCal, thanks. Out in the woods. No beach, but lots of deer, wild turkeys, and much flora and fauna. Some snow, but not much. Friendly neighbors, far enough away that we don’t see or hear them, but close enough for help in an emergency. So far, it’s a winner. Hopefully, there’s only one more move in the works, and I won’t notice that one.
Of course, when one moves across the country, and has to pay movers out of ones’ own pockets, one makes the effort to downsize so as to avoid paying to move any extraneous belongings. We downsized seriously before the first move, giving away tools, gardening supplies, furniture, and more, to friends and relatives. The friends to whom we gave most of the stuff ended up being the ones that suggested we move to the new community, where they also settled. Donated stuff keeps flowing back in our direction from Doug and Diane, tipping the scales closer to their original balance. Every week, something new appears in the garage that we haven’t seen for a year. It’s like Christmas, all the time!
Speaking of downsizing (finally, the real content), I’ve been spending some time working with and demonstrating ASP.NET 2.0’s membership functionality. I love how simple they’ve made it to manage users and roles, storing data in a local SQL Express database named ASPNETDB.MDF. I also need to distribute and deploy these demo applications, often to attendees at classes or conferences.
Here’s the problem: By default, ASPNETDB.MDF is 10MB in size. Yikes. That’s a lot of “air” to be transporting, when you’ve only added three users to the system. There may be some global setting that controls the default size of this database, but it’s too large for my needs, for sure.
Using SQL Server 2005, I could certainly shrink the database-the Management Studio includes a right-click interface to do this. I could also use the DBCC ShrinkFile statement to do the work, but it’s not quite that easy. The documentation doesn’t explain how to shrink the contents of a free-standing MDF file, and all the attempts I made simply did nothing at all.
Finally, I called in my partner, Brian Randell. He’s spent a lot more time with SQL Server 2005 than I have, and he, too, was at first stumped. With a little research, he came up with a solution that works quite nicely, and he has posted a blog entry about the answer: http://www.mcwtech.com/CS/blogs/brianr/archive/2005/12/16/138.aspx. In his code, he creates a temporary database, attaches the MDF file to the new database, and calls the DBCC ShrinkFile statement. Finally, he detaches the temporary database:
SET NOCOUNT ON;
GO
USE MASTER;
GO
CREATE DATABASE SHRINK_ASPNETDB
ON (FILENAME = 'C:\Path\APP_DATA\ASPNETDB.MDF'),
(FILENAME = 'C:\Path\APP_DATA\aspnetdb_log.ldf')
FOR ATTACH;
GO
USE SHRINK_ASPNETDB;
GO
DECLARE @FileName nvarchar(100);
SELECT @FileName = FILE_NAME(1);
DBCC SHRINKFILE (@FileName, 1);
GO
USE MASTER;
GO
sp_detach_db SHRINK_ASPNETDB;
GO
(You will, of course, need to change the paths to the database and log file to match your own situation.) From the Windows command prompt, you can run the sqlcmd utility, like this (assuming that you’ve named the script shrink.sql):
sqlcmd -E -S .\SQLExpress -i Shrink.sql
Brian’s tip will certainly save me a lot of time and effort, no longer requiring me to cart around huge empty databases. (For more of Brian’s blog, drop by http://www.mcwtech.com/cs/blogs/brianr).
For now, I’ve downsized and moved enough. I saw a movie recently in which a character was packing to move, and I actually found myself getting nauseous. I’m here to stay for now-you’re bored hearing about the moves, and goodness knows, I’m tired of participating. Thanks to Brian, at least, we’ve found an easier way to move ASP.NET’s membership database around.
