DaMack


Hopefully I'm posting in the right area. There is a database that has grown to about
41-42 GB in size in about a 2 month period. The previous database had grown to about
22 GB before it was purged out. I'm running this on SQL 2000, and I've tried running all
the DBCC SHINKFILE and SHRINKDATABASE commands to no avail. In this case,
the MDF file is the one that has grown out of control as opposed to the log file (LDF file).

Does anyone have any suggestions on what could be done to control the size



Re: Large database growth out of control

Madhu K Nair


check the space used by the database... 
USE pubs
sp_spaceused @updateusage = 'TRUE'
Since you have mentioned its MDF not LDF then i feel there is actual data exists in the database.. nevertheless, check it.. If there is no data and still MDF consume space then you can shrink the database using SHRINKDATABASE, which you mentioned u have already done... So my gut feeling is the database contains data..
 
Madhu
 







Re: Large database growth out of control

Amit Banerjee - MSFT

Try the steps which Madhu suggested and in case your database unused space is not high for the data file and the database MDF is actually consisting of valid data, then you might want to check what type of transactions have been happening in the past two months.

If there is not much unused space in your MDF file, then SHRINKFILE or SHRINKDATABASE is not going to help you a lot.

Also, you might want to check the usage of char datatype and BLOBs in your database.







Re: Large database growth out of control

Damack

Ok, thanks...I've run these commands and here is the result (this is the same type of database on a test server:

1> use nss
2> exec sp_spaceused @updateusage = 'TRUE'
3> go

database_name

database_size
unallocated space
------------------------------------------------------------------------------

------------------------------------------------- ------------------
------------------
NSS

642.05 MB
0.13 MB


reserved data index_size unused
------------------ ------------------ ------------------ ------------------
656832 KB 653296 KB 1056 KB 2480 KB


It does look like it's pretty filled up. What would happen if I put a limit on the overall growth, will the DB stop working after it reaches it's target size




Re: Large database growth out of control

Arnie Rowland

I'm confused here.

In your original post, you indicated that the database size had grown to 42-43 Gigabytes.

Here, the display indicates a database size of about 650 Megabytes -without much free space.

Help me out here.

Also, to your followup question: When the database has no space available, and AUTOGROW is set - FALSE, then the database effectively becomes READONLY. All INSERT/UPDATE activity is rejected.






Re: Large database growth out of control

Damack

Yes, you are correct. This is a sample DB of the exact same type of database, but happens to be a smaller one I have in a test environment. This database has built up over the last few weeks to be the size it is now, which I thought was some what large for a very small data set compared to the 42 GB DB that runs on a much larger data set. I'm attempting to have these commands run on the actual server that has the larger 42 GB DB and I'll post the results here once I have them, however I suspect it will be similar results.

I guess ultimately I need to look at why the data is inflating the database so quickly as we have seen it grow almost 1 GB in a day!

Thanks for the info on the Autogrow....that is exactly what I needed to know.




Re: Large database growth out of control

Madhu K Nair

Is there any Bulk Load or BCP IN scheduled

Madhu






Re: Large database growth out of control

Adam D. Turner

Do you use scheduled batch processing

Adam






Re: Large database growth out of control

Damack

Sorry, I'm a little green when it comes to the more in depth stuff with SQL. What does a Bulk Load or BCP IN do




Re: Large database growth out of control

Damack

Not that I'm aware of no. In fact, there is nothing that runs on this SQL
server other than this database (for my test environment). The database
with the larger environment has many other databases that run along side
of it, but to my knowledge, there is nothing out of the ordinary that runs on
this database.

It does appear that the database does get a lot of data dumped into it, however
I'm not altogether sure the size accurately reflects the data being stored. All it
captures is file names, directories, owner and sizes among some other attibutes,
however it's not storing the actual files that it scans.

Basically, this database is part of a software package that stores statistical
information, and literal file scans of folders and directories and stores the collected
data to this DB. There can potentially be millions of files scanned, but again, I'm
not sure how this can actually inflate the database that greatly.




Re: Large database growth out of control

Arnie Rowland

So if it is scanning 'millions of files', does it then store millions of 'new' rows of data about those files

And if so, how frequently is such activity occurring

And how much historical data is being preserved

That scenario could easily lead to the db size inflation you are reporting...






Re: Large database growth out of control

NorthwindDBA

What was the result of sp_spaceused on the large (42GB) database

You might want to look at the individual tables and see which one is growing fast.






Re: Large database growth out of control

DaMack

Ok, after further investigation I found that the specific table for this database that is out of control has 4 columns in it. I'll define them below as I suspect there could be another way of storing this data without duplicating the results as what is currently happening. Basically a file scan runs (of various types...each type has it's own table), and the data collected from the scan gets stored into the table. upon subsequent scans, the exact same data it stored all over again with the exception to the ID column which identifies how many times the report has ran.

