Lập Trình .NET

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 31457280KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 62914560KB )
GO

USE [tempdb];
EXEC sp_helpfile;

SELECT name, physical_name AS CurrentLocation, state_desc  
FROM sys.master_files  
WHERE database_id = DB_ID(N'tempdb');
GO

-- Stop SQL Server
-- Copy files to new location
-- Start SQL Server

USE master;
GO

ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\MSSQL\DATA\tempdb.mdf');
GO

ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'E:\MSSQL\DATA\templog.ldf');
GO

-- Restart SQL Server


-- https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-system-databases?view=sql-server-2017
-- https://www.mytechmantra.com/LearnSQLServer/How-to-Move-TempDB-to-New-Drive-in-SQL-Server/

select EOMONTH(GETDATE())

using IWshRuntimeLibrary;

string drive = ConfigurationManager.AppSettings["Drive"];
string folder = ConfigurationManager.AppSettings["Folder"];
string userName = ConfigurationManager.AppSettings["Username"];
string password = ConfigurationManager.AppSettings["Password"];

IWshNetwork_Class network = new IWshNetwork_Class();

try
{
 network.RemoveNetworkDrive(drive);
}
catch (Exception ex)
{

}

try
{
 network.MapNetworkDrive(drive, folder, Type.Missing, userName, password);
}
catch (Exception ex)
{

}

var stream = File.Open(filePath, FileMode.Open,
 FileAccess.Read,
 FileShare.ReadWrite);

SELECT OBJECT_NAME([ps].[object_id], [ps].[database_id]) 
            AS [ProcedureName]
 , [ps].[execution_count] AS [ProcedureExecutes]
 , [qs].[plan_generation_num] AS [VersionOfPlan]
 , [qs].[execution_count] AS [ExecutionsOfCurrentPlan]
 , SUBSTRING ([st].[text], 
  ([qs].[statement_start_offset] / 2) + 1, 
     ((CASE [statement_end_offset] 
   WHEN -1 THEN DATALENGTH ([st].[text]) 
      ELSE [qs].[statement_end_offset] END 
   - [qs].[statement_start_offset]) / 2) + 1) 
      AS [StatementText]
    , [qs].[statement_start_offset] AS [offset]
    , [qs].[statement_end_offset] AS [offset_end]
    , [qp].[query_plan] AS [Query Plan XML]
    , [qs].[query_hash] AS [Query Fingerprint]
    , [qs].[query_plan_hash] AS [Query Plan Fingerprint]
FROM [sys].[dm_exec_procedure_stats] AS [ps]
JOIN [sys].[dm_exec_query_stats] AS [qs] ON [ps].[plan_handle] = [qs].[plan_handle]
CROSS APPLY [sys].[dm_exec_query_plan] ([qs].[plan_handle]) AS [qp]
CROSS APPLY [sys].[dm_exec_sql_text] ([qs].[sql_handle]) AS [st]
WHERE [ps].[database_id] = DB_ID()
ORDER BY [ProcedureName], [qs].[statement_start_offset];