How to delete the log file
I want to delete the log file of a database but it says it cannot delete the mail transaction log.
From the SQL Analyzer y get a message that says that the log file is not available. It is 500GB in size.
How can I delete it or truncate it?
From the SQL Analyzer y get a message that says that the log file is not available. It is 500GB in size.
How can I delete it or truncate it?
ASKER
Thanks
Now I have this process and the log file starts growing and growing (takes about 28 hours, 500 million records) until there is no more disk space for the log file. Is there a way to process without the log file. I undertand de recovery issues. However it is importante to know
Now I have this process and the log file starts growing and growing (takes about 28 hours, 500 million records) until there is no more disk space for the log file. Is there a way to process without the log file. I undertand de recovery issues. However it is importante to know
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oh my gosh, close some of your open questions!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"Make a backup of your DB. Dettach the database. Delete the log file. Attach the database, this will recreate a new log file."
You should never rely on this method...SQL Server will NOT always recreate the LDF file.....
You should never rely on this method...SQL Server will NOT always recreate the LDF file.....
Hello,
robrodp glad you finally solved your problem. Thanks for the points.
arbert:
If a database comprises only a single data file and a single transaction log file, the database can be attached to an instance of SQL Server without using the transaction log file, provided the database was cleanly shut down with no users and no open transactions. When the data file is attached, SQL Server creates a new transaction log file automatically.
The database must have been successfully detached from SQL Server using the sp_detach_db system stored procedure.
Single-file databases are useful .All the data is stored in a single file; attaching the single file to SQL Server automatically re-creates a transaction log so that the database can be used.
Jose
robrodp glad you finally solved your problem. Thanks for the points.
arbert:
If a database comprises only a single data file and a single transaction log file, the database can be attached to an instance of SQL Server without using the transaction log file, provided the database was cleanly shut down with no users and no open transactions. When the data file is attached, SQL Server creates a new transaction log file automatically.
The database must have been successfully detached from SQL Server using the sp_detach_db system stored procedure.
Single-file databases are useful .All the data is stored in a single file; attaching the single file to SQL Server automatically re-creates a transaction log so that the database can be used.
Jose
"If a database comprises only a single data file and a single transaction log file, the database can be attached to an instance of SQL Server without using the transaction log file, provided the database was cleanly shut down with no users and no open transactions. When the data file is attached, SQL Server creates a new transaction log file automatically. "
Yes in the perfect world--this isn't a good idea if you value your data....Look at all the questions on the site in which it DIDN'T recreate the log....Experience teaches....
Yes in the perfect world--this isn't a good idea if you value your data....Look at all the questions on the site in which it DIDN'T recreate the log....Experience teaches....
Well, in this case it worked !
Thanks
jose
Thanks
jose
Lucky for you
backup log YOURDATABASENAMEHERE with truncate_only
go
DBCC SHRINKFILE(YOURDATABASENAM
Brett