MS SQL - Revision history

Sstrutt: /* Commands and Maintenance */ Added "Show database recovery modes"

2019-09-22T13:42:18Z

Commands and Maintenance: Added "Show database recovery modes"

← Older revision		Revision as of 13:42, 22 September 2019
Line 149:		Line 149:
	EXECUTE sp_who2; /* Show current processes */		EXECUTE sp_who2; /* Show current processes */
	SELECT name,log_reuse_wait_desc FROM sys.databases; /* Show why transaction log can't grow */		SELECT name,log_reuse_wait_desc FROM sys.databases; /* Show why transaction log can't grow */
			SELECT name,recovery_model_desc FROM sys.databases ORDER BY name /* Show database recovery modes */
	</source>		</source>

2018-05-22T18:24:39Z

Commands and Maintenance: Added "Table Sizes"

← Older revision		Revision as of 18:24, 22 May 2018
Line 175:		Line 175:

	Source: http://stackoverflow.com/questions/5945360/sql-server-2008-how-to-query-all-databases-sizes		Source: http://stackoverflow.com/questions/5945360/sql-server-2008-how-to-query-all-databases-sizes

			=== Table Sizes ===
			Replace '''DatabaseName''' in the query below
			<source lang="sql">
			USE <DataBaseName>
			SELECT
			t.NAME AS TableName,
			i.name as indexName,
			sum(p.rows) as RowCounts,
			sum(a.total_pages) as TotalPages,
			sum(a.used_pages) as UsedPages,
			sum(a.data_pages) as DataPages,
			(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
			(sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,
			(sum(a.data_pages) * 8) / 1024 as DataSpaceMB
			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.NAME NOT LIKE 'dt%' AND
			i.OBJECT_ID > 255 AND
			i.index_id <= 1
			GROUP BY
			t.NAME, i.object_id, i.index_id, i.name
			ORDER BY
			object_name(i.object_id)
			</source>

			Source: https://stackoverflow.com/questions/2094436/how-to-find-largest-objects-in-a-sql-server-database

	=== Memory Usage ===		=== Memory Usage ===

2016-11-02T13:17:05Z

Database Sizes: Added note

← Older revision		Revision as of 13:17, 2 November 2016
Line 158:		Line 158:

	=== Database Sizes ===		=== Database Sizes ===
			Use the following in order to find the sizes of databases on your server.
	<source lang="sql">		<source lang="sql">
	SELECT		SELECT

2016-09-07T19:46:49Z

Commands and Maintenance: Added link

← Older revision		Revision as of 19:46, 7 September 2016
Line 138:		Line 138:

	== Commands and Maintenance ==		== Commands and Maintenance ==
			For performance issues, see http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

	Crib sheet of some very basic stuff, the syntax isn't exactly the same, but see also [[MySQL]]		Crib sheet of some very basic stuff, the syntax isn't exactly the same, but see also [[MySQL]]

2016-08-30T11:56:23Z

Daily Maintenance Plan: Added "Reorganise Index Task" transaction log note

← Older revision		Revision as of 11:56, 30 August 2016
Line 83:		Line 83:
	#* File extension: <code>bak</code>		#* File extension: <code>bak</code>
	#* File age: Select as appropriate		#* File age: Select as appropriate

			Be aware rhat the '''Reorganise Index Task''' can cause lots of activity to be generated in the transaction log, therefore if your recovery mode is ''Full'' (rather than ''Simple''), the transaction log can grow large very quickly. If performing on a database that's been running for some time this can be an issue. Consider setting the recovery mode to ''Simple'' for the first run, or perform at a time when if your transaction log fills you can recover without too much disruption to applications using the database.

	=== Hourly Transaction Log Backup ===		=== Hourly Transaction Log Backup ===

2016-08-22T15:30:30Z

Daily Maintenance Plan: Moved backup to start

← Older revision		Revision as of 15:30, 22 August 2016
Line 67:		Line 67:
	#* Delete all historical data (backup/restore, SQL Server Agent and Maintenance plan histories)		#* Delete all historical data (backup/restore, SQL Server Agent and Maintenance plan histories)
	#* Older than 2 weeks		#* Older than 2 weeks
			# '''Back Up Database Task'''
			#* Backup type: Full
			#* Databases: All databases
			#* Backup will expire: Leave unticked
			#* Create a backup file for every database: Update as appropriate
	# '''Reorganise Index Task'''		# '''Reorganise Index Task'''
	#* All user databases		#* All user databases
Line 74:		Line 79:
	#* Update: All existing statistics		#* Update: All existing statistics
	#* Scan type: Full scan		#* Scan type: Full scan
	~~# '''Back Up Database Task'''~~
	~~#* Backup type: Full~~
	~~#* Databases: All databases~~
	~~#* Backup will expire: Leave unticked~~
	~~#* Create a backup file for every database: Update as appropriate~~
	# '''Maintenance Cleanup Task'''		# '''Maintenance Cleanup Task'''
	#* Delete files of the following type: Backup files		#* Delete files of the following type: Backup files

2015-12-03T08:41:11Z

Rebuild All Indexes: Added single user bit

@@ Line 236: / Line 236: @@
 ==== Rebuild All Indexes ====
-Unless you are running SQL Enterprise, this needs to be run when your database is '''offline'''
+Unless you are running SQL Enterprise, this needs to be run when your database is '''single-user mode'''.
 <source lang="sql">
 USE DatabaseName                            /* Change to database name! */
@@ Line 259: / Line 260: @@
 DEALLOCATE TableCursor
 </source>
 [[Category:Applications]]
 [[Category:Microsoft]]

2015-12-02T23:32:28Z

Fragmentation: Added "Rebuild All Indexes"

@@ Line 217: / Line 217: @@
 ALTER INDEX IndexName ON TableName REORGANIZE;                       /* Online */
 ALTER INDEX IndexName ON TableName REBUILD;                          /* Offline */
-ALTER INDEX IndexName ON TableName REBUILD WITH (ONLINE = ON);      /* Online - 2008 Ent and above only */
+ALTER INDEX IndexName ON TableName REBUILD WITH (ONLINE = ON);       /* Online - 2008 Ent and above only */
 </source>
@@ Line 233: / Line 233: @@
 INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id
 WHERE indexstats.avg_fragmentation_in_percent > 50;
 </source>
 [[Category:Applications]]
 [[Category:Microsoft]]

2015-07-28T15:17:28Z

Commands and Maintenance: Added "Memory Usage"

@@ Line 146: / Line 146: @@
 SELECT name,log_reuse_wait_desc FROM sys.databases;                            /* Show why transaction log can't grow */
 </source>
 === Manual Backup ===
 <source lang="sql">
@@ Line 170: / Line 170: @@
 Source: http://stackoverflow.com/questions/5945360/sql-server-2008-how-to-query-all-databases-sizes
 === Fragmentation ===

2015-07-12T08:38:09Z

Commands and Maintenance: Added SELECT name,log_reuse_wait_desc FROM sys.databases;

← Older revision		Revision as of 08:38, 12 July 2015
Line 143:		Line 143:
	SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%' /* Show tables */		SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%' /* Show tables */
	SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' /* Show base tables only (not views etc) */		SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' /* Show base tables only (not views etc) */
	EXECUTE sp_who2; /* Show current ~~stored procedure status~~' */		EXECUTE sp_who2; /* Show current processes */
			SELECT name,log_reuse_wait_desc FROM sys.databases; /* Show why transaction log can't grow */
	</source>		</source>
			/
	=== Manual Backup ===		=== Manual Backup ===
	<source lang="sql">		<source lang="sql">