You've kind of answered your own question. You said the application requires a particular version of MSSQL server, so that would seem to preclude any type of upgrade to another version. I would recommend getting your "out of hard drive space" issue resolved. IMHO, it's best to address the root cause of the problem (disk space) instead of trying to find a workaround for the problem (upgrading MSSQL). As others here have suggested, take a look at the transactionlogs as they're the most likely culprit here regarding disk consumption.
–
joeqwertyNov 20 '09 at 12:51

4 Answers
4

You could probably use SQuirreL java SQL client to do a export of the db and then import it into another db. Its a little bit of a learning curve to do that though... i'd say the transaction log/backup idea is much better...

So, if you never back up an SQL Server it uses space endlessly as the transaction logs grow and grow. Have you tried backing it up? A backup causes a checkpoint, which lets SQL server know it doesn't need to keep certain transaction log files.

You might need to back it up to a remote system if the drive is too full. Remember, the backup will be written by the Windows account that SQL is running as, not your user account - so that user needs to have access to the network share you are attempting to back up to.

There are other reasons for MSSQL file growth, of course, including insertion of data. ;) If that's not it however, you can look at things like transactions that aren't ever committed.

The answer to your stated question about changing from MSSQL to some other platform is that it depends. If you are referring to an app that already supports multiple DB back ends, then it can be as easy as dumping your data to some universal format, then importing it in to the db platform you prefer, then switching your app to use your preferred DB - but in most cases it is not this simple. In most cases, the application is tightly coupled to the RDBMS platform and it's not as simple to "change back ends" without re-writing significant parts of the code.

Besides the comment from GregD, it maybe the host that is out of space.

You could update your question with the following information:

SQL Server Version?

Host HDD Partitioning and drive space?

Size of the SQL Server data files and log files?

Maintenance plans that you have?

It may just be that you need to truncate your log file if you are using a simple recovery method.

As for moving to a more modern SQL version, you will need to setup a test lab first then try the application against SQL Expr 2005 or 2008. I would also put the database into compatibility mode to ensure even more compatibility.

You may be in a position where you cant put the app against another version as it may be using stored procedures or views that only exist in the MSSQL Server version that it is linked to.

If you can't upgrade the database and you are indeed having resource issues you would need to investigate the following:

Upgrade the system by adding more HDD space

Add a small SAN to the site and attach to the system. Move the SQL Database to the SAN for improved performance

Upgrade the system by replacing the metal that it runs on

Consider consolidating the service by thinking about Virtualisation if excessive IO/Memory is not required

After you have done analysis and have some factual data in front of you can you then start making recommendations to the appropriate persons on how best to move forward and ensure this resource is available for the foreseeable future.