Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dips255
Starting Member
17 Posts |
 Posted - 2013-04-30 : 05:50:32
|
| DBCC SQLPERF(LOGSPACE)ALTER DATABASE [MyDB] SET RECOVERY SIMPLE;GODBCC SHRINKFILE (MyDB_Log, 1);GOALTER DATABASE [MyDB] SET RECOVERY FULL;GOI 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
52326 Posts |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-30 : 08:41:21
|
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_countersWHERE 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). |
 |
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-04-30 : 10:27:53
|
| James, thank you for putting the various MS descriptions into English I can understand. I hope dips255 has also been enlightened.djj |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-30 : 11:55:06
|
| You are very welcome djj55. Glad to help. |
|
|
|
|
|
|
|
|
DBCC SHRINKFILE