Is there a way to be faster?
Is there a way to contract all databases at a time?
The code is as follows: |
Copy code |
Alter database name Set recovery simple Go Dbcc shrinkdatabase (database name) Go Alter database name Set recovery full Go |
At present, there is also a log compression tool, a B/S interface form of operations to compress the database, that is, when selecting a database, you need to re-select a specific library, in addition, when the number of databases is large, some databases are compressed and not sorted automatically. Currently, the compressed database is automatically rolled to the end, the database with the largest log record capacity is always opened in each drop-down list. This is easy to implement. The requirement is that the execution speed is faster when logs are compressed?
How to optimize it? Is there a way to compress all databases at one time?
Achieve one-time compression of all databases through the stored procedure: passed the test in Sqlserver2005
The code is as follows: |
Copy code |
Create procedure shrinkDatabase As Declare @ name nvarchar (2000) Declare getDataBaseCursor cursor Select name from sysdatabases // retrieve all database names Open getDataBaseCursor Fetch next from getDataBaseCursor Into @ name // place the obtained value in a variable While @ fetch_status = 0 // perform compression cyclically based on the value Begin Exec ('alter database' + @ name + 'set recovery simple ') Exec ('dbcc shrinkdatabase ('+ @ name + ')') Exec ('alter database' + @ name + 'set recovery full ') Fetch next from getDataBaseCursor Into @ name end Close getDataBaseCursor // close Deallocate getDataBaseCursor // release Sp_helpdb urltest // compare the database size Exec shrinkDatabase // execute |