August 4, 2014 at 10:28 pm
Hi Pros,
I want to capture database size in below table. Cam you please help me as I'm a newbei. I need to capture the Rundate so I can schedule it. Thanks in advance.
create table DBSize
(
name nvarchar(24),
db_size nvarchar(13),
owner nvarchar(24),
dbid smallint,
created char(11),
status varchar(340),
compatibility_level tinyint,
RunDate datetime default getdate()
)
insert into DBSize exec sp_helpdb
select * from DBSize
August 4, 2014 at 11:43 pm
sp_helpdb is good to run manually to view db information, but no good for storing in a table for later comparison. It returns the database size as a char and other information like compatability level are in a long string of text - not easy to compare and look for changes over time.
If you look at the underlying code for sp_helpdb it looks at the old system tables, dbo.sysdatabases and dbo.sysfiles.
You could query those tables in the same way as the procedure but the recommended way to view this info in SQL2005+ is to use the newer catalog views or DMV's. http://msdn.microsoft.com/en-us/library/ms187997.aspx
An example query (from http://sqlserverperformance.wordpress.com/ by Glenn Berry) to show dbsize:
-- File Names and Paths for TempDB and all user databases in instance (Query 16) (Database Filenames and Paths)
SELECT DB_NAME([database_id])AS [Database Name],
[file_id], name, physical_name, type_desc, state_desc,
is_percent_growth, growth,
CONVERT( bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)
WHERE [database_id] > 4
AND [database_id] <> 32767
OR [database_id] = 2
ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);
And compatability level:
-- Recovery model, log reuse wait description, log file size, log usage size (Query 19) (Database Properties)
-- and compatibility level for all databases on instance
SELECT db.[name] AS [Database Name], db.recovery_model_desc AS [Recovery Model],
db.log_reuse_wait_desc AS [Log Reuse Wait Description],
ls.cntr_value AS [Log Size (KB)], lu.cntr_value AS [Log Used (KB)],
CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log Used %],
db.[compatibility_level] AS [DB Compatibility Level],
db.page_verify_option_desc AS [Page Verify Option], db.is_auto_create_stats_on, db.is_auto_update_stats_on,
db.is_auto_update_stats_async_on, db.is_parameterization_forced,
db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,
db.is_auto_close_on, db.is_auto_shrink_on, db.is_cdc_enabled
FROM sys.databases AS db WITH (NOLOCK)
INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK)
ON db.name = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK)
ON db.name = ls.instance_name
WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%'
AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
AND ls.cntr_value > 0 OPTION (RECOMPILE);
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply