pppsql2vssql

SELECT 
s.Name AS SchemaName, 
t.Name AS TableName, 
p.rows AS RowCounts, 
CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2))/1024 AS Used_GB, 
CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2))/1024 AS Unused_GB, 
CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2))/1024 AS Total_GB 
FROM sys.tables t 
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id 
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id 
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id 
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id 
GROUP BY t.Name, s.Name, p.Rows 
ORDER BY 4 desc

USE ReportServer
GO 
SELECT 
    cat.Name
    ,cat.Path 
    ,sb.LastStatus
    ,sb.LastRunTime
    ,sb.SubscriptionID
    ,rs.ScheduleID
FROM dbo.Catalog AS cat WITH(NOLOCK)
JOIN dbo.Subscriptions AS sb WITH(NOLOCK)
    ON CAT.ItemID = SB.Report_OID 
LEFT OUTER JOIN DBO.ReportSchedule AS rs WITH(NOLOCK)
    ON sb.SubscriptionID = rs.SubscriptionID
WHERE sb.LastStatus LIKE N'%挂起%' OR sb.LastStatus LIKE N'%正在%'
ORDER BY sb.LastRunTime DESC 

EXEC msdb.dbo.sp_start_job N'A7F27C9F-DD0A-4C78-8E04-1D3B07B9C7C3'

if object_id('tempdb..#Data') is not null DROP TABLE #Data   
if object_id('tempdb..#dataNew') is not null DROP TABLE #dataNew   
create table #Data(name varchar(100),row varchar(100),reserved varchar(100),data varchar(100),index_size varchar(100),unused varchar(100))  

declare @name varchar(100)  
declare cur cursor  for  
    select name from sysobjects where xtype='u' order by name  
    open cur  
        fetch next from cur into @name  
        while @@fetch_status=0  
        begin  
            insert into #data  
            exec sp_spaceused   @name  
            print @name  
          
            fetch next from cur into @name  
        end  
    close cur  
deallocate cur

create table #DataNew(name varchar(100),row varchar(100),reserved varchar(100),data varchar(100),index_size varchar(100),unused varchar(100))  
insert into #dataNew  
    select name,convert(varchar(100),row) as row
    ,convert(varchar(100),replace(reserved,'KB','')) as reserved
    ,convert(varchar(100),replace(data,'KB','')) as data  
    ,convert(varchar(100),replace(index_size,'KB','')) as index_size
    ,convert(varchar(100),replace(unused,'KB','')) as unused 
    from #data   
    -- select * from #dataNew order by name

select reserved/1024/1024 as G,* from #dataNew order by 1 DESC

--- db size 
SET NOCOUNT ON
declare @sql nvarchar(max)
declare @sql2 nvarchar(max)
declare @dbname nvarchar(50)
create table #db_log_size (database_name nvarchar(100),dbsize nvarchar(200),logsize nvarchar(200))
create table #result (database_name nvarchar(100),reservedpages nvarchar(200),usedpages nvarchar(200),pages nvarchar(200))

declare cur cursor for
select name from sys.databases where state=0
open cur
    fetch next from cur into @dbname
    while @@FETCH_STATUS = 0
    begin
        set @sql='
            select 
            '''+@dbname+''' as database_name,
            sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) as dbsize,
            sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) as logsize
            from '+@dbname+'.dbo.sysfiles'
        insert into #db_log_size exec sp_executesql @sql

        set @sql2='
            select 
            '''+@dbname+''' as database_name,
            sum(a.total_pages) as reservedpages,
            sum(a.used_pages) as usedpages,
            sum(
                CASE
                When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0
                When a.type <> 1 and p.index_id < 2 Then a.used_pages
                When p.index_id < 2 Then a.data_pages
                Else 0
                END
                ) as pages
            from '+@dbname+'.sys.partitions p join '+@dbname+'.sys.allocation_units a on p.partition_id = a.container_id
            left join '+@dbname+'.sys.internal_tables it on p.object_id = it.object_id'
        insert into #result exec sp_executesql @sql2

        fetch next from cur into @dbname
    end
close cur
deallocate cur

select *
from (
select 
    d.database_name as '数据库名称',
    ltrim(str((convert (dec (15,2),d.dbsize) + convert (dec (15,2),d.logsize)) * 8192 / 1048576 / 1024,15,2) + 'GB') as '数据库总大小',
    ltrim(str((case when d.dbsize >= r.reservedpages 
                then (convert (dec (15,2),d.dbsize) - convert (dec (15,2),r.reservedpages))  * 8192 / 1048576 / 1024 else 0 end),15,2) + 'GB') as '未分配空间',
    ltrim(str(convert(dec(15,2),(convert (dec (15,2),r.reservedpages) + convert (dec (15,2),d.logsize)) 
             / (convert (dec (15,2),d.dbsize) + convert (dec (15,2),d.logsize))) * 100)) + '%' as '空间使用率'
from #db_log_size d 
inner join #result r on d.database_name = r.database_name
) a
order by CAST(replace(数据库总大小,'GB','') AS FLOAT) DESC

go

backup database [test] to disk='d:\test.bak' with format,compression

--还原数据库
USE [master]
RESTORE DATABASE [test] FROM  DISK = N'D:\test.bak' WITH  FILE = 1, 
MOVE N'test' TO N'g:\SQLDATA\test.mdf',
MOVE N'test_log' TO N'L:\SQLDATA\test.ldf',  NOUNLOAD,  STATS = 5,norecovery
GO

--备份还原进度
SELECT   DB_NAME(er.[database_id]) [DatabaseName],  
er.[command] AS [CommandType],  
er.[percent_complete],  
er.start_time,
CONVERT(DECIMAL(5, 2), er.[percent_complete]) AS [Complete_Percent]  
,CONVERT(DECIMAL(38, 2), er.[total_elapsed_time] / 60000.00) AS [ElapsedTime_m]  
,CONVERT(DECIMAL(38, 2), er.[estimated_completion_time] / 60000.00) AS [EstimatedCompletionTime_m]  
FROM sys.dm_exec_requests AS er  
WHERE
er.[command] in ( 'RESTORE DATABASE' ,'BACKUP DATABASE')
order by er.start_time desc

--收缩文件进度
SELECT DB_NAME(database_id) AS Exec_DB
,percent_complete
,CASE WHEN estimated_completion_time < 36000000
THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining]
,b.text as tsql   --sql语句
,*
FROM sys.dm_exec_requests
cross apply sys.dm_exec_sql_text(sql_handle) as b
WHERE command LIKE 'DbccFilesCompact%' --and database_id=db_id('cardorder')
ORDER BY 2 DESC

restore filelistonly from disk='E:\dbbackup\test\test_backup_2022_01_07_155907_4455487.bak'、

-- 查看最占用CPU的慢SQL语句
SELECT TOP 10
   total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
   execution_count,
   (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
    (CASE WHEN statement_end_offset = -1
       THEN LEN(CONVERT(nvarchar(max), text)) * 2
       ELSE statement_end_offset
    END - statement_start_offset)/2)
   FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC

--Alwayson创建SID相同的用户 
[sp_help_revlogin] @login_name

--查看zaiqi订阅
SELECT *
FROM (
SELECT S.MatchData
    ,S.LastStatus
    ,CAST(s.MatchData AS XML).value('(/ScheduleDefinition/StartDateTime)[1]','date') AS StartDateTime
    ,CAST(s.MatchData AS XML).value('(/ScheduleDefinition/EndDate)[1]','date') AS EndDate
    ,c.[Path],rs.ReportID
    ,s.ExtensionSettings
    ,s.Parameters 
FROM [Catalog] AS c
JOIN ReportSchedule AS rs ON rs.ReportID = c.ItemID
JOIN Subscriptions AS s ON s.SubscriptionID = rs.SubscriptionID
WHERE 1=1
AND c.Path like  N'%Enterprise%'
and s.LastStatus <> N'已禁用'
) A
WHERE 1=1 and EndDate >getdate()
order by EndDate DESC