Hi All,

Once I had to find out few details of available database snapshots in SQL Server. I wrote following script which helped me to find out following:

• how many database-snapshots are available & live
• whats the size1 of database snapshot as well as the allotted size2 for database snapshot
• What collation is assigned to it
• where it is physically located in my hard-drive

1. Size of database snapshot = Actual Size in query results
2. Size allotted for database snapshot = File Size in query results

Apart from this result, you can modify the query to get these details for all the available databases in SQL Server. You just have to remove the WHERE clause.

Following sql query will help you to create a database snapshot:

SQL Query:

SELECT
D.NAME AS [DATABASE NAME],
CASE
WHEN MF.TYPE_DESC = 'ROWS' THEN 'Database File'
WHEN MF.TYPE_DESC = 'LOG' THEN 'Log File'
END AS [FILE TYPE],
D.CREATE_DATE AS [CREATION DATE],
D.COLLATION_NAME AS [COLLATION USED],
MF.PHYSICAL_NAME AS [PHYSICAL LOCATION],
MF.STATE_DESC AS [STATE],
CONVERT(VARCHAR(10), CONVERT(DECIMAL(10,2), CONVERT(DECIMAL(10,2), MF.SIZE)/(1024*8))) + ' MB' AS [ACTUAL SIZE],
CASE
WHEN (CONVERT(DECIMAL(10,2), MF.SIZE) * 8) > 1024 THEN CONVERT(VARCHAR(10), CONVERT(DECIMAL(10,2), (CONVERT(DECIMAL(10,2), MF.SIZE) * 8)/1024)) + ' MB'
WHEN (CONVERT(DECIMAL(10,2), MF.SIZE) * 8) > 1048576 THEN CONVERT(VARCHAR(10), CONVERT(DECIMAL(10,2), (CONVERT(DECIMAL(10,2), MF.SIZE) * 8)/1048576)) + 'GB'
WHEN (CONVERT(DECIMAL(10,2), MF.SIZE) * 8) > 1073741824 THEN CONVERT(VARCHAR(10), CONVERT(DECIMAL(10,2), (CONVERT(DECIMAL(10,2), MF.SIZE) * 8)/1073741824)) + 'TB'
ELSE CONVERT(VARCHAR(10), CONVERT(DECIMAL(10,2), MF.SIZE) * 8) + ' KB'
END AS [FILE SIZE]
FROM SYS.MASTER_FILES AS MF
LEFT OUTER JOIN
SYS.DATABASES D
ON MF.DATABASE_ID = D.DATABASE_ID
WHERE MF.PHYSICAL_NAME LIKE '%.ss%'


Thanks, Khilit