The auto-extend vaule by default on MS SQL server 2008 R2 SP2

Dear all,

we all know that by default, the auto-shrink feature of all user database in MS SQL server is disabled and what I knew so far is, no matter what this value is, when we delete/archive data out from user database, the .mdf file size will be smaller no matter this feature on or off, am I right?

how is your option on this as previous experience this, .mdf file size will be smaller whenever large amount of data has been deleted.

ALSO, we do not shrink datafile as it can create >90% of index fragmentation, right? only log can be shrink by release the tail of inactive transaction log, right?

please help to clarify that and how can we make sure that auto-shrink option is really on ? e.g., once enable this option to yes from per user database option, should we do a full backup for it to take effect?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
when we delete/archive data out from user database, the .mdf file size will be smaller no matter this feature on or off, am I right?
No. The data inside the file will be reduced but the file size will stay the same. The only way to reduce a data file is to shrink it. Deleting data won't reduce the file size.

once enable this option to yes from per user database option, should we do a full backup for it to take effect?
No need to do nothing after enabling the autoshrink. I'm just wondering why do you want to do that. Side effects are so much and there's no advantage at all to turn that option on.

NOTE: During the autoshrink the database may and will cause locks. Performance degradation will be very high. This operation should be made manually and only as emergency and temporary solution. I highly recommend you to do not turn autoshrink on for any database.
marrowyungSenior Technical architecture (Data)Author Commented:
hi Victor,

tks for the quick update.

"No. The data inside the file will be reduced but the file size will stay the same."

what I tried before is once data deleted, it will release the free space to the OS really quickly.

"The only way to reduce a data file is to shrink it. "

we have experience on that and similiar concept also said that we can't shrink the data file as there will be >90% of index fragmentation, is that still correct?

usually we only do shrink the log by release free space at the tail of the log, we don't actually shrink it by rearrange data.

"No need to do nothing after enabling the autoshrink. I'm just wondering why do you want to do that. Side effects are so much and there's no advantage at all to turn that option on."

it is our vendor challenge us that this option is turn off by default and it is us who don't turn that on !  all data archive retention setting reside within application logic, so we can only archive data from within application.

so this means  to us that if we don't turn this option on, then this is our problem.

"NOTE: During the autoshrink the database may and will cause locks. Performance degradation will be very high. This operation should be made manually and only as emergency and temporary solution. I highly recommend you to do not turn autoshrink on for any database."

where you get this from ?
marrowyungSenior Technical architecture (Data)Author Commented:
so you mean manual shrink it by using : "Reorganize pages before releasing unused space" option ?

I just tried to shrink by release unused space only, but it can't shrinked by that.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Vitor MontalvãoMSSQL Senior EngineerCommented:
we can't shrink the data file as there will be >90% of index fragmentation, is that still correct?
I can't confirm the >90% fragmentation but yes, it will be highly fragmented.

it is our vendor challenge us that this option is turn off by default and it is us who don't turn that on !
Yes, and you're taking a wise decision. It's also the recommendation from Microsoft. As your vendor why they want to go against Microsoft recommendation?

so this means  to us that if we don't turn this option on, then this is our problem.
It will be only your problem if you turn it on. Letting it off will avoid problems. Also if you have the data file in a dedicated drive there's no need to shrink the data file. It's also a recommendation to avoid grows since that can cause some minor performance issues (the engine need to wait for the file grows to continue a process).

where you get this from ?
From my own experience. When I was a rookie DBA :)
marrowyungSenior Technical architecture (Data)Author Commented:
tks for your fast response, having your as my teacher is very good .

"I can't confirm the >90% fragmentation but yes, it will be highly fragmented."

there are script on the web to check index fragementation, I verify that before and yes, around 98% and that's why everyone on the web don't do this.

"Yes, and you're taking a wise decision. It's also the recommendation from Microsoft. As your vendor why they want to go against Microsoft recommendation?"

This mean MIcrosoft also recommend turn it off and that's why it is the default? so they are wrong

"Also if you have the data file in a dedicated drive there's no need to shrink the data file."

I don't understand the relationship between dedicated drive and shrink the data file, no relationship, right?

tks rookie DBA ! ahhaha


so you still telling me I need to shrink the DATA  file manually? by reorganise it  and give a target size?
Vitor MontalvãoMSSQL Senior EngineerCommented:
This mean MIcrosoft also recommend turn it off and that's why it is the default? so they are wrong
Bulls eye :)

don't understand the relationship between dedicated drive and shrink the data file, no relationship, right?
What I want to tell is that if the drive is dedicated for that database only it won't affect others so there's no need to shrink to save space. After all the drive is dedicated to the database, right?

so you still telling me I need to shrink the DATA  file manually? by reorganise it  and give a target size?
No. I'm telling you to don't shrink at all but if someday you need to do this operation then do it manually so you can control it better. And after a data file shrink always run a REORG for immediately defragmentation.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Please give a look in this Microsoft's article about autogrow and autoshrink.
Vitor MontalvãoMSSQL Senior EngineerCommented:
And also this article from Paul Randall. He's just the best SQL Server Expert in the world. He even teaches SQL Server professionals from Microsoft.
marrowyungSenior Technical architecture (Data)Author Commented:
victor, what is your time zone, you reply so quick?

bull eyes, don't understand that ..... AHHAH

"What I want to tell is that if the drive is dedicated for that database only it won't affect others so there's no need to shrink to save space. After all the drive is dedicated to the database, right?"

for me it has no relationship as if it is near full, then no matter if it is dedicated or not, it still full.

all database .mdf file store on that, same, drive.

"No. I'm telling you to don't shrink at all but if someday you need to do this operation then do it manually so you can control it better. And after a data file shrink always run a REORG for immediately defragmentation."

so you mean shrink by using reorganize data method, right? or something else ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
victor, what is your time zone, you reply so quick?
Central Europe (GMT+1). And I receive notifications when a question has an update :)

bull eyes, don't understand that ..... AHHAH
Means you really hit in the precise point. 100% correct.

all database .mdf file store on that, same, drive.
Ok. That solution will make you to have extra management attention because will affect the others databases as well.

so you mean shrink by using reorganize data method, right? or something else ?
There is no "shrink by using reorganize data method". There are two operations:
1st - Shrink;
2nd - Reorganize
marrowyungSenior Technical architecture (Data)Author Commented:
"Now, don’t confuse shrinking the transaction log with shrinking data files. Shrinking the log is necessary if your log has grown out of control, or as part of a process to remove excessive VLF fragmentation (see Kimberly’s excellent posts on this here and here). However, shrinking the log should be a rare operation and should not be part of any regular maintenance you perform."

"Shrinking of data files should be performed even more rarely, if at all. Here’s why – data file shrink causes *massive* index fragmentation."


this i also what meant too, only log is ok to shrink ! not the data file, I am right !!

by default, the auto shrink is off!

so you mean deleting data still can't smaller the size of data file but also need a shrink ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
this i also what meant too, only log is ok to shrink ! not the data file, I am right !!
 by default, the auto shrink is off!
Yes, you're right.

so you mean deleting data still can't smaller the size of data file but also need a shrink ?
Deleting data will never reduce the file size.
Imagine the data file as a disk drive and the records in the data file as files in a disk drive. When you delete files the disk drive won't shrink, right? Even there's more free space in the disk drive it won't shrink. With the data files is basically the same. You delete records but the size of the file still the same. Deleting records will only free space inside of the data file.
Reorganize a database is a similar operation as defragment a disk drive. Will keep records continuous so will increase the queries performances.
marrowyungSenior Technical architecture (Data)Author Commented:
"Central Europe (GMT+1). And I receive notifications when a question has an update :)"

so you don't sleep ?

"Means you really hit in the precise point. 100% correct."

80-90% of time I will ! if I make it 100% correct I will be bored.

"Ok. That solution will make you to have extra management attention because will affect the others databases as well."

but adding an additional drive for each user database .mdf file also create another overhead for SAN and System administrator, right? I hope not for me.

'There is no "shrink by using reorganize data method". There are two operations:
1st - Shrink;
2nd - Reorganize"

I mean this :

option on shrinking file
Vitor MontalvãoMSSQL Senior EngineerCommented:
but adding an additional drive for each user database .mdf file also create another overhead for SAN and System administrator, right? I hope not for me.
If it's in the SAN then there will be no overhead at all. It's more an organization thing that won't let a database to affect the others. In the worst case only that database will be affected if no free space happens.

"shrink by using reorganize data method".
Good that you post the screen shot. Since I usually don't use SSMS for admin tasks (I prefer write the command in the query window) I didn't understand what you pretend. That option is basically to tell SQL Server to shrink the file but let some free space. The free space will be the value that you'll fill in that field.

By the way, why the data file reached that much size?
marrowyungSenior Technical architecture (Data)Author Commented:
marrowyungSenior Technical architecture (Data)Author Commented:
"If it's in the SAN then there will be no overhead at all. It's more an organization thing that won't let a database to affect the others. In the worst case only that database will be affected if no free space happens.
"

yeah, I understand what you mean but it will be very hard to do as what if no. of user database keep growing ?

it is good to separate production, staging and test on different server, someone even install web server on the SQL server box as they don't have more budget.

"

 By the way, why the data file reached that much size? "

once the application upgraded, the SQL data world changed and even tempdb usage 8 times bigger.
Vitor MontalvãoMSSQL Senior EngineerCommented:
it is good to separate production, staging and test on different server, someone even install web server on the SQL server box as they don't have more budget.
Yes, everything depends on budget. We can't make omelets without eggs.

once the application upgraded, the SQL data world changed and even tempdb usage 8 times bigger.
Ok, you may have here a condition to shrink the database. You may add a task for shrink and reorganize the database every time the application is upgraded. But this should me take as exception and not as regular task.
marrowyungSenior Technical architecture (Data)Author Commented:
"Ok, you may have here a condition to shrink the database. You may add a task for shrink and reorganize the database every time the application is upgraded. But this should me take as exception and not as regular task."

I have receive more information now depends on the live and no live data parameter inside the application and we are waiting for someone to ADJUST that value and more live data will become non-live data.

"Ok, you may have here a condition to shrink the database. You may add a task for shrink and reorganize the database every time the application is upgraded"

then this is another problem. we should let the vendor check their abnormal usage first and then make sure that this is really a good action I think.
marrowyungSenior Technical architecture (Data)Author Commented:
what is the best shrink command you use in terms of speed ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
There's only one shrink command:
DBCC SHRINKFILE (LogicalFileName, NewSize);

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
we have set the new archive data figure for the application, let see if the archive in this way really help.

so we have smaller live data period need to store in local hard disk.

will come back and let you know.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.