I am using SQL Server 2012. I created the backup (full and transaction log backup) using maintenance plan and scheduled the job using SQL Server agent.
After the success of the backup, I cannot see why the C partition on my disk is not reduced. What I know is that after the backup of logs, the disk will be reduced.
Forgive me for the ignorance but I did my search. Plus, I am coming from Oracle database background.
Is there anything wrong? Under what circumstances the disk size is not reduced after log backup?
Best Answer
Full backups have nothing to do with the size of the database files. Log backups only peripherally affect log file size. If your database is in full recovery mode then the data in the log file will remain until a log backup is taken. This means that as more data is written to the log file additional space will be required.
Think of it as a bucket. Over time water is added to the bucket. Every time you take a picture of the bucket (log backup) you can pour the water out. Regardless of how full the bucket is it remains the same size.
Now let's say you have a large transaction (requiring a lot of water) or you haven't been taking your log backups frequently enough. SQL wants to add water to the bucket but it's now full. You now need a bigger bucket. So SQL goes out and gets a bigger bucket. Now your bucket is larger. You take a log backup and the bucket is emptied. The bucket doesn't shrink back to it's original size. Your regular usage may now only fill a small portion of the bucket but that again doesn't affect the size of the bucket.
Note I'm way over simplifying. There are other things that can cause your bucket not to empty.