Francisco Tapia
fhtapia at gmail.com
Tue Aug 13 16:02:14 CDT 2013
replies in-line... -Francisco <http://twitter.com/seecoolguy> On Tue, Aug 13, 2013 at 11:28 AM, J- P <jnatola at hotmail.com> wrote: > 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? > the FULL recovery model is reserved for mission critical systems where you cannot afford to lose not even a minute of data. On a system failure your initial thing to attempt is to perform a backup of the transaction log, when you go to recover the database you restore up to a point in time by first applying the full backup then each subsequent transaction log file until you reach the "point in time" you want to recover to. There is no need to change the recovery model if the system meets those requirements and the business cannot afford to loose not even a minute of data. other workarounds allow you to setup the recovery model as a SIMPLE recovery, where you have nightly full backups (or weekly if they are too large) then hourly differential backups (or by whatever the largest acceptable data loss the business is wiling to accept). Can I do a SHRINKFILE during operation hours? > Yes, it simply brings down the file size, and should have very little impact to system performance while it shrinks the file. You must always perform a log backup before you run a shrinkfile on the log file. 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- > i'm not familiar with that issue, but it might help, per the other article though this seems to be a normal issues with Sql Express on Windows 2003 SP1. Do you know the SP of both your DB Engine and the Windows Server? > > 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 > >