Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
i've been doing some reading, and i understand that i can't just delete the log file. So, it looks like i'm going the route of creating a backup DB for the historical data, and starting over. I only have 6 tables, 2 of which are large and have clustered indices..the other tables are on the small side. –  steve Mar 24 at 13:02
1  
What's your recovery model? Are you running log backups if you're in FULL or BULKLOGGED? Look at this answer for more detail. –  Mike Fal Mar 24 at 13:40

1 Answer 1

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?

share|improve this answer
    
so i used the import / export wizard to create a copy of my DB data. Now i'd like to purge my original database (to make space) so that i can continue operating, until i migrate to a larger DB. How do i do this (is this just deleting data?) –  steve Mar 24 at 13:57
    
You can try running shrink on your DB (technet.microsoft.com/en-us/library/ms189035.aspx), but given that your size is relatively small to begin with the gain won't likely be that much unless you are running a DB with very high traffic. You can see the possible gain by viewing your DB's disk usage. Otherwise maybe consider normalizing your tables if they aren't fully normalized, this likely could save some space through elimination of redundancy. –  Conor Mar 24 at 18:22

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.