[dba-SQLServer] A couple of quick questions

[Borge Hansen]

fhtapia,
thanks a lot for taking the time to enlighten me a bit here.
I will have to read up a bit more on this, so I can set up a proper and
automated backup system of this fairly small but never the less LOB sql
database.
Links to tsql command and vis GUI are both welcome.
/borge

On Thu, May 21, 2015 at 2:15 AM, <fhtapia at gmail.com> wrote:

> Hi Borge,
>   Welcome to the group!  hopefully we can all contribute to your questions
> :)
>
> 1) I haven't worked with Access upsized tables for quite some time, so
> hopefully someone else can chime in here if it's required by the FE GUI.
> 2) You are correct in thinking that you should not need to auto-grow your
> database, for performance reasons growing a database should occur under
> idle times, (So when is that?) that's you the admin, you review the
> database growth by monitoring it using any of the sql reports or 3rd party
> reporting. then you can guage monthly how often you want to grow your
> database.
>
> Your log file should grow as little as possible.  In order to effectively
> use
>  the point in time recovery you need to have regular transaction log
> backups.  This ensures you will be able to recover your database to it's
> last known good state.
>
> Generally to recover a database you FIRST backup the transaction log. then
> step two would be to apply the last full backup, followed by your
> differential backups, and last your transaction log backups.  you of course
> want to discriminate with the last transaction log backup file to specify
> the time to right before the moment of failure.
>
> So what size should the log file be?  A general rule will be to make a
> default size of about 10% of the size of your mdf file.  that's just a
> generic rule, but if you make 2x the size of the largest table in your mdf
> (data) file.  you can bet that you will have very little growth occurring
> in the transaction log.
>
> 3) After a backup Sql server takes the committed transactions from the log
> file and writes them to the data file.  if you are in simple mode this
> happens regularly on a checkpoint.  If you have a mission critical
> transactional database you will want to backup the log on a regular basis.
> My setup is generally to backup the log when it reaches 60% of capacity.  I
> write my backup files to a separate disk system, in case of a disk
> catastrophe.  So this provides me with a regular log backup at roughly
> every 30 min for many of our systems.
>
> so under regular practice you see, once you've set your transaction log
> file, you will only need to grow your transaction log file once your data
> file reaches a larger capacity.
>
> With Sql Server, the database files are set, and they reuse any internal
> space, when available.  So the file size you see on the disk is not the
> actual size of your data, which is why your bak files are a different size.
>
> 4)  there are a few, I'll dig through some of my links and post back.  Do
> you prefer a tsql command or via the GUI?
>
>
> On Tue, May 19, 2015 at 10:34 PM Borge Hansen <pcs.accessd at gmail.com>
> wrote:
>
> > Hi all,
> > We are finally in the process of migrating an Access application backend
> > having been in production since 2000 to SQL Db backend.
> > At the same time we are upgrading the Frontend from Access2003 to
> > Access2010.
> > And the client is transferring from 2003 Terminal Server to 2012R2
> Terminal
> > Server (I think they call it something else now other than Terminal
> > Server).
> >
> > We upsized to 2005 SQL Standard Edition Db using the Access2003 Upsizing
> > wizard and looking out for the various gotchas.
> >
> > The SQL Backend is now in production on existing 2003 Server
> >
> > It is about to be transferred to SQL 2014 Express on new 2012R2 Server
> >
> > A couple of questions:
> >
> > *1*.
> > Each table has lots of extended table properties - probably carried over
> > from Access.
> > Is there any need to keep these extended properties?
> >
> > *2*.
> > I backed up a version of the 2005SQL production Db and restored on
> SQL2014
> > Express as a development / staging Db.
> >
> > In Db general properties the Db is reported as being 1390.31Mb in size
> with
> > 80.61Mb available.
> > The .mdf file is report as being 780Mb in initial size;
> > The .ldf file as being 612Mb in initial size.
> >
> > I changed the auto growth from default 10Mb for the .mdf and 10% for the
> > .ldf to 1,024Mb for each with maximum of 10,240Mb (10Gb - max size for a
> > SQL Express Db).
> >
> > An incremental growth of 1Gb - is that best setting?
> > Or what do you people suggest?
> >
> > *3*.
> > I did another full backup from the SQL 2005 Db using SSMS. The .bak file
> is
> > 727Mb.
> >
> > What happens to the .ldf log file during a full backup?
> >
> > At a high conceptual level I understand the function of the log file: It
> > helps restore a Db to the point of  last committed transaction before a
> > crash by using a combination of the last full backup and the log file.
> >
> > When we do a full backup is the log file "reset" somehow or does it still
> > keep a lot of history information?
> > A log file can grow very big.
> > Some say to never shrink the log file...
> > What is the abc of dealing with / handling the log file?
> >
> > *4*.
> > Anyone got a link to a good step by step walkthrough of how to do a
> restore
> > using a full backup + existing log file to a specific point, i.e. just
> > before a Db crash.
> >
> >
> > Thanks,
> > /borge
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
> >
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>