0

This is an entirely new install of SQL server of an entirely new server. The setup is intended to replace another ageing server and we are in the process of setting up the server environment.

Last Thursday we installed SQL Server and restored the clients database, but there are no [nil, none, not any, zero] client users, management jobs, or scheduled processes configured. Effectively the system is isolated from the world living in its own universe.

On Monday I got a shout from the systems team telling me an out-of-space alert had been raised from the transaction log drive and that it was nearing the top of its 80Gb capacity.

80Gb of transaction log seems like a lot for an untouched SQL database. In a live environment we will have management jobs running to backup the trans log every 15 mins so we will not see the issue.

Obviously something is running to cause SQL to write so much to the trans log. I am curious as to what it might be. Apart from anything else I always assumed that it is user activity and occasional DBA work that causes the translog to grow but it would seem that some part of the growth is due to something else.

Can anyone offer any insight, or suggest how I can analyse the transaction log contents to get a sense of what is happening?

UPDATE: Couple of updates from when I first wrote the question.

  1. Why can't I examine the translog file - it had to be killed to get the space back - not my decision.

  2. There is a VEEEM backup solution in play here. It turns out that the login credentials for VEEEM to access SQL server were not enabled with SQL Server access. The event log shows the VEEEM attempting to log in 4 times them abandoning. The disk space usage graph shows an almost exponential consumption of disk space.

1

Sounds like there may be a maintenance job running intermittently in the background which is filling your log. Do you have access to the job system to look? On non-production systems you'll also want to put the database in simple recovery mode in most circumstances.

  • Thanks - yes its mine to play with and no there are no maint jobs except the failing VEEEM login that wants to truncate the trans log but can't. Really, I had a colleague follow me through to confirm there are no maint jobs - nada - and no SQL Agent jobs either, and no Windows scheduler jobs either. This is my first time out with SQL 2016 and I wonder if there is any other auto process MS added, but I can't find it if there is. – Vanquished Wombat Feb 15 '17 at 12:51
  • 1
    The log sizes you were describing sound like what we typically generate during index rebuilds. Which is why I thought there might be a production index job. If there are no jobs perhaps this behavior is specific to SQL 2016. It doesn't sound right. – Matthew Sontum Feb 15 '17 at 17:38
  • Agree it sounds odd. And I was with you on thinking something must be running that I was not aware of. But this is a new SQL Server installation with only one database and that is a straight recover-to-database from a backup from the system that is moving to this new machine. The app is a web system and there are no open IIS sites atm. And no management jobs for index rebuilds etc which are part of the usual admin we will be setting up but not actually set up yet. I hoped someone else might have had the same experience and be able to explain it. I guess we will see. Thanks for your thoughts. – Vanquished Wombat Feb 15 '17 at 21:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.