• Skip to primary navigation
  • Skip to main content

ROB SYMONDS

  • Home
  • About

Rob Symonds

Setup for SQL Server 2005 Developer Edition Fails With MSXML6 Errors

2008-06-23 By Rob Symonds

I was trying to install SQL Server 2005 Developer Edition on an XP SP3 workstation today but setup kept failing. The setup screen looked like this:

[Read more…] about Setup for SQL Server 2005 Developer Edition Fails With MSXML6 Errors

Get Space Used By Files in All Databases

2008-06-19 By Rob Symonds

DECLARE @dbs TABLE (dbname NVARCHAR(200))
DECLARE @wrkStatement NVARCHAR(200)
DECLARE @wrkSql NVARCHAR(4000)

INSERT INTO @dbs(dbname) 
	SELECT name 
	FROM master..sysdatabases 
	WHERE name NOT IN ('model', 'master', 'msdb')
	ORDER BY name

CREATE TABLE #tmp123_results (
	[Db] NVARCHAR(100),	
	[FileId] INT, 
	[LogicalName] NVARCHAR(655), 
	[AllocatedMB] DECIMAL(12, 2), 
	[UsedMB] DECIMAL(12, 2), 
	[FreeMB] DECIMAL(12, 2), 
	[PercentUsed] DECIMAL(12, 2),
	[PercentFree] DECIMAL(12, 2),
	[PhysicalName] NVARCHAR(655)
)

DECLARE @curr_db NVARCHAR(100)
DECLARE @crsSelect CURSOR

SET @crsSelect = CURSOR FOR
	SELECT dbname
    FROM @dbs

OPEN @crsSelect

FETCH NEXT FROM @crsSelect INTO @curr_db
WHILE @@FETCH_STATUS = 0
    BEGIN
		SET @wrkStatement = '['+@curr_db+ '].dbo.sp_executesql'
		SET @wrkSql = 'INSERT INTO #tmp123_results(Db,FileId,LogicalName,AllocatedMB,UsedMB,FreeMB,PercentUsed,PercentFree,PhysicalName) 
			SELECT 
			DB = '''+@curr_db+''',
			a.FILEID,
			NAME =	a.NAME,
			ALLOCATED_MB = convert(decimal(12,2),round(a.size/128.000,2)),
			USED_MB = convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)),
			FREE_MB = convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) ,
			PercentUsed = 100 * (convert(decimal(12,2),round((fileproperty(a.name,''SpaceUsed''))/128.000,2)) / convert(decimal(12,2),round(a.size/128.000,2)) ) ,
			PercentFree = 100 * (convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) / convert(decimal(12,2),round(a.size/128.000,2)) ) ,
			FILENAME = a.FILENAME
			FROM [' + @curr_db + '].dbo.sysfiles a'	

		EXEC @wrkStatement @wrkSql

        FETCH NEXT FROM @crsSelect INTO @curr_db
    END

CLOSE @crsSelect
DEALLOCATE @crsSelect

SELECT * FROM #tmp123_results ORDER BY Db, FileId

DROP TABLE #tmp123_results

Shrinking Databases and Files

2008-06-19 By Rob Symonds

Shrinking should not be done on a regular basis. [Read more…] about Shrinking Databases and Files

  • « Go to Previous Page
  • Go to page 1
  • Interim pages omitted …
  • Go to page 8
  • Go to page 9
  • Go to page 10

Copyright © 2023 · Log in

  • RSS
  • Twitter
  • LinkedIn