Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #11 (permalink)  
Old May 25th, 2004, 06:57 PM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

You eventually received messages that is is full and you must truncate the logs or backup your database

  #12 (permalink)  
Old May 26th, 2004, 10:37 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I thought I was not to truncate it manually. What should I do then?

Chris
  #13 (permalink)  
Old May 26th, 2004, 11:49 AM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

I think that is now your choice to decided which route you want to take. Given all the information provide make a choice and see what is best for your environment works for you

1.Change database mode to simply
2.Schedule backup a of your database
3.Schedule a dbcc shrinkfile of your database log file


  #14 (permalink)  
Old May 26th, 2004, 12:24 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default

With the options listed, I do not think I can use the shrink file. everything I see on it says it just clears out free space and basically compacts the data. It does not appear to actually clear out old data.

I am not sure what backing up my database will do as it is not the database I am worried about. It is the Log files I need to remove. I do back it up daily with Veritas Backup Exec and that appears to have no affect on the LOG file.

If I change it to SIMPLY, will I not then lose some redundancy against data loss?

I guess I wish this was easier. Can I just delete the log file? Does not SQL server actually commit all transactions to the database, or do I have transactions just sitting in the file for who knows what to actually commit them? I wish SQL server would automatically maintain them. Maybe I should just make it SIMPLY cause it is wasted space anyway, but then I would not be able to do anything beyond my last backup.

Chris
  #15 (permalink)  
Old May 26th, 2004, 12:31 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

You cannot DELETE the log just like that. The log will always be in use when the SQL server is running and the DB is functional. That will never solve your purpose.

_________________________
-Vijay G
Strive for Perfection
  #16 (permalink)  
Old May 26th, 2004, 06:56 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

You can try this out. I did that a week back. But be informed that if the database can be taken offline for a short while, only then you can go for this solution, because it uses dettach and attach database methods for immediate effect in log file size.

Steps for Shrinking LOG space, if you run out of HDD space and want that to get effected immediately.

1) 'Removes the inactive part of the LOG without making a backup copy of it and truncates the log
    BACKUP LOG <DB_NAME> WITH TRUNCATE_ONLY

2) 'Shrinks the size of the specified LOG file for the related database.
    DBCC SHRINKFILE (<DB_NAME>_Log, 100) only in multiples of 100 MBs

3) 'To bring DB offline and get the shrunk LogFile size effected physically in the HDD
    sp_detach_db <DB_NAME>, true

4) 'To bring the DB online.
    sp_attach_db @dbname = N'<DB_NAME>',
            @filename1 = N'C:\MSSQL\Data\<DB_NAME>_Data.MDF',
            @filename2 = N'C:\MSSQL\Data\<DB_NAME>_Log.LDF'

NOTE: Replace all the <DB_NAME> with your database name.

Hope that helps.

Cheers!

_________________________
-Vijay G
Strive for Perfection
  #17 (permalink)  
Old September 30th, 2004, 08:10 AM
Friend of Wrox
 
Join Date: Sep 2004
Location: New Delhi, Dekhi, India.
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to Anantsharma Send a message via Yahoo to Anantsharma
Default

Seems very much bothered about it.

When u use Enterprise manager to backup the Transaction Log, be sure to check the check box "Remove Inactive Entries from transaction log". U can find this check box in option tab of backup wizard.

U can also use BACKUP LOG <LOGFILE NAME> TRUNCATE ONLY

The best is to shrink Data base. Its very effective. Use Enterprice manager to shrink database( Rclick on database >> All Tasls >> Shring database). Specify to leave 10-15 percent space after shrinking database.

After doing this Ur maintanance plan will handle further (hourly log backup is enough for non production environment).

B. Anant
  #18 (permalink)  
Old September 30th, 2004, 09:26 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

A database can never shrink to a size less than the initial size. if your database initial size was 1 GB, but you only have 1 MB of data, every time your database shrinks it will return to 1 GB. So it will not release the space.

Transaction logs:
A server based DBMS such as SQL Server, Sybase, Oracle, does not write the data directly into the database as it gets changes. It writes the changes to the log file. This increases the speed of the database since it does not have to keep writting and reading from the disk. Instead it reads from the log, which is more accessible. The data is written to the database later. The RDBMS takes care of that.

YOU CAN NOT DELETE THE TRANSACTION LOG. If you do this you will ruin your database. To shrink that log or even the database, you need to detach and re-attach the database when users are not logged into the DB. If your database w2ill only be 1 MB of size, set the databasse initial size to 5 MB and let it grow in increments of 1 or 2 MB and set a max limit so that it will never eat up the disk.

If you do not set a limit for the database size or the log size, the database could eventually outgrow the disk size and then you will lose data. If you have a max allowed size for the database once the database reaches that size the database will become read only and will not allow you to make further changes, but you will not lose any data that already existed in the database or log file.

Read up books on line on this. It is the best source ot info on SQL Server.

If you create a backup uf your database, this backup needs to also be picked up by some automated process and stored in another disk or tape so that when your database server crashes (it is just a matter of time) then you will have a backup available.





Sal




Similar Threads
Thread Thread Starter Forum Replies Last Post
capture error logs jemacc SQL Server 2000 1 January 10th, 2007 08:17 PM
About Web logs anshul Pro PHP 1 January 5th, 2007 04:54 AM
Keeping transaction logs down to a reasonable size afroblanca SQL Server 2000 3 August 23rd, 2005 12:31 PM
Oracle Logs echovue Oracle 1 December 22nd, 2004 10:12 AM
record logs rajanikrishna Pro VB 6 0 April 2nd, 2004 03:37 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.