August 20, 2017 at 6:24 pm
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
August 20, 2017 at 9:34 pm
srik18 - Sunday, August 20, 2017 6:24 PMDear 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 Moden
Change is inevitable... Change for the better is not.
August 20, 2017 at 10:01 pm
srik18 - Sunday, August 20, 2017 6:24 PMDear 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
Try Testing trace flag 610.
@JayMunnangi
August 21, 2017 at 12:14 am
JakDBA - Sunday, August 20, 2017 10:00 PMsrik18 - Sunday, August 20, 2017 6:24 PMDear 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
SriTry Testing trace flag 610.
What you really mean is to try "Minimal Logging" for bulk loads. That's a great idea to prevent the expansion of the log file in the future but the op wants to know how to reduce the current size of the log file, which I explained, and how to reduce the size of the MDF file without the massive fragmentation that occurs during such a shrink which is followed by massive regrowth when rebuilding the indexes, all of which are relatively incredibly slow.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 21, 2017 at 12:36 am
JakDBA - Sunday, August 20, 2017 10:00 PMTry Testing trace flag 610.
Trace flag 610 is useless unless he also switches to bulk-logged or simple recovery modes, with their different effects on ability to recover in a disaster.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 21, 2017 at 12:37 am
srik18 - Sunday, August 20, 2017 6:24 PMLog 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.
During those loads, how much free space does the log file have?
How often are you doing log backups?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 21, 2017 at 5:58 am
Jeff Moden - Sunday, August 20, 2017 9:34 PMsrik18 - Sunday, August 20, 2017 6:24 PMDear 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
SriIt'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
August 21, 2017 at 6:19 am
GilaMonster - Monday, August 21, 2017 12:37 AMsrik18 - Sunday, August 20, 2017 6:24 PMLog 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.
During those loads, how much free space does the log file have?
How often are you doing log backups?
Geff,
Logs are being backed up every 15 minutes. I need to check the % free when bulk load is happening. This is being done by 3rd party application which i dont have control
Thanks
Sri
August 21, 2017 at 2:08 pm
You also need to verify that the log is in status to be shrunk, like so:
SELECT log_reuse_wait_desc, *
FROM sys.databases
WHERE name = '<your_db_name>'
If the result is 'NOTHING', then carry on as before, the issue is as already stated. But if it's something else, you'll need to look into that reason.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 21, 2017 at 8:48 pm
srik18 - Monday, August 21, 2017 5:58 AMJeff Moden - Sunday, August 20, 2017 9:34 PMsrik18 - Sunday, August 20, 2017 6:24 PMDear 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
SriIt'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 0Thanks
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy