The other day I found myself in the position of needing to restore multiple databases to a server. In this case all the log files would be on one drive and all the data files on another. Rather than sit there and write restore scripts for ~20 databases I figured it would be quicker to…
Category: T-SQL
Who Owns Your Databases And Jobs?
Quick question, who owns your databases? And how about your jobs? Don’t know? Go find out…now find out without using the GUI. Querying sys.databases will give you the owner_sid. The trouble is that you have to relate that owner_sid back to a user. The same with sysjobs, that sid needs to relate to someone. You…
Replication–Beware Reuse of View Names When Filtering Articles
I came across a very strange issue with replication this weekend. As a part of a release a new table was being created with extra columns and a view was created with the old table name that referenced the old column set (so as not to break old legacy applications). This table was replicated. The…
Mass Updating Job Step Retry Attempts
Sometimes I find that the SQL Server team make curious choices as regards default operations. When setting up replication by default the log reader and distribution agent job steps will retry 2,147,483,647 times before failing. Doing the math on the retry interval of 1 minute that means that the job wouldn’t actually hit a failed…
xp_logininfo–Your View Into Active Directory
It’s always good to know what users have access to your SQL instances but when it comes to Active Directory users and groups it can quickly become complicated and you might not know who has access at any given time. Requests can come in for you to provide access to a user, but how can…
Stop Bad Database Design
Every year that goes by I sit in hope that I won’t see bad database design. Every year I am disappointed. As an example here is a table create statement that I saw the other day (table and column names have been changed to protect the innocent) (more…)
Please Don’t Use Deprecated Data Types
I know that a lot of vendors like to write for the lowest common denominator (i.e. SQL 2000) but really folks it’s gone too far. I’m sick of cracking open vendor code that’s certified for SQL 2008 and seeing things like IMAGE and TEXT data types. Microsoft deprecated these things back when they released SQL…
T-SQL Tuesday #18–CTE A Simpler Form Of Recursion
It’s T-SQL Tuesday time folks. Bob Pusateri (blog|twitter) is the host and has chosen Common Table Expressions (CTEs) as the topic of the month. Several ideas came to mind for writing about CTEs, one of the best uses I’ve seen for one recently was to grab the name of the most recent backup file for…
Are You Putting Out?–The SQL OUTPUT Clause
I had some code come through the other day from a developer, it was designed to perform an insert and then return the inserted data back to the client for display. This kind of code is probably seen very frequently out there in the real world. Quick, Create A Table As we’ll be playing around…
T-SQL Tuesday #10 – Applying Indexes To Replicated Tables
It’s T-SQL Tuesday time again! This month Michael J. Swart (blog|twitter) is hosting and has given us the topic of indexes. I’ve previously written about how you can find indexes that are not being used and get their sizes. Not being allowed to reuse that meant that I had to come up with something else.…