SELECT*

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:

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 %

 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

 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' )