December 24, 2007 at 2:21 am
Hi Guys
I am currently having a problem here with the shrinking of a log file. Currently the database has a few file groups where some data and log files reside.
I am trying to free up some space on a particular drive. It shows on the "Shrink file" window in the GUI that the current size of the log is 15 GB. The used space is about 13 MB. When I shrink the file nothing happens. I ran dbcc updateusage before the shrink but still the current size of the file remains unchanged.
Can anyone please help me with this?
Thanks in advance
IC
December 24, 2007 at 6:17 am
The log-file can only shrink until the last log-chunck in use.
You'll force it to shrink manualy.
http://support.microsoft.com/kb/q256650/ is for sql7, but also works on sql2000
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 27, 2007 at 8:17 am
r u using shrinkfile or shrinkdatabase? only shrinkfile will be able to shrink it to less than its original create size.
use dbcc loginfo(dbname) to check if the last chunk is being used. If it is it will have a status of 2. Then use the URL ALZDBA referred to to cycle the used portion out.
---------------------------------------------------------------------
December 27, 2007 at 2:11 pm
I use this method:
--sp_helpdb to verify log size, FileID and recovery Mode of the log
sp_helpdb [database]
--If necessary, change recovery mode to Simple
Use [database]
alter database [database] set recovery simple
--Backup LOG with NO_LOG
Use [database]
BACKUP LOG [database] WITH NO_LOG
--Shrink log file
Use [database]
DBCC SHRINKFILE (2)
--If necessary, change recovery mode back to FULL
Use [database]
alter database [database] set recovery simple
--be sure to perform a FULL database backup when completed
December 28, 2007 at 7:39 am
1) A 15GB log file with a few MB used leads me to believe that you had the database set for FULL recovery mode but weren't doing tlog backups. If that is the case it is the reason your log file got so big in the first place.
2) Log files are broken up into virtual log "pieces". As stated by others, you can only shrink a file back to the last one in use. you will need to add enough data to the database to have the log file wrap back around to the beginning virtual log boundary.
3) An easier method (if you have a single data file) is to detach and reattach the database using sp_attach_single_file_db. This will create a new empty log for you. Note that the database will obviously be offline when you do this operation. See BOL and use with caution.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply