database set to simple but transaction log still going 1

[richey1]

I have a database set-up whereby each day the data is deleted and new data is appended from a text file. The transaction log was growing out of control so I ran a DBCC Shrink (it went back to Space allocated 1mb) and set the Database to be simple not full. the space allocated is back up to 177mb and the ctbalances.mdf file is 55mb (which is ok) yet the ldf file is 176mb (which is better than before but still high) have I done this right ?
I have another database (which is a 3rd party software company database) which has lots and lots of tables but i can't set to simple - i will need to keep 2 weeks worth of backups and potentially restore to varying points in time.
What is my best approach re. this one - do i need to enable the transaction log backup , say once weekly ?
Finally, is it always good practice to have your mdf file in the data directory and your ldf file in the log directory ?

thanks very much
regards
richard

 

[JayKusch]

SQL Server still writes out some information to the transaction log because each transaction still needs to be committed/rolledback as a set. But once the transaction is completed, the log information is marked ok to overwrite, so the log won't grow "as much".


Thanks

J. Kusch

 

[mrdenny]

The mdf and ldf files can be in any folder that you want them to be in. There is no write or wrong place for them to be. I usually place them in the {x}:/MSSQL/MSSQL/Data folder on what ever drive they need to be on.

The trick to scheduling transaction log backups is to figure out how much x downtime will cost the company in dollars. If 24 hours of downtime will cost the company $1,000,000 in lost work, then you need to backup the transaction log more often, unless this amount of loss is allowed. If 1 hour of downtime will cost the company $41,666 in lost work, and this amount of loss is ok then backup every hour, if it's not backup more often.

Transaction log backups and a quick database backup that you can do without much effect on the production system. That said, they should be done much more than once per week.

As Jay said the transacation log of a simple database will grow, just not as fast as or as large as a database set to full recovery. A large transaction log is not a problem. It just seams that you are moving large amounts of data around the database.

Many people get concerned about large log files when they shouldn't. These logs need to be backed up and saved in case of a server failure so that the database can be restored.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[richey1]

thanks guys

re. the 3rd party software database
I have set-up the transaction log backup to run 4 days a week with remove files older than set to 7 days.
I notice the first .trn was 296mb but the 2nd one is only 11.4 mb - does that sound ok as long as the .trn files stay around 11.4 mb from now on I'll be ok re. space

thanks
richard

 

[mrdenny]

That's probably about right. The first one will be big because it has all the transactions in the log to backup. The other logs will be so small because they only have the new transactions to backup.

Denny

--Anything is possible. All it takes is a little research. (Me)