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.
| Author |
Topic |
|
kpsreenath
Yak Posting Veteran
52 Posts |
 Posted - 2006-03-02 : 15:28:40
|
| Hi AllLast wednesday, there was an increase of 4gb on a particular data file.It was 20gb and now it is 24gb.I checked with the database owners to see whether there is any mass uploading or anything like that which have happened and they are not able to give a solid answer for that.They asked me to give more information on which tables the data got added etc.Is there any way, I can do somemore investigation on how this size increase. I have read about a tool called SQL Compare.Should I be using this on the daily full backups to understand the difference.Is there anyway I can troublshoot this.Thanks in advanceSreenath |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-03-02 : 15:34:55
|
| SQL Compare would not work for your problem. It's to check differences between two schemas. I can't think of anything that would be able to help here as everything would rely on you having captured the data when it was at 20GB. You would have needed to know row counts, table sizes, etc... when it was at 20GB and now that it is at 24GB. Are they sure that the increase is used space and not just free space? What does sp_spaceused show for this database?Tara Kizeraka tduggan |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-03-02 : 15:51:44
|
| You could start by looking at how much space each table in your database is using. The script posted on this link will let you analyze table space usage:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762If you have an old backup from before the increase, you could restore the old backup to a new database, and use the same script to look at how much space each table was using then.You could look for new tables or tables that increased in size. It is also possible that a new index on a large table is what caused the change in size.As Tara said, the increase could be in free space. Maybe someone loaded a large table, and then dropped it. That might cause your database file to grow, and then leave it with a lot of free space. Operations like re-indexing can also cause the database to grow as they transfer the data to the new index structure.CODO ERGO SUM |
|
|
|
kpsreenath
Yak Posting Veteran
52 Posts |
Posted - 2006-03-02 : 16:08:31
|
| I am not sure abt whether its the used space which is increased or not as i dont have the data with respect to the 20gb fileThis is the sp_spaceused showing me nowDbName database size unallocated namexxxxxx 26361.19 MB 5405.58 MBReserved data index_size unused18386544 KB 13733368 KB 4481440 KB 171736 KBI dont understand what these values mean.Is there any documentation of what is reserved space,un allocated space and unused space means?Can someone pls explain me.Out of the 26gb, 23gb is for data and 3 gb for the logspace.ThanksSreenath |
|
|
|
kpsreenath
Yak Posting Veteran
52 Posts |
Posted - 2006-03-02 : 16:14:06
|
| Thanks Tara and MichaelI think now i have a way to troubleshoot this, but still i am little confused with the data which sp_spaceused is providing.This data is not adding up. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-03-02 : 16:19:29
|
| sp_spaceused is documented in SQL Server Books Online.Tara Kizeraka tduggan |
|
|
|
|
|
|
|
|
Data file growth