Newbie asks how to back up or reduce transaction log

[scouser]

Hi

I am quite new to SQL, using all the online help seems to give me theory but not a simple answer to a simple question!

How do I back up the transaction log as this is stopping me from doing any work on SQL! What is the name of the transaction log, is it visible? Where can I find it?
Examples need the name of the transaction log file name, but I don't know....

Thanks for your help

Shirley

 

[tlbroadbent]

There are four FAQs about this in the FAQ area of this forum. In particular, I recommend faq183-1534. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[dzdncnfsd]

Thanks,Terry.
I read some of the info on the links you provided and I think detach, attach will work for me. After I detach the database, I want to delete the log file, but since the the database name no longer appears in the database list, how do I delete the log file? Do I delete the physical file from Windows Explorer, then reattach the database?
Thanks,
Gladys
Gladys Clemmer
gladys.clemmer@goldkist.com

 

[tlbroadbent]

You can delete the physical file. Open the database properties and look for the Transaction Log tab. Click on the tab to see the physical file name.

If you detach th DB and then run sp_attach_single_file_db, SQL Will overwrite the log file.

Syntax: sp_attach_single_file_db
sp_attach_single_file_db
[ @dbname = ] 'dbname',
[ @physname = ] 'physical_name'

Example:
sp_attach_single_file_db
'mydatabase',
'E:\mssql\data\mydatabase.mdf' Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[scouser]

thanks for your help

 

[tlbroadbent]

Correction: I experimented with sp_attach_single_file_db and found that it doesn't overwrite the file. It will reuse the file. So the file size isn't reduced. You could shrink the file after reattaching but deleting the physical file before attaching the single file would be better. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[dzdncnfsd]

Thanks, Terry. Turns out I cannot do anything anyway because of two active processes that are using the database. I can't kill them, I can't force them to process, I can't detach the database, can't shrink the log file size, etc. Do you have any ideas on how to get rid of them? One is in rollback status, the other is awaiting command.
Thanks for any help you can offer.
Gladys
Gladys Clemmer
gladys.clemmer@goldkist.com

 

[tlbroadbent]

The only remaining alternative is to stop and restart SQL Server. Sometimes that doesn't resolve these kinds of problems so you may need to reboot the server. It is unfortunate, but true, that sometimes things get so hosed that you just have to clear SQL out of memory and restart it. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[dzdncnfsd]

Thanks, I was afraid of that because it is on a very busy server, but at least I can just resort to that and stop wasting time looking for an alternative.
Thanks,
Gladys
Gladys Clemmer
gladys.clemmer@goldkist.com