T-SQL Query to find Drive space details

Here is a T-SQL Query to find Drive space details.
This query returns Total drive space, Used space on the drive, Free space on the drive along with the free space percentage.

This requires, enabling xp_cmdshell

Enable xp_cmdshell:

EXEC sp_configure 'show advanced options' , 1
GO
RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell' , 1
GO
RECONFIGURE
GO

Once xp_cmdshell is enabled, executing the below script will provide the details about the drives of the server that hosts the connected SQL Server Instance

 DECLARE @MOUNTVOL TABLE
 ( MOUNTVOLResult nVARCHAR(500)
  ,ExecCommand nVARCHAR(500))
 
INSERT INTO @MOUNTVOL (MOUNTVOLResult) 
EXEC XP_CMDSHELL 'MOUNTVOL'
 
DELETE @MOUNTVOL WHERE MOUNTVOLResult LIKE '%VOLUME%'
DELETE @MOUNTVOL WHERE MOUNTVOLResult IS NULL
DELETE @MOUNTVOL WHERE MOUNTVOLResult NOT LIKE '%:%'
DELETE @MOUNTVOL WHERE MOUNTVOLResult LIKE '%MOUNTVOL%'
DELETE @MOUNTVOL WHERE MOUNTVOLResult LIKE '%RECYCLE%'
 
UPDATE @MOUNTVOL SET ExecCommand = 'EXEC XP_CMDSHELL ''FSUTIL VOLUME DISKFREE ' + LTRIM(RTRIM(MOUNTVOLResult)) +''''
 
DECLARE @DRIVESpace TABLE
 ( DriveLetter VARCHAR(10)
  ,DriveInfo VARCHAR(100))
   
WHILE (SELECT COUNT(*) FROM @MOUNTVOL) <>0
BEGIN
 DECLARE @Command nVARCHAR(500), @DriveLetter nVARCHAR(10)
 Select @Command = ExecCommand, @DriveLetter= MOUNTVOLResult from @MOUNTVOL
 INSERT INTO @DRIVESpace (DriveInfo) Exec sp_executeSQL @Command
 UPDATE @DRIVESpace SET DriveLetter=@DriveLetter WHERE DriveLetter IS NULL
 DELETE FROM @MOUNTVOL WHERE ExecCommand=@Command
END
 
DECLARE @FinalResults TABLE
 ( DriveLetter nVARCHAR(10)
  ,[TotalDriveSpace(MB)] DECIMAL(18,2)
  ,[UsedSpaceOnDrive(MB)] AS ([TotalDriveSpace(MB)] - [FreeSpaceOnDrive(MB)])
  ,[FreeSpaceOnDrive(MB)] DECIMAL(18,2)
  ,[TotalDriveSpace(GB)] AS CAST(([TotalDriveSpace(MB)]/1024) AS DECIMAL(18,2))
  ,[UsedSpaceOnDrive(GB)] AS CAST((([TotalDriveSpace(MB)] - [FreeSpaceOnDrive(MB)])/1024) AS DECIMAL(18,2))
  ,[FreeSpaceOnDrive(GB)] AS CAST(([FreeSpaceOnDrive(MB)]/1024) AS DECIMAL(18,2))
  ,[%FreeSpace] AS CAST((([FreeSpaceOnDrive(MB)]/[TotalDriveSpace(MB)])*100) AS DECIMAL(18,2)))
 
INSERT INTO @FinalResults (DriveLetter, [TotalDriveSpace(MB)],[FreeSpaceOnDrive(MB)])
SELECT RTRIM(LTRIM(DriveLetter))
    ,[TotalDriveSpace(MB)] = SUM(CASE WHEN DriveInfo LIKE 'TOTAL # OF BYTES%' THEN CAST(SUBSTRING(DriveInfo, 32, 48) AS FLOAT) ELSE CAST(0 AS FLOAT) END)/1024/1024
    ,[FreeSpaceOnDrive(MB)] = SUM(CASE WHEN DriveInfo LIKE 'TOTAL # OF FREE BYTES%' THEN CAST(SUBSTRING(DriveInfo, 32, 48) AS FLOAT) ELSE CAST(0 AS FLOAT) END)/1024/1024
FROM @DRIVESpace
WHERE DriveInfo LIKE 'TOTAL # OF %'
GROUP BY DriveLetter
ORDER BY DriveLetter
 
SELECT * FROM @FinalResults

T-SQL Query to find IP Address of SQL Server

Here is a handy T-SQL script to find the IP Address of the SQL Server you are connected to.

SELECT CONNECTIONPROPERTY('local_net_address') AS [IP Address Of SQL Server]

SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') [Machine Name]
   ,SERVERPROPERTY('InstanceName') AS [Instance Name]
   ,LOCAL_NET_ADDRESS AS [IP Address Of SQL Server]
   ,CLIENT_NET_ADDRESS AS [IP Address Of Client]
 FROM SYS.DM_EXEC_CONNECTIONS 
 WHERE SESSION_ID = @@SPID

SQL Server Edition Upgrade

SQL Server edition upgrade is a process in which an SQL Server instance will be upgraded from one edition to another.
To find the supported paths for Version and Edition Upgrades, follow the below link to make sure to choose the right upgrade path.
http://msdn.microsoft.com/en-IN/library/ms143393.aspx

The steps in this post, shows the procedure to upgrade SQL Server 2008 R2 Standard Edition to Enterprise Edition.

Before the Upgrade:

Edition Upgrade Process:

1. Insert the Installation media and Run "setup.exe"
2. Go to "Maintenance" page and choose "Edition Upgrade"
3. This will now check and verify few rules which help in the smooth setup process. Click "Ok" in this screen
    
4. On clicking ok, the setup will now verify few more rules. Click "Next" in this screen
   
5. Enter the Product Key and click "Next"
   
6. Read the License Terms and accept it and click "Next"
   
7. Select the Instance that needs to be upgraded and Click "Next"
   
8. On clicking next in previous step, the setup verifies few rules related to upgrade, based on the result, fix any issues reported or click "Next"
   
9. In the summary screen, verify the actions, if any changes required click "Back" else click "Upgrade"
   
10. Once the Upgrade is completed, Click "Close"
    

After Upgrade:

Hiding User Databases

In my previous post "Hide System databases and objects", I had explained on how to hide the system databases and system objects. In this post I will explain how to hide "User Databases".

Hiding User Databases may not be necessary all the times, but in some cases when your SQL Server instance hosts mission critical databases to which the business puts a rule saying no one else apart from them should be able to see their database. (off course admins are exception)
For Example consider an SQL Server instance that hosts database for HR and Finance. In this case a HR user should not be able to see Finance Database and a Finance user should not be able to see HR database.

To simulate and test this,
I have created 2 databases named

1. HR
2. Finance

I have created 5 Users

1. HRAdmin -- Owner of HR Databases and Default DB is "HR"
2. HRReadWrite -- This user has read and write access on HR databases and Default DB is "HR"
3. FinanceAdmin -- Owner of Finance Database and Default DB is "Finance"
4. FinanceReadWrite -- This user has read and write access on Finance databases and Default DB is "Finance"
5. ReadWriteBoth -- This user has read and write access on both databases and Default DB is "master"

Below is the view for an Administrator

Now, let us Deny "View Any Databases" permissions for the "public" user

use [master]
GO
DENY VIEW ANY DATABASE TO PUBLIC
GO

After Denying permission to public user, the view for an administrator is still the same.

Now let us connect as the user "HRAdmin", and below is the view for HRAdmin
HRAdmin is able to see only the "HR" user database because it is the owner of this database alone.

Next, connect as "HRReadWrite". HRReadWrite user is not able to see any user databases in Object explorer, but can do read and write operations on "HR" Database alone.

Now let us connect as "FinanceAdmin", since FinanceAdmin is owner of Finance database, this user is able to see only "Finance" user Databaase.

Now connect as "FinanceReadWrite". FinanceReadWite user is not able to see any user databases in Object explorer, but can do read and write operations on "Finance" user Database alone.

Now let us connect using the user "ReadWriteBoth" which has read and write access on both the databases HR and Finance. This user is unable to see any user database in object explorer, but can do read and write operations on both HR and Finance database using the query editor.