Posts

Showing posts from February, 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

Image
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 nee…