The Multifunctioning DBA

Jul 30 2010   10:00PM GMT

SQL Server Test Instances



Posted by: Colin Smith
Restore, SQL Server

Lately I have had a number of test databases where the log file has just gotten way out of control and I have been close to running out of disk space. This is a problem and it is caused by someone, including me, restoring a database from production to the test instance. If the database in production uses the Full Recovery Model, then the restored database on the test instance will also be using that recovery model. Since we do not do any tran log backups on test systems, and rarely do full backups either, the log will just continue to grow. To fix the issue I always go in and change the db to the simple recovery model and then I will shrink the log file so that I can reclaim my disk. In my monitoring script that I am writing I will be adding some code in to change the recovery model to simple if it is using any other model and the instance is marked as test or dev. This should prevent me from running into this problem when databases are restored to test.

Comment on this Post

Leave a comment: