I see you guys are adament that I must start a new post for the same topic. I find it interesting that we are in 2012 and from 2000 to this day MS still hasn't added the Total Drive Space to xp_fixeddrives through a SP. Maybe I'll start a new thread, hopefully someone has an actual working solution... Anyone want to punch me another blow? Go right ahead! It still does not resolve the Total Drive Space issue.

Paul Els (3/3/2012)I see you guys are adament that I must start a new post for the same topic. I find it interesting that we are in 2012 and from 2000 to this day MS still hasn't added the Total Drive Space to xp_fixeddrives through a SP. Maybe I'll start a new thread, hopefully someone has an actual working solution... Anyone want to punch me another blow? Go right ahead! It still does not resolve the Total Drive Space issue.

Not by me. I'd like to see the answer right here... no new post required. I think its interesting that a problem could go on for so long without a good answer.

--Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

I agree that this is a perfect topic to continue, love to look at the history without having to search related stuff up.

But here is another twist to this for everyone to think about. What about those situations that you cannot use any OLE automation or xp_cmdshell solutions? My company locks everything down due to strict security regulations and audit compliancy, so using something in the SQL Server TSQL scope is not necessarily a solution.

We really need to potentially think outside the box on solutions and look to expand our thoughts on other opportunities within SQL Server, maybe calling CLR, SSIS or something on those lines. I am really attempting to get creative here and not just stick with TSQL so any other thoughts are definitely welcome.

goaler (7/3/2012)What about those situations that you cannot use any OLE automation or xp_cmdshell solutions? My company locks everything down due to strict security regulations and audit compliancy,...

Heh... I love the irony of it all at companies like that because if they had actually set things up to correctly and securely use things like xp_CmdShell, their security would be bullet proof.

--Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

I have found a solution to this age old problem. Now I will just explain the cocept, in my next post I will give the code. I use it at a large bank. It requires that 1 DLL file (Basically you can compile the code yourself, if you like me you trust no one) and then put the DLL in a common folder on each server. I copy it to C:\Windows . Then this must be hooked into SQL. Then my tsql code runs against each instance in a loop. If you cycle through approx 2000 instances, some are bound not to have the .Net FW it requires. For that I handle it with a TRY...CATCH and just display the Free Space. Hence the Total Drive Space is just set to 0 for such servers. I could get creative and create a lookup table to replace the 0 with the size, but I did not want to do that, because some LUN Admin might change the size without me knowing. I would rather want to see 0 GB Total for servers with the .Net FW problem, so that I can address the .Net FW issue on such servers. I still use the xp_fixeddrives so I could not get away from this limitation yet.

Anyway, I have to run, but I will come back and what code I use to do the trick. I must say, I didn't write the CSharp code, but it works pretty good.

For those interested I will list the steps. It might be a bit over the top, but here goes...If you run into trouble, let me know where you're stuck and I will try to help you to get it going, but please try it on some Dev Servers first... My time is very limited, so only if you're really stuck, let me know. I advise you do this piece-meal (e.g. chunk by chunk on 4 dev servers to start with):

-- Next, you will need to copy the TotalDriveSpace.dll file to -- a common folder on each Server, e.g. C:\Windows-- So either compile the CSharp code and drop it somewhere -- in a shared folder (e.g. \\HostName\ShareName\FolderName\TotalDriveSize.dll)-- Or pop me an email (paul_els@hotmail.com) to send you the compiled TotalDriveSpace.dll file -- Then run this and with a bit of luck (and access rights) your SQL server-- will copy the file to it's C:\Windows folder. -- If this fails (e.g. Access Denied), contact your SysAdmin to copy it there for youexec master..xp_cmdshell 'copy \\HostName\ShareName\FolderName\TotalDriveSize.dll C:\Windows'

-- To verify that the file is in place you can execute a simple DOS Dir:exec master..xp_cmdshell 'dir C:\Windows\TotalDriveSize.dll'

On each each of my Servers I have a database in which I store my library code.This can contain Stored Procs, Functions, etc. I call it SQLMaintenance.So create a tiny blank database called SQLMaintenance on each of the 4 Dev Servers.

-- THEN SET IT TRUSTWORTHYALTER DATABASE SQLMaintenance SET TRUSTWORTHY ONGO

-- Now we create an assemly from the .dll--drop ASSEMBLY DiskSpaceCREATE ASSEMBLY DiskSpace FROM 'C:\WINDOWS\TotalDriveSize.dll'WITH PERMISSION_SET = UNSAFEGO

-- After all this "Hard" work, now it is time for the fun to start-- Below the first line shows for a named instance an example-- Below the second line shows for a default instance an exampleEXEC [DevServer1\InstanceName].SQLMaintenance.dbo.isp_DiskSpace @ServerName = 'DevServer1'EXEC [DevServer2].SQLMaintenance.dbo.isp_DiskSpace @ServerName = 'DevServer2'EXEC [DevServer3\InstanceName].SQLMaintenance.dbo.isp_DiskSpace @ServerName = 'DevServer3'EXEC [DevServer4\InstanceName].SQLMaintenance.dbo.isp_DiskSpace @ServerName = 'DevServer4'

Another option would be to try and do this whole thing via SSIS. The advantage there is that you don't need to create linked servers. You can e.g. create a table with all the Instance Names. Then cycle through the list and connect to each server in a Loop and execute code against it on the fly.

It is my hope that this, although a lot of work, will help you guys to pull better reports.From a Central Server, which must also have a SQLMaitenance database you can now do a bit of magic and cycle through each server, to collect the Total Drive Space and Free Space and then build and send 1 email to a number of people to address it. Notice in the method below, to send an email I have to put the built message in a queue... you may have to handle email differently.

-- THE PURPOSE OF THIS SCRIPT IS TO POPULATE A TABLE WITH THE FREE SPACE PER DRIVE/LUN -- THEN TO BUILD A HTML EMAIL AND SEND IT -- TEST: EXEC SQLMaintenance.dbo.sp_Report_TotalDriveSpaceAndFreeSpace_via_email

DECLARE InstancesCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY -- MAKE THE CURSOR LIGHT WIGHT FOR -- Please pre-populate the Instances Table with a list of the instances you want to monitor select InstanceName from SQLMaintenance.dbo.Instances

OPEN InstancesCursor FETCH NEXT FROM InstancesCursor INTO @InstanceName

END TRY BEGIN CATCH -- RETRIEVE THE ERROR AND ADD IT TO THE MEMORY TABLE @ServerNameAndErrorDetails INSERT INTO @ServerNameAndErrorDetails SELECT @InstanceName, ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() as ErrorState, ERROR_PROCEDURE() as ErrorProcedure, ERROR_LINE() as ErrorLine, ERROR_MESSAGE() as ErrorMessage END CATCH -- PROCESS THE NEXT RECORD FETCH NEXT FROM InstancesCursor INTO @InstanceName END

CLOSE InstancesCursor DEALLOCATE InstancesCursor

-- ONLY SHOW ERROR IF THERE WERE ANY if (select COUNT(*) from @ServerNameAndErrorDetails) > 0 BEGIN select * from @ServerNameAndErrorDetails END

Like Jeff I use xp_cmdshell with PowerShell to query the Win32_Volume WMI class. You wouldn't need xp_cmdshell or PowerShell at all if SQL could issue WQL command, but alas. This gets both total capacity, label and free space information on both fixed drives and mount points: