| Author |
SQL Transaction Log
|
|
| Tony Crossly 2005-07-27, 7:23 am |
| How do I clear down my SQL transaction log?
I have one SQL 2000 database that is 800Mb - The associated log file is over
5Gb and I am unable to clear it down.
Can anyone help me out.
I have run a backup on the database and then tried to using:-
backup log dbname with truncate_only
The command runs successfully but the transaction log is still 5Gb!
When I try and delete the log I am told that that primary file cannot be
removed from the database.
Thanks in advance.
| |
| Uri Dimant 2005-07-27, 7:23 am |
| Tony
Run BACKUP LOG command and then DBCC SHRINKFILE TO reduce physical size of
the file
If you set FULL RECOVERY mode to the database you will have to do BACKUP LOG
on to remove inactive portions (commited transaction) of the file , so it
will allow to the Log file reuse the virutal log file and not to grow
unexpectable
"Tony Crossly" <Tony Crossly@discussions.microsoft.com> wrote in message
news:478145E2-960C-4716-B597- 529BF9E2B0DC@microso
ft.com...
> How do I clear down my SQL transaction log?
>
> I have one SQL 2000 database that is 800Mb - The associated log file is
> over
> 5Gb and I am unable to clear it down.
>
> Can anyone help me out.
>
> I have run a backup on the database and then tried to using:-
> backup log dbname with truncate_only
>
> The command runs successfully but the transaction log is still 5Gb!
>
> When I try and delete the log I am told that that primary file cannot be
> removed from the database.
>
> Thanks in advance.
| |
| Yogish 2005-07-27, 7:23 am |
| Also, never think of deleting transaction log file (FYI, it can be deleted by
turning off the MSSQLSERVER service). Deleting the transaction log file will
leave the database in the suspect mode.
Just carry out the steps mentioned by Uri.
--
- - - - - - - - -
Thanks
Yogish
"Tony Crossly" wrote:
> How do I clear down my SQL transaction log?
>
> I have one SQL 2000 database that is 800Mb - The associated log file is over
> 5Gb and I am unable to clear it down.
>
> Can anyone help me out.
>
> I have run a backup on the database and then tried to using:-
> backup log dbname with truncate_only
>
> The command runs successfully but the transaction log is still 5Gb!
>
> When I try and delete the log I am told that that primary file cannot be
> removed from the database.
>
> Thanks in advance.
| |
| Tony Crossly 2005-07-27, 7:23 am |
| I am new to SQL so I am unsure of the command and could do with some help.
I tried running backup log dbname from the query analyser but it didn't work.
I tried the dbcc shrinkfile dbname and that won't run either.
Could you post the exact command?
Sorry to be a pain.
"Uri Dimant" wrote:
> Tony
> Run BACKUP LOG command and then DBCC SHRINKFILE TO reduce physical size of
> the file
>
> If you set FULL RECOVERY mode to the database you will have to do BACKUP LOG
> on to remove inactive portions (commited transaction) of the file , so it
> will allow to the Log file reuse the virutal log file and not to grow
> unexpectable
>
>
> "Tony Crossly" <Tony Crossly@discussions.microsoft.com> wrote in message
> news:478145E2-960C-4716-B597- 529BF9E2B0DC@microso
ft.com...
>
>
>
| |
| Uri Dimant 2005-07-27, 7:23 am |
| Tony
> I tried running backup log dbname from the query analyser but it didn't
> work.
Have you got an error?
Please refer to the BOL. It has very good description about how to do that.
"Tony Crossly" < TonyCrossly@discussi
ons.microsoft.com> wrote in message
news:AC689949-57AB-405A-A17F- 653F2662F45D@microso
ft.com...[color=darkred]
>I am new to SQL so I am unsure of the command and could do with some help.
>
> I tried running backup log dbname from the query analyser but it didn't
> work.
>
> I tried the dbcc shrinkfile dbname and that won't run either.
>
> Could you post the exact command?
>
> Sorry to be a pain.
>
> "Uri Dimant" wrote:
>
| |
| Andrew Dadmun 2005-07-27, 8:23 pm |
| "Tony Crossly" < TonyCrossly@discussi
ons.microsoft.com> wrote...
>I am new to SQL so I am unsure of the command and could do with some help.
>
> I tried running backup log dbname from the query analyser but it didn't
> work.
>
> I tried the dbcc shrinkfile dbname and that won't run either.
>
> Could you post the exact command?
>
> Sorry to be a pain.
>
First,
BACKUP LOG DBNAME WITH TRUNCATE_ONLY
Then,
DBCC SHRINKFILE (Logfilename,2)
If you get stuck on the second step, look up the syntax in BOL.
Regards,
Andy
| |
| eko_elits@yahoo.com 2005-08-01, 11:23 am |
| if you want to delete it, you have to stop the server first, and then you can delete it, but it's not the answer.
I got the problem like u, but i use the crazy idea. I truncate table when i deleted, because it's the best way to stop log growth
********************
********************
********************
**********
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|
|
|
|