Get Total Size and Free Space of disks hosting SQL Server database files

I have always wanted to know the disk size and free space available using TSQL and not use xp_cmdshell. I have hated to use xp_fixeddrives since it only gives the free space and not the total size of the volume. Just learnt about the DMV: sys.dm_os_volume_stats and works great. This works only on SQL Server 2008 R2 SP1 and higher though.

SELECT       DISTINCT volume_mount_point as Drive,
             logical_volume_name as Volume_Name,
            CAST(total_bytes/1024./1024./1024. as decimal(20,2)) 
as Total_Size_GB,
             CAST(available_bytes/1024./1024./1024. as decimal(20,2)) 
as Free_Space_GB,
            CAST((CAST(available_bytes as decimal(20,2))*100/total_bytes) as decimal(5,2)) 
as Percent_Free
FROM        sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)
ORDER BY    1

OUTPUT

SQL Server fails to start. Error: 912, Severity: 21, State: 2. Script level upgrade for database 'master' failed.

Issue Description:

        SQL Server services not coming online because TempDB Tlog files go full due to Active_Transaction during startup.

Error Message (ERRORLOG):

Error: 9002, Severity: 17, State: 4.

The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.

Error: 912, Severity: 21, State: 2.

Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 9002, state 4, severity 17. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

Error: 3417, Severity: 21, State: 3.

Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

SQL Server shutdown has been initiated

Fix/Resolution:

• Start SQL Server services from command line using the below parameters:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn>sqlservr.exe -sMSSQLSERVER -f -T3608

• Open connection to SQL Server and execute the below (SSMS GUI will not work. Will have to use SQLCMD or Query Window within SSMS):

--To confirm we have the connection on the correct server

        select serverproperty('servername')

--Executed the below to resize the TempDB Log file

        USE [master]

      GO

        ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 10GB )

        GO

--From ERRORLOG found the below:

Clearing tempdb database.

Starting up database 'model'.

CHECKDB for database 'model' finished without errors on 2014-09-06 17:00:05.970 (local time). This is an informational message only; no user action is required.

Starting up database 'tempdb'

• Stop SQL Server service that was started from command line (Ctrl + C).
• Restart SQL Server service from SQL Server configuration manager.
• The upgrade script should run successfully and user connections should now be successful.

New Index Creation Considerations - SQL Server

For a DBA, it is normal to receive index recommendations from the application team or users. And this recommendation is typically not based on sound database knowledge. For example, users would run DTA - Database Tuning Advisor and come up with the recommendations. While I support the utility of the DTA tool and believe it is a great 1st step, the recommendations coming out of it should not be implemented blindly.

In this post, I am trying to provide a brief description on the things to do or consider before we create a new index.

• Review the existing indexes on the table. Check for duplicate or overlapping indexes. To identify existing indexes on a table use the below TSQL:

exec sp_helpindex

• Review the suggested index along with missing index details from the DMV: sys.dm_db_missing_index_details. We can use the below TSQL to get the list of missing indexes identified by SQL Server. Please use the output of the below query just as reference and not as the determining factor.

USE [database-name]
GO
SELECT db_name(d.database_id) [DB_Name],
object_name(d.object_id) Table_Name,
d.equality_columns Equality_Columns,
d.inequality_columns Inequality_Columns,
d.included_columns Included_Columns,
gs.unique_compiles Unique_Compiles,
gs.user_seeks User_Seeks,
gs.user_scans User_Scans,
gs.last_user_seek Last_User_Seek,
gs.avg_total_user_cost Avg_Total_User_Cost,
gs.avg_user_impact Avg_User_Impact
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats gs
ON gs.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON g.index_handle = d.index_handle
WHERE d.database_id = d.database_id
AND d.object_id = d.object_id
AND db_name(d.database_id) = 'database-name'
AND object_name(d.object_id) = 'table-name'
ORDER BY gs.user_seeks DESC
GO

• Get the SQL from the requester for which they are recommending the index. Before we implement the index, execute the SQL and get the time, IO and execution plan details so that we can compare it after we create the index. Use the below SET options to get the Time and IO details. On SSMS, there is a button on top to enable Inclusion of Actual Execution Plan

SET STATISTICS TIME ON

SET STATISTICS IO ON

• Once we create the index, redo the above step and compare the output. We should immediately see the difference.
• Give it some time and depending on how often the index is expected to be used, run the below TSQL to get the metrics of the index usage.

USE [database-name]
GO
SELECT OBJECT_NAME(i.object_id) Table_Name,
i.name Index_Name,
ius.user_seeks User_Seeks,
ius.user_scans User_Scans,
ius.user_lookups User_Lookups,
ius.user_updates User_Updates,
ius.last_user_seek Last_User_Seek,
ius.last_user_scan Last_User_Scan,
ius.last_user_lookup Last_User_Lookup,
ius.last_user_update Last_User_Update
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.indexes i
ON ius.[object_id] = i.[object_id]
AND ius.index_id = i.index_id
WHERE ius.database_id = 7
AND OBJECTPROPERTY(i.[object_id], 'IsUserTable') = 1
AND i.index_id <> 0
AND OBJECT_NAME(i.[object_id]) = 'table-name'
GO