杂记

对crm数据库中的数据大小分析：

USE CRM
GO
--查看表使用情况
IF OBJECT_ID('tempdb..#TB_TEMP_SPACE') IS NOT NULL DROP TABLE #TB_TEMP_SPACE
GO
CREATE TABLE #TB_TEMP_SPACE(
NAME VARCHAR(500)
,ROWS INT
,RESERVED VARCHAR(50)
,DATA VARCHAR(50)
,INDEX_SIZE VARCHAR(50)
,UNUSED VARCHAR(50)
)
GO
SP_MSFOREACHTABLE 'INSERT INTO #TB_TEMP_SPACE exec sp_spaceused ''?'''
GO
SELECT *
FROM #TB_TEMP_SPACE
ORDER BY REPLACE(DATA,'KB','')+0 DESC
GO 

　　数据库全备份：

USE [master]
GO

/****** Object:  View [dbo].[Tool_FullbackupSyntex]    Script Date: 2015/12/23 11:42:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER view [dbo].[Tool_FullbackupSyntex]
as
with tempA as
(
--**记得要改这里的路径、路径对于每一个服务器都是不同的**--
select 'backup database '+name +' to disk = '+''''+ 'F:\backup\'+name+convert(varchar(100),getdate(),112)+'_with_compression.bak'+''''+' with init,compression; ' as cmd
from sys.databases where name not in('tempdb','master','model','msdb')),
tempB as(
select cmd ,ROW_NUMBER() over(order by cmd) rowNumber from tempA)
select * from tempB;



GO

　　

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[Tool_FullBack]    Script Date: 2015/12/23 11:41:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[Tool_FullBack]
as
begin

    -- @i 用于记录一共有多少个数据库。（除tempdb以外）。
    declare @i as int=0;
    select @i=count(*) from dbo.Tool_FullbackupSyntex;
    -- @cmd 用于备份的动态脚本。
    declare @cmd as nvarchar(max)=' ';
    -- 加载动态脚本到 @tempA 表中。
    declare @tempA table(rowNumber int,cmd nvarchar(255));
    insert into @tempA(rowNumber,cmd)
    select a.rowNumber,a.cmd from dbo.Tool_FullbackupSyntex as a;

    -- 拼接动态脚本。
    while @i>=1
        begin
            select @cmd=@cmd+cmd from @tempA where rowNumber=@i;
            set @i = @i-1;
        end
    -- 执行备份
    exec(@cmd);
end;

　　执行全备份：

USE [master]
GO

DECLARE    @return_value int

EXEC    @return_value = [dbo].[Tool_FullBack]

SELECT    'Return Value' = @return_value

GO

　　监控数据库中进程相关的情况

 select  a.*,a.cmd,a.waittime,DB_NAME(a.dbid) as DBName,b.text as SQLText,cmd,a.last_batch from sys.sysprocesses as a cross apply sys.dm_exec_sql_text(sql_handle) as b
where cmd !='AWAITING COMMAND'