Sql Server数据库实用命令

1.清理日志语句:当日志容量过大时

backup log isale with NO_LOG;
backup log isale with TRUNCATE_ONLY;
DBCC SHRINKDATABASE(isale);

 

2.Sql Server自动备份语句

declare @filename nvarchar(100) set @filename='D:dbstore/isale'+replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
+'.bak'

BACKUP DATABASE [isale] TO  DISK =@filename
WITH NOFORMAT, INIT, 
NAME = N'isale-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

 

3.Sql Server查看索引碎片

use databaseName

DBCC SHOWCONTIG

eg:对于单表

use isale
DBCC SHOWCONTIG (branch_move_in)

 

DBCC SHOWCONTIG scanning 'Employee' table... 
Table: 'Employee' (1195151303); index ID: 1, database ID: 53 
TABLE level scan performed. 
- Pages Scanned................................: 179 
- Extents Scanned..............................: 24 
- Extent Switches..............................: 24 
- Avg. Pages per Extent........................: 7.5 
- Scan Density [Best Count:Actual Count].......: 92.00% [23:25] 
- Logical Scan Fragmentation ..................: 0.56% 
- Extent Scan Fragmentation ...................: 12.50% 
- Avg. Bytes Free per Page.....................: 552.3 
- Avg. Page Density (full).....................: 93.18% 
DBCC execution completed.

信息  描述
Pages Scanned   表或索引中的长页数
Extents Scanned 表或索引中的长区页数
Extent Switches  DBCC遍历页时从一个区域到另一个区域的次数
Avg. Pages per Extent      相关区域中的页数
Scan Density[Best Count:Actual Count]
Best Count是连续链接时的理想区域改变数，Actual Count是实际区域改变，
Scan Density为100%表示没有分块。
Logical Scan Fragmentation   扫描索引页中失序页的百分比
Extent Scan Fragmentation    不实际相邻和包含链路中所有链接页的区域数
Avg. Bytes Free per Page       扫描页面中平均自由字节数
Avg. Page Density (full) 平均页密度，表示页有多满

重修复索引：
DBCC DBREINDEX (branch_move_in)

 

4.Sql Server检查数据库状态

DBCC CHECKDB