SQL Server Get free space in all files in an Instance

SQL Server Get free space in all files in an Instance

SQL Server Get free space in all files in an Instance:

As DBAs we are always challenged of unexpected size growth of log or data files and need to shrink files based on available free space in each file. Most of the times we are stuck and do a lot of scripting to find the free space available in each data or log files for an SQL Database. Here is a simple query which will return free space available in all data and log files of all the databases in an instance of your SQL Server. By providing the parameters facilitated in the script you can get free space in a particular database or on a particular drive.

Transact-SQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

SETNOCOUNTON

DECLARE@DBNameNVARCHAR(100)=NULL,--Provide DBName if looking for a specific database or leave to get all databases details

@DriveNVARCHAR(2)=NULL--Mention drive letter if you are concerned of only a single drive where you are running out of space

Share This Story, Choose Your Platform!

Manohar Punna has started his career in 2008 as a core DBA and is now working as Senior DBA for S&P Capital IQ (McGraw Hill Financials). In his 5 years of game with SQL Server he has worked in service, support, insurance & banking and financial domains. As a Microsoft GTSC ex he bring great knowledge on SQL core internals. In his free time Manohar wants to explore everything else from travelling to sitting idle, from dieting to heavy 7 course meal, from full day sleeps to overnight ride in the city.