| Author |
Topic |
|
admin001
Posting Yak Master
166 Posts |
 Posted - 2003-06-16 : 23:25:55
|
| Hi ,I have two huge databases with approx 135 GB and 72 GB size . But one major concern I just noticed on this SQL Server is the log file size which is 130 GB and 59 GB respectively for both the databases . There is no backup configured for these databases so that i cannot truncate the log file size . The server is SQL 2000 with SP2 . Is there any way to truncate the log file size ( ldf file ) and free up some space ? Actually I am facing problem with space availiability on my hard drive . I cannot configure a backup as there is no space left on the disk . If it is possible to truncate then typically how much time will it take for the truncate process . Need some help badly on this .Thanks |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-06-16 : 23:45:37
|
| backup log YourDB with NO_LOGgothen do a dbcc shrinkfile.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
 |
|
|
admin001
Posting Yak Master
166 Posts |
Posted - 2003-06-17 : 00:08:25
|
| Hey Chad ,Thanks . Will the backup log take space on my hard disk ? . I believe the NO_LOG option sets a simple recovery model rather than full recovery . Instead can i use truncate only option . Can you suggest on this ?Thanks once again. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-06-17 : 01:24:52
|
| It won't take up disk space. NO_LOG and TRUNCATE_ONLY are equivalent. Neither changes your recovery model.Remeber, all previous log backups will be invalidated after you run this. (However, I suspect you don't have any Log backups based on the size of your logs.)You should do something about what got you into this situation. Either change your recovery model to simple, or do more frequent log backups. -Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
admin001
Posting Yak Master
166 Posts |
Posted - 2003-06-17 : 02:24:21
|
| Hi Chad ,I have scheduled to run the script at midnight where there will be no load on the server . The command isbackup log dbname with truncate_only . I just wanted to know whether this job will have any impact on the data and that it will be safe if I leave it run overnight . Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-17 : 12:22:28
|
| You really don't need to run this during after hours. Since you have full recovery model option set, you need to scheduled backups of your transaction logs after you have reclaimed the disk space back.Tara |
|
|
|
admin001
Posting Yak Master
166 Posts |
Posted - 2003-06-17 : 21:25:44
|
| Hi ,Thanks for all your help . I did a slight change in my backup log command . I changed the option to backup log mydbname with no_log ( instead of truncate_only ). with db recovery model set to full . Actually , this job i had scheduled did run successfully , but this did not free up the space on my drive . The LDF file size still shows the same size of around 130 GB , but if I go to the taskpad and view the database details , it shows that the space is free . But this somehow has not got reflected on the total drive capacity . Is this space permanently allocated to the database , but it should not be . I am confused !!!!! Do i have to run the job with truncate option now and run shrink the file manually . ?Please help .... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-06-17 : 21:32:47
|
| Truncating the log does not make the log file smaller. All it does is clear committed transactions so that the log won't need to expand as new transactions are done. To make the file smaller, yes, you need to run shrinkfile, either manually or through a scheduled job. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-06-17 : 23:42:46
|
quote:
Truncating the log does not make the log file smaller. All it does is clear committed transactions so that the log won't need to expand as new transactions are done. To make the file smaller, yes, you need to run shrinkfile, either manually or through a scheduled job.
As I said in the second post  -Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
admin001
Posting Yak Master
166 Posts |
Posted - 2003-06-19 : 22:13:56
|
| Hello ,Thanks for all your support and help . Finally it worked after backing up the log and shrinking the log file. Now I am pretty much comfortable with the disk space . I have scheduled backups also for the databases . I had one question related to this , maybe its superficial , but need to know . Whatever data to be written to the database first writes it to a transaction log before inserting it to the data file . But this happens only after commit i.e only if the transactions are committed , i guess . My question is how are the transactions committed and who decides that transactions should be committed and should be inserted to the database . Does database checkpointing takes care of this and till what time the data will be there in the trans. log before it is declared as non-commmitted . I was just wondering how does it all work .........Thanks once again for all your precious help . |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-06-20 : 00:06:33
|
quote:
I have scheduled backups also for the databases
I hope you mean log backups. Database backups won't help.Your code will decide when a transaction is commited. SQL Server internally determines when it needs to write data to the datafile. See "Checkpoints and the Active Portion of the Log" in BOL. The recovery interval helps determine the max time it should take to recover, and the interval between checkpoints will be based on this value.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
|
Truncate Log file.