SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 autoshrink option in SQL Server 2005
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

mahajanakhil1985
Yak Posting Veteran

India
71 Posts

Posted - 05/19/2010 :  09:52:55  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 05/19/2010 :  11:13:21  Show Profile  Reply with Quote
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 ...)

Edited by - Kristen on 05/19/2010 12:13:37
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 05/19/2010 :  12:50:41  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx
http://www.sqlskills.com/blogs/paul/2007/11/13/AutoshrinkTurnItOFF.aspx


--
Gail Shaw
SQL Server MVP
Go to Top of Page

mahajanakhil1985
Yak Posting Veteran

India
71 Posts

Posted - 05/19/2010 :  13:35:45  Show Profile  Reply with Quote
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?
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 05/19/2010 :  17:18:49  Show Profile  Reply with Quote
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"
Go to Top of Page

mahajanakhil1985
Yak Posting Veteran

India
71 Posts

Posted - 05/20/2010 :  07:57:50  Show Profile  Reply with Quote
@kirsten

Thanks!
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.
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 05/20/2010 :  10:00:55  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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 time

quote:
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 Shaw
SQL Server MVP
Go to Top of Page

mahajanakhil1985
Yak Posting Veteran

India
71 Posts

Posted - 05/20/2010 :  12:36:26  Show Profile  Reply with Quote
Thanks all!!!
Go to Top of Page

mahajanakhil1985
Yak Posting Veteran

India
71 Posts

Posted - 05/24/2010 :  11:10:05  Show Profile  Reply with Quote
@GilaMonster

I 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.
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 05/24/2010 :  12:12:49  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Yes, it tells about unallocated free space, and that's what shrink file will release if you use it.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

mahajanakhil1985
Yak Posting Veteran

India
71 Posts

Posted - 05/24/2010 :  13:02:39  Show Profile  Reply with Quote
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?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 05/24/2010 :  13:04:58  Show Profile  Visit tkizer's Homepage  Reply with Quote
In a scenario like that, you would rebuild the clustered index. Calculating how much space it would free would be dependent upon the size of the rows and number of them.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

mahajanakhil1985
Yak Posting Veteran

India
71 Posts

Posted - 05/24/2010 :  13:18:58  Show Profile  Reply with Quote
@tkizer

Could 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?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 05/24/2010 :  13:25:07  Show Profile  Visit tkizer's Homepage  Reply with Quote
I think so, but I'll let someone else better answer that such as Gail.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

mahajanakhil1985
Yak Posting Veteran

India
71 Posts

Posted - 05/24/2010 :  13:30:53  Show Profile  Reply with Quote
@tkizer

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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 05/24/2010 :  13:46:44  Show Profile  Visit tkizer's Homepage  Reply with Quote
But did you rebuild the clustered index?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

mahajanakhil1985
Yak Posting Veteran

India
71 Posts

Posted - 05/24/2010 :  14:33:02  Show Profile  Reply with Quote
I don't have such a large clustered index to test its effects.
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 05/24/2010 :  15:36:35  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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 Shaw
SQL Server MVP
Go to Top of Page

zstarsales04
Starting Member

20 Posts

Posted - 05/25/2010 :  02:32:39  Show Profile  Visit zstarsales04's Homepage  Reply with Quote
spam removed
Go to Top of Page

mahajanakhil1985
Yak Posting Veteran

India
71 Posts

Posted - 05/25/2010 :  04:49:55  Show Profile  Reply with Quote
@Gail Shaw
quote:
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?
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 05/25/2010 :  05:04:28  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Pretty much, yes.

Why are you so focused on shrinking? You shouldn't be shrinking a database in general

--
Gail Shaw
SQL Server MVP
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000