28. January 2010 by Bennett.

Here is a trivial script to show where a particular index resides. It saves clicking around the SSMS GUI.


SELECT i.name, i.type_desc, i. is_primary_key, i.is_unique, s.name AS [Filegroup]
FROM sys.indexes i
INNER JOIN sys.data_spaces s
ON i.data_space_id = s.data_space_id
WHERE i.name IS NOT NULL
AND i.name NOT IN (‘clust’, ‘clst’, ‘nc1′, ‘nc2′, ‘nc3′, ‘nc’, ‘cl’)
ORDER BY s.data_space_id, i.name

Posted in T-SQL, SQL Server | Print | No Comments »

20. January 2010 by admin.

Here’s an extremely concise PowerShell script to remove old files.  You could use it to enforce a policy that keeps 90 days of ETL input files on a server.  Files older than that would purged with this script.  The script would be invoked by a SQLAgent CMDExec task or a Windows Task Scheduler job.

#Powershell Script to delete files older than a certain age

$intFileAge = 90  #age of files in days

$strFilePath = “c:\archive” #path to clean up

#create filter to exclude folders and files newer than specified age

Filter Select-FileAge 

      {

      param($days)

      If ($_.PSisContainer) {}

              # Exclude folders from result set

      ElseIf ($_.LastWriteTime -lt (Get-Date).AddDays($days * -1))

            {$_}

      }

      get-Childitem -recurse $strFilePath | Select-FileAge $intFileAge ‘CreationTime’ | remove-item 

Posted in Utilities, Windows OS | Print | 3 Comments »

7. January 2010 by admin.

Here is a quick and dirty script to identify orphan database files - that is, files that are no longer in the system catalog. It uses several undocumented stored procedures and is not particularly clean SQL, but it works.

To do: come up with a version that uses LogParser to do the same thing.

DECLARE @SQL VARCHAR(MAX)
CREATE TABLE #temp1 (DRIVE CHAR(1), MB_FREE INT)
INSERT INTO #temp1 (DRIVE, MB_FREE)
EXEC xp_fixeddrives
CREATE TABLE #TEMP2(SUBDIRECTORY VARCHAR(256),
DEPTH INT, [FILE] INT, FULL_PATH VARCHAR(256), COMMAND VARCHAR(512))
SELECT @SQL=COALESCE(@SQL,”)+ ‘INSERT INTO #TEMP2 (SUBDIRECTORY, DEPTH, [FILE]) EXEC Master.dbo.xp_DirTree “‘ + DRIVE +
‘:\MSSQL\DATA”, 1, 1; UPDATE #TEMP2 SET FULL_PATH = ”’ + DRIVE + ”’ + ”:\MSSQL\DATA\” + SUBDIRECTORY WHERE FULL_PATH IS NULL; ‘
FROM #temp1
PRINT @SQL
EXEC (@SQL)
DELETE
FROM #TEMP2
WHERE [FILE] = 0
OR SUBDIRECTORY LIKE ‘%CER’
OR SUBDIRECTORY LIKE ‘MSSQLSYSTEMRESOURCE%’
OR SUBDIRECTORY LIKE ‘distmdl%’
OR FULL_PATH IN
(
SELECT PHYSICAL_NAME
FROM SYS.MASTER_FILES
)
SELECT FULL_PATH, ‘DEL ‘ + FULL_PATH AS COMMAND
FROM #TEMP2
DROP TABLE #temp1
DROP TABLE #TEMP2

Posted in Uncategorized | Print | 1 Comment »