October 1, 2009 at 12:34 am
If i use dbcc shirinkfile(<file id>,truncateonly) in production hour
Would it affect on any performance..If yes tell me any other way so that i can shrink data file/logfile during prod hours.
Thnks in advance.
October 1, 2009 at 1:03 am
Doing a shrink operation on production db in working hours is not recommended. You need to find out when your db is least used and do it then.
Log files should be managed using log backups. A proper backup strategy will minimize the chances of doing a shrink.
Why the shrink operation on the data file ?
"Keep Trying"
October 1, 2009 at 1:10 am
litu deb (10/1/2009)
If i use dbcc shirinkfile(<file id>,truncateonly) in production hourWould it affect on any performance..If yes tell me any other way so that i can shrink data file/logfile during prod hours.
Thnks in advance.
It would have an effect on performance, is there any particular reason why you think you need to do a shrinkfile. and shrinking should never be done during production hours unless it is an emergency
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 1, 2009 at 1:20 am
Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.
Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/
http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx
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
October 1, 2009 at 1:55 am
As server is running out of space i have to shrink either log files/database file.I can't take log back up because of space issue.
October 1, 2009 at 2:40 am
litu deb (10/1/2009)
As server is running out of space i have to shrink either log files/database file.I can't take log back up because of space issue.
Thought the issue might be that. Even if you shrink, you will have to take into account how long before this situation happens again. how big are you databases and matching log files and the total disk space on the drive on which they are located.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 1, 2009 at 3:11 am
litu deb (10/1/2009)
As server is running out of space i have to shrink either log files/database file.I can't take log back up because of space issue.
Have you requested more space for the server? Repeated shrinking is not the answer. If you're in full recovery you HAVE to take log backups or your log is going to grow until it fills the disk and no amount of shrinking will prevent that
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
October 2, 2009 at 3:51 am
Gail is right. If you have very little space on your server you have a problem. Doing repeated shrink operations is a bit like trying to empty the ocean with a bucket.
You need to start by looking at your recovery requirements. Do you have to recover everything that happened up to the time of failure? Or is it OK to recover to (say) 6PM last night?
If you have to recover to the point of failure you MUST get more disk space, because at present what you are doing will not allow you to do the recovery you need. In order to recover to the time of failure, you need to take regular log backups and you must never truncate the log.
If it is OK to recover to a fixed time in the day, then you should change your databases to use Simple recovery mode. With simple recovery, you do not need to take transaction log backups, and the transaction logs will never grow larger than the minimum size needed for your databases.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 2, 2009 at 9:43 am
How much of the space reserved for your datafile is actually in use? If you are using SSMS you can right-click the database and the properties tab will show you the Size and Space Available. If the Space Available is small in proportion to the Size (e.g 5% or so) then Shrinking is not going to help a lot. If the Space Available is large in proportion to the Size then you may gain some time with a shrink operation.
The situation is similar for the log file but a little more complex to work around. First what Database Recovery Model set to. If it is "Simple" you have one set of options if it is "Full" you have a different set of options.
It would also help to know what SQL Version you are using since the tools are a little different for each version.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply