I have a few questions that I want to understand in order to take informed decision. I am not a DBA by profession, I have done some administrative tasks in the past and now I would have to create a maintenance plans to some of my new servers and understand the issues with my old servers.
My database log size is 50GB(converting MB to GB) and when I use the following query:
SELECT name, size/128.0 FileSizeInMB, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS EmptySpaceInMB FROM sys.database_files;
it returns 25GB as the Empty space consistently. My database is in FULL recovery mode with hourly log back up. Doesn't this log file get trimmed at some point in time after hourly transactional backup and reduce the size of the log file?
We have a defragmentation plan set up at night. This plan runs for 15 mins and rebuilds all indexes above 40% fragmentation level. The way the DBA from one of our vendors explain is that during the rebuild the tables are locked and hence make the log file grow. I don't understand this clearly. Doesn't the log files grow in both rebuilding and re-organizing? Cant a long running re-organize fill up logs quickly?
How frequently should we update statistics after a daily index re-organize - immediately or weekly/monthly?