[dba-SQLServer] Access disconnecting from sql express DB

J- P jnatola at hotmail.com
Tue Aug 13 13:28:15 CDT 2013


As I inherited this when I took over the network, (and by no means a dba) the backup model
is set to full, there are log backups that run every hour, and nightly database backups that occur at 11 (in addition, there is a remote backup that I implemented that takes the hourly trn files and the nightly backup  off site  (a bit redundant but better safe than sorry).
 
Looking at the "general" properties of the DB it indeed says 
last database backup 8/12/2013 11 pm
last database log backup 8/12/2013 2pm
 
I did try a manual backup log file but the it has no affect on the LDF , and it is 8 GIGs
 
Should I change the recovery model, or what is the best option to shrink this 8GB mammoth?
 
Can I do a SHRINKFILE during operation hours?
 
Also I did read that windows 7 64 bit and access 2007 had well known issue that suggests/recommends turning off llmnr
 
http://accessexperts.com/blog/2011/11/02/windows-7-64bit-slow-with-access-2007-solved/
 
But it doesn't say if that carried over to 2010-
 
 
 

 

 

 

 

 

 

 

 

 

 

Jean-Paul Natola

 

 
> From: fhtapia at gmail.com
> Date: Tue, 13 Aug 2013 11:10:41 -0700
> To: dba-sqlserver at databaseadvisors.com
> Subject: Re: [dba-SQLServer] Access disconnecting from sql express DB
> 
> you need to truncate that log file, your system is unnecessarily seeking on
> that file if you mis-spoke and meant 8mb then disregard. if you DID mean
> 8GB for the ldf (Transaction Log File) make sure you either A) are on the
> correct recovery option for your database (Full for up to the minute
> failures, or Simple, for nightly backups restores only).
> 
> run a backup of the log then a shrinkfile, in general the rule of thumb is
> that the transaction log file should only be about 10% of your database
> size.  If  you have load operations that occur regularly, then it should be
> only as large as your largest data load table.
> 
> This will help mitigate the server side problem.
> 
> however, there is a known problem running on Express on 2003 server:
> 4.0 Database Engine
> The notes in this section are late-breaking items for the SQL Server 2005
> Database Engine and Database Engine-specific command prompt utilities that
> also apply to SQL Server Express.
> 
> http://support.microsoft.com/kb/910229/en-us
> 
> 4.1 Connections May Be Forcibly Closed When Running on Windows Server 2003
> > SP1
> > If TCP/IP networking is turned on, client connections to an instance of
> > the SQL Server Express Database Engine running on Windows Server 2003
> > Service Pack 1 might fail with the following error:
> >
> > ProviderNum: 7, Error: 10054, ErrorMessage: "TCP Provider: An existing
> > connection was forcibly closed by the remote host".
> >
> >
> > This might occur when you are testing scalability with a large number of
> > client connection attempts. To resolve this issue, use the regedit.exe
> > utility to add a new DWORD value named SynAttackProtect to the registry key
> > HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\
> > with value data of 00000000
> > .
> >
> > *Security Note* Setting this registry key might expose the server to a
> > SYN flood denial-of-service attack. Remove this registry value when testing
> > is complete.
> >
> > *Note* Incorrectly editing the registry can cause serious problems that
> > might require you to reinstall your operating system. Microsoft cannot
> > guarantee that problems resulting from editing the registry incorrectly can
> > be resolved. Before editing the registry, back up any valuable data. For
> > more information about how to back up, restore, and edit the registry,
> > click the following article number to view the article in the Microsoft
> > Knowledge Base:
> > 256986 <http://support.microsoft.com/kb/256986> Description of the
> > Microsoft Windows registry
> >
> 
> 
> 
> 
> -Francisco <http://twitter.com/seecoolguy>
> 
> 
> 
> 
> On Tue, Aug 13, 2013 at 10:55 AM, J- P <jnatola at hotmail.com> wrote:
> 
> > Hi all,
> >
> >
> >
> > I'm not sure if this is an Access or SQL question, so please excuse me if
> > I'm in the wrong area,
> >
> > New PC's running 7 64  and are using access 2010 32bit runtime to connect
> > to a sql express 2005 database,
> >
> > Computers are new, new gigabit poe switch and new cat 6 has been run (and
> >  tested and certified), one user has already been disconnected and kicked
> > out twice today, I want to leave a trace running on sql till end of
> > business , should I be concerned about performance issues, or is there
> > another way to troubleshoot this?  server windows 2003 r2 dual  1.84GB
> > rammirror 80gb sata mdf is 900mbldf is 8gb  however,DBCC SQLPERF(logspace)
> >  shows;
> > DATABASE 7608.305     0.9379592 0 and when I look at the general
> > properties in smss it shows Size   8492.75MBSpace Avail  206.38 MB# of
> > Users 17  Any pointers are greatly appreciated
> >
> > _______________________________________________
> > 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
> 
 		 	   		  


More information about the dba-SQLServer mailing list