Labels

admin (1) aix (1) alert (1) always-on (2) Architecture (1) aws (3) Azure (1) backup (3) BI-DWH (10) Binary (3) Boolean (1) C# (1) cache (1) casting (3) cdc (1) certificate (1) checks (1) cloud (3) cluster (1) cmd (7) collation (1) columns (1) compilation (1) configurations (7) Connection-String (2) connections (6) constraint (6) copypaste (2) cpu (2) csv (3) CTE (1) data-types (1) datetime (23) db (547) DB2 (1) deadlock (2) Denali (7) device (6) dotNet (5) dynamicSQL (11) email (5) encoding (1) encryption (4) errors (124) excel (1) ExecutionPlan (10) extended events (1) files (7) FIPS (1) foreign key (1) fragmentation (1) functions (1) GCP (2) gMSA (2) google (2) HADR (1) hashing (3) in-memory (1) index (3) indexedViews (2) insert (3) install (10) IO (1) isql (6) javascript (1) jobs (11) join (2) LDAP (2) LinkedServers (8) Linux (15) log (6) login (1) maintenance (3) mariadb (1) memory (4) merge (3) monitoring (4) MSA (2) mssql (444) mssql2005 (5) mssql2008R2 (20) mssql2012 (2) mysql (36) MySQL Shell (5) network (1) NoSQL (1) null (2) numeric (9) object-oriented (1) offline (1) openssl (1) Operating System (4) oracle (7) ORDBMS (1) ordering (2) Outer Apply (1) Outlook (1) page (1) parameters (2) partition (1) password (1) Performance (103) permissions (10) pivot (3) PLE (1) port (4) PostgreSQL (14) profiler (1) RDS (3) read (1) Replication (12) restore (4) root (1) RPO (1) RTO (1) SAP ASE (48) SAP RS (20) SCC (4) scema (1) script (8) security (10) segment (1) server (1) service broker (2) services (4) settings (75) SQL (74) SSAS (1) SSIS (19) SSL (8) SSMS (4) SSRS (6) storage (1) String (35) sybase (57) telnet (2) tempdb (1) Theory (2) tips (120) tools (3) training (1) transaction (6) trigger (2) Tuple (2) TVP (1) unix (8) users (3) vb.net (4) versioning (1) windows (14) xml (10) XSD (1) zip (1)

Move SQL server database files

-- 1. set DB offline:
ALTER DATABASE MyDataBase SET OFFLINE;

-- 2. move the files
ALTER DATABASE MyDataBase MODIFY FILE ( NAME = 'MyBbFile', FILENAME = 'E:\SQL\Data\MyBbFile.mdf' );
ALTER DATABASE MyDataBase MODIFY FILE ( NAME = 'MyBbFile_log', FILENAME = 'E:\SQL\Log\MyBbFile_log.ldf' );
ALTER DATABASE MyDataBase MODIFY FILE ( NAME = 'MyBbFile_BLOBStorage', FILENAME = 'E:\SQL\BLOB\MyBbFile.ndf' );

-- 3. copy-paste the files to the new folder (via Windows)

-- 4. set DB online:
ALTER DATABASE MyDataBase SET ONLINE;

-- get data of the DB files:
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'MyDataBase');

Get tables size

SELECT t.NAME AS TableName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB, 
SUM(a.used_pages) * 8 AS UsedSpaceKB, 
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID 
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.is_ms_shipped = 0
GROUP BY t.Name, p.Rows
ORDER BY TotalSpaceKB desc

Get aggregations of few columns for each record - using 'Values' operator

SELECT ValuesTestID, ValuesTestName, 
IntCol1, IntCol2, IntCol3, IntCol4, IntCol5, IntCol6,
( Select MAX(ValuesTestName)
From    (Values (IntCol1), (IntCol2), (IntCol3), (IntCol4), (IntCol5), (IntCol6)) 
UniqueColumn(ValuesTestName)
) MaxIntCol,
( Select SUM(ValuesTestName)
From    (Values (IntCol1), (IntCol2), (IntCol3), (IntCol4), (IntCol5), (IntCol6)) 
UniqueColumn(ValuesTestName)
) SumIntCol
FROM ValuesTest

Get DBs files locations and drive details

SELECT DISTINCT 
dovs.database_id as database_id,
DB_NAME(dovs.database_id) as DBName,
mf.physical_name as PhysicalFileLocation,
dovs.logical_volume_name as LogicalDriveName,
dovs.volume_mount_point as Drive,
CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY DB_NAME(dovs.database_id)

Get Disk Space on Server

EXEC MASTER..xp_fixeddrives