March 6, 2017 at 10:34 am
We are finally getting around to moving an old SQL 2000 database to a new server; new OS; new version of SQL. However, I have discovered that the log file for the main database "seems" to be starting to have some issues - and I really don't want to transfer these issues to the new server.
The issue is the log file is 15 GB ( the main database is 32 GB); has 353 VLFs and every single VLF has a status code of '2'. This tells me that the log has active transactions in EVERY VLF? Strange, as this log is backed up every hour and the database itself is fully backed up every evening at 7 PM. I would think that some VLFs would be freed up, but it does not appear to be happening...or such a small number of VLFs are freed up that they are immediately used over the course of the evening. The hourly log backups resume after the FULL until midnight. Then system backups take over and the hourly log backups resume at 3:15 AM. The current log file is NOT running out of space and has not caused a second file to be generated.
So, my question is - I need to define a skeleton database on the NEW server for this database - main *mdf and the *ldf files - which I will load from a FULL backup. Can I redefine the size and auto-growth factors for the log file to be a more reasonable set of parameters so the number of VLFs is reduced and the growth factor is more in line with the actual growth of the log?...or will the restored log file revert the log settings back to what the original log file was created with ( probably some small initial size - 20 MB and 10% growth that has caused all of these VLFs to be generated.)
In the SQL and web research I have read I do not see this discussed and wonder if it is possible to redefine the log file in this fashion.
March 6, 2017 at 11:15 am
Is this SQL 2000 or 2008?
When you restore, you get the settings from the database backed up, but you can change your growth settings.
March 6, 2017 at 11:35 am
Hi Steve - wow...I put in a question and the main man answers!! LOL
Bear with me the explanation is tedious - The production database is on Windows SQL 2000; OS is Windows Server 2003. Because I have read that I cannot go 2000 to 2008, I built a SQL 2005 server on VM and I will jump from 2000 to 2005 to 2008 and the final 2008 location will be production. I feel that it is necessary to do this because we have to change SQL version and OS version during this migration. I can't go to 2012 SQL or higher because the vendor has not certified the application for 2012, so I'm stuck at 2008.
So, if the restore on the new skeletal database causes the log file to revert back to the original log definition, what can I do on the SQL 2005 environment (before I backup and restore to 2008) to resize the log file? Will I have to go through the process as described in either Gail or Jeff's posts on SQLCentral? This is the only way to redefine the file and resolve this "stuffed" log file? I'm curious why the log is not getting some of the VLFs freed up when the backups are run.
March 6, 2017 at 11:51 am
OK, if you're going up from 2000, do that first. Go to 2005 and get there.
Then what I'd do is clear up your VLFs first. You can do this as described in many places, but rebuild the log and get the number down. Then grow the log file appropriately for your workload. Glenn Berry has a good post: https://sqlserverperformance.wordpress.com/2010/06/22/detecting-and-reducing-vlfs-in-sql-server-20082008-r2-transaction-log-files/
Once you've got the log set, then move it to 2008. Again, I'd do this with backup/restore, as this gives me another place I can return to if I have issues.
March 6, 2017 at 12:47 pm
Thank you Steve, for the additional reference to Glenn Barry's post. I had not seen that one. It will be a big help. I'm almost thinking that it would be best if I tried to clear up this
issue on the SQL 2000 box (production) over a weekend - BEFORE I did the migration to SQL 2005. I believe I will try changing the growth factor to 2 GB in production (instead of 10%).
Perhaps that will give me just a tad of space, once it auto-grows. I just feel that I should be seeing one or two VLFs with a status of '0'. Would running a CHECKPOINT after I run a
transaction backup be of any help?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply