SQL Server 的一些工作记录

一些命令

简单查询

sp_who 'username'  ----查看该用户连接的sid与执行的操作、状态

select * from sys.sysprocesses ---查看进程

select * from sys.sysusers  ---查看用户信息

select * from sys.dm_exec_connections  ----查看连接情况

select session_id,status,login_name,login_time,*  from  sys.dm_exec_sessions ----查看会话

select sql_handle,* from sys.dm_exec_requests  ----查看请求的ID,请求的状态

select spid, status, cmd from master.sys.sysprocesses where dbid = DB_ID('QDHGCW')  ----查看对应数据库在后台进行的操作

dbcc sqlperf(logspace)    ---可查看日志文件的使用占比　log space used(%)

附加数据库

具有原数据文件和日志文件的数据库才可进行导入

USE [master]

GO

CREATE DATABASE [testspace10g90] ON 

( FILENAME = N'D:\sqlserver\E\MSSQLfile\bigdata\data\testspace10g90.mdf' ),

( FILENAME = N'D:\sqlserver\E\MSSQLfile\bigdata\log\testspace10g90.ldf' )

 FOR ATTACH

GO

设置恢复模式

USE master ;  

ALTER DATABASE model SET RECOVERY FULL ; 

恢复模式简单介绍

恢复模式有三种：

• FULL
  所有事务操作都将记录到事务日志中，可支持时间点恢复。比较推荐。
• BULK_LOGGED
  与 FULL 模式相比，只能恢复到日志的结尾。适用于长事务、大容量的操作。
• SIMPLE
  无事务日志，所有的事务操作会先存储在一个临时文件中，待执行后将写入到数据文件，只能恢复到备份状态。不太推荐。

在多年的数据备份行业的支持工作中，大多客户都有一点错误的理解：SQL Server 会自动对日志进行截断，对日志进行收缩。
这是错误的理解，SQL Server 中数据库的恢复模式设置为 FULL 或 BULK_LOGGED ,不会对日志文件做截断，只有当进行了日志备份操作后，日志文件才会进行截断操作，在后续对日志文件进行复写重用。

备份

backup database dbname to disk='e:\s.bak' with name='full backup'  -----完全备份

with differential            ------------差异备份，需要与上一语句结合使用

backup log dbname to disk='e:\a.bak'            --------------日志备份，可对日志文件进行截断

backup database dbname file='file1',file='file2' to disk='e:\s.bak'      ------------------备份文件

日志备份虽然能对日志文件进行截断，但其并非是对日志文件的大小进行截断，而是对于空间的截断。日志文件的大小不会发生变化，在后续的使用中，会对其空间进行覆盖重写，当覆盖重写的内容超过原空间大小，则日志文件大小会继续增长，直至下次截断。

收缩日志

当业务系统对数据库进行长期的大事务操作，导致数据库日志文件不断增大，从而对磁盘空间造成压力。而对数据库及日志文件进行截断并不能减小其占用的空间大小，因此需要对日志文件进行收缩，以达到节省空间的作用。
以下的两个命令连续执行，间隔时间越少越明显（可多次运行），直到达到所需要求。

--截断，备份日志文件

BACKUP LOG CloudMonitor TO DISK='NUL'

--收缩

DBCC SHRINKFILE('CloudMonitor_log')

收缩日志文件对备份是有影响的，执行收缩后，是不能做日志备份的，或者说日志备份集是不可用的，因为日志文件的内部空间位置已经发生了变化，需要进行完全备份然后再做日志备份，才能保证接下来的备份集可用。这一点对于使用第三方备份软件的时候特别需要注意，因为很多运维或dba会写一个周期性的日志文件收缩任务，但没有与备份软件厂商做沟通，结果导致部分备份集不可用，这往往是在发生故障需要恢复时才发现。

并发备份

其实说是并发备份，但与理解的不同，这里不是对多个数据库的并发备份，而是对一个数据库进行分块备份，将一个数据库的备份分成多个备份文件。

backup database dbname to disk='disk1',disk='disk2',disk='disk3';

恢复

restore database dbname from disk='e:\a.bak'

with norecovery             -----------结尾日志备份

with no_truncate            -------------结尾日志备份--丢失文件时，sql2000只能使用这个，其他版本两个都可以用

将数据库中的表复制到新建表

select * into test5 from simple.dbo.test1

查看快照

SELECT * FROM sys.fn_db_backup_file_snapshots ('dbname');  

备份文件快照

合成备份或者说是快照备份,完成后使用上一语句查看是否生成快照

backup database dbname to disk='e:\asdf.bak' with file_snapshot;

查询数据库数据页

select size from sys.master_files

where name='scutech1' 

查询用户表:

select * from sys.objects where type='U'

查询表，临时添加标识id字段

select row_number() over(order by aa asc) as id,aa,bb,cc,dd from testdb.dbo.testdata1

在已经创建的表中增加自增字段，自增字段会根据表中记录自动增长（包括已经有的记录）

alter table tablename add id bigint identity(1,1)

查询存储过程

select * from sys.objects where type='P'

查询存储过程的内容

EXEC Sp_HelpText  '存储过程name'

查询自定义函数

select * from dbo.sysobjects  WHERE xtype='AF'

查询自定义函数的内容

EXEC Sp_HelpText  '自定义函数name'

查询各个磁盘分区的剩余空间：

        Exec master.dbo.xp_fixeddrives 

查询Sql Server数据文件及日志文件的相关信息

可查看包括文件组、当前文件大小、文件最大值、文件增长设置、文件逻辑名、文件路径等信息

     select * from [数据库名].[dbo].[sysfiles]

     转换文件大小单位为MB：

    select name, convert(float,size) * (8192.0/1024.0)/1024. from [数据库名].dbo.sysfiles

查询当前数据库的磁盘使用情况：

    Exec sp_spaceused

一些可能有用的基础命令

1. 更改字段类型长度 

    alter table 表名

    alter column 字段名 类型的长度

    例：把城市表的城市名字段由原来的长度20改为30

    alter table city 

    alter column cityname varchar(30); 

2. 更改字段类型 

    alter table 表名

    alter column 字段名 更改后的类型

    例：把城市表的城市名字段有原来的varchar类型改为int类型

    alter table city 

    alter column cityname int; 

3. 添加not null约束 

    alter table 表名 alter column 字段名 int not null 

    例：把cid不能输入空值

    alter table city alter column cid int not null； 

4. 设置主键 

    alter table 表名 add constraint 主键名 primary key(字段名)

    例：把cid设为主键

    alter table city add constraint PK_cid primary key(cid)； 

5. 更改字段名 

    EXEC sp_rename '表名.字段名','更改后的字段名','COLUMN'

6. 添加字段名 

    ALTER TABLE 表 ADD 字段名 字段类型 DEFAULT null

sql server 实现sleep延时

waitfor delay '00:00:00:10' 表示延时10毫秒

一些脚本

创建数据库的bat脚本

@echo off

set i=1

for /L %%i in (1,1,11) do 

echo  create database bigdatatest%%i on primary >> create.sql

echo ( >> create.sql

echo name = 'bigdatatest%%i',>> create.sql

echo filename ='D:\sqlserver\bigdatatest%%i.mdf',>> create.sql

echo size=500MB,>> create.sql

echo filegrowth=50MB>> create.sql

echo ")">> create.sql

echo log on (>> create.sql

echo name = 'bigdatatest%%i_log',>> create.sql

echo filename='D:\sqlserver\bigdatatest%%i_log.ldf',>> create.sql

echo size=100MB,>> create.sql

echo filegrowth=20MB>> create.sql

echo ")">> create.sql

echo go>> create.sql

echo use bigdatatest%%i >> create.sql

echo go >> create.sql

set t=1

for /L %%t in (1,1,100) do (

echo " create table testdata1 ( id int identify(1,1), d_t1 datatime default current_timestamp,a1 char(50) default 'QWERTYUIOP1234567890asdfghjkl01111110000ZXCaaaV',a2 char(50) default 'QWERTYUIOP0987654321asdfghjkl01111110000ZXCaaaB',a3 char(50) default 'POIuytREWQ0987654321asdfghjkl01111110000ZXCbbbN',a4 char(50) default 'QWERTUIOP0987654321ZXCVBNMKL7Y7Y7Y7Y7Y1ZXCzz0P') ">>create.sql

)

)

pause

创建循环插入数据的sql 的bat脚本

@echo off

echo declare @id int >> insert1_1.sql

echo begin >> insert1_1.sql

echo set @id=1 >>insert1_1.sql

echo "while @id < 10000000" >>insert1_1.sql

echo begin >>insert1_1.sql

set i=1

for /L %%i in (1,1,34) do (

echo "insert into bigdatatest1.dbo.testdata%%i values(default,default,default,default,default)" >>insert1_1.sql

)

echo set @id+=1 >>insert1_1.sql

echo end >>insert1_1.sql

echo end >>insert1_1.sql

pause

循环插入数据 sql脚本

declare @id int 

begin 

set @id=1 

while @id < 10000000

begin 

insert into bigdatatest11.dbo.testdata1 values(default,default,default,default,default) 

insert into bigdatatest11.dbo.testdata2 values(default,default,default,default,default) 

set @id+=1 

end 

end