[Notes from Pinal]: Search on the internet about ‘Shrinking Database’, you will find plenty of advice why it is bad and it should not be done. If you do not believe me, just try it yourself. Even I have blogged about it before that it is an absolutely bad thing to do. However, there are very few blogs which talks about how to solve this ancient problem. The reason, I call it ancient problem is that there are quite a few organizations which are doing this for many years and they have many different places where they have included Shrinking Database code. There are quite a few places, where I see that people have created database jobs to shrink the database. I was talking to Tim about this and he comes up with a beautifully simple script where he demonstrated how to find out jobs which are shrinking database files.
Linchpin People are database coaches and wellness experts for a data driven world. In this 16th episode of the Notes from the Fields series database expert Tim Radney (partner at Linchpin People) explains a very simple script to find out jobs shrinking database files.
Often when analyzing a SQL Server Instance I come across jobs that are automating shrinking a database file. Checking for a shrinking operation is important when analyzing a server for a number of reasons. One is that anytime a data file or log file has to grow, transactions have to wait until the growth operation is complete thus causing a performance impact. Shrinking a log file can contribute to high virtual log file counts and shrinking a data file will lead to fragmenting the database.
I check to see if any database maintenance plans exist and if so I check to see if auto shrink has been chosen, occasionally I find this is the case. I also have a TSQL script that will search for any reference to the word ‘shrink’ in a job step. I find custom jobs like this more often than auto shrink being used in a maintenance plan. Often times the justification for someone configuring a job like this is due to drive space issues.
In cases where the shrink is to reclaim drive space it is most often due to not having a proper backup routine in place for the transaction logs or a process that keeps a transaction open for an extended period of time. The transaction log will need to be large enough to handle your index maintenance, ETL processes and transactions that occur between log backups. If you find that you are having an erratic large growth, then you will need to examine what transactions or processes are holding open an active transaction for such a long duration or the frequency of your log backups.
Below is the script I use to search for the word shrink in any tsql job step.
DECLARE @search VARCHAR(100)
SET @Search = 'shrink'
SELECT A.[job_id],
B.[name],
[step_id],
[step_name],
[command],
[database_name] FROM [msdb].[dbo].[sysjobsteps] A
JOIN [msdb].dbo.sysjobs B ON A.job_id = B.[job_id] WHERE command LIKE '%' + @Search + '%'
ORDER BY [database_name],
B.[name],
[step_id]
If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.
Note: Tim has also written an excellent book on SQL Backup and Recovery, a must have for everyone.
Related Articles:
- SQL SERVER – SHRINKDATABASE For Every Database in the SQL Server
- SQL SERVER – What the Business Says Is Not What the Business Wants
- Shrinking Database is Bad – Increases Fragmentation – Reduces Performance
Reference: Pinal Dave (https://blog.sqlauthority.com)
