Shrinking a SQL Server database

Tags:
SQL Server 2000
SQL Server Database Shrinking
SQL Server databases
SQL Server Enterprise Manager
My problem is regarding SQL Server 2000. Whenever I try to access SQL Server Enterprise Manager, I am told that the disk is full, along with the tempdb log. I tried to shrink the database, but for some reason I am not able to do so. Can you help me?
ASKED: February 25, 2009  8:48 PM
UPDATED: February 25, 2009  9:20 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following