• Repost - originally posted 21-06-2012

Full recovery = transaction log backups

A common occurrence in reviewing SQL Server implementations is finding very large databases that have log file sizes greater than the size of the database file. When you look at how much space is actually being used - you see that the transaction log usage is very large. You might have a database with 10GB of actual data (and indexes), the data file might be sized at 15GB, but the transaction log is 22GB (and growing). This can cause issues with disk space filling up and degraded performance. You can check the database recovery model by querying the sys.databases catalog view. The recovery model of the database dictates your point in time recovery. Full recovery gives you the ability to recover to a point in time. Simple recovery means you can recover from the last full backup. If you don't need point in time recovery then change the recovery to simple and then you can shrink the transaction log file back to reclaim the space. This is one of the rare times that it may be OK to shrink a database file. If the database is in full recovery then you should be doing regular transaction log backups. You can either set these up using the maintenance stored procedures from Ola Hallengren, the database maintenance wizard, stored procedures or T-SQL and having a scheduled job run the transaction log backups. This will keep your transaction log file small and give you the ability to recover to a point in time.

5 views0 comments

Recent Posts

See All

Standardise database sizes and automation

The default setting for the initial sizing of data and log files (and their autogrowth) is not adequate. I like to make sure that we set standard sizes for our data and log files - plus standard sizes

Migrating SSIS packages to AWS

We are currently migrating a large amount of SSIS packages (SQL Server 2008R2) to SQL Server 2016 at AWS. It has been a learning exercise to get these packages running. What we have learnt (so far) is

Autoclose and autoshrink - set them to off

By running a simple check of your database settings (use helpdb or query sys.databases) you may find databases that are set to autoclose and/or autoshrink. If so, you should change these settings as p