February 16, 2015 at 4:31 am
Hi SSC Members,
Before shrinking a database , how to determine that how much disk space will be free after shrink and approximately how much time sql server will take to shrink datafiles.
-----------------
Aditya Rathour
SQL DBA
Not Everything that is faced can be changed,
but nothing can be changed until it is faced.
February 16, 2015 at 4:34 am
The first, you can check in the shrink dialog, the second there's no way to determine
First however, why are you shrinking the database?
Unless you've just done some major archiving or some process grew the database beyond what it should be, it's just going to regrow and hence you won't have saved any disk space, just wasted a lot of time.
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
February 16, 2015 at 4:52 am
Thanks for quick reply
I have dropped some archived tables from my database , that's why i want to shrink datafiles.
Sir i have two another confusion regarding datafile shrink:
1) Does shrink activity will block the users to database or not?
2) Datafiles are growing so fast , how it can be diagnose that what queries/activities are the causes to grow datafiles?
-----------------
Aditya Rathour
SQL DBA
Not Everything that is faced can be changed,
but nothing can be changed until it is faced.
February 16, 2015 at 5:03 am
No, it won't block users, but it will slow things down and you will need to rebuild indexes afterwards and that will block users (unless it's an online rebuild)
If your data files are growing, rather just leave the free space in the files to be reused. Unless you've made so much space free that you don't expect it to be reused in 6-12 months, it'll probably work out better just to leave the free space in the files and let it be reused.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply