Database Size - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Database Size

Author

Topic

trigun
Starting Member

11 Posts

Posted - 2006-09-07 : 20:48:33

Hi Guys,

We have an SQL database that is about 40GB in size. Most of the data types on the tables are set to Char() so we decided to change them into VarChar(). We were able to shrink the 40GB database to 20GB; we did this by creating an identical database with the fields data type set to VarChar and then importing the data from the original database.

The setup was initially fine until we found a glaring increase in the size of the database. In just a span of 6 days, the database grew from 20 GB to 35 GB. We tried rebuilding the indexes on all tables hoping that that the database will release fragmented space but unfortunately it did not work.

Is there a logical explanation on how it ended up acquiring too much space?

Please advice
Thanks....

MichaelP
Jedi Yak

2489 Posts

Posted - 2006-09-07 : 21:16:16

What grew exactly? The data file(s) (.MDF / .NDF) or the transaction logs (.LDF)?

What is the recovery model set to for this database?

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights.

trigun
Starting Member

11 Posts

Posted - 2006-09-07 : 21:37:08

Hi MichaelP,

The MDF is the one that is growing rapidly, It grows about 2GB a day. The recovery model is set to full.

MichaelP
Jedi Yak

2489 Posts

Posted - 2006-09-07 : 21:47:38

2GB's a day for your MDF? Wow.
What is the growth rate set to for that file?

You may want to do a sp_spaceused for each table daily for the next few days and compare the results. This will help you find what table is growing. From there, you can figure out WHY it's growing.

Take a look at Nigel's really nice sp_spaceused script for all tables.
http://www.nigelrivett.net/SQLAdmin/SpaceUsedAllTables.html

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-09-07 : 22:08:44

are you using a different fill factor for your indexes in the new environment?

are you columsn varchar or did you use nvarchar?

-ec

trigun
Starting Member

11 Posts

Posted - 2006-09-07 : 22:27:09

quote:
Originally posted by eyechart

are you using a different fill factor for your indexes in the new environment?

are you columsn varchar or did you use nvarchar?

-ec

Hi EyeChart,

We only used Varchar(). The fill factor is just the same though we added additional clustered index for tables that have no clustered indexes.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-07 : 22:30:45

You may want to look a the difference in size on a table by table basis. You can run the script below in each database to see what the size of each table is and to see if the average bytes per row, or average index bytes per row has changed.

Script to analyze table space usage
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762

CODO ERGO SUM

trigun
Starting Member

11 Posts

Posted - 2006-09-07 : 23:05:04

There is a great amount of increase on the reserved, data, and index_size. Do you have any idea how to fix this problem?

Thanks in advance...

MichaelP
Jedi Yak

2489 Posts

Posted - 2006-09-07 : 23:15:11

Well trigun, I think that's what YOU have to figure out.
If you know what tables are growing, you should be able to track down what is putting data into those tables.

If you are having issues tracking what is doing all the inserting, run SQL profiler to help you track down what statements are being run and what user(s) are calling those statements.

It sounds like someone is inserting a lot of data into one or a few tables.

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-07 : 23:22:05

quote:
Originally posted by trigun

There is a great amount of increase on the reserved, data, and index_size. Do you have any idea how to fix this problem?

Thanks in advance...

How about the number of rows in the tables?

Does the script I posted a link for show a difference in data bytes per row and index bytes per row?

Have you looked at the index fill factors to see if they are the same?

CODO ERGO SUM

trigun
Starting Member

11 Posts

Posted - 2006-09-08 : 00:39:13

quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by trigun

There is a great amount of increase on the reserved, data, and index_size. Do you have any idea how to fix this problem?

Thanks in advance...

How about the number of rows in the tables?

Does the script I posted a link for show a difference in data bytes per row and index bytes per row?

Have you looked at the index fill factors to see if they are the same?

Hi,

The index fill factor are all the same with the old database. There differences between the Average_Data_Bytes_Per_Row and the Average_Index_Bytes_Per_Row. There are cases wherein the Average_Data_Bytes_Per_Row of a table is greater than its Average_Index_Bytes_Per_Row and Vice Versa.

CODO ERGO SUM

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-09-08 : 00:46:31

are you using all varchar(8000) or are you using something sensible?

can you post the results from MVJ's script?

-ec

trigun
Starting Member

11 Posts

Posted - 2006-09-08 : 01:13:55

quote:
Originally posted by eyechart

are you using all varchar(8000) or are you using something sensible?

can you post the results from MVJ's script?

-ec

Hi,

Below is the partial list of the result

Avg. bytes/row Avg. data/row Avg. index/row Avg. unused/row
1557.02623, 590.28730, 966.45652, 0.28242
664.82389, 208.84211, 455.81542, 0.16635
1639.00706, 776.30381, 861.84244, 0.86081
622.51986, 192.87129, 429.35736, 0.29121
2662.76840, 2151.60306, 510.06206, 1.10328
455.24855, 214.47836, 240.67767, 0.09252
1064.88312, 582.23825, 480.61346, 2.03140

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-09-08 : 03:31:35

quote:

Originally posted by trigun

Below is the partial list of the result


Avg. bytes/row  Avg. data/row   Avg. index/row   Avg. unused/row 
1557.02623,	590.28730,      966.45652,       0.28242
664.82389,	208.84211,      455.81542,       0.16635
1639.00706,	776.30381,      861.84244,       0.86081
622.51986,	192.87129,      429.35736,       0.29121
2662.76840,	2151.60306,     510.06206,       1.10328
455.24855,	214.47836,      240.67767,       0.09252
1064.88312,	582.23825,      480.61346,       2.03140

unforunately, that really means nothing to us at all :(

have you compared these stats between the two databases to determine which of your tables is growing too quickly? Or are you seeing that all tables have increased in size in this same way?

-ec

trigun
Starting Member

11 Posts

Posted - 2006-09-08 : 04:38:59

All of the tables have increased in size in the same way :( Any other idea?

Kristen
Test

22859 Posts

Posted - 2006-09-08 : 05:06:57

Very strange, but not sure I have any advice, but asking a bunch of questions might give me, or someone else, a Eureka moment!

How did you transfer the data from the old CHAR database to the new VARCHAR one?

INSERT INTO NEW.dbo.MyTable
SELECT * FROM OLD.dbo.MyTable

DTS ?

BCP ?

or some other way?

How did you modify the columns from CHAR to VARCHAR - completely new CREATE TABLE statement, or some sort of ALTER TABLE statement?

If you use Enterprise Manager, Database, All Tasks, Shrink - how much does it say the database can be shrunk? (Please Cancel out of there, do NOT use the shrink option!)

Do you have maintenance routines for REINDEX? Update Statistics? How often do they run?

"The recovery model is set to full"

How often do your TLog backups run? (They are scheduled, right?)

Kristen

trigun
Starting Member

11 Posts

Posted - 2006-09-08 : 05:18:45

quote:
Originally posted by Kristen

Very strange, but not sure I have any advice, but asking a bunch of questions might give me, or someone else, a Eureka moment!

How did you transfer the data from the old CHAR database to the new VARCHAR one?

INSERT INTO NEW.dbo.MyTable
SELECT * FROM OLD.dbo.MyTable

DTS ?

BCP ?

or some other way?

How did you modify the columns from CHAR to VARCHAR - completely new CREATE TABLE statement, or some sort of ALTER TABLE statement?

If you use Enterprise Manager, Database, All Tasks, Shrink - how much does it say the database can be shrunk? (Please Cancel out of there, do NOT use the shrink option!)

Do you have maintenance routines for REINDEX? Update Statistics? How often do they run?

"The recovery model is set to full"

How often do your TLog backups run? (They are scheduled, right?)

Kristen

Hi Kristen,

1. We used DTS (Import/Export) to transfer the data from one database to the newly structured one.

2. We modify the Char() data types to varchar() by editing each fields manually in the SQL Management Studio (SQL 2005).

3. We have not shrunk the database yet.

4. Yes we have maintenance routine for rebuilding indexes. It is scheduled to run every weekends.

5. The recovery model is set to full

6. We don't have backup schedules for transaction log. We only do differential backups every 12 noon and midnight.

Hope this helps
Thanks...

Kristen
Test

22859 Posts

Posted - 2006-09-08 : 05:43:30

"6. We don't have backup schedules for transaction log."

That's probably the most major issue then (not withstanding that your MDF is growing, rather than your LDF specifically)

If the Recovery Model is set to FULL you need to do Transaction Log backups (probably every 15 minutes would be advisable). You will then have the ability to restore to a moment-in-time.

Alternatively change the Recovery Model to SIMPLE - but then you will only be able to recover to your last full backup (or last full + a subsequent Diff)

Your LDF file will grow continuously UNTIL you do one of these.

"2. We modify the Char() data types to varchar() by editing each fields manually in the SQL Management Studio (SQL 2005)"

This may have had some "inefficiency" knock-on effects. I'm no expert on this, but I remember reading that in-situ changes (such as as probably caused by making the changes via SQL Management Studio - but I don't know that for sure either!) can cause the packing of the data to be looser than would be the case using a brand new DROP TABLE / CREATE TABLE statement (but its also possible that SQL Management Studio used that approach).

Its probably nothing to worry about, but as you were looking to save space it might be worth contemplating at least!

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-08 : 07:14:05

quote:
Originally posted by trigun

All of the tables have increased in size in the same way :( Any other idea?

That really means nothing, unless you are willing to let us know in what way. You posted info from my script, but without showing the complete row, or whether it was from the old database or new one. The info is meaningless without seeing a before or after.

You seem very reluctant to share any meaningful information that we could use to help you, so instead of wasting time trying to draw infomation out of you, I will just say good luck.

CODO ERGO SUM

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-09-08 : 11:33:19

I wonder if Paul Randal might have some insight on what is going on.

Also, is it possible that activity in your database has just picked up to the point that this is normal growth?

Kristen
Test

22859 Posts

Posted - 2006-09-08 : 11:48:05

If a record is being created with small-ish column data, and then UPDATEd with bigger-column-data, it presumably aint going to fit back where it was, and will get moved - maybe the "holes" aren't suitable for reuse ... often enough.

Same deal if a column that is indexed is getting widened often ... index pages needing to be split etc.

Kristen

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources