Data drive
-
Data drive is full not the log space. If it is ldf then yes, full recovery model with more frequent log backups or simple recovery. But what would be the cause of mdf data file to grow suddenly. Please advise? Just increase the disk space?
-
Have you looked at what objects are using the disk space or how much free space there is on the datafile?
If you right click on the database in the explorer tree of SSMS and choose Reports / Standard Reports there are several reports you can see:
-
Online index rebuilds could cause the MDF file to grow quickly and leave free space behind once it's completed
- onetsql wrote:
Online index rebuilds could cause the MDF file to grow quickly and leave free space behind once it's completed
There's a way to keep that from happening but you do need some extra space somewhere. Determine what your largest index to rebuild is and then tou can do a CREATE INDEX with DROP_EXISTING to "rebuild" the index on a different file group. Then, do all of your other rebuilds in order from the smallest to the largest. When you're done, do the CREATE INDEX with DROP_EXISTING to move the index back to the original file group and drop the other file group.
My Infrastructure teams knows the value of doing things like this an so they gave a a 1TB "DBA ONLY" disk for me to do such things with. My agreement was that I'd never store anything permanent on it. It's been a real lifesafer and has made a whole bunch of things a whole lot simpler.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Another thing to look into is Trace Flag 692 and the problems it solves with "Fast Inserts" that take place in a RBAR fashion. It doesn't cause "sudden growth" but it can cause a shedload of growth depending on your index maintenance (I didn't do any for 3 years... just rebuilt stats. And, yeah... it was an experiment that really paid off.)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply