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 recently added a new disk to our SQL Server box and followed these steps from Microsoft to move the .mdf file to the new drive. I opted to leave the log file on the original disk for performance reasons.

When I tried to bring the database back online, I was met with a systems permissions exception (admin access was needed). I went into Windows Services and changed SQL Server to log on as the local system account. This appeared to have resolved the problem.

However, two days later, the .mdf file is the same size as pre-transfer (I took it offline and only the timestamp changed). I checked the log, which is usually under 1GB, and found that it has now reached 30GB.

Am I missing something? Why hasn't SQL Server written any of this new data to the DB file itself?

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

However, two days later, the .mdf file is the same size as pre-transfer (I took it offline and only the timestamp changed).

There was enough free space in your data file for any new data that was written. When that spare/free space is consumed the file will grow, not before. You can confirm the size and free space in your data file with:

SELECT 
    [File Name] = name
    , [Physical Name] = physical_name
    , [Total Size in MB] = size/128.0
    , [Available Space In MB] = size/128.0
        - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 
FROM 
    sys.database_files;

I checked the log, which is usually under 1GB, and found that it has now reached 30GB.

Your database is in full recovery mode and you aren't backing up the log. For this and all manner of transaction log growth explanations, see @MikeWalsh's canonical answer on the topic at Why Does the Transaction Log Keep Growing or Run Out of Space?

share|improve this answer
    
Actually, I am backing up the log file in ten minute intervals. With truncate enabled, it still doesn't shrink. Instead, it compounds the same log data over and over (even if no operations are performed on the database in the 10 minute interval), resulting in excessively large backups. The log_reuse_wait_desc column says "log backup" where all other databases say "checkpoint." –  JoshJonesCS Apr 21 '13 at 1:52
    
@JoshJonesCS Although not entirely clear, it sounds as though you are backing up the log to the same file repeatedly, so the backup file is getting larger? –  Mark Storey-Smith Apr 21 '13 at 15:02
    
It's set to append to the existing backup set. Should I be using a different option? Prior to the .mdf relocation, I never saw the log inflate this much, which is why I'm worried. –  JoshJonesCS Apr 21 '13 at 17:54
    
You aren't looking at the log size, you are looking at the size of all backups of the log, appended to a single backup device file. Have you taken a full backup of this database? It sounds like you don't fully understand transaction logs, or backups. You should read the linked answer in full and the BOL topic on backups. –  Mark Storey-Smith Apr 21 '13 at 18:42
    
The database is fully backed up once every night. I Googled for two days before asking. No answer explains why the .mdf stops growing and log_reuse_wait_desc says "log backup" despite frequent log backups and daily full backups. –  JoshJonesCS Apr 21 '13 at 19:28
show 2 more comments

make sure it is in full recovery. take a full, take a log backup. check the free space int he log, not the log size and verify that it has given back some space. Check to see if you have a transaction open by another SPID, be careful killing it if it has been active and writing data could mean a 29GB rollback that would take forever to clear. Check to see if you have CDC or replication enabled on this database as both can keep the log from clearing.

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.