SqlServer 数据库服务器运用

1.压缩数据库日志文件

当数据库服务器满了之后，可用以下sql进行空间释放。

USE [master]
GO
ALTER DATABASE AFMS SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE AFMS SET RECOVERY SIMPLE
GO
USE AFMS
GO
DBCC SHRINKFILE (N'AFMS_Log' , 11, TRUNCATEONLY)
GO
USE [master]
GO
ALTER DATABASE AFMS SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE AFMS SET RECOVERY FULL
GO

如果对日志文件的逻辑名称不清楚的话，用下面这句sql可以直接查出来：

SELECT file_id, name FROM sys.database_files 

2.查询数据库死锁sql

SELECT  
 der.[session_id],der.[blocking_session_id],  
 sp.lastwaittype,sp.hostname,sp.program_name,sp.loginame,  
 der.[start_time] AS '开始时间',  
 der.[status] AS '状态',  
 dest.[text] AS 'sql语句',  
 DB_NAME(der.[database_id]) AS '数据库名',  
 der.[wait_type] AS '等待资源类型',  
 der.[wait_time] AS '等待时间',  
 der.[wait_resource] AS '等待的资源',  
 der.[logical_reads] AS '逻辑读次数'  
 FROM sys.[dm_exec_requests] AS der  
 INNER JOIN master.dbo.sysprocesses AS sp ON der.session_id=sp.spid  
 CROSS APPLY  sys.[dm_exec_sql_text](der.[sql_handle]) AS dest  
 WHERE [session_id]>50 AND session_id<>@@SPID  
 ORDER BY der.[session_id]  
 GO  

3.备份数据库

declare @filename varchar(255)
declare @date datetime
select @date=getdate()
select @filename='E:\每天晚上备份数据库\NDHG_BusinessData-'+CAST(DATEPART(yyyy,@date) as varchar)+'-'+CAST(DATEPART(mm,@date) as 
varchar)+'-'+CAST(DATEPART(dd,@date) as varchar)+'.bak'
backup database NDHG_BusinessData to disk=@filename with init 

select @filename='E:\每天晚上备份数据库\NDHG_SystemDataBase-'+CAST(DATEPART(yyyy,@date) as varchar)+'-'+CAST(DATEPART(mm,@date) as 
varchar)+'-'+CAST(DATEPART(dd,@date) as varchar)+'.bak'
backup database NDHG_SystemDataBase to disk=@filename with init 
go

4.删除备份文件

<# :
cls
@echo off
rem 删除指定目录\文件夹下指定天数之前创建的文件
mode con lines=5000
set #=Any question&set @=WX&set $=Q&set/az=0x53b7e0b4
title %#% +%$%%$%/%@% %z%
cd /d "%~dp0"
powershell -NoProfile -ExecutionPolicy bypass "Invoke-Command -ScriptBlock ([ScriptBlock]::Create([IO.File]::ReadAllText('%~f0',[Text.Encoding]::Default)))"
echo;%#% +%$%%$%/%@% %z%
pause
exit
#>
$path="D:\xxx\yourfolder";
if(-not (test-path -liter $path)){Write-host ('"'+$path+'" not found');exit;};
$ago=-180;
$agoday=get-date ((get-date).adddays($ago).toSTring('yyyy-MM-dd')+' 00:00:00');
$files=@(dir -liter $path -recurse|?{$_ -is [System.IO.FileInfo]});
for($i=0;$i -lt $files.length;$i++){
if($files[$i].CreationTime -le $agoday){
write-host ('['+$files[$i].CreationTime.toSTring('yyyy-MM-dd')+']'+$files[$i].FullName);
Remove-Item -liter $files[$i].FullName -force -whatif;
};
}

解决恢复挂起的状态

RESTORE database   dbname with recovery

ALTER DATABASE [ZSYG_SystemDataBase] SET SINGLE_USER;

DBCC CHECKDB([ZSYG_SystemDataBase], REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE [ZSYG_SystemDataBase] SET MULTI_USER