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

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)