J- P
jnatola at hotmail.com
Tue Aug 13 18:40:54 CDT 2013
So I changed recovery to simple, did a shrink via smss and selected log, shrink, reclaim unused space and WOW did it shrink, it went down to 1MB , thats not even 1 % , much less 10% of the DB size (hope I didnt muck it up) Then I reverted back to full recovery , ran a full backup and a log backup and its still working, as far as versions they are the following ; 2003 r2 sp2 sql 9.0.5000 sp4 What are items I should look for in a trace that should be considered "flags" - this DB started in access 97, went through many hands and "coders" (using the term loosely,) then went to sql express , and the front end has gone from Access 97 to 2000, , xp, 2003, and now on 2010 So there are many places that it could go wrong- If I were to open a PSS case, should I open an Access 2010 case or SQL case? Thanks again for your help Jean-Paul Natola > From: fhtapia at gmail.com > Date: Tue, 13 Aug 2013 14:02:14 -0700 > To: dba-sqlserver at databaseadvisors.com > Subject: Re: [dba-SQLServer] Access disconnecting from sql express DB > > 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 > > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com >