How full are the files?

• dan-572483

  SSCoach

  Points: 15870

  More actions

  July 21, 2011 at 2:40 pm

  #244549

  I'd like to be able to run a command or query to list the files within a database and how full each one is. I want to be able see which ones should be increased in size in advance to they don't autogrow during uptime.

  I've been looking through various sp_help and DMVs and while there are several ways to list the current file size and autogrowth settings, I haven't yet found how to list the data size vrs file size, or percent full.

  What is the easiest way to do this?

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  July 21, 2011 at 3:30 pm

  #1356787

  This will tell you the space used inside all database files, and much more.

  Get Server Database File Information

  http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058

• JamesMorrison

  Hall of Fame

  Points: 3213

  More actions

  July 21, 2011 at 5:41 pm

  #1356828

  Here is what I have on my "DBA" database on every production server.

  CREATE TABLE [dbo].[DatabaseAvailableSpace](

  [ID] [int] IDENTITY(1,1) NOT NULL,

  [DatabaseName] [nvarchar](50) NULL,

  [TotalSpaceInMB] [int] NULL,

  [UsedSpaceInMB] [int] NULL,

  [FreeSpaceInMB] [int] NULL,

  [TimeCollected] [smalldatetime] NOT NULL

  ) ON [PRIMARY]

  GO

  ALTER TABLE [dbo].[DatabaseAvailableSpace] ADD CONSTRAINT [DF_DatabaseAvailableSpace_TimeCollected] DEFAULT (getdate()) FOR [TimeCollected]

  GO

  Then after that I have a job on each server. It has a bunch of steps that goes through each database on the server and runs this exact same code for each step. The only change in each step is the drop down box for which database it is using. On my production servers there are 14 databases each, so 14 steps. Takes about 5-10 seconds to complete all of them combined.

  This job runs once every 15 minutes to record the data results to my DBA.dbo.DatabaseAvailableSpace table.

  Insert into DBA.dbo.DatabaseAvailableSpace

  (DatabaseName,TotalSpaceInMB,UsedSpaceInMB,FreeSpaceInMB)

  select

  name,

  cast((size/128.0) as int) as TotalSpaceInMB,

  cast((cast(fileproperty(name, 'SpaceUsed') as int)/128.0) as int) as UsedSpaceInMB,

  cast((size/128.0 - cast(fileproperty(name, 'SpaceUsed') AS int)/128.0) as int) as FreeSpaceInMB

  from sys.database_files

  The results are great over time. It shows EXACTLY what is happening to your .MDF and .LDF files.

  For example, here are the recent rows showing what happened to my log file during re-indexing and an autogrow.

  (Database names changed to protect the innocent)

  

  select * from DBA.dbo.DatabaseAvailableSpace

  where DatabaseName = 'Database_log'

  Order by TimeCollected DESC