Jim Lawrence
accessd at shaw.ca
Thu Oct 6 19:22:55 CDT 2005
Hi Ping: That does seem very fast. You would not have mistaken the Event log file, which also has size limits, for the transaction log file? Truncating - shrinking the log file after a backup should be sufficient security. The only time there would be an issue is if the server database ever becomes corrupted as it would take longer to recover. 3.5 million records is fairly average for a medium sized business. Jim -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Ping Li Sent: Thursday, October 06, 2005 12:18 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] Help: What to do when transaction log file is full Hi, Jim and Francisco, Thank you both for your suggestions. I think maybe it's better to set up the data file for auto-growth by a small increment of MB. But I don't quite understand the log file. I mean, it has 3GB space allocated and the usage is only 4% when I checked with command: DBCC sqlperf(logspace). Why did it give me log file full error when I tried to insert records? Is 3.5million records really huge? We don't need the transaction log so we set the recovery model to SIMPLE and backup the database with no log option. That means we truncate the log file every day when we back up the database. Should I shrink the log file first before I set the database to autogrow? What would the file size be after it gets shrunk? will it have performance hit later on when the file grows back large? As I understand, truncate a log file doesn't free up the space for other use. Only shrink can actually reduce the file size. Thanks again. Ping dba-sqlserver-request at databaseadvisors.com wrote: >Send dba-SQLServer mailing list submissions to > dba-sqlserver at databaseadvisors.com > >To subscribe or unsubscribe via the World Wide Web, visit > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >or, via email, send a message with subject or body 'help' to > dba-sqlserver-request at databaseadvisors.com > >You can reach the person managing the list at > dba-sqlserver-owner at databaseadvisors.com > >When replying, please edit your Subject line so it is more specific >than "Re: Contents of dba-SQLServer digest..." > > >Today's Topics: > > 1. Re: Help: What to do when transaction log file is full > (Jim Lawrence) > 2. Re: Help: What to do when transaction log file is full > (Francisco Tapia) > > >---------------------------------------------------------------------- > >Message: 1 >Date: Wed, 05 Oct 2005 11:20:50 -0700 >From: Jim Lawrence <accessd at shaw.ca> >Subject: Re: [dba-SQLServer] Help: What to do when transaction log > file is full >To: dba-sqlserver at databaseadvisors.com >Message-ID: <009601c5c9d9$8403e0a0$017ba8c0 at xpserver> >Content-Type: text/plain; charset=us-ascii > >Hi Ping: > >My recommendation would be to let the database grow automatically and >monitor how much space is being used. If you backup regularly and shrink >only when necessary there should be no problem. If you set a fixed limit and >the database is live there can be some serious ramifications if it suddenly >runs out of space. > >My two cents worth. >Jim > >-----Original Message----- >From: dba-sqlserver-bounces at databaseadvisors.com >[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Ping Li >Sent: Wednesday, October 05, 2005 7:19 AM >To: dba-sqlserver at databaseadvisors.com >Subject: [dba-SQLServer] Help: What to do when transaction log file is full > >Hi, > >I am absolutely new to DBA tasks and I really need help from you all. > >Last week, after I used DTS wizzard to copy a huge table as a backup >copy, I used query analyzer to truncate the old table and insert new >records into it. It ran for a long time and came back with an error >message saying "the log file is full, free up some space...". I checked >the database. It was set to use simple recovery model and with a static >size. The database is set to backup complete with no log option. The >reason for what is that the database is more of a data mart than >transactional database. > >The settings are as follows: >Data file: space allocated: 100000MB. >Log file: space allocated: 3000MB; automatically growth--checked; file >growth: 1000 in MB; Maximum size: restricted file growth(MB) 3100 > >I talked with somebody. Some suggested me change the database to grow >automatically or auto-increment by 10% while the other strongly urge not >to set it to grow automatically. One of people I talked shrink the log >regularly. > >I would appreciate any imput on what's the best practice in managing log >files. Thank you, > >Ping Li >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > > > >------------------------------ > >Message: 2 >Date: Wed, 5 Oct 2005 13:15:44 -0700 >From: Francisco Tapia <fhtapia at gmail.com> >Subject: Re: [dba-SQLServer] Help: What to do when transaction log > file is full >To: dba-sqlserver at databaseadvisors.com >Message-ID: > <b874372a0510051315g2deff45ev66a31c434346e52a at mail.gmail.com> >Content-Type: text/plain; charset=ISO-8859-1 > >Ping, >You acutally do not need a log file that is that huge in size, at least not >typically. Some DBAs will argue with unresctricted growth on the data file, >but imnsho, it's not a huge issue to allow the data file to grow by at least >10%. The log file does not have this need. If you shrink the log file to >something like 50mb, you can monitor it's usage, and have it to auto-backup >when the transaction lone reaches 60% of capacity by setting up an alert and >having the alert performe the backup job when the log file is at 60%. If you >monitor the growth of the transaction log you'll find that it may not be >growing that fast, and thus you can possibly shrink the log file even >further possibly 25mb or 15... depends really on how heavy your server is >being used. > >In books online lookup Sql Agent Alerts to find out more on what else you >can do w/ them. > >hope this helps. > >On 10/5/05, Jim Lawrence <accessd at shaw.ca> wrote: > > >>Hi Ping: >> >>My recommendation would be to let the database grow automatically and >>monitor how much space is being used. If you backup regularly and shrink >>only when necessary there should be no problem. If you set a fixed limit >>and >>the database is live there can be some serious ramifications if it >>suddenly >>runs out of space. >> >>My two cents worth. >>Jim >> >>-----Original Message----- >>From: dba-sqlserver-bounces at databaseadvisors.com >>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Ping Li >>Sent: Wednesday, October 05, 2005 7:19 AM >>To: dba-sqlserver at databaseadvisors.com >>Subject: [dba-SQLServer] Help: What to do when transaction log file is >>full >> >>Hi, >> >>I am absolutely new to DBA tasks and I really need help from you all. >> >>Last week, after I used DTS wizzard to copy a huge table as a backup >>copy, I used query analyzer to truncate the old table and insert new >>records into it. It ran for a long time and came back with an error >>message saying "the log file is full, free up some space...". I checked >>the database. It was set to use simple recovery model and with a static >>size. The database is set to backup complete with no log option. The >>reason for what is that the database is more of a data mart than >>transactional database. >> >>The settings are as follows: >>Data file: space allocated: 100000MB. >>Log file: space allocated: 3000MB; automatically growth--checked; file >>growth: 1000 in MB; Maximum size: restricted file growth(MB) 3100 >> >>I talked with somebody. Some suggested me change the database to grow >>automatically or auto-increment by 10% while the other strongly urge not >>to set it to grow automatically. One of people I talked shrink the log >>regularly. >> >>I would appreciate any imput on what's the best practice in managing log >>files. Thank you, >> >>Ping Li >>_______________________________________________ >>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 >> >> >> >> > > >-- >-Francisco >http://pcthis.blogspot.com |PC news with out the jargon! >http://sqlthis.blogspot.com | Tsql and More... > > >------------------------------ > >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > >End of dba-SQLServer Digest, Vol 32, Issue 4 >******************************************** > > -- Ping Li Developer/Analyst CLA Fiscal Administration 208 Johnston Hall 101 Pleasant St SE University of Minnesota Minneapolis, MN 55455 (612)-626-0304 pli at umn.edu _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com