March 24, 2007 at 6:54 am
My transactional log is too big . There is no enough space to perform a backup. When I ran Dbcc shrinkfile -- size doesn't change. Please respond
March 24, 2007 at 11:58 am
Unless there is space in the log file to shrink to, dbcc shrinkfile will not function as it will not remove the active portion of the log.
Are there any old log backups that you can delete to make space for the new log backup? If so, delete them or if you want to keep them, move the old backups to a temporary drive.
Do you have another drive which is big enough to back up to? If so, run a manual backup of the log file to that drive.
You can backup the log with the truncate only option which will truncate the log. This also means that if your database then failed, you would not be able to restore to a point in time. I would suggest if you used this process, run a full database backup after.
Also, if the log is too big, can I ask, do you backup the log file? If you do, it may be worth considering the frequency to make sure you have smaller backups. With more backups, you may also want to consider the retention period.
March 25, 2007 at 8:19 am
just run this query
DBCC SQLPERF(logspace). This will return the size of the log file for the database and the percentage space effectively used. once u find that you can run backup log with truncate_only over the database and then shrink the file this will work great just check and let me know.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
March 25, 2007 at 2:46 pm
backup transaction mydb with no_log
will clear space as well. Then you can run the backup.
Be sure to setup log backups after this along with data backups.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply