November 12, 2007 at 1:18 am
Dear All,
I had taken the full backup on saturday evening at 6pm. And we have scheduled jobs for diff backup every 3 hours. And there will not be any database activity on saturday after 6 upto monday morning. I had noticed that there is a enormous increase in the size of the diffbackup.
The diffbackup which was taken at 6.30am was around 500 mb but the diffbackup at 9.30am was around 7 GB. And there were no employees in our organisation to perform any database activity. And nobody can access the database from outside our office. Plz guide me why the database diffbackup size had increased from 500 Mb to 7 Gb within a span of 3 hours and that too on sunday.
Thanks in Advance
Chandra Mohan N
[font="Verdana"]Thanks
Chandra Mohan[/font]
November 12, 2007 at 3:09 am
A differential backup saves only the pages since the last full backup. If many pages have changed you can get a very large differential backup. If there was little user activity have a look at your jobs. For example, do you have a db_reindex scheduled for Saturday morning?
Regards,
Andras
November 16, 2007 at 12:37 pm
Something definitely happened. Either someone did a load, index rebuild, some activity that moved lots of pages around.
November 17, 2007 at 7:24 am
Dear All,
we have a team of 4 sql dba's and none of us were there at that time and no scheduled jobs are running. My live server will be accessed by employees from 14 different branches. And each branch is given a login so all the employees in that region access the database with that userid and password. I suspect that something unsual happened at that time. I would like to monitor who is logging to the database and what are the queries that are being executed. Can anyone tell me how to enable this system from the backend.
Thanks & Regards
Chandra Mohan N
[font="Verdana"]Thanks
Chandra Mohan[/font]
November 19, 2007 at 8:32 am
Set up a Profiler trace (or better yet, server side trace). You can search in Books Online under Profiler (http://msdn2.microsoft.com/en-us/library/ms173757.aspx)
You might want to just monitor the batches and look for a section of time that has a lot of activity. You can automate this with stored procedures and let the trace run in the background. One thing to be aware of is that this can generate lots of data if there is a lot of activity. So be sure that you have enough disk space, add in a large drive or move the files off to another server regularly.
November 20, 2007 at 6:09 am
It is important to design a backup strategy to meet the recovery SLA, and I have seen very few situations where a differential backup is beneficial.
If the SLA is to recover the database to any point in time (PIT) then you must use Full recovery. This requires that you do Full database backups and Log backups.
If the SLA is to only recover to (say) 09:00 on any day then you can use Simple recovery. This only requires that you do Full database backups.
If you are replicating a database using Log Shipping you must also use Full recovery with Full database backups and Log backups.
If you have a database in Full recovery mode, then a backup strategy using daily full database backups followed by regular (e.g. hourly) log backups is normally the simplest approach for both backup and recovery. (If you are using Log Shipping then moer frequent Log backups are often necessary.)
If the database is very large (over 500 GB) then it can become hard to schedule a daily full backup, so doing a weekly full backup and daily differentials can then be a good approach. If you do regular index rebuilds, make sure you do the full backup after the rebuilds in order to minimise the size of the differentials.
If you have to switch between Full and Bulk-Logged, or run continuously with Bulk-Logged to meet performance targets for bulk loads, then you need to do a database backup to re-establish the log chain for PIT recovery. If the database is small (under 50 GB) I would always aim to do a Full backup at this point, but for larger databases a Differential can be more appropriate.
If you have large or very heavily updated databases and know you can avoid using differential backups, you can avoid the locking issues around resetting the page map used for Differentials by using the WITH COPY_ONLY option.
If your database is in Simple recovery mode, then differentials can be more useful. The best approach is still to only use Full database backup, scheduled to run at the time required to meet your recovery SLA. However if it is not convenient to run the Full backup at this time, run it when convenient and do a differential at the time required for the recovery SLA. The differential should run quickly and capture the changes needed to meet the SLA.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply