I'm somewhat of a new DBA and I'm managing a SQL Server 2012 instance that has a fair amount of activity. I'm running in Full Recovery mode because we need point in time recovery.
Right now, I'm taking a full backup of the databases and logs every day at 5am. Some of the log files have ballooned up to 300gb and even after taking a backup they don't reduce in size. I can get them to reduce in size by running something similar to:
BACKUP LOG db1 TO DISK = '\\server\share\db1_log1.trn'; DBCC ShrinkFile([db1_log], 0); BACKUP LOG db1 TO DISK = '\\server\share\db1_log2.trn'; DBCC ShrinkFile([db1_log], 0); BACKUP LOG db1 TO DISK = '\\server\share\db1_log3.trn'; DBCC ShrinkFile([db1_log], 0);
When I check the LSNs of the backup files I see something like:
RESTORE headeronly FROM DISK = N'\\server\share\db1_log1.trn' FirstLSN: 15781000014686200001 SecondLSN: 15802000000665000001 RESTORE headeronly FROM DISK = N'\\server\share\db1_log2.trn' FirstLSN: 15802000000665000001 SecondLSN: 15805000000004100001 RESTORE headeronly FROM DISK = N'\\server\share\db1_log3.trn' FirstLSN: 15805000000004100001 SecondLSN: 15808000000004200001
I don't believe I'm breaking my log chain by shrinking the log files. Reading up on this, I do believe I'm hurting my performance because those shrunk log files have to re-grow themselves.
- Why doesn't the log file shrink after my backups? Is it because there are uncommitted transactions?
- At first I was thinking I should shrink the log files after every 5:00 AM backup. After reading up on how that's bad for performance I now believe that I need to take regular log backups every couple of hours during the day. Is that correct?
- My normal full backup of the database/logs happens every day at 5:00 AM and sometimes takes 3 hours. If I schedule the log backups to happen every hour, what will happen when the log backup collides with the 5:00 AM backup?