February 14, 2013 at 10:05 am
I am working on Capture database growth trends on a server.
I got little nice script from below Link and I also copy pasted it.
I need help on loading the output into an TABLE with current time Stamp.
So that I can maintain history for the database growth trends.
Can any one please help me, Thanks in advance.
http://gallery.technet.microsoft.com/scriptcenter/All-Databases-Data-log-a36da95d
------------------------------Data file size----------------------------
if exists (select * from tempdb.sys.all_objects where name like '%#dbsize%')
drop table #dbsize
create table #dbsize
(Dbname sysname,dbstatus varchar(50),Recovery_Model varchar(40) default ('NA'), file_Size_MB decimal(30,2)default (0),Space_Used_MB decimal(30,2)default (0),Free_Space_MB decimal(30,2) default (0))
go
insert into #dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB)
exec sp_msforeachdb
'use [?];
select DB_NAME() AS DbName,
CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) ,
CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')),
sum(size)/128.0 AS File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB
from sys.database_files where type=0 group by type'
go
-------------------log size--------------------------------------
if exists (select * from tempdb.sys.all_objects where name like '#logsize%')
drop table #logsize
create table #logsize
(Dbname sysname, Log_File_Size_MB decimal(38,2)default (0),log_Space_Used_MB decimal(30,2)default (0),log_Free_Space_MB decimal(30,2)default (0))
go
insert into #logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB)
exec sp_msforeachdb
'use [?];
select DB_NAME() AS DbName,
sum(size)/128.0 AS Log_File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB
from sys.database_files where type=1 group by type'
go
--------------------------------database free size
if exists (select * from tempdb.sys.all_objects where name like '%#dbfreesize%')
drop table #dbfreesize
create table #dbfreesize
(name sysname,
database_size varchar(50),
Freespace varchar(50)default (0.00))
insert into #dbfreesize(name,database_size,Freespace)
exec sp_msforeachdb
'use [?];SELECT database_name = db_name()
,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'')
,''unallocated space'' = ltrim(str((
CASE
WHEN dbsize >= reservedpages
THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576
ELSE 0
END
), 15, 2) + '' MB'')
FROM (
SELECT dbsize = sum(convert(BIGINT, CASE
WHEN type = 0
THEN size
ELSE 0
END))
,logsize = sum(convert(BIGINT, CASE
WHEN type <> 0
THEN size
ELSE 0
END))
FROM sys.database_files
) AS files
,(
SELECT reservedpages = sum(a.total_pages)
,usedpages = sum(a.used_pages)
,pages = sum(CASE
WHEN it.internal_type IN (
202
,204
,211
,212
,213
,214
,215
,216
)
THEN 0
WHEN a.type <> 1
THEN a.used_pages
WHEN p.index_id < 2
THEN a.data_pages
ELSE 0
END)
FROM sys.partitions p
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
LEFT JOIN sys.internal_tables it
ON p.object_id = it.object_id
) AS partitions'
-----------------------------------
if exists (select * from tempdb.sys.all_objects where name like '%#alldbstate%')
drop table #alldbstate
create table #alldbstate
(dbname sysname,
DBstatus varchar(55),
R_model Varchar(30))
--select * from sys.master_files
insert into #alldbstate (dbname,DBstatus,R_model)
select name,CONVERT(varchar(20),DATABASEPROPERTYEX(name,'status')),recovery_model_desc from sys.databases
--select * from #dbsize
insert into #dbsize(Dbname,dbstatus,Recovery_Model)
select dbname,dbstatus,R_model from #alldbstate where DBstatus <> 'online'
insert into #logsize(Dbname)
select dbname from #alldbstate where DBstatus <> 'online'
insert into #dbfreesize(name)
select dbname from #alldbstate where DBstatus <> 'online'
select
d.Dbname,d.dbstatus,d.Recovery_Model,
(file_size_mb + log_file_size_mb) as DBsize,
d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB,
l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,fs.Freespace as DB_Freespace
from #dbsize d join #logsize l
on d.Dbname=l.Dbname join #dbfreesize fs
on d.Dbname=fs.name
order by Dbname
February 14, 2013 at 10:15 am
Without reading and testing each block, it looks like you've already done the real work. From here you have two choices:
1. When your script is done, insert the data from these temp tables into a physical table.
2. Instead of inserting into the temp table, insert directly into the physical table.
February 14, 2013 at 10:26 am
Thanks for the replay Ed,
But my concern is, I need the Current TIME STAMP into.
I ok with any good post available, Please post the links.
February 14, 2013 at 10:39 am
First create physical tables to use for long-term tracking of the size info you want. Include a datetime column. You'll only do this once when you first set things up. This will store the data from each run of you procedure so you can analyze it over time.
You'll want to do this for each table with info you want to track long-term. For example, to create the table to store database size using your definition of #dbsize temp table:
create table dbsize (
Dbname sysname,
dbstatus varchar(50),
Recovery_Model varchar(40),
file_Size_MB decimal(30,2),
Space_Used_MB decimal(30,2),
Free_Space_MB decimal(30,2),
entry_date datetime);
Then, at the end of your procedure, populate the real table with the data from the data from your #dbsize temp table and include your date field. You'll want to populate each table you created above. So, for the dbsize table:
INSERT INTO dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB, entry_date)
SELECT Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB, GetDate()
FROM #dbsize;
Don't forget to drop those temp tables at the end of you procedure. Please do yourself a favor and make sure you understand how this stuff works before deploying it to a production environment. Always understand stuff before running it in production, no matter who it's from.
February 15, 2013 at 1:27 am
This is how I do it currently, I capture the recovery model in a different proc, but wont be to hard to build it into this
CREATE TABLE [DatabaseFileUsage]
(
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[DatabaseName] [sysname] NOT NULL,
[FileID] [int] NULL,
[FileSizeMB] [decimal](18, 2) NULL,
[SpaceUsedMB] [decimal](18, 2) NULL,
[FreeSpaceMB] [decimal](18, 2) NULL,
[LogicalName] [sysname] NOT NULL,
[FileLocation] [sysname] NOT NULL,
[DateCollected] [date] NULL DEFAULT GETUTCDATE(),
CONSTRAINT [PK_DB_DatabaseFileUsage] PRIMARY KEY CLUSTERED ([ID] ASC)
)
GO
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = REPLACE(
CAST(
(
SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
'INSERT INTO [DatabaseFileUsage] (DatabaseName, FileID, FileSizeMB, SpaceUsedMB, FreeSpaceMB, LogicalName, FileLocation)' + CHAR(13) + CHAR(10) +
'SELECT ' + CHAR(13) + CHAR(10) +
'DatabaseName = DB_NAME(), ' + CHAR(13) + CHAR(10) +
'a.FILEID, ' + CHAR(13) + CHAR(10) +
'[FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +') / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +')) / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'a.NAME, a.FILENAME ' + CHAR(13) + CHAR(10) +
'FROM dbo.sysfiles a;' + CHAR(13) + CHAR(10)
FROM sys.databases
FOR XML PATH('')
) AS NVARCHAR(MAX)
),'&#x 0D;',CHAR(13) + CHAR(10) --remove the space between '&#x 0D;'
)
EXECUTE sp_executesql @sql
February 15, 2013 at 8:11 am
Thanks Anthony
When I try to execute, I am getting errors, Copied below.
Will it work for SQL server 2012 release?
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = REPLACE(
CAST(
(
SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
'INSERT INTO [DatabaseFileUsage] (DatabaseName, FileID, FileSizeMB, SpaceUsedMB, FreeSpaceMB, LogicalName, FileLocation)' + CHAR(13) + CHAR(10) +
'SELECT ' + CHAR(13) + CHAR(10) +
'DatabaseName = DB_NAME(), ' + CHAR(13) + CHAR(10) +
'a.FILEID, ' + CHAR(13) + CHAR(10) +
'[FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +') / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +')) / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'a.NAME, a.FILENAME ' + CHAR(13) + CHAR(10) +
'FROM dbo.sysfiles a;' + CHAR(13) + CHAR(10)
FROM sys.databases
FOR XML PATH('')
) AS NVARCHAR(MAX)
),'&#x 0D;',CHAR(13) + CHAR(10) --remove the space between '&#x 0D;'
)
EXECUTE sp_executesql @sql
I am getting error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 22
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 31
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 42
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 51
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 52
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 61
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 62
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 71
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 72
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 81
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 82
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 91
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 92
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 101
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 102
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 111
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 112
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 121
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 122
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 131
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 132
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 141
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 142
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 151
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 152
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 161
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 162
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 171
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 172
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 181
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 182
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 191
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 192
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 201
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 202
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 211
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 212
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 221
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 222
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 231
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 232
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 241
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 242
Incorrect syntax near '&'.
February 15, 2013 at 8:14 am
Did you remove the space on the line with the comment?
February 15, 2013 at 8:30 am
Anthony,
it execute good, But gives an error on the table. below is the error message.
I have created the table in one of the user databases
Msg 208, Level 16, State 1, Line 3
Invalid object name 'DatabaseFileUsage'.
February 15, 2013 at 8:37 am
Did you create the table in the script?
If not comment out the line which does the insert in the other script?
February 15, 2013 at 9:01 am
First created the Table. And then running the DECLARE InSERT into statement to load into the table DatabaseFileUsage.
My understanding is the statement is loads in to the table right ?
February 15, 2013 at 9:46 am
yep, the table needs to be in every db or you need to do the three part naming convention on the insert line
db.schema.table
February 18, 2013 at 8:38 am
Personally, I like collecting this type of data all in one place. It allows me to run reports on a single table instead of having to hit tables in multiple databases. A bonus is that other people have no chance of messing with things. 😀 For my situation, this is just a cleaner approach.
February 18, 2013 at 8:39 am
Ed Wagner (2/18/2013)
Personally, I like collecting this type of data all in one place. It allows me to run reports on a single table instead of having to hit tables in multiple databases. A bonus is that other people have no chance of messing with things. 😀 For my situation, this is just a cleaner approach.
yep same, thats what the original code does but didnt divulge the db name and schama name in the script
February 15, 2014 at 3:20 am
Hi,
how to send the output as a HTML mail
http://gallery.technet.microsoft.com/scriptcenter/All-Databases-Data-log-a36da95d#content
This what I have done but getting error. Please help me to get the output as a HTML mail
DECLARE @table NVARCHAR(MAX) ;
SET @table = N'<H1>DBSize</H1>' + N'<table border="1">' +
N'<tr><th>d.Dbname</th><th>d.dbstatus</th><th>d.Recovery_Model</th><th>DBSIZE</th><th>(file_size_mb + log_file_size_mb) as DBsize
</th><th>d.file_Size_MB</th></th><th>d.Space_Used_MB</th><th>d.Free_Space_MB</th><th>l.Log_File_Size_MB</th><th>log_Space_Used_MB</th>
<th>l.log_Free_Space_MB</th><th>fs.Freespace</th></tr>'
+ CAST ( ( SELECT [d.Dbname] AS 'td','',[d.dbstatus] AS 'td','',[d.Recovery_Model] AS 'td','', (file_size_mb + log_file_size_mb) AS 'td','',[FILE_SIZE_MB] AS 'td','',
[d.file_Size_MB] AS 'td','',[d.Space_Used_MB] AS 'td','',[d.Free_Space_MB] AS 'td','',[l.Log_File_Size_MB] AS 'td','',
[log_Space_Used_MB] AS 'td','',[l.log_Free_Space_MB] AS 'td','',[fs.Freespace] AS 'td'
from #dbsize d join #logsize l
on d.Dbname=l.Dbname join #dbfreesize fs
on d.Dbname=fs.name
order by Dbname
FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail @profile_name='GmailAccount', --Change to your Profile Name
@recipients='Myname@gmail.com', --Put the email address of those who want to receive the e-mail
@subject = 'DatabaseFileSizes', @body = @table, @body_format = 'HTML' ;
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply