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

    OBJECT_NAME(object_id) AS tblName 
    ,dbo.index_name(object_id, index_id) AS ixName 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) 
    avg_fragmentation_in_percent > 20 
    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

     [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 - 
    ,[Parent Query] = qt.text
    ,DatabaseName = DB_NAME(qt.dbid)
    sys.dm_exec_query_stats qs
    sys.dm_exec_sql_text(qs.sql_handle) AS qt
    [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
    Commented Nov 30, 2016 at 18:36

1 Answer 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,
 @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
    Commented Nov 30, 2016 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/… Commented Nov 30, 2016 at 19:28
  • Another Article for you : brentozar.com/archive/2016/08/… Commented Nov 30, 2016 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
    Commented Nov 30, 2016 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
    Commented Nov 30, 2016 at 21:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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