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!
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!