Press "Enter" to skip to content

Category: Administration

Check Your CHECKDBs

Published 2016-01-07 by Kevin Feasel

Richie Lee has a script to check the last known CHECKDB run date:

One of the most important duties of a DBA is to ensure that CHECKDB is run frequently to ensure that the database is both logically and physically correct. So when inheriting an instance of SQL, it’s usually a good idea to check when the last CHECKDB was last run. And ironically enough, it is actually quite difficult to get this information quickly, especially if you have a lot of databases that you need to check. The obvious way is to run DBCC DBINFO against the specific database. This returns more than just the last time CHECKDB was run, and it is not especially clear which row returned tells us the last CHECKDB (FYI the Field is “dbi_dbccLastKnownGood”.)

It’s a bit of a shame that this information isn’t made available in an easily-queryable DMV.

Comments closed

VLFs

Published 2016-01-06 by Kevin Feasel

Tom Roush talks VLFs, changes in DBCC LOGINFO, and Availability Groups:

Turns out SQL 2008R2 (where the original script worked) returns different fields than 2012 and 2014 (where it didn’t).

I figured I didn’t want to find out which version of the script to use every time I needed to run it on a server, so I told the script to figure that out by itself, and then run the appropriate hunk of code (example below)

This is a good explanation of how to back out of a complex situation.

Comments closed

Change Azure SQL Database Compatibility Level

Published 2016-01-06 by Kevin Feasel

Tom LaRock shows us how to change the compatibility level of an Azure SQL Database:

You can change the compatibility level of an Azure SQL Database.

It’s true! I know!

OK, so I’m a little excited about this one. See, I’ve been giving this talk on cardinality for the past couple of years now, so this is a hidden gem to me. When I found out this was possible I took out my demo scripts to see if changing the compatibility level would have any effect.

This is interesting, especially given that Management Studio doesn’t give you that option.  Know your T-SQL, folks.

Comments closed

Trickle Migration

Published 2016-01-05 by Kevin Feasel

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.

Comments closed

Trace Flags Without Sysadmin

Published 2016-01-01 by Kevin Feasel

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.

Comments closed

Trustworthy Databases

Published 2016-01-01 by Kevin Feasel

Kenneth Fisher asks if you check TRUSTWORTHY settings on your databases:

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.

Comments closed

Bad Fixes

Published 2015-12-31 by Kevin Feasel

David Alcock looks at a few common “fixes” which end up causing their own problems:

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.

Comments closed

Monitoring For Suspect Pages

Published 2015-12-28 by Kevin Feasel

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.

Comments closed