Find database file size in SQL Server instance

Find database file size in SQL Server instance

I used find database file size so many times a day for different databases in different instance/server. I can go to SSMS and find out the same from database property from GUI but I am script buddy and would like to keep each possible script handy so that I can quickly find out the information what I want.

I have used system view “sysaltfiles” from “master” database which keeps information about each and every database file available in current SQL Server instance. We actually get total number of pages contained in each database file and later on we have to convert number of pages to MB.

Here is the very small yet very useful script to find the database file size in SQL Server.

[sourcecode language=”sql”]SELECT
DB_NAME(dbid) AS DatabaseName
,Name as LogicalFileName
,CASE WHEN GroupID=1 THEN ‘Data’ ELSE ‘Log’ END AS FileType
,FileName as FilePath
,size as TotalPage
,(Size*8192E)/1048576 AS FileSizeInMB
FROM
master..SysAltFiles[/sourcecode]

There are few different way of getting same information, which way do you prefer to use? If you have any other script, do share it!!!!

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Sharing IT tips at “Quick Learn

Note: Microsoft Books online is a default reference of all articles

Keep watch on your database file size in SQL Server 2008/2005

Keep watch on your database file size in SQL Server 2008/2005

Well, in my previous article, I saw the method of how to keep watch on your disk drive (Click here to view), how to get information about your data and log file size as those are the very important task of DBA. Today, I am going to show one more important job which DBA simply can’t ignore. DBA needs to keep watch on every data and log file of databases server wide.  If it reaches at certain limits, DBA should be notified by email immediately so that s/he can take any action regarding this. Let me show you one very simple script which can make this task very easy for DBA.

Here is the script with Cursor:

 

[sourcecode language=”sql”]DECLARE @DiskSpace bigint
DECLARE @DataBaseName VARCHAR(50)
DECLARE @Name VARCHAR(50)
DECLARE @DriveLetter CHAR(1)
DECLARE @DiskSize bigint
Declare @fileName varchar(max)
SET   @DiskSpace = 1
DECLARE DriveSpace CURSOR FAST_FORWARD FOR

SELECT
databasename=DB_NAME(dbid),
name,
filename,
drive=LEFT(filename,1),
[size]=convert(bigint,[size])*8/1024
FROM
sysaltfiles
ORDER BY
dbid

OPEN
DriveSpace
FETCH NEXT FROM
DriveSpace
INTO
@DataBaseName, @Name,@fileName, @DriveLetter, @DiskSize

WHILE
(@@FETCH_STATUS = 0)
BEGIN
IF
@DiskSize > @DiskSpace
BEGIN
DECLARE @mes VARCHAR(MAX)
SET
@mes=’Hi Ritesh, ‘ + @Name + ‘ file of ‘ + @DataBaseName + ‘ database exceeds 1000 MB limit, current size is ‘ + cast(@DiskSize as varchar(10)) + ‘ and file name is ‘ + @fileName
EXEC
msdb..sp_send_dbmail
@recipients = ‘Rits4Friends@gmail.com’,
@subject= ‘Drive is about to full’,
@body= @mes
End

FETCH NEXT FROM
DriveSpace
INTO
@DataBaseName, @Name,@fileName, @DriveLetter, @DiskSize
End

CLOSE DriveSpace
DEALLOCATE DriveSpace
GO[/sourcecode]

All you need to do is, keep this script in JOBS and run that job once or twice everyday. If you don’t have database email configured than you could check this by remove comment before “Print” statement in script, don’t forget to comment email sending code, if you don’t have email configured in database.
 

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles .