November 8, 2011 at 6:14 am
Hi there,
Because of mistake in a Stored Procedure (detailed logging turned on for all my customers) one table in each customer database has grown way too large. I would like to truncate the databases because my storage is almost filled up now.
Every night a full backup is made. But if I understood correctly, the logfile will not shrink it's just not getting bigger (until the current size isn't big enough any more).
What's the best way to go about it?
Thx,
Raymond
November 8, 2011 at 6:45 am
Ok, you're going to need to give us a lot more info first.
What's larger than it should be, the log file or the data file?
What recovery model are you using?
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
November 8, 2011 at 7:30 am
Two examples of database files:
26GB mdf + 12GB ldf
10GB mdf + 28GB ldf
When I look at previous backups, the BAK files have grown 2GB overnight.
type of backup is: FULL
November 8, 2011 at 7:30 am
You haven't answered either of my questions.
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
November 10, 2011 at 10:52 pm
You can take Log BACKUP, provided that your Recovery model type should be FULL or Bulk Logged.
Sagar Sonawane
** Every DBA has his day!!:cool:
November 10, 2011 at 11:41 pm
Log truncation occurs at these points:
•At the completion of any BACKUP LOG statement.
•Every time a checkpoint is processed, provided the database is using the simple recovery model. This includes both explicit checkpoints resulting from a CHECKPOINT statement and implicit checkpoints generated by the system. The exception is that the log is not truncated if the checkpoint occurs when a BACKUP statement is still active
The size of a transaction log is therefore controlled in one of these ways:
•When a log backup sequence is being maintained, schedule BACKUP LOG statements to occur at intervals that will keep the transaction log from growing past the desired size.
•When a log backup sequence is not maintained, specify the simple recovery model.
Factors That Can Delay Log Truncation
http://msdn.microsoft.com/en-us/library/ms345414(v=SQL.105).aspx
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply