Mastering DBA Troubleshooting (Part 1)
Arunagiri Gopal
Senior Manager – Database Management | Data Analyst | AI & Azure Cost Optimization Specialist | Data Architecture | Digital Transformation | IoT Solutions | Automation Expert | SQL Optimization Specialist | Blogger
1. ## Overall, this query provides a summary of the current database connections, showing the number of connections for each database and the associated login names. It can be useful for monitoring and managing database connections in a SQL Server instance.
SELECT DB_NAME(dbid) AS DBName,
COUNT(dbid) AS NumberOfConnections,
loginame
FROM sys.sysprocesses
GROUP BY dbid, loginame
ORDER BY DB_NAME(dbid)
2. ## The provided SQL query is used to retrieve information about the encryption state of databases in a SQL Server instance. Here's a short description of the query:
## Overall, this query helps you monitor the encryption status of databases in a SQL Server instance and provides details about their encryption settings.
SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
WHEN '0' THEN 'No database encryption key present, no encryption'
WHEN '1' THEN 'Unencrypted'
WHEN '2' THEN 'Encryption in progress'
WHEN '3' THEN 'Encrypted'
WHEN '4' THEN 'Key change in progress'
WHEN '5' THEN 'Decryption in progress'
WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed'
ELSE 'No Status'
END,
percent_complete, create_date, key_algorithm, key_length,
encryptor_thumbprint, encryptor_type FROM sys.dm_database_encryption_keys
3. LOG SHIPPING: This query is likely used to retrieve information or status related to log shipping for a primary database named "STRGD12" in the context of SQL Server. It may be used for monitoring and managing log shipping for disaster recovery purposes, ensuring data consistency, and maintaining high availability of the database.
sp_help_log_shipping_monitor_primary STRGD12,SSS
4. The "sp_help_log_shipping_monitor_secondary" procedure is typically used to check the status of log shipping on the secondary server, view information about the log shipping configuration, and monitor the synchronization and health of the secondary database for disaster recovery purposes.
EXEC sp_help_log_shipping_monitor_secondary GDJNT154,STRGD12_sss
5. The query essentially retrieves a list of backup operations from the "backupset" table and sorts them in descending order based on the finish date, so you can see the most recent backup operations at the top of the result set. This information is useful for monitoring and managing database backups.
select name,database_name,backup_Start_date,backup_finish_date
from msdb.dbo.backupset order by backup_finish_date desc
6. The xp_ReadErrorLog extended stored procedure in SQL Server is used to read and retrieve error log entries. In the query you've provided:
The Query is using xp_ReadErrorLog to read error log entries from October 24, 2023, to October 25, 2023, in the current SQL Server error log. This can be helpful for troubleshooting and diagnosing issues that occurred during that time frame.
xp_ReadErrorLog 1,1, NULL, NULL, '20231024', '20231025'
7. The Query is using xp_ReadErrorLog to read error log entries from October 18, 2023, to October 19, 2023, in the current SQL Server error log. This can be useful for reviewing error log entries that occurred during that specific time frame, which can be helpful for troubleshooting and diagnosing issues.
xp_ReadErrorLog 0,1, NULL, NULL, '20231018', '20231019'
8. The command you've provided, "FSUTIL FSINFO NTFSINFO N:", is used to retrieve NTFS (New Technology File System) information for a specific drive or volume represented by "N:". Here's what each part of the command does:
FSUTIL FSINFO NTFSINFO N:
9. This SQL query retrieves information about CPU usage, wait time, elapsed time, reads, writes, and the number of connections for different programs accessing a SQL Server instance. Here's a breakdown of what the query does:
This query can be useful for performance analysis and identifying which programs or applications are consuming the most CPU resources in a SQL Server instance, helping with troubleshooting and optimization efforts.
SELECT CPU = SUM(cpu_time) ,WaitTime = SUM(total_scheduled_time) ,ElapsedTime = SUM(total_elapsed_time) ,Reads = SUM(num_reads) ,Writes = SUM(num_writes) ,Connections = COUNT(1) ,Program = program_name
FROM sys.dm_exec_connections con
LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = con.session_id
GROUP BY program_name ORDER BY cpu DESC
10. This SQL query retrieves information about the most recent log backups for SQL Server databases.
This query is helpful for monitoring and reviewing the most recent log backups performed on SQL Server databases, which is crucial for ensuring data integrity and disaster recovery readiness.
SELECT top 10 bs.user_name,bs.database_name, bs.backup_start_date, bs.backup_finish_date, bm.physical_device_name, bm.logical_device_name
FROM msdb..backupset bs
JOIN msdb..backupmediafamily bm
WHERE bs.type='L' ORDER BY bs.backup_start_date desc, bs.database_name
11. This query is useful for monitoring and reviewing the most recent full database backup of a specific database, which is essential for backup management and disaster recovery planning.
SELECT bs.user_name,bs.database_name, bs.backup_start_date, bs.backup_finish_date, bm.physical_device_name, bm.logical_device_name FROM msdb..backupset bs JOIN msdb..backupmediafamily bm
WHERE bs.type='d' and bs.database_name='MGKDB'
ORDER BY bs.backup_start_date desc, bs.database_name
12. This query is useful for monitoring databases and determining when the last full backup was performed for each database, providing valuable information for backup management and data recovery planning.
SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup, ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as LastBackupDate
FROM master.dbo.sysdatabases B
LEFT OUTER JOIN
msdb.dbo.backupset A ON
A.database_name = B.name AND
A.type = 'D'
13. The query essentially identifies sessions that are currently blocked by other sessions and returns information about those blocked sessions, specifically their session IDs (spid). This information can be valuable for troubleshooting and identifying blocking issues within a SQL Server instance, allowing you to take appropriate actions to resolve the blocking situations.
Select spid,blocked from sysprocesses where blocked>0
14. The query returns the "Buffer cache hit ratio" value for the Buffer Manager performance counter. This ratio is a measure of how often SQL Server can find a requested data page in its buffer cache without having to read it from disk. A high hit ratio indicates efficient memory usage, while a low ratio may suggest performance issues related to disk I/O. Monitoring this counter can help in assessing and optimizing SQL Server's memory management.
SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Buffer cache hit ratio'
15. This query is useful for identifying which user databases are consuming the most CPU resources on the SQL Server instance, which can be helpful for performance tuning and troubleshooting. The results will show the top CPU-consuming databases with their names, CPU times, and percentages of total CPU usage.
WITH DB_CPU_Stats AS
(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName],
SUM(total_worker_time) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N'dbid') AS F_DB GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
DatabaseName, [CPU_Time_Ms],
CAST([CPU_Time_Ms] 1.0 / SUM([CPU_Time_Ms]) OVER() 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats WHERE DatabaseID > 4 -- system databases
AND DatabaseID <> 32767 -- ResourceDB
ORDER BY row_num OPTION (RECOMPILE);
16. This information can be valuable for monitoring and troubleshooting the performance of your SQL Server instance, identifying blocking issues, and understanding the current workload on the server.
select * from sys.dm_exec_requests;
17. These are some of the common parameters that you can use with sp_who to get specific information about active sessions on your SQL Server. The exact list of parameters and their behavior may vary depending on the version of SQL Server you are using.
a. return a list of all active processes on the server.
EXEC sp_who
b. specify a particular Session ID (SPID) to get information about a specific session.
EXEC sp_who 51
c. Common status values include 'RUNNABLE', 'SLEEPING', 'BACKGROUND', etc.
EXEC sp_who @status = 'RUNNABLE'
d. Filter the results based on the login name of the sessions.
EXEC sp_who @loginame = 'myuser'
e. Can filter the results based on the hostname or machine name from which the sessions are connected.
EXEC sp_who @hostname = 'mycomputer'
f. can filter the results based on the name of the database to which the sessions are connected.
EXEC sp_who @dbname = 'mydatabase'
g. The ecid (Execution Context ID) parameter allows you to filter results by the execution context ID.
EXEC sp_who @ecid = 1
-- Define variables to hold parameter values
DECLARE @spid INT = NULL; -- Set to NULL if not filtering by SPID
DECLARE @status VARCHAR(20) = NULL; -- Set to NULL if not filtering by status
DECLARE @loginame VARCHAR(50) = NULL; -- Set to NULL if not filtering by login name
DECLARE @hostname VARCHAR(50) = NULL; -- Set to NULL if not filtering by hostname
DECLARE @dbname VARCHAR(50) = NULL; -- Set to NULL if not filtering by database name
DECLARE @ecid INT = NULL; -- Set to NULL if not filtering by ECID
-- Execute sp_who with optional parameters
EXEC sp_who @spid = @spid,
@status = @status,
@loginame = @loginame,
@hostname = @hostname,
@dbname = @dbname,
@ecid = @ecid;
-- Add additional SQL statements here to format or manipulate the results as needed
18. The DBCC INPUTBUFFER command in SQL Server is used to retrieve the last statement that was sent from a client to SQL Server for a specific session (identified by its SPID - Server Process ID).
NOTE: Need appropriate permissions to execute DBCC INPUTBUFFER as it provides access to potentially sensitive information about SQL Server sessions.
DBCC INPUTBUFFER (session_id)
19. Final result of this query will be a list of processes (sessions) that are currently connected to the 'TMRDEV' database, along with various details about each process.
SELECT *
FROM sys.sysprocesses
WHERE dbid IN (
SELECT database_id
FROM sys.databases
WHERE name = 'TMRDEV'
)
20. Retrieves information about database file segments from the sysfiles system view in SQL Server. It calculates various metrics such as file size, space used, available space, and percent used for each file segment.
SELECT
RTRIM(name) AS [Segment Name], -- Name of the file segment
groupid AS [Group Id], -- Group ID of the file segment
filename AS [File Name], -- File name
CAST(size/128.0 AS DECIMAL(10,2)) AS [Size in MB], -- File size in MB
CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Space Used], -- Space used in MB
CAST(size/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space], -- Available space in MB
CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent Used] -- Percent used
FROM sysfiles
ORDER BY groupid DESC
21. The SQL commands are altering the size of a database file named 'FF_SharedB_Q' within the 'master' database. Specifically, it's modifying the size of the file to 170369024 kilobytes (KB), which is approximately 166.05 gigabytes (GB).
USE [master];
GO
ALTER DATABASE [Mst_SharedB_Q]
MODIFY FILE (
NAME = N'Mst_SharedB_Q',
SIZE = 170369024KB
);
GO
22. This SQL query retrieves information about database file segments in SQL Server where the file name contains the substring 'templog'.
This query provides valuable information about file segments with 'templog' in their names, allowing you to monitor and manage them effectively.
-- Retrieve information about database file segments
SELECT
RTRIM(name) AS [Segment Name], -- The name of the file segment
groupid AS [Group Id], -- The group ID of the file segment
filename AS [File Name], -- The name of the database file
CAST(size/128.0 AS DECIMAL(10,2)) AS [Size in MB], -- File size in MB
CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Space Used], -- Space used in MB
CAST(size/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space], -- Available space in MB
CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent Used] -- Percent used
FROM sysfiles
-- Filter the results to include only file segments with names containing 'templog'
WHERE name LIKE '%templog%'
-- Order the results by group ID in descending order
ORDER BY groupid DESC;
23. The SQL query retrieves various information about SQL Server sessions, including details about their execution, blocking, and resource usage. Here's the query with explanations:
This query provides valuable information about active sessions, their resource usage, and the SQL statements they are executing, which can be useful for monitoring and troubleshooting purposes in SQL Server.
-- Calculate the time difference between 'start_time' and the current time in minutes
SELECT
DATEDIFF(minute, start_time, GETDATE()) AS MinutesPassed,
dess.session_id,
blocking_session_id,
command,
login_name,
db_name(der.database_id) AS DBName,
program_name,
host_name,
der.cpu_time,
wait_type,
login_time,
GETDATE() AS CurrentTime,
der.status,
der.logical_reads,
wait_resource,
percent_complete,
DATEDIFF(minute, login_time, GETDATE()) AS StartTime_Passed,
text
FROM
sys.dm_exec_requests der
JOIN
sys.dm_exec_sessions dess ON der.session_id = dess.session_id
CROSS APPLY
sys.dm_exec_sql_text(der.sql_handle) des
WHERE
der.session_id > 50
AND der.session_id <> @@SPID
-- Order the results by 'cpu_time' in descending order
ORDER BY
der.cpu_time DESC;
24. The SQL query retrieves information about currently executing SQL Server sessions along with various details about their execution, including blocking information and query text.
i. However, be cautious when including execution plans as they can be quite large and impact performance.
SELECT
s.session_id,
r.STATUS,
r.blocking_session_id AS 'blocked_by',
r.wait_type,
r.wait_resource,
CONVERT(VARCHAR, DATEADD(ms, r.wait_time, 0), 8) AS 'wait_time',
r.cpu_time,
r.logical_reads,
r.reads,
r.writes,
CONVERT(VARCHAR, DATEADD(ms, r.total_elapsed_time, 0), 8) AS 'elapsed_time',
CAST((
'<?query -- ' + CHAR(13) + CHAR(13) + Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
(
CASE r.statement_end_offset
WHEN - 1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset
) / 2
) + 1) + CHAR(13) + CHAR(13) + '--?>'
) AS XML) AS 'query_text',
COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS 'stored_proc',
r.command,
s.login_name,
s.host_name,
s.program_name,
s.host_process_id,
s.last_request_end_time,
s.login_time,
r.open_transaction_count
FROM
sys.dm_exec_sessions AS s
INNER JOIN
sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) AS st
--OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp -- uncomment (2) if you want to see plan
WHERE
r.wait_type NOT LIKE 'SP_SERVER_DIAGNOSTICS%'
OR r.session_id != @@SPID
ORDER BY
r.cpu_time DESC,
r.STATUS,
r.blocking_session_id,
s.session_id;
25. The SQL query retrieves information about sessions that are currently being blocked by other sessions in SQL Server. It includes details about the session, the blocking session, the database name, host name, login name, and the query text.
-- Retrieve information about blocked sessions
Recommended by LinkedIn
SELECT
r.session_id,
r.blocking_session_id,
DB_NAME(r.database_id) AS Database_Name,
s.host_name,
s.login_name,
s.original_login_name,
r.status,
r.command,
r.cpu_time,
r.total_elapsed_time,
t.text AS Query_Text
FROM
sys.dm_exec_requests r
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) t
INNER JOIN
sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE
r.blocking_session_id <> 0;
26. DBCC (Database Console Commands) is a set of Transact-SQL (T-SQL) commands provided by Microsoft SQL Server for database administration and maintenance.
1. DBCC CHECKDB:
• Verifies the logical and physical integrity of a database.
• Checks for corruption and inconsistencies within the database.
• Provides detailed information about errors found.
2. DBCC CHECKTABLE:
• Checks the integrity of a specific table within a database.
• Helps identify and repair corruption at the table level.
3. DBCC CHECKALLOC:
• Checks the allocation structures of a database.
• Verifies the consistency of space allocation within a database.
4. DBCC SHRINKDATABASE:
• Reduces the size of a database by reclaiming unused space.
• Can be used to shrink the entire database or specific files.
5. DBCC SHRINKFILE:
• Shrinks a specific database file to reduce its size.
• Useful for managing disk space and file growth.
6. DBCC INDEXDEFRAG:
• Defragments clustered and non-clustered indexes.
• Can improve index performance and reduce fragmentation.
7. DBCC UPDATEUSAGE:
• Corrects inaccuracies in space usage information stored in system catalogs.
• Helps ensure that space usage statistics are up to date.
8. DBCC FREEPROCCACHE:
• Clears the plan cache, removing execution plans for stored procedures and queries.
• Useful for troubleshooting and performance tuning.
9. DBCC DROPCLEANBUFFERS:
• Removes all clean pages from the buffer pool.
• Forces SQL Server to read data from disk when it is needed, which can be useful for testing and benchmarking.
10. DBCC INPUTBUFFER:
• Retrieves the last statement sent from a client to SQL Server for a specific session.
• Useful for troubleshooting and auditing.
11. DBCC OPENTRAN:
• Displays information about the oldest active transactions in the database.
• Helps identify long-running transactions that may cause blocking.
12. DBCC TRACEON and DBCC TRACEOFF:
• Enables or disables trace flags, which are used for diagnostic purposes.
• Allows you to enable or disable specific SQL Server behaviors.
13. DBCC TRACESTATUS:
• Provides information about which trace flags are currently active and their status.
14. DBCC SQLPERF:
• Retrieves various performance-related statistics, such as cache hit ratios and I/O statistics.
• Useful for monitoring and performance tuning.
15. DBCC CLONEDATABASE:
• Creates a copy of an existing database with limited data and schema.
• Useful for creating a lightweight database for testing and debugging.
16. DBCC CHECKIDENT:
• Checks and resets the identity seed value for a specified table.
• Can be used to correct identity column values after data manipulation.
27. The sys.sysprocesses system view to retrieve information about currently active processes or sessions.
SELECT *
FROM sys.sysprocesses
WHERE spid = 99;
28. This query will return a list of SQL Server Agent job definitions from the msdb database where the job_id contains the specified string.
The SQL query selects information from the sysjobs system view in the msdb database, filtering the results based on the job_id column. It looks for job IDs that contain the specified string '%A966D6498E9495A90AE966EC%'.
USE msdb;
GO
SELECT *
FROM sysjobs
WHERE job_id LIKE '%A966D6498E9495A90AE966EC%';
29. Generates a list of SQL Server KILL statements for sessions with a dbid (database ID) of 6.
Execute these KILL statements to terminate the specified sessions associated with database ID 6. Be cautious when using KILL statements, as forcibly terminating a session can result in data inconsistencies or unexpected behavior if not done carefully.
SELECT 'KILL ' + CAST(spid AS VARCHAR(10)) AS spid
FROM sys.sysprocesses
WHERE dbid = 6;
30. This query is useful for monitoring long-running or resource-intensive queries related to database backup, index creation, and other operations, providing information on their progress and estimated completion times.
This query is useful for monitoring long-running or resource-intensive queries related to database backup, index creation, and other operations, providing information on their progress and estimated completion times.
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(6,2), r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(6,2), r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(6,2), r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
-- Include the actual command text for better understanding
CONVERT(VARCHAR(100), (
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 'Command'
FROM sys.dm_exec_requests r
WHERE command IN ('BACKUP DATABASE', 'CREATE INDEX', 'RESTORE DATABASE', 'LOG BACKUP');
31. This script is designed to gather file information from all databases in the SQL Server instance and store it in a temporary table.
The SQL script creates a temporary table #db_files and uses a cursor to iterate through all databases in the SQL Server instance.
It's useful for analyzing file sizes and space utilization across multiple databases, especially when you want to filter files based on their location (drive) and order them by size.
-- Create a temporary table to store database file information
create table #db_files(
db_files varchar(300),
file_loc varchar(300),
filesizeMB decimal(9,2),
spaceUsedMB decimal(9,2),
FreespaceMB decimal(9,2)
)
declare @strSQL nvarchar(2000)
DECLARE @dbName varchar(MAX)
DECLARE @getDBname CURSOR
-- Create a cursor to iterate through all databases
SET @getDBname = CURSOR FOR
select name from sys.databases
OPEN @getDBname
FETCH NEXT
FROM @getDBname INTO @dbName
-- Iterate through databases
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @dbName
-- Build a dynamic SQL statement to insert file information into the temporary table
select @strSQL =
'
use ' + @dbName + '
INSERT INTO #db_files
select
name,
filename,
convert(decimal(12,2), round(a.size/128.000,2)) as FileSizeMB,
convert(decimal(12,2), round(fileproperty(a.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB,
convert(decimal(12,2), round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB
from dbo.sysfiles a
'
-- Execute the dynamic SQL statement
exec sp_executesql @strSQL
FETCH NEXT
FROM @getDBname INTO @dbName
END
-- Close and deallocate the cursor
CLOSE @getDBname
DEALLOCATE @getDBname
GO
-- Select file information from the temporary table for files located on drive 'G' and order by file size
select * from #db_files where file_loc like 'G%' ORDER BY filesizeMB DESC
-- Drop the temporary table
drop table #db_files
32. The SQL script appears to be a script for shrinking a specific data file (file_id = 1) in a SQL Server database. It uses a cursor to iteratively shrink the file in smaller batches until the desired target size is reached.
-- Declare variables
declare @fsize float, @spaceus float, @idfile int, @numfiles int, @increment int, @tamano int, @batchSize int
-- Set the batch size for the shrink process
SET @batchSize = 250
-- Create a cursor to fetch file size and space used information
DECLARE db_cursor CURSOR FOR
select f.file_id,
Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) Size,
Cast(FILEPROPERTY(name, 'SpaceUsed') * 8.0/1024.0 as decimal(18,2)) as spaceused
from sys.database_files f
where f.file_id in (1)
-- Open the cursor
OPEN db_cursor
-- Fetch the initial file size and space used
FETCH NEXT FROM db_cursor INTO @idfile, @fsize, @spaceus
-- Loop through the cursor results
WHILE @@FETCH_STATUS = 0
BEGIN
-- Set the target size for the file
set @tamano = @fsize
-- Loop to shrink the file in smaller batches
while @tamano - @batchSize > @spaceus
begin
set @tamano = @tamano - @batchSize;
-- Execute the DBCC SHRINKFILE command to shrink the file to the target size
dbcc shrinkfile(@idfile, @tamano)
end
-- Fetch the next file's information
FETCH NEXT FROM db_cursor INTO @idfile, @fsize, @spaceus;
END
-- Close and deallocate the cursor
CLOSE db_cursor
deallocate db_cursor
33. The SQL script creates a stored procedure named usp_prologspace in the dbmonitor database. This stored procedure populates a table variable @LogSpace with information about log space usage using the DBCC SqlPerf(logspace) command and then retrieves details about the log files for databases in the sys.databases and sys.master_files system views.
This stored procedure can be executed to retrieve and display information about log space usage for databases in the dbmonitor database. Additionally, it provides dynamic SQL commands that can be used to truncate log files if needed.
USE dbmonitor; -- Specify the database where the stored procedure will be created
GO
-- Create the stored procedure
CREATE PROCEDURE usp_prologspace
AS
BEGIN
-- Declare a table variable to store log space information
DECLARE @LogSpace TABLE
(
DB VARCHAR(255),
LogSizeMB INT,
PercentUsed FLOAT,
Status INT
);
-- Populate the @LogSpace table with log space information using DBCC SqlPerf(logspace)
INSERT INTO @LogSpace
EXECUTE('DBCC SqlPerf(logspace)');
-- Select relevant log space information and shrink commands
SELECT
LogSizeMB,
PercentUsed,
log_reuse_wait_desc,
ls.status,
FROM
@LogSpace LS
JOIN
sys.databases sd ON ls.DB = sd.name
JOIN
sys.master_files smf ON sd.database_id = smf.database_id
WHERE
smf.type_desc = 'LOG'
ORDER BY
LogSizeMB DESC;
END;
34. execute all three SQL commands in sequence, have the necessary permissions to execute these commands, especially the DBCC commands, as they might require elevated privileges.
-- SQL Script
-- CHECKPOINT: Initiates a manual write of dirty (modified) data pages from the buffer cache to data files,
-- ensuring that data changes are persisted to disk, which can improve database recovery.
CHECKPOINT;
GO
-- DBCC FREEPROCCACHE: Clears the SQL Server plan cache, removing cached execution plans for stored procedures and queries.
-- This forces SQL Server to recompile and re-optimize queries when they are executed next.
DBCC FREEPROCCACHE;
GO
-- DBCC SHRINKFILE (TEMP4, 5024): Reduces the size of a specific database file named "TEMP4" to 5024 megabytes (MB).
-- Shrinking database files should be approached cautiously as it can result in fragmentation and may not always be necessary.
DBCC SHRINKFILE (TEMP4, 5024);
GO
35. The query will return all rows from the sysjobs table where the job_id matches the specified pattern. The result will include information about the SQL Server Agent jobs that meet this criteria.
-- SQL Script
-- Switch the database context to msdb
USE msdb;
GO
-- Retrieve SQL Server Agent jobs with a job_id matching the pattern '%71F0C212%'
-- sysjobs: Stores information about SQL Server Agent jobs
-- job_id: Unique identifier for each SQL Server Agent job
-- LIKE '%71F0C212%': Matches job_ids containing the substring '71F0C212'
SELECT * FROM sysjobs WHERE job_id LIKE '%71F0C212%';
36. Select the session_id of sessions where login_name is 'test'
SELECT session_id
FROM sys.dm_exec_sessions
WHERE login_name = 'test';