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 (NAFMS_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:xxxyourfolder";
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;
};
}