Archive for the ‘SQL Server’ Category

Bulk change recovery model on all databases

August 12, 2013 2 comments

I don’t know where I found this script, but I’m using it every day (well, weeks may be more accurate) on Test environments to change all the Database to simple recovery model and save lot of disk space.

use [master]
-- Declare container variabels for each column we select in the cursor
declare @databaseName nvarchar(128)
 -- Define the cursor name
declare databaseCursor cursor
 -- Define the dataset to loop
select [name] from sys.databases where name not in ('Master','tempdb','model','msdb')
 -- Start loop
open databaseCursor
 -- Get information from the first row
fetch next from databaseCursor into @databaseName
--  Loop until there are no more rows
while @@fetch_status = 0
  print 'Setting recovery model to Simple for database [' + @databaseName + ']'
  exec('alter database [' + @databaseName + '] set recovery Simple')
  print 'Shrinking logfile for database [' + @databaseName + ']'
  exec(' use [' + @databaseName + '];' +'  
     declare @logfileName nvarchar(128);
    set @logfileName = (
        select top 1 [name] from sys.database_files where [type] = 1
    dbcc shrinkfile(@logfileName,1);
    -- Get information from next row
    fetch next from databaseCursor into @databaseName 
 --  End loop and clean up
close databaseCursor
deallocate databaseCursor 

All you have to do, is to copy/paste this script in a SQL Server Management Studio SQL Query window. It works on numerous SQL version (I used it on SQL 2005 ->2012) no matter the system language…

Categories: SQL Server Tags:

SQL Server 2008 /2008R2 : Shrink LDF Files

April 10, 2012 2 comments

This is a common issue, by design, LDF files associated to databases are not shrink, and size may became huge. Sometime, you have to manually shrink them. Here is how to do:

First : DO NOT CHANGE recovery mode to simple, then turning back to “Full”. This is a heavy operation that under (pretty rare indeed) circumstances may corrupt some data.

Instead, do the following:

  1. Backup transactional log via SQL Server Manager (Right Click>Tasks>Backup> choose backup Type: Transaction Logs)

This will, backup and flush the LDf file, now you have to do a checkpoint. This will move the active section of log to move at the beginning of it.

  1. To achieve this Checkout, start a “new Query” windows after selecting database, and simply type “Checkpoint
  2. Now, backup transaction Log again (you can use same file as the first backup)
  3. Shrink file using right click>Tasks>Shrink>File and specify Log as file Type and hit Release unused space radio button

Click OK, the file is now shrinked 😉

Thanks to Christian Robert :

What about the simple recovery mode?

When you perform a SharePoint Backup, SQL is making full backup. That kind of backup do not shrink LDF file, and SharePoint native backup do not handle transaction log. So, if you are only using Sharepoint tools (backup-SPSite/Backup-SPFarm cmdlet or Central Administration) to bakup youre far, you can safely turn youre database to a simple recovery mode. But if you have a more powerful backup solution (such as Microsoft Data Protection Manager) please consider Full recovery mode and en maintenance plan that will periodically perform transaction log backup.

More Info:

Article URL
Checkpoints and the Active Portion of the Log
Plan for backup and recovery (SharePoint Server 2010)
SQL Server Recovery Model Overview
Categories: SQL Server