Delete SQL Server database logs and database recovery methods without logs

Time:2021-11-27

1、 Method of deleting database log file

You once reported that the database transaction log was full when executing SQL, and then reported an error. And then struggling with how to delete the database log for a long time? Now we provide two methods to delete the log file. I hope it can help you!

Method 1: manual operation

1. Database – > right click – > properties – > Options – recovery mode – > switch from complete to simple
2. Database – > right click – > Task – > shrink – file – > switch from complete to simple – > file type – > log – > shrink file to

Method 2: stored procedure instead of manual operation

--How many m does the log file shrink to
  DECLARE @DBLogSise AS INT
  SET @DBLogSise=0
  --Query the log file name corresponding to the database
  DECLARE @strDBName AS NVARCHAR(500)  DECLARE @strLogName AS NVARCHAR(500)  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 ['[email protected]+'] SET RECOVERY SIMPLE;
  --Shrink log file
  DBCC SHRINKFILE ('''[email protected]+''' , '+CONVERT(VARCHAR(20),@DBLogSise)+');
  --Restore database restore mode to full
  ALTER DATABASE ['[email protected]+'] 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 m)

2、 Database recovery method without log file

Today, the customer reported an error in SQL. It was found that the Database disk of the client server had been used up, and the log file reached 500GB. Later, due to my wrong operation, the log file (. LDF) was deleted. Later, the. MDF file was always said that there was no log file, and the attachment was unsuccessful. Later, it was finally solved after some twists and turns. Let’s share it!

Operation steps

1. Create a new database file with the same name
2. Pause sqletver service
3. Overwrite the original MDF file with the new database, and delete the LDF file of the new database
4. Restart the sqlserver service. The database you see is like this and cannot be opened

5. Execute the following SQL statement

--1. Set to emergency
Alter database database name set emergency
--2. Set to single user mode
Alter database database name set single_ user
--3. Check and rebuild the log file
DBCC CHECKDB ('Database name ', repair_allow_data_loss)
--4. Step 3. If there is an error prompt, run step 4. If there is no error, skip
DBCC CHECKDB ('Database name ', repair_rebuild)
--5. Restore to multi-user mode
Alter database database name set multi_ user

6. At this point, the log file of Library modification will be regenerated, and the whole process is completed

Alternatively, manual addition can be adopted (refer to @ code track Engineer for this method)

This is the end of this article about deleting SQL Server database logs and database recovery methods without logs. For more information about deleting SQL Server database logs and database recovery methods without logs, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you will support developeppaer in the future!