And, now it makes sense...

I had another email from Lightspeed, in response to my request to know why my databases were shrinking even though autoshrink = false, and this got to the root of the matter.

Previous versions of Total Traffic Control could be run on SQL Express, which has a 4GB database size limit: so, TTC has a subroutine that automatically shrinks databases, in order to get round this issue.

Now, however, they don't support running the system on SQL Express, so this code is redundant. However, it's not yet been removed. They're working on that: it'll have to be thoroughly tested first, so it won't be instant.

I'm very glad I asked: and I'm very pleased to have had such a good reply!

A response from Lightspeed

Remember this post? Well, I emailed Lightspeed. Their response?

"By default, the database is set to autogrow and autoshrink to save space."

In the last week, I have had the following.

Initial size 5430MB
Manually grown database to 8000MB
Autogrow to 8800MB
Autoshrink to 5430MB
Autogrow to 5900MB

There is plenty of space on the drive on which the database files reside (C:\, because Lightspeed recommend that SQL be installed on the C:\drive running under the local system account).

Moreover, autoshrink is set to false for every single database on the server, so I don't know what exactly is causing this shrinkage, but it ain't SQL. In fact, I would posit that it is completely inaccurate to say that the databases are autoshrinking, as this shrinkage does not show up in the 'Data/Log files Autogrow/Autoshrink' events part of the Disk Usage reports for the databases. It just happens, it doesn't show up in the logs, and it is really rather annoying me.

When the autoshrink/autogrow won't show in SQL Reports

...Try the script below, from this discussion on MSDN. It worked beautifully for me when it turned out that there were well over a million rows of autogrow/autoshrink data, and thus the autogrow/autoshrink data from SQL 2008's built in reports wasn't going to show correctly.

I then changed the data file size, so that the thing grew in one BIG chunk, gave it lots of elbow room for more data to fill up the file, and left it well alone.

DECLARE @filename VARCHAR(100), @filenum int

SELECT @filename = CAST(value AS VARCHAR(100))

FROM fn_trace_getinfo(DEFAULT)

WHERE property = 2

  AND traceid = 1

  AND value IS NOT NULL



-- Go back 4 files since default trace only keeps the last 5 and start from there.

SELECT @filename = substring(@filename, 0, charindex('_', @filename)+1) + convert(varchar, (convert(int, substring(left(@filename, len(@filename)-4), charindex('_', @filename)+1, len(@filename)))-4)) + '.trc'



--Check if you have any events

SELECT gt.HostName,

       gt.ApplicationName,

       gt.NTUserName,

       gt.NTDomainName,

       gt.LoginName,

       gt.SPID,

       gt.EventClass,

       te.Name AS EventName,

       gt.EventSubClass,

       gt.TEXTData,

       gt.StartTime,

       gt.EndTime,

       gt.ObjectName,

       gt.DatabaseName,

       gt.FileName

FROM [fn_trace_gettable](@filename, DEFAULT) gt

JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id

WHERE EventClass in (92, 93, 94, 95)

and gt.DatabaseName = 'tempdb'  --Change the DB Name here

ORDER BY StartTime;

3rd Party Software Again...

This time, Lightspeed System's TTC seems to be the culprit. It has a nifty little system that sets autoshrink = true for all its databases, and then it gets terribly upset if the database file fills up. Of course, if it wasn't autoshrinking, then it wouldn't need to autogrow, and it wouldn't get its knickers in a twist because it couldn't autogrow on the terms of SQL's default, which is an autogrow of 10%.

When autoshrink = true, SQL will try to shrink all database files with more than 25% free space. This creates a performance hit for the CPU: the default setting for SQL is to set autoshrink = false (except on the Desktop Edition, where the default setting for SQL is autoshrink = true).

When autogrow = true, SQL will try to grow the database file while running an INSERT. The query may well time out, the autogrow will fail, and the database file will fill up. The default for SQL is to set autogrow = true, with a growth of 10%.

Both autogrow and autoshrink cause problems with file fragmentation and index fragmentation. It is vastly more healthy to set the initial database file size to the anticipated database file size, and then leave it well alone. There will be less load on the CPU, neither files nor indexes nor pages will fragment and the server will run more efficiently. It's like the difference between ironing a shirt on an ironing board, unimpeded, and ironing a shirt on a folded up towel on one corner of your work desk. Yes, it can be done, but no, we really don't want to as it takes longer and is vastly more exhausting.

Why Lightspeed Systems seems to think that Total Traffic Control's databases should be set to autoshrink is something I haven't yet established. My Google-fu is not strong today, so if you do know the answer, I would love to hear it.