Delete log to increase DB space?

Question

I'm running SQL Server 2008 Express and hitting the 4GB size wall. I'd like to make some space to give me time before i migrate to a larger DB.

I don't need my transaction log. So, can i delete my log to increase space?

Otherwise, i'll have to create a duplicate DB structure, and then copy the data into the new DB and then delete everything from the original DB so i can start over.

In this case, is there an "easy way" to copy the data from the old tables into new tables?

Thomas Stringer · Answer 1 · 2014-03-24T13:35:33.037

I don't need my transaction log.

Yes you do.

can i delete my log to increase space?

No you can't.

In this case, is there an "easy way" to copy the data from the old tables into new tables?

Before you go throwing the towel in with your current database, you should step back and analyze why you think your transaction log is too large in your current database. Unless you logged workload changes, the same behavior will occur in the new database.

Find out first how large your transaction log file is and how much space is actually being used inside the file:

use YourDatabaseName;
go

select
    name,
    physical_name,
    type_desc,
    size_mb = 
        convert(decimal(10, 2), size * 8.0 / 1024),
    used_space_mb = 
        convert(decimal(10, 2), fileproperty(name, 'spaceused') * 8.0 / 1024)
from sys.database_files;

Note: you'll need to change "YourDatabaseName" to the appropriate database name.

Likewise, there are certain database settings (recovery model) and log reuse descriptions that can also aid in the troubleshooting of a transaction log that is just too large:

select 
    name,
    recovery_model_desc,
    log_reuse_wait_desc
from sys.databases
where name = 'YourDatabaseName';

The above queries should be a decent starting point with troubleshooting and/or discovery regarding your problem. Feel free to comment below with your findings and we can take this a step further.

Another thing, are you completely confined to SQL Server 2008 for your Express Edition? In SQL Server 2008 R2 and above, the maximum database size is 10 GB. Can you not use a newer version of SQL Server?

Delete log to increase DB space?

1 Answers1