May 1, 2022 at 8:53 am
We are facing an issue with a SQL server 2016 server that is not releasing space to the OS.
When running
dbcc shrinkfile (OurDatabase_log,truncateonly)
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
12 2 1024 1024 1024 1024
DBCC LOGINFO
RecoveryUnitId FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
0 2 2031616 8192 31521 0 64 0
0 2 2031616 2039808 31522 0 64 0
0 2 2031616 4071424 31526 2 64 0
0 2 2285568 6103040 31520 0 128 0
SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'OurDatbase'
NOTHING
However, the OS is showing 1.5TB log file and I am unable to get this reduced. Backups of the database have been taken, recovery mode has been changed from Full to Simple, max file size of the log file has been reduced to 1TB, SQL server has been restarted several times, the machine has been restarted ... I am not sure how to proceed.
May 1, 2022 at 4:51 pm
First - using the truncate only option isn't going to do anything for a transaction log. You need to specify a size - but that isn't going to help either in this situation because the file you are looking at is already as small as it can be made.
It looks like the person that created this database and transaction log - created the initial file at 1.5TB. You now have 4 virtual log files in that transaction log (VLF) sized at 2,031,616. An initial size for a file created at 1MB would have 4 VLF's sized at 262,144 (.25MB) for comparison.
I am not sure that number is correctly reported from DBCC LOGINFO though. Try using the DMV sys.dm_db_log_info to get a better idea of the size of each VLF.
Now - the question is how to fix the problem. I think the best approach would be just to create a new transaction log at 1MB - and manually grow that out in small chunks, say 512MB per growth up to the size needed for normal daily operations. Then you need to wait until the system transitions the log to the new file - and once the new file is being used you can then remove the old file.
Worse case - you may need to detach the database (it must been a clean shutdown), rename the old log - then attach with rebuild log. That should create a new transaction log at the minimal size.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 3, 2022 at 11:56 am
to add:
Shrinkfile: DBCC SHRINKFILE this also explains what's special for log files !
intro VLF: "A Busy/Accidental DBA’s Guide to Managing VLFs"
read: "Important change to VLF creation algorithm in SQL Server 2014"
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 9, 2022 at 1:08 pm
TRUNCATEONLY
Releases all free space at the file's end to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent. target_size is ignored if specified with TRUNCATEONLY.
The TRUNCATEONLY option does not move information in the log, but does remove inactive VLFs from the end of the log file.
You should try shrinking it to the size you want it to be. If your database is in FULL mode run a transaction log backup first to clear out data in the log file.
USE [YourDatabase]
GO
DBCC SHRINKFILE (N'YourDatabase_log' , 102400
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply