My production server's database grows unexpectedly and almost I am going to create a job for shrinking the database which runs once in a month. Is it a good idea?
The backups was failed and the time of failure huge transactions were logged which rapidly grows the DB.
I am using SQL Server 2008 R2.
The backups was failed and the time of failure huge transactions were logged which rapidly grows the DB.
If this was a one-time occurrence and your log file (LDF) is grotesquely huge, take a log backup and then shrink the log file (only) to its pre-bloat level just this once. Otherwise, leave it the way it is.
The reason you don't want to shrink your data file is because it fragments your indexes, which can affect performance. In addition, because your indexes get fragmented when you shrink your database. the next time your re-indexing kicks off, your datafile will grow for rebuilds and your log file will grow for reorgs.
The reason you don't want to shrink your log file to a size smaller than its usual working size is because instant file initialization works for data files, but not for log files, so if the log file is going to grow to that size again it'll be a performance hit while it grows.
There's a lot of information on why shrinking is bad. Only do it if you have a good reason.
The disadvantages are efficiency. If your DB keeps expanding, it is doing so for a reason. Shrinking it is doing a lot of IO that the DB will just expend more time to undo as it expands back to its original size. This is a lot of DB effort for no effect in the end.
The effort would be better spent on understanding what is causing it to grow, and (in some cases) mitigating that particular process. For example, perhaps there is a large bulk ETL operation that can be segregated into its own tablespaces / datafiles for better efficiency, with the temporary tables dropped after the operation is complete.
The upshot is that a DB usually has a equilibrium size for a given workload and dataset, and it takes a structural change to move it.
