It keeps growing and growing and growing...
March 28, 2008 9:42 AM Subscribe
Help me understand MSSQL logs... At least enough to do what I need to do.
Ok, I know the logs are there for disaster recovery and such. I understand the concept of Point-in-Time restore. But what I don't get is why the logs don't get truncated when a backup of the DB is done. One would think that you would get a fresh new log started after a backup, but yet my log keeps growing and growing and growing.
Anyway, I'm sure there are reasons for it, but what I need is to be able to do backups where the log gets reduced in size when the backup is done, since I will no longer need the past logged data after that point.
For reference: I'm doing 'Full' recovery model, and a 'Full' (Backup Type) backup is done once daily, at night. I want to keep the logs for Point in Time restores for crashes that happen during the day. I don't need a log for what happened in prior days. The code used is like as follows:
BACKUP DATABASE [my_db]
TO DISK = @sBackupName
WITH INIT, NOUNLOAD, NAME = N'backup label', NOSKIP, STATS=10, NOFORMAT
Yes, I'm a SQL n00b.
Thanks for any help/info on this.
Ok, I know the logs are there for disaster recovery and such. I understand the concept of Point-in-Time restore. But what I don't get is why the logs don't get truncated when a backup of the DB is done. One would think that you would get a fresh new log started after a backup, but yet my log keeps growing and growing and growing.
Anyway, I'm sure there are reasons for it, but what I need is to be able to do backups where the log gets reduced in size when the backup is done, since I will no longer need the past logged data after that point.
For reference: I'm doing 'Full' recovery model, and a 'Full' (Backup Type) backup is done once daily, at night. I want to keep the logs for Point in Time restores for crashes that happen during the day. I don't need a log for what happened in prior days. The code used is like as follows:
BACKUP DATABASE [my_db]
TO DISK = @sBackupName
WITH INIT, NOUNLOAD, NAME = N'backup label', NOSKIP, STATS=10, NOFORMAT
Yes, I'm a SQL n00b.
Thanks for any help/info on this.
« Older I'm going to Disney World!!! (Crap, what do I do... | Commuting from South Orange County to LA environs? Newer »
This thread is closed to new comments.
You have to backup your transaction logs also. SQL considers this necessary to have a "complete" backup. From the SQL Server books online:
"Each transaction backup captures the inactive portion of the log and then truncates the log to remove the inactive portion. "
See the help section titled "Backup Under the Full Recovery Model" for more info.
posted by xena at 11:37 AM on March 28, 2008