IM 01 SQL disk and certificate maintenance

Shrinking transaction logs

1. Check the current file size from the DB

sp_helpdb 'example_db';

2. Check if the DBs are in SIMPLE or FULL recovery mode FULL grows because the transactions don't get purged.

SELECT name AS [Database Name], recovery_model_desc AS [Recovery Model] FROM sys.databases

3. Script to switch to SIMPLE and truncate

use example_db;
go;
alter database example_db set recovery SIMPLE;
go;
DBCC shrinkfile('example_db_Log', 0, TRUNCATEONLY);
go;
sp_helpdb 'example_db';

Checking backup cert expiration

SQL Server makes its own certs to use in backup encryption. Those expire yearly.

jadmin@test:/opt/machete/secrets/sqlserver$ openssl x509 -in sqlserver_backup_cert.cer -inform der -noout -dates
notBefore=Sep 19 21:25:42 2019 GMT
notAfter=Sep 19 21:25:42 2020 GMT