November 16, 2015 at 12:13 pm
Hello comunity
I read that is not best practice to Shrink database due to index defragmentation,
but my DATA file occupies in disk 39 GB when in fact the size is 19GB of space, so unused space are more and less 20GB.
They were reconstructed all indexes on all tables of the database as well as the defragmented all tables.
What is the best solution to the problem since users complain of slowness.
I hope someone could give me the best solution or script to avoid this problem, because in last instance i will go to Shrink the database.
Of course after shrinking the database, i will go to rebuild all indexes on all tables.
Many thanks,
Luis Santos
November 16, 2015 at 12:31 pm
luissantos (11/16/2015)
Hello comunityI read that is not best practice to Shrink database due to index defragmentation,
but my DATA file occupies in disk 39 GB when in fact the size is 19GB of space, so unused space are more and less 20GB.
They were reconstructed all indexes on all tables of the database as well as the defragmented all tables.
What is the best solution to the problem since users complain of slowness.
I hope someone could give me the best solution or script to avoid this problem, because in last instance i will go to Shrink the database.
Of course after shrinking the database, i will go to rebuild all indexes on all tables.
Many thanks,
Luis Santos
Shrinking your database in a situation like that isn't bad. What is bad is shrinking your database and not fixing the fragmentation that occurs after the shrink is completed. If this a one time thing it shouldn't be a big deal. If however, you are going to insert a ton of data again so the datafiles grow this much it isn't going to help.
You seem to have an issue with performance also. This may be due to shrinking the database or it could be a number of other reasons.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 16, 2015 at 12:42 pm
luissantos (11/16/2015)
What is the best solution to the problem since users complain of slowness.
Why do you believe that slowness is related to the size of the data file and the unused space?
November 16, 2015 at 12:56 pm
Hello Sean
Thanks for your reply, also what you recomend to shrink, SSMS, or do you know some good script to execute This operation ?
In fact, i always rebuild all indexes on each tables of my database to avoid the kind of problems you have mencioned.
If you know trust scripts to execute these kind of operation, could you post the links ?
Best regards
Luís Santos
November 16, 2015 at 12:59 pm
The best solution is to tune the existing indexes, starting with verifying that you have the best clustered index on every table. Btw, empty/unused space within the db will not cause slowness. Only fragmentation within the indexes themselves could cause that, but you're rebuilt them, so that's not the problem in your case.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 16, 2015 at 1:17 pm
Hello Luis
I perform This operation one time for this reason and after sending data to historic database when i keep olders years.
This database is exactly like the same that the current production database, the only diference is to keep older records.
All the hardware, Server, structure of network are the same, and in fact users tell me that the application work much more faster.
It' is why i presume that the problem is the same.
Also, keeping in disk a data file with the double size that they don't have even the indexes are rebuild and the tables are rebuild, is not good for sql server.
Maybe i am wrong but i thanks not !
Best regards
Luís santos
November 16, 2015 at 1:28 pm
Empty space in data files won't hurt performance, unless the disk is full and that prevents OS operations. Growing a file is expensive, so you must plan to leave free space in your files to prevent growth. Some operations are very disk intensive and might require a lot of space which be freed after the process, but still allocated.
So, unless you're experiencing severe low space on disk issues, I wouldn't bet on the file size being a problem.
November 16, 2015 at 1:36 pm
luissantos (11/16/2015)
Also, keeping in disk a data file with the double size that they don't have even the indexes are rebuild and the tables are rebuild, is not good for sql server.
Err, why not? What's that free space going to do?
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply