0

We have two databases in SQL Server 2005 with installed navision database in both database. Due to disk space I want to shrink old database which is not in use and having 40% free space in .mdf and 20% free in .ndf to 10% each. Will it affect performance or any issue due to shrinking in second database (live)?

  • Uday, you mention you want to shrink the old database which isn't in use. In that case then it won't affect performance as you aren't using it..... but in that case why not just drop the database and claim back all the space? – Mat Jul 15 '16 at 8:16
4

It will affect overall server performance (so second database will be affected).

Shrinking database affects I/O mostly, as it moves pages around data files. But whole operation does put load on CPUs too and it's fully logged, so with database in FULL RECOVERY mode it will put a strain on Your transaction log file too - it will grow up a lot.

Shrinking file has more downsides You should be aware of. It might make Your datafiles bigger (because it needs space to move pages around). It will cause fragmentation, and rebuilding indexes needs space in data files too.

Only time shrink is relatively safe is using WITH TRUNCATEONLY option.

Try avoiding data file shrink, as there are better options. For example moving tables and indexes into new filegroup and dropping old one - this one also requires provisioning enough space for operation, but is more elegant than shrink and doesn't cause fragmentation.

Of course there are situations when shrink is ok (or is the only possible option), but You have to be aware of it's downsides and remember to defragment database afterwards. When space is an issue, defragmenting with DBCC INDEXDEFRAG or ALTER INDEX REORGANIZE will prevent database from growing up again after shrink

Paul Randal wrote few important articles about shrinking:

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-930-data-file-shrink-does-not-affect-performance/

http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.