To start off, I am a Software Engineer - nowhere near a DBA. I can install SQL Server and setup a simple SQL Server database, which I have done. I setup a simple SQL Server 2016 database on a client's web server (Windows 2012 R2) to store some data for a new website being deployed on the server. It started off as a very small database with a few tables. It has quickly grown to have a few dozen tables, several of which have thousands of records. The database is basically being used to import data from an external API and store it locally so that it can be accessed frequently without performance issues. All of the data imports are up an running without issue. However, I have noticed CPU spikes on SQL Server. More importantly, the log file for the database is up to 33GB. I am very afraid this log file is going to end up using all of the available disk space and crash the server. I need to know what I can do in the short-term (until we get a real DBA in here) to prevent this from happening.

  • 1
    Are you backing up the database and transaction log? What are your recovery needs? One of the simplest solutions would be to put the database into Simple Recovery mode and let SQL Server manage the log file automatically. – joeqwerty Apr 16 '19 at 2:18
  • I came here to say the exact same thing. Simple Recovery mode for that database might be the best fast thing you can do. Other notes - head to brentozar.com and get the first responder kit, especially the Blitz system assessment tool. Also - what other concerns do you have with your server? It might be that I can put together an answer that covers your for a large portion of what you need. – Rob Pearson Apr 16 '19 at 2:24
  • @joeqwerty There are currently no backups being done besides an occasional manual backup of the database to copy to a test server. Adding backups is on my to-do list. Recovery needs are...minimal. Outside of the imported API data, there is not a large amount of data, and the API data can always be re-imported. I had read about Simple Recovery model. What are the drawbacks of Simple Recovery model? At what point after changing to Simple Recovery model will the log file "shrink", or will I need to take further action to shrink it? – Eric Belair Apr 16 '19 at 12:37
  • The main drawback of the Simple Recovery model is the fact you lose point in time recovery. You will only ever be able to recover from the last FULL and/or DIFF back up. Once the database's recovery model is changed to simple, you should be able to freely shrink the transaction log. This action breaks the log chain, so, if you go back to FULL, ever, a new FULL backup will need to be taken to re-establish the log chain. – rvsc48 Apr 16 '19 at 17:09
  • 1
    Best practice is to perform the type of backup (Full, Log, etc.) at a frequency (hourly, daily, weekly) that meets your RPO and RTO. We can't answer that for you. – joeqwerty Apr 17 '19 at 2:07

This is echoing what is already said but the first thing I would do is manually run a backup and see what effect that has on the USED space for the file. You may still need to shrink the file to technically free up the space (sorry Brent). Other than that setting a regular backup in SQL to a network share somewhere would keep it from happening again and you could say you "solved the issue AND started backing up the DB. Then ask for a raise.

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.