0

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 ?

Improve this question
3
  • 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, 2012 at 18:37
  • @joeqwerty yes, is what i'm actually doing, but I was hoping for something different :-)
    – aleroot
    Nov 19, 2012 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, 2012 at 18:47

1 Answer 1

Reset to default
0

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'
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .