Kills every user session in one database, for example before a restore. Set the database name and run it first with @Apply = 0 to see the KILL commands it would run; set @Apply = 1 to run them. Your own session is skipped. Works on SQL Server 2016 to 2022 and later. — Kill…
sp_who3 works like sp_who2 but adds the running SQL text, wait type, reads, writes and elapsed time, using the sys.dm_exec_sessions and sys.dm_exec_requests DMVs. It only lists sessions that are running a request. For much more detail use Adam Machanic’s sp_WhoIsActive. Tested on SQL Server 2017 and 2025 and safe to run more than once. USE…
Who is connected, to which database, from where. Uses sys.dm_exec_sessions, which replaces the old sys.sysprocesses view. Works on SQL Server 2016 to 2022 and later. — Connections per database and login (SQL Server 2016-2022) SELECT DB_NAME(s.database_id) AS [Database Name], s.login_name AS [Login Name], s.host_name AS [Host Name], s.program_name AS [Program Name], COUNT(*) AS [No Of…
Error message when you open SQL Server Configuration Manager in SQL Server: “Cannot connect to WMI provider. You do not have permission or the server is unreachable” https://support.microsoft.com/en-us/kb/956013
Classic way to check the database is alright. DBCC CHECKDB(‘DatabaseName’) WITH NO_INFOMSGS, ALL_ERRORMSGS
In the SQL Server Management Studio, to find out details of the active transaction, execute following command DBCC opentran() You will get the detail of the active transaction, then from the SPID of the active transaction. For example, if SPID is 69 then execute the command as sp_who2 69 sp_lock 69 Now , you can…
SELECT SJ.name as job_name , SJ.enabled as is_job_enabled , SS.enabled as is_schedule_enabled /*, ISNULL(SJ.description, ”) as job_desc*/ , SS.name as schedule_name , CASE freq_type WHEN 1 THEN ‘Occurs on ‘ + STUFF(RIGHT(active_start_date, 4), 3,0, ‘/’) + ‘/’ + LEFT(active_start_date, 4) + ‘ at ‘ + REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3,…
A tail-log backup captures the log records that have not been backed up yet, so a restore loses nothing. Take one before restoring over a database, or after a failure if the log file can still be read. The database must be in FULL or BULK_LOGGED recovery. Tested on SQL Server 2017 and 2025. In…
Types of Replication Microsoft SQL Server provides the following types of replication for use in distributed applications: Transactional replication. For more information, see Transactional Replication. Merge replication. For more information, see Merge Replication. Snapshot replication. For more information, see Snapshot Replication.
These three issues are all related, yet very different. When we deal with reports of problems, it’s essential to clearly identify what is actually happening. Sometimes, we need clarification by asking about the symptoms, instead of accepting what the user says without delving any deeper. The root cause of a problem may not even be…
The DBCC commands a DBA still uses on SQL Server 2016 to 2022, grouped by job, with the old ones that have gone or been replaced at the end. Examples tested on SQL Server 2017 and 2025. Checking for corruption DBCC CHECKDB Checks the allocation, structure and logical integrity of every object in a database….
Data, log and total size of each user database in MB, with recovery model, compatibility level and state. Uses the file type rather than the .mdf/.ldf name, so extra .ndf files are counted. Works on SQL Server 2016 to 2022 and later. — Data, log and total size per user database in MB (SQL Server…
SELECT P.[publication] AS [Publication Name] ,A.[publisher_db] AS [Database Name] ,A.[article] AS [Article Name] ,A.[source_owner] AS [Schema] ,A.[source_object] AS [Table] FROM [distribution].[dbo].[MSarticles] AS A INNER JOIN [distribution].[dbo].[MSpublications] AS P ON (A.[publication_id] = P.[publication_id]) ORDER BY P.[publication], A.[article];
SELECT @@SERVERNAME AS server_name, sjs.last_run_outcome,sj.enabled, sj.name, sjs.step_id, sjs.step_name, sjs.database_name, CAST(CAST(sjs.last_run_date AS varchar(10)) AS datetime) AS failed_date FROM msdb.dbo.sysjobsteps AS sjs INNER JOIN msdb.dbo.sysjobs AS sj ON sjs.job_id = sj.job_id WHERE (sjs.last_run_date <> 0) AND (CAST(CAST(sjs.last_run_date AS varchar(10)) AS datetime) > GETDATE() – 2) ORDER BY sj.name
SELECT r.session_id, r.command, CONVERT(NUMERIC(6,2),r.percent_complete)AS [Percent Complete], CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time], CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours], CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2, CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END) FROM sys.dm_exec_sql_text(sql_handle))) AS [SQL Command] FROM sys.dm_exec_requests AS r WHERE r.command IN (‘RESTORE DATABASE’,’BACKUP DATABASE’)
select article, * from dbo.MSreplication_objects where article like ‘Table_name’ select sp.name, ss.srvname, sa.name from sysarticles sa join syssubscriptions ss on ss.artid = sa.artid join syspublications sp on sa.pubid = sp.pubid where sa.name like ‘table_name’
USE master; GO — Create the Event Session IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name=’DBDeletedCreated’) DROP EVENT SESSION DBDeletedCreated ON SERVER; GO CREATE EVENT SESSION DBDeletedCreated ON SERVER ADD EVENT sqlserver.object_created ( SET collect_database_name = (1) ACTION(sqlserver.nt_username,sqlserver.session_id,sqlserver.client_hostname,sqlserver.client_app_name,sqlserver.sql_text) WHERE object_type = ‘DATABASE’ ), ADD EVENT sqlserver.object_deleted( SET collect_database_name = (1) ACTION(sqlserver.nt_username,sqlserver.session_id,sqlserver.client_hostname,sqlserver.client_app_name,sqlserver.sql_text) WHERE object_type = ‘DATABASE’ )…
How to Fix SQL Server Database Error 3707? — 1. ALTER Database leads SET Emergency; GO — 2. ALTER Database leads Set Single_User GO — 3. DBCC CHECKDB (leads, REPAIR_ALLOW_DATA_LOSS) With ALL_ERRORMSGS; GO — 4. ALTER Database leads set multi_user GO
DBCC SHRINKDATABASE (N’Leads’, 0,TRUNCATEONLY)
Precious Lite 2025 | All Rights Reserved. Precious Lite theme by Flythemes