CN106649481A - A method and system of log optimization for SQL Server database - Google Patents
A method and system of log optimization for SQL Server database Download PDFInfo
- Publication number
- CN106649481A CN106649481A CN201610872612.9A CN201610872612A CN106649481A CN 106649481 A CN106649481 A CN 106649481A CN 201610872612 A CN201610872612 A CN 201610872612A CN 106649481 A CN106649481 A CN 106649481A
- Authority
- CN
- China
- Prior art keywords
- target database
- data base
- raw data
- database
- daily record
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
- G06F16/215—Improving data quality; Data cleansing, e.g. de-duplication, removing invalid entries or correcting typographical errors
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/23—Updating
- G06F16/2365—Ensuring data consistency and integrity
Abstract
The invention discloses a log optimization method and system for a SQL Server database, comprising: a target database is created, and the original database and the target database are stopped running. The log file of the target database is deleted, and the mdf file of the target database is replaced into the mdf file of the original database. When target database is started, the physical and logical consistency of the target database is checked, and the original database with the target database needs to be replaced if there is no consistency problem with the target database. In the present embodiment, by creating a target database that is consistent with the original database type, the target database is covered by the original database mdf file, after the success of the test, and the original database is replaced by the target database. The target database log file greatly more reduced than the original database in the log file, reducing the disk invalid use, so that the use of disk space is more reasonable.
Description
Technical field
The present invention relates to SQL Server database technical fields, more particularly, it relates to a kind of SQL Server data
The daily record optimization method in storehouse and system.
Background technology
SQL Server databases are widely used in various application datas and process, but due to SQL Sever database journals
Increase too fast, cause to take disk space in a large number, invalid use is caused to disk space.This problem most common way is solved at present
Be by historical data deletion reducing daily record, but this mode, it is impossible to meet the demand that user is searched daily record in the future.
Therefore, how to realize reducing SQL Server database journals, instruct enterprise IT operation maintenance personnels to SQL Server
More preferably safeguard, more efficient utilization disk space uses the problem for being those skilled in the art's needs solution.
The content of the invention
It is an object of the invention to provide the daily record optimization method and system of a kind of SQL Server databases, to realize subtracting
Little SQL Server database journals, instruct enterprise IT operation maintenance personnels preferably to safeguard SQL Server, more efficient utilization
Disk space.
For achieving the above object, following technical scheme is embodiments provided：
A kind of daily record optimization method of SQL Server databases, including：
Create target database, raw data base out of service and the target database；The raw data base and institute
The type for stating target database is SQL Server databases；
The log files of the target database are deleted, and the number of targets is replaced with the mdf files of the raw data base
According to the mdf files in storehouse；
Start the target database, the physics and logical consistency of the target database are checked, if described
There is no consistency problem in target database, then the target database is replaced into the raw data base.
Wherein, before the establishment target database, also include：
Judge memory value shared by the log files of the raw data base whether more than predetermined threshold；If so, then perform described
The step of creating target database.
Wherein, the target database is replaced after the raw data base, is also included：
The information that the raw data base is replaced by the target database is generated, and the information is sent
To predetermined terminal.
Wherein, start after the target database, the state of the target database is set to doubt；Check described
Target database is not present after consistency problem, and the state of the target database is set to normally.
Wherein, after the state of the target database being set to doubt, it is allowed to the system to the target database
Catalogue is modified；After the state of the raw data base is set to normally, refuse the system to the target database
Catalogue is modified.
Wherein, the target database is replaced after the raw data base, is also included：
Log files in the raw data base are compressed into storage.
A kind of daily record optimization system of SQL Server databases, including：
Creation module, for creating target database, raw data base out of service and the target database；The original
The type of beginning database and the target database is SQL Server databases；
Removing module, for deleting the log files of the target database；
Replacement module, for replacing the mdf files of the target database with the mdf files of the raw data base；
Module is checked, for starting the target database, the physics and logical consistency of the target database is entered
Row is checked, if the target database does not have consistency problem, the target database is replaced into the raw data base.
Wherein, also include：
Judge module, for judging memory value shared by the log files of the raw data base whether more than predetermined threshold；If
It is then to trigger the creation module.
Wherein, also include：
Generation module, for generating the information that the raw data base is replaced by the target database；
Sending module, for the information to be sent to predetermined terminal.
Wherein, also include：
Compression module, for the log files in the raw data base to be compressed into storage.
By above scheme, a kind of daily record optimization method of SQL Server databases provided in an embodiment of the present invention
And system, including：Create target database, raw data base out of service and the target database；The raw data base
SQL Server databases are with the type of the target database；The log files of the target database are deleted, and with institute
The mdf files for stating raw data base replace the mdf files of the target database；Start the target database, to the mesh
The physics and logical consistency of mark database is checked, if there is no consistency problem in the target database, will be described
Target database replaces the raw data base；
It can be seen that, in the present embodiment, by creating the target database with raw data base type always, and by original
Mdf file coverage goal databases in beginning database, and after success is checked, initial data is replaced by target database
Storehouse, the log files that the log files in target database are compared in raw data base are substantially reduced, and reduce the invalid of disk and use,
Make the use of disk space more reasonable.
Description of the drawings
In order to be illustrated more clearly that the embodiment of the present invention or technical scheme of the prior art, below will be to embodiment or existing
The accompanying drawing to be used needed for having technology description is briefly described, it should be apparent that, drawings in the following description are only this
Some embodiments of invention, for those of ordinary skill in the art, on the premise of not paying creative work, can be with
Other accompanying drawings are obtained according to these accompanying drawings.
Fig. 1 is a kind of daily record optimization method schematic flow sheet of SQL Server databases disclosed in the embodiment of the present invention；
Fig. 2 optimizes system architecture diagram for a kind of daily record of SQL Server databases disclosed in the embodiment of the present invention.
Specific embodiment
Below in conjunction with the accompanying drawing in the embodiment of the present invention, the technical scheme in the embodiment of the present invention is carried out clear, complete
Site preparation is described, it is clear that described embodiment is only a part of embodiment of the invention, rather than the embodiment of whole.It is based on
Embodiment in the present invention, it is every other that those of ordinary skill in the art are obtained under the premise of creative work is not made
Embodiment, belongs to the scope of protection of the invention.
The embodiment of the invention discloses the daily record optimization method and system of a kind of SQL Server databases, to realize reducing
SQL Server database journals, instruct enterprise IT operation maintenance personnels preferably to safeguard SQL Server, more efficient utilization magnetic
Disk space.
Referring to Fig. 1, a kind of daily record optimization method of SQL Server databases provided in an embodiment of the present invention, including：
S101, establishment target database, raw data base out of service and the target database；The raw data base
SQL Server databases are with the type of the target database；
Wherein, before the establishment target database, also include：
Judge memory value shared by the log files of the raw data base whether more than predetermined threshold；If so, then perform described
The step of creating target database.
Specifically, in the present embodiment, the daily record of currently used database can be detected whether more than predetermined threshold；If
It is then to perform this programme automatically.And the new target database created in this programme can be named as prestat,
Establishment is out of service by raw data base and newly-built target database after terminating, and is to perform S102 to prepare.
S102, the log files for deleting the target database, and replace described with the mdf files of the raw data base
The mdf files of target database；
In this programme, need to delete the log files in target database, and the mdf files of raw data base are covered
The mdf files of lid target database, it should be noted that mdf files are the abbreviations of primary datafile, it is MASTER numbers
According to the database file in storehouse.MASTER databases are the core databases of information in all SQL Server databases of storage, will
Mdf files in raw data base are covered in newly-built database, so that the data of the data of new database and former database are protected
Hold consistent.
S103, the startup target database, check the physics and logical consistency of the target database, if
There is no consistency problem in the target database, then the target database is replaced into the raw data base.
Specifically, start after target database, target database can automatically create new log files, specifically include：
dbcc rebuild_log('prestat','d:\Program Files\Microsoft SQL
Server\MSSQL\Data\prestat_log.ldf')
And target database is checked by dbcc sentences:dbcc checkdb('prestat')；If it was found that differing
Sex chromosome mosaicism is caused, then target database is repaired；If there is no consistency problem, target database is replaced into initial data
Storehouse is operated.
In this programme, after starting the target database, the state of the target database is set to doubt；Inspection
Look into the target database not existing after consistency problem, the state of the target database is set to normally, to specifically include：
sp_dboption'prestat','dbo use only','false'。
It should be noted that after in this programme the state of the target database being set to doubt, it is allowed to institute
The system directory for stating target database is modified；After the state of the raw data base is set to normally, refuse to institute
The system directory for stating target database is modified；
Specifically, after the state of target database being set to doubt, need to allow the system mesh to new storehouse Prestat
Record is modified, and to obtain modification authority, is specifically included：
use master
go
sp_configure'allow updates',1
go
reconfigure with override
go
Update sysdatabases set status=-32768where dbid=DB_ID (' prestat')
The embodiment of the invention discloses a kind of specific daily record optimization method, relative to a upper embodiment, the present embodiment pair
Technical scheme has made further instruction and optimization.Specifically：The target database is replaced after the raw data base,
Also include：
Log files in the raw data base are compressed into storage.
Specifically, replaced after raw data base with newly-built database, raw data base can be compressed process, one
Aspect can reduce raw data base and take disk space, and another convenience can play a part of a backup.
And in the present embodiment, the target database is replaced after the raw data base, is also included：Generate institute
The information that raw data base is replaced by the target database is stated, and the information is sent to predetermined terminal, with
Prompting user raw data base is replaced by newly-built data place.
Daily record provided in an embodiment of the present invention optimization system is introduced below, daily record described below optimization system with
Above-described daily record optimization system can be with cross-referenced.
Referring to Fig. 2, a kind of daily record optimization system of SQL Server databases provided in an embodiment of the present invention, including：
Creation module 100, for creating target database, raw data base out of service and the target database；Institute
The type for stating raw data base and the target database is SQL Server databases；
Removing module 200, for deleting the log files of the target database；
Replacement module 300, for replacing the mdf files of the target database with the mdf files of the raw data base；
Check module 400, the physics and logical consistency for starting the target database, to the target database
Checked, if the target database does not have consistency problem, the target database is replaced into the initial data
Storehouse.
Based on above-mentioned technical proposal, this programme also includes：
Judge module, for judging memory value shared by the log files of the raw data base whether more than predetermined threshold；If
It is then to trigger the creation module.
Based on above-mentioned technical proposal, this programme also includes：
Generation module, after being set to normally in the state of the target database, generates the raw data base
The information replaced by the target database；
Sending module, for the information to be sent to predetermined terminal.
Based on above-mentioned technical proposal, this programme also includes：
Compression module, for the log files in the raw data base to be compressed into storage.
A kind of daily record optimization method and system of SQL Server databases provided in an embodiment of the present invention, including：Create
Target database, raw data base out of service and the target database；The raw data base and the target database
Type be SQL Server databases；The log files of the target database are deleted, and with the raw data base
Mdf files replace the mdf files of the target database；Start the target database, the physics to the target database
Checked with logical consistency, if the target database does not have consistency problem, the target database is replaced
The raw data base；
It can be seen that, in the present embodiment, by creating the target database with raw data base type always, and by original
Mdf file coverage goal databases in beginning database, and after success is checked, initial data is replaced by target database
Storehouse, the log files that the log files in target database are compared in raw data base are substantially reduced, and reduce the invalid of disk and use,
Solve the problems, such as that enterprise is excessive to SQL SER VER database journals, as long as attendant is periodically to SQL Server data
Storehouse daily record is compressed and reduces, and can preferably safeguard that SQL Server run, and more rationally effectively uses disk space.
Each embodiment is described by the way of progressive in this specification, and what each embodiment was stressed is and other
The difference of embodiment, between each embodiment identical similar portion mutually referring to.
The foregoing description of the disclosed embodiments, enables professional and technical personnel in the field to realize or using the present invention.
Various modifications to these embodiments will be apparent for those skilled in the art, as defined herein
General Principle can be realized in other embodiments without departing from the spirit or scope of the present invention.Therefore, the present invention
The embodiments shown herein is not intended to be limited to, and is to fit to and principles disclosed herein and features of novelty phase one
The most wide scope for causing.
Claims (10)
1. a kind of daily record optimization method of SQL Server databases, it is characterised in that include：
Create target database, raw data base out of service and the target database；The raw data base and the mesh
The type of mark database is SQL Server databases；
The log files of the target database are deleted, and the target database is replaced with the mdf files of the raw data base
Mdf files；
Start the target database, the physics and logical consistency of the target database are checked, if the target
There is no consistency problem in database, then the target database is replaced into the raw data base.
2. daily record optimization method according to claim 1, it is characterised in that before the establishment target database, also wrap
Include：
Judge memory value shared by the log files of the raw data base whether more than predetermined threshold；If so, the establishment is then performed
The step of target database.
3. daily record optimization method according to claim 2, it is characterised in that the target database is replaced into described original
After database, also include：
Generate the information that the raw data base is replaced by the target database, and the information is sent to pre-
Determine terminal.
4. daily record optimization method according to claim 3, it is characterised in that
After starting the target database, the state of the target database is set to doubt；Check the target data
Storehouse is not present after consistency problem, and the state of the target database is set to normally.
5. daily record optimization method according to claim 4, it is characterised in that
After the state of the target database is set to doubt, it is allowed to which the system directory of the target database is repaiied
Change；After the state of the raw data base is set to normally, refuse to repair the system directory of the target database
Change.
6. the daily record optimization method according to any one in claim 1-5, it is characterised in that by the target database
After replacing the raw data base, also include：
Log files in the raw data base are compressed into storage.
7. the daily record of a kind of SQL Server databases optimizes system, it is characterised in that include：
Creation module, for creating target database, raw data base out of service and the target database；The original number
SQL Server databases are according to the type in storehouse and the target database；
Removing module, for deleting the log files of the target database；
Replacement module, for replacing the mdf files of the target database with the mdf files of the raw data base；
Module is checked, for starting the target database, the physics and logical consistency of the target database is examined
Look into, if the target database does not have consistency problem, the target database is replaced into the raw data base.
8. daily record according to claim 7 optimizes system, it is characterised in that also include：
Judge module, for judging memory value shared by the log files of the raw data base whether more than predetermined threshold；If so,
Then trigger the creation module.
9. daily record according to claim 8 optimizes system, it is characterised in that also include：
Generation module, for generating the information that the raw data base is replaced by the target database；
Sending module, for the information to be sent to predetermined terminal.
10. the daily record according to any one in claim 7-9 optimizes system, it is characterised in that also include：
Compression module, for the log files in the raw data base to be compressed into storage.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201610872612.9A CN106649481A (en) | 2016-09-30 | 2016-09-30 | A method and system of log optimization for SQL Server database |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201610872612.9A CN106649481A (en) | 2016-09-30 | 2016-09-30 | A method and system of log optimization for SQL Server database |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN106649481A true CN106649481A (en) | 2017-05-10 |
Family
ID=58854385
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201610872612.9A Pending CN106649481A (en) | 2016-09-30 | 2016-09-30 | A method and system of log optimization for SQL Server database |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN106649481A (en) |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN107247643A (en) * | 2017-06-28 | 2017-10-13 | 上海优刻得信息科技有限公司 | A kind of data base management method, device, system, storage medium and equipment |
| CN108829543A (en) * | 2018-06-21 | 2018-11-16 | 郑州云海信息技术有限公司 | A method of reducing backup Linux system log size |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20130198139A1 (en) * | 2012-01-30 | 2013-08-01 | Memsql, Inc. | Durability implementation plan in an in-memory database system |
| CN104123300A (en) * | 2013-04-26 | 2014-10-29 | 上海云人信息科技有限公司 | Data distributed storage system and method |
| CN104462261A (en) * | 2014-11-23 | 2015-03-25 | 国云科技股份有限公司 | Method for making only MDF attached to restore database |
-
2016
- 2016-09-30 CN CN201610872612.9A patent/CN106649481A/en active Pending
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20130198139A1 (en) * | 2012-01-30 | 2013-08-01 | Memsql, Inc. | Durability implementation plan in an in-memory database system |
| CN104123300A (en) * | 2013-04-26 | 2014-10-29 | 上海云人信息科技有限公司 | Data distributed storage system and method |
| CN104462261A (en) * | 2014-11-23 | 2015-03-25 | 国云科技股份有限公司 | Method for making only MDF attached to restore database |
Non-Patent Citations (1)
| Title |
|---|
| 樊志平 等: ""SQL Server 数据库的备份与恢复策略研究"", 《电脑知识与技术》 * |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN107247643A (en) * | 2017-06-28 | 2017-10-13 | 上海优刻得信息科技有限公司 | A kind of data base management method, device, system, storage medium and equipment |
| CN108829543A (en) * | 2018-06-21 | 2018-11-16 | 郑州云海信息技术有限公司 | A method of reducing backup Linux system log size |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN101334797B (en) | Distributed file systems and its data block consistency managing method | |
| US9690671B2 (en) | Manifest-based snapshots in distributed computing environments | |
| CN102985911B (en) | Telescopic in height and distributed data de-duplication | |
| US9031910B2 (en) | System and method for maintaining a cluster setup | |
| CN105677250B (en) | The update method and updating device of object data in object storage system | |
| CN102480494B (en) | File updating method, device and system | |
| CN103455557B (en) | A kind of structural data synchronous method based on daily record | |
| CN109284069A (en) | A kind of distributed memory system and method for storing Backup Data | |
| US9760617B2 (en) | Applying transaction log in parallel | |
| US9436559B2 (en) | Storage apparatus and method for controlling cache of storage apparatus | |
| CN102681917B (en) | A kind of operating system and restorative procedure thereof | |
| CN102693388B (en) | Data safety protection processing system, method and storage medium | |
| CN102651007A (en) | Method and device for managing database indexes | |
| CN104615710A (en) | Electronic map frame data updating method | |
| CN105808274A (en) | Software upgrading method and system as well as corresponding clients and servers | |
| CN105426748B (en) | A kind of update method and equipment of rule file | |
| CN106936623A (en) | The management method of distributed cache system and cache cluster | |
| CN104217174A (en) | Safety storage system and safety storage method for distributed files | |
| CN108092936A (en) | A kind of Host Supervision System based on plug-in architecture | |
| CN103618788A (en) | System high-availability method supporting B/S structure | |
| WO2016169237A1 (en) | Data processing method and device | |
| CN103428288B (en) | Based on the copies synchronized method of subregion state table and coordinator node | |
| CN108121793A (en) | A kind of DB Backup dispositions method and device | |
| CN106649481A (en) | A method and system of log optimization for SQL Server database | |
| US20130041868A1 (en) | Data synchronization |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| RJ01 | Rejection of invention patent application after publication | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20170510 |