[私人小抄] MSSQL 維護相關

[私人小抄] MSSQL 維護相關

--mdf檔案 換路徑

/*

--如果不能offline 手動砍
EXEC sp_who2
KILL [session_id]
*/

--檢查目前路徑
SELECT name,physical_name
FROM AAAAA.sys.database_files

/*

--修改路徑
ALTER DATABASE AAAAA
MODIFY FILE (NAME = AAAAA, FILENAME = 'D:\SqlData\Stage_YiChiStore.mdf');
ALTER DATABASE AAAAA
MODIFY FILE (NAME = AAAAA_Log, FILENAME = 'D:\SqlData\Stage_YiChiStore_log.ldf');

--將 DB offline
ALTER DATABASE AAAAA SET OFFLINE WITH ROLLBACK IMMEDIATE  


-- 將檔案搬到新的路徑下


--online成功 就算完成
ALTER DATABASE AAAAA SET ONLINE
*/

--處理log檔案過大
USE AAAAA;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AAAAA
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AAAAA_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AAAAA
SET RECOVERY FULL;
GO

 

 

如果內容有誤請多鞭策謝謝