Recently I needed to apply compression data on a particularly large table. One of the main reasons for applying compression was because the database was extremely low on space, in both the data and the log files. To make matters worse, the data and log files were nowhere near big enough to accommodate compressing the entire table in one go. If the able was partitioned then I could have done one partition at a time and all my problems would go away. No such luck.
Best way to eat an elephant, etc. etc. Read the whole thing; you might be in a similar situation someday.
The initial thought is to enable the trace flag at session level. We ran into two challenges. First, application needs code change (which they couldn’t do) to enable it. Secondly, dbcc traceon requires sysadmin rights. Customer’s application used a non-sysadmin user. These two restrictions made it seem impossible to use the trace flag.
However, we eventually came up with a way of using logon trigger coupled with wrapping the dbcc traceon command inside a stored procedure. In doing so, we solved all problems. We were able to isolate the trace flag just to that application without requiring sysadmin login.
This is the very edge of an edge case. In normal practice, change the code.
I wasn’t surprised (although a little disappointed) that out of the 9 people the answered only one person was, and of the rest 5 didn’t even know what TRUSTWORTHY is. I even had one person ask me later. That’s somewhat scary because under the right circumstances if you give me a database with TRUSTWORTHY turned on I can take over your instance. I’m NOT going to show you how but it isn’t terribly difficult.
I’ll admit that I have been a bit non-chalant about TRUSTWORTHY in the past, but turning it on is the smart move.
There was a blog post by Boris Hristov which had some good images of the various places to look in the GUI to make sure that the updates have been picked up correctly. Using these and through experimentation I was able to answer those questions.
Interesting questions and good answers.
I’m seeing lots of CXPACKETS waits, how do I fix these?
Bad Advice = Set the maximum degree of parallelism to 1, no more CXPACKET waits!
I’m seeing index fragmentation occur quite frequently on some of my indexes, what should I do?
Bad Advice = Set fill factor on the server to 70, no more fragmentation problems!
I’m worried about TempDB contention. What should I do?
Bad Advice = Set the number of files to the number of cores, no more contention issues!
Read the post for better advice.
dbo.suspect_pagesis a table that resides in the MSDB database and is where SQL Server logs information about corrupt database pages (limited to 1,000 rows) that it encounters, not just when
DBCC CHECKBis run but during normal querying of the database. So if you have a DML operation that accesses a corrupt page, it will be logged here, this means that you have a chance of identifying a corruption in your database outside of the normal DBCC CHECKDB routine.
This is a nice tool we can use to check for corruption.
The only problem with this approach is that our database was configured (rightly or wrongly) with approximately 250 sequences! Since we could not be sure which sequences would ultimately cause us problems we decided to increment each one by 10,000.
Not being someone who likes performing monotonous tasks and also recognising the fact that this task would probably need to be performed again in the future I decided to attempt to programmatically solve this problem.
The script isn’t too difficult to understand but let me reiterate his warning: read the script before you run it, and know exactly what it’s doing before you run it.
This automatically poses the question of how it is possible to restore a backup chain with CDC? On a database restore, in order to apply differential backups and transaction logs the NORECOVERY clause is required to prevent SQL Server from performing database recovery.
If this option is required but KEEP_CDC in conjunction with it is incompatible, surely this means point in time restores are not possible for restores that require CDC tables to be retained?
The answer is a bit surprising, and my guess is that most database administrators are totally unaware of this restoration quirk.
In order to maintain data integrity within the database, locks are used on resources like tables, rows, pages etc. by any process that wishes to use them. This is done to ensure multiple process don’t alter the same resources at one time leading to data inconsistency. When a process wishes to lock a resource, it sends a request to the server and the server grants it. However, when a process requests lock on a resource that has already been locked by another process, the request is denied. The requesting process is thus placed on “hold” until the resource it is requesting for isn’t released. In this situation, the requesting process is called a blocked process, and such a process could put a halt on other subsequent processes and activities scheduled on the server.
Thus identifying a blocked process and releasing it requires a DBA team to check the application database blocking. Additionally, here are some other techniques that may be used to find out which processes are creating a block on the server:
My favorite method, not mentioned, is Adam Machanic’s sp_whoisactive.