June 2, 2009 at 5:09 am
Hi
i have some 20 databases in sqlserver2005 how i need to check total size of all databases in the server
individual size i can check with sp_helpdb
but over all memory occupied by all the databases present in the server
and
how to tune the each the database for performence tunung
Thanks in advance
June 2, 2009 at 6:14 am
sivark1 (6/2/2009)
Hi
i have some 20 databases in sqlserver2005 how i need to check total size of all databases in the server
individual size i can check with sp_helpdb
There are different ways on how to do that and if you look through the scripts section on this site you should find a couple of solutions.
Here's on example using the performance counters:
SELECT counter_name, SUM(cntr_value )/1024.0 as [Totalsize (Mb)]
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME LIKE '%:Databases%'
AND counter_name IN ('Data File(s) Size (KB)', 'Log File(s) Size (KB)')
AND instance_name '_Total'
GROUP BY counter_name
but over all memory occupied by all the databases present in the server
Again the best source should be the performance counters.
SELECT counter_name, SUM(cntr_value )/1024.0 as [Total Server Memory (Mb)]
FROM sys.dm_os_performance_counters
WHERE counter_name ='Total Server Memory (KB)'
GROUP BY counter_name
how to tune the each the database for performence tunung
That really depends. Every database is different and has different performance issues. There are tons of articles about performance tuning but not one solution for all databases. As a start you could check your index usage and if SQL Server reports any missing indexes.
http://msdn.microsoft.com/en-us/library/ms345524(SQL.90).aspx. But I have to warn you, don't just implement any index SQL Server is proposing. Not every vendor allows you to make changes to their database and also a lot of the suggested indexes will be duplicates or only help one or two specific queries.
A better option is often to create a workload file using Profiler and then use the Database Tuning Advisor (DTA) to analyze the workload.
[font="Verdana"]Markus Bohse[/font]
June 4, 2009 at 12:27 am
For find out DB file size u can use this script
SET NOCOUNT ON
DECLARE @DBName VarChar(100)
CREATE TABLE #FileList
(
DBName VarChar(100),
FileName VarChar(500),
FileSize BigInt
)
DECLARE curDB CURSOR
FORSELECT Name FROM sys.databases
OPEN curDB
FETCH NEXT FROM curDB INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('INSERT INTO #FileList (DBName,FileName,FileSize)
SELECT ''' + @DBName + ''',Physical_Name,(Size * 8/1024) SizeInMB
FROM ' + @DBName + '.sys.database_files')
FETCH NEXT FROM curDB INTO @DBName
END
CLOSE curDB
DEALLOCATE curDB
SELECT DBName,FileName,FileSize FileSizeInMB FROM #FileList
DROP TABLE #FileList
SET NOCOUNT OFF
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply