SQL Server log file shrink

So, lots of versions of the database on the same dev server - multiple development teams running their own instance of the same schema.  This script will delete the contents of all the log files to clear some disc.


USE master

DECLARE db_cursor CURSOR FOR 
SELECT d.name FROM sysaltfiles f 
INNER JOIN sysdatabases d ON (f.dbid = d.dbid)
WHERE d.name NOT IN('master','model','msdb','tempdb')
AND f.filename LIKE '%.ldf' AND f.name = 'xxx_Log'

DECLARE @dbName VARCHAR(70)

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @dbName

WHILE @@FETCH_STATUS = 0  
BEGIN  
DECLARE @shrinkStatement VARCHAR(400)
SELECT @shrinkStatement = 'USE master; ALTER DATABASE ['+CONVERT(VARCHAR(70), @dbName)+'] SET RECOVERY SIMPLE; USE '+CONVERT(VARCHAR(70), @dbName)+'; DBCC SHRINKFILE (xxx_Log, 7); ALTER DATABASE ['+CONVERT(VARCHAR(70), @dbName)+'] SET RECOVERY FULL;';
EXEC (@shrinkStatement)

FETCH NEXT FROM db_cursor INTO @dbName  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

Database files

If you're short of space on your SQL Server hard disk then it's useful to know which database are eating this space.  This SQL will show the physical filenames of the database, their filename and their physical size on the disc:

use master
select d.name, f.filename, f.size 
from sysaltfiles f 
inner join sysdatabases d
on (f.dbid = d.dbid)
where d.name not in ('master','model','msdb','tempdb')
order by 3 desc,1,2