Truncating your SQL 2008 Database with a few lines of SQL…

Here’s a scenario you may be familiar with: you’ve got yourself a nice Sharepoint setup that you’ve gotten to run rather nicely. Conscientious admin that you are, you’ve set up a good, solid maintenance plan that checks your database health, backs up your database and transaction log… But all of a sudden, your backup drive fills up. Since everything has been hunky dory you only realize this during your next server check and by then, the transaction log’s grown to monstrous proportions! You clear up your backup drive and free up space, but you realize to your horror that your transaction log isn’t shrinking… Oh no!

If all of this is hitting home, you’ve probably already realized that the nifty little commands that used to work in SQL Server 2005 aren’t working on SQL Server 2008. So did I. Here’s my new trick for truncating your SQL 2008 database, hope it helps. I would highly recommend you read the whole article thoroughly before proceeding, it has information that you need to know before you do what you’re about to do.

Open up SQL Management studio, then open a query window to the database. For simplicity’s sake, I’ll assume your DB is called WSS_Content but if you’ve got multiple Content Databases / Site Collections (as well you should), the same applies with a different database name / log file name.

First, run this:

alter database WSS_Content set recovery simple with NO_WAIT
go
checkpoint
go
dbcc shinkfile(WSS_Content, 1)
go
alter database WSS_Content set recovery full with NO_WAIT

And get yourself some coffee. Lots of coffee — the bigger your transaction log is, the longer it will take. Run this during a weekend, or as soon as you can when there are as little people in your office as possible; do NOT abort the process, or you’ll regret it.

The above snippet of code switches your database from a full recovery model to a simple recovery model. The full recovery model makes thorough use of the transaction log; the simple does not. Before SQL Server actually makes any changes to its database, it stores the commands in the transaction log – this is so that if your server crashes it can continue to execute what it was doing when it crashed. This is what makes your SQL database so nice and robust: it is catalogging EVERYTHING it’s doing so that if something goes wrong it can retrace its steps.

I know what you’re thinking, and no. It’s not a good idea to keep your database in ‘simple’ mode, no matter how good your backups are. The rule of thumb is that if you have a production database that stores data of any relevance at all to you, you should be using the full recovery model, period. If your database is a ‘holding area’, if you’re just using it to perform computations and pass it off to another database, you can use a simple recovery model, maybe even run the database on a RAID-0 array so it’s nice and fast. Or if your database is written to only once a day, for instance if you are retrieving data from another site or the web and caching it locally, then backing it up immediately afterwards. Those are the only two examples I can think of where it makes sense to use a simple recovery model.

Now that you’ve executed the above code, the following code should be pretty fast:
backup log WSS_Content to disk = N'{some backup location of your choice}’
go
dbcc shrinkfile(‘WSS_Content_Log’, 1)

This is what actually shrinks the file. It makes a backup of the transaction log as SQL 2008 expects it and then shrinks the file. Of course, if you have enough space in your backup drive, you may wish to just execute this code – it’ll all depend on how big your transaction log has grown.

>SQL annoyances

>So here’s a nice little pickle I got myself into: migrating an SQL Server 2008 database to another server this morning, I’m confronted with a nice little F-U message:

The database “x” cannot be opened because it is version 661. This server supports version 655 and earlier. A downgrade path is not supported.

Nice, eh? I thought it was sweet.

This cute little error is due to the fact that SQL Server 2008 and R2 have different “compatibility levels”. What this means, in essence, is that databases can’t be migrated from one flavor of SQL Server 2008 to the other using the traditional detach-attach method. Oh, and in case you’re wondering — no, you can’t use a simple backup-restore operation either; nice try, though.

So — am I screwed?

Note that this is only a problem if you’re going from an newer version of SQL Server to an older one (in my case, this was from SQL Server 2008 R2 to ‘plain old SQL Server 2008). If this is happening to you, don’t worry: there are ways to coax your database into its new environment. The simplest, of course, is to use the same version of SQL Server 2008 as your old machine. But perhaps this isn’t what you would like to hear. That’s certainly not what I wanted to hear: upgrades are free if and only if you have Software assurance.

So here are a few possibilities; each a wee bit suck-y, if you ask me:

  • Script all your database objects to a giant, mahoosive SQL file. Not great, but feasible if you have a small database.
  • Have both SQL Server 2008 and R2 running on the same machine; link instances, and run Export Data. Database Services and Replication features must be installed. Unfortunately, what this does is upgrade your shared components. Suck.

If you have any other means of doing this, be a pal and let me know, won’t you? 😀

>Unicode in Ruby with SQL Server

>I’ve been using Ruby on Rails a lot recently, and in one instance I found myself in a bit of a pickle: using UTF-8 fields with SQL Server 2005. After a bit of googling and a lot of playing around, I finally found a solution that seems to work; figured that someone out there might find it useful…

Here’s the environment in which I tried this out:

Here are a few things I tried beforehand and which didn’t work:

  • Installed mssqlclient (had high hopes for this one but I kept getting errors about the adapter not existing. If anyone’s got some insight here, please let me know and I’ll add your URL or text to the article :-D)
  • Tried altering the column types (biggest problem here was with the constraint. Yes, you can drop the constraint but I found that it beat the purpose of using rails’ scripts to generate the data model because every time you’d want to drop the column you’d have to edit your migration and plug in the constraint’s unique name… Yuck.)

You’ll also note that, according to rubyonrails’ wiki on using unicode strings (http://wiki.rubyonrails.org/rails/pages/HowToUseUnicodeStrings), you don’t need to change much of your application’s code to switch to UTF8. As a matter of fact, I simply performed the necessary tests from script/console and realized that $KCODE and ActiveSupport:MultiByte were already set / installed.

My problem actually occured when my migrate scripts were generating the data model: instead of generating the utf8 fields nvarchar and ntext, it was creating varchar and text. If your environment is already set up to support utf8, the only thing you need to do is modify the sqlserver_adapter to generate the correct fields.

This is how it’s done:

  • Navigate to C:rubylibrubygems1.8gemsactiverecord-sqlserver-adapter-1.0.0.9216libactive_recordconnection_adapters (assuming that you’re using the default installation paths)
  • Save a copy of sqlserver_adapter.rb (duh :-D)
  • Edit the sqlserver_adapter.rb file and locate the native_database_types method.
  • Change ‘varchar’ to ‘nvarchar’ and ‘text’ to ‘ntext’ – Save the file
  • Regenerate your data model (yup, you’ll need to delete your existing database). If you’ve already got production data in your database, you’re most certainly going to want to backup your data, restore it to another database and migrate data using ETL’s (hand-rolled or SSIS…) Best of luck!

A few last things:

  • In your database.yml file, make sure you’ve got this line in the profile:

encoding: utf8

  • In the ApplicationController class, you’ll need to define a before_filter method to set the correct headers; open up the application.rb file and set up a before filter like this:

before_filter :set_charset

def set_charset

#Sets the response headers to UTF-8. Alone, this doesn’t do squat: 

response.headers[“Content-Type”] ) “text/html; charset=utf-8”

#Sets the code page; very important…

WIN32OLE.codepage = WIN32OLE::CP_UTF8

end

Many thanks to Andres Mondano Pellegrini for the code. You’ll find his article, which saved my ass, here: www.gaugeus.com (if you’re having trouble finding it, perform a search on “Unicode Support in Ruby on Rails with MySQL and Microsoft SQL Server”)