0

Actually it almost doubled in a few hours.

I am hosting relatively small database in sql server 2008. By hosting I mean I have an account at hosting provider. Last time I checked (call it hour zero) my DB was about 90MB big according to hosting data about disk space used. At the same time I did a backup and got backup file 93MB big.

Today I did some work with DB. Added few hundred record in a couple of tables. Nothing big, no blob fields nothing like that. Al this data shoud amount for less tha 0.1% of DB size. Right now, 7 hours after hour zero I did another backup and I noticed that now my DB occupies 160MB. Meaning it almost doubled in size. But the new backup file is actually smaller than the one at hour zero. It is 81MB.

I assume that SQL Server reserves space in increments and it can reserve empty space for later use. But to double the size of DB in a few hours is a bit to much. In the past it has grown incrementaly pretty much correlated with the amount of actual data stored in DB.

I did run some transaction on DB, but all executed OK so I think none of them was left hanging.

I also regularly run elaborate query which tells me how many rows, hom much used space and how much total space every table needs. Nothing unusual there. Another guess is that transaction log, which I admit do not know much about also takes disk space quota and I suspect this has grown out of proportions. Just to chek I have restored the 81MB backup file, which holds the 160Mb big database. I did shrink database on that restored database and it more than halved to 77MB. There are people who strongly advise against shrinking database, so I do not do that on production DB.

Can someone shed some light on what is going on or what should I keep my eye on?

EDIT: Just checked.

  • Database files size: 131648 KB
  • LOG size:32448 KB

So, I guess my file size has grown. But why?

Did:

SET STATISTICS TIME ON
exec sp_MSforeachdb 'use [?]; EXEC sp_helpfile'

Got:

  • Growth for DB: 1024KB
  • Growth for log: 10%

BradC suggested I run a query, so I did and got:

db_name  type  logical_name  TotalMB  UsedMB  FreeMB  MaxSizeMB  GrowthRate
MyDb     ROWS  MyDB            128.6    73.4    55.2     1000.0     1MB
MyDB     LOG   MyDB_log         31.7     3.2    28.5       NULL     10% 

closed as too broad by Erik Darling, Shawn Melton, RDFozz, LowlyDBA, Mr.Brownstone Jan 8 '18 at 21:29

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    Is your database in simple recovery? What are your autogrow settings configured for on the data and log files? – Nic Jan 8 '18 at 20:41
  • 2
    Do you have any maintenance tasks set up for the database? Rebuilding indexes requires a certain amount of free space in the database, which will no longer be in use once the process completes. Shrinking that away is a bad idea, as a similar amount of space is likely to be needed for future runs of the same jobs. – RDFozz Jan 8 '18 at 20:44
  • @RDFozz It is Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64) . – o115208 Jan 8 '18 at 21:06
0

Can't do much without more info; as a start run the following query on your database:

SELECT DB_NAME() as dbname, type_desc, name as logical_name, 
    TotalMB = CONVERT(decimal(12,1),size/128.0),
    UsedMB = CONVERT(decimal(12,1),FILEPROPERTY(name,'SpaceUsed')/128.0),
    FreeMB = CONVERT(decimal(12,1),(size - FILEPROPERTY(name,'SpaceUsed'))/128.0),
    MaxSizeMB = CASE WHEN max_size = -1 THEN NULL 
        ELSE CONVERT(DECIMAL(18, 1), max_size / 128.0) END,
    GrowthRate = CASE WHEN is_percent_growth = 1 THEN CONVERT(varchar(12),growth) + '%'
            WHEN growth = 0 THEN 'FIXED'
            ELSE CONVERT(varchar(12), growth/128) + 'MB' END,
    physical_name
 FROM sys.database_files WITH (NOLOCK)
 ORDER BY type, file_id;

This query looks into the detail of the database files, and tells you whether you are simply dealing with a lot of empty space, or actual data, and whether it is located in the data file or the log file.

You can also use the "growth rate" to determine if it simply grew in one big chunk, instead of smaller amounts.

Any further action depends on where the unexpected growth actually took place.

  • I have executed your query and put results as edit in question above. And... it seems text cannot be formated on stack exchange so it look like garbage. Click on edit question and in edit box you will see properly formated table with results. – o115208 Jan 8 '18 at 21:33
  • @o115208 Fixed the formatting for you using "code" mode and spaces instead of tabs. Frankly, your numbers don't look that bad to me. SQL likes a lot of "elbow room" in its files to work, for things like reindexing and stuff like that. Can you really not spare the extra 100MB? – BradC Jan 8 '18 at 21:43
  • I can spare extra 100MB. It is just that I was baffled with such a size jump. Until now it has grown steadily and slowly in accordance with data added. Like 0.2% or 0.5% at times I checked. But today almost 100%. This got me worried. – o115208 Jan 8 '18 at 21:47
  • 1
    @o115208 Is all that data in a single large table? If so, reindexing that table would require 2x the table size, which might explain it. – BradC Jan 9 '18 at 15:17
  • Since some time has passed and not much more plausible explanation appeared your answer was the best. At least it showed that DB growth went into free space. Accepted an upvoted. Although... I would like to know more about the strategy that almost doubled db size. If I was near to limit with my quota this would be very bad. – o115208 Jan 13 '18 at 11:31

Not the answer you're looking for? Browse other questions tagged or ask your own question.