Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to delete sqlserver database logs and the recovery methods of databases without logs. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

I. the method of deleting database log files

You have a time when executing SQL, the database reported that the transaction log is full, and then execute the error report. Then obsessed with how to delete the database log, messing around for a long time, now provide two ways to delete log files, I hope it can help you!

Method 1: manual operation 1. Database-> right-click-> Properties-> options-recovery Model-> switch from completion to simple 2. Database-> right-click-> Task-> shrink-File-> switch from completion to simple-> File Type-> Log-> shrink the file to

Method 2: stored procedures instead of manual operations

The copy code is as follows:-- how much the log file shrank to M DECLARE @ DBLogSise AS INT SET @ DBLogSise=0-- query the name of the log file 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 An INNER JOIN sys.master_files AS B ON A. databaserecovery = B.database_id WHERE A.database_id=DB_ID () SET @ strSQL='-- set the database recovery model to simple ALTER DATABASE ['+ @ strDBName+'] SET RECOVERY SIMPLE -- shrink log file DBCC SHRINKFILE (''+ @ strLogName+''','+ CONVERT (VARCHAR (20), @ DBLogSise) +');-- restore database restore mode to full ALTER DATABASE ['+ @ strDBName+'] SET RECOVERY FULL 'exec (@ strSQL)

1. Execute the above stored procedure 2 in the database. Then execute EXEC dbo.usp_p_delDBLog @ DBLogSise = 0 (shrink to how many M) 2. Database recovery method without log files

Today, the client side of the implementation of SQL error, after checking that the client server database disk has been all used up, the log file to the extent of 500GB, later, due to my wrong operation led to the log file (.ldf) was deleted, and then add .MDF file is always said that there is no log file addition is not successful, and then after some trouble finally solved, the following share!

Operation steps

1. Create a new database file with the same name 2. Suspend SQLSetver service 3. The original mdf file, overwrite the new database, delete the new database ldf file 4. Restart the SQLSetver service, and the database you see looks like this and cannot be opened.

5. Execute the following SQL statement to copy the code as follows:-- 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 operation 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 the modified library will be regenerated, and the whole process will be completed.

Or you can use manual attachment.

On the deletion of sqlserver database logs and no log database recovery methods to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.