Database growing unusually

Question

Database growing unusually

san43

Hall of Fame

Points: 3196
More actions
May 9, 2006 at 12:01 pm

#168039

One database is growing unusally like 30GB in 6 hours, couldn't find the reason.
When sp_spaceused is run on one table ,it is showing actual size is 180Gb ,used space is 3GB and unused space 177 GB.
What could be the reason?
What can I do for this?

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

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

SQLBill SSC Guru Points: 51440 More actions · Answer 1

Lots of reasons.......are you doing database maintenance, such as reindexing? That will cause it to grow to a large size.

-SQLBill

Philip Kelley SSCertifiable Points: 6351 More actions · Answer 2

Various internal statistics will get out of date over time, particularly if you're doing a lot of index-intensive work (such as reindexing). Running DBCC UPDATEUSAGE causes SQL to recalculate and reset the values to accurately reflect what's going on in the database. Try that and check again?

Philip

Pedro R. Lopez SSC Eights! Points: 902 More actions · Answer 3

Does any of the process running is this servers use temporals tables ?

It use to happen to me that a store procedure made for a report was ussing temporal tables , and does tables were huge because of the bad programing in the procedure.

Pedro R. Lopez
http://madurosfritos.blogspot.com/[/url]

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

Based on "actual size is 180Gb ,used space is 3GB and unused space 177 GB" I'd say that you've got bad code out there somewhere. Yes, re-indexing can cause space growth 'spurts', but I do not believe that they are of the magnitude that you are seeing. I'd statr with an sp_who/sp_who2 during the periods of exponential growth, then take that information and fine tune it with profiler to find out just where the offending processes are.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

SQLBill SSC Guru Points: 51440 More actions · Answer 5

Something else to check.....is this extra space in the Data files or the log files?

Is your database in FULL Recovery Mode or SIMPLE Recovery Mode? If in FULL Recovery Mode are you doing transaction log backups?

-SQLBill

san43 Hall of Fame Points: 3196 More actions · Answer 6

I am trying to shrink the file using DBCC SHRINKFILE command on one of the datafiles , It runs and gives the result but actually datafile is not reduced.

But I can see that database is not using that much of space.

What can I do?

SQLBill SSC Guru Points: 51440 More actions · Answer 7

After you run the shrinkfile command, you need to run DBCC UPDATEUSAGE command. Refer to the BOL for the proper syntax.

-SQLBill