"Out of Disk Space" Errors on SQL Server Machine
Technical Note 1608
Last Reviewed 16-Sep-2007

Applies To
Express Software Manager version 6.0 and higher

Summary
If you see "out of disk space" errors on your SQL Server, you may be able to resolve the problems by changing the database backup mode to simple, backing up the database, and shrinking it. This technical note describes how to do this process using OSQL commands.

About the OSQL Utility and OSQL Commands
The OSQL utility comes with every copy of SQL Server, including MSDE. This utility allows you to run scripts, system procedures, and Transact-SQL statements directly from the operating system command prompt.

OSQL commands can include a large number of parameters or switches. To see a list of OSQL command options, enter the following command at the command prompt of the database server (the machine on which SQL Server or MSDE is running):

osql -?

Many of the switches are case sensitive, so enter the commands in this note exactly as they are presented.

The OSQL utility can be run in interactive mode, or in batch mode. The instructions in this technical note specify a single command line that starts the utility, executes a query, and exits the utility afterward.

For more information about OSQL, see the Microsoft Web site.

Reducing the Amount of Space Used by the Database
The process of configuring an MSDE database to use less space is a three-step process:

1. Change the database backup mode to simple.

2. Back up the database.

3. Shrink the database.

Changing the Database Backup Mode to Simple
To change the backup mode of an SQL database using OSQL, enter the following case-sensitive command at the command prompt of the database server (the computer on which the database resides):

osql –U<username> -P<password> -Q"ALTER DATABASE <database name> SET RECOVERY SIMPLE"

Example
Note: Unless otherwise stated, the below example assumes that you are changing the backup mode of the Express database, are using the “sa” account, and that this account has a password of “esm02”.

Changing the backup mode of the Express database
To change the backup mode of the “Express” database, enter this command at the command prompt of the database server:

osql -Usa –Pesm02 -Q"ALTER DATABASE Express SET RECOVERY SIMPLE"

Backing Up an SQL Database using OSQL Commands
To back up an SQL database using OSQL, enter the following case-sensitive command at the command prompt of the database server (the computer on which the database resides):

osql -U<user> -P<password> -Q"BACKUP DATABASE <database name> to disk = '<path>\<database name>.bak' WITH INIT"

Example
Note: Unless otherwise stated, the below example assumes that you are backing up the Express database, are using the “sa” account, and that this account has a password of “esm02”. The example also assumes that the C:\temp folder already exists, while the database backup file “Express.bak” does not exist.

Backing up the Express database
To back up the “Express” database, enter this command at the command prompt of the database server:

osql -Usa –Pesm02 -Q"BACKUP DATABASE Express to disk = 'c:\temp\Express.bak' WITH INIT"

For additional information about backing up SQL databases using OSQL, see Technical Note 1596.

Shrinking an SQL Database with OSQL Commands
To shrink an SQL database using OSQL, enter the following case-sensitive command at the command prompt of the database server:

osql -U<user> -P<password> -Q"DBCC ShrinkDatabase (<database>)"

Examples
Note: Unless otherwise stated, the below example assumes that you are shrinking the Express database, are using the “sa” account, and that this account has a password of “esm02”.

Shrinking the Express database
To shrink the “Express” database, enter this command at the command prompt of the database server:

osql -Usa –Pesm02 -Q"DBCC ShrinkDatabase (Express)"

Related Technical Notes

1596	Backing Up an SQL Database using OSQL
1606	Maintaining Your SQL Database With OSQL
2000	Express Software Manager Technical Notes
9989	Express Software Manager 6.x Technical Notes