Odds are your transaction log has filled the hard drive.
Log into the instance with Query Analyzer and use the BACKUP LOG statement to remove the data from the transaction log.
<pre>BACKUP LOG YourDatabase WITH TRUNCATE_ONLY</pre>
You should then be able to shrink the log file using the DBCC SHRINKFILE statement.
<pre>USE YourDatabase
GO
DBCC SHRINKFILE (YourDatabase_Log, 1024)
GO</pre>
In the above example the file name is YourDatabase_Log and the size we are shrinking it to is 1024 Megs.
You can query the dbo.sysfiles table to see the names of the database files if that isn’t the correct file name.
After you correct the drive space problem, if keeping the transaction log around for point in time recovery is not required you can change the database from FULL recovery to SIMPLY recovery. This will prevent the transaction log from filling the hard drive in the future.
To change the recovery mode log into the server with SQL Server Enterprise Manager and right click on the database and select properties. On the options tab and change the recovery mode from FULL to SIMPLE and click OK.
(Those instructions may not be perfectly accurate, I’m typeing this from memory as I don’t have a SQL 2000 server handy to look at.)
Discuss This Question: