25,996 reputation
44586
bio website blogs.msdn.com/b/sql_pfe_blog
location In the Ocean somewhere...
age
visits member for 3 years, 7 months
seen 1 hour ago

SQL Server Support Escalation Engineer at Microsoft.

Twitter: @SQLife
Blog: http://tstringer.azurewebsites.net
Email: sqlsalt [at] outlook [dot] com

Disclaimer
The views expressed on my posts on this site are mine alone and do not reflect the views of my company. All posts of mine are provided "AS IS" with no warranties, and confers no rights.

The following disclaimer applies to all code, scripts and demos available on my posts:

This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.


Jan
15
comment Disk Space usage
This should be pulled outside of SQL Server. Manage operating system things in the operating system in my opinion. Use SQL Server to store the data, by all means, and create a report off of the data. But don't pull the data from within SQL Server.
Jan
13
comment Script Rename of SQL Server Database and Database Files
That could quite easily be automated through PowerShell. Using SMO, the SQL work is concise and straightforward, and then the rename of the database files is a one-liner.
Jan
9
comment How do you run T-SQL commands on multiple servers from one place?
What does your script specifically do? That may allow us to get more specific with a recommendation.
Jan
9
comment Database size after importing the .sql file
There is absolutely no way to determine that. If the .sql file is 34.5 GB of comments then you'd have no problem :-) What is in the SQL file? (this comment is assuming SQL Server)
Jan
8
comment How can I filter an extended event by database in SQL Server 2008 R2
@JackDouglas That's awesome :-)
Jan
8
comment How can I filter an extended event by database in SQL Server 2008 R2
@JustinDearing Honestly, all I do is just hard code the database ID and then put a comment right next to it with the database name so my future-me knows what I intended without having to resolve it. It has worked for me with no issues.
Jan
8
comment How can I filter an extended event by database in SQL Server 2008 R2
@ShawnMelton No, I'm saying the package is spelled "sqlserver" not "sqlsever". Also, there is no database_name action in 2008 R2.
Jan
8
comment How can I filter an extended event by database in SQL Server 2008 R2
You have a typo. Should be "sqlserver.database_name".
Jan
7
comment Getting ApplicationName (sqlserver.client_app_name) to show up in an xevent
You could also just try changing your event session target with a different file name. My guess is that would work for you too and be equally as effective.
Jan
7
comment Getting ApplicationName (sqlserver.client_app_name) to show up in an xevent
There is no way (outside of xp_cmdshell, of course).
Jan
7
comment Getting ApplicationName (sqlserver.client_app_name) to show up in an xevent
Did you add that action after the fact? With the event file I have seen it where the GUI doesn't show new columnar data when you add columns (actions or fields). I.e. start session, stop session, add action, start session - don't see it show in the GUI. Either query the target, or move the old XEL files. That has worked for me in the past.
Jan
7
comment AlwaysOn Availability Groups go to Resolving status
If the replicas can't talk to each other over the database mirroring endpoint then that would be a cause for this issue. Check the connectivity between the two servers' DB mirroring endpoints (permissions, etc.). Therein lies your problem, most likely.
Jan
7
comment AlwaysOn Availability Groups go to Resolving status
Do the AGs recover from the resolving status or do they stay in resolving? Look in the error log of SQL Server to find out during the time of the changing state. Also take a look in the alwayson_health XE session data during this time.
Jan
4
comment Programmatically determine whether a database was shut down cleanly or whether it required crash recovery
Why does is_cleanly_shutdown not work for you?
Jan
2
comment Resource cost of a blocked query
I would look to see what gains could be made out of analyzing and tuning the reporting queries, absolutely. Of course, there are also other ways to offload reporting workloads as well. But that's probably a different conversation.
Dec
31
comment There was an unknown error applying the filestream settings. check the parameters are valid. (0x80070002)
As per your last comment, is your issue resolved?
Dec
31
comment There was an unknown error applying the filestream settings. check the parameters are valid. (0x80070002)
Do you get this error when trying to enable filestream through SQL Server Configuration Manager?
Dec
30
comment Check if database has changed to determine need to perform backup
What are you really gaining out of this approach? What's wrong with small differential backups? Storage space is cheaper than dirt, and file system management is easy. I don't see how the juice is worth the squeeze here.
Dec
29
comment What is the cause of this problem with CONVERT()?
Aaron is spot on. Your brain is converting the binary data to integer data then straight to numeric, but SQL Server doesn't do that implicit conversion from binary -> integer -> numeric(x, y). For SQL Server to follow your thought process, you'd have to do something like this: PRINT CONVERT(NUMERIC(38, 0), convert(int, 0x00000001), 0); PRINT CONVERT(NUMERIC(38, 0), convert(int, 0x00010001), 0);.
Dec
3
comment What significance does an index_id < 256000 have?
+1 that's an awful bad habit. Forget about index_id. Especially since more accurate data for determining type is sitting right next to it... literally.