Need to truncate a transaction log

I have a database which is 2 GB but the log file has grown to 35 GB. I would 
like to truncate the log so I set the recovery method to SIMPLE but the log 
file did not truncate after last night's backup.

This has worked for other databases and I get a small log file. Later, I 
reset the recovery method back to FULL.

Rich Lawson 


0
Rich
7/28/2010 4:59:21 PM
sqlserver.server 1327 articles. 0 followers. Follow

4 Replies
574 Views

Similar Articles

[PageSpeed] 18

Rich Lawson (nospam@nospam.com) writes:
> I have a database which is 2 GB but the log file has grown to 35 GB. I
> would like to truncate the log so I set the recovery method to SIMPLE
> but the log file did not truncate after last night's backup. 

If you set the recocvery model to SIMPLE, this will truncate the transaction 
log. However, note that the size of the file will not change. You will need 
to shrink it with DBCC SHRINKFILE.

Note also that the log never is never truncated past the oldest active 
transaction; you can use DBCC OPENTRAN to check for this condition.

Note also that replication can affect whether the log is truncated. 
Transactions not yet send to the distribution database will not be 
truncated.
 
> This has worked for other databases and I get a small log file. Later, I 
> reset the recovery method back to FULL.
 
I hope that you understand that when you do this that you break the log 
chain, and if you have a requirement to be able to do point in time 
restores, you will need to run a full backup directly after having set the 
recovery model back to full.

You are backing up your transaction log regularly, aren't you?


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
7/28/2010 9:58:04 PM
Thank you.

What is the difference from using the command line and the SQL Management 
Server screens?


"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9DC3F3D0928C8Yazorman@127.0.0.1...
> Rich Lawson (nospam@nospam.com) writes:
>> I have a database which is 2 GB but the log file has grown to 35 GB. I
>> would like to truncate the log so I set the recovery method to SIMPLE
>> but the log file did not truncate after last night's backup.
>
> If you set the recocvery model to SIMPLE, this will truncate the 
> transaction
> log. However, note that the size of the file will not change. You will 
> need
> to shrink it with DBCC SHRINKFILE.
>
> Note also that the log never is never truncated past the oldest active
> transaction; you can use DBCC OPENTRAN to check for this condition.
>
> Note also that replication can affect whether the log is truncated.
> Transactions not yet send to the distribution database will not be
> truncated.
>
>> This has worked for other databases and I get a small log file. Later, I
>> reset the recovery method back to FULL.
>
> I hope that you understand that when you do this that you break the log
> chain, and if you have a requirement to be able to do point in time
> restores, you will need to run a full backup directly after having set the
> recovery model back to full.
>
> You are backing up your transaction log regularly, aren't you?
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 


0
Rich
7/28/2010 11:30:59 PM
Rich Lawson (nospam@nospam.com) writes:
> What is the difference from using the command line and the SQL Management 
> Server screens?
 
As long as you use the Script button in the dialogues to review the commands 
before they are executed, none at all. If you don't, the difference is that 
in the latter case you don't know what you are doing.

I always give reference to the commands, since that's all I use. The 
commands are described in Books Online, and you can look up what they do. If 
you are using the GUI you have to guess what various options the GUI 
controls map to.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
7/29/2010 8:53:46 AM
Thanks. I will use your suggestions.

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9DC46ED75545FYazorman@127.0.0.1...
> Rich Lawson (nospam@nospam.com) writes:
>> What is the difference from using the command line and the SQL Management
>> Server screens?
>
> As long as you use the Script button in the dialogues to review the 
> commands
> before they are executed, none at all. If you don't, the difference is 
> that
> in the latter case you don't know what you are doing.
>
> I always give reference to the commands, since that's all I use. The
> commands are described in Books Online, and you can look up what they do. 
> If
> you are using the GUI you have to guess what various options the GUI
> controls map to.
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 


0
Rich
7/29/2010 3:55:52 PM
Reply:

Similar Artilces:

