we are using sql server 2008 r2 standard with sp2
I have a 12 GB database in production server, log file was set to 150MB with increment of 10 percent, in last 4 years database size has gone from 2gb to 12 gb
i ran following command
and found i have 150 plus rows(which means 150 plus virtual log files)
i increased the size of log file to 25 percent of data file which comes to be approx 3gb and also set auto growth to 20 percent
addtional info: we have a log shipping environment in production, i am taking log backups every 15 mins.....
still number of virtual log files is same , why is that , how to bring them between number of 25-50 as thats the recomended
Last edited by amitwadhawan123; 11-08-12 at 12:19.
The only way to eliminate virtual log files is to shrink the log file (see DBCC SHRINKFILE), then set the log file to the required size. Since you are in FULL recovery mode, this may take several attempts, as the log file will not shrink below the current high water mark in the file (last active virtual log file).
now the t-log backup goes for logshipping it reduced the log file size to 12 MB even though i set to 3gb, how much percentage increase should be done?? how to handle this during batch processes which occur once a month and affect 1 million rows on an avg?
now number of Vlog files is 42 ....??
Last edited by amitwadhawan123; 11-08-12 at 12:24.
There will be fewer virtual log files, if you create the log file at the appropriate size (3GB, it looks). When a physical log file grows, new virtual log files are appended on the end. None of the old ones are reallocated. In short, auto growth is bad.