Category: Administration

Richie Lee encountered a use case for trickle migration:

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.

Jack Li shows how to enable a trace flag without sysadmin or changing any application code:

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.

Cody Konior looks at slipstreaming SQL Server 2012:

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.

John Martin shows us about dbo.suspect_pages:

dbo.suspect_pages is 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 CHECKB is 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.

Mark Broadbent had to increment all of his sequences by 10,000.  Here’s how he did it:

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.