Need help with printing Excel document
I want to print it out exactly as it looks on the screen. With all the cells and rows visable. Highlight all the cells you wish to see printed out. Then do File > PrintArea > SetPrintArea...........then do File > PrintPreview.......what you see is what will print out, assuming you have the correct print drivers and margins set etc......... If it still don't work, post back with more details. Vaya con Dios, Chuck, CABGx3 "jody mullis via OfficeKB.com" <forum@OfficeKB.com> wrote in message news:507040B8C48C0@OfficeKB.com... > I want to print it out exactly a...

Need help with query summing hours
Tring to get total worked hours and total maintenance hours summary. I have a table with the clock hours WorkID WorkDate WorkEmployeeID WorkHours And have a maintenace table MaintID MaintDate MaintEmployeeID MaintHours Needing help combining the information to get summary something like this.. Date - Employee - Clock Hours - Maintenance Hours 1/1/10 Joe Billy Bob 8 6 1/1/10 Billy Joe Bob 8 7 Whay type of query do I need to build, any help would be great. Thanks -- Message posted via http://www.accessmo...

Formula needed 01-22-10
Hi I need a formula that will look in one column range for a certain value and for another value in another column range and when they are both found in the same row, will count the number of occurances. Help!!!! Krissy wrote: > Hi > I need a formula that will look in one column range for a certain value and > for another value in another column range and when they are both found in the > same row, will count the number of occurances. Help!!!! http://www.contextures.com/xlFunctions01.html#SumProduct Omit the "values to be summed". =SUMPRODUCT(--(A2...

need some help please, read inside, cheers!!
Hello Peeps! I am trying to work out a formula that can count multiple strings i.e. =COUNTIF($E$3:$E$40,"Setup") But not only have it count look for the word setup, have it look for another word to i.e!!! =COUNTIF($E$3:$E$40,"Setup", "Done") This doesn't work but u get the jist of what i need, any help would be much appreciated!!me! Thanks Alot Kami Wilson Use separate countifs: =COUNTIF($E$3:$E$40,"Setup") + COUNTIF($E$3:$E$40,"Done") Andy. "Wilson" <Lordwils@btopenworld.com> wrote in message news:78na00ppvvbsqt...

To type I need to depress keys for twice as long as usual. Why?
Having inadvertently depressed a "shift" key for longer than 6 seconds, I have created a problem with typing. To type I need to depress the keys for about twice as long as usual, with an associated sound as well. I can temporarily correct this by depressing the shift key again for longer than 6 seconds but I need to make the correction more permanent. How do I do this? This is a Windows thing, not specifically Word. Go to Start menu > Control Panel > Accessibility Options (or in Vista or Windows 7, Ease of Access Center). Turn off Filter Keys. To prevent it from ...

Newbie
I have installed Exchange 2003 (new install) on a new install of Windows Server 2003. When I went to add info for the SMTP server (using Exchange Management System - EMS) I navigated to Servers - Exchange1 - Protocols - SMTP There's no default SMTP server listed in there. How can I get one in there? -Fran- Did you have IIS installed already? <Fran> wrote in message news:vmrpu1h04d06lpot07kbqu78lnsflpqcaa@4ax.com... >I have installed Exchange 2003 (new install) on a new install of > Windows Server 2003. > > When I went to add info for the SMTP server (using Exch...

Need to remove data in table due to input mask; cannot close.
ACCESS Table open, unable to close or modify telephone field. Had tried i/p mask of 0 due to "Unlisted" numbers appearing right-justified when doing a report. I sure thought I changed it back to a NO MASK prior saving the table. Now, table is open and I am unable to do anything with that field, even deleting the content, to satisfy the mask problem. Hands seems to be tied in 'catch 22'; I would have thought (!!??) that the table would not have saved properly if the input mask did not match the field contents. I'm stuck! Thank you so much. =?Utf-8?B?b2ZmZXJvY2t...

Inserted rows, now need Counta function
Hi I've got a data dump. I've figured out how to insert a blank row after a change in name in column A and insert "Total" - so... bill.... bill.... bill Total - bob.... bob.... bob.... bob Total - What I need in column C next to total is to insert the COUNTA function for each person. Any ideas? cheers You say your code inserts a blank row after a change in name and inserts "Total", but your example doesn't show this. Can you post the code you are using to do your "insert"? It will probably be easier to handle the ...

