SQL Script that list total database size on server
-
I'm looking for a sql script that will list all the used database space on a server vs. what is allocated. I thought I'd find something in the script archive but there doesn't appaer to be anything listing the "Total" space used, only per database.
How would you list the total space used (other than adding up per DB) on a server that may have upwars of 100 DB's?
Thanks!!
-
Chubb
I have a script which I use to find the used and free space in each datafile and here it goes
/******************************************************/
USE TEMPDB
CREATE TABLE FileDetails (
DbName varchar(100), FileId int , FileGroupName Varchar(50), TotalExtents int , UsedExtents int , [MaxSize] int,
Name nvarchar( 128 ) , FileName nvarchar( 500 ) ,
TotalSize AS ( ( TotalExtents * 64.0 ) / 1024 ) ,
UsedSize AS ( ( UsedExtents * 64.0 ) / 1024 )
)
/** This script will let you find the database size in TotalExtents & UsedExtents **/
BEGIN
/* Get data file(s) size */
DECLARE @db VARCHAR(50), @cmd VARCHAR(2000), @SQL01 NVARCHAR (400)
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR
SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA WHERE CATALOG_NAME NOT IN ('Northwind','Pubs','Model','Master','MSDB')
OPEN dcur
FETCH NEXT FROM dcur INTO @db
WHILE @@FETCH_STATUS=0
BEGIN
/** Creation of Temporary Table in TempDB**/
CREATE TABLE #FileDetails (
FileId int , FileGroupId int , TotalExtents int , UsedExtents int ,
Name nvarchar( 128 ) , FileName nvarchar( 500 ) ,
TotalSize AS ( ( TotalExtents * 64.0 ) / 1024 ) ,
UsedSize AS ( ( UsedExtents * 64.0 ) / 1024 )
)
SET @cmd = 'use ' + @db + ' DBCC showfilestats'
INSERT INTO #FileDetails (FileId , FileGroupId , TotalExtents , UsedExtents , Name , Filename)
EXEC(@cmd)
SET @SQL01 =
'INSERT INTO FileDetails (DBName, FileId , FileGroupName , TotalExtents , UsedExtents , Name , Filename)
SELECT ' + '''' + @db + '''' + ', A.FileId , B.GroupName , A.TotalExtents , A.UsedExtents ,
A.Name , A.Filename FROM #FileDetails A Left Outer Join ' + @db + '..SysFileGroups B On A.FileGroupId = B.GroupId'
EXEC SP_EXECUTESQL @SQL01
DROP TABLE #FileDetails
FETCH NEXT FROM dcur INTO @db
END
END
UPDATE FileDetails SET [MaxSize] = B.[MaxSize]
FROM FileDetails A
INNER Join Master..Sysaltfiles B
ON A.FileId = B.FileId and A.[Name] = B.[Name]
CLOSE dcur
DEALLOCATE dcur
/*********************************************************/
You may want to customize the resultset queries of the FileDetails to get what you want!
Thanks!Viking
-
Use this bit to get the results without using a cursor. Replace <<mydb>> with a database that exists on your server.
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FileDetails]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGINCREATE TABLE dbo.FileDetails ( FileId int , FileGroupId int , TotalExtents int , UsedExtents int , Name nvarchar( 128 ) , FileName nvarchar( 500 ) , TotalSize AS ( ( TotalExtents * 64.0 ) / 1024 ) , UsedSize AS ( ( UsedExtents * 64.0 ) / 1024 ) ) END
exec master..sp_MSforeachdb 'USE ? INSERT INTO <<mydb>>.dbo.FileDetails (FileId, FileGroupId, TotalExtents, UsedExtents, Name, Filename) EXEC(''DBCC showfilestats'')'SELECT * FROM <<mydb>>.dbo.FileDetails
--------------------
Colt 45 - the original point and click interface
-
I use the following procedure that lists space used for each database by space free per file group and space used per table. I also have it raise an error if the number of extents remaining on the data file group starts running low.
CREATE procedure checksizes as
-- Create the temp table for further querying
CREATE TABLE #TableTemp(
acquired datetime,
DatabaseName varchar(50),
rec_id int IDENTITY (1, 1),
table_name varchar(128),
nbr_of_rows int,
data_space decimal(15,2),
index_space decimal(15,2),
total_size decimal(15,2),
db_size decimal(15,2))
declare @dbname varchar(25)
declare @sql varchar(1000)
declare dbcursor cursor for select name from master..sysdatabases where dbid >4
EXEC sp_MSforeachdb @command1="print '?' DBCC updateusage ('?')"
open dbcursor
fetch next from dbcursor into @dbname
while @@fetch_status = 0
begin
-- Get all tables, names, and sizes
set @sql = 'USE '+@DBNAME+' EXEC sp_msforeachtable @command1="insert into #TableTemp(nbr_of_rows, data_space, index_space) exec sp_mstablespace ''' + '?'+''''+'",@command2="update #TableTemp set table_name = '''+'?'+''' where rec_id = (select max(rec_id)
from #TableTemp)"'
exec (@sql)
update #TableTemp set databasename = @dbname, acquired=(select getdate()),total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #TableTemp where databasename is null) where databasename is null
fetch next from dbcursor into @dbname
end
close dbcursor
deallocate dbcursor
insert into tablesizes SELECT * FROM #TableTemp where databasename + table_name + convert(varchar(12), nbr_of_rows) not in (select databasename+ table_name+ convert(varchar(12),nbr_of_rows) from tablesizes)
DROP TABLE #TableTemp
insert into dbsizes select getdate(), d.name, f.name, f.filename, f.size*8/1024, f.growth*8/1024, f.maxsize*8/1024, Extents = case when growth = 0 then 0 else (maxsize - size)/growth end, f.groupid,-1 from master..sysdatabases d, master..sysaltfiles f where f.dbid = d.dbid and f.dbid > 4
declare @groupid int
declare @extents int
declare @filename varchar(200)
declare @dbid int
declare @growth int
declare spaceused cursor for select name, dbname from dbsizes where spaceused = -1
open spaceused
fetch next from spaceused into @filename, @dbname
while @@fetch_status = 0
begin
set @sql = 'USE '+@dbname + ' update sqlmanager..dbsizes set spaceused = (SELECT FILEPROPERTY('''+@filename+''', ''spaceused'')*8/1024) where name = '''+@filename+''' and spaceused =-1'
exec (@sql)
fetch next from spaceused into @filename, @dbname
end
close spaceused
deallocate spaceused
declare groupcursor cursor for select groupid, dbname , sum(extents)
from dbsizes
group by groupid, dbname, acquired
having sum(extents) < 4
and sum(spaceused) > 0 and convert(decimal (9,2),sum(spaceused))/convert(decimal (9,2),sum(size))>.9
and convert(varchar(10),getdate(),101)=convert(varchar(10), acquired, 101)
open groupcursor
fetch next from groupcursor into @groupid, @dbname, @extents
while @@fetch_status = 0
begin
set @growth = (select max(growth) from dbsizes where dbname = @dbname and groupid = @groupid and size <> maxsize)
set @sql = 'File Group '+convert(varchar(2),@groupid)+ ' on database '+@@servername+'/'+@dbname+' has '+convert(varchar(2),@extents)+' extents of '+ convert(varchar(5),@growth) + 'MB and less than 10percent free'
raiserror(@sql,16,1) with log
--print @sql
fetch next from groupcursor into @groupid, @dbname, @extents
end
close groupcursor
deallocate groupcursor
--drop table #dbtemp
GO
-
Thanks to all for the info!!!!
-
can you pls. post the definition of the tables dbsizes and tablesizes?
Msg 208, Level 16, State 1, Procedure checksizes, Line 37
Ungültiger Objektname 'dbsizes'.
-
Here is one that I wrote after not finding what I was looking for online...
The following script has the code for the table creates, view creates, two stored procedures and a job (2005 syntax).
you can drop it into a query window and run the code which should create all objects (you will need to change out the DB_UTILS reference to your base DB)
Once you get the objects created it is as easy as running the two procedures
1. to generate the report information
2. to email the report
The report shows both drive and db space reports separated out
Drive (size, used, free, % free)
DB (db name, db file name, db drive letter, file size, space used, space free, % free)
change out references to DB_UTILS and make sure you put an email address in the second stored procedure and it should work off the bat...
USE master
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ole Automation Procedures';
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO
--------------------------------------------------------
-- create work tables
--------------------------------------------------------
IF EXISTS (SELECT * FROM DB_UTILS.dbo.sysobjects WHERE name = 'tb_Databases')
BEGIN
DROP TABLE DB_UTILS.dbo.tb_Databases
PRINT 'DROPPED tb_Databases From DB_UTILS'
END
CREATE TABLE DB_UTILS.dbo.tb_Databases
(
row_id INT IDENTITY(1,1),
dbname SYSNAME,
db_size INT, -- Size of database, in kilobytes.
remarks VARCHAR(254)
)
----------------------------------------------------------
IF EXISTS (SELECT * FROM DB_UTILS.dbo.sysobjects WHERE name = 'tb_DBShowFileStats')
BEGIN
DROP TABLE DB_UTILS.dbo.tb_DBShowFileStats
PRINT 'DROPPED tb_DBShowFileStats From DB_UTILS'
END
CREATE TABLE DB_UTILS.dbo.tb_DBShowFileStats
(
row_id INT IDENTITY(1,1),
dbname NVARCHAR(255),
fileid INT,
filegroup INT,
totalextents INT,
usedextents INT,
name VARCHAR(255),
filename VARCHAR(255)
)
----------------------------------------------------------
IF EXISTS (SELECT * FROM DB_UTILS.dbo.sysobjects WHERE name = 'tb_FixedDriveSpace')
BEGIN
DROP TABLE DB_UTILS.dbo.tb_FixedDriveSpace
PRINT 'DROPPED tb_FixedDriveSpace From DB_UTILS'
END
CREATE TABLE DB_UTILS.dbo.tb_FixedDriveSpace
(
drive_name CHAR(1) PRIMARY KEY,
free_space DECIMAL(10,3) NULL,
total_size DECIMAL(10,3) NULL
)
--------------------------------------------------------
-- create views
--------------------------------------------------------
USE DB_UTILS
GO
IF EXISTS (SELECT * FROM DB_UTILS.sys.views WHERE name = 'vw_FixedDriveSpace')
BEGIN
DROP VIEW vw_FixedDriveSpace
PRINT 'DROPPING View vw_FixedDriveSpace'
END
GO
CREATE VIEW vw_FixedDriveSpace AS
SELECT TOP 100 PERCENT
a.drive_name + ':\' as [Drive],
STR(SUM(a.total_size), 10, 2) as [Drive_Size],
STR(SUM((a.total_size - a.free_space)), 10, 2) as [Space_Used],
STR(SUM(a.free_space), 10, 2) as [Space_Free],
STR((a.free_space * 100 / a.total_size), 10, 2) as [Pct_Free]
FROM DB_UTILS.dbo.tb_FixedDriveSpace as a
GROUP BY a.drive_name, a.free_space, a.total_size
ORDER BY (a.free_space * 100 / a.total_size), a.drive_name
GO
----------------------------------------------------------
IF EXISTS (SELECT * FROM DB_UTILS.sys.views WHERE name = 'vw_DBFreeSpace')
BEGIN
DROP VIEW vw_DBFreeSpace
PRINT 'DROPPING View vw_DBFreeSpace'
END
GO
CREATE VIEW vw_DBFreeSpace AS
SELECT TOP 100 PERCENT
SUBSTRING(a.dbname, 1, 26) as [Name],
SUBSTRING(b.name, 1, 26) as [FileName],
LEFT(b.filename, 3) as [Drive],
STR(SUM((b.totalextents * 64.0) / 1024.0), 10, 2) as [DB_File_Size],
STR(SUM((b.usedextents * 64.0) / 1024.0), 10, 2) as [Space_Used],
STR(SUM((b.totalextents - b.usedextents) * 64.0 / 1024.0), 10, 2) as [Space_Free],
STR(SUM((((b.totalextents - b.usedextents) * 64.0) / 1024.0 * 100.0 /
((b.totalextents * 64.0) / 1024.0))), 10, 2) as [Pct_Free]
FROM DB_UTILS.dbo.tb_Databases as a
INNER JOIN DB_UTILS.dbo.tb_DBShowFileStats as b on a.dbname = b.dbname
GROUP BY a.dbname, b.name, b.filename, b.totalextents, b.usedextents
ORDER BY (((b.totalextents - b.usedextents) * 64.0) / 1024.0 * 100.0 / ((b.totalextents * 64.0) / 1024.0)),
a.dbname,
b.name
GO
USE DB_UTILS
GO
IF EXISTS (SELECT * FROM DB_UTILS.sys.objects WHERE name = 'bp_DBandServerSpaceReport' AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE bp_DBandServerSpaceReport
PRINT 'DROPPING bp_DBandServerSpaceReport'
END
GO
CREATE PROCEDURE bp_DBandServerSpaceReport AS
SET NOCOUNT ON
-- work variables
-----------------
DECLARE @SQLCmd NVARCHAR(MAX),
@Result INT,
@FSO INT,
@DriveNameOut INT,
@TotalSizeOut VARCHAR(20),
@MB NUMERIC
SET @MB = 1048576
-- clear work tables
--------------------
TRUNCATE TABLE DB_UTILS.dbo.tb_Databases
TRUNCATE TABLE DB_UTILS.dbo.tb_DBShowFileStats
TRUNCATE TABLE DB_UTILS.dbo.tb_FixedDriveSpace
----------------------------------------------------------
-- load database table with database names
-----------------------------------------------------------
SET @SQLCmd = 'master..sp_databases'
INSERT INTO DB_UTILS.dbo.tb_Databases (dbname, db_size, remarks) EXEC sp_executesql @SQLCmd
-- loop through databases and load file stats table with information for each database
--------------------------------------------------------------------------------------
DECLARE @dbname VARCHAR(200)
SET @dbname = ''
WHILE @dbname IS NOT NULL
BEGIN
SELECT @dbname = MIN(dbname)
FROM DB_UTILS.dbo.tb_Databases
WHERE dbname > @dbname
IF @dbname IS NOT NULL
BEGIN
SELECT @SQLCmd = 'USE [' + @dbname + ']; DBCC SHOWFILESTATS'
INSERT INTO DB_UTILS.dbo.tb_DBShowFileStats (fileid, filegroup, totalextents, usedextents, name, filename) EXEC sp_executesql @SQLCmd
UPDATE DB_UTILS.dbo.tb_DBShowFileStats
SET dbname = @dbname
WHERE dbname IS NULL
END
END
-- loop through databases and load file stats table with information for each database
--------------------------------------------------------------------------------------
INSERT DB_UTILS.dbo.tb_FixedDriveSpace(drive_name, free_space) EXEC master.dbo.xp_fixeddrives
EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @FSO OUT
IF @Result <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FSO
END
-- loop through and get drive metadata for each drive on the server
-------------------------------------------------------------------
DECLARE @drive_name VARCHAR(50)
SET @drive_name = ''
WHILE @drive_name IS NOT NULL
BEGIN
SELECT @drive_name = MIN(drive_name)
FROM DB_UTILS.dbo.tb_FixedDriveSpace
WHERE drive_name > @drive_name
IF @drive_name IS NOT NULL
BEGIN
-- get drive information
------------------------
EXEC @Result = sp_OAMethod @FSO, 'GetDrive', @DriveNameOut OUT, @drive_name
-- error handling
-----------------
IF @Result <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FSO
END
-- get drive size
-----------------
EXEC @Result = sp_OAGetProperty @DriveNameOut, 'TotalSize', @TotalSizeOut OUT
-- error handling
-----------------
IF @Result <> 0
BEGIN
EXEC sp_OAGetErrorInfo @DriveNameOut
END
-- update temp table with values
--------------------------------
UPDATE DB_UTILS.dbo.tb_FixedDriveSpace
SET total_size = @TotalSizeOut / @MB
WHERE drive_name = @drive_name
END
END
-- destroy the fso
------------------
EXEC @Result = sp_OADestroy @FSO
-- error handling
-----------------
IF @Result <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FSO
END
GO
-- create procedure that will send mail
---------------------------------------
IF EXISTS (SELECT * FROM DB_UTILS.sys.objects WHERE name = 'bp_DBandServerSpaceReport_SendEmail' AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE bp_DBandServerSpaceReport_SendEmail
PRINT 'DROPPING bp_DBandServerSpaceReport_SendEmail'
END
GO
CREATE PROCEDURE bp_DBandServerSpaceReport_SendEmail AS
-- send report
--------------
-- email variables
------------------
DECLARE @EmailAddress VARCHAR(30),
@EmailSubject VARCHAR(200),
@EmailImportance VARCHAR(10),
@EmailQuery VARCHAR(4000),
@EmailMessage VARCHAR(500),
@EmailFormat VARCHAR(20),
@EmailResultsWidth INT
-- drive space query
--------------------
SELECT @EmailAddress = 'NEED TO ENTER YOUR EMAIL ADDRESS HERE',
@EmailSubject = 'Database Size Report - ' + @@SERVERNAME,
@EmailMessage = 'The System Drive Space Results Are As Follows:' + CHAR(10) +
'----------------------------------------------',
@EmailQuery =
'SET NOCOUNT ON;' + CHAR(10) +
'PRINT ''''' + CHAR(10) +
'SELECT Drive as [Drive],' + CHAR(10) +
' Drive_Size as [Drive Size (MB)],' + CHAR(10) +
' Space_Used as [Space Used (MB)],' + CHAR(10) +
' Space_Free as [Space Free (MB)],' + CHAR(10) +
' Pct_Free as [Pct. Free]' + CHAR(10) +
'FROM DB_UTILS.dbo.vw_FixedDriveSpace' + CHAR(10) +
'ORDER BY Pct_Free, Drive' + CHAR(10) +
'PRINT ''''' + CHAR(10) +
'PRINT ''''' + CHAR(10) +
'SELECT '' '' as ''The Database Space Results Are As Follows:''' + CHAR(10) +
'SELECT Name as [DB Name],' + CHAR(10) +
' FileName as [DB File Name],' + CHAR(10) +
' Drive as [Drive],' + CHAR(10) +
' DB_File_Size as [DB File Size],' + CHAR(10) +
' Space_Used as [Space Used (MB)],' + CHAR(10) +
' Space_Free as [Space Free (MB)],' + CHAR(10) +
' Pct_Free as [Pct. Free]' + CHAR(10) +
'FROM DB_UTILS.dbo.vw_DBFreeSpace' + CHAR(10) +
'ORDER BY Pct_Free, Name, FileName',
@EmailFormat = 'TEXT',
@EmailImportance = 'NORMAL',
@EmailResultsWidth = 150
-- Send Mail
------------
EXEC msdb..sp_send_dbmail
@profile_name = @@SERVERNAME,
@recipients = @EmailAddress,
@subject = @EmailSubject,
@body = @EmailMessage,
@query = @EmailQuery,
@body_format = @EmailFormat,
@query_result_width = @EmailResultsWidth,
@importance = @EmailImportance
GO
-- create SQL job
------------------
USE [msdb]
GO
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'DB_UTILS - Server and DB Space Check')
EXEC msdb.dbo.sp_delete_job @job_name = N'DB_UTILS - Server and DB Space Check', @delete_unused_schedule=1
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DB_UTILS - Server and DB Space Check',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Compiles A Size Report On All Drives And All Databases Reporting Space In-Use, Free Space, And Total Space.',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa',
@notify_email_operator_name=N'SqlAdmin',
@job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run DB Space Report',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=1,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC DB_UTILS.dbo.bp_DBandServerSpaceReport',
@database_name=N'DB_UTILS',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Email DB Space Report',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=1,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC DB_UTILS.dbo.bp_DBandServerSpaceReport_SendEmail',
@database_name=N'DB_UTILS',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Weekly',
@enabled=1,
@freq_type=8,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20011115,
@active_end_date=99991231,
@active_start_time=41500,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
-
This is the best script so far I have seem. Excellent work well thought out
Siraj
-
Thanks for the comments!
-
I use this script to retrieve database space information.
DECLARE @TempFiles TABLE (
[Name] [nvarchar](128) NULL,
[DatabaseID] [int] NULL,
[Type] [nvarchar](60) NULL,
[State] [nvarchar](60) NULL,
[SizeMB] [float] NULL,
[SizeUsedMB] [float] NULL,
[MaxSizeMB] [float] NULL,
[AutoGrowSize] [float] NULL,
[PercentGrowth] [bit] NULL,
[ReadOnly] [bit] NULL,
[FilesystemPath] [nvarchar](260) NULL)
INSERT INTO @TempFiles (
[Name],[DatabaseID],[Type],[State],[SizeMB],[SizeUsedMB]
,[MaxSizeMB],[AutoGrowSize],[PercentGrowth],[ReadOnly],[FilesystemPath])
EXEC sp_msforeachdb 'USE [?]; SELECT [name],
DB_ID() as [DatabaseID],
[type_desc] as [Type],
[state_desc] as [State],
/128.00 as [SizeMB],
fileproperty([name],''SpaceUsed'')/128.00 as [SizeUsedMB],
CASE WHEN [max_size] = -1 then [max_size] ELSE [max_size]/128.00 END as [MaxSizeMB],
CASE WHEN [is_percent_growth] = 1 THEN [growth] ELSE [growth]/128.00 END as [AutoGrowSize],
[is_percent_growth] as [PercentGrowth],
CASE WHEN [is_media_read_only] = 1 OR [is_read_only] = 1 THEN 1 ELSE 0 END as [ReadOnly],
[physical_name] as [FilesystemPath]
FROM sys.database_files'
SELECT * FROM @TempFiles
-
My Database names are big and the entire name gets missed , even if i remove the columns DB file name and Drive on
The Database Space Results Are As Follows field...
can you help me where i have to tweak this, if possible can this be got out as an HTML format ..if i use output as @EmailFormat = 'TEXT',
as HTML...
it gets totally jumbled...
Appreciate help..
-
how large are the database names / database file names that you are having an issue with?
-
The client insists that he wants the output as a table format, xml\html ,
Since all belong to Sharepoint , the GUI is set byitself and created by them for ex: PerformancePoint Service_077c593014bf4d1ea2718da615259f9f
Eben
-
Nice usage of the foreachloop rather than a cursor.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply