1

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?

  • Backing up the log prevents growth, but it doesn't (and shouldn't!) shrink. See dba.stackexchange.com/q/29829/1186 – Aaron Bertrand May 9 '16 at 21:08
  • Any idea, how my c drive size increased 5 GB in just a week even though there is no heavy transaction on the system? A week ago, the C drive was full, and the disk size was reduced from 234 mb as free space till 190GB as free space (after taking the backup). Now the free disk space is 185 GB. I was told that the C drive usage is reduced when the backup is taken. I am wondering If I am not taking the backup properly – Mussa May 9 '16 at 21:29
  • As a general rule of thumb for sql-server, it loves to hold any memory (RAM or Disk) it can get for as long as it can regardless if it actively uses it or not. This is actually a good thing because the thought process behind it is: If it uses it once, it will need to again. And because it already has the space reserved, it doesn't have the expensive operation of having to secure the memory again which can be very costly to do. – Eric S May 9 '16 at 21:33
1

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.

  • I can see that the transaction logs is having larger unused space after the backup. I understand what you mean by your analogy you used. But I faced this case a week ago: my c drive free space was 234 mb. After taking full and log backup, the free size of c drive became 190 gb. Now it reached 185 gb as a free size. When I take a full and log backup then it seems that the disk used space is not decreased. I am wondering if I am not taking the backup properly. – Mussa May 9 '16 at 22:01
  • Since taking a backup can't actually increase your free space I'm going to say the two aren't related. On an off guess you probably have your tempdb located on your C drive (any DBs on your C drive should be moved and particularly that one). When you re-start SQL Server tempdb will go back to it's default size (depending on what that's set as). You can look in sys.master_files for database files residing on the C drive. – Kenneth Fisher May 9 '16 at 22:18
  • Is it possible you have a shrink database step in that maintenance plan? Simply backing up will never reduce the file size as Kenneth has stated. – Chad Mattox May 9 '16 at 22:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.