Moving Exchange logs and database
I am attempting to move the log and database files for Exchange 2003. I have followed all the steps listed here: http://support.microsoft.com/?kbid=821915 However, the log files are still being written to the old log folder. Are there any subsequent steps that need to be followed? Is it just a matter of waiting? Thank you for your help, Nick Are you sure you're looking at the right logs? Did you make the change for all storage groups? -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" <nicenick@gmail.com> wrote in message news:1158769...

Excel Add-In for SQL Server Analysis Services Error -- Need Help
I have the add-in installed and it was working. However, lately I keep getting an error stating: Excel Add-in for Analysis Services has not been initialized I have uninstalled and reinstalled but no luck. Anyone help out here? Alan ...

need to get rid error message
Last month I tried to install Symantec's Winfax software on my XP Pro machine. For whatever reason, it kept crashing during the installation, and I couldn't figure out a way around it. Now whenever I first start Word or Excel, I get an error message that says: Compile error in hidden module: WinFaxPrintAndMerge I can click OK and it goes away, but what do I need to do to make it so that message doesn't appear again? In other words, how do I fix whatever it is that it thinks is wrong? Any help you can provide would be much appreciated. Thanks. By the way, I'm usin...

Dual Value Axes advice needed please.
Hi all, need some help with what should be a simple chart. Using Excel 2003, I have a worksheet with a rolling year of data, months on row 1, series titles in A2:A4, values in B2:M4 and Annual Total in Column N (data in N2:N4). As the Annual Total is 12 times the average I want the Total to be assigned to a second Value Axis on the right from the default Value Axis on the left. I have no problem setting a complete Series to a second value Axis, but I want to set a category to the second axis. How can I do that? Thanks. -- Spence To add the secondary category axis, go to Chart menu...

I need the items to have grades
In any business, there are items of different quality, different origin, different level of concentration etc. For example, Tea powder of 3 qualities: High, Medium, low or Tea made in different countries: India, Malasia, China. In the above cases, the item code has to be same because it is basically the same item, but there should be a way to identify them separately. The best way I have seen, to manage this requirement is to have a 'Grade' Column with the Item code. There should be a Grade Master in the Cards Menu of Inventory Module, where the user can create the Grade Type...

I need a rectangular label set for a DVD case.
not the square ones... but one that's rectangular, approx 7.25 x 5.25 inches. Thanks!! debbieT debbie@truesoaps.com ...

Downloading in Outlook 2003 creates a file "needs list" instead of d/l file
When I have an email with attachment, and I go to download it, it downloads it and sometimes renames it to "needs list". It is the same size as the original file, however it cant be opened by any known program. In this case, it should be a .dwg autocad file, but it has happened a couple of times. the name in the saveas box is correct, you download it, and go to that directory and what you were expecting to be there isnt, instead needs list is there. Anyone here of this before? I searched Google, and the newsgroups and the MShelp files, and found nothing. Any ideas woul...

Y scale for P. ie when P$= 2, QD=10 & QS=3 P needs to be Y scale
I am trying to create a graph the compares Quantity Demanded and Supplied in relation to a particular price to find the equilibrium (intersection). The problem is, I have no idea how to make one row the Y scale in my graph. and the other two (QD and QS) the two line graphs which should intersect. For example at a price $0, the QD is 11 units and the QS is 0. At a price $ 4 the QD is 9 units and the QS is 3 units. So as price increases/decreases, demand and supply changes! Hi, Maybe Tushar Mehta's page can help you out. http://tushar-mehta.com/excel/charts/supply_and_demand/ Cheers ...

Domain name not always needed to log into OWA?
This is something I've wondered about for a while. Why is it that only some of our new Exchange Server installs insist on the OWA login <up to 13 chars. of domain name>\<user name> while others have no trouble with only <user name>? "Julie" <julie@somewhere.org> wrote: >This is something I've wondered about for a while. > >Why is it that only some of our new Exchange Server installs insist on >the OWA login <up to 13 chars. of domain name>\<user name> while >others have no trouble with only <user name>? Probably b...

can send but can receive- event log shows loop within Exchange
Hi all, When I try to send email to my exchange 2003 server from an external email account I never get the email. I get the following error message in my event log. ------------------------ A non-delivery report with a status code of 5.3.5 was generated for recipient rfc822;adrown@[192.168.2.249] (Message-ID <200411090532.iA95WemV015823@gatekeeper.com>). Causes: A looping condition was detected. (The server is configured to route mail back to itself). If you have multiple SMTP Virtual Servers configured on your Exchange server, make sure they are defined by a unique incoming port and t...

I need to do a triptic
En que programa puedo hacer un triptico/ I need to do a triptico http://office.microsoft.com/es-es/templates/results.aspx?qu=triptico&av=TPL000 -- Mary Sauer http://msauer.mvps.org/ "frank" <frank@discussions.microsoft.com> wrote in message news:334CFB3C-BFC6-4792-A129-DA25C8BC4354@microsoft.com... > En que programa puedo hacer un triptico/ I need to do a triptico ...

Protection Options, Do I need them all?
I have choices of All Shares/All Volumes/Hyper-V and System Protection If I use System Protection (Bare Metal/System State) do I need to backup the volume and shares? Yes, BMR is for the operating system only. It doesn't back up any user data. System State is the associated registry/Active Directory data. Daniel "TechAnalyst" wrote: > I have choices of All Shares/All Volumes/Hyper-V and System Protection > > If I use System Protection (Bare Metal/System State) do I need to backup the > volume and shares? is it better to use native ntbackup tha...

Help...Need to modify data within a column in a .csv file
I have been given a .csv file of several thousand rows, and I need to change column A of each. The brief cutout below shows column A of the first two rows. (I need to skip the first row, which is the headers.) Kinda clumsy to have such a wide column, but the application this file is to be fed after I change the contents requires all the data separated by double-quotes to be in the one column. Cell A2: 09/12/0509/25/05"1417385"121"307"111-11-1111"09/30/05"LASTNAME1 Cell A3: 09/12/0509/25/05"1417386"101"900000005"222-22-2222"09/30/05"...

Need help with frozen outlook
There's a Daemon failure message that's sitting in my mailbox, as the first message, and it causes outlook to get hung up. Every time I start outlook, it opens, then freezes, and I have to go to the task manager to shut it down. I've tried uninstalling, reinstalling and repairing, but the old mail messages keep populating in my inbox when I reinstall the program. I've deleted every trace of outlook from the registry, and tried reinstalling and connecting while not connected to the internet. How can I remove my messages?? ...

Categorizing multiple transactions
I downloaded my transactions and of course some had blank categories. I want to be able to somehow categorize the payee so I don't have to modify hundereds manually (there all the same category) and I also want these to be automatically recognized when new transactions of the same are downloaded. ...

I need your help in transferring XP SP2 unto Flash USB Drive
I have an updated desktop that has Windows 7. I still have my Windows XP SP2 CD. I also inherited an HP netbook that had XP SP2 and wanted to know if it is possible to take my Windows XP SP2 CD and place it into desktop without it trying to reformat it and then to just copy it unto a Flash USB Drive. From there, I can then reinstall Windows XP SP2 unto netbook. Can that be done? Why don't you just use the hidden Recovery partition (not to be confused with System Restore) to reinstall WinXP on the netbook? Speaking of which, why do you need to reinstall WinXP on t...

Manufacturing Scrap Transactions
Version 10, seems to be an issue with the Scrap Transaction Type of the component transaction entry window. 1. Picklist calls for 6 of component XYZ 2. Component Transaction entry is used to kit the 6 components 3. Later the 6 issued are scrapped using the Scrap transaction type 4. Picklist is increased by 6 and 6 more are issued to the MO 5. Now I want to Scrap the 2nd 6, and when I go to the Scrap transaction type, there is none available in WIP to Scrap. 6 The information button for that component shows 12 issued, 6 scrapped but there is none to scrap now... Is this...