How do we monitor our SQL Server file data? Acknowledge the size and the growth is an important thing for all of us. Knowing the condition of the machine is critical in term of Database Server need a specific condition in its physical infrastructure – the harddisk/physical device should be in a sufficient free space. The growth of the database and its current used space is also very critical to acknowledge because it will provide us knowledge how we interact with the data growth, decide, and how we maintain them. Not only critical for the database itself, but also for the Operating System as well. You can imagine if one of our database files growing in 30MB per daily, and someday in the future it hits Windows Server with hundreds MB left in space? How come??
Of course, every database is living on its own speed. The very active growth is everywhere as well as the ordinary one, as you can mention any retail or banking database or your sms content business which growing in 100 thousand records daily. And in other plant, the medical record of certain hospital may require no more than thousand rows per day.
We should also take care of the ratio between the maximum capacity and the growth per day. The blue one is the most acceptable thing. The orange is in danger speed.
System SP or Systables?
SQL Server (2000 or 2005) provide us some system Stored Procedures and tables to identify the need above. Day by day there always come a point for us have the choices. It’s pretty good to have the built-in SP ahead, but you want to go deep down, knowing the very basic entity of the information is very much valuable. I would like to say that for simple need, we can depend only for the the system Stored Procedure. You can run sp_helpdb - for example – if you just need to check one server machine. But How we do that if we want to check several machine at the same time? I believe we will be more happy by doing some SELECT query on sysdatabase tables.
Of course, we also need to put other syntax combination in the mind, to make all these stuffs possible.
In SQL Server 2000 and SQL Server 2005 I found the are two beautiful object to get this out. One is master..xp_fixeddrives stored procedure. and the other is sysfile table.
Server Space
First, what in my mind is how to check the physical space for the server?
We have master..xp_fixeddrives on the table. Running this will give us all free space information on the current machine.
EXEC master..xp_fixeddrives
And the result is
drive MB free
----- -----------
C 1053
D 16438
(2 row(s) affected)
To check other machine from specific/other location, we just type the IP/domain name of the SQL Instance to be monitored.
EXEC [10.2.5.32].master..xp_fixeddrives
Imagine that we have one instance as monitoring central engine, and n-number of server being monitored.
We can create a temporary table/variable table/ or physical table to save the result of rows above.
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#disk_free_space]'))
DROP TABLE #disk_free_space
CREATE TABLE #disk_free_space (
DriveLetter CHAR(1) NOT NULL,
FreeMB INTEGER NOT NULL)
INSERT INTO #disk_free_space
EXEC [10.2.5.32].master..xp_fixeddrives
And then create a variable table to add other relevant information
declare @TotalSpace table(
Server varchar(100),
Drive varchar(10),
Alloc int
)
Insert into @TotalSpace
SELECT 'Server-1 - > [10.2.5.32]' , * FROM #disk_free_space
And execute the query the get the picture in the modest way.
Select Server, Drive, RTRIM(RIGHT(SPACE(22) + convert(varchar(20), convert(money, Alloc),1),22)) + ' MB' As Size from @TotalSpace
You can get all server space by inserting each result of master..xp_fixeddrives as depicted below.
Server1 - > [10.2.5.31] C 15,958.00 MB
Server1 - > [10.2.5.31] D 83,088.00 MB
Server2 - > [10.2.5.32] C 52,973.00 MB
Server2 - > [10.2.5.32] D 26,635.00 MB
Server3 - > [10.2.5.34] C 20,597.00 MB
Server3 - > [10.2.5.34] D 144,449.00 MB
Space Used by Each Database
After we identify the total space of the volume in the machine.. All databases occupied is the next target we need to know. We can do this by query SELECT command to sysfile table.
Let say I just want to see the file name , the location of the file, and the size.
SELECT fileid, name, filename, size, growth from sysfiles
And because the size in page (8KB) we need to convert this into MB (usual unit). Multiply this by
SELECT fileid, name, filename, size * 8 /1000 as [size – MB] , growth from sysfiles
The result would be:
This query will work on the current database (wtrack). To get the information to a specific database you should spell the database name first.
SELECT fileid, name, filename, size * 8 /1000 as [size – MB] , growth
from [WTrack].dbo.sysfiles
We need to retrieve all database name using sysdatabases table.
select name from master.dbo.sysdatabases
By combining these two system tables and using cursor definition we can grab all databases size, as this script below. Please notice some system database name exclude from the query.
Declare @sql varchar(6600)
Declare @nama varchar(30)
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#mytemp]'))
DROP TABLE #mytemp
create Table #mytemp
(
servername varchar(20), fileid smallint, name nchar(128), filename nchar(260), ukuran int, size varchar(30), growth int
)
Declare namaDB Cursor For
select name from master.dbo.sysdatabases where name not in ('master','model','msdb','tempdb')
AND name in ('zmyTools','ztes2','ztestdaily')
order by name
Open namaDB
FETCH NEXT FROM namaDB into @nama
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql ='SELECT ''' + @servername + ''', fileid, name, filename, size * 8 /1000, '
SET @sql = @sql + ' RTRIM(RIGHT(SPACE(22) + convert(varchar(20), convert(money, size *8/1000),1),22)) + '' MB'' as mySize, growth FROM [' + @servername + '].[' + @nama + '].dbo.sysfiles '
SET @sql = ' INSERT INTO #mytemp ' + @sql
EXEC(@sql)
FETCH NEXT FROM namaDB into @nama
END
Close namaDB
deallocate namaDB
Select * from #mytemp
No comments:
Post a Comment