As a DBA there are occasions where we are required to collect details from a database which can
be used for further analysis. For example, getting the status of the database (i.e. File Size,
Free Space, Status of the database, and who is the current owner of the database). This kind of
information is required, and very useful, for auditing purposes in addition to tracking the database/database
file's size for various reasons. I had a script which does this job for me, exactly the way
I want it; however, I have to run it for each database separately.
One fine day, while answering some question's on a forum, I found a script by Dan Guzman which
retrieved most of the information I needed, and it does this for all the databases. I have
adopted Dan G.'s script for my use and modified it by adding some more details to it.
Please review the script below. Let me know if you like it or dislike it. I will try to
make further improvements on this script.
--==================================================================================================================
-- Script Originally Written By: Dan Guzman | https://www.dbdelta.com/
-- Modified by: Hemantgiri S. Goswami
-- Reference:
-- https://social.msdn.microsoft.com/Forums/en/transactsql/thread/226bbffc-2cfa-4fa8-8873-48dec6b5f17f
--==================================================================================================================
DECLARE
@SqlStatement NVARCHAR(MAX)
,@DatabaseName SYSNAME;
IF OBJECT_ID(N'tempdb..#DatabaseSpace') IS NOT NULL
DROP TABLE #DatabaseSpace;
CREATE TABLE #DatabaseSpace
(
SERVERNAME SYSNAME,
DBID INT,
DATABASE_NAME SYSNAME,
Recovery_Model VARCHAR(15),
DBOWNER VARCHAR(25),
LOGICAL_NAME SYSNAME,
FILE_PATH SYSNAME,
FILE_SIZE_GB DECIMAL(12, 2),
SPACE_USED_GB DECIMAL(12, 2),
FREE_SPACE_GB DECIMAL(12, 2),
GROWTH_OPTION VARCHAR(15),
MAXIMUM_SIZE INT,
AUTOGROWTH INT,
DB_STATUS VARCHAR(100)
);
DECLARE DatabaseList CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM sys.databases WHERE STATE = 0;
OPEN DatabaseList;
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DatabaseList INTO @DatabaseName;
IF @@FETCH_STATUS = -1 BREAK;
SET @SqlStatement = N'USE '
+ QUOTENAME(@DatabaseName)
+ CHAR(13)+ CHAR(10)
+ N'INSERT INTO #DatabaseSpace
SELECT
[ServerName] = @@ServerName
,[DBID] = SD.DBID
,[DATABASE_NAME] = DB_NAME()
,[Recovery_Model] = d.recovery_model_desc
,[DBOwner] = SUSER_SNAME(sd.sid)
,[LOGICAL_NAME] = f.name
,[File_Path] = sf.filename
,[FILE_SIZE_GB] = (CONVERT(decimal(12,2),round(f.size/128.000,2))/1024)
,[SPACE_USED_GB] = (CONVERT(decimal(12,2),round(fileproperty(f.name,''SpaceUsed'')/128.000,2))/1024)
,[FREE_SPACE_GB] = (CONVERT(decimal(12,2),round((f.size-fileproperty(f.name,''SpaceUsed''))/128.000,2))/1024)
,[Growth_Option] = case sf.status
& 0x100000
WHEN 1048576 THEN ''Percentage''
WHEN 0 THEN ''MB''
END
,[Maximum_Size] = SF.MaxSize
,[AutoGrowth(MB)] = (SF.Growth*8/1024)
,[DB_Status] =
CASE SD.STATUS
WHEN 0 THEN ''Normal''
WHEN 1 THEN ''autoclose''
WHEN 2 THEN ''2 not sure''
WHEN 4 THEN ''select into/bulkcopy''
WHEN 8 THEN ''trunc. log on chkpt''
WHEN 16 THEN ''torn page detection''
WHEN 20 THEN ''Normal''
WHEN 24 THEN ''Normal''
WHEN 32 THEN ''loading''
WHEN 64 THEN ''pre recovery''
WHEN 128 THEN ''recovering''
WHEN 256 THEN ''not recovered''
WHEN 512 THEN ''offline''
WHEN 1024 THEN ''read only''
WHEN 2048 THEN ''dbo use only''
WHEN 4096 THEN ''single user''
WHEN 8192 THEN ''8192 not sure''
WHEN 16384 THEN ''16384 not sure''
WHEN 32768 THEN ''emergency mode''
WHEN 65536 THEN ''online''
WHEN 131072 THEN ''131072 not sure''
WHEN 262144 THEN ''262144 not sure''
WHEN 524288 THEN ''524288 not sure''
WHEN 1048576 THEN ''1048576 not sure''
WHEN 2097152 THEN ''2097152 not sure''
WHEN 4194304 THEN ''autoshrink''
WHEN 1073741824 THEN ''cleanly shutdown''
END
FROM SYS.DATABASE_FILES F
JOIN
MASTER.DBO.SYSALTFILES SF
ON F.NAME COLLATE database_default = SF.NAME COLLATE database_default
JOIN
MASTER.SYS.SYSDATABASES SD
ON
SD.DBID = SF.DBID
JOIN
MASTER.SYS.DATABASES D
ON
D.DATABASE_ID = SD.DBID
AND DATABASEPROPERTYEX(SD.NAME,''Updateability'') <> ''OFFLINE''
ORDER BY [File_Size_GB] DESC';
EXECUTE(@SqlStatement);
END
CLOSE DatabaseList;
DEALLOCATE DatabaseList;
SELECT * FROM #DatabaseSpace;
DROP TABLE #DatabaseSpace;
GO
Update: The code has been updated to display the output even if the collation is different for some database. -- Hemantgiri S. Goswami |Linkedin | Twitter
Want to talk with an expert? Schedule a call with our team to get the conversation started.
10 Comments. Leave new
Hi Hemant,
Nice script. Just one quick comment. The “CREATE TABLE” statement references MB for some of the table names while the “INSERT TABLE” statement references GB. I’m pretty sure you meant GB and based on my results I was able to validate that. Also, you may want to augment the script with one column for the actual percentage of free space left. Good job on the script though!!!
Hi Jay,
Indeed!! Good catch, I meant GB only. I have updated the script to reflect that. I will be rolling out the new version of this script soon.
Thank you
Hemantgiri
hmm , works but that looks more like something from 2000 days with sysaltfiles and database status code.
sys.master_files can retrieve info about files under databases that are not online because it’s stored under master database so will get full picture of your databases.
Cheers
Hi Mohammed,
Yes, this script was created for a client who had most of the servers on 2000 :). I am in progress of making a new version of this script that will include the VLF count as well. I will try to make change accordingly to use sys_master_file
Regards
Hemantgiri
I am getting SP2-0310: unable to open file “SqlStatement.sql”
i.e it does like those @@ signs. what are they for ? and how to get the script to run ?
Hi Samir,
Those @@ServerName to print the server name, you can copy the entire code and execute it inside query window to populate the result set.
Thank you
Hemantgiri
Excellent script, the one i’m looking for, I tried running the script without any modification but getting some messages, please see the details given below –
Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.
Given below is the line that is causing error
,[FREE_SPACE_GB] = (CONVERT(decimal(12,2),round((f.size-fileproperty(f.name,”SpaceUsed”))/128.000,2))/1024)
Could you please elaborate more on this error please?
Hi Ajay,
That was because of the conflicting collation, I have edited the script and have added COLLATE function, should be good now.
Thank you
Hemantgiri
Hi all
Please can anyone help me with add the details below to this Script i have run it on my test system and it’s works fine
I am required to collect full details from all of our oracle database/server to be used for analysis.
Many Thanks
RDMS
EDITION
HOST NAME
IP ADDRESS
NO OF INSTANCES
SERVER
DATABASE NAME or SID
Hi Yunis,
Probably it would be better to ask these questions in an Oracle based forum, rather than a blog about SQL Server,
as you are more likely to get useful responses that way.
Fortunately at Pythian we have lots of folks working on various database technologies :)
Your request is somewhat short on details, so the following may or may not fit your needs.
For instance, will you be logging on to the server to get this info?
If so, you already know the hostname.
The following connections use elevated privileges.
If you have access to the server then you will be able to run these commands.
If not then you will need to connect remotely to the database
Connect to the database as a user with privileges that allow reading the data dictionary
The SYSTEM account will work for this.
RDMS
EDITION
col product format a40
col version format a30
col status format a30
set linesize 120 trimspool on
select * from product_component_version;
HOST NAME
This query will provide the database server name
select sys_context(‘userenv’,’server_host’) from dual;
If using RAC, probably just get it from gv$instance
select i.inst_id,i.instance_name from gv$instance i
This information is also available from the command line in Windows/Linux/Unix
>$ hostname
someserver
IP ADDRESS
Replace the hostname parameter with the value returned by the previous query
select utl_inaddr.get_host_address(‘HOSTNAME goes here’) from dual
This information is also available from the command line in Windows/Linux/Unix
Windows:
ipconfig /all
Linux/Unix:
ifconfig -a
or
ip a
NO OF INSTANCES
You haven’t specified just what you are asking for here.
The number of instances on the server?
If so then there several ways to go about it, here are a couple.
Windows:
sc queryex type= service state= all | find /i “Ora
Linux/Unix:
Find the oratab file.
Usually it is in one of two places
/etc/oratab/
/var/opt/oracle/oratab
This command will show all the oracle homes that are listed in oratab.
These may or may not correspond to the actual number of instances running on the server.
grep -vE ‘^#|^$’ /etc/oratab | cut -f1 -d:
Another method:
ps -flea | grep [p]mon
SERVER
Sorry, don’t know what you are asking for here.
DATABASE NAME or SID
Database and SID are two separate things.
On standalone databases they are likely the same value.
For RAC databases they will be different.
The SID will be the values seen in the oratab file.
Database name:
select name from v$database;