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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-04 : 08:11:37
|
| Bert writes "What is the role of the log file? If I set the log file to over-write itself can tranactions be rolled back. Is the only way to shrink the log file by detach database, delete log file (mydb.ldf) and finally to re-attach the database. How does the "autoshrink" function work?This is a big pain on most ISP's since they dont allow control of the database size and set it at a fixed size, resulting in a 10 meg database with a 40 meg log file and the database is now full! ArggThanks,Bert" |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-04-04 : 09:05:05
|
| Let's start from the beginning. Your first question is scary because the log file plays a central, critical role in a SQL Server database. You absolutely need to know what it does to be effective with SQL Server. The log file touches many administrative aspects of SQL Server, such as backup/restore.You should start by reading up on the "recovery model" topic in Books OnLine. Follow the links to related articles as well.To answer your questions outright:1) You can't "set" the log file to overwrite itself. SQL Server does that automatically at times it deems appropriate.2) If you delete the log file, you will be in a world of hurt. never delete it! use DBCC SHRINKDB/SHRINKDATABASE.3) The autoshrink feature calls the above DBCC command at intervals determined by SQL Server.For the moment, if you can't execute statements because the transaction log is full, BACKUP the database. failing that, run BACKUP LOG {database name} WITH TRUNCATE_ONLY.setBasedIsTheTruepath<O> |
 |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-04-04 : 23:24:30
|
quote:
2) If you delete the log file, you will be in a world of hurt. never delete it! use DBCC SHRINKDB/SHRINKDATABASE.
Thank you SetBased..I have been trying to impress this very idea from the day I joined SQLteam, but people continue to suggest that as a proper way to shrink the log.http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11557-Chad |
|
|
|
|
|
|
Log File