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

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

Chris

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

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

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

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

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

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