John W. Colby
jwcolby at colbyconsulting.com
Sun Sep 5 11:20:26 CDT 2004
Francisco, I appreciate all you assistance in this stuff. Your patience and knowledge are a godsend, and on SUNDAY at that!!! 8-) So how do I run this backup? Don't worry, I am heading out to buy a SQL Server 2K admin book which will hopefully assist me in all this stuff. In the meantime I need to get this log truncated so that I can start the imports back up before I go. >BACKUP LOG DBName WITH TRUNCATE ONLY Is this run in the SQL query window (as a query)? Do I need to somewhere tell SQL Server where the backup files are going to go? >DBCC SHRINKFILE (DBName_Log, 10) Do I need to do the shrink? I am going to continue the import which will grow the container again. Is the space reused if I don't shrink it or is it like Access where the file just adds NEW space and ignores old empty space. The help seems to indicate that the freed up empty space will be reused. While I have your attention... The following is what I see in the BOL for truncate. Truncate Method The Truncate method archive-marks transaction log records. Applies ToTransactionLog Object Syntax object.Truncate( ) Parts object Expression that evaluates to an object in the Applies To list How do I "call" this code. It appears that object is an object to be called from code. Can I do this from Access (since that is my comfort zone)? Do I have to reference this thing somehow. IOW What the h&^% is this and how do I use it? I am an accomplished programmer (or like to think so) but I haven't a clue how to get started with this stuff. John W. Colby www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Francisco Tapia Sent: Sunday, September 05, 2004 11:57 AM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] Is it my imagination? I know your rant on this one John, but the truth is that Truncating a log is something that must be done w/ care depending on how you want it to be done, the point to a Transaction log file is to keep an additional redundancy to the recently added/modified data. So you don't want to just blindingly truncating logs. Additionally, I think the mentality is diffrent when dealing w/ Sql Server than it is w/ something like Access.... Now it's not impossible for them to give you a gui tool to do this: (where DBName is your database name) BACKUP LOG DBName WITH TRUNCATE ONLY DBCC SHRINKFILE (DBName_Log, 10) the 10 is the size you want your newly truncated log to be the size of. If you simply just backup the log you will also "Clear checkpoints" and fully commit data from the log to the database. But running a point such as the above w/ TRUNCATE ONLY could be dangerous in a production environment because you "could" potentially loose data if some user were trying to append a series of large records at that point in time. I suspect that is why they don't just give you a GUI button for it. Because you know people, the moment you make something easier, they take that route instead of doing things the right way... On Sun, 05 Sep 2004 11:23:20 -0400, John W. Colby <jwcolby at colbyconsulting.com> wrote: > Is it my imagination or is SQL Server just not particularly friendly? > Yea, I know, it is a BIG database and you are supposed to know what > you are doing but still... > > For example, I need to truncate my log files. I am doing this BCP and > three of the 4 BCPs I had running last night failed due to the log > file running our of room. SIGH! > > Now I can find a help page on Truncating the log file, but nowhere > does it actually say "do this". It seems that this would be something > that needs to be done manually once in awhile if for no other reason > than (like this case) to get back in operation quickly. So you would > THINK there would be a "truncate log file" menu item somewhere. > > Ah yes, research is sooooo good for the soul. I have a TON of real > (paying) work waiting to be done and I'm trying to figure out how to > do a simple thing like truncate the log file! > -- -Francisco _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com