Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a SQL Server 2005 Database on a dedicated partition, during the time the database grown and now it have occupied all the space on the partition, now the problem is that the only operation I can do on the database is detach, but i want to remove old data from some tables to save space ...

How can I remove old data from the database if SQL Server interface doesn't allow to run queries on it ?

share|improve this question
    
Detach the database, move it to a drive that has enough space, attach the database, and perform whatever maintenance you need to shrink it. –  joeqwerty Nov 19 '12 at 18:37
    
@joeqwerty yes, is what i'm actually doing, but I was hoping for something different :-) –  aleroot Nov 19 '12 at 18:40
    
Unfortunately, if the disk doesn't have enough space for you to perform any operations in SQL then you have no choice. –  joeqwerty Nov 19 '12 at 18:47
add comment

1 Answer

SQL Server cannot perform the operation because the transaction log is full; it has nothing to do with the data file(s).
As long as there is still space in the transaction log, SQL server will not stop working.

You need to find out if and why the transaction log is full:

DBCC SQLPERF (LOGSPACE)

What you can try is to switch the DB to SIMPLE recovery:

ALTER DATABASE YourDB SET RECOVERY SIMPLE

If it doesn't free up space, see what is preventing it:

SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'YourDB'
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.