|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Server Disk Space
Is there a reliable way to get the amount of disk space available on a
computer running SQL Server? xp_fixeddrives was a start but since it is undocumented and doesn't appear to work with a remote 2005 server, it isn't a complete solution. I need something that works for - local and remote servers - SQL Server 2000, 2005 and 2008 Knowing the maximum database size simply isn't enough because database may indicate unlimited or an amount greater than physical disk space. Mark Atlanta GA I use a OLE_Automation call which will work on SQL 2000, 2005, and 2008 if
you enable the OLE_Automation in the Surface Area Configuration for SQL Server 2005/2008. The code is available on the following link: http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=DiskSpaceMon&referringTitle=Home Show quoteHide quote "John Bigbooty" <BOGUSevileye0702BOGUS@bellsouth.netBOGUS> wrote in message news:4936934a.108348781@news1.newscene.com... > Is there a reliable way to get the amount of disk space available on a > computer running SQL Server? > > xp_fixeddrives was a start but since it is undocumented and doesn't > appear to work with a remote 2005 server, it isn't a complete > solution. > > I need something that works for > > - local and remote servers > > - SQL Server 2000, 2005 and 2008 > > Knowing the maximum database size simply isn't enough because database > may indicate unlimited or an amount greater than physical disk space. > > Mark > Atlanta GA Hi,
I just tried XP_FIXEDDRIVES in remote servers in SQL 2000/2005. in SQL 2005 it works fine when used as below. - Connection made to the remote SQL Server and EXEC MASTER.dbo.Xp_fixeddrives - Current SQL connection but remote server is a linked server. EXEC [LINKED SERVER].MASTER.dbo.Xp_fixedDrives or XP_CMDSHELL can be used as below but care should be taken to ensure that security accounts are the same in both servers so it can connect to the remote drive. DROP TABLE #TEMP Create table #TEMP (Output TEXT) INSERT INTO #TEMP ([Output]) EXEC XP_CMDSHELL 'DIR \\RemotePC\C$' -- Remote drive -- OR EXEC XP_CMDSHELL 'DIR C:' -- Local SELECT * FROM #TEMP WHERE [OUTPUT] Like '%BYTES FREE%' Best Regards, Roshan Jayalath Show quoteHide quote "John Bigbooty" wrote: > Is there a reliable way to get the amount of disk space available on a > computer running SQL Server? > > xp_fixeddrives was a start but since it is undocumented and doesn't > appear to work with a remote 2005 server, it isn't a complete > solution. > > I need something that works for > > - local and remote servers > > - SQL Server 2000, 2005 and 2008 > > Knowing the maximum database size simply isn't enough because database > may indicate unlimited or an amount greater than physical disk space. > > Mark > Atlanta GA > I tried master.dbo.xp_fixeddrives again. It doesn't work for my remote
non-linked server. Although the command completes without error, no rows are returned. The xp_cmdshell isn't an option. This is a multi-user commercial application and I cannot ask admins to open up their disk drives to all users. Mark Atlanta GA On Tue, 2 Dec 2008 20:04:05 -0800, Roshan Jayalath <RoshanJayal***@discussions.microsoft.com> wrote: Show quoteHide quote >Hi, > >I just tried XP_FIXEDDRIVES in remote servers in SQL 2000/2005. in SQL 2005 >it works fine when used as below. > > - Connection made to the remote SQL Server and EXEC MASTER.dbo.Xp_fixeddrives > > - Current SQL connection but remote server is a linked server. EXEC [LINKED >SERVER].MASTER.dbo.Xp_fixedDrives > >or XP_CMDSHELL can be used as below but care should be taken to ensure that >security accounts are the same in both servers so it can connect to the >remote drive. > >DROP TABLE #TEMP >Create table #TEMP (Output TEXT) >INSERT INTO #TEMP ([Output]) >EXEC XP_CMDSHELL 'DIR \\RemotePC\C$' -- Remote drive >-- OR EXEC XP_CMDSHELL 'DIR C:' -- Local > >SELECT * FROM #TEMP WHERE [OUTPUT] Like '%BYTES FREE%' > >Best Regards, >Roshan Jayalath > >"John Bigbooty" wrote: > >> Is there a reliable way to get the amount of disk space available on a >> computer running SQL Server? >> >> xp_fixeddrives was a start but since it is undocumented and doesn't >> appear to work with a remote 2005 server, it isn't a complete >> solution. >> >> I need something that works for >> >> - local and remote servers >> >> - SQL Server 2000, 2005 and 2008 >> >> Knowing the maximum database size simply isn't enough because database >> may indicate unlimited or an amount greater than physical disk space. >> >> Mark >> Atlanta GA >> If OLE Automation isn't an option as well, you can create a SQL Agent task
that calls a VBScript in the OS that uses WMI to query the data and a OLE DB connection to load it back into a SQL Table. The sp_start_job can be used to kick the job off. Show quoteHide quote "John Bigbooty" <BOGUSevileye0702BOGUS@bellsouth.netBOGUS> wrote in message news:493990cf.173249937@news1.newscene.com... >I tried master.dbo.xp_fixeddrives again. It doesn't work for my remote > non-linked server. Although the command completes without error, no > rows are returned. > > The xp_cmdshell isn't an option. This is a multi-user commercial > application and I cannot ask admins to open up their disk drives to > all users. > > Mark > Atlanta GA > > On Tue, 2 Dec 2008 20:04:05 -0800, Roshan Jayalath > <RoshanJayal***@discussions.microsoft.com> wrote: > >>Hi, >> >>I just tried XP_FIXEDDRIVES in remote servers in SQL 2000/2005. in SQL >>2005 >>it works fine when used as below. >> >> - Connection made to the remote SQL Server and EXEC >> MASTER.dbo.Xp_fixeddrives >> >> - Current SQL connection but remote server is a linked server. EXEC >> [LINKED >>SERVER].MASTER.dbo.Xp_fixedDrives >> >>or XP_CMDSHELL can be used as below but care should be taken to ensure >>that >>security accounts are the same in both servers so it can connect to the >>remote drive. >> >>DROP TABLE #TEMP >>Create table #TEMP (Output TEXT) >>INSERT INTO #TEMP ([Output]) >>EXEC XP_CMDSHELL 'DIR \\RemotePC\C$' -- Remote drive >>-- OR EXEC XP_CMDSHELL 'DIR C:' -- Local >> >>SELECT * FROM #TEMP WHERE [OUTPUT] Like '%BYTES FREE%' >> >>Best Regards, >>Roshan Jayalath >> >>"John Bigbooty" wrote: >> >>> Is there a reliable way to get the amount of disk space available on a >>> computer running SQL Server? >>> >>> xp_fixeddrives was a start but since it is undocumented and doesn't >>> appear to work with a remote 2005 server, it isn't a complete >>> solution. >>> >>> I need something that works for >>> >>> - local and remote servers >>> >>> - SQL Server 2000, 2005 and 2008 >>> >>> Knowing the maximum database size simply isn't enough because database >>> may indicate unlimited or an amount greater than physical disk space. >>> >>> Mark >>> Atlanta GA >>> > I bit more follow-up. It appears xp_fixeddrives on SQL 2005 requires
that the login is part of the sysadmin role. That is not an option for my application. Mark Atlanta GA On Tue, 2 Dec 2008 20:04:05 -0800, Roshan Jayalath <RoshanJayal***@discussions.microsoft.com> wrote: Show quoteHide quote >Hi, > >I just tried XP_FIXEDDRIVES in remote servers in SQL 2000/2005. in SQL 2005 >it works fine when used as below. > > - Connection made to the remote SQL Server and EXEC MASTER.dbo.Xp_fixeddrives > > - Current SQL connection but remote server is a linked server. EXEC [LINKED >SERVER].MASTER.dbo.Xp_fixedDrives > >or XP_CMDSHELL can be used as below but care should be taken to ensure that >security accounts are the same in both servers so it can connect to the >remote drive. > >DROP TABLE #TEMP >Create table #TEMP (Output TEXT) >INSERT INTO #TEMP ([Output]) >EXEC XP_CMDSHELL 'DIR \\RemotePC\C$' -- Remote drive >-- OR EXEC XP_CMDSHELL 'DIR C:' -- Local > >SELECT * FROM #TEMP WHERE [OUTPUT] Like '%BYTES FREE%' > >Best Regards, >Roshan Jayalath > >"John Bigbooty" wrote: > >> Is there a reliable way to get the amount of disk space available on a >> computer running SQL Server? >> >> xp_fixeddrives was a start but since it is undocumented and doesn't >> appear to work with a remote 2005 server, it isn't a complete >> solution. >> >> I need something that works for >> >> - local and remote servers >> >> - SQL Server 2000, 2005 and 2008 >> >> Knowing the maximum database size simply isn't enough because database >> may indicate unlimited or an amount greater than physical disk space. >> >> Mark >> Atlanta GA >> Hi,
What if you run xp_fixeddrives as scheduled jobs in local servers and update a local table and later onwards query the table over the network. ? Best Regards, Roshan Jayalath Show quoteHide quote "John Bigbooty" wrote: > I bit more follow-up. It appears xp_fixeddrives on SQL 2005 requires > that the login is part of the sysadmin role. That is not an option for > my application. > > Mark > Atlanta GA > > > On Tue, 2 Dec 2008 20:04:05 -0800, Roshan Jayalath > <RoshanJayal***@discussions.microsoft.com> wrote: > > >Hi, > > > >I just tried XP_FIXEDDRIVES in remote servers in SQL 2000/2005. in SQL 2005 > >it works fine when used as below. > > > > - Connection made to the remote SQL Server and EXEC MASTER.dbo.Xp_fixeddrives > > > > - Current SQL connection but remote server is a linked server. EXEC [LINKED > >SERVER].MASTER.dbo.Xp_fixedDrives > > > >or XP_CMDSHELL can be used as below but care should be taken to ensure that > >security accounts are the same in both servers so it can connect to the > >remote drive. > > > >DROP TABLE #TEMP > >Create table #TEMP (Output TEXT) > >INSERT INTO #TEMP ([Output]) > >EXEC XP_CMDSHELL 'DIR \\RemotePC\C$' -- Remote drive > >-- OR EXEC XP_CMDSHELL 'DIR C:' -- Local > > > >SELECT * FROM #TEMP WHERE [OUTPUT] Like '%BYTES FREE%' > > > >Best Regards, > >Roshan Jayalath > > > >"John Bigbooty" wrote: > > > >> Is there a reliable way to get the amount of disk space available on a > >> computer running SQL Server? > >> > >> xp_fixeddrives was a start but since it is undocumented and doesn't > >> appear to work with a remote 2005 server, it isn't a complete > >> solution. > >> > >> I need something that works for > >> > >> - local and remote servers > >> > >> - SQL Server 2000, 2005 and 2008 > >> > >> Knowing the maximum database size simply isn't enough because database > >> may indicate unlimited or an amount greater than physical disk space. > >> > >> Mark > >> Atlanta GA > >> > > That sounds like a possibility. Then my question changes to one of
trying to figure out how to schedule jobs on remote server via code and ideally how to run the job on demand. I'll look into it. Thanks Mark Atlanta GA On Thu, 4 Dec 2008 01:09:01 -0800, Roshan Jayalath <RoshanJayal***@discussions.microsoft.com> wrote: Show quoteHide quote >Hi, > >What if you run xp_fixeddrives as scheduled jobs in local servers and update >a local table and later onwards query the table over the network. ? > >Best Regards, >Roshan Jayalath > >"John Bigbooty" wrote: > >> I bit more follow-up. It appears xp_fixeddrives on SQL 2005 requires >> that the login is part of the sysadmin role. That is not an option for >> my application. >> >> Mark >> Atlanta GA >> >> >> On Tue, 2 Dec 2008 20:04:05 -0800, Roshan Jayalath >> <RoshanJayal***@discussions.microsoft.com> wrote: >> >> >Hi, >> > >> >I just tried XP_FIXEDDRIVES in remote servers in SQL 2000/2005. in SQL 2005 >> >it works fine when used as below. >> > >> > - Connection made to the remote SQL Server and EXEC MASTER.dbo.Xp_fixeddrives >> > >> > - Current SQL connection but remote server is a linked server. EXEC [LINKED >> >SERVER].MASTER.dbo.Xp_fixedDrives >> > >> >or XP_CMDSHELL can be used as below but care should be taken to ensure that >> >security accounts are the same in both servers so it can connect to the >> >remote drive. >> > >> >DROP TABLE #TEMP >> >Create table #TEMP (Output TEXT) >> >INSERT INTO #TEMP ([Output]) >> >EXEC XP_CMDSHELL 'DIR \\RemotePC\C$' -- Remote drive >> >-- OR EXEC XP_CMDSHELL 'DIR C:' -- Local >> > >> >SELECT * FROM #TEMP WHERE [OUTPUT] Like '%BYTES FREE%' >> > >> >Best Regards, >> >Roshan Jayalath >> > >> >"John Bigbooty" wrote: >> > >> >> Is there a reliable way to get the amount of disk space available on a >> >> computer running SQL Server? >> >> >> >> xp_fixeddrives was a start but since it is undocumented and doesn't >> >> appear to work with a remote 2005 server, it isn't a complete >> >> solution. >> >> >> >> I need something that works for >> >> >> >> - local and remote servers >> >> >> >> - SQL Server 2000, 2005 and 2008 >> >> >> >> Knowing the maximum database size simply isn't enough because database >> >> may indicate unlimited or an amount greater than physical disk space. >> >> >> >> Mark >> >> Atlanta GA >> >> >> >> I would suggest that using SQL to check the disk space is really the wrong
approach. If you handled this through code (possibly a vbscript to query WMI) you'll be in better shape. Keep in mind that in some environments you'll encounter issues using many methods if people use volume mount points. A WMI has been reliable for me in the past to retrieve free space in these circumstances. Also be careful for the fact that some DBA's like to put system files on one drive, data files on another and log files on another drive. You can check for alot of those variations in sys.master_files. Goodluck! -Mike Show quoteHide quote "John Bigbooty" <BOGUSevileye0702BOGUS@bellsouth.netBOGUS> wrote in message news:4936934a.108348781@news1.newscene.com... > Is there a reliable way to get the amount of disk space available on a > computer running SQL Server? > > xp_fixeddrives was a start but since it is undocumented and doesn't > appear to work with a remote 2005 server, it isn't a complete > solution. > > I need something that works for > > - local and remote servers > > - SQL Server 2000, 2005 and 2008 > > Knowing the maximum database size simply isn't enough because database > may indicate unlimited or an amount greater than physical disk space. > > Mark > Atlanta GA  
Other interesting topics
Max server memory being ignored
Re-attaching database Consolidate datafiles to one drive SQL 2k5-32bit >SQL 2k5-64bit Taking database offline taking long time Maintenence Cleanup Task does not delete backup files 32 vs 64 bit SQL Server hotfixes Re: Odd Identity Behavior in Enterprise Manager Bug or Bad Practice on my part? sql server profiler: what does this combination of completed batches mean |
|||||||||||||||||||||||
