SQL Server clears logs and all table data

In the query analyzer, perform the following three steps in sequence, where databasename is your database file name
1. Clear logs: dump transaction databasename WITH NO_LOG

2. truncate transaction logs: backup log databasename WITH NO_LOG

3. shrink the database: dbcc shrinkdatabase (databasename)

--/////////////////////////////////////// ///////////////////////////

SQL Server Log clearing method

One method: Clear logs.
1. Open the query analyzer and enter the command dump transaction database name WITH NO_LOG
2. open the Enterprise Manager and right-click the database you want to compress -- all tasks -- shrink database -- shrink file -- Select log file -- select to shrink to XXM In the shrink mode, here we will provide a minimum number of M that can be reduced. Enter this number directly and click OK.

Method 2:

Clear logs:

------------------------------------------
Backup log library name WITH NO_LOG

Dbcc shrinkfile ('Log filename ', New Size numeric type such as 1)

The log file name is as follows:

Select name from sysfiles
For example:
Mastlog

---------------------------------------------
Backup log DATABASENAME
With truncate_only
Dbcc shrinkdatabase (DATABASENAME, SIZE)
If you have whole back up every day, you can set a job,
It is cleared every three days or one week.
In this way, logs will not grow up.

-------------------------------------
1: Delete LOG
1: Database Separation
2: delete LOG files
3: attach a database
This method generates a new LOG with a size of more than 500 kb.
Then set the database to automatically contract
2: Clear logs
Dump transaction database name WITH NO_LOG

Again:
Enterprise Manager -- Right-click the database you want to compress -- all tasks -- contract database -- contract file -- Select log file -- select to contract to XXM in the contract mode, here we will provide a minimum number of M that can be reduced. Enter this number directly and click OK.

Method 3:
Step 1:
Backup log database_name with no_log
Or backup log database_name with truncate_only -- no_log and truncate_only are synonymous here. You can execute either of them.
Step 2:
1. compress all data and log files of a specific database and execute dbcc shrinkdatabase (database_name, [, target_percent]) -- database_name is the name of the database to be compress; target_percent indicates the percentage of available space in the database file after the database is shrunk.
2. compress data or log files in a specific database at a time, and run dbcc shrinkfile (file_id, [, target_size]) -- file_id is the ID of the file to be compress, to obtain the file ID, use the FILE_ID function or search for sysfiles in the current database. target_size indicates the size of the desired file in megabytes (expressed in integers ). If not specified, dbcc shrinkfile reduces the file size to the default file size.

Either dbcc can contain the notruncate or truncateonly parameter. For more information, see help.

Method 4:
(This method is generally successful in the sqlserver2000 environment, but not necessarily in sqlserver7 or below ):
Step 1:
Back up the entire database for emergency purposes.
Step 2:
After the backup is complete, run the following statement in Query Analyzer:
Exec sp_detach_db yourDBName, true -- unload the registration information of this DB in MSSQL
Step 3:
Delete the log file or remove the log file from the directory where the log physical file is located.
Step 4:
Execute the following statement in Query Analyzer:
Exec sp_attach_single_file_db yourDBName, 'd:/mssql7/data/yourDBName_data.mdf'
-- Register the database as a single file. If yes, MSSQL automatically generates a K log file for the database.

The preceding methods are valid in log clearing.
However, can I prevent SQL server from generating log logs? The above methods seem to be invalid.
I have a case here:
My client's SQL server generates 4-MB log every day and clears it every day, which is inconvenient. Is there a way to avoid log generation?

I analyzed the cause of the customer's log generation and tested it accordingly.
The customer clears the database every day and imports data from the total system to SQL server. I feel that sqlserver does not generate much log during insertion, and the log is generated greatly when the entire database is deleted.
For example:
SELECT * into test_2 from B _bgxx
A total of 45000 records generate dozens of M logs. If
Delete from test_2
More than 80 M logs are generated, which is obviously a problem.

Although it can be changed:
Truncate table test_2

 

Recently, I found that the database is too large and has insufficient space. Therefore, I plan to clear all the data in the database, but there are a lot of tables, one by one, which is really troublesome, therefore, we want to use SQL statements to clear all data at a time. three methods are found for clearing. the database used is ms SQL SERVER.
1. Search all table names and construct an SQL statement.

Declare @ trun_name varchar (8000)
Set @ trun_name =''
Select @ trun_name = @ trun_name + 'truncate table' + [name] + ''from sysobjects where xtype = 'U' and status> 0
Exec (@ trun_name)

This method is applicable when there are not many tables. Otherwise, the number of tables exceeds the length of the string and cannot be completely cleared.
2. Use a cursor to clear all tables

Declare @ trun_name varchar (50)
Declare name_cursor cursor
Select 'truncate table' + name from sysobjects where xtype = 'U' and status> 0
Open name_cursor
Fetch next from name_cursor into @ trun_name
While @ FETCH_STATUS = 0
Begin
Exec (@ trun_name)
Print 'truncated table' + @ trun_name
Fetch next from name_cursor into @ trun_name
End
Close name_cursor
Deallocate name_cursor

This is self-built and can be called as a stored procedure. It can clear all the table data at a time, and it can also be selected to clear the table.
3. Use Microsoft's undisclosed stored procedures

Exec sp_msforeachtable "truncate table? "

 

This method can clear all tables at a time, but does not apply filter conditions.

 

Recently, I found that the database is too large and has insufficient space. Therefore, I plan to clear all the data in the database, but there are a lot of tables, one by one, which is really troublesome, therefore, we want to use SQL statements to clear all data at a time. three methods are found for clearing. the database used is ms SQL SERVER.
1. Search all table names and construct an SQL statement.

Declare @ trun_name varchar (8000)
Set @ trun_name =''
Select @ trun_name = @ trun_name + 'truncate table' + [name] + ''from sysobjects where xtype = 'U' and status> 0
Exec (@ trun_name)

This method is applicable when there are not many tables. Otherwise, the number of tables exceeds the length of the string and cannot be completely cleared.
2. Use a cursor to clear all tables

Declare @ trun_name varchar (50)
Declare name_cursor cursor
Select 'truncate table' + name from sysobjects where xtype = 'U' and status> 0
Open name_cursor
Fetch next from name_cursor into @ trun_name
While @ FETCH_STATUS = 0
Begin
Exec (@ trun_name)
Print 'truncated table' + @ trun_name
Fetch next from name_cursor into @ trun_name
End
Close name_cursor
Deallocate name_cursor

This is self-built and can be called as a stored procedure. It can clear all the table data at a time, and it can also be selected to clear the table.
3. Use Microsoft's undisclosed stored procedures

Exec sp_msforeachtable "truncate table? "

 

This method can clear all tables at a time, but does not apply filter conditions.