Just so I can find this script when I need it …
--MoveDataFile.sql
USE master;
GO
-- 1 alter the metadata to the new path
ALTER DATABASE [SomeDatabaseName] MODIFY FILE (NAME = SomeLogicalFileName, FILENAME = 'F:\SQLData\SomeFileName.mdf');
-- 2 take database offline
ALTER DATABASE [SomeDatabaseName] SET OFFLINE;
-- 3 move the datafile via windows explorer
-- 4 bring database online
ALTER DATABASE [SomeDatabaseName] SET ONLINE;
The SQL Server “Recovery Time Interval” setting used to be ‘0’ by default, which could have a performance impact during a Checkpoint, by amplifying lazywriter contention.
Indirect checkpointing can alleviate this and has been available since SQL Server 2012. Indeed the default value was changed to ’60’ (seconds) from SQL Server 2016 onwards.
I like this. It’s succinct
EXEC sp_MSforeachdb 'use [?] if db_id() > 4
SELECT db_name() [database], * FROM sys.triggers
WHERE is_disabled = 1';
Database Consultant 