This is common information which DBA required on a daily basis for monitoring or reporting purposes. This is basic details which help us to get the database growth statistics on daily, weekly and monthly basis so we can have an idea for the disk space management. We can dump daily database statistics and make database size and growth reports. You all know about the script and you may use too, even I would like to share. System stored procedures help us to get the same database information, e.g sp_helpdb stored procedure give details,
This is all information for database which returned by sp_helpdb by passing the database name as a parameter. If you do not wish to pass the database name, then it will return all databases without physical file details. Same approach receive by sp_spaceused system stored procedure without any parameter passing.
Now I would like to get it with some other scripts which give details for each database and their physical files.
This is details returned by each physical file of all databases, but now the same script with minor changes return whole database size details without physical files detail.
These are the scripts which I want to share with you which may help you and I would like you to share your thoughts and scripts which you are using for database size and growth statistics. You may read my earlier post for Database Backup Statistics and History.
EXEC sp_helpdb 'DEMO';
/* Output :
name db_size owner dbid created status compatibility_level
------------------------ ------ ------ ----------- ---------------- -------------------
DEMO 13.25 MB sa 19 Apr 28 2015 Status=ONLINE,... 110
name fileid filename filegroup size maxsize growth usage
------- -------- ------------------------------------------------------------------------------ --------- ------- ------------ -------- -------
DEMO 1 C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\DEMO.mdf PRIMARY 9280 KB Unlimited 1024 KB data only
DEMO_log 2 C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\DEMO_log.ldf NULL 4288 KB 2147483648 KB 10% log only
*/
This is all information for database which returned by sp_helpdb by passing the database name as a parameter. If you do not wish to pass the database name, then it will return all databases without physical file details. Same approach receive by sp_spaceused system stored procedure without any parameter passing.
USE DEMO
GO
EXEC sp_spaceused;
/* Output :
database_name database_size unallocated space
-------------- ------------------ ------------------
DEMO 13.25 MB 3.52 MB
reserved data index_size unused
------------- ------------------ ------------------ ---------
5680 KB 3064 KB 2024 KB 592 KB
*/
Now I would like to get it with some other scripts which give details for each database and their physical files.
USE master
GO
SELECT db.[name] AS 'DBName',
af.name AS 'LogicalName',
af.[filename] AS 'PhysicalName',
( Cast(( ( ( Cast(af.[size] AS NUMERIC(18, 4)) * 8192 ) / 1024 ) / 1024 )
AS
NUMERIC(18, 2)) ) AS 'FileSize_MB'
FROM sys.sysdatabases db
INNER JOIN sys.sysaltfiles af
ON db.dbid = af.dbid
/* Output :
DBName LogicalName PhysicalName FileSize_MB
------------------- --------------------------- ---------------------------------------------------------------------------------------- ---------------
master master C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\master.mdf 4.88
master mastlog C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\mastlog.ldf 1.75
tempdb tempdev C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\tempdb.mdf 8.00
tempdb templog C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\templog.ldf 0.50
model modeldev C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\model.mdf 4.06
model modellog C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\modellog.ldf 1.00
msdb MSDBData C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\MSDBData.mdf 22.31
msdb MSDBLog C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\MSDBLog.ldf 19.63
test test C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\test.mdf 4.06
test test_log C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\test_log.ldf 1.31
test1 test1 C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\test1.mdf 8.00
test1 test1_log C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\test1_log.ldf 19.63
Repl1 Repl1 C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\Repl1.mdf 23.00
Repl1 Repl1_log C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\Repl1_log.ldf 2.50
SBExternal SBExternal C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SBExternal.mdf 4.06
SBExternal SBExternal_log C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SBExternal_log.ldf 1.02
SBExternalReceiver SBExternalReceiver C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SBExternalReceiver.mdf 4.06
SBExternalReceiver SBExternalReceiver_log C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SBExternalReceiver_log.ldf 1.02
importdb importdb C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\importdb .mdf 4.06
importdb importdb _log C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\importdb _log.ldf 1.02
import import C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\import.mdf 9.06
import import_log C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\import_log.ldf 2.81
SchemaSync SchemaSync C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SchemaSync.mdf 4.06
SchemaSync SchemaSync_log C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SchemaSync_log.ldf 1.02
SchemaSync_New SchemaSync_New C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SchemaSync_New.mdf 4.06
SchemaSync_New SchemaSync_New_log C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SchemaSync_New_log.ldf 1.02
hMailServer hMailServer C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\hMailServer.mdf 4.06
hMailServer hMailServer_log C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\hMailServer_log.ldf 1.02
AdventureWorksLT2008 AdventureWorksLT2008_Data D:\Data\AdventureWorksLT2008_Data.mdf 8.31
AdventureWorksLT2008 AdventureWorksLT2008_Log D:\Data\AdventureWorksLT2008_Log.ldf 18.00
powershelldb powershelldb C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\powershelldb.mdf 4.06
powershelldb powershelldb_log C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\powershelldb_log.ldf 1.02
SMOSimple_DB SMOSimple_DB C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SMOSimple_DB.mdf 4.06
SMOSimple_DB SMOSimple_DB_log C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SMOSimple_DB_log.ldf 1.02
Merge_Repl_Sub Merge_Repl_Sub C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\Merge_Repl_Sub.mdf 9.00
Merge_Repl_Sub Merge_Repl_Sub_log C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\Merge_Repl_Sub_log.ldf 3.75
DEMO DEMO C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\DEMO.mdf 9.06
DEMO DEMO_log C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\DEMO_log.ldf 4.19
*/
This is details returned by each physical file of all databases, but now the same script with minor changes return whole database size details without physical files detail.
USE master
GO
SELECT db.[name] AS 'DBName',
Sum(( Cast(( ( ( Cast(af.[size] AS NUMERIC(18, 4)) * 8192 ) / 1024 ) /
1024 ) AS
NUMERIC(18, 2)) )) AS 'FileSize_MB'
FROM sys.sysdatabases db
INNER JOIN sys.sysaltfiles af
ON db.dbid = af.dbid
GROUP BY db.[name]
/* Output :
DBName FileSize_MB
--------------------- -------------
master 6.63
tempdb 8.50
model 5.06
msdb 41.94
test 5.37
test1 27.63
Repl1 25.50
SBExternal 5.08
SBExternalReceiver 5.08
importdb 5.08
import 11.87
SchemaSync 5.08
SchemaSync_New 5.08
hMailServer 5.08
AdventureWorksLT2008 26.31
powershelldb 5.08
SMOSimple_DB 5.08
Merge_Repl_Sub 12.75
DEMO 13.25
*/
These are the scripts which I want to share with you which may help you and I would like you to share your thoughts and scripts which you are using for database size and growth statistics. You may read my earlier post for Database Backup Statistics and History.
No comments:
Post a Comment
Please Use Good Leanguage