| Author |
Topic |
|
mahajanakhil1985
Yak Posting Veteran
71 Posts |
 Posted - 2010-05-19 : 09:52:55
|
| I was searching through the Net to find the disadvantages of keeping a SQL Server database on autoshrink option but didn't get any satisfactory response. Are there any performance implications of doing so?Thanks!! |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-19 : 11:13:21
|
| Autoshrink will reduce the size of files, which will immediately grow back to the same size because of normal activity. This repeated Shrink/Grow fragments the files, and the internal SQL logical meta data, and leads to reduced performance.With regular backups SQL filesize should be "static" (i.e. growing at whatever rate you add data), not growing at an alarming rate.Schedule TLog backups for every 15 minutes if you use Full Recovery Model (if you only backup the Log once a day, or less, then, Yeah, the Tlog will become huge ...) |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
|
|
mahajanakhil1985
Yak Posting Veteran
71 Posts |
Posted - 2010-05-19 : 13:35:45
|
| So, when should be shrink the database/file manually and how do we know how much space is occupied and how much is free? And how do we know if shrinking the database/file would not cause any harm effect? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-19 : 17:18:49
|
| Manual shrink if you have made an exceptional, one-off, deletion of (say) stale data and it has caused the logs to grow larger than is normally the case.Or perhaps if you did a one-off bulk import of data (again, which caused the Log file to grow above its normal size).In such circumstances you should shrink the log file back to its "normal size" |
|
|
|
mahajanakhil1985
Yak Posting Veteran
71 Posts |
Posted - 2010-05-20 : 07:57:50
|
| @kirstenThanks!This was about the log files. Under what conditions, we should shrink data files? And how can we know if the data file shrink operation will be helpful and that it won't degrade the performance.I am specifically asking about a database table/view that contains information about space that will be vacated after we issue shrink file command. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-05-20 : 10:00:55
|
quote:
Originally posted by mahajanakhil1985
Under what conditions, we should shrink data files?
When you've archived or purged large amounts of data and don't expect the free space to be reused in a significant period of timequote:
And how can we know if the data file shrink operation will be helpful and that it won't degrade the performance.
That's easy. Shrink will never help performance. Will almost always degrade performance due to index fragmentation.You can use sp_spaceused to see how much free space there is in the database.--Gail ShawSQL Server MVP |
|
|
|
mahajanakhil1985
Yak Posting Veteran
71 Posts |
Posted - 2010-05-20 : 12:36:26
|
| Thanks all!!! |
|
|
|
mahajanakhil1985
Yak Posting Veteran
71 Posts |
Posted - 2010-05-24 : 11:10:05
|
| @GilaMonsterI think sp_spaceused tells us only about unallocated free space in the database and not free space that could be reclaimed after performing shrink of database.How could I know the free space that may be vacated if we perform DBCC SHRINKDATABASE. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-05-24 : 12:12:49
|
| Yes, it tells about unallocated free space, and that's what shrink file will release if you use it.--Gail ShawSQL Server MVP |
|
|
|
mahajanakhil1985
Yak Posting Veteran
71 Posts |
Posted - 2010-05-24 : 13:02:39
|
| Okay. I was mistaken then.I have a different scenario then.Suppose lot of deletions have occurred in a table due to which space in data pages got freed. Can we push all remaining rows on one side and free the remaining space?If yes, 1. how can we predict the space that would be freed before actually shrinking the space?2. How can we actually free this space? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mahajanakhil1985
Yak Posting Veteran
71 Posts |
Posted - 2010-05-24 : 13:18:58
|
| @tkizerCould you please let me know one more thing?Does a data page become added to unallocated space if all rows in that page get deleted? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mahajanakhil1985
Yak Posting Veteran
71 Posts |
Posted - 2010-05-24 : 13:30:53
|
| @tkizerIf so is the case, then this space should become unallocated and should be shown by sp_spaceused procedure as unallocated. If so, then it should be shrunk by DBCC SHRINKFILE procedure. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mahajanakhil1985
Yak Posting Veteran
71 Posts |
Posted - 2010-05-24 : 14:33:02
|
| I don't have such a large clustered index to test its effects. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-05-24 : 15:36:35
|
quote:
Originally posted by mahajanakhil1985
Does a data page become added to unallocated space if all rows in that page get deleted?
Usually, yes. Doesn't necessarily mean it'll happen immediately.--Gail ShawSQL Server MVP |
|
|
|
zstarsales04
Starting Member
20 Posts |
Posted - 2010-05-25 : 02:32:39
|
| spam removed |
|
|
|
mahajanakhil1985
Yak Posting Veteran
71 Posts |
Posted - 2010-05-25 : 04:49:55
|
@Gail Shawquote:
Originally posted by mahajanakhil1985
If so is the case, then this space should become unallocated and should be shown by sp_spaceused procedure as unallocated. If so, then it should be shrunk by DBCC SHRINKFILE procedure.
Above is actually my post. Is it true? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-05-25 : 05:04:28
|
| Pretty much, yes. Why are you so focused on shrinking? You shouldn't be shrinking a database in general--Gail ShawSQL Server MVP |
|
|
|
Next Page
|
autoshrink option in SQL Server 2005