I'm thinking there must be another way to effecienty store this information only one time in the table (which will prevent excessive growth), and perhaps add another column to identify a value that corresponds with the ID column. The ID column is read by our software to show the instance of the report that may have run on different days. The other fields seem pretty obvious what they are but the 3rd field (Field3) I assume is some sort of attribute or checksum of the file it found. All the results here have been duplicated since the scan ran 2 different times. Any suggestions for this I think this is now getting into a DB architecture issue...Not sure if this post remains appropriate for this forum...please let me know.. (By the way, thanks for all your help so far guys!)

ID Field1 Field2 Field3
-------------------------------------------------------------------------------------------------------
1 C:\Users\alex.gardner\Benefits.pdf DOMAIN\John.Reed 67733
1 C:\Users\alex.gardner\Comp plan.pdf DOMAIN\John.Reed 103066
1 C:\Users\alex.gardner\Users.pdf DOMAIN\John.Reed 220020
1 C:\Users\john.penti\Brochure.pdf DOMAIN\John.Reed 1621659
1 C:\Users\john.sweeny\Jokes.rtf DOMAIN\John.Reed 139246
1 C:\Users\john.sweeny\News article.pdf DOMAIN\John.Reed 1167871
1 C:\Users\John.Reed\07-22-07_1130.jpg DOMAIN\John.Reed 404610
1 C:\Users\John.Reed\07-22-07_1131.jpg DOMAIN\John.Reed 575430
1 C:\Users\John.Reed\07-22-07_1134.jpg DOMAIN\John.Reed 575439
1 C:\Users\John.Reed\07-22-07_1135.jpg DOMAIN\John.Reed 481186
1 C:\Users\John.Reed\07-22-07_1136-2.JPG DOMAIN\John.Reed 111181
1 C:\Users\John.Reed\07-22-07_1136.jpg DOMAIN\John.Reed 459129
1 C:\Users\John.Reed\Comments on Training Guide.rtf DOMAIN\John.Reed 45784
1 C:\Users\John.Reed\Dangerous file.exe DOMAIN\John.Reed 6010424
1 C:\Users\John.Reed\DSCF7248.JPG DOMAIN\John.Reed 894266
1 C:\Users\John.Reed\Sales report Q3.pdf DOMAIN\John.Reed 105558
1 C:\Users\michael.simpson\Marketing plan.pdf DOMAIN\John.Reed 206473
2 C:\Users\alex.gardner\Benefits.pdf DOMAIN\John.Reed 67733
2 C:\Users\alex.gardner\Comp plan.pdf DOMAIN\John.Reed 103066
2 C:\Users\alex.gardner\Users.pdf DOMAIN\John.Reed 220020
2 C:\Users\john.penti\Brochure.pdf DOMAIN\John.Reed 1621659
2 C:\Users\john.sweeny\Jokes.rtf DOMAIN\John.Reed 139246
2 C:\Users\john.sweeny\News article.pdf DOMAIN\John.Reed 1167871
2 C:\Users\John.Reed\07-22-07_1130.jpg DOMAIN\John.Reed 404610
2 C:\Users\John.Reed\07-22-07_1131.jpg DOMAIN\John.Reed 575430
2 C:\Users\John.Reed\07-22-07_1134.jpg DOMAIN\John.Reed 575439
2 C:\Users\John.Reed\07-22-07_1135.jpg DOMAIN\John.Reed 481186
2 C:\Users\John.Reed\07-22-07_1136-2.JPG DOMAIN\John.Reed 111181
2 C:\Users\John.Reed\07-22-07_1136.jpg DOMAIN\John.Reed 459129
2 C:\Users\John.Reed\Comments on Training Guide.rtf DOMAIN\John.Reed 45784
2 C:\Users\John.Reed\Dangerous file.exe DOMAIN\John.Reed 6010424
2 C:\Users\John.Reed\DSCF7248.JPG DOMAIN\John.Reed 894266
2 C:\Users\John.Reed\Sales report Q3.pdf DOMAIN\John.Reed 105558
2 C:\Users\michael.simpson\Marketing plan.pdf DOMAIN\John.Reed 206473




Re: Large database growth out of control

DaMack

Unfortunately we had no choice but to can that DB because the server ran out of space. There were some other critical DB's that run on this server which were more important than this large one. Please see my previous post for more info on what I've discovered.




Re: Large database growth out of control

Arnie Rowland

That validates my earlier post. Thanks.

Now the issue is one of design, and determining what data is appropiate to collect and update.

In order to help you re-design this database, we would need to know the 'purpose' of this data collection/storage scheme. (I suspect that Field3 is the file size.)

Are you attempting to know about changed files, new files, missing files

As you noticed, there will be a more efficient method of accomplishing the task.