Delete SQL Server database logs and database recovery methods without logs _mssql

Source: Internet
Author: User
Tags sql error

How to delete a database log file

You used to have it. When executing SQL, the database reports that the transaction log is full and then executes the error. Then tangled in how to delete the database log, for half a day, now provide two kinds of methods to delete log files, I hope to help you!

Method One: manual operation
1. Database-> Right key-> properties-> Option-recovery mode-> from completion switch to simple
2. Database-> Right key-> task-> shrink-file-> switch from completion to simple-> file type-> log-> to shrink files to



Method Two: Stored procedures instead of manual operations

Copy Code code as follows:

--The log file shrinks to how many m
DECLARE @DBLogSise as INT
SET @DBLogSise =0
--Query the name of the log file corresponding to the database
DECLARE @strDBName as NVARCHAR DECLARE @strLogName as NVARCHAR (+) DECLARE @strSQL as VARCHAR (1000)
SELECT
@strLogName =b.name, @strDBName =a.name from Master.sys.databases as a INNER JOIN sys.master_files as B on A . database_id = b.database_id WHERE a.database_id=db_id ()

SET @strSQL = '
--Set the database recovery mode to simple
ALTER DATABASE [' + @strDBName + '] SET RECOVERY simple;
--Shrink log file
DBCC shrinkfile (' + @strLogName + ', ' +convert (VARCHAR), @DBLogSise) + ');
--Restore the database restore mode to complete
ALTER DATABASE [' + @strDBName + '] SET RECOVERY full '
EXEC (@strSQL)

1. Execute the above stored procedure in the database
2. Then execute EXEC dbo.usp_p_deldblog @DBLogSise = 0 (shrink to how many m)

second, the database recovery method without log file

Today, the client side of the SQL error, the view is that the client server database disk has been used up, the log file reached 500GB degree, and later due to my error operation caused the log file (. ldf) was deleted, Later attach. mdf file always said no log file attached unsuccessful, later after some toss finally solved, the following share!

Operation Steps

1. Create a new database file with the same name
2. Suspension of Sqlsetver Services
3. The original MDF file, overwrite the new database, delete the LDF file
4. Restart the Sqlsetver service, then see the database is like this, can not open


5. Execute the following SQL statement
Copy Code code as follows:

--1. Set as a state of emergency
ALTER DATABASE name SET emergency
--2. Set to Single user mode
ALTER DATABASE name set Single_user
--3. Check and rebuild the log file
DBCC CHECKDB (' database name ', Repair_allow_data_loss)
--4. Step 3rd If you have an error prompt, run step 4th, and then skip the error without errors
DBCC CHECKDB (' database name ', Repair_rebuild)
--5. Revert to multiuser mode
ALTER DATABASE name set Multi_user

6. This will regenerate the library log files, the entire process is complete

Or it can be manually attached (this method refers to @ code path Cheng)


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.