You are currently browsing the Blog weblog archives for August, 2008.
| S | M | T | W | T | F | S |
|---|---|---|---|---|---|---|
| « Jun | Oct » | |||||
| 1 | 2 | |||||
| 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| 10 | 11 | 12 | 13 | 14 | 15 | 16 |
| 17 | 18 | 19 | 20 | 21 | 22 | 23 |
| 24 | 25 | 26 | 27 | 28 | 29 | 30 |
| 31 | ||||||
- IIS (1)
- Open Source (1)
- Performance (4)
- Personal (3)
- Powershell (1)
- SQL (1)
- SQL Server (21)
- T-SQL (15)
- Uncategorized (6)
- Utilities (5)
- Windows OS (14)
- 14. April 2011: Deduplicating files with LogParser and SQL Server
- 25. February 2011: The final voyage of the USNS H. H. Hess
- 16. February 2011: Free SQL Server training videos
- 23. August 2010: Alert for long-running SQL datbase backups
- 7. April 2010: Learning SMO & Powershell
- 25. February 2010: SQL Generators for moving database files
- 28. January 2010: Index to Filegroup mapping
- 20. January 2010: PowerShell Script to Clean Up Old Files Based on Age
- 7. January 2010: Quick & Dirty way to identify orphan files
- 29. July 2009: Trigger Mass Enable / Disable
- April 2011
- February 2011
- August 2010
- April 2010
- February 2010
- January 2010
- July 2009
- June 2009
- February 2009
- January 2009
- December 2008
- November 2008
- October 2008
- August 2008
- June 2008
- May 2008
- April 2008
- March 2008
- February 2008
- January 2008
- December 2007
- October 2007
- September 2007
- May 2007
- April 2007
- February 2007
Archive for August 2008
Shrinking all log files
18. August 2008 by Bennett.
I’m not a big proponent of shrinking files. There are a lot of postings on DBA forums citing why this practice should be avoided. However we have a development server with a lot of databases, and the log files need to be shrunk on a pretty regular basis.
I wrote a little query to dynamically build the list of databases and logical filenames and then run DBCC SHRINKFILE to shrink all the log files. Here’s the script:
SET nocount ON;
CREATE TABLE #tempFileInfo
(
dbName VARCHAR(256),
logicalName VARCHAR(256),
)
DECLARE @DBName VARCHAR(256)
DECLARE @SQL VARCHAR(MAX)
DECLARE CursorDB CURSOR FOR
SELECT [name] FROM sys.databases WHERE database_id > 4 AND state_desc = ‘ONLINE’
OPEN CursorDB
FETCH NEXT FROM CursorDB INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @DBName = ‘[’ + @DBName + ‘]’
SET @SQL = ’select ‘ + CHAR(39) + @DBName + CHAR(39) + ‘, [name] from ‘ + @DBName + ‘.sys.sysfiles WHERE filename LIKE ‘+ CHAR(39) + ‘%ldf’ +CHAR(39)
–print @SQL
INSERT INTO #tempFileInfo
EXEC (@SQL)
FETCH NEXT FROM CursorDB INTO @DBName
END
CLOSE CursorDB
DEALLOCATE CursorDB
SET @SQL = ”
SELECT @SQL=COALESCE(@SQL,”)+ ‘USE ‘ + dbName + ‘ DBCC SHRINKFILE(’ + CHAR(39) + logicalName + CHAR(39) + ‘, 1); ‘
FROM #tempFileInfo
PRINT @SQL
EXEC(@SQL)
DROP TABLE #tempFileInfo
Posted in T-SQL, SQL Server | Print | No Comments »