Shrinking database log files in an
AlwaysOn Availability Group environment
If you're slacking and not backing up your transaction logs for your
synchronized AlwaysOn databases, you may be running out of disk space from
built up logs. In fact, you're probably here because you received an alert
about your SQL server data disks filling up and are in a slight panic. OK, I
get it, let's get on with it.
This is more complicated than shrinking logs in a non-HA instance, pay attention:
This is more complicated than shrinking logs in a non-HA instance, pay attention:
1.
Login to your database server and
open a New Query window
2.
Execute the backup command to backup
the transaction log to a null location:
BACKUP LOG databaseName TO DISK='NUL:' WITH NO_CHECKSUM, CONTINUE_AFTER_ERROR
BACKUP LOG databaseName TO DISK='NUL:' WITH NO_CHECKSUM, CONTINUE_AFTER_ERROR
o
NUL is used so it will not actually
write the backup to the disk- you won't lose precious disk space.
o
This may take a bit depending on the
size of the log. It will also grow the log file slightly.
o
We use CONTINUE_AFTER_ERROR because
we don't care if the log is corrupt. It's going to be deleted soon anyway.
3.
Execute the shrink command and take a
look at the results:
DBCC SHRINKFILE (databaseName_log, EMPTYFILE);
DBCC SHRINKFILE (databaseName_log, EMPTYFILE);
o
This command attempts to shrink the
log file to nothing (empty it).
o
Look at the results, you will see
column CurrentSize. If the column has a large number, it probably
didn't shrink much. Check the screenshot at the bottom for an example.
o
Do not expect CurrentSize to drop all
the way to 0 unless the database is pretty docile
4.
Execute all 3 commands in this order,
in the same query window, until you notice the CurrentPages dropping
in the results.
BACKUP LOG databaseName TO DISK='NUL:' WITH NO_CHECKSUM, CONTINUE_AFTER_ERROR
Use databaseName
dbcc loginfo
DBCC SHRINKFILE (databaseName_log, EMPTYFILE);
BACKUP LOG databaseName TO DISK='NUL:' WITH NO_CHECKSUM, CONTINUE_AFTER_ERROR
Use databaseName
dbcc loginfo
DBCC SHRINKFILE (databaseName_log, EMPTYFILE);
o
The backup will run quicker now that
you have already taken the original 'big' backup
o
The more the database is in use, the
more you will have to execute it- the transaction log is always changing
5.
That's it, check your log file size. Screenshot example below
This example was on a SharePoint config log file that was 80GB!
