發表文章

目前顯示的是 2月, 2016的文章

SQL Server Release Memory

In my case, when server run my data transfer task, the server memory will get hight. I need to restart service. Another way in discuss : [ SQL Server Bulk Insert Physical Memory Issue ] I write it to a stored procedure, like bellow: USE [master] GO /****** Object: StoredProcedure [dbo].[SQL_Server_Release_Memory] Script Date: 2/24/2016 12:42:10 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /*---------------------------------------------------- description: SQL Server Release Memory author: Robin date: 2016/02/24 testing code: ----------------------------------------------------- EXEC SQL_Server_Release_Memory -----------------------------------------------------*/ CREATE PROCEDURE [dbo].[SQL_Server_Release_Memory] AS BEGIN /*Query physical memory in use*/ SELECT [description], value_in_use , (select top 1 physical_memory_in_use_kb/1024 FROM sys.dm_os_process_memory) as physical_memory_in_use_mb FROM sys.configurations WHERE name ='max server memory (MB)'

Database Copy Paste

圖片
This  T-SQL script is like copy A database paste to another database B in SQL Server . My SQL Server version is 2014, haven’t run another version. /*declare*/ DECLARE @path varchar(100)='E:\SQL_DATA\BACKUP\'; DECLARE @backfile varchar(100)=@path+'SourceDB.baktmp'; /*backup*/ BACKUP DATABASE [SourceDB] TO DISK = @backfile WITH NOFORMAT , NOINIT , NAME = N'SourceDB-Full Database Backup' , SKIP, NOREWIND, NOUNLOAD, STATS = 10 ; /*import*/ ALTER DATABASE [TargetDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE RESTORE DATABASE [TargetDB] FROM DISK = @backfile WITH FILE = 1 , MOVE N'TargetDB' TO N'E:\SQL_DATA\TargetDB.mdf' , MOVE N'TargetDB_log' TO N'E:\SQL_DATA\TargetDB_log.ldf' , NOUNLOAD, REPLACE, STATS = 5 ALTER DATABASE [TargetDB] SET MULTI_USER ; /*delete backup file*/ DECLARE @deldate varchar(10)= CONVERT(char, DATEADD(day, +1, GETDATE()), 111); EXECUTE master.dbo.xp_delete_file 0,@path,N'baktmp',@deldate; You