[dba-SQLServer] A couple of quick questions

[fhtapia at gmail.com]

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
>
>