July 27, 2015 at 1:33 pm
I have a database that I am trying to recover space from, it consists mostly of unallocated space, but I can’t seem to get that unused space released.
Database size: 40,245.13 MB
DatafileMB: 38,063.63, DataAvailableMB: 37,085.15
LogfileMB: 2181.51
Sysfiles shows:
fileidgroupidsizemaxsizegrowthstatusperfname
114872144-112820SomeDB
202792332684354561010486420SomeDB_log
The DB is in simple recovery mode. There are no open transactions (used dbcc opentran).
The server is running SQL Server 2014 and the DB is in compatibility mode SQL Server 2008 (100). It was upgraded to 2014 a month or two ago.
I have tried to re-size the log to 100mb, but any way I have tried (none gave errors), the log file remains the same size. I have tied to shrink the log file (through the UI and via DBCC commands) without success; no errors, but also no change in file size.
I have checked Log Reuse Waits, just in case, and as expected it showed “NOTHING” (select log_reuse_wait_desc, name from sys.databases)
I tried running a checkpoint, but that did not allow any resize or shrink to work.
I have tied creating large transactions to move the used point in the log file, in case this was the issue. I did this by creating tables that I drop after large inserts. While it shows me that the log space % used increased, the log file still does not allow the space to be reduced.
The following is what I was using for the transactions to get the log used.
BEGIN TRAN
select a.* into testtable from sysobjects a, sysobjects b, sysobjects c
ROLLBACK TRAN
Each insert creates 93,576,664 rows.
Running dbcc SQLPerf(logspace) :
DBLogSize(MB)LogSpaceUsed(%)Status
SomeDB2181.534.204890
Running dbcc loginfo:
RecoveryUnitIdFileIdFileSizeStartOffsetFSeqNoStatusParityCreateLSN
0211437342728192602640
02114373427211437424645701280
Do I just need to continue running large transactions until the log space used gets high enough to get the “end point” in the log to really move? Is there an easier way to accomplish this (please tell me there is, I have several DBs that have the almost identical problem), what I am using moves the Log Space Percent Used about a percent on each execution.
July 27, 2015 at 2:32 pm
The minimum number of VLFs is 2, which you're already at. That's why you can't shrink the log anymore.
You're essentially at the minimum size of the log given how the log file for that DB was created. There is a hacky way of getting around that, by creating a snapshot and reverting to it, but I highly doubt that 2 GB is that important to reclaim.
From the description of your effort, though, you took the right steps if you really, really needed to shrink it. My first instinct is that reclaiming the space is likely not that important. Unless the log grew from an abnormal workload, it will likely end up growing to that size again (even if it did grow because of an abnormal workload, someone might run something strange again). Once you consider that, I'd think twice before trying so hard to reclaim that little space.
Still, if for some reason you really do need that space, you did things the way you would have to, and checked all the right things.
Cheers!
July 27, 2015 at 2:58 pm
Your Loginfo results are a bit wonky.
It shows that the first vlf is 8kb and the second vlf is ~1090mb. This is not normal.
Second piece of wonky is that the starting offset has the second vlf starting prior to the first vlf.
Is this the complete results for just one database's transaction log?
One thing (and maybe I missed that you tried it already), is to try growing the transaction log by a couple of GB, and then to try and shrink it.
Last piece of wonky is that the total size of these two vlfs does not match the 2gb previously indicated.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 27, 2015 at 3:02 pm
Check the columns...the data is misaligned with the actual values because the header RecoveryUnitID takes up as much space as the first two columns of data.
You just have to manually figure out the columns, and then it all makes sense 🙂
EDIT: Here's how it's supposed to look without the alignment problem:
RecoveryUnitIdFileIdFileSizeStartOffsetFSeqNoStatusParityCreateLSN
0211437342728192602640
02114373427211437424645701280
July 27, 2015 at 3:07 pm
Jacob Wilkins (7/27/2015)
Check the columns...the data is misaligned with the actual values because the header RecoveryUnitID takes up as much space as the first two columns of data.You just have to manually figure out the columns, and then it all makes sense 🙂
D'oh and there it is.
Next question then would be:
What is the size of the transaction log for model? I would bet it is also 2GB and thus the reason for this one being created at 2GB.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 27, 2015 at 3:13 pm
SQLRNNR (7/27/2015)
Your Loginfo results are a bit wonky.It shows that the first vlf is 8kb and the second vlf is ~1090mb. This is not normal.
Second piece of wonky is that the starting offset has the second vlf starting prior to the first vlf.
Is this the complete results for just one database's transaction log?
One thing (and maybe I missed that you tried it already), is to try growing the transaction log by a couple of GB, and then to try and shrink it.
Last piece of wonky is that the total size of these two vlfs does not match the 2gb previously indicated.
Like Jacob mentioned, the minimum number of VLFs is 2. These are created when the database is created. And, VLFs in the same growth of a log are equally sized, which these aren't.
Whoa.. those output columns aren't lined up as pretty as they seem to be... The RecoveryUnitID heading is over two columns. So, they do have the same file size... and they are really huge for being the initial VLFs.
Considering that they come over from model when the database is originally created (verified by the CreateLSN = 0), just what is the size of the model database log file? What does a DBCC LOGINFO on model have to show?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 27, 2015 at 3:35 pm
Also, if it is related to model, the model configuration would have to be even more unusual, since for the initial VLFs to be that size, the database would have to be created with an initial log size of 17(ish) GB. That would be an odd choice for model, to be sure 🙂
I'm hoping the size of the log file was just specified as 17 GB for that DB when it was created, instead of taking the model defaults. Of course, the OP mentioned that he saw the same for several DBs, so that lends a little fuel to the model hypothesis.
EDIT: I only copied half my post over initially. Added the second bit.
July 27, 2015 at 5:09 pm
I think I have an idea of how you got to where you are with these tlogs and vlfs. I have just been able to repro it with the following script.
USE master;
GO
DROP DATABASE VLFtest
GO
CREATE DATABASE [VLFtest]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'VLFtest', FILENAME = N'G:\Database\Data\VLFtest.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'VLFtest_log', FILENAME = N'G:\Database\Log\VLFtest_log.ldf' , SIZE = 18000mb , FILEGROWTH = 1024mb)
GO
USE VLFtest;
GO
DBCC loginfo
DBCC SHRINKFILE(2,EMPTYFILE)
DBCC SHRINKFILE(2,2500)
DBCC SHRINKFILE(2,2048)
CHECKPOINT
USE VLFtest;
GO
DBCC loginfo
Obviously, paths etc would need to change.
But from the looks of it, there is an automated database creation script that creates your databases with very large tlogs. That script then attempts to shrink the logs via the emptyfile and then the standard variants of shrinkfile (successive size shrinks).
Once, that was done, I was down to a tlog with just 2 vlfs that were roughly the same size you had.
From here, I am no longer able to shrink the tlog.
I tried reproducing this with the model database hypothesis but I could not.
Edit: I will post the code sample later. It is blocked from posting at the moment.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 27, 2015 at 8:12 pm
Thank you all for explaining the parts I was missing. I had been very worried that there was something wrong/corrupt with these DB's. Finding out it was more about the distant past & how they were created has made me feel much better!
I am cleaning up after something that happened in the past (predates my involvement, and the client had a lot of turnover in quasi-DBA's... so it can be a bit of an interesting environment). I am also not a DBA, I do extensive DB development, so I have had to reach into the DBA world, but it is not my comfort zone.
The model on this server has a 7mb log file, so that isn't, at least now, the source of the log file size. There are 5 DB's that I suspect all come from the same original DB (it is possible the original is one of the 5 that I see today). All claim very close to the exact amount of space in total (about 40gb), some use less than 20% of that space, and all have exactly the same size log file. There is somewhere around 100gb that could potentially be reclaimed if the DB's get down to a reasonable space use for what they actually hold.
As some pointed out, 2gb isn't that big a deal in the grand scheme of things. Even across the 5 DB's, cutting most of them down to the 100mb to 500mb size isn't going to make a huge difference. There is only one DB that might be large enough to end up growing the log file to even 1gb (I'd be very surprised if it ever needed more than that).
That all makes the log file issue more of a curiosity for me. Is there a way to get rid of these log files and create new ones to replace them (new & of a reasonable size for each DB)?
July 27, 2015 at 8:15 pm
Forgot to mention that yes, I did try increasing the size of the log file. After that, a shrink will return it to the starting roughly 2gb.
July 27, 2015 at 9:50 pm
The only way I know is the one I mentioned before, creating a snapshot and reverting to it.
That will rebuild the log file as a 500 KB file. It's probably an unintended "feature" (i.e., bug), but that will start you with a new log with two tiny VLFs.
You'd want to make sure this won't cause any problems, by ensuring no activity between creating the snapshot and the revert, and checking for full-text catalogs (reverting drops those). If any of those databases are using full recovery, it also breaks the log chain.
I wouldn't bother, but that's how it could be done in a pinch.
Cheers!
July 28, 2015 at 12:02 am
I like the snapshot method for Enterprise edition.
An alternative would be to detach and reattach during a maintenance window. The caveat here is to ensure a backup is performed first, and to attach the database forcing a rebuild of the log. Again, these are extremes for small gains.
Based off the prior example, this would work to recreate two small vlfs. Then the log can be grown to an appropriate size.
USE master;
GO
EXEC sp_detach_db 'VLFtest'
GO
/* move or rename the log file */
CREATE DATABASE [VLFtest] ON (FILENAME = 'G:\Database\Data\VLFtest.mdf') FOR ATTACH_REBUILD_LOG
GO
USE VLFtest;
GO
DBCC loginfo
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply