2

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?

JoshJonesCS
  • 23
  • 2

2 Answers2

5

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?

Community
  • 1
Mark Storey-Smith
  • 31,860
  • 9
  • 90
  • 125
3

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.

Wes Brown
  • 31
  • 1