I'm new mantaining databases, I'm more like programmer but you know in some jobs you must be multitools because theres no specific dba.
My boss told me to change the recovery model from Full to simple because there was no specific routine to back up log and store the backups and the result was a 1.5 gb transaction log with no other initial backups (or maybe missing, the guy how do the project left) besides they do not care about the possible dataloss on daily basis. So I followed the command.
We schedule a windows and I do the job on SSMS and do the following steps:
- Make a full copy to database with -> Tasks Copy Databases.
- Make a full and transact log copy on Database.
Go to database options and change Recovery model from full to simple.
Shrink the transaction log ->Tasks > Shrink > Files
In the Shrink file dialog the currently allocate space field was the 1.5gb but were about 1 gb the Avaliable free space so I left 600MB to the log file
So after this the information was able to query but the performances is bad as hell a stored procedure that take 0.6 sec to execute before the changes now take up to 12 secs to run.
Moreover I repeat this in database copies (from the copy that I do before changes) and realized that instead of shrink the space in disk it's growing exactly the space trying to shrink.
After this I try to see if was fragmentation and indeed there about the 40% of tables about up to 50% in avg_fragmentation_in_percent
SELECT OBJECT_NAME(object_id) AS tblName ,dbo.index_name(object_id, index_id) AS ixName ,avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) WHERE avg_fragmentation_in_percent > 20 AND index_type_desc IN('CLUSTERED INDEX', 'NONCLUSTERED INDEX') ORDER BY avg_fragmentation_in_percent DESC
So I try to reorganize the index on the tables. Some go down to 20% but must still on ~ 45%
Finnaly I realised that the CPU was overheating more and more until reach 100% of CPU usage.
Here I used this query to make the what querys are cosuming more resources
SELECT TOP 50 [Average CPU used] = total_worker_time / qs.execution_count, [Total CPU used] = total_worker_time, [Execution count] = qs.execution_count, [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) ,[Parent Query] = qt.text ,DatabaseName = DB_NAME(qt.dbid) FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY [Average CPU used] DESC;
Can anyone tell me what's happend?