x

how to find the size of page file in sql server 2005

Hi can any one help me find out size of page file in sql server 2005

more ▼

asked Jul 20, 2010 at 06:32 AM in Default

avatar image

ramesh 1
2.2k 66 69 73

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Assuming your server has "xp_cmdshell" and "OLE Automation Procedures" enabled, then in theory it's a matter of searching each drive for "pagefile.sys" in the root directory.

This code is what I use to gather disk space information (including pagefile) (props to David Bird's articles on "SQLOverview" - http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/61621/ shamelessly adapted and enhanced by me):

 IF EXISTS
               (SELECT *
                FROM    tempdb.dbo.sysobjects
                WHERE   id = OBJECT_ID(N'[tempdb].[dbo].[HoldDiskSpace]')
                )
        DROP TABLE [tempdb].[dbo].[HoldDiskSpace]
 GO
 
 CREATE TABLE [tempdb].[dbo].[HoldDiskSpace] (
             [Server] nvarchar(128),
             Drive char(1) PRIMARY KEY,
             [Free(MB)] int NULL,
             [Total(MB)] int NULL,
             [Free(%)] tinyint NULL,
             [SwapFileSize(MB)] int NULL,
             [DateChecked] [datetime] NOT NULL CONSTRAINT [DF_HoldDiskSpace_Date Checked]  DEFAULT (getdate()),
 )
 
 SET NOCOUNT ON
 DECLARE @hr INT
 DECLARE @fso INT
 DECLARE @drive VARCHAR(3)
 DECLARE @odrive INT
 DECLARE @TotalSize VARCHAR(20)
 DECLARE @MB BIGINT ; SET @MB = 1048576
 DECLARE @SwapFile INT
 DECLARE @PageFileName varchar(20)
 DECLARE @PFExists INT
 
 INSERT [tempdb].[dbo].[HoldDiskSpace](Drive,[Free(MB)])
  EXEC master.dbo.xp_fixeddrives
 -- This is a VB method
 EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
 IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
 
 DECLARE dcur CURSOR LOCAL FAST_FORWARD
    FOR SELECT drive from [tempdb].[dbo].[HoldDiskSpace]
    ORDER by drive
 
 OPEN dcur
 FETCH NEXT FROM dcur INTO @drive
 WHILE @@FETCH_STATUS=0
   BEGIN
   EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
   IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
   EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
   IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
      UPDATE [tempdb].[dbo].[HoldDiskSpace]
       SET [Total(MB)]=@TotalSize/@MB,
           [Server] = @@SERVERNAME ,
           [Free(%)] = CAST(([Free(MB)]/(@TotalSize/@MB*1.0))*100.0 as int)
       WHERE drive=@drive
   SELECT @PageFileName = @drive + ':\Pagefile.sys'
   EXEC @hr = sp_OAMethod @fso, 'FileExists', @PFExists OUT, @PageFileName
   --select @PageFileName, @PFExists
   IF @PFExists = 1
   BEGIN
   EXEC @hr = sp_OAMethod @fso, 'GetFile', @SwapFile out, @PageFileName
   If @hr <> 0
          EXEC sp_OAGetErrorInfo @SwapFile
   ELSE
     BEGIN
       EXEC @hr = sp_OAGetProperty @SwapFile, 'Size', @TotalSize out
       IF @hr <> 0 EXEC sp_OAGetErrorInfo @SwapFile
       UPDATE [tempdb].[dbo].[HoldDiskSpace]
       SET [SwapFileSize(MB)] = @TotalSize/@MB WHERE drive = @drive
     END
     END
   FETCH NEXT FROM dcur INTO @drive
 END
 CLOSE dcur
 DEALLOCATE dcur
 
 EXEC @hr=sp_OADestroy @fso
 IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

Then, when you're done, simply run "SELECT * FROM [tempdb].[dbo].[HoldDiskSpace]"

more ▼

answered Jul 20, 2010 at 06:55 AM

avatar image

ThomasRushton ♦♦
42k 20 51 53

Two notes: Not all pagefile.sys' are in the root of drives (long story about 32 bit O/S and > 4095 swap files) and this only shows how much space is consumed, not how much of the swap space is in use.

Jul 20, 2010 at 11:21 AM Blackhawk-17

Thanks for the tip - I'll have to figure out an alternative method. Damn. More work.

As for only showing how much space is consumed - that was the question.

Jul 20, 2010 at 02:00 PM ThomasRushton ♦♦

Just a little warning, there is a documented memory leak in sp_oacreate. I am not sure in which service pack it was fixed in. I can try to find it later, but today it is too late.

Jul 20, 2010 at 04:23 PM Håkan Winther

sp_OAMethod had a leak...

Fixed in SQL2000SP4, according to http://support.microsoft.com/kb/816937

And in CU2 for SQL2005SP2, according to http://support.microsoft.com/kb/937277 - first fixed version of the binary is 2005.90.2232.0

Jul 21, 2010 at 12:11 AM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2031

asked: Jul 20, 2010 at 06:32 AM

Seen: 694 times

Last Updated: Jul 20, 2010 at 06:32 AM

Copyright 2017 Redgate Software. Privacy Policy