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
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.
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.
Is there any Bulk Load or BCP IN scheduled
Madhu
Do you use scheduled batch processing
Adam
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...
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.
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.