Help on finding the total space used by a database schema

Question

Help on finding the total space used by a database schema

Guras

SSCertifiable

Points: 6518
More actions
July 19, 2013 at 9:06 am

#280342

I am using SQL server 2008 R2 and I would like to find the total space used by just the schema of the database, not the data.
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Guras (7/19/2013)
I am using SQL server 2008 R2 and I would like to find the total space used by just the schema of the database, not the data.
Thanks

What do you mean by not the data? How much storage space does the ddl consume? Without the data (I assume this also mean indexes), there really isn't much.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Guras SSCertifiable Points: 6518 More actions · Answer 2

Yes, just the tables , stored procs, views, indexes stc.

Thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Guras (7/19/2013)
Yes, just the tables , stored procs, views, indexes stc.
Thanks

The tables are the data. The ddl for tables, stored procs and views is next to nothing. What exactly are you looking for here?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Guras SSCertifiable Points: 6518 More actions · Answer 4

I need to find the disk space estimation to create a new database with all the objects of the current databases ( without the data copied) ..for e.g. I am just goign to run a create script on the current objects of the database and execute on this new datasbase. So, I am trying to get an estimations on how much space it will take since we have lot of about 200 stored procs and tables.

Thank you!

Lowell SSC Guru Points: 323518 More actions · Answer 5

Guras (7/19/2013)
I need to find the disk space estimation to create a new database with all the objects of the current databases ( without the data copied) ..for e.g. I am just goign to run a create script on the current objects of the database and execute on this new datasbase. So, I am trying to get an estimations on how much space it will take since we have lot of about 200 stored procs and tables.
Thank you!

Seans pointing you in the right direction.

a table adds one row of data in sys.objects, and one row per column in sys.columns, and most likely a few rows in sys.indexes;

the size of a single table, even with ten indexes and a hundred columns is almost nothing...maybe a 100K or 200K in size.

it's the data and the indexes that make the difference.

as a proof of concept, i scripted the size and # of objects from a new database and then a create script that does all my objects...1500+tables, and loads of views and procs.

the results:

new database "EmptySchema" size 2.81 size / 1.05 mb space available.

type_desc(No column name)

INTERNAL_TABLE5

SERVICE_QUEUE3

SYSTEM_TABLE45

after the scripts were run:

size 17.06 Meg / 0.13 mb space available.

type_desc(No column name)

CHECK_CONSTRAINT263

DEFAULT_CONSTRAINT889

FOREIGN_KEY_CONSTRAINT2852

INTERNAL_TABLE5

PRIMARY_KEY_CONSTRAINT1678

SERVICE_QUEUE3

SQL_SCALAR_FUNCTION7

SQL_STORED_PROCEDURE68

SQL_TABLE_VALUED_FUNCTION1

SYSTEM_TABLE45

UNIQUE_CONSTRAINT293

USER_TABLE1706

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 6

SIZE
Specifies the size of the file defined in the <filespec>. When a SIZE parameter is not supplied in the <filespec> for a primary file, SQL Server uses the size of the primary file in the model database. When a SIZE parameter is not specified in the <filespec> for a secondary or log file, SQL Server makes the file 1 MB.
size
Is the initial size of the file defined in the <filespec>. The kilobyte (KB), megabyte (MB), gigabyte (GB), or terabyte (TB) suffixes can be used. The default is MB. Specify a whole number; do not include a decimal. The minimum value for size is 512 KB. If size is not specified, the default is 1 MB. The size specified for the primary file must be at least as large as the primary file of the model database.

My guess would be to look at the size of your primary Model database file and that should give you an estimate of the size of an empty, newly created, database on your new server. I've never done it but that would be a logical assumption.

Erin

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

Guras (7/19/2013)
I need to find the disk space estimation to create a new database with all the objects of the current databases ( without the data copied) ..for e.g. I am just goign to run a create script on the current objects of the database and execute on this new datasbase. So, I am trying to get an estimations on how much space it will take since we have lot of about 200 stored procs and tables.
Thank you!

OK now I see what you are after. I would say that 200 procs and tables is considered quite small by current database sizes. This could vary a lot based on how you setup your database and log files. That being said the absolute best way to determine this is to run it once. Then look at the size of your data and log files for the new database. Then there is no estimation, you know exactly how big it will be.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/