If sqlserver is used in windows, if the file size exceeds GB, we cannot delete it directly. I have encountered this problem before in apache logs and have not deleted it yet, if the SQL Server database logs are too large, how can we quickly delete them? Is there a solution? The answer is yes. I will introduce you to the two deletion methods below. Use in simple recovery mode
If sqlserver is used in windows, if the file size exceeds GB, we cannot delete it directly. I have encountered this problem before in apache logs and have not deleted it yet, if the SQL Server database logs are too large, how can we quickly delete them? Is there a solution? The answer is yes. I will introduce you to the two deletion methods below. Use in simple recovery mode
If sqlserver is used in windows, if the file size exceeds GB, we cannot delete it directly. I have encountered this problem before in apache logs and have not deleted it yet, if the SQL Server database logs are too large, how can we quickly delete them? Is there a solution? The answer is yes. I will introduce you to the two deletion methods below.
Is it meaningless to use backup log with NO_log in simple recovery mode?
1. Clear logs
Dump transaction database name WITH NO_LOG
2. truncate transaction logs:
Backup log database name WITH NO_LOG
3. Compress database files (if not compressed, the database files will not be reduced
Enterprise Manager -- Right-click the database you want to compress -- all tasks -- contract database -- contract file
-- Select log file -- select to shrink to XXM in the contraction mode. Here, a minimum number of MB allowed to be shrunk is displayed. Enter this number directly and click OK.
-- Select data file -- select to shrink to XXM in the contraction mode. Here, a minimum number of MB allowed to be shrunk is displayed. Enter this number directly and click OK.
You can also use SQL statements to complete
-- Shrink Database
Dbcc shrinkdatabase (customer profile)
-- Contract the specified data file. 1 is the file number. You can use this statement to query: select * from sysfiles
Dbcc shrinkfile (1)
The Code is as follows: |
|
Backup, log, Mode -- Step 1: Clear logs Dump transaction [YZGA] WITH NO_LOG -- Step 2: truncate transaction logs Backup log [YZGA] WITH NO_LOG -- Step 3: shrink the database Dbcc shrinkdatabase ([YZGA]) |
Note:
Although you can use this option to manually truncate transaction logs, we strongly recommend that you do not do this because it will disconnect the log chain. Media fault protection cannot be provided for databases until the next full backup or full differential backup. Log truncation is performed manually only in special cases and data backup is created immediately.
Method 2
1. Go to the Enterprise Manager and select a database, such as it, for all tasks-> detach a database.
2. If the database is being connected and used, Click Clear to select OK.
3. Go to the database file storage directory and delete the it_log.LDF file. You cannot copy it to another place first.
4. Go to the Enterprise Manager and choose database> all tasks> attach database.
5. Select it_Data.MDF. At this time, you will see that the log file is a Red Cross. It doesn't matter if you are sure. (What you see in this area is not a Red Cross, but not found. If you do not find the attached file, an error is reported. You cannot find the physical file. If you solve the problem, simply delete the file that you did not find and leave only mdf, then click Add)
6. At this time, the database will prompt you whether the database has no logs to create a new one. OK.
7. If the new log file becomes larger in the future, continue this step.