SQLDatabases - Revision history

Aleks at 18:05, 18 July 2024

2024-07-18T18:05:01Z

← Older revision		Revision as of 19:05, 18 July 2024
Line 87:		Line 87:
	Check SQL conectivity:		Check SQL conectivity:
	[https://github.com/microsoft/CSS_SQL_Networking_Tools MS Github docs]		[https://github.com/microsoft/CSS_SQL_Networking_Tools MS Github docs]

			== Transaction Log Shipping ==
			To fix when transaction log shipping may have failed one option is to complete a restore of the database to the secondary server from a backup of the primary.

			First disable all the transaction shipping jobs on both SQL server instances

			Then take full backup from primary database

			Restore the backup over the secondary database with the "restore with standby" option

			The log shipping jobs can then be re-enabled. They will clear out all the old log transaction files and start with a new chain.

2023-11-15T20:32:21Z

14 revisions imported

← Older revision	Revision as of 21:32, 15 November 2023
(No difference)

2023-07-04T16:26:25Z

← Older revision		Revision as of 17:26, 4 July 2023
Line 82:		Line 82:
	**^this command can cause loss of data which may then require to be restored if needed.		**^this command can cause loss of data which may then require to be restored if needed.
	*ALTER DATABASE db_name SET MULTI_USER		*ALTER DATABASE db_name SET MULTI_USER

			==Network Check==

			Check SQL conectivity:
			[https://github.com/microsoft/CSS_SQL_Networking_Tools MS Github docs]

2023-03-08T13:18:57Z

SQL CPU usage query

← Older revision		Revision as of 14:18, 8 March 2023
Line 21:		Line 21:
	*SELECT scheduler_id, cpu_id, status, is_online FROM sys.dm_os_schedulers GO		*SELECT scheduler_id, cpu_id, status, is_online FROM sys.dm_os_schedulers GO

			When this still fails with the following error, you can fix it:

			error = "ALTER DATABASE failed because a lock could not be placed on database 'dbname' Try again later."

			you can run the following command to find out who is keeping a lock on your database:

			*EXEC sp_who2


			And use whatever SPID you find in the following command:

			*KILL <SPID>

			Then run the ALTER DATABASE command again. It should now work.

	== SQL Maintenance ==		== SQL Maintenance ==

2023-03-08T11:30:02Z

← Older revision		Revision as of 12:30, 8 March 2023
Line 25:		Line 25:
	Use following website for setup of good practice maintenance tasks.		Use following website for setup of good practice maintenance tasks.
	* https://ola.hallengren.com/		* https://ola.hallengren.com/

			===SQL Instance Updates===
			Cumulative updates for SQL server do not show in windows / SQL server installer update checks

			Access the Cumulative update packages here (SQL 2019 example )https://www.microsoft.com/en-us/download/details.aspx?id=100809

2023-03-07T11:38:19Z

← Older revision		Revision as of 12:38, 7 March 2023
Line 11:		Line 11:


	== ~~Stuck~~ database not going offline ==		== SQL database not going offline ==

	You can use the following command to force a database to go offline if standard way is not working or is taking a long time:		You can use the following command to force a database to go offline if standard way is not working or is taking a long time:

2023-03-07T11:38:00Z

← Older revision		Revision as of 12:38, 7 March 2023
Line 9:		Line 9:

	If desperate you can change the backup type of the database to "simple" then in the shrink files section select log file and then In Shrink action choose Reorganize pages before releasing unused space, set the desired size of the file and then click OK. After action is completed you can change backup to full and perform a backup as previous backups will not be ale to restore to the current state of the database.		If desperate you can change the backup type of the database to "simple" then in the shrink files section select log file and then In Shrink action choose Reorganize pages before releasing unused space, set the desired size of the file and then click OK. After action is completed you can change backup to full and perform a backup as previous backups will not be ale to restore to the current state of the database.


			== Stuck database not going offline ==

			You can use the following command to force a database to go offline if standard way is not working or is taking a long time:
			*ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE

2021-11-16T14:23:48Z

SQL Suspect Database

← Older revision		Revision as of 15:23, 16 November 2021
Line 55:		Line 55:
	*ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE		*ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
	*DBCC CHECKDB ('db_name', REPAIR_ALLOW_DATA_LOSS)		*DBCC CHECKDB ('db_name', REPAIR_ALLOW_DATA_LOSS)
			**^this command can cause loss of data which may then require to be restored if needed.
	*ALTER DATABASE db_name SET MULTI_USER		*ALTER DATABASE db_name SET MULTI_USER

2021-11-16T13:01:56Z

SQL Suspect Database

← Older revision		Revision as of 14:01, 16 November 2021
Line 43:		Line 43:
	If a database appears as suspect after server restore or other issue and the database is flagged as suspect you can try to recover the database with the following procedure.		If a database appears as suspect after server restore or other issue and the database is flagged as suspect you can try to recover the database with the following procedure.

	Run the following SQL Queries on the database:		Run the following SQL Queries on the database: (replace db_name with the name of the database)

	Can start with		Can start with

2021-11-16T12:54:20Z

← Older revision		Revision as of 13:54, 16 November 2021
Line 38:		Line 38:
	*ALTER USER <Name of SQL account>		*ALTER USER <Name of SQL account>
	*WITH LOGIN = <Name of orphaned DB account>		*WITH LOGIN = <Name of orphaned DB account>


			== SQL Suspect Database ==
			If a database appears as suspect after server restore or other issue and the database is flagged as suspect you can try to recover the database with the following procedure.

			Run the following SQL Queries on the database:

			Can start with
			*EXEC sp_resetstatus 'db_name':

			If this does not work then try the following;

			*ALTER DATABASE db_name SET EMERGENCY
			*DBCC CHECKDB ('db_name')
			**Check results to see if this may have fixed the database
			*ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
			*DBCC CHECKDB ('db_name', REPAIR_ALLOW_DATA_LOSS)
			*ALTER DATABASE db_name SET MULTI_USER