Shrinking VLDB''S
-
I have a VLDB. It is about 120GB of physical disk space. Periodically, the MDF bloats from 120GB to 230+ GB. It takes as much space as there is available to it on the drive.
This occurs whether data is imported into it or not.
It happens regularly but not on a schedule (i.e 1-2 times/week but different days of the week)
There is a job that reindexes the tables periodically, which I thought was the culprit, but the job is not disabled and the DB is still ballooning.
What would cause the DB to bloat?
Also, it takes several hours to shrink the DB using DBCC SHRINKDATABASE or SHRINKFILE.
If I try to truncate only, without compressing, it doesn't shrink it at all. Is there a faster way to shrink the DB?
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me -
Do you have auto-update statistics turned on as well? If you've got a regular re-indexing job already setup then turn off the auto-update.
--------------------
Colt 45 - the original point and click interface
-
Hi Phil,
Yes I do have a job that recreates index every week.
What else can I do other than turing-off auto update
thanks
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me -
Was/Is the auto-update turned on? If so then this could be the reason for the irregular bloating. If you turn it off, shrink the Database/Tran log, you should have more stable growth.
As for quicker ways to shrink the database, Truncate Table works wonders
Really, with a database of that size it will take time to compress the data pages and shift the data to the start of the file. It is a lot of data to shuffle around.
--------------------
Colt 45 - the original point and click interface -
Depending on the complexity of the database it's sometimes faster to just dump the data out to disk via bcp, truncate the tables, drop indexes, shrink the database to the correct size then reload the data and reindex than it is to shrink/compress/reorganize the database.
Very counterintuitive but it works, particularly if you're dealing with a fairly simple database (e.g. just a few very large tables) with few or no constraints, etc.
-
I agree with Joe, but keep in mind that truncate table can get a little more difficult, you need to drop/recreate sp's & views.
see BOL:
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
JP
-
Hi!!
Try to run your job (Shrink @DB and Shrink @DBFile) in QA that works faster than scheduled job...
Regards
Shashank
Regards,
Papillon
-
- There is a job that reindexes the tables periodically, which I thought was the culprit, but the job is not disabled and the DB is still ballooning.
You mean the job is still enabled? What if it is not enabled - do you still get the bloat? When I reindex one of our customer's DBs (it's at about the 2GB mark), the transaction log can easily bloat out, which is expected. But you say your MDF is extremely bloated - does SQL seem to indicate that there is a lot of free space in the database once it becomes bloated?
What output do you get when you run
sp_spaceused
in QA for your database?
-
Try to schedule reindexing on the weekend, putting your Database in simple recovery mode before that process.
-
If your database is suddenly doubling in size, but shrinks back to normal with SHRINKDATABASE, it could be because either some process is creating a lot of data temporarily and then deleting it, or updates of a badly-chosen clustered index are fragmenting existing tables. sp_spaceused and DBCC SHOWCONTIG could be useful in finding out whether you have uncommitted free space in the database or fragmented tables with high average free space in each page.
Some actions in Enterprise Manager table design mode cause it to create a temporary duplicate of the original table. Reindexing tables also requires extra space to work in, but I assume these are low-probability explanations.
If it is truly a mystery why the database grows, you might run SQL Profiler and look for page allocation events. On the other hand, this will eat a lot of space if you leave it running and could become more of a problem than a solution.
You could schedule a job to check the database size (size column in sysfiles) at intervals (1-5 min?), and if it sees the database has grown too much (over 150GB? faster than 1GB/min?) it could run DBCC INPUTBUFFER on all active SPIDs to log what is running.
Finally, you could combine these ideas and have a periodic job that checks database size and starts a profiler trace on the fly (look at extended stored procs under SQL Profiler in BOL) that runs for a limited time. This would collect a limited amount of data at exactly the right time and should tell you what you need to know.
-
Scott is on the right track with his previous post...
I recently had an indexing issue with a huge non-clustered table that sounds similar to this. Are any of your tables stored in heaps, without a clustered index?
A programmer inadvertantely Deleted the Non-clustered index that was the Primary Key for the table. She wanted to recreate that index and used the generic syntax:
CREATE INDEX zip_ind
ON authors (zip)
WITH FILLFACTOR = 100
Because the statement assumed the Index was to be CLUSTERED, It created a local COPY of the table AND all of the nonclustered indexes.... almost 3 times the size of the original NonCLustered Index. WOW! I had capped that database limit so as not to run out of disk, but had it finished, it would have shown your symptoms and I would be asking the same question: Who extended my database$&*#)#$^
Stuart
"Chance is always powerful. Let your hook be always cast. In the pool where you least expect it, will be a fish" - Ovid
-
Good point - one of those things you probably wouldn't think of if you hadn't come across it before
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply