June 5, 2014 at 11:58 am
I'm trying to capture the sizes of all Databases into a Permanent Table and include the Date.
It works when inserting into a #Temp Table.
When I try inserting into a permanent table it returns NULL.
The following code needs modified to create a permanent table and store the Date:
CREATE TABLE #databases ( DATABASE_NAME VARCHAR(50), DATABASE_SIZE FLOAT, Date VARCHAR(100) )
INSERT #Databases EXEC ('EXEC sp_databases');
SELECT@@SERVERNAME AS SERVER_NAME, DATABASE_NAME,
DATABASE_SIZE AS 'KB',
ROUND(DATABASE_SIZE / 1024, 2) AS 'MB',
ROUND((DATABASE_SIZE / 1024) / 1024, 2) AS 'GB',
CONVERT(date, getdate()) AS Date FROM #databases
DROP TABLE #databases;
Any help would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 5, 2014 at 12:25 pm
I think you are making it harder than it needs to be:
Select db_name(mf.database_id) As DatabaseName
, convert(int
, Case When convert(bigint , sum(mf.size)) >= 268435456 Then Null
Else sum(mf.size) * 8 -- Convert from 8192 byte pages to Kb
End) As DatabaseSize
, cast(getdate() As date) As MyDate
From sys.master_files mf
Where mf.state = 0
Group By
mf.database_id;
I am sure you can get what you need from the above...;)
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 5, 2014 at 12:46 pm
Thanks I'm trying t get the size in GB and store in a permanent table so capture the growth.
It should exclude the Transaction Log. (edit)
I will run it as Daily Job.
Thanks again.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 5, 2014 at 2:12 pm
Jeffrey Williams 3188 (6/5/2014)
I think you are making it harder than it needs to be:
Select db_name(mf.database_id) As DatabaseName
, convert(int
, Case When convert(bigint , sum(mf.size)) >= 268435456 Then Null
Else sum(mf.size) * 8 -- Convert from 8192 byte pages to Kb
End) As DatabaseSize
, cast(getdate() As date) As MyDate
From sys.master_files mf
Where mf.state = 0
Group By
mf.database_id;
I am sure you can get what you need from the above...;)
Excellent this works for me.
I wish that I could get it in GB.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 5, 2014 at 3:42 pm
Put the code I posted in a CTE - then use your calculations to convert it to MB/GB. Or, just wrap the calculation that is in KB with the calculations for MB/GB.
To exclude the log files add to the where clause: And mf.Type <> 1
Or, you can just group on the type also - and get the sizes for all types.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 5, 2014 at 4:29 pm
Jeffrey Williams 3188 (6/5/2014)
Put the code I posted in a CTE - then use your calculations to convert it to MB/GB. Or, just wrap the calculation that is in KB with the calculations for MB/GB.To exclude the log files add to the where clause: And mf.Type <> 1
Or, you can just group on the type also - and get the sizes for all types.
Could you please provide me with the line of code to list GB's?
Thank you.:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 5, 2014 at 4:49 pm
Welsh Corgi (6/5/2014)
Jeffrey Williams 3188 (6/5/2014)
I think you are making it harder than it needs to be:
Select db_name(mf.database_id) As DatabaseName
, convert(int
, Case When convert(bigint , sum(mf.size)) >= 268435456 Then Null
Else sum(mf.size) * 8 -- Convert from 8192 byte pages to Kb
End) As DatabaseSize
, cast(getdate() As date) As MyDate
From sys.master_files mf
Where mf.state = 0
Group By
mf.database_id;
I am sure you can get what you need from the above...;)
Excellent this works for me.
I wish that I could get it in GB.
What is the formula for GB?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 5, 2014 at 11:27 pm
1024 KB = 1 MB
1024 MB = 1GB
KB / 1024.0 / 1024.0
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 9, 2014 at 11:44 am
Here is mine ...
DECLARE @total int, @id int, @dbname varchar(200), @sql varchar(MAX)
DECLARE @t table(ID int not null identity(1,1), name varchar(255))
INSERT INTO @t(name)
SELECT name FROM sys.databases WHERE database_id > 4
ORDER BY name
--Assigning values to variables
SET @total = @@ROWCOUNT
SET @id=1
WHILE @id <= @total
BEGIN
SELECT @dbname = name FROM @t WHERE ID = @id
SET @sql =
'
USE [' + @dbname + '];
INSERT INTO DBSize
SELECT
DB_NAME() AS DBName,
SUM( convert(decimal(12,2),round(a.size/128.000,2))) AS Size,
SUM(convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2))) UsedSpace,
SUM(convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2))) As FreeSpace,
getdate() AS MetricDate
FROM dbo.sysfiles a
WHERE filename LIKE ''E:\SQL_Data%''
'
EXECUTE(@sql)
SET @id = @id + 1
END
Feel free to modify, adjust or change if needed.
I also use SSRS to generate nice reports based on this. It allows me to see clients with unexpected growth or when I do need to increase file size to avoid auto growth.
June 9, 2014 at 1:49 pm
CREATE TABLE [dbo].[QA_dbSizeSnapshot](
[rowId] [int] IDENTITY(1,1) NOT NULL,
[dbName] [varchar(255) NOT NULL,
[dataFileSizeMB] [float] NOT NULL,
[logFileSizeMB] [float] NOT NULL,
[capturedDtg] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[QA_dbSizeSnapshot] ADD CONSTRAINT [DF_QA_dbSizeSnapshot_capturedDtg] DEFAULT (getdate()) for [capturedDTG]
GO
/******Job Query****/
WITH FS
AS
(SELECT database_id, [TYPE], size * 8.0/1024 as size FROM sys.master_files)
INSERT INTO QA_dbSizeSnapshot(dbName, dataFileSizeMb, logFileSizeMb)
SELECT db.[name] as dbName,
(SELECT SUM(fs.) FROM fs WHERE fs.[TYPE] = 0 AND fs.database_id) DataFileSizeMB,
(SELECT SUM(fs.) FROM fs WHERE fs.[TYPE] = 1 AND fs.database_id) LogFileSizeMB
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply