57,410 reputation
777159
bio website
location Rhode Island
age 40
visits member for 3 years, 4 months
seen 13 hours ago

Senior Consultant at SQL Sentry, SQL Server MVP, @AaronBertrand on twitter, blogger at sqlperformance.com and sqlblog.com, Mac user, volleyball player, curler, hockey/football/tennis fan (Go Bruins!), proud father and Canadian, eh?


1d
awarded  Good Answer
Jul
26
awarded  Nice Answer
Jul
24
comment Matching two rows in a single table if two specific keys have different values
How does this involve MySQL and SSIS?
Jul
23
comment How to figure out Disk I/O by table
Same comment as above - need to be careful about case sensitive collations. DMVs and catalog views are defined in the system as lower case, so you should have queries that match.
Jul
23
comment How to figure out Disk I/O by table
I'd be careful about upper-casing catalog views and DMVs... on a case sensitive system, those queries might break. Also I think the objectproperty check is redundant, since sys.tables won't contain any non-user tables.
Jul
23
comment Are Views optimized when I add a WHERE clause to them?
@profimedica indexed views can be created for performance reasons (e.g. to store intermediary results like aggregates instead of calculating them at runtime). If a view is not materialized, it can be for a variety of reasons: DRY (common join or filter performed in many different queries), security, obfuscation, schema simplification.
Jul
22
comment Preventing SQL Injection
The parameter will absolutely not be used - it will be passed in but nothing will happen to it. Look closer, you've already concatenation it to the string and it's no longer a parameter. You can't parameter use a table or column name anyway...
Jul
21
comment Long running simple query SQL Server 2008 R2
@Kris I don't think GETDATE() is causing a problem. As long as there is an index on timeStamp then it is still sargable.
Jul
21
comment CREATE DATABASE defaulting to wrong log path
I don't know how to solve your issue - if that UNC path is being used for the log path in a default create database statement, I'd call Microsoft support.
Jul
21
comment Database revision needed for sports events database
@user44098 That is bizarre logic indeed. A table is a set of things. A set of users, a set of packages, a set of messages. It is not a set of user, a set of package, or a set of message. You update all the users, you don't update all the user. I think the singular nomenclature comes from object-oriented people who are always thinking of things as acting on a single item at a time, which is not how we think in SQL Server.
Jul
21
comment CREATE DATABASE defaulting to wrong log path
Sorry, I meant the UNC path. Did you find that anywhere? SQL Server is getting it from somewhere.
Jul
21
comment CREATE DATABASE defaulting to wrong log path
And did you search the registry for that path?
Jul
21
comment CREATE DATABASE defaulting to wrong log path
What is the log file location for the model database?
Jul
21
comment Impact of deleting 100GB table on Mirroring
I might be inclined to turn mirroring off, then drop the table, and then re-initialize mirroring from scratch.
Jul
21
comment CREATE DATABASE defaulting to wrong log path
sorry, you are talking circles around me. You say that you are trying to create a database called MyDB and it is failing because it is attempting to create a log file on a UNC path (this is what is showing on the error message in the screen shot). Why is that a UNC path? If that is intentional, are you sure the SQL service account has access? What does this specific create database command have to do with DefaultPathDB or the script you listed in the question that uses dynamic SQL to build a CREATE DATABASE command that manually specifies the location and sizes of each file?
Jul
21
comment CREATE DATABASE defaulting to wrong log path
And to clarify, the script you are actually is running is not as simple as CREATE DATABASE [MyDB];, as you said in your comment - your actual create database command is a lot more complicated than that, you're dynamically dictating the location of data and log files. Why not just use the model database to define these things, instead of an additional user database that could have different settings than what you want new databases to inherit for a variety of reasons? (This would also simplify your script substantially.)
Jul
21
comment CREATE DATABASE defaulting to wrong log path
Ok @KenWilson and did you print @Default_Log_Path to see if it contains what you expect? You are not relying at all on the default log directory for the server, you are relying on the specific configuration of a database called DefaultPathDB. So if that database is not configured correctly, then yes, you will ultimately attempt to place log files in the wrong folder, too.
Jul
21
comment CREATE DATABASE defaulting to wrong log path
Seems like most of the problem is that you're looking for \MSSQLServer in the registry, but should actually be looking for \MSSQL10_50.MSSQLSERVER in the registry. The rest is probably that your script is malformed in some way - 'Backup Path\test_log.LDF' doesn't make sense for two reasons; (1) why is it creating a log file that has anything to do with a backup path and (2) why are there no leading drive letters there? Can you show the actual script you're using?
Jul
18
comment Way to do MERGE with update source when target not match?
You should also have a read of this. mssqltips.com/sqlservertip/3074/…
Jul
18
comment Way to do MERGE with update source when target not match?
@tostringtheory You can use backticks to make @variables not act like pings.