November 5, 2012 at 4:39 am
Dear,
1. I like to auto shrink my database. But I dont know how to do it.
2. My current DB size is 2 GB. I want that if my database increases by 20%, it will shrink the database.
If you have better suggestion than my scenario, suggest me.
Please help me. Thank you for your time.
Regards,
Akbar
November 5, 2012 at 4:41 am
Better scenario is do not shrink the database at all, it causes a whole heap of potential problems and fixing them will only make the database bigger again, so you in a catch 22 situation.
November 5, 2012 at 5:04 am
please suggest me, what can I do. Which approach should I practice?
November 5, 2012 at 5:08 am
What is your reasoning behind wanting to shrink the database?
The approach I would take it to leave it as is and do not shrink unless there is a valid business reason.
November 5, 2012 at 8:15 am
Don't regularly shrink your database. This is not MS Access that required a compact and shrink often. shrink is not compression. Free space in a SQL database is not harmful.
A data growing means that you're adding data to it, shrinking in that case is not going to reduce space used and is just going to slow things down.
Do not autoshrink. Do not shrink regularly.
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
November 5, 2012 at 8:30 am
shohelr2003 (11/5/2012)
Dear,1. I like to auto shrink my database. But I dont know how to do it.
2. My current DB size is 2 GB. I want that if my database increases by 20%, it will shrink the database.
If you have better suggestion than my scenario, suggest me.
Please help me. Thank you for your time.
Regards,
Akbar
Hi,
2GB or 2TB. If 2GB then I'll send you a flash memory of 32GB and your problem is resolved :), I'm kidding, I suppose your database has grown up to 2TB.
So a data compression approach can help you here. If your system is OLAP then it will help you much on reducing the storage space if you simply apply a PAGE-type of compression for all tables. If you have OLTP system, then do the data compression carefully. The following link can help you a lot: http://msdn.microsoft.com/en-us/library/dd894051(v=sql.100).aspx
With OLTP systems combining PAGE and ROW compression for tables can be more useful rather than applying only one type.
If you have big tables, then make a deeper analysis: see the indexes scans and updates and then
decide how to compress them, and do it manually if you have big tables. Data compression affects the index rebuild.
Also consider some other aspects from the paper...
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
November 5, 2012 at 9:07 pm
Thank you for your kind help.
Actually I need to shrink my log file. As my database increases regularly, it slows down my application system.
Pls suggest me.
November 5, 2012 at 9:08 pm
thank you for your kind information.
November 5, 2012 at 10:05 pm
shohelr2003 (11/5/2012)
Actually I need to shrink my log file. As my database increases regularly, it slows down my application system.
A large transaction log will not cause performance problems. Free space in the data file will not cause performance problems.
I suggest you investigate the actual cause of the performance problems and leave the log alone.
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
November 6, 2012 at 12:05 am
GilaMonster (11/5/2012)
shohelr2003 (11/5/2012)
Actually I need to shrink my log file. As my database increases regularly, it slows down my application system.A large transaction log will not cause performance problems. Free space in the data file will not cause performance problems.
I suggest you investigate the actual cause of the performance problems and leave the log alone.
thank you for your suggestion.
November 6, 2012 at 9:19 am
shohelr2003 (11/5/2012)
Thank you for your kind help.Actually I need to shrink my log file. As my database increases regularly, it slows down my application system.
Pls suggest me.
Perhaps your tlog is growing because your database is in full recovery mode and you have never backed up the tlog? This is a VERY common occurrence. Doing a full backup does NOT flush out committed transactions from the tlog.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply