Dear Guru's,
I have read that using the DBCC ShrinkFile is not the best practice but our data and log files keep growing.
Its in Full recovery mode and we do daily full backup and 15 minute transaction log files backup using the maintenance plan but still its not reducing
Below are the sizes of Log and Data files currently in one of the databases.
Log file ( FileSize in MB 31189.94 -- UsedSpace MB ( 19.75 ) and FreeSpaceMB (31170.19)
Data file ( FileSize in MB 64286.13 -- UsedSpace MB ( 32373.06 ) and FreeSpaceMB (31913.06)
Their is lot of bulk load kind of activity going on daily where it inserts and deletes lot of data from these tables. I tried to add the disk space but its just eating up whole disk.
What's the best way to restore this free space to OS and what are the other options and good practices
Thanks
Sri
It's ok to shrink the log file IF it normally doesn't need so much room. If you don't know how much room it actually needs, shrink it to 0 and then immediately regrow it to something smaller than the original. Make sure that the growth is in mega-bytes rather than %. For the size of your database, I'd recommend, as a bit of a swag, to regrow it to 2,000 MB (2GB) with a growth of 500MB or 1000MB and see how things play out.
You say you "tried to add the disk space but its just eating up whole disk." How big a disk did you try adding? If it ate the face off an additional disk, I'd say that you have some code somewhere that has an accidental many-to-many join in it, which is also known as a Cartesian Product and, yes, it will eat whatever it can, although TempDB is normally the victim there.
Soooooo.... before you shrink the log file, Google for code and the necessary alert to monitor what is causing your log file to grow.
Shifting gears to the MDF file, are you also using maintenance plans to do index rebuilds/reorganizes? Either way, what is the size of your largest table including all the indexes?
Jeff,
Yes the settings are in MB for log file its set at 500 MB and for the data 1024 MB . I do monitor the disk place and get the alerts. For D Drive so far added 100 GB and E its at 200 GB. I dont see much growth of TempDB. I am in process of upgrading the DB's to 2014 i hope it gets better. I use Ola's maintenance scripts. Please find below the numbers. Also i cant change the recovery models
| ## Aug 20 2017 7:22AM ## D:7GB;E:102GB |
| ## Aug 19 2017 7:22AM ## D:23GB;E:112GB |
| ## Aug 18 2017 7:22AM ## D:23GB;E:113GB |
RowCounts TotalSpaceMB UsedSpaceMB UnusedSpaceMB
3,911,943 26726 22363 4363
2,300,072 3077 3073 3
2,064,830 1008 1005 2
923,513 572 571 0
3,772,469 428 428 0
1,187,280 330 330 0
2,300,206 176 175 1
59,267 28 27 0
Thanks
Sri
Thanks for taking the time, Sri.
Your largest table may explain the size of the log file if the Clustered Index is being rebuilt or reorganized on a regular basis.
On that note, I'll tell you that I've not defragged any indexes on any of my production boxes since the 17 of January, 2016 (more than a year and a half ago) and performance got markedly better in the first three months and hasn't gotten any worse since then. You should try the same experiment yourself once we get the current problems cleaned up a bit. The key is to rebuild stats that need it.
Getting back to your problem, you monitor your disks and get the alerts but have you done like I suggested and figured out what it is that is causing the growth? That's the first step that I'd take.