A Comprehensive Guide to Enhancing Server Performance (Part 1)

To Support DBAs Worldwide: Expert Query Writing and Troubleshooting Tips

In the world of database administration, ensuring optimal performance is paramount. Database administrators (DBAs) rely on a variety of techniques to troubleshoot and validate the performance of their database systems. In this article, we'll explore a crucial aspect of database management: the use of queries for performance validation.

?

Introduction

Database performance issues can be detrimental to an organization's operations. Slow queries, bottlenecks, and resource constraints can lead to downtime, decreased productivity, and even customer dissatisfaction. To proactively address these challenges, DBAs employ a set of queries designed specifically for performance validation.

?

?

Part I.

?

Overall, this query provides a summary of the current database connections, showing the number of connections for each database and the associated login names. It can be useful for monitoring and managing database connections in a SQL Server instance.

?

SELECT DB_NAME(dbid) AS DBName,

COUNT(dbid) AS NumberOfConnections,

loginame

FROM??? sys.sysprocesses

GROUP BY dbid, loginame

ORDER BY DB_NAME(dbid)

?

?

The provided SQL query is used to retrieve information about the encryption state of databases in a SQL Server instance. Here's a short description of the query:

Overall, this query helps monitor the encryption status of databases in a SQL Server instance and provides details about their encryption settings.

?

SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,

encryption_state_desc =

CASE encryption_state

???????? WHEN '0' THEN 'No database encryption key present, no encryption'

???????? WHEN '1' THEN 'Unencrypted'

???????? WHEN '2' THEN 'Encryption in progress'

???????? WHEN '3' THEN 'Encrypted'

WHEN '4' THEN 'Key change in progress'

WHEN '5' THEN 'Decryption in progress'

WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed'

???????? ELSE 'No Status'

?END,

percent_complete, create_date, key_algorithm, key_length,

encryptor_thumbprint, encryptor_type? FROM sys.dm_database_encryption_keys

?

?

LOG SHIPPING: This query is likely used to retrieve information or status related to log shipping for a primary database named "STRGD12" in the context of SQL Server. It may be used for monitoring and managing log shipping for disaster recovery purposes, ensuring data consistency, and maintaining high availability of the database.

?

sp_help_log_shipping_monitor_primary STRGD12,SSS

?

?

The "sp_help_log_shipping_monitor_secondary" procedure is typically used to check the status of log shipping on the secondary server, view information about the log shipping configuration, and monitor the synchronization and health of the secondary database for disaster recovery purposes.

?

EXEC sp_help_log_shipping_monitor_secondary GDJNT154,STRGD12_sss

?

?

The query essentially retrieves a list of backup operations from the "backupset" table and sorts them in descending order based on the finish date, can see the most recent backup operations at the top of the result set. This information is useful for monitoring and managing database backups.

?

select name,database_name,backup_Start_date,backup_finish_date

from msdb.dbo.backupset order by backup_finish_date desc

?

?

The xp_ReadErrorLog extended stored procedure in SQL Server is used to read and retrieve error log entries.

The Query is using xp_ReadErrorLog to read error log entries from October 24, 2023, to October 25, 2023, in the current SQL Server error log. This can be helpful for troubleshooting and diagnosing issues that occurred during that time frame.

?

xp_ReadErrorLog 1,1, NULL, NULL, '20231024', '20231025'

?

?

The Query is using xp_ReadErrorLog to read error log entries from October 18, 2023, to October 19, 2023, in the current SQL Server error log. This can be useful for reviewing error log entries that occurred during that specific time frame, which can be helpful for troubleshooting and diagnosing issues.

?

??????? xp_ReadErrorLog 0,1, NULL, NULL, '20231018', '20231019'

?

?

The command you've provided, "FSUTIL FSINFO NTFSINFO N:", is used to retrieve NTFS (New Technology File System) information for a specific drive or volume represented by "N:". Here's what each part of the command does:

FSUTIL FSINFO NTFSINFO N:

?

?

This SQL query retrieves information about CPU usage, wait time, elapsed time, reads, writes, and the number of connections for different programs accessing a SQL Server instance. Here's a breakdown of what the query does:

This query can be useful for performance analysis and identifying which programs or applications are consuming the most CPU resources in a SQL Server instance, helping with troubleshooting and optimization efforts.

?

?

SELECT CPU = SUM(cpu_time) ,WaitTime = SUM(total_scheduled_time) ,ElapsedTime = SUM(total_elapsed_time) ,Reads = SUM(num_reads) ,Writes = SUM(num_writes) ,Connections = COUNT(1) ,Program = program_name

FROM sys.dm_exec_connections con

LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = con.session_id

GROUP BY program_name ORDER BY cpu DESC

?

?

This SQL query retrieves information about the most recent log backups for SQL Server databases.

This query is helpful for monitoring and reviewing the most recent log backups performed on SQL Server databases, which is crucial for ensuring data integrity and disaster recovery readiness.

?

SELECT top 10 bs.user_name,bs.database_name, bs.backup_start_date, bs.backup_finish_date, bm.physical_device_name, bm.logical_device_name

FROM msdb..backupset bs

JOIN msdb..backupmediafamily bm

ON bs.media_set_id = bm.media_set_id

WHERE bs.type='L' ORDER BY bs.backup_start_date desc, bs.database_name

?

?

This query is useful for monitoring and reviewing the most recent full database backup of a specific database, which is essential for backup management and disaster recovery planning.

?

SELECT bs.user_name,bs.database_name, bs.backup_start_date, bs.backup_finish_date, bm.physical_device_name, bm.logical_device_name FROM msdb..backupset bs JOIN msdb..backupmediafamily bm

ON bs.media_set_id = bm.media_set_id

WHERE bs.type='d' and bs.database_name='MGKDB'

ORDER BY bs.backup_start_date desc, bs.database_name

?

?

This query is useful for monitoring databases and determining when the last full backup was performed for each database, providing valuable information for backup management and data recovery planning.

?

SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup, ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as LastBackupDate

FROM master.dbo.sysdatabases B

LEFT OUTER JOIN

msdb.dbo.backupset A ON

A.database_name = B.name AND

A.type = 'D'

GROUP BY B.Name ORDER BY B.name

?

?

The query essentially identifies sessions that are currently blocked by other sessions and returns information about those blocked sessions, specifically their session IDs (spid). This information can be valuable for troubleshooting and identifying blocking issues within a SQL Server instance, allowing to take appropriate actions to resolve the blocking situations.

?

Select spid,blocked from sysprocesses where blocked>0

?

?

The query returns the "Buffer cache hit ratio" value for the Buffer Manager performance counter. This ratio is a measure of how often SQL Server can find a requested data page in its buffer cache without having to read it from disk. A high hit ratio indicates efficient memory usage, while a low ratio may suggest performance issues related to disk I/O. Monitoring this counter can help in assessing and optimizing SQL Server's memory management.

?

SELECT object_name, counter_name, cntr_value

FROM sys.dm_os_performance_counters

WHERE [object_name] LIKE '%Buffer Manager%'

AND [counter_name] = 'Buffer cache hit ratio'

?

?

This query is useful for identifying which user databases are consuming the most CPU resources on the SQL Server instance, which can be helpful for performance tuning and troubleshooting. The results will show the top CPU-consuming databases with their names, CPU times, and percentages of total CPU usage.

?

WITH DB_CPU_Stats AS

(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName],

SUM(total_worker_time) AS [CPU_Time_Ms]

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]

FROM sys.dm_exec_plan_attributes(qs.plan_handle)

WHERE attribute = N'dbid') AS F_DB GROUP BY DatabaseID)

SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],

DatabaseName, [CPU_Time_Ms],

CAST([CPU_Time_Ms] 1.0 / SUM([CPU_Time_Ms]) OVER() 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]

FROM DB_CPU_Stats WHERE DatabaseID > 4 -- system databases

AND DatabaseID <> 32767 -- ResourceDB

ORDER BY row_num OPTION (RECOMPILE);

?

?

This information can be valuable for monitoring and troubleshooting the performance of your SQL Server instance, identifying blocking issues, and understanding the current workload on the server.

