By: Bernard Black | Updated: 2023-05-31 | Comments | Related: More > Database Administration
Problem
One of the things that DBAs and system administrators often do is keep an eye on the size of databases and total server space used by all databases. You could write queries on each instance to gather this information and then combine it, but in this article, I am going to show you a C# app I put together to help retrieve and display this information.
Solution
We will create a Windows C Sharp form that shows the top 50 largest databases or servers. This uses a PowerShell script that will be embedded in a SQL Server Agent Job on one server to collect and store the data centrally from all of the servers you specify.
This is what the app looks like with some collected data. Below we can see information for the servers.
Step 1 - Create Tables to Store Data
Create two tables on any SQL Server instance where you want to run and collect the data for your servers and databases. This should be on a SQL Server that also has SQL Agent since we will use a scheduled job to centrally collect the data.
Run the T-SQL script below to create the two tables. The database that contains these tables should be on a server that can hold a permanent database. The first table, "sql_servers", will contain the SQL Server server names (that you need to enter) which will be used to connect to and collect data. The "sql_databases" table will hold the collected information for each database on each server.
CREATE TABLE [dbo].[sql_servers]( [is_sqlserver] [varchar](50) NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[sql_databases]( [is_identity] [int] IDENTITY(1,1) NOT NULL, [is_sqlserver] [varchar](60) NOT NULL, [is_name] [varchar](60) NOT NULL, [is_date_stamp] [date] NOT NULL, [is_size] [real] NULL, [is_available] [real] NULL, [is_indexsize] [real] NULL ) ON [PRIMARY] GO
After the tables have been created, add entries to the "sql_servers" table for each SQL Server instance you want to collect data from. You could either use the edit functionality in SSMS, write INSERT statements or any other method to populate this table.
Step 2 - Create SQL Server Agent Job to Collect Data
The following SQL code will create a SQL Server Agent Job to collect data. This code includes a PowerShell script that will loop through each server in the "sql_servers" table and then collect information for each database on that SQL Server. The PowerShell script uses integrated security to connect to SQL Server.
Job Step 1
The first step for the job will truncate the table and reload the data. This is optional if you want to store historical data. This will need to run against the database that you are using to store the data.
truncate table sql_databases;
Job Step 2
The second step for the job will run this PowerShell script to collect data from each server and each database.
You will need to change the values for:
- $SQLServer - this is the server where the data will be stored
- $SQLDBName - this is the database where the data will be stored
####DB_Size_Load
#### NOTE : Change the next 2 values for your environment
$SQLServer = "WIN-337JP8T8O6K" #use Server\Instance for named SQL instances!
$SQLDBName = "Test"
#####Next line gets the servers
$SqlQuery = "select is_sqlserver from sql_servers"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet, "Server_Names") | Out-Null
$SqlConnection.Close()
#####....put max length string on names.
$today = Get-Date
$MaxLength = 60
foreach ($row in $DataSet.Tables["Server_Names"].rows) {
$server = $row.is_sqlserver
$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server
if ($srv.Status -EQ "Offline" -OR $srv.Status -EQ "Online" ) {
$dbs = $srv.Databases
ForEach ($db in $dbs) {
if ( $db.IsAccessible ) {
$name1 =$db.name
if ($name1.Length -gt $MaxLength )
{ $name2 = $name1.substring(0, $MaxLength) }
else
{ $name2 = $name1 }
$size1 = $db.size
$dbspace1 = $db.DataSpaceUsage/1KB
$dbindexsp1 = $db.IndexSpaceUsage/1KB
$dbspaceavail1 = $db.SpaceAvailable/1KB
switch ($name1 ) {
'master' {}
'model' {}
'tempdb' {}
Default { Invoke-SQLcmd -ServerInstance $SQLServer -Database $SQLDBName -Query "INSERT INTO sql_databases VALUES (''$server'',''$name2'',''$today'',$dbspace1,$dbspaceavail1,$dbindexsp1) " } ###### end Default
} #####end switch
} #####end IsAccessible
} #####end database loop
} #####end Status Offline check
} ####end server loop
Complete SQL Server Agent Job Code
Here is the complete code if you want to run the script to create the SQL Server Agent Job. The job that is created is called "DB_Size_Loader" and is scheduled to run every day at 7pm and you can change as needed. I would put this job on the same server where you created the database and tables.
There are a few lines that you will need to update to match your environment.
Enter the database name where you are storing the data.
- @database_name=N'is_dba',
Enter in values for the server and database where you are storing the data.
- $SQLServer = "WIN-337JP8T8O6K" #use Server\Instance for named SQL instances!
- $$SQLDBName = "Test"
Enter a valid path to write the SQL Agent output file. This file can be used for troubleshooting if something goes wrong.
- @output_file_name=N'e:\temp\BMB_0331.txt',
USE [msdb]
GO
/****** Object: Job [DB_Size_Loader] Script Date: 3/31/2023 3:25:35 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 3/31/2023 3:25:35 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DB_Size_Loader',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Truncate_Table] Script Date: 3/31/2023 3:25:35 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Truncate_Table',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'truncate table sql_databases;',
@database_name=N'is_dba',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Load_DB_Sizes] Script Date: 3/31/2023 3:25:35 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Load_DB_Sizes',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'PowerShell',
@command=N'####DB_Size_Load
#### NOTE : Change the next 2 values for your environment
$SQLServer = "WIN-337JP8T8O6K" #use Server\Instance for named SQL instances!
$SQLDBName = "Test"
#####Next line gets the servers
$SqlQuery = "select is_sqlserver from sql_servers"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet, "Server_Names") | Out-Null
$SqlConnection.Close()
#####....put max length string on names.
$today = Get-Date
$MaxLength = 60
foreach ($row in $DataSet.Tables["Server_Names"].rows) {
$server = $row.is_sqlserver
$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server
if ($srv.Status -EQ "Offline" -OR $srv.Status -EQ "Online" ) {
$dbs = $srv.Databases
ForEach ($db in $dbs) {
if ( $db.IsAccessible ) {
$name1 =$db.name
if ($name1.Length -gt $MaxLength )
{ $name2 = $name1.substring(0, $MaxLength) }
else
{ $name2 = $name1 }
$size1 = $db.size
$dbspace1 = $db.DataSpaceUsage/1KB
$dbindexsp1 = $db.IndexSpaceUsage/1KB
$dbspaceavail1 = $db.SpaceAvailable/1KB
switch ($name1 ) {
''master'' {}
''model'' {}
''tempdb'' {}
Default { Invoke-SQLcmd -ServerInstance $SQLServer -Database $SQLDBName -Query " INSERT INTO sql_databases VALUES ('$server','$name2','$today',$dbspace1,$dbspaceavail1,$dbindexsp1) " } ###### end Default
} #####end switch
} #####end IsAccessible
} #####end database loop
} #####end Status Offline check
} ####end server loop
',
@database_name=N'master',
@output_file_name=N'e:\temp\BMB_0331.txt',
@flags=2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'The_SCHED',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20230329,
@active_end_date=99991231,
@active_start_time=190000,
@active_end_time=235959,
@schedule_uid=N'4bb3ea59-d66e-416f-931a-daff884a463c'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
If you're using SQLExpress which doesn't have SQL Agent, then copy the PowerShell code into a file with a .ps2 extension and create a Windows Task Scheduler job to run the PowerShell code. Note that the PowerShell script uses integrated security to connect the database.
Troubleshooting
When the job runs and if you get the infamous "The term 'Invoke-SQLcmd' is not recognized as the name of a cmdlet, function,...." here is a link on how to get the cmdlet How to Load Invoke_SQLcmd. I think most newer versions of PowerShell will have this cmdlet.
3 - C Sharp Code
Below is the CSharp code that is used to create the .exe. Save the code below in a file with a .cs extension. I called this file "Csharp_Query_Biggest_DBs2.cs".
// Here is the entire program. After playing around with it...seems to work.
// 1 labels, 2 buttons, and a gridview.
using System;
using System.Drawing;
using System.Windows.Forms;
using System.Drawing.Printing;
using System.Data;
using System.Data.SqlClient;
//using System.Web.UI.WebControls;
class MyForm : System.Windows.Forms.Form
{
Label label1;
Label label2;
//TextBox txtbx1;
// ComboBox comboBoxServ1;
Button btn1; Button btn2; Button btn4; Button exit;
DataGridView dataGridView1 = new DataGridView();
BindingSource bindingSource1 = new BindingSource();
SqlDataAdapter dataAdapter = new SqlDataAdapter();
Font SmallFont = new Font("Arial", 8);
Font MediumFont = new Font("Arial", 10);
Font LargeFont = new Font("Arial", 12);
public MyForm()
{
label1 = new Label();
label2 = new Label();
// txtbx1 = new TextBox();
btn1 = new Button(); btn2 = new Button(); btn4 = new Button();
exit = new Button();
label1.UseMnemonic = true;
label1.Text = "BIGGEST SQL DATABASES and SERVERS";
label1.Location = new Point(20, 15);
label1.BackColor = System.Drawing.Color.Transparent;
//label1.BackColor = Color.LightGray;
label1.ForeColor = Color.Maroon;
label1.Size = new Size(350, 15);
label1.Font = LargeFont;
//Lbl.Font = New Font(“Tahoma”, 12.0F)
//label1.Font.Size = FontUnit.Large;
// Mytextbox.Font = new Font("Broadway", 12);
// label1.Size = new Size(200, 150);
btn1.Text = "TOP 50 DBs";
btn1.Location = new Point(30 , 50);
btn1.Size = new Size(100, 20);
btn2.Text = "TOP SERVERS";
btn2.Location = new Point(175 , 50);
btn2.Size = new Size(100, 20);
btn4.Text = "PRINT";
// btn4.Font = new Font("Arial", 8);
btn4.Location = new Point(320 , 50);
btn4.Size = new Size(80, 20);
exit.Text = "Exit";
exit.Location = new Point(500 , 15);
exit.Size = new Size(50, 20);
exit.BackColor = Color.Maroon;
exit.ForeColor = Color.White;
dataGridView1.Location = new Point(15, 80);
dataGridView1.Size = new Size(1200,800);
dataGridView1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells;
//Text to be Displayed in the Caption-Title Bar
this.Text = "CSharp Form for SQL Server";
this.StartPosition = FormStartPosition.CenterScreen;
this.AutoScaleBaseSize = new Size(5, 13);
this.ClientSize = new Size(800, 600); //Size except the Title Bar-CaptionHeight
this.AutoScroll = true;
this.MaximizeBox = false;
this.Controls.Add(label1);
this.Controls.Add(btn1);
this.Controls.Add(btn2);
this.Controls.Add(btn4);
this.Controls.Add(exit);
this.Controls.Add(label2);
this.Controls.Add(dataGridView1);
btn1.Click += new EventHandler(Btn1_Clicked);
btn2.Click += new EventHandler(Btn2_Clicked);
btn4.Click += new EventHandler(Btn4_Clicked);
exit.Click += new EventHandler(Ext_Clicked);
Csharp_Query_SQL_DB_Sizes_Load_1();
} // end MyForm
//// Next part runs when the RUN button is clicked. NOTE: The "Data Source" will need to match your server
public void Btn1_Clicked(object ob, EventArgs e)
{
string connectionString = "Data Source=SQLServer;Initial Catalog=is_dba;Integrated Security=True";
using (SqlConnection SqlCon = new SqlConnection(connectionString))
{
try
{
SqlCon.Open();
}
catch
{
MessageBox.Show("CONNECTION= " + connectionString, "ERROR");
MessageBox.Show("Cant connect to DB. ", "Connect ERROR");
}
// string sql = "select top 20 server_name, backup_finish_date, database_name from backupset where database_name = ''msdb'';";
string sql =
"select top 50 is_sqlserver as 'Server' " +
" ,is_name as 'Database' " +
" ,CONVERT(numeric(10,2),(is_size + is_indexsize + is_available)/1000) as 'Total Size (GB)' " +
" ,CONVERT(numeric(10,2), is_available/1000) as 'Available Space' " +
" ,CONVERT(varchar, CAST((is_size + is_indexsize)* 100/(is_size + is_indexsize + is_available) AS MONEY),1) as 'Pct Full' " +
" from sql_databases " +
// " where is_date_stamp = cast(DATEADD(day, -1, GETDATE() ) as DATE) " + //this line incase your not truncating daily per job
" order by 'Total Size (GB)' desc ";
try
{
/// Next lines fill a data table and bind to the gridview.
SqlDataAdapter sqlDa = new SqlDataAdapter(sql, SqlCon);
DataTable dtb1 = new DataTable();
sqlDa.Fill(dtb1);
dataGridView1.DataSource = dtb1;
}
catch
{
// Next line used to debug if you try new/changed query
MessageBox.Show("query= " + sql, "ERROR");
}
} //end using connection
} //end button 1
public void Btn2_Clicked(object ob, EventArgs e)
{
string connectionString = "Data Source=SQLServer;Initial Catalog=is_dba;Integrated Security=True";
using (SqlConnection SqlCon = new SqlConnection(connectionString))
{
try
{
SqlCon.Open();
}
catch
{
MessageBox.Show("CONNECTION= " + connectionString, "ERROR");
MessageBox.Show("Cant connect to DB. ", "Connect ERROR");
}
string sql =
"select top 50 is_sqlserver as 'Server' " +
" ,CONVERT(numeric(10,2),SUM(is_size + is_indexsize + is_available)/1000) as 'Size of all DBs (GB)' " +
" from sql_databases " +
// " where is_date_stamp = cast(DATEADD(day, -1, GETDATE() ) as DATE) " + //you may want to change -1 to fit the load
" GROUP BY is_sqlserver " +
" order by 'Size of all DBs (GB)' desc ";
try
{
/// Next lines fill a data table and bind to the gridview.
SqlDataAdapter sqlDa = new SqlDataAdapter(sql, SqlCon);
DataTable dtb1 = new DataTable();
sqlDa.Fill(dtb1);
dataGridView1.DataSource = dtb1;
}
catch
{
// Next line used to debug if you try new/changed query
MessageBox.Show("query= " + sql, "ERROR");
}
} //end using connection
/// } //end blank server
} //end button 2
public void Btn4_Clicked(object ob, EventArgs e)
{
PrintDocument printDocument1 = new PrintDocument();
printDocument1.PrintPage += new PrintPageEventHandler(printDocument1_PrintPage);
printDocument1.Print();
}
public void printDocument1_PrintPage(object sender, System.Drawing.Printing.PrintPageEventArgs e)
{
Bitmap bm = new Bitmap(this.dataGridView1.Width, this.dataGridView1.Height);
dataGridView1.DrawToBitmap(bm, new Rectangle(0, 0, this.dataGridView1.Width, this.dataGridView1.Height));
e.Graphics.DrawImage(bm, 0, 0);
}
public void Ext_Clicked(object ob, EventArgs e)
{
Application.Exit();
MessageBox.Show("Successfully Closed", "EXIT");//not Shown! Do you know Why?
}
public void Csharp_Query_SQL_DB_Sizes_Load_1( )
{
string connectionString = "server=SQLServer; Initial Catalog=is_dba;Integrated Security=SSPI";
using (SqlConnection SqlCon = new SqlConnection(connectionString))
{
try
{ SqlCon.Open(); }
catch
{
MessageBox.Show("Cant connect to DB server. Check DB for your security. ", "Connect ERROR");
}
}
}
public static void Main()
{
Application.Run(new MyForm());
}
}
Compiling Code
You've got a CSharp compiler on your PC and its called csc.exe. As I've written in previous articles (see links below), you can take the code displayed below and compile it using csc.exe. This creates an .exe file that can be run on any Windows computer.
To see where the compilers are on your machine at a Windows command prompt run this:
c:\dir csc.exe /s
Note the location of the compiler you want to use. I used the .NET 3.5 compiler and compiled it like this:
C:\Windows\Microsoft.NET\Framework64\v3.5\csc.exe Csharp_Query_Biggest_DBs2.cs
Collect Data and Run the App
If the SQL Agent job has not run yet, you can run it manually so it goes out and collects data from each of the servers.
Then run the exe you created and you should see something like this.
The "TOP 50 DBs" button will show the 50 largest databases on all servers and the "TOP SERVERS" will list which servers are using the most disk space. Note that the "PRINT" button will go to your default printer defined in Windows and it will print whichever list is on your screen.
Next Steps
Here are some links on using DB statistics and Windows forms. The first sets up a big statistics collection system that can easily link up to the form in this tip.
- Collecting DB Stats using PowerShell
- Making a simple CSharp Windows form
- Tip for importing data to SQL Server
About the author
Bernard Black is a DBA for Beacon Health System in South Bend, IN and has been working with databases for over 15 years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2023-05-31
