Let me know the concept

• Vasantharajan

  Grasshopper

  Points: 10

  More actions

  November 24, 2009 at 3:45 am

  #137383

  hi all:

  I would like to know about the concept of DISK SPACE MONITORING IN SQL SERVER 2005. Plz gimme some website name or URL to clarify my doubts. or revert back to me if you know that concept.

  Thanks in advance.

• Dave Ballantyne

  SSC-Dedicated

  Points: 33667

  More actions

  November 24, 2009 at 3:50 am

  #1083350

  Im assuming you've already googled , so what doubts do you have ?

  ---

  
  Clear Sky SQL
  My Blog[/url]

• Atul DBA

  Hall of Fame

  Points: 3610

  More actions

  November 24, 2009 at 6:03 am

  #1083390

  There are many ways to monitor the disk space utilization in SQL Server 2005. You can use the SP_SPACESUSED procedure to get the space used by databases.

  You may create a table in your databse and store the daily/weekly disk value to monitor and analyse the database growth pattern. Something like below code will help you.

  Execute the following script in Query Editor to create a table as size log and deploy the insert script into SQL Server Agent for daily, weekly, etc. execution. Disk drive free space can be obtained by sys.xp_fixeddrives and xp_cmdshell commands.

  use AdventureWorks

  go

  create table DBFileSize(

  DBFileSizeID int identity(1,1) primary key,

  [Date] datetime default (getdate()),

  Name varchar(30),

  FileID int,

  FileName varchar(100),

  FileGroup varchar(50),

  FileSize varchar(20),

  MaxSize varchar(20),

  Growth varchar(20),

  Usage varchar(15)

  )

  go

  insert DBFileSize(Name, FileID, FileName, FileGroup, FileSize, MaxSize, Growth, Usage)

  exec sp_helpfile

  go

  select * from DbFileSize

  go

  http://www.sqlusa.com/bestpractices2005/performancetuning/monitordiskusage/

  Read more about disk space management from this url :

  http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1252873_mem1,00.html