[dba-SQLServer] A couple of quick questions

Thu May 21 12:27:46 CDT 2015

Thanks Arthur for adding your insights to Stewart's response...
Will keep those extended table properties.
/borge

On Thu, May 21, 2015 at 6:24 PM, Arthur Fuller <fuller.artful at gmail.com>
wrote:

> 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
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>