I often find in my sharepoint development environments that I have issues with transaction logs filling up. This gives me the "Error" page with no indication of what the problem is...yes I can go and change the <customErrors> tag to set the customer errors mode to off and add the callstack to the page but it still doesn't address the problem. I whipped this script up because the dev sql server we are using has well over a hundred db's on it and I didn't fell like manually typing the ALTER DATABASE [xxx] SET RECOVERY SIMPLE for each one or doing the clickity click thing either. Now the solution below probably breaks lots of best practices and such but it works for what I wanted it to do....which is modify all existing db's recovery mode. To modify new db's recovery mode, just set the model db's option to simple and voila..you are ready to go.
Quick note: YOU PROBABLY DO NOT WANT TO EVER...I MEAN EVER...USE THIS ON A PRODUCTION SYSTEM.
*/
Modify th recovery model for all databases on your server
*/
DECLARE @lasterror int
DECLARE @sql nvarchar(max)
DECLARE @dbname nvarchar(100)
DECLARE @dbid int
DECLARE @recModel int
-- cycle the log
exec sp_cycle_errorlog
CREATE Table #dbInfo
(
database_id int NOT NULL,
dbName nvarchar(100) NOT NULL,
recoveryModel int
)
--SELECT database_id, [name], recovery_model FROM sys.databases order by database_id
SELECT TOP 1 @dbid = database_id FROM sys.databases where database_id > 4 ORDER BY database_id asc;
SELECT @dbname = [name] FROM sys.databases where database_id = @dbid;
SELECT @recModel = recovery_model FROM sys.databases where database_id = @dbid;
-- begin backup process
WHILE @dbid > 0
BEGIN
INSERT INTO #dbInfo (database_id, dbName, recoveryModel) VALUES (@dbid, @dbname, @recModel);
IF @recModel = 1
BEGIN
SELECT @sql = 'ALTER DATABASE [' + @dbname + '] SET RECOVERY SIMPLE'
EXEC sp_executesql @sql
SELECT @lasterror = @@ERROR
IF @lasterror <> 0
-- log the error to the message console
PRINT 'ERROR: Changing the recovery mode for database : ' + @dbname + ' with error number ' + CAST(@lasterror as nvarchar(25))
ELSE
PRINT 'Recovery mode changed to simple (3) for database : ' + @dbname
END
-- get a new record
SELECT @dbid = 0;
SELECT TOP 1 @dbid = database_id FROM sys.databases where database_id > 4 and
database_id NOT IN (SELECT DISTINCT x.database_id FROM #dbInfo x);
SELECT @dbname = [name] FROM sys.databases where database_id = @dbid;
SELECT @recModel = recovery_model FROM sys.databases where database_id = @dbid;
END
DROP Table #dbInfo
SELECT database_id, [name], recovery_model FROM sys.databases order by database_id
GO
Comments