Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
noquintic
Starting Member
10 Posts |
 Posted - 2007-07-18 : 14:42:40
|
| version:SQL Server 2000. db size: 25.6GBtrans log file: 32GBso far: I have read the Forum FAQ on "clearing the transaction file" and some DBCC SHRINK in Books Online.I am trying to set up maintenance plan or backup plan. File sizes are getting very large and performance is horrible since we have had no regular maintainence in the past (No DBA in house).1. What is the best way to identify the location of the transaction log file associated with a particular database? When I right-click on the database name in Enterprise Manager and select properties, I can see one location for the transaction log. However, If I right-click on the database > All Tasks > Shrink Database > click Files and select Temp_Table_Log, I get a different location.2. We delete then repopulate about 105000 records in one particular table each day. In addition, we do the same with about a hundred rows in several other tables daily.-- Should I be doing Full Backups nightly?-- I have the option set to "AutoShrink" on the db. Will this truncate and shrink the transaction log as well as shrink the db when I do a full backup?Thanks,Più pranzo libero! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-18 : 16:06:19
|
| Do you require point in time recovery or could you live with restoring to the last full backup in the case of an issue? If you don't require point in time recovery, then change the recovery model to SIMPLE.Yes you should be performing nightly full backups. We do that plus backup our transaction logs every 15 minutes, due to the criticality of the data. Your mileage may vary. You should never shrink the database or its files unless you know you will not need that space in the near future. You receive a huge penalty if you shrink the database and the database needs to expand the files. So you might as well leave them at their current size and just add more disk space. Don't worry about the database size, focus on performance issues instead.What you see in the shrink should just be the logical name and not the physical name. What you see in the database properties will be both. Make sure you check the fields to know which is which.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
|
|
|
transaction log file location