Seeing that the recovery model is set to simple and msdn states that the simple recovery model does not support point-in-time recovery - Does this mean that I won't be able to use my transaction log backups to restore the database in a disaster to an hour before it happened?
Even taking a transaction log backup is not supported for databases using the SIMPLE
recovery model. This is a restriction of the database engine based on how this recovery model works, and the recovery features it doesn't support, as you mentioned.
A transaction log backup maintenance plan task automatically skips databases in SIMPLE
recovery to avoid causing errors.
Which backup should be done first, the database backup or the transaction log backups? Articles that I'm busy reading say I should do the database backup first and then the transaction log backup else I will get maintenance plan errors, but I'm currently first backing up my transaction logs and then data databases and I'm not getting any errors.
For the reasons I mentioned above, it won't matter for databases using SIMPLE
recovery, as they will be skipped by the transaction log backup task.
For databases in the other two recovery models, a full backup must exist before you start taking transaction log backups (just the first time), or you will get an error -- this is probably what the articles refer to.
Point-in-time recovery ability is normally driven by business need -- in other words, you determine how critical the data is and how much you can afford to lose, then set the appropriate recovery model to meet those needs, and finally create a backup solution.
Even though SIMPLE
recovery does not support point-in-time recovery, if an hour of data loss is okay, perhaps a differential backup solution could work for you. (There are far too many variables that go into developing this kind of solution to give you a complete picture with what was provided in the question.)
TRUNCATEONLY affects both the LOG and the DATA files in 2008. On BOL for SQL Server 2012 the message simply indicates that if you only wish to SHRINK the database file, then you should use DBCC SHRINKFILE which will allow you to shrink either the data or log files.
For 2008, it is clearly indicated that TRUNCATEONLY only affects DATA files.
Lets test. To visualize what happens using TRUNCATEONLY
with SHRINKDATABASE
, here is a run down of what happened to a database called performance
that I have installed locally.
SELECT @@VERSION;
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
Jun 28 2012 08:36:30
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
USE performance;
I ran DBCC LOGINFO
just to take a peak inside the transaction log and found that all of the virtual log files after about 200 were inactive, status = 0. In my performance database, all those inactive VLFs can be truncated and the space can be given back to the OS.
Here is some sample output from LOGINFO.
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
------ -------- ----------- ------ ------ ------ -----------------
2 253952 8192 23 2 64 0
2 253952 262144 24 2 64 0
2 270336 516096 25 2 64 24000000013400005
I then ran DBCC SQLPERF(LOGSPACE)
and got the following output
DBCC SQLPERF(LOGSPACE)
/*
Database Name Log Size (MB) Log Space Used (%) Status
Performance 9870,867 3,395626 0
*/
I then ran
DBCC SHRINKDATABASE(PERFORMANCE, truncateonly)
and got the following result
/*
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
8 1 66464 288 44944 44944
8 2 116584 72 116584 72
*/
Next, I reran
DBCC SQLPERF(LOGSPACE)
and got
/*
Database Name Log Size (MB) Log Space Used (%) Status
Performance 910,8047 37,08699 0
*/
SHRINKDATABASE
with TRUNCATEONLY
gave back over 9 GB of available space from the transaction log file back to the OS.
I tried the same experiment with another database called performance2
sp_spaceused
/*
Performance2 52.19 MB 22.81 MB
*/
DBCC SHRINKDATABASE(Performance2, truncateonly)
sp_spaceused
/*
database_name database_size unallocated space
Performance2 31.13 MB 21.13 MB
*/
Which gave back 20 MB to the OS. Using SQL Server 2008, SHRINKDATABASE TRUNCATEONLY
shrinks both data and transaction log files.
Best Answer
If you need to
shrink
yourlog file
often, it means that thesize
it has after ashrink
is not sufficient for your everyday activity, so it grows again and again.This means that you do unnecessary work when
shrink
it, and, that is worse, every time yourlog
needs to expand, it wastes timezeroing out
every new piece of file it adds (and your users WAIT for the completion of this zeroing out).You should stop this double waste of time and eliminate
log shrinking
from yourmaintenance plan
. If you are not satisfied with the size of your log,shrink
it once and then make your log backups more frequently.