DB size not increased

Question

DB size not increased

HBhagat

Hall of Fame

Points: 3341
More actions
May 31, 2011 at 3:57 am

#100384

Hi,
Every 3 Months we have to load large chunks of raw data into our Database. There is no problem of space or bcp anything.
My problem is the DB size has not increased after inserting data into the database.
The below are the different dates when I had taken the size of the database .Its same while it should have an increase after 16-05-2011.
Date 18/04/2011 16/05/2011 23/05/2011 31/05/2011
Size 103.1977051103.1977051103.1977051103.1977051
The tables have got the data.
The details about the table
TableName Total_rowsreserverd Data index_size unsued
Table 1 10849462343624 KB 342104 KB1272 KB248 KB
Table 2 5313536168008 KB167336 KB640 KB32 KB
Table 3 273461386280 KB85888 KB344 KB48 KB
Table 4 1407422184 KB2144 KB16 KB24 KB
Table 5 10520714360 KB11856 KB2472 KB32 KB
Table 6 674831096 KB1032 KB16 KB48 KB
Table 7 515497128 KB5816 KB1248 KB64 KB
Table 8 31116520 KB504 KB16 KB0 KB
Table 9 262103672 KB2960 KB648 KB64 KB
Table 10 9108 648 KB600 KB8 KB 40 KB
Table 11 386 80 KB16 KB8 KB 56 KB
Table 12 376 40 KB16 KB24 KB0 KB
I used sp_spaceused to know the table size.
This database is of SQL 6 version.
Please help me in finding why the DB size did not increase?
Thanks,
Harsha

Viewing 15 posts - 1 through 15 (of 16 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Harsha

Every three months? You've barely got six weeks between the first and last dates. Could the database file be not growing because there's sufficient space in the file to accomodate the data you're inserting?

John

HBhagat Hall of Fame Points: 3341 More actions · Answer 2

Hi John,

Thanks for the quick reply,

I am not familiar with the filegroups details...Can you shed more light on this please, or any link where I can find the details..

The dates are for the last 6 weeks as I had started taking the details in last 6 weeks.. ..

Thanks,

Harsha

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Harsha

Use sp_spaceused to get the total free space in the database (includes data and log files). Then use DBCC SQLPERF(LOGSPACE) to calculate the amount of free space in the log, and substract that. This works on "modern" versions of SQL Server... but version 6 - who knows!

John

HBhagat Hall of Fame Points: 3341 More actions · Answer 4

Sorry being acting Dumb.. but I am not able to understand what am I doing ...

By using sp_spaceused I got

Database Name database_sizeunallocated_space

DB 1 105674.45 MB1594.84 MB

Datbase_Name reserverd Data index_size unsued

DB 1 106562344 KB99689488 KB3205800 KB3667056

KB

By using SQLperf(logspace)

Log SizeLog Space UsedStatus

14.81256.2269249 0

Please help..

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Unallocated (ie free) space in the database including log is 1595MB. Log size is 14MB, of which 6% is used. That means log free space is 94%, which in MB is... well, I'm sure you've got a calculator. Take that figure away from your 1595 and you have the number of free MB in your data file(s).

John

crazy4sql SSCoach Points: 19590 More actions · Answer 6

if you really want to play and learn then take this database in some test environment.

Modify the initialsize of database files(mdf and ldf) to 5mb(assuming your current database files are less than 5 mb in size else increase it to the actual value) both and disable autogrowth.

Now put the data in database by the same process as you doing now, you will very soon get the error as " No more space available in mdf/ldf"

Then only you will be able to understand the importance of all the query and guidance provided above by John.

----------
Ashish

HBhagat Hall of Fame Points: 3341 More actions · Answer 7

Hi John,

I have calculated the values.

I need to give a good explanation as to what has happened or how it had happened to my director.

I am not very familiar with the filegroups. If you can provide a link from where I can get a good brief about filegroups, their allocation and how to calculate the basic details please.

Thanks,

Harsha

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

Harsha

Try this:

http://www.lmgtfy.com/?q=filegroups

Nobody has mentioned filegroups in this thread, so it's not particularly relevant to the problem you've described. However, it certainly won't do you any harm to do a bit of background reading to understand the differences between files and filegroups.

John

HBhagat Hall of Fame Points: 3341 More actions · Answer 9

Hi John,

Thanks for the Link..

I was checking them as well , but the thing what you had mention no where I could find.

Thanks.

I have mailed my boss... over this .

Thanks everybody...

ankur_libra Hall of Fame Points: 3999 More actions · Answer 10

Hi,

To ensure current values are returned, use:-

USE DB Name;

GO

EXEC sp_spaceused @updateusage = N'TRUE';

GO

Try it and find out the database size. Hope it works..

With regards,

Ankur

ninad.soman0 Ten Centuries Points: 1111 More actions · Answer 11

Hi,

hey will removing the "@updateusage = N'TRUE'" make difference ?

I tried without it and the results were same ...

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

It is impossible that you didn't load that much data in the db that it would make a huge difference???

shiv-356842 SSCarpal Tunnel Points: 4310 More actions · Answer 13

Hi Experts,

can you and body please explaine what is the differences in the below to commands

EXEC sp_spaceused @updateusage = N'TRUE';

EXEC sp_spaceused

i am using in test database i am also getting same result please explaine me bit cleare....

Thanks in advance...

Cath Trimble SSCarpal Tunnel Points: 4201 More actions · Answer 14

Ninad, Shiv,

Search for sp_spaceused in BOL. You will see:

There are some situations, for example, after an index is dropped, when the space information for the table may not be current... Use updateusage only when you suspect incorrect values are being returned.

Which suggests that in your case the correct values are already being returned by sp_spaceused and updateusage is not needed.

BOL is always the first place to go for questions like this.