Server Fault Asked on December 25, 2021
Basic: what’s the size on disk of my MS SQL Server DB?
More: can I quickly see where the data is? i.e. which tables, logs, etc
If you want to check database size of particular database using SSMS Gui
Go to Server Explorer -> Expand it -> Right click on Database -> Choose Properties -> In popup window choose General tab ->See Size
Source: Check database size in Sql server ( Various Ways explained)
Answered by Vikas Lalwani on December 25, 2021
There are several descriptions of how to do it from the GUI.
Real DBA's know: GUI's are for chumps.
sp_helpdb
Returns a recordset of all of the file names, locations, space on disk, and type.
You can also retrieve the file names from each databases's sysfiles table.
Answered by Chris MIller on December 25, 2021
The easiest way (no typing!): In SQL 2005/8 from Management Studio, right click the database, select Reports, Standard Reports, Disk Usage (also By Top Tables, Table and Partition).
Answered by user3047 on December 25, 2021
This is a query/view that gets all of this info, and more, without any "evil" cursors or loops. ;-)
/*
vwTableInfo - Table Information View
This view display space and storage information for every table in a
SQL Server 2005 database.
Columns are:
Schema
Name
Owner may be different from Schema)
Columns count of the max number of columns ever used)
HasClusIdx 1 if table has a clustered index, 0 otherwise
RowCount
IndexKB space used by the table's indexes
DataKB space used by the table's data
16-March-2008, [email protected]
31-January-2009, Edited for better formatting
*/
--CREATE VIEW vwTableInfo
-- AS
SELECT SCHEMA_NAME(tbl.schema_id) as [Schema]
, tbl.Name
, Coalesce((Select pr.name
From sys.database_principals pr
Where pr.principal_id = tbl.principal_id)
, SCHEMA_NAME(tbl.schema_id)) as [Owner]
, tbl.max_column_id_used as [Columns]
, CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [HasClusIdx]
, Coalesce( (Select sum (spart.rows) from sys.partitions spart
Where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount]
, Coalesce( (Select Cast(v.low/1024.0 as float)
* SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
Where i.object_id = tbl.object_id )
, 0.0) AS [IndexKB]
, Coalesce( (Select Cast(v.low/1024.0 as float)
* SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
Where i.object_id = tbl.object_id)
, 0.0) AS [DataKB]
, tbl.create_date, tbl.modify_date
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS idx ON (idx.object_id = tbl.object_id and idx.index_id < 2)
INNER JOIN master.dbo.spt_values v ON (v.number=1 and v.type='E')
Enjoy.
Answered by RBarryYoung on December 25, 2021
This script loops through all of the tables in the current database and shows how much space each one takes up for data, indexes, and unused space:
http://sqlserverpedia.com/wiki/Calculate_Current_Table_Sizes
Answered by Brent Ozar on December 25, 2021
run this to get the size per table:
/******************************************************************************
** File: “GetTableSpaceUsage.sql”
** Name: Get Table Space Useage for a specific schema
** Auth: Robert C. Cain
** Date: 01/27/2008
**
** Desc: Calls the sp_spaceused proc for each table in a schema and returns
** the Table Name, Number of Rows, and space used for each table.
**
** Called by:
** n/a – As needed
**
** Input Parameters:
** In the code check the value of @schemaname, if you need it for a
** schema other than dbo be sure to change it.
**
** Output Parameters:
** NA
*******************************************************************************/
/*—————————————————————————*/
/* Drop the temp table if it's there from a previous run */
/*—————————————————————————*/
if object_id(N'tempdb..[#TableSizes]') is not null
drop table #TableSizes ;
go
/*—————————————————————————*/
/* Create the temp table */
/*—————————————————————————*/
create table #TableSizes
(
[Table Name] nvarchar(128) /* Name of the table */
, [Number of Rows] char(11) /* Number of rows existing in the table. */
, [Reserved Space] varchar(18) /* Reserved space for table. */
, [Data Space] varchar(18) /* Amount of space used by data in table. */
, [Index Size] varchar(18) /* Amount of space used by indexes in table. */
, [Unused Space] varchar(18) /* Amount of space reserved but not used. */
) ;
go
/*—————————————————————————*/
/* Load the temp table */
/*—————————————————————————*/
declare @schemaname varchar(256) ;
-- Make sure to set next line to the Schema name you want!
set @schemaname = 'dbo' ;
-- Create a cursor to cycle through the names of each table in the schema
declare curSchemaTable cursor
for select sys.schemas.name + '.' + sys.objects.name
from sys.objects
, sys.schemas
where object_id > 100
and sys.schemas.name = @schemaname
/* For a specific table uncomment next line and supply name */
--and sys.objects.name = 'specific-table-name-here'
and type_desc = 'USER_TABLE'
and sys.objects.schema_id = sys.schemas.schema_id ;
open curSchemaTable ;
declare @name varchar(256) ; /* This holds the name of the current table*/
-- Now loop thru the cursor, calling the sp_spaceused for each table
fetch curSchemaTable into @name ;
while ( @@FETCH_STATUS = 0 )
begin
insert into #TableSizes
exec sp_spaceused @objname = @name ;
fetch curSchemaTable into @name ;
end
/* Important to both close and deallocate! */
close curSchemaTable ;
deallocate curSchemaTable ;
/*—————————————————————————*/
/* Feed the results back */
/*—————————————————————————*/
select [Table Name]
, [Number of Rows]
, [Reserved Space]
, [Data Space]
, [Index Size]
, [Unused Space]
from [#TableSizes]
order by [Table Name] ;
/*—————————————————————————*/
/* Remove the temp table */
/*—————————————————————————*/
drop table #TableSizes ;
taken from Robert Caine blog
This code is for Microsoft SQL 2005+
Answered by balexandre on December 25, 2021
You can see the physical files in sys.database_files
. This has the path to the file and the size (in blocks IIRC).
sp_spaceused
will show you how much space an individual object takes up.
Answered by ConcernedOfTunbridgeWells on December 25, 2021
Run Start Programs Microsoft SQL Server Enterprise Manager. Open Database sheet, in property %databasename% you can see location Data filrs & Transaction files.
Answered by nerf on December 25, 2021
You'll probably want to start with the sp_spaceused command.
For example:
sp_spaceused Returns information about the total size of the database
sp_spaceused 'MyTable' Returns information about the size of MyTable
Read the docs for all the things you can get information about. You can also use the sp_msforeachtable command to run sp_spaceused against all tables at once.
Edit: Be aware the command sometimes returns multiple datasets, each set containing a different chunk of stats.
Answered by David on December 25, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP