September 23, 2003 at 4:40 pm
Hi,
I'm trying to shrink the log file from the existing size(171MB) to the amount used(0.2MB. Found this by right clicking on the DB->Shrink DB->Files->.LDF File->Space used) by it, but it still stays on it's existing size (171MB).
Appreciate any help in advance.
Thanks
September 23, 2003 at 4:59 pm
Try Refreshing that view by selecting refresh after right clicking.
Or you can try the following from QA: This will shirnk it to 10 MB.
BACKUP LOG DBNAME WITH TRUNCATE_ONLY
go
DBCC SHRINKFILE (DBNAME_Log, 10)
go
BACKUP LOG DBNAME WITH TRUNCATE_ONLY
go
.
September 23, 2003 at 5:54 pm
http://www.sqlservercentral.com/scripts/contributions/26.asp
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
September 23, 2003 at 6:52 pm
Unless you override by using DBCC SHRINKFILE with the target size option, you cannot reduce the file size below its originally configured size. (The default size for the log file is 25% of the data file size or 512KB, whichever is larger.)
It's a good idea to leave both the data and log files at their maximum natural sizes, rather than shrinking them and then letting them autogrow. This will give you better performance because autogrowth takes processor cycles and can fragment the files. Leave autogrowth enabled for the files, but set an alert if it occurs, because you should explore the cause if it happens when your files are sized correctly. Stop the SQL services and defrag the volumes after autogrowth, even if you subsequently shrink the files.
The correct size for the log file is the size it needs after the heaviest processing period between scheduled tran log backups. This is the size it will autogrow to naturally, but you could see atypical growth if you perform an atypical mass update. If you are performing database maintenance tasks involving mass updates or deletes (or bcp), it's a good idea to first backup the database, then the log file (which will truncate the log), and then change your recovery model to Simple (i.e. truncate log on checkpoint). After the maintenance, change the recovery model back to Full and perform backups of both data and log. This technique should keep your log file from growing enough to require shrinking. The performance you will lose by truncating on checkpoint should be offset by the performance you gain by avoiding autogrowth. Be aware, though, that deleting or updating a large number of rows from one table will be a single transaction if you don't take extraordinary steps to prevent this, and so may cause growth of the log file because a checkpoint cannot be performed until after the implicit transaction completes.
--Jonathan
--Jonathan
September 23, 2003 at 7:00 pm
quote:
http://www.sqlservercentral.com/scripts/contributions/26.aspSteve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
Wrapping VLFs like this is unnecessary with SQL Server 2000, of course. If using previous versions, this will allow immediate gratification. 😉
--Jonathan
--Jonathan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply