March 25, 2008 at 10:04 am
Hello,
will u plz tell me how can i shrink ldf files , its is in my L; drive and there is no more space so i need t5o shrink it .
plz tell me
i am using sql 2000 .
thaxxx
regards
jagpal singh
March 25, 2008 at 10:21 am
first take a full backup.
after the full backup do an transaction log backup
then you should be able to shrink the file with
dbcc shrinkfile('logfile' , destination size)
if that dosen't work try to execute the code.
backup log 'database' with truncate_only
kgunnarsson
Mcitp Database Developer.
March 26, 2008 at 1:07 am
What recovery mode is your database in? If full, do you have regular log backups running?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 26, 2008 at 3:47 am
Well. im using about 150 databases , so that varies. 🙂
In cases where Transaction log size has been come an issue. We have scheduled an transaction log backup every 4 hours. with a full backup once per day.
That should help keeping your log small. NOTE! if you are doing massive imports to the database from external sources. You should consider moving to Bulked-logged recovery model.
kgunnarsson
Mcitp Database Developer.
March 26, 2008 at 4:21 am
kgunnarsson (3/25/2008)
backup log 'database' with truncate_only
Just bear in mind that a log truncation breaks the log chain. You will not be able to restore the database to a time after the log truncation unless you take a full or diff database backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 26, 2008 at 4:35 am
but if you do a full backup before the truncation
I was under the asumption that this could be avoided by doing full backup and transaction log backup before the truncate.
is that wrong?
kgunnarsson
Mcitp Database Developer.
March 26, 2008 at 4:36 am
Sorry , the previous quote was in regards to breaking the log chain
kgunnarsson
Mcitp Database Developer.
March 26, 2008 at 4:43 am
kgunnarsson (3/26/2008)
but if you do a full backup before the truncation
I was under the asumption that this could be avoided by doing full backup and transaction log backup before the truncate.
is that wrong?
Absolutely.
When you truncate a log, you are throwing away log records that have not been backed up. When SQL does log restores, there must not be any missing log records. If there are (caused by a log truncation, a switch to simple recovery mode or a missing log backup file), log restores cannot be done after the missing records.
A full or diff backup will create a new base for future log backups.
Backup log with truncate_only is not a recommended operation (and, from what I recall, won't work in SQL 2008), but if you do one for any reason, you need to do a database backup right after or risk not being able to recover the dataabse past the point where you truncated the log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 26, 2008 at 5:22 am
hi,
can we shring log files with ssms as well
thaxx
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply