database size increased dramatically

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 size increased dramatically

Author

Topic

nice123ej
Starting Member

48 Posts

Posted - 2008-06-25 : 23:39:51

hi
my database size was normally 6MB when i back it up
U have done some changes to tabled (changed the key for some tables)
and fixed some Stopred Procs then database size jumped to 66MB !!!!

what happened to make database size jumps from 6MB to 66MB (More than 1100% !!!!)

how to shrink it back to the original size
I tried the Shrink tool, but size still 66MB !!
what can i do?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-26 : 00:34:34

Did you add any indexes, especially clustered ones? Is your fragmentation level high?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

nice123ej
Starting Member

48 Posts

Posted - 2008-06-26 : 00:55:42

I changed the Primary Key for some tables but no other indexes at all
I dont know how to check Fragmentation Level?!!
even though for a database to be increases this much! is this normal?!!
is there any way to shrink it?!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-26 : 08:07:14

quote:
Originally posted by nice123ej

I changed the Primary Key for some tables but no other indexes at all
I dont know how to check Fragmentation Level?!!
even though for a database to be increases this much! is this normal?!!
is there any way to shrink it?!

66MB is not big deal. We handle almost TB of database.

maninder
Posting Yak Master

100 Posts

Posted - 2008-06-26 : 11:43:52

To answer your question:
USE DBNAME
DBCC SHOWCONTIG and check for "logical Scan Fragmentation"

SQL 2005:

select * from sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,NULL)
then check for avg_fragmentation_in_percent on the index_type_desc which is not a HEAP.

Maninder

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-26 : 12:56:35

quote:
Originally posted by nice123ej

I changed the Primary Key for some tables but no other indexes at all

Primary keys are indexes and by default they are clustered.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources