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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 DBCC SHRINKFILE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dips255
Starting Member

17 Posts

Posted - 04/30/2013 :  05:50:32  Show Profile  Reply with Quote
DBCC SQLPERF(LOGSPACE)
ALTER DATABASE [MyDB] SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE (MyDB_Log, 1);
GO
ALTER DATABASE [MyDB] SET RECOVERY FULL;
GO

I use this to shrink db log which helps me avoid the query time out errors on my web application. However I am not sure this is the right solution. I have also come to many posts saying that Shrinkfile is not a good solution. Is it so? Also what should be the optimum value of shrink size

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/30/2013 :  06:07:28  Show Profile  Reply with Quote
see

http://www.mssqltips.com/sqlservertip/2055/issues-with-running-dbcc-shrinkfile-on-your-sql-server-data-files/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3704 Posts

Posted - 04/30/2013 :  08:41:21  Show Profile  Reply with Quote
You can shrink your log file if you want to/need to, but as a general practice, that is not a good idea. What you should do is the following:

1. Observe the size of your log file over a period of several days that covers the typical workload.


2. When the log file grows to some size beyond which you don't want it to grow, take a log backup. That will/should free up the virtual log files. That will not reduce the size of the log file on the OS, but there will be free space within the log file. Now the log file should not grow for a while. You can see the file size and how much of it is used using this query:
SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name IN ( 'Percent Log Used','Log File(s) Size (KB)')
AND instance_name = 'YourDatabaseNameHere'


3. As the log file grows and gets to near where it is taking up 80 or 90 percent of the space, take a log back up again. When you do this a few times, you will get an idea of how frequently you need to take log backups. Schedule a job to do log backups at a little higher frequency than that.

Sometimes the log file will grow beyond what you want it to be - for example, when you rebuild indexes on large tables, or when you have long running queries. (The former can be alleviated by switching to bulk logged mode for the duration of the index rebuild, but you have to be careful doing so for other reasons).

In any case, if you find that there is log file size is larger than it needs to be (i.e., with regular log backups, there is lot of free space in the log file always), then you can shrink the log file.

If you can help it, don't let the log file autogrow. The reason is that when the log file grows, all activity on the database has to stop. New VLFs created have to be zeroed out, so it can pause the database for the duration of that initialization. However, you should always allow the log file to autogrow - if it needs to grow and can't, the database will come to a screeching halt.

Everything I said above is in reference to log files. As for datafiles, you should never shrink them. (Well, I should never say never, but you get the idea).
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
337 Posts

Posted - 04/30/2013 :  10:27:53  Show Profile  Reply with Quote
James, thank you for putting the various MS descriptions into English I can understand. I hope dips255 has also been enlightened.

djj
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3704 Posts

Posted - 04/30/2013 :  11:55:06  Show Profile  Reply with Quote
You are very welcome djj55. Glad to help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 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.08 seconds. Powered By: Snitz Forums 2000