Posts

Showing posts from February, 2022

How to read audit file from drive,CREATE SERVER level AUDIT

Image
The below query will tell us about how to import data from SQLAUDIT file to table in a database. We are creating a table here and importing data from file SELECT * INTO Audit_HumanResources_Department FROM ( SELECT event_time ,action_id ,session_server_principal_name AS UserName ,server_instance_name ,database_name , schema_name ,object_name , statement FROM sys.fn_get_audit_file( 'G:\Audit\HumanResource_Department\*.sqlaudit' , DEFAULT , DEFAULT ) --WHERE YEAR(event_time)=YEAR(GETDATE()) --AND month(event_time)=month(getdate()) --and day(event_time)=day(getdate()) )T The below piece of code will explain about create server level audit with Windows Application Event logs and its specifiactions and how the logs information will be route to eventlogs(eventvwr) applicatin logs. Before that we need to provide permissons at SECPOL.MSC(Local Security Policy). Below link will be explained about how give permissions that particular SQL Server serice account. https:...

Database Size Calculation in SQL Server

This is a sample calculation and dont provide the proof log file size calculation use DatabaseName GO EXEC sp_spaceused Reserved = Data + Index + Unused( All values in KB) 230540328 = 64265016 + 164723400 + 1551912 Reserved = 230 GB DatabaseSize = Reserverd + LogFileSize( Values in GB) 280 = 230 + 50 ----Free space calculation 1 ’ 551 , 912 / 64 , 265 , 016 = 2 %

Which query is causing to the log file growth.

 Sometimes when we are online, we could see that log file grow continuously. The below query will tell us which query is causing to that logfile growth of that particular database. SELECT session_id, dt.transaction_id, DB_NAME(database_id) as DB, sum (database_transaction_log_bytes_used) TotalLogBytesUsed FROM sys.dm_tran_database_transactions dt LEFT JOIN sys.dm_tran_session_transactions st on (dt.transaction_id = st.transaction_id) GROUP BY session_id, dt.transaction_id, database_id ORDER BY 4 DESC

How much portion or percentage BACKUP or RESTORE command is completed.

 The below query will tell us how much portion of BACKUP or RESTORE is completed. ---How much portion of BACKUP OR RESTORE is completed. SELECT session_id as SPID, command, a. text AS Query, start_time, percent_complete, dateadd( second ,estimated_completion_time / 1000 , getdate()) as estimated_completion_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a WHERE r.command in ( 'BACKUP DATABASE' , 'RESTORE DATABASE' )