February 25, 2010 at 4:03 am
I've got a 10Gb database, that has only a 10Mb transaction log.
The database is set to FULL recovery model.
There are no backups running for this database (!).
Hundreds of rows of data are written to one specific table in the database, but I can't work out why the transaction log isn't filling. Can anyone shed any light on why this might be?
February 25, 2010 at 4:24 am
Is there any job running which truncates and shrink the log..? Please confirm.
February 25, 2010 at 4:43 am
If the database was switched from Simple to Full recovery and a backup has never been run it is possible that it isn't in Full recovery mode at all.
http://msdn.microsoft.com/en-us/library/ms178052.aspx
BrainDonor.
February 25, 2010 at 4:52 am
There's no jobs on the server at all, backup or otherwise.
February 25, 2010 at 4:53 am
BrainDonor (2/25/2010)
If the database was switched from Simple to Full recovery and a backup has never been run it is possible that it isn't in Full recovery mode at all.http://msdn.microsoft.com/en-us/library/ms178052.aspx
BrainDonor.
Hmm, that's a interesting point. Is there any way of determing how a database was created?
February 25, 2010 at 5:00 am
Hmm, that's a interesting point. Is there any way of determing how a database was created?
Not that I'm aware of. However, if it was deliberately set to Full Recovery shouldn't there be a backup plan for it?
I'd be tempted to run a Full backup and then see what happens to the Log.
BrainDonor.
February 25, 2010 at 7:23 am
Neil Thomas (2/25/2010)
Hundreds of rows of data are written to one specific table in the database, but I can't work out why the transaction log isn't filling. Can anyone shed any light on why this might be?
Because you have never run a backup.
Without a full backup, there's nothing to base the log chain on (remember, logs are records of changes to the DB), hence there's no point in keeping them. Once you take the first full backup (first after creating the DB or switching to full recovery), then SQL will start to retain the logs so that you can back them up.
If you try to take a log backup now, you'll get an error saying that SQL can't take a log backup because there's no current full 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
February 25, 2010 at 7:25 am
BrainDonor (2/25/2010)
A little bit of testing shows that it takes the recovery model from the 'model' db (oddly enough).
Why oddly? That's what the model database is, a model for the creation of new databases. Set model's recovery model to simple and all new databases will be in simple recovery. Create a table in model and all new databases will have that table in, etc.
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
February 25, 2010 at 7:45 am
Neil, I think the important thing here is you get jobs set up to backup your database (full and log backups).
🙂
---------------------------------------------------------------------
February 25, 2010 at 8:35 am
Yup, they'll be added. I just wanted to understand how it had got into that state.
It's supposed to have been backed up as part of a system backup (hence somebodies decision not to take SQL backups) but that hasn't happened for a while.
February 25, 2010 at 10:31 am
Like Gail Shaw said: If you have never taken a FULL backup of your database, log backup of that database will also be not available.
To Double-check:
You can query an undocumented system view called sys.database_recovery_status. If the value of last_log_backup_lsn column of this view is NULL it means the database is not maintaining a sequence of log backups and it is in auto-truncate mode that means SQL Server is truncating the log File on every Checkpoint.
You can use the below mentioned query:
SELECT @@servername,db_name(database_id) as 'database', last_log_backup_lsn FROM sys.database_recovery_status(nolock)
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
February 25, 2010 at 11:05 am
Neil Thomas (2/25/2010)
It's supposed to have been backed up as part of a system backup (hence somebodies decision not to take SQL backups) but that hasn't happened for a while.
I don't think I would rely on system backups to ensure my database is backed up. It is always nice to say that the DB is backed up and you know for sure that the backup has occurred. After-all, it will be the DBA who is questioned when the database is broke.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 25, 2010 at 12:19 pm
Neil Thomas (2/25/2010)
It's supposed to have been backed up as part of a system backup (hence somebodies decision not to take SQL backups) but that hasn't happened for a while.
A system backup as in a file system backup? as in copy the files off somewhere else?
If so, don't! File level backups do not work for SQL server, the files are open and locked by SQL while the database is in use and the file backups that can read open files will often read off inconsistent files.
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply