Troubleshooting SQL Server log file size with AlwaysOn - Shrinking the logs
The SQL Server logs filled up my hard drive and the database stopped working! Crap!
So some backups were missed and now the log file has filled up the disk. Here is how to shrink the logs.
It's panic time, so lets get some triage done.
Is this on a Virtual Machine?
Can you expand the disk size to get your database running again while you figure out the rest? You might need to go that route if everything has ground to a halt.
Backup the database and logs, shrink the log file
If you don't have your backup priority set for the primary replica/server, do that first.
1. Backup the database on the primary server
2. Backup the transaction logs on the primary. This will truncate the logs on the primary and all secondary servers
3. Right click on the database to go "Tasks" -> "Shrink" -> "Files"
4. From the "File Type" menu, select "Log" and press OK
Only shrink the logs, you can't shrink the database while your connected to a AlwaysOn Availablity group.
Check to see how much free space is available now in the logs with this command:
DBCC SQLPERF(LOGSPACE)
Hopefully your using less than 5% now.
Now that you fixed that problem, check your backups and figure out why the transaction logs are not getting backed up (truncated).
My Initial Database Log size is too large and SQL will not let me lower the size
If you need to lower the initial log file size (if the shrink was not enough and the log is still big) you will need to remove it from the Availabilty group and take these steps.
This will need to be done during a maintenance window since your going to break AlwaysOn for a while.
1. Create a full backup of the database
2. Go to the database properties (right click on the DB -> properties), under the "options" section, change the database recovery model to "Simple" then press OK
3. Right click on the database to go "Tasks" -> "Shrink" -> "Files"
4. From the "File Type" menu, select "Log" and press OK
5. Go back to database properties -> options and change the recovery model back to "Full" (do not press OK)
6. Click on the "Files" section of the properties window and change the initial size and growth rate of the log file. then press OK
7. The log file is now a reasonable size
8. Join the database back to the AlwaysOn availablity group.
So some backups were missed and now the log file has filled up the disk. Here is how to shrink the logs.
It's panic time, so lets get some triage done.
Is this on a Virtual Machine?
Can you expand the disk size to get your database running again while you figure out the rest? You might need to go that route if everything has ground to a halt.
Backup the database and logs, shrink the log file
If you don't have your backup priority set for the primary replica/server, do that first.
1. Backup the database on the primary server
2. Backup the transaction logs on the primary. This will truncate the logs on the primary and all secondary servers
3. Right click on the database to go "Tasks" -> "Shrink" -> "Files"
4. From the "File Type" menu, select "Log" and press OK
Only shrink the logs, you can't shrink the database while your connected to a AlwaysOn Availablity group.
Check to see how much free space is available now in the logs with this command:
DBCC SQLPERF(LOGSPACE)
Hopefully your using less than 5% now.
Now that you fixed that problem, check your backups and figure out why the transaction logs are not getting backed up (truncated).
My Initial Database Log size is too large and SQL will not let me lower the size
If you need to lower the initial log file size (if the shrink was not enough and the log is still big) you will need to remove it from the Availabilty group and take these steps.
This will need to be done during a maintenance window since your going to break AlwaysOn for a while.
1. Create a full backup of the database
2. Go to the database properties (right click on the DB -> properties), under the "options" section, change the database recovery model to "Simple" then press OK
3. Right click on the database to go "Tasks" -> "Shrink" -> "Files"
4. From the "File Type" menu, select "Log" and press OK
5. Go back to database properties -> options and change the recovery model back to "Full" (do not press OK)
6. Click on the "Files" section of the properties window and change the initial size and growth rate of the log file. then press OK
7. The log file is now a reasonable size
8. Join the database back to the AlwaysOn availablity group.
Comments