sqlserver 日志压缩 表分区

1.日志压缩 

USE [master]
GO
ALTER DATABASE [user] SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE [user] SET RECOVERY SIMPLE   --简单模式
GO
USE [user]  --数据库名
GO
DBCC SHRINKFILE (N'user_log' , 2, TRUNCATEONLY)  --设置压缩后的日志大小为2M，可以自行指定  user_log日志文件名
GO
USE [master]
GO
ALTER DATABASE [user] SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE [user] SET RECOVERY FULL  --还原为完全模式
GO

 

2.表分区

--创建出入境分区
USE user;
alter database [user] add filegroup crjGtoup1
alter database [user] add filegroup crjGtoup2
alter database [user] add filegroup crjGtoup3
alter database [user] add filegroup crjGtoup4
alter database [user] add filegroup crjGtoup5
alter database [user] add filegroup crjGtoup6
alter database [user] add filegroup crjGtoup7
alter database [user] add filegroup crjGtoup8
alter database [user] add filegroup crjGtoup9
alter database [user] add filegroup crjGtoup10
alter database [user] add filegroup crjGtoup11
alter database [user] add filegroup crjGtoup12
alter database [user] add filegroup crjGtoup13
alter database [user] add filegroup crjGtoup14
alter database [user] add filegroup crjGtoup15
alter database [user] add filegroup crjGtoup16
alter database [user] add filegroup crjGtoup17
alter database [user] add filegroup crjGtoup18
alter database [user] add filegroup crjGtoup19
alter database [user] add filegroup crjGtoup20
alter database [user] add filegroup crjGtoup21
alter database [user] add filegroup crjGtoup22
alter database [user] add filegroup crjGtoup23
alter database [user] add filegroup crjGtoup24
alter database [user] add filegroup crjGtoup25
alter database [user] add filegroup crjGtoup26
alter database [user] add filegroup crjGtoup27
alter database [user] add filegroup crjGtoup28
alter database [user] add filegroup crjGtoup29
alter database [user] add filegroup crjGtoup30
alter database [user] add filegroup crjGtoup31
alter database [user] add filegroup crjGtoup32
alter database [user] add filegroup crjGtoup33
alter database [user] add filegroup crjGtoup34
alter database [user] add filegroup crjGtoup35
alter database [user] add filegroup crjGtoup36
alter database [user] add filegroup crjGtoup37
alter database [user] add filegroup crjGtoup38
alter database [user] add filegroup crjGtoup39
alter database [user] add filegroup crjGtoup40
alter database [user] add filegroup crjGtoup41
alter database [user] add filegroup crjGtoup42
alter database [user] add filegroup crjGtoup43
alter database [user] add filegroup crjGtoup44
alter database [user] add filegroup crjGtoup45
alter database [user] add filegroup crjGtoup46
alter database [user] add filegroup crjGtoup47
alter database [user] add filegroup crjGtoup48
alter database [user] add filegroup crjGtoup49
alter database [user] add filegroup crjGtoup50
alter database [user] add filegroup crjGtoup51
alter database [user] add filegroup crjGtoup52
alter database [user] add filegroup crjGtoup53
alter database [user] add filegroup crjGtoup54
alter database [user] add filegroup crjGtoup55
alter database [user] add filegroup crjGtoup56
alter database [user] add filegroup crjGtoup47
alter database [user] add filegroup crjGtoup48
alter database [user] add filegroup crjGtoup49
alter database [user] add filegroup crjGtoup50
alter database [user] add filegroup crjGtoup51
alter database [user] add filegroup crjGtoup52
alter database [user] add filegroup crjGtoup53
alter database [user] add filegroup crjGtoup54
alter database [user] add filegroup crjGtoup55
alter database [user] add filegroup crjGtoup56
alter database [user] add filegroup crjGtoup57
alter database [user] add filegroup crjGtoup58
alter database [user] add filegroup crjGtoup59
alter database [user] add filegroup crjGtoup60
alter database [user] add filegroup crjGtoup61
alter database [user] add filegroup crjGtoup62
alter database [user] add filegroup crjGtoup63
alter database [user] add filegroup crjGtoup64
alter database [user] add filegroup crjGtoup65
alter database [user] add filegroup crjGtoup66
alter database [user] add filegroup crjGtoup67
alter database [user] add filegroup crjGtoup68
alter database [user] add filegroup crjGtoup69
alter database [user] add filegroup crjGtoup70
alter database [user] add filegroup crjGtoup71
alter database [user] add filegroup crjGtoup72
alter database [user] add filegroup crjGtoup73
alter database [user] add filegroup crjGtoup74
alter database [user] add filegroup crjGtoup75
alter database [user] add filegroup crjGtoup76
alter database [user] add filegroup crjGtoup77
alter database [user] add filegroup crjGtoup78
alter database [user] add filegroup crjGtoup79
alter database [user] add filegroup crjGtoup80
alter database [user] add filegroup crjGtoup81
alter database [user] add filegroup crjGtoup82
alter database [user] add filegroup crjGtoup83

alter database [user] add file(name='crj01',filename='D:\sqldata\crj01.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup1
alter database [user] add file(name='crj2',filename='D:\sqldata\crj2.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup2
alter database [user] add file(name='crj3',filename='D:\sqldata\crj3.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup3
alter database [user] add file(name='crj4',filename='D:\sqldata\crj4.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup4
alter database [user] add file(name='crj5',filename='D:\sqldata\crj5.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup5
alter database [user] add file(name='crj6',filename='D:\sqldata\crj6.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup6
alter database [user] add file(name='crj7',filename='D:\sqldata\crj7.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup7
alter database [user] add file(name='crj8',filename='D:\sqldata\crj8.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup8
alter database [user] add file(name='crj9',filename='D:\sqldata\crj9.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup9
alter database [user] add file(name='crj10',filename='D:\sqldata\crj10.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup10
alter database [user] add file(name='crj11',filename='D:\sqldata\crj11.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup11
alter database [user] add file(name='crj12',filename='D:\sqldata\crj12.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup12
alter database [user] add file(name='crj13',filename='D:\sqldata\crj13.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup13
alter database [user] add file(name='crj14',filename='D:\sqldata\crj14.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup14
alter database [user] add file(name='crj15',filename='D:\sqldata\crj15.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup15
alter database [user] add file(name='crj16',filename='D:\sqldata\crj16.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup16
alter database [user] add file(name='crj17',filename='D:\sqldata\crj17.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup17
alter database [user] add file(name='crj18',filename='D:\sqldata\crj18.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup18
alter database [user] add file(name='crj19',filename='D:\sqldata\crj19.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup19
alter database [user] add file(name='crj20',filename='D:\sqldata\crj20.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup20
alter database [user] add file(name='crj21',filename='D:\sqldata\crj21.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup21
alter database [user] add file(name='crj22',filename='D:\sqldata\crj22.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup22
alter database [user] add file(name='crj23',filename='D:\sqldata\crj23.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup23
alter database [user] add file(name='crj24',filename='D:\sqldata\crj24.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup24
alter database [user] add file(name='crj25',filename='D:\sqldata\crj25.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup25
alter database [user] add file(name='crj26',filename='D:\sqldata\crj26.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup26
alter database [user] add file(name='crj27',filename='D:\sqldata\crj27.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup27
alter database [user] add file(name='crj28',filename='D:\sqldata\crj28.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup28
alter database [user] add file(name='crj29',filename='D:\sqldata\crj29.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup29
alter database [user] add file(name='crj30',filename='D:\sqldata\crj30.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup30
alter database [user] add file(name='crj31',filename='D:\sqldata\crj31.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup31
alter database [user] add file(name='crj32',filename='D:\sqldata\crj32.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup32
alter database [user] add file(name='crj33',filename='D:\sqldata\crj33.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup33
alter database [user] add file(name='crj34',filename='D:\sqldata\crj34.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup34
alter database [user] add file(name='crj35',filename='D:\sqldata\crj35.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup35
alter database [user] add file(name='crj36',filename='D:\sqldata\crj36.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup36
alter database [user] add file(name='crj37',filename='D:\sqldata\crj37.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup37
alter database [user] add file(name='crj38',filename='D:\sqldata\crj38.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup38
alter database [user] add file(name='crj39',filename='D:\sqldata\crj39.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup39
alter database [user] add file(name='crj40',filename='D:\sqldata\crj40.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup40
alter database [user] add file(name='crj41',filename='D:\sqldata\crj41.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup41
alter database [user] add file(name='crj42',filename='D:\sqldata\crj42.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup42
alter database [user] add file(name='crj43',filename='D:\sqldata\crj43.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup43
alter database [user] add file(name='crj44',filename='D:\sqldata\crj44.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup44
alter database [user] add file(name='crj45',filename='D:\sqldata\crj45.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup45
alter database [user] add file(name='crj46',filename='D:\sqldata\crj46.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup46
alter database [user] add file(name='crj47',filename='D:\sqldata\crj47.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup47
alter database [user] add file(name='crj48',filename='D:\sqldata\crj48.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup48
alter database [user] add file(name='crj49',filename='D:\sqldata\crj49.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup49
alter database [user] add file(name='crj50',filename='D:\sqldata\crj50.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup50
alter database [user] add file(name='crj51',filename='D:\sqldata\crj51.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup51
alter database [user] add file(name='crj52',filename='D:\sqldata\crj52.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup52
alter database [user] add file(name='crj53',filename='D:\sqldata\crj53.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup53
alter database [user] add file(name='crj54',filename='D:\sqldata\crj54.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup54
alter database [user] add file(name='crj55',filename='D:\sqldata\crj55.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup55
alter database [user] add file(name='crj56',filename='D:\sqldata\crj56.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup56
alter database [user] add file(name='crj57',filename='D:\sqldata\crj57.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup57
alter database [user] add file(name='crj58',filename='D:\sqldata\crj58.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup58
alter database [user] add file(name='crj59',filename='D:\sqldata\crj59.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup59
alter database [user] add file(name='crj60',filename='D:\sqldata\crj60.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup60
alter database [user] add file(name='crj61',filename='D:\sqldata\crj61.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup61
alter database [user] add file(name='crj62',filename='D:\sqldata\crj62.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup62
alter database [user] add file(name='crj63',filename='D:\sqldata\crj63.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup63
alter database [user] add file(name='crj64',filename='D:\sqldata\crj64.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup64
alter database [user] add file(name='crj65',filename='D:\sqldata\crj65.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup65
alter database [user] add file(name='crj66',filename='D:\sqldata\crj66.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup66
alter database [user] add file(name='crj67',filename='D:\sqldata\crj67.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup67
alter database [user] add file(name='crj68',filename='D:\sqldata\crj68.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup68
alter database [user] add file(name='crj69',filename='D:\sqldata\crj69.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup69
alter database [user] add file(name='crj70',filename='D:\sqldata\crj70.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup70
alter database [user] add file(name='crj71',filename='D:\sqldata\crj71.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup71
alter database [user] add file(name='crj72',filename='D:\sqldata\crj72.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup72
alter database [user] add file(name='crj73',filename='D:\sqldata\crj73.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup73
alter database [user] add file(name='crj74',filename='D:\sqldata\crj74.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup74
alter database [user] add file(name='crj75',filename='D:\sqldata\crj75.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup75
alter database [user] add file(name='crj76',filename='D:\sqldata\crj76.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup76
alter database [user] add file(name='crj77',filename='D:\sqldata\crj77.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup77
alter database [user] add file(name='crj78',filename='D:\sqldata\crj78.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup78
alter database [user] add file(name='crj79',filename='D:\sqldata\crj79.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup79
alter database [user] add file(name='crj80',filename='D:\sqldata\crj80.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup80
alter database [user] add file(name='crj81',filename='D:\sqldata\crj81.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup81
alter database [user] add file(name='crj82',filename='D:\sqldata\crj82.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup82
alter database [user] add file(name='crj83',filename='D:\sqldata\crj83.mdf',size=5MB,filegrowth=5MB)to filegroup crjGtoup82

use [user]
create partition function crjPartionFun(varchar(20)) as range left for values(
'20200301000000','20200401000000','20200501000000','20200601000000','20200701000000',
'20200801000000','20200901000000','20201001000000','20201101000000','20201201000000',
'20210101000000','20210201000000','20210301000000','20210401000000','20210501000000',
'20210601000000','20210701000000','20210801000000','20210901000000','20211001000000',
'20211101000000','20211201000000','20220101000000','20220201000000','20220301000000',
'20220401000000','20220501000000','20220601000000','20220701000000','20220801000000',
'20220901000000','20221001000000','20221101000000','20221201000000','20230101000000',
'20230201000000','20230301000000','20230401000000','20230501000000','20230601000000',
'20230701000000','20230801000000','20230901000000','20231001000000','20231101000000',
'20231201000000','20240101000000','20240201000000','20240301000000','20240401000000',
'20240501000000','20240601000000','20240701000000','20240801000000','20240901000000',
'20241001000000','20241101000000','20241201000000','20250101000000','20250201000000',
'20250301000000','20250401000000','20250501000000','20250601000000','20250701000000',
'20250801000000','20250901000000','20251001000000','20251101000000','20251201000000',
'20260101000000','20260201000000','20260301000000','20260401000000','20260501000000',
'20260601000000','20260701000000','20260801000000','20260901000000','20261001000000',
'20261101000000','20261201000000')

create partition scheme [crjPartionScheme] as partition [crjPartionFun] to(
[crjGtoup1],[crjGtoup2],[crjGtoup3],[crjGtoup4],[crjGtoup5],[crjGtoup6],[crjGtoup7],[crjGtoup8],[crjGtoup9],[crjGtoup10],
[crjGtoup11],[crjGtoup12],[crjGtoup13],[crjGtoup14],[crjGtoup15],[crjGtoup16],[crjGtoup17],[crjGtoup18],[crjGtoup19],[crjGtoup20],
[crjGtoup21],[crjGtoup22],[crjGtoup23],[crjGtoup24],[crjGtoup25],[crjGtoup26],[crjGtoup27],[crjGtoup28],[crjGtoup29],[crjGtoup30],
[crjGtoup31],[crjGtoup32],[crjGtoup33],[crjGtoup34],[crjGtoup35],[crjGtoup36],[crjGtoup37],[crjGtoup38],[crjGtoup39],[crjGtoup40],
[crjGtoup41],[crjGtoup42],[crjGtoup43],[crjGtoup44],[crjGtoup45],[crjGtoup46],[crjGtoup47],[crjGtoup48],[crjGtoup49],[crjGtoup50],
[crjGtoup51],[crjGtoup52],[crjGtoup53],[crjGtoup54],[crjGtoup55],[crjGtoup56],[crjGtoup57],[crjGtoup58],[crjGtoup59],[crjGtoup60],
[crjGtoup61],[crjGtoup62],[crjGtoup63],[crjGtoup64],[crjGtoup65],[crjGtoup66],[crjGtoup67],[crjGtoup68],[crjGtoup69],[crjGtoup70],
[crjGtoup71],[crjGtoup72],[crjGtoup73],[crjGtoup74],[crjGtoup75],[crjGtoup76],[crjGtoup77],[crjGtoup78],[crjGtoup79],[crjGtoup80],
[crjGtoup81],[crjGtoup82],[crjGtoup83])

create clustered index ix_rjsj on t_crj_ryxx(rjsj) on crjPartionScheme(rjsj)

select * from t_crj_ryxx where $partition.[crjPartionFun](rjsj)=14

select * from t_crj_ryxx where SUBSTRING(RJSJ,0,9)='20220304'

分区部分可参考网址:https://www.cnblogs.com/xiaomengshan/p/11139299.html