Cannot Shrink SQL Logs. 99% Empty space.
-
Hi there,
I followed many guides and articles to solve my problem. I am disappointment because I cannot find out what I am doing wrong, and I must be doing something wrong.
I have a SQL database and I backed up the database and the logs.
Then I go to shrink and I see that it is nearly 97% empty. So I am expecting after the shrink a filesize of 3% of the original.
It does not change anything... my file is still 16 gigas and I need help.
Here is a quick view of the options. I am showing you that as the recovery model is unusual to me.
Lets assume my database is named dbo_DBFramework, could anyone here send me a script i could run and it would back up the database in C:/BACKUP and then backup logs and shrinks it.
My second question is regarding the shrinking itself. After reading, I saw some people did not recommend not to shrink but I did not understand how it is possible to reduce the log size then....
Thanks for your time
Update 1 : I tried the same process on another server I have on a VM and it is working fine.
- This topic was modified 5 years, 2 months ago by jbeclapez.
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
There will be a few things in play here.
Since the database is in bulk-logged mode, the transaction log will not be cleared out, until you backup the log. It is likely that the active portion of the log is at the tail end of the log file. In order to find out, you can run DBCC LOGINFO() in the database. Check the Status column. Each row represents a Virtual Log File (VLF), and you can see if any are waiting to be backed up if they do not have a 2 in the status column. Using DBCC SHRINKFILE() alone, you can only remove the VLFs from the last non-zero status VLF to the end of the file. This is why you were able to shrink the log on a different machine. The last in-use VLF was likely much earlier in the file.
The log file does tend to find an equilibrium, though. The large size may be due to a large update, or a large set of updates that happen between individual log backups, so 16 GB may be a good size for this. In other words, shrinking the log file may only get you some diskspace back for a day, or a week, until the log needs to grow again.
-
Hello Crow1969,
Thanks for taking time to answer me. It is really appreciated. Moreover, I think that you found my issue. I did what you told me and in fact, at the top of the loginfo I see some 2 in Status and the last line is a 2 but in between you have plenty of zeros. I did a printscreen.
This happens on both of my servers. One server has a log of 16g and the other one has a log of 45g. I am now trying to find a way around those status. If you have a solution Crow please could you share it?
Thanks
-
Backing up the log should advance the active VLF to the next entry. If there is no next entry (i.e. you are at the end of the file), then it goes back to the first VLF (usually). If you want to shrink the file, then
run dbcc shrinkfile (this should remove all VLFs beyond the currently active VLF, or to the specified size, whichever is greater)
backup the log (resets the current VLF to earlier in the file)
run dbcc shrinkfile (removes VLFs from the end of the file)
-
Thanks again Crow!
I had to offline the DB and put it back online and then I did what you said. It worked!
Do you have a script that would backup all database and logs daily - and then shrink the logs? I had a look at Ola Hallengren site but it seems to complicated for me.
-
It is easy to setup -- you can follow this post -- https://www.sqlserverblogforum.com/dba/how-to-use-configure-ola-hallengren-sql-server-maintenance-script/
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply