1

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

Reboot...

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?

  • Did you actually take a Full backup of the db, or only make a copy? They are not the same... – Kevin3NF Nov 30 '16 at 18:36
-1

Seen this happen a bunch. Your first mistake was to shrink the transaction log. When you set the recovery model to Simple the log is automatically truncated after a full backup of the Database.

What you have now is a combination of Index Fragmentation and Statistics needing to be rebuilt. I would high recommend Ola Hallengren's free utility that can do both for you.

The following works for me after creating the procedures:

EXECUTE dbo.IndexOptimize
 @Databases = 'USER_DATABASES',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30,
 @UpdateStatistics = 'ALL'

The second thing that happened is most likely your query cache got purged and it now has to build new statistics and has cause parameter sniffing on your queries. Rebuilding your statistics should help.

  • So... the error was shrinking? why? pardon my ignorance... I think (my logic, not something that see or read) that another thing to do is to purge the transact log file in order to commit all the information in it.... or this happend in the moment that database change recovery model change... Or this information its already in the database file? – Robert K Nov 30 '16 at 19:19
  • Shrinking was only part of the issue. All of your transaction should be committed prior to a backup or truncation of the log. SQL is good about that. The main problem is in the fragmentation of your indexes and your Statistics. When you rebooted the Server your purged the query cache, so now it has to build new query plans that may not now be optimized. ( parameter sniffing) Here is a good article on Parameter sniffing: brentozar.com/archive/2016/11/… – bwilliamson Nov 30 '16 at 19:28
  • Another Article for you : brentozar.com/archive/2016/08/… – bwilliamson Nov 30 '16 at 19:46
  • Thanks a lot! that solve my problem, Ola Hallengren's works like a Charm! Im in debt... Still have to make some stress test and verify query plans but looks very good now. – Robert K Nov 30 '16 at 21:01
  • Shrinking a transaction log does not impact statistics or table fragmentation. You get fragmentation with data file shrink operations, but OP stated they shrank the log only. And statistics do not change with shrink operations at all (there is potential that they could be out of date and need updating, but that is a complete aside to file operations). – Nic Nov 30 '16 at 21:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.