[dba-SQLServer] A couple of quick questions

[Arthur Fuller]

Hi Borge, and another welcome to the group.

Since Stuart asked,  I'll chime in with a note or two on the topic of
extended properties.

In general, their impact on the size of the database is negligible, so
there's no reason to delete them. Instead, be thankful for whatever the
upsizing wizard was able to bring in.

More specifically, extended properties can be used to:
a) specify captions for a table, view or column
b) specify an input mask (i.e. for zip/postal codes and maybe phone
numbers, part numbers, etc.)
c) describe various objects (table, view, column) for use in data
dictionaries and documentation

Beyond that, I personally think it's a good idea to add useful ones that
might not have been migrated. Whenever I create a new table or discover one
without extended properties, I typically add new ones that I think that I
will someday need.

Here's a simple example of how to do that, cribbed from the documentation.
The extended property value 'Minimum inventory quantity.' is added to the
SafetyStockLevel column in the Product table that is contained in the
Production
schema:

<sql>

USE AdventureWorks2008R2;
GO
EXEC sys.sp_addextendedproperty
@name = N'MS_DescriptionExample',
@value = N'Minimum inventory quantity.',
@level0type = N'SCHEMA', @level0name = Production,
@level1type = N'TABLE',  @level1name = Product,
@level2type = N'COLUMN', @level2name = SafetyStockLevel;
GO

</sql>

To display the extended properties, use the function
fn_listextendedproperty
<https://technet.microsoft.com/en-us/library/ms179853%28v=sql.105%29.aspx>.
 This function can display extended properties on a single database object or
 all objects in the database, based on the object type. For example, you
 can return the extended properties on a table or all columns in the
table.

The following example displays all extended properties set on the database
itself.

<sql>

USE AdventureWorks2008R2;
GO
SELECT objtype, objname, name, value
FROM fn_listextendedproperty(default, default, default, default,
default, default, default);
GO

 </sql>

Hope this helps.

Arthur


On Wed, May 20, 2015 at 2:15 PM, <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
>
>


-- 
Arthur