?

select * from sys.dm_exec_requests;

?

?

These are some of the common parameters that can use with sp_who to get specific information about active sessions on your SQL Server. The exact list of parameters and their behavior may vary depending on the version of SQL Server are using.

?

Return a list of all active processes on the server.

EXEC sp_who

?

Specify a particular Session ID (SPID) to get information about a specific session.

EXEC sp_who 51

?

Common status values include 'RUNNABLE', 'SLEEPING', 'BACKGROUND', etc.

EXEC sp_who @status = 'RUNNABLE'

?

Filter the results based on the login name of the sessions.

??????? EXEC sp_who @loginame = 'myuser'

?

Can filter the results based on the hostname or machine name from which the sessions are connected.

EXEC sp_who @hostname = 'mycomputer'

?

??????? Can filter the results based on the name of the database to which the sessions are connected.

??????? EXEC sp_who @dbname = 'mydatabase'

?

The ecid (Execution Context ID) parameter allows to filter results by the execution context ID.

EXEC sp_who @ecid = 1

?

-- Define variables to hold parameter values

DECLARE @spid INT = NULL; -- Set to NULL if not filtering by SPID

DECLARE @status VARCHAR(20) = NULL; -- Set to NULL if not filtering by status

DECLARE @loginame VARCHAR(50) = NULL; -- Set to NULL if not filtering by login name

DECLARE @hostname VARCHAR(50) = NULL; -- Set to NULL if not filtering by hostname

DECLARE @dbname VARCHAR(50) = NULL; -- Set to NULL if not filtering by database name

DECLARE @ecid INT = NULL; -- Set to NULL if not filtering by ECID

?

-- Execute sp_who with optional parameters

EXEC sp_who @spid = @spid,

?????????? @status = @status,

?????????? @loginame = @loginame,

?????????? @hostname = @hostname,

?????????? @dbname = @dbname,

?????????? @ecid = @ecid;

?

-- Add additional SQL statements here to format or manipulate the results as needed

?

?

The DBCC INPUTBUFFER command in SQL Server is used to retrieve the last statement that was sent from a client to SQL Server for a specific session (identified by its SPID - Server Process ID).

NOTE:? Need appropriate permissions to execute DBCC INPUTBUFFER as it provides access to potentially sensitive information about SQL Server sessions.

?

DBCC INPUTBUFFER (session_id)

?

?

Final result of this query will be a list of processes (sessions) that are currently connected to the 'TMRDEV' database, along with various details about each process.

?

SELECT *

FROM sys.sysprocesses

WHERE dbid IN (

??? SELECT database_id

??? FROM sys.databases

??? WHERE name = 'TMRDEV'

)

?

?

Retrieves information about database file segments from the sysfiles system view in SQL Server. It calculates various metrics such as file size, space used, available space, and percent used for each file segment.

?

SELECT

??? RTRIM(name) AS [Segment Name], -- Name of the file segment

??? groupid AS [Group Id], ?-- Group ID of the file segment

??? filename AS [File Name], -- File name

??? CAST(size/128.0 AS DECIMAL(10,2)) AS [Size in MB], -- File size in MB

??? CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Space Used], -- Space used in MB

??? CAST(size/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space], -- Available space in MB

??? CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent Used] -- Percent used

FROM sysfiles

ORDER BY groupid DESC

?

?

The SQL commands are altering the size of a database file named 'FF_SharedB_Q' within the 'master' database. Specifically, it's modifying the size of the file to 170369024 kilobytes (KB), which is approximately 166.05 gigabytes (GB).

?

USE [master];

GO

?

ALTER DATABASE [Mst_SharedB_Q]

MODIFY FILE (

??? NAME = N'Mst_SharedB_Q',

??? SIZE = 170369024KB

);

GO

?

?

This SQL query retrieves information about database file segments in SQL Server where the file name contains the substring 'templog'.

This query provides valuable information about file segments with 'templog' in their names, allowing to monitor and manage them effectively.

?

-- Retrieve information about database file segments

SELECT

??? RTRIM(name) AS [Segment Name],??????? -- The name of the file segment

??? groupid AS [Group Id],??????????????? -- The group ID of the file segment

??? filename AS [File Name],????????????? -- The name of the database file

??? CAST(size/128.0 AS DECIMAL(10,2)) AS [Size in MB],???????????????? -- File size in MB

??? CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Space Used],? -- Space used in MB

??? CAST(size/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space],? -- Available space in MB

??? CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent Used]? -- Percent used

FROM sysfiles

-- Filter the results to include only file segments with names containing 'templog'

WHERE name LIKE '%templog%'

-- Order the results by group ID in descending order

ORDER BY groupid DESC;

?

?

The SQL query retrieves various information about SQL Server sessions, including details about their execution, blocking, and resource usage. Here's the query with explanations:

This query provides valuable information about active sessions, their resource usage, and the SQL statements they are executing, which can be useful for monitoring and troubleshooting purposes in SQL Server.

?

-- Calculate the time difference between 'start_time' and the current time in minutes

SELECT

??? DATEDIFF(minute, start_time, GETDATE()) AS MinutesPassed,

??? dess.session_id,

??? blocking_session_id,

??? command,

??? login_name,

??? db_name(der.database_id) AS DBName,

??? program_name,

??? host_name,

??? der.cpu_time,

??? wait_type,

??? login_time,

??? GETDATE() AS CurrentTime,

??? der.status,

??? der.logical_reads,

??? wait_resource,

??? percent_complete,

??? DATEDIFF(minute, login_time, GETDATE()) AS StartTime_Passed,

??? text

FROM

??? sys.dm_exec_requests der

JOIN

??? sys.dm_exec_sessions dess ON der.session_id = dess.session_id

CROSS APPLY

??? sys.dm_exec_sql_text(der.sql_handle) des

WHERE

??? der.session_id > 50

??? AND der.session_id <> @@SPID

-- Order the results by 'cpu_time' in descending order

ORDER BY

??? der.cpu_time DESC;

?

?

The SQL query retrieves information about currently executing SQL Server sessions along with various details about their execution, including blocking information and query text.

However, be cautious when including execution plans as they can be quite large and impact performance.

?

SELECT

??? s.session_id,

??? r.STATUS,

??? r.blocking_session_id AS 'blocked_by',

??? r.wait_type,

??? r.wait_resource,

??? CONVERT(VARCHAR, DATEADD(ms, r.wait_time, 0), 8) AS 'wait_time',

??? r.cpu_time,

??? r.logical_reads,

??? r.reads,

??? r.writes,

??? CONVERT(VARCHAR, DATEADD(ms, r.total_elapsed_time, 0), 8) AS 'elapsed_time',

??? CAST((

??????? '<?query --? ' + CHAR(13) + CHAR(13) + Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (

??????????? (

??????????????? CASE r.statement_end_offset

??????????????????? WHEN - 1

??????????????????????? THEN Datalength(st.TEXT)

??????????????????? ELSE r.statement_end_offset

??????????????????? END - r.statement_start_offset

??????????????? ) / 2

??????????? ) + 1) + CHAR(13) + CHAR(13) + '--?>'

??????? ) AS XML) AS 'query_text',

??? COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS 'stored_proc',

??? r.command,

??? s.login_name,

??? s.host_name,

??? s.program_name,

??? s.host_process_id,

??? s.last_request_end_time,

??? s.login_time,

??? r.open_transaction_count

FROM

??? sys.dm_exec_sessions AS s

INNER JOIN

??? sys.dm_exec_requests AS r ON r.session_id = s.session_id

CROSS APPLY

??? sys.dm_exec_sql_text(r.sql_handle) AS st

--OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp -- uncomment (2) if want to see plan

WHERE

??? r.wait_type NOT LIKE 'SP_SERVER_DIAGNOSTICS%'

??? OR r.session_id != @@SPID

ORDER BY

??? r.cpu_time DESC,

??? r.STATUS,

??? r.blocking_session_id,

??? s.session_id;

?

?

The SQL query retrieves information about sessions that are currently being blocked by other sessions in SQL Server. It includes details about the session, the blocking session, the database name, host name, login name, and the query text.

?

-- Retrieve information about blocked sessions

SELECT

??? r.session_id,

??? r.blocking_session_id,

??? DB_NAME(r.database_id) AS Database_Name,

??? s.host_name,

??? s.login_name,

??? s.original_login_name,

??? r.status,

??? r.command,

??? r.cpu_time,

??? r.total_elapsed_time,

??? t.text AS Query_Text

FROM

??? sys.dm_exec_requests r

CROSS APPLY

??? sys.dm_exec_sql_text(sql_handle) t

INNER JOIN

??? sys.dm_exec_sessions s ON r.session_id = s.session_id

WHERE

??? r.blocking_session_id <> 0;

?

?

DBCC (Database Console Commands) is a set of Transact-SQL (T-SQL) commands provided by Microsoft SQL Server for database administration and maintenance.

?

1.???????? DBCC CHECKDB:

??????????? Verifies the logical and physical integrity of a database.

??????????? Checks for corruption and inconsistencies within the database.

??????????? Provides detailed information about errors found.

?

2.???????? DBCC CHECKTABLE:

??????????? Checks the integrity of a specific table within a database.

??????????? Helps identify and repair corruption at the table level.

?

3.???????? DBCC CHECKALLOC:

??????????? Checks the allocation structures of a database.

??????????? Verifies the consistency of space allocation within a database.

?

4.???????? DBCC SHRINKDATABASE:

??????????? Reduces the size of a database by reclaiming unused space.

??????????? Can be used to shrink the entire database or specific files.

?

5.???????? DBCC SHRINKFILE:

??????????? Shrinks a specific database file to reduce its size.

??????????? Useful for managing disk space and file growth.

?

6.???????? DBCC INDEXDEFRAG:

??????????? Defragments clustered and non-clustered indexes.

??????????? Can improve index performance and reduce fragmentation.

?

7.???????? DBCC UPDATEUSAGE:

??????????? Corrects inaccuracies in space usage information stored in system catalogs.

??????????? Helps ensure that space usage statistics are up to date.

?

8.???????? DBCC FREEPROCCACHE:

??????????? Clears the plan cache, removing execution plans for stored procedures and queries.

??????????? Useful for troubleshooting and performance tuning.

?

9.???????? DBCC DROPCLEANBUFFERS:

??????????? Removes all clean pages from the buffer pool.

??????????? Forces SQL Server to read data from disk when it is needed, which can be useful for testing and benchmarking.

?

10.?????? DBCC INPUTBUFFER:

??????????? Retrieves the last statement sent from a client to SQL Server for a specific session.

??????????? Useful for troubleshooting and auditing.

?

11.?????? DBCC OPENTRAN:

??????????? Displays information about the oldest active transactions in the database.

??????????? Helps identify long-running transactions that may cause blocking.

?

12.?????? DBCC TRACEON and DBCC TRACEOFF:

??????????? Enables or disables trace flags, which are used for diagnostic purposes.

??????????? Allows to enable or disable specific SQL Server behaviors.

?

13.?????? DBCC TRACESTATUS:

??????????? Provides information about which trace flags are currently active and their status.

?

14.?????? DBCC SQLPERF:

??????????? Retrieves various performance-related statistics, such as cache hit ratios and I/O statistics.

??????????? Useful for monitoring and performance tuning.

?

15.?????? DBCC CLONEDATABASE:

??????????? Creates a copy of an existing database with limited data and schema.

??????????? Useful for creating a lightweight database for testing and debugging.

?

16.?????? DBCC CHECKIDENT:

??????????? Checks and resets the identity seed value for a specified table.

??????????? Can be used to correct identity column values after data manipulation.

?

?

The sys.sysprocesses system view to retrieve information about currently active processes or sessions.

?

SELECT *

FROM sys.sysprocesses

WHERE spid = 99;

?

?

This query will return a list of SQL Server Agent job definitions from the msdb database where the job_id contains the specified string.

?

The SQL query selects information from the sysjobs system view in the msdb database, filtering the results based on the job_id column. It looks for job IDs that contain the specified string '%A966D6498E9495A90AE966EC%'.

???????

USE msdb;

GO

SELECT *

FROM sysjobs

WHERE job_id LIKE '%A966D6498E9495A90AE966EC%';

?

?

Generates a list of SQL Server KILL statements for sessions with a dbid (database ID) of 6.

Execute these KILL statements to terminate the specified sessions associated with database ID 6. Be cautious when using KILL statements, as forcibly terminating a session can result in data inconsistencies or unexpected behavior if not done carefully.

?

SELECT 'KILL ' + CAST(spid AS VARCHAR(10)) AS spid

FROM sys.sysprocesses

WHERE dbid = 6;

?

?

This query is useful for monitoring long-running or resource-intensive queries related to database backup, index creation, and other operations, providing information on their progress and estimated completion times.

?

This query is useful for monitoring long-running or resource-intensive queries related to database backup, index creation, and other operations, providing information on their progress and estimated completion times.

?

SELECT

??? r.session_id,

??? r.command,

??? CONVERT(NUMERIC(6,2), r.percent_complete) AS [Percent Complete],

??? CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GetDate()), 20) AS [ETA Completion Time],

??? CONVERT(NUMERIC(6,2), r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],

??? CONVERT(NUMERIC(6,2), r.estimated_completion_time/1000.0/60.0) AS [ETA Min],

??? CONVERT(NUMERIC(6,2), r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],

??? -- Include the actual command text for better understanding

??? CONVERT(VARCHAR(100), (

??????? SELECT SUBSTRING(text, r.statement_start_offset/2,

??????????? CASE WHEN r.statement_end_offset = -1 THEN 1000

??????????? ELSE (r.statement_end_offset - r.statement_start_offset)/2 END)

??????? FROM sys.dm_exec_sql_text(sql_handle)

??? )) AS 'Command'

FROM sys.dm_exec_requests r

WHERE command IN ('BACKUP DATABASE', 'CREATE INDEX', 'RESTORE DATABASE', 'LOG BACKUP');

?

?

This script is designed to gather file information from all databases in the SQL Server instance and store it in a temporary table.

The SQL script creates a temporary table #db_files and uses a cursor to iterate through all databases in the SQL Server instance.

It's useful for analyzing file sizes and space utilization across multiple databases, especially when want to filter files based on their location (drive) and order them by size.

?

-- Create a temporary table to store database file information

create table #db_files(

??? db_files varchar(300),

??? file_loc varchar(300),

??? filesizeMB decimal(9,2),

??? spaceUsedMB decimal(9,2),

??? FreespaceMB decimal(9,2)

)

?

declare @strSQL nvarchar(2000)

DECLARE @dbName varchar(MAX)

DECLARE @getDBname CURSOR

?

-- Create a cursor to iterate through all databases

SET @getDBname = CURSOR FOR

select name from sys.databases

?

OPEN @getDBname

FETCH NEXT

FROM @getDBname INTO @dbName

?

-- Iterate through databases

WHILE @@FETCH_STATUS = 0

BEGIN

??? PRINT @dbName

?

??? -- Build a dynamic SQL statement to insert file information into the temporary table

??? select @strSQL =

??? '

??????? use ' + @dbName + '

??????? INSERT INTO #db_files

? ??????select

??????????? name,

??????????? filename,

??????????? convert(decimal(12,2), round(a.size/128.000,2)) as FileSizeMB,

??????????? convert(decimal(12,2), round(fileproperty(a.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB,

??????????? convert(decimal(12,2), round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB

??????? from dbo.sysfiles a

??? '

???

??? -- Execute the dynamic SQL statement

??? exec sp_executesql @strSQL

?

??? FETCH NEXT

??? FROM @getDBname INTO @dbName

END

?

-- Close and deallocate the cursor

CLOSE @getDBname

DEALLOCATE @getDBname

GO

?

-- Select file information from the temporary table for files located on drive 'G' and order by file size

select * from #db_files where file_loc like 'G%' ORDER BY filesizeMB DESC

?

-- Drop the temporary table

drop table #db_files

?