This is one of those posts that is probably only useful for me. At work we have some VMs set up for general purpose development. Each dev has one assigned to them except mine is no longer mine. Hazards of leaving a job I suppose. So on my return, I've been camping out on other, less used VMs when I need to have a long running process. One of the things I notice though is that these boxes are nearly out of space.
Why? Because databases are all in FULL recovery mode. This setting makes sense in production environments but as no one is taking backups in this dev environment, this setting causes the database log files to grow and grow and grow and poof, the machines are out of disk space.
The cure, the following query will generate a row for every database in the instance in full recovery mode. I then copy and paste the results back into SSMS/sqlcmd and voila, things are fixed.
SELECT
'ALTER DATABASE ' + quotename(DB.name) + ' SET RECOVERY SIMPLE WITH NO_WAIT' AS cmd
FROM
sys.databases DB
WHERE
DB.recovery_model_desc = 'full';
I then shrink the log but that's ok because it never should have grown that large.
1 comment:
I worked in a place where the production databases were all full without tlog backups...luckily they had not had a lot of data volume so no real problems arose before I noticed it...
One of our standard build procedures for all new SQL servers but particularly dev or test environment servers, is to immediately switch the recovery model of the model database to simple. It defaults to full, and I would rather someone make the conscious decision that they need full recovery model (and therefore know also to instigate tlog backups) than, what is more often the case, they just create a bunch of dbs with no thought to recovery model or backups and the transaction log goes absolutely insane over time...
Post a Comment