Shrikfile question
-
I ran shrinkfile against my database log file. The file had expanded to 20 gb, but was only using 50 mb at the time. I ran the command against a SQL2000 DB using the GUI interface in enterprise manager. I checked the box to move pages to the beginning of the file, and I chose to allow 10% free space after the operation completed. The operation appeared to run fine, but when I look at the size of the log file it is still 17 gb (50 mb shows as used)
Any thoughts on why this file did not shrink properly, and how to shrink the file successfully?
-
Most of the times GUI will not work as it suppose to be...
Use DBCC SHRINKFILE command...and before running the shrinkfile command run the BACKUP LOG command if the recovery model is full/bulk_logged...
MohammedU
Microsoft SQL Server MVP -
A log file can only be shrunk to a virtual log file boundary. Take a look books online "Shrinking the Transaction Log". Hope this helps.
-
Unless this is a production system the easiest method is to switch to simple recovery, checkpoint, shrink the file, I'd usually use the dbcc shrinkfile as thus dbcc shrinkfile(fileno) this is the most aggressive, use dbcc shrinkfile(fileno,size in mb ) for a slightly more controlled.
For a database in full recovery quite often you cannot get a full shrink - if it's only going to grow again it's pontless shrinking it - better to find what caused the large growth and try to control it.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
As a drastic measure, to shrink a log file for a database in full recovery mode when there is not enough room to backup the log file:
Backup Log DatabaseName With No_Log
DBCC ShrinkFile('FileName', 0)
In SQL 2005, you will get a warning that With No_Log is being deprecated and shoudln't be used any more. Setting a target size of 0 MB will shrink the log to the smallest allowable size for the file.
If you are going to do this on a regular basis, I suggest backing up the log for real (without With No_Log) and then shrinking the file to a workable size, not to its minimum size.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Thanks for your reply. This article in books online was very helpful. However it leads me to expand my question. I would first like to explain my situation a little more clearly.
The database that I work with is set to Simple recovery method, so the logical portion of the log file is not using very much space at all. The database is not in recovery mode. It simply has a very large log file. The problem that I see is that this log file was originally configured as a multi gb physical file.
does this mean that my virtual log file is as large as the original size I created the log file with, and that I cannot shrink the log file to a size that is smaller?
-
When you have multiple log files then you have shrink them individually...and virtual log file is not as large as the original log file size...
SQL Server 2005 Books OnlineTransaction Log Physical Architecturehttp://msdn2.microsoft.com/en-us/library/ms179355.aspx
http://support.microsoft.com/kb/873235
http://msdn2.microsoft.com/en-us/library/ms189085.aspx
MohammedU
Microsoft SQL Server MVP -
I thank everyone for thier responses to my query, but I am still unclear on one aspect.
It is my understanding that DBCC shrinkfile can only shrik a file to a size that is larger than the virtual log. My question is, what determines the size of each virtual log, and can the size of your virtual log be manipulated after it is created
I would especially like to to change the size of virtual log 1 as I think I have removed all other virtual logs by using the following method.
First I checkpointed the log file. Next I Shrank the DB Log and moved all of the pages to the beginning of the file. Next I deleted a second physical log file that was no longer being used.
-
I think MS follows some algorithm to determine the virtual log size based on the tlog size...and you can't change the size of it..
Read the following articles for details...
http://msdn2.microsoft.com/en-us/library/aa933049(SQL.80).aspx
http://support.microsoft.com/kb/907511
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
MohammedU
Microsoft SQL Server MVP -
Hi Colin,
I am not sure what you means the "Unless this is a production system...".
Base on my past experience, when the database is in the full recovery mode, the dbcc shrinkfile does not really working. Everytime when I do the shrink job I have to set database as simple recovery mode, dbcc shrinkfile, then set database back to full recovery mode again.
Do you have any better way, if I have to do the dbcc shrink?
Thanks in advance.
-
Hi
,Points to note
================
1.To shrink a log smaller than its original size you must shrink individual files with DBCC SHRINKFILE. You cannot use DBCC SHRINKDATABASE to shrink a log to a size smaller than its original or explicitly defined size. The original size is defined as the size of the log due to CREATE DATABASE plus any explicit ALTER DATABASE commands. The original size does not include automatic growth of the log.
2. The physical log file can never be smaller than the amount of space currently used within the log file.
You can use the DBCC SQLPERF (LOGSPACE) command to monitor the amount of space used.
3. Because a log can be shrunk only to a virtual log file (VLF) boundary, it is not possible to shrink a log file to a size smaller than a VLF even if the space is not being used
Shrinking of the transaction log:======================================
1. Make sure there are no open transactions
DBCC OPENTRAN(databasename)
2. You must run a BACKUP LOG statement to free up space by removing the inactive portion of the log or if you want to truncate the
transaction log only then run the below command
>> BACKUP LOG pubs WITH TRUNCATE_ONLY option.
Shrink the Tlog
>> DBCC SHRINKFILE(Logfilename, Target size in MB)
After the log shrink take a full database of the Master as well as User database.
One of the reasons that the SQL Server transaction log is not being truncated
---------------------------------------------------------------------------
An unbounded update fills up the log; that is, there is no WHERE clause on the UPDATE statement. Since a transaction (either user declared or implicit) must be able to be entirely rolled back or committed as a whole, the log must be large enough to maintain all information for the transaction. Thus, even when truncating the log, it is possible to fill the log if it is too small for a single large transaction. This situation can also occur if a WHERE clause is used, the table is very large, and the WHERE clause is too general, causing a large number of records to be affected. There is another case where this could happen: if multiple updates are performed that together affect enough records to fill the log before the checkpoint is done, the log can still fill up.
Prevent the transaction log files from growing unexpectedly
------------------------------------------------------------
To prevent the transaction log files from growing unexpectedly, consider using one of the
following methods:
• Set the size of the transaction log files to a large value to avoid the automatic expansion of the transaction log files.
• Configure the automatic expansion of transaction log files by using memory units instead of a percentage after you thoroughly evaluate the optimum memory size.
http://support.microsoft.com/kb/315512/
.You can change the recovery model from full to simple if you do not want to use the
transaction log files during a disaster recovery operation.
• Back up the transaction log files regularly to delete the inactive transactions in your transaction log.
• Design the transactions to be small.
• Make sure that no uncommitted transactions continue to run for an indefinite time.
• Schedule the Update Statistics option to occur daily.
• To defragment the indexes to benefit the workload performance in your production environment, use the DBCC INDEXDEFRAG Transact-SQL statement instead of the DBCC DBREINDEX Transact-SQL statement. If you run the DBCC DBREINDEX statement, the transaction log may expand significantly when your SQL Server database is in Full recovery mode. Additionally, the DBCC INDEXDEGRAG statement does not hold the locks for a long time, unlike the DBCC DBREINDEX statement.
Please check the following links for more information:---------------------------------------------------------
http://support.microsoft.com/kb/324432/en-us
http://support.microsoft.com/kb/317375/en-us
http://support.microsoft.com/kb/873235/en-us
Regards,
Minaz Amin
"More Green More Oxygen !! Plant a tree today"
-
TYMA .. I'd normally assume that a production database is set to full recovery with regular transaction log backups .. if you switch the database to simple recovery you break the point of recovery and must do a full or differential backup after you change back to full recovery otherwise you won't be able to apply any transaction log backups you make after this point.
If you don't want the transaction log backups then just set the database to simple recovery, much easier to shrink the size down, the log will clear after a checkpoint.
So either use full recovery with frequent tlog backups or put the database into simple recovery.
I sympathise if you're having problems reclaiming device space, and guys it's all very well posting lots of links and pasting from other articles but real world experience says that this often doesn't work and despite your best efforts you just can't shrink a device !! Your only way out then without switching to simple recovery is to stop and start the sql service, this will usually work.
dbcc shrinkfile(2) for example will attempt to shrink file number 2 back to the original size it was created at. ( use sp_helpfile to get the file nos )
To get rid of a multiple file tlog - detach the database, move your ldf files, re-attach and create a new ldf file. Make sure you backup before trying any of this.
As I mentioned before, it's good policy to find out what grows your tlog and see if you can control that process, shrinking files, especially mdf/ndf, is actually not a good idea and although the tlog shrinks don't have the same impact to the database you'll most likely get severe ntfs fragmentation which is not good.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Hi,
It is good to create a maintenance plan to backup the log in a particular interval depending on the workload.Minaz Amin"More Green More Oxygen !! Plant a tree today"
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply