October 25, 2010 at 8:08 am
Dear All,
Can Someone please advise on the below
1.What will happen in internally of the .ldf file of database when we shrink the log file.
2.What will happen in internally of the .mdf file of database when we shrink the data file.
3.What will happen if we shrink the entire database.
Which method is better? since our databases are in huge size i.e, more than 375 GB.
Thanks and Regards,
Ravichandra.
October 25, 2010 at 8:18 am
400GB is not a huge database. Moderately big at best.
Why do you want to shrink in the first place? It's not recommended, certainly not for regular usage.
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
October 25, 2010 at 8:28 am
To get space...as we have databases like that 15 databases.
If we shrink the databases, we can get the less time to finish the backups and also aquire some space.
Thanks and Regards,
Ravichandra.
October 25, 2010 at 8:32 am
Shrinking won't affect your backup speed or time at all.
How much free space is in those databases? (sp_spaceused)
How fast are they growing?
Bear in mind that, unless you've just done some large archiving, it's unlikely that you've regain much space and very likely that the databases will just grow again.
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
October 25, 2010 at 8:38 am
The below are the details:
database_size
-----------------
524441.13 MB
unallocated space
----------------------
96174.97 MB
Reserved
--------------
173558688 KB
Data
--------
111380152 KB
index_size
----------
62152776 KB
unused
------------
25760 KB
Regards,
Ravichandra.
October 25, 2010 at 8:39 am
How fast are they growing? -----> how I can check this pls
Regards,
Ravichandra.
October 25, 2010 at 8:43 am
ravisamigo (10/25/2010)
How fast are they growing? -----> how I can check this pls
You monitor their size over a period of time and see how the size changes.
If you shrink that database that you posted about, you'll regain about 90GB out of the 530, however a DB should have around 10% free space for normal activity, so that means that you'd regain less than 40GB. If the DB is growing it could get back to that size very quickly.
You may well need to get additional storage space here. Unless the databases have had large archive operations,you probably won't get much space back.
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
October 25, 2010 at 8:49 am
Additionally (suprised that Gila hasn't mentioned this yet) the continual grownig and shrinking of the database filse will cause fragmentation of the files, which will add to the overhead of the server attempting to keep track of all of the fragments. During the growth period you will also see performance degradation. This is becuse SQL Server is attempting to expand the physical size of the database file(s) as well as the intense disk IO which is occuring during this period.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
October 25, 2010 at 8:51 am
Paul Randal - Has Provided a very wonderful detail explanation of shrinking a database/File and its affect.
http://technet.microsoft.com/en-us/magazine/ff808322.aspx
and
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply