Saturday, July 11, 2009

sahrink-files script

USE MASTER GO CREATE TABLE #TMPFIXEDDRIVES ( DRIVE CHAR(1), MBFREE INT) INSERT INTO #TMPFIXEDDRIVES EXEC xp_FIXEDDRIVES CREATE TABLE #TMPSPACEUSED ( DBNAME VARCHAR(50), FILENME VARCHAR(50), SPACEUSED FLOAT) INSERT INTO #TMPSPACEUSED EXEC( 'sp_msforeachdb''use ?; Select ''''?'''' DBName, Name FileNme, fileproperty(Name,''''SpaceUsed'''') SpaceUsed from sysfiles''') SELECT C.DRIVE, CASE WHEN (C.MBFREE) > 1000 THEN CAST(CAST(((C.MBFREE) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB' ELSE CAST(CAST((C.MBFREE) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB' END AS DISKSPACEFREE, A.NAME AS DATABASENAME, B.NAME AS FILENAME, CASE B.TYPE WHEN 0 THEN 'DATA' ELSE TYPE_DESC END AS FILETYPE, CASE WHEN (B.SIZE * 8 / 1024.0) > 1000 THEN CAST(CAST(((B.SIZE * 8 / 1024) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB' ELSE CAST(CAST((B.SIZE * 8 / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB' END AS FILESIZE, CAST((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) AS DECIMAL(15,2)) SPACEFREE, B.PHYSICAL_NAME FROM SYS.DATABASES A JOIN SYS.MASTER_FILES B ON A.DATABASE_ID = B.DATABASE_ID JOIN #TMPFIXEDDRIVES C ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE JOIN #TMPSPACEUSED D ON A.NAME = D.DBNAME AND B.NAME = D.FILENME ORDER BY DISKSPACEFREE, SPACEFREE DESC DROP TABLE #TMPFIXEDDRIVES DROP TABLE #TMPSPACEUSED
After copying and pasting the above into a SQL Server 2005 query window and running all of the code the result set will look something like this:
Next Steps
With a little modification, you can schedule the script to shrink data files containing over X amount of free space on a drive with less than X amount of disk space.
In a perfect world, DBA’s would just be able to ask for more drive space and not have to worry about shrinking files. It is more efficient to give the database the room it needs instead of having to shrink them all the time. Until then, I guess I will keep using this script.Readers Who Read This Tip Also Read
How to setup SQL Server alerts and email operator notifications
Methods to determine the status of a SQL Server database
Reading the SQL Server log files using T-SQL
Collecting SQL Server database usage information for free space and trending
Listing SQL Server Object Dependencies
More... Comment or Ask Questions About This Tip
Discuss this tip: http://blogs.mssqltips.com/forums/t/628.aspx
There are 1 comments for this tip, last post by tosscrosby

No comments:

Post a Comment