database is 350 gb?? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

database is 350 gb??

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-05-16 : 05:47:05

my database is 350 gb

How can I determine what is taking up all that space??

ratheeshknair
Posting Yak Master

129 Posts

Posted - 2008-05-16 : 06:12:20

Are you sure that your database doesnt have that much data.

RKNAIR

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-05-16 : 07:10:20

it has data but that much - it doesn't make sense?

is there a way to compress or to tell what is taking up all that space?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2008-05-16 : 09:25:36

sp_spaceused

--------------------
keeping it simple...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-05-16 : 10:44:30

You can use this script to look at the amount of space that is being used by each file in your database and how much unused space there is in each file:
Get Server Database File Information
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058

You can use this script to find how much space each table in the database is using:
Script to analyze table space usage
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762

CODO ERGO SUM

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-17 : 17:41:41

Or run standard db disk usage report.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-05-18 : 02:45:11

thanks those were very helpful

what does it mean unused space -- is this space that is allocated to the sql? and counting in the sql space? as i show a lot of unused space in the db and the log?Is there something I should do about this?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-05-18 : 03:59:00

what is the best way to archive data from big tables?
I don't need it now but would like it saved?
should i move it to a different db?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-18 : 21:32:30

Unused space in data file is allocated space but not used yet, leave that if server has enough disk space. For archiving, you can move old data to archive table in same db.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-05-19 : 02:23:43

how can i get rid of the unused space?

Is it better to move the larger table to a separate database or does that just make it take up more space? Any gain of doing that?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-19 : 23:30:41

If those unused space will not be used anymore, you can shrink db to release those space. Archiving table in same db or to another db makes no difference in disk usage.

mdubey
Posting Yak Master

133 Posts

Posted - 2008-05-26 : 20:23:44

Please first you may use "sp_spaceused" command on the server. It will tell you how much space used in the DB and then try to shrink the DB and log file.

After this process you can again use the same SP "SP_SPACEUSED" then only you come will come to know what is the exact size of the DB.

Manoj
MCP, MCTS

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-26 : 20:36:06

Use 'sp_msforeachtable 'sp_spaceused "?"'' to find out space used by each table, and don't shrink log file if it's not in unreasonable size.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources