commande SQL formation

voir version:

select @@version

passer une base en mode single user

ALTER DATABASE [toto] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

passer en mode multi user

alter database [toto] set MULTI_USER

voir la configuration exec sp_configure

activé affichage des option avancé

exec sp_configure 'show advanced options', 1;
REconfigure;

autorisé la mise en autonomie partielle des bases

exec sp_configure 'contained database authentication',1;
reconfigure

exec sp_configure 'cost threshold for parallelism',12
exec sp_configure 'max degree of parallelism',2
exec sp_configure 'max server memory (MB)', 4096
exec sp_configure'optimize for ad hoc workloads',1
exec sp_configure'backup compression default',1
exec sp_configure'backup checksum default',1
exec sp_configure
reconfigure

Backup

BACKUP DATABASE [toto] TO DISK = 'c:\toto.bak' with Compression

ajouter filegroup

USE [master]
GO
ALTER DATABASE [DB_GRAND_HOTEL] ADD FILEGROUP [FG_DATA]
GO
ALTER DATABASE [DB_GRAND_HOTEL] ADD FILE ( NAME = N'FG_DATA_1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019FBIN2\MSSQL\DATA\FD_DATA_1.mdf' , SIZE = 102400KB , FILEGROWTH = 65536KB ) TO FILEGROUP [FG_DATA]
GO
ALTER DATABASE [DB_GRAND_HOTEL] ADD FILE ( NAME = N'FG_DATA_2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019FBIN2\MSSQL\DATA\FD_DATA_2.mdf' , SIZE = 102400KB , FILEGROWTH = 65536KB ) TO FILEGROUP [FG_DATA]
GO

setter filegroup par default

USE [DB_GRAND_HOTEL]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'FG_DATA') ALTER DATABASE [DB_GRAND_HOTEL] MODIFY FILEGROUP [FG_DATA] DEFAULT
GO

modifier taille de fichier

USE [DB_GRAND_HOTEL]
GO
DBCC SHRINKFILE (N'DB_GRAND_HOTEL' , 10)
GO
USE [master]
GO
ALTER DATABASE [DB_GRAND_HOTEL] MODIFY FILE ( NAME = N'DB_GRAND_HOTEL_log', SIZE = 102400KB )
GO

voir nombre de page utilisé et temps

SET STATISTCS IO ON
SET STATISTCS TIME ON