April 27, 2013 at 12:35 am
Hi,
what is wrong this code? pl. suggest
create table databaseSize (
DatabaseName varchar (100),
RowSizeMB varchar (50),
LogSizeMB varchar(50),
DBSizeGB varchar (50),
SteamSizeMB varchar(50),
TextIndexSizeMB varchar(50),
time_stamp getdate())
insert into databaseSize (
'DatabaseName',
'RowSizeMB',
'LogSizeMB',
'DBSizeGB',
'SteamSizeMB',
'TextIndexSizeMB',
'time_stamp')
SELECT
DB_NAME(db.database_id) DatabaseName,
(CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
(CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,
(CAST(mfrows.RowSize AS FLOAT)*8)/1024/1024+(CAST(mflog.LogSize AS FLOAT)*8)/1024/1024 DBSizeG,
(CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,
(CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB
FROM sys.databases db
LEFT JOIN (SELECT database_id,
SUM(size) RowSize
FROM sys.master_files
WHERE type = 0
GROUP BY database_id, type) mfrows
ON mfrows.database_id = db.database_id
LEFT JOIN (SELECT database_id,
SUM(size) LogSize
FROM sys.master_files
WHERE type = 1
GROUP BY database_id, type) mflog
ON mflog.database_id = db.database_id
LEFT JOIN (SELECT database_id,
SUM(size) StreamSize
FROM sys.master_files
WHERE type = 2
GROUP BY database_id, type) mfstream
ON mfstream.database_id = db.database_id
LEFT JOIN (SELECT database_id,
SUM(size) TextIndexSize
FROM sys.master_files
WHERE type = 4
GROUP BY database_id, type) mftext
ON mftext.database_id = db.database_id
ORDER BY 4 DESC
thanks
ananda
April 27, 2013 at 3:35 am
ananda.murugesan (4/27/2013)
Hi,what is wrong this code? pl. suggest
create table databaseSize (DatabaseName varchar (100),
RowSizeMB varchar (50),
LogSizeMB varchar(50),
DBSizeGB varchar (50),
SteamSizeMB varchar(50),
TextIndexSizeMB varchar(50),
time_stamp getdate())
insert into databaseSize (
'DatabaseName',
'RowSizeMB',
'LogSizeMB',
'DBSizeGB',
'SteamSizeMB',
'TextIndexSizeMB',
'time_stamp')
SELECT
DB_NAME(db.database_id) DatabaseName,
(CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
(CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,
(CAST(mfrows.RowSize AS FLOAT)*8)/1024/1024+(CAST(mflog.LogSize AS FLOAT)*8)/1024/1024 DBSizeG,
(CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,
(CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB
FROM sys.databases db
LEFT JOIN (SELECT database_id,
SUM(size) RowSize
FROM sys.master_files
WHERE type = 0
GROUP BY database_id, type) mfrows
ON mfrows.database_id = db.database_id
LEFT JOIN (SELECT database_id,
SUM(size) LogSize
FROM sys.master_files
WHERE type = 1
GROUP BY database_id, type) mflog
ON mflog.database_id = db.database_id
LEFT JOIN (SELECT database_id,
SUM(size) StreamSize
FROM sys.master_files
WHERE type = 2
GROUP BY database_id, type) mfstream
ON mfstream.database_id = db.database_id
LEFT JOIN (SELECT database_id,
SUM(size) TextIndexSize
FROM sys.master_files
WHERE type = 4
GROUP BY database_id, type) mftext
ON mftext.database_id = db.database_id
ORDER BY 4 DESC
thanks
ananda
Comments below of the minor changes needed. Try and avoid the quoted identifiers as the square brackets are more standard and don't need the user options to be altered.
use tempdb
create table databaseSize (
DatabaseName varchar (100),
RowSizeMB varchar (50),
LogSizeMB varchar(50),
DBSizeGB varchar (50),
SteamSizeMB varchar(50),
TextIndexSizeMB varchar(50),
TIME_STAMP DATETIME DEFAULT GETDATE() --<< added datatype and default
)
GO
insert into databaseSize (
[DatabaseName], -- change quote to square bracket
[RowSizeMB], -- change quote to square bracket
[LogSizeMB], -- change quote to square bracket
[DBSizeGB], -- change quote to square bracket
[SteamSizeMB], -- change quote to square bracket
[TextIndexSizeMB]/*, -- change quote to square bracket
'TIME_STAMP'*/ -- Remove this column as you havent got a matching column from the select statement
)
SELECT
DB_NAME(db.database_id) DatabaseName,
(CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
(CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,
(CAST(mfrows.RowSize AS FLOAT)*8)/1024/1024+(CAST(mflog.LogSize AS FLOAT)*8)/1024/1024 DBSizeG,
(CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,
(CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB
FROM sys.databases db
LEFT JOIN (SELECT database_id,
SUM(size) RowSize
FROM sys.master_files
WHERE type = 0
GROUP BY database_id, type) mfrows
ON mfrows.database_id = db.database_id
LEFT JOIN (SELECT database_id,
SUM(size) LogSize
FROM sys.master_files
WHERE type = 1
GROUP BY database_id, type) mflog
ON mflog.database_id = db.database_id
LEFT JOIN (SELECT database_id,
SUM(size) StreamSize
FROM sys.master_files
WHERE type = 2
GROUP BY database_id, type) mfstream
ON mfstream.database_id = db.database_id
LEFT JOIN (SELECT database_id,
SUM(size) TextIndexSize
FROM sys.master_files
WHERE type = 4
GROUP BY database_id, type) mftext
ON mftext.database_id = db.database_id
ORDER BY 4 DESC
select * from databaseSize ;
Hope this helps
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply