[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

SQL Server backup and shrink

Can backup of a SQL Server database and shrink database done together. or have to wait for the full backup and then shrink the data files?
0
espanolanthony
Asked:
espanolanthony
  • 3
  • 2
6 Solutions
 
chapmandewCommented:
no, can't be done together (same statement).

Also, the shrink operation should NOT be done on the data files very often whatsoever...could lead to bad performance.
0
 
Anthony PerkinsCommented:
>>Can backup of a SQL Server database and shrink database done together. or have to wait for the full backup and then shrink the data files?<<
Besides being a very bad idea, the data file will not shrink after a full backup any more than before.
0
 
pcelbaCommented:
The question was probably if you can do these two tasks parallely. But the answer is still NO. It is even mentioned in help:

You cannot shrink the database or transaction log while the database or transaction log is being backed up. Conversely, you cannot create a database or transaction log backup while you are trying to shrink the database or transaction log.

Regarding the performance, I would say the shrinking itself should not decrease performance if you use appropriate FILLFACTOR. The AUTO_SHRINK database option can be useful sometimes.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
chapmandewCommented:
>>, I would say the shrinking itself should not decrease performance if you use appropriate FILLFACTOR. The AUTO_SHRINK database option can be useful sometimes.

That is where you are wrong.  
http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx
0
 
pcelbaCommented:
chapmandew, thanks for the article!  It gave me much more than info about shrinking...

0
 
chapmandewCommented:
very welcome.  Paul Randall was the guy who wrote most (if not all) of the DBCC routines in SQL SErver and a lot of the db engine stuff...he has a LOT of interesting reads on that site.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now