Swore I had this already.
SELECT * FROM sys.traces
sp_trace_setstatus 2, 0 --stop trace 2
go
sp_trace_setstatus 2, 2 --close/remove trace 2
Monday, April 30, 2012
Thursday, March 8, 2012
[Powershell] Script out each job to a separate file
Code blatantly stolen from both John Sansom and Jan Goyvaerts (mostly John; read the credits), but I modified it a bit.
I needed to be able to script out each of my jobs to separate files. And I need to trap for characters that can't be used in a file name (why, oh why, did I ever put Colons in my job names?)
(updated on Pi Day - forgot numbers, which caused some jobs to not script out)
# Date: 23/02/12 updated 2012/03/14
# Author: John Sansom
# Description: PS script to generate all SQL Server Agent jobs on the given instance.
# The script accepts an input file of server names.
# Version: 1.1 mbourgon modified to save each job to a separate file; excludes non-filename characters
# 1.11 added numbers.
# Example Execution: .\Create_SQLAentJobSripts.ps1 .\ServerNameList.txt
param([String]$ServerListPath)
#Load the input file into an Object array
$ServerNameList = get-content -path $ServerListPath
#Load the SQL Server SMO Assemly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
#Create a new SqlConnection object
$objSQLConnection = New-Object System.Data.SqlClient.SqlConnection
#For each server in the array do the following..
foreach($ServerName in $ServerNameList)
{
Try
{
$objSQLConnection.ConnectionString = "Server=$ServerName;Integrated Security=SSPI;"
Write-Host "Trying to connect to SQL Server instance on $ServerName..." -NoNewline
$objSQLConnection.Open() | Out-Null
Write-Host "Success."
$objSQLConnection.Close()
}
Catch
{
Write-Host -BackgroundColor Red -ForegroundColor White "Fail"
$errText = $Error[0].ToString()
if ($errText.Contains("network-related"))
{Write-Host "Connection Error. Check server name, port, firewall."}
Write-Host $errText
continue
}
#IF the output folder does not exist then create it
$OutputFolder = ".\$ServerName"
$DoesFolderExist = Test-Path $OutputFolder
$null = if (!$DoesFolderExist){MKDIR "$OutputFolder"}
#Create a new SMO instance for this $ServerName
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $ServerName
#Script out each SQL Server Agent Job for the server
$jobs = $srv.JobServer.Jobs
#Using regex invocation courtesy of Jan Goyvaerts at regular-expressions.info
$regex = [regex] '[^a-zA-Z0-9-\ ]'
foreach ($job in $jobs)
{
$jobname = ".\$OutputFolder\" + $regex.Replace($Job.Name,'_') + ".job.sql"
$job.Script() | Out-File $jobname
}
}
I needed to be able to script out each of my jobs to separate files. And I need to trap for characters that can't be used in a file name (why, oh why, did I ever put Colons in my job names?)
(updated on Pi Day - forgot numbers, which caused some jobs to not script out)
# Date: 23/02/12 updated 2012/03/14
# Author: John Sansom
# Description: PS script to generate all SQL Server Agent jobs on the given instance.
# The script accepts an input file of server names.
# Version: 1.1 mbourgon modified to save each job to a separate file; excludes non-filename characters
# 1.11 added numbers.
# Example Execution: .\Create_SQLAentJobSripts.ps1 .\ServerNameList.txt
param([String]$ServerListPath)
#Load the input file into an Object array
$ServerNameList = get-content -path $ServerListPath
#Load the SQL Server SMO Assemly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
#Create a new SqlConnection object
$objSQLConnection = New-Object System.Data.SqlClient.SqlConnection
#For each server in the array do the following..
foreach($ServerName in $ServerNameList)
{
Try
{
$objSQLConnection.ConnectionString = "Server=$ServerName;Integrated Security=SSPI;"
Write-Host "Trying to connect to SQL Server instance on $ServerName..." -NoNewline
$objSQLConnection.Open() | Out-Null
Write-Host "Success."
$objSQLConnection.Close()
}
Catch
{
Write-Host -BackgroundColor Red -ForegroundColor White "Fail"
$errText = $Error[0].ToString()
if ($errText.Contains("network-related"))
{Write-Host "Connection Error. Check server name, port, firewall."}
Write-Host $errText
continue
}
#IF the output folder does not exist then create it
$OutputFolder = ".\$ServerName"
$DoesFolderExist = Test-Path $OutputFolder
$null = if (!$DoesFolderExist){MKDIR "$OutputFolder"}
#Create a new SMO instance for this $ServerName
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $ServerName
#Script out each SQL Server Agent Job for the server
$jobs = $srv.JobServer.Jobs
#Using regex invocation courtesy of Jan Goyvaerts at regular-expressions.info
$regex = [regex] '[^a-zA-Z0-9-\ ]'
foreach ($job in $jobs)
{
$jobname = ".\$OutputFolder\" + $regex.Replace($Job.Name,'_') + ".job.sql"
$job.Script() | Out-File $jobname
}
}
Wednesday, February 29, 2012
[Replication] Running manually / Running by hand
Had to run some replication by hand while the agent was offline. Here's how to do it. I'd give thanks but I don't remember where I first read it. Probably Hilary Cotter. Thanks, Hilary!
SELECT subsystem,
REPLACE(command, '-Continuous', '')
FROM msdb.dbo.sysjobsteps
WHERE job_id IN ( SELECT job_id
FROM msdb.dbo.sysjobs
WHERE name LIKE 'yourreplnamehere%'
) --or whatever the job is called
AND
subsystem <> 'TSQL'
AND
subsystem <> 'snapshot'
That gives you the commands needed. We’ve removed the -Continuous
for simplicity. (otherwise it would continue to run, and you’d need 3 windows)
Login to the server as SQLService. Bring up a command window.
cd "\Program Files\Microsoft SQL Server\90\com\" (or wherever replication is installed; this was an old box)
logread.exe
distrib.exe (repeat for
each server as necessary)
Wednesday, February 22, 2012
[tuning] Statistics - when were they created... OR RECENTLY UPDATED?
(update 2013/08/07 - since it shows recent times...)
Courtesy of Nayan Raval & #SQLHelp, which led me to another article (http://blogs.solidq.com/fabianosqlserver/post.aspx?id=52&title=undocumented+option(querytraceon+%3Ctracenumber%3E)+and+trace+flags+2388%2C+2389%2C+2390) which documents 2 more undocumented trace flags.
How do you find out when statistics were created? If it's on an indexed field, when the index was created (crdate in sys.indexes). But statistics on non-indexed fields? Using Trace Flag 2388 changes the information that SHOW_STATISTICS returns.
DBCC TRACEON (2388)
DBCC SHOW_STATISTICS ('yourtablename','_WA_Sys_x')
DBCC TRACEOFF (2388)
Look for the row with the oldest Updated. The oldest update, depending on how often it's been updated, MAY show you when it was created.
And there's another use for this trace flag... when, aside from the most-recent date, was a statistic updated? STATS_DATE will show you the LAST time it was updated, but I recently had a problem where I knew the problem existed and quickly updated stats, then realized I wanted to know when BEFORE then it had been updated. One search to my blog later, code found, and there we go.
Courtesy of Nayan Raval & #SQLHelp, which led me to another article (http://blogs.solidq.com/fabianosqlserver/post.aspx?id=52&title=undocumented+option(querytraceon+%3Ctracenumber%3E)+and+trace+flags+2388%2C+2389%2C+2390) which documents 2 more undocumented trace flags.
How do you find out when statistics were created? If it's on an indexed field, when the index was created (crdate in sys.indexes). But statistics on non-indexed fields? Using Trace Flag 2388 changes the information that SHOW_STATISTICS returns.
DBCC TRACEON (2388)
DBCC SHOW_STATISTICS ('yourtablename','_WA_Sys_x')
DBCC TRACEOFF (2388)
And there's another use for this trace flag... when, aside from the most-recent date, was a statistic updated? STATS_DATE will show you the LAST time it was updated, but I recently had a problem where I knew the problem existed and quickly updated stats, then realized I wanted to know when BEFORE then it had been updated. One search to my blog later, code found, and there we go.
Tuesday, February 14, 2012
[VHD] Mounting a virtual disk easily
I’ve used Mark Russinovich’s “Disk2VHD” before (http://technet.microsoft.com/en-us/sysinternals/ee656415),
and found a clever trick on windows 7 boxes. (be VERY careful if you have the image file on the same disk that you imaged - don't dare boot off it up. I'd move it to a new machine)
Once you have the file on your new machine, use Disk
Partition to mount the VHD to pull stuff off as needed. That way you don’t need
to deal with Virtual PC most of the time.
sel
vdisk file="c:\users\yournamehere\desktop\imagename.vhd"
attach vdisk
Tuesday, January 31, 2012
[Oddities] There's a limit to a SET assignment... Dynamic SQL, strings, and NVARCHAR
I had a query against a table that would change based on the day (mytable_20120131). Copied and pasted it into a nvarchar(max) variable, tried to get it to run multiple times without luck. Odd error messages - number of selects doesn't match number of inserts, "incorrect syntax near the keyword 'on'" (just means there's a problem somewhere in the code), problem near a comma, etc. Was using nvarchar(max) since I'm plugging it into sp_executesql. Oh, and it's an explicit insert with over 140 fields.
Coworker had the bright idea to look at where the dynamic table name was added. Did a "select @sql", copied and pasted into notepad.
declare @sql nvarchar(max), @mytable nvarchar(max)
set @mytable = 'dbo.mytable'
set @sql = 'insert into mytable ([myfielda],[myfieldb],..." + @mytable + ' on ...'
select @sql
Here's what it looked like around dbo.mytable when I did the select:
"myfieldbo.mytable"
Huh?
Turns out, there's a length to the string assignment: 4000 characters. I normally don't run into it because my code normally looks like this when I need dynamic sql, but I didn't want to have to deal with splitting the 30 lines I'd need:
set @sql = 'insert into mytable ([myfielda],[myfieldb],.."
+ '[myfieldg],[myfieldh]...'
+ '[myfieldy],[myfieldz]...'
Fix? In this case, an easy one:
set @sql = N'insert into mytable ([myfielda],[myfieldb],..." + @mytable + N' on ...'
It appears that because it's all nvarchar-assigned, that it never runs into the limit.
Weird.
Coworker had the bright idea to look at where the dynamic table name was added. Did a "select @sql", copied and pasted into notepad.
declare @sql nvarchar(max), @mytable nvarchar(max)
set @mytable = 'dbo.mytable'
set @sql = 'insert into mytable ([myfielda],[myfieldb],..." + @mytable + ' on ...'
select @sql
Here's what it looked like around dbo.mytable when I did the select:
"myfieldbo.mytable"
Huh?
Turns out, there's a length to the string assignment: 4000 characters. I normally don't run into it because my code normally looks like this when I need dynamic sql, but I didn't want to have to deal with splitting the 30 lines I'd need:
set @sql = 'insert into mytable ([myfielda],[myfieldb],.."
+ '[myfieldg],[myfieldh]...'
+ '[myfieldy],[myfieldz]...'
Fix? In this case, an easy one:
set @sql = N'insert into mytable ([myfielda],[myfieldb],..." + @mytable + N' on ...'
It appears that because it's all nvarchar-assigned, that it never runs into the limit.
Weird.
Monday, January 30, 2012
[Table Partitioning] what is your lock escalation?
Bare bones query. This will give you the lock escalation for your partitioned tables. In our experience we've had good luck with AUTO (which is _not_ the default!), but the reason it's not the default is because some MS customers had deadlock issues with it enabled. For us, it prevents issues with queries blocking inserts.
Also, let me put a suggestion in your ear: don't go with just one table for inserts & queries. Create a partitioned table to catch live data, keep a week's worth of data. Create a mechanism to copy that into your history (what gets queried) table. Make the history table AUTO, and experiment with your live table. Compress the history. Make a view between the two if you need. Like much on this blog, let my pain be your gain.
SELECT distinct tables.name,
lock_escalation_desc
FROM sys.partitions
inner join sys.tables
on tables.object_id = partitions.object_id
and partition_number >1
order by name
Also, let me put a suggestion in your ear: don't go with just one table for inserts & queries. Create a partitioned table to catch live data, keep a week's worth of data. Create a mechanism to copy that into your history (what gets queried) table. Make the history table AUTO, and experiment with your live table. Compress the history. Make a view between the two if you need. Like much on this blog, let my pain be your gain.
SELECT distinct tables.name,
lock_escalation_desc
FROM sys.partitions
inner join sys.tables
on tables.object_id = partitions.object_id
and partition_number >1
order by name
Wednesday, January 25, 2012
[Code] Using EXECUTE AS with a trigger to capture changes when a user doesn't have permissions.
Had to build this to keep track of changes on certain tables in our environment. Due to limited permissions, we wound up with people hitting the table but the trigger not working right, because they hadn't been granted permissions. So the obvious solution is to grant permissions to both tables to a group, then add people into that group. Not an option for this particular scenario. Hence, my code.
Note that THIS WILL NOT WORK across databases, unless it’s
marked as TRUSTWORTHY. Planned
functionality, not a bug. Within a database it works.
New code bolded. I
created a user Change_User, a change-tracking table named change_mytable and gave Change_User select/insert permissions ONLY into change_mytable.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter trigger [dbo].[mytrigger]
on [dbo].[mytable]
with execute as 'change_user'
after update
as
BEGIN
SET NOCOUNT ON
declare @current_user nvarchar(128) --suser_sname()
is nvarchar(128)
execute as caller --caller is the original person running code
set @current_user = SUSER_SNAME() --get the name
of the user
--select @current_user --returns
my name when I run it
revert --go back to the EXECUTE AS user, ch_user
--Audit capture
if exists (select name from sysobjects where name = 'change_mytable')
begin
--print suser_sname() --returns
change_user
insert into change_mytable(
ID, name, updated_date,
change_login, dml_action)
SELECT
ID,
NAME, getdate(), @current_user, 'Update'
FROM
Deleted
end
END
GO
Friday, January 20, 2012
Adding locations to SSMS "Open File"
Been looking like this for years. Who knew, the right google and there it is. I take no credit for this, simply sticking it in my blog so that I can find it next time.
http://www.sqlservercentral.com/Forums/Topic907188-391-1.aspx
Todd Engen
http://www.sqlservercentral.com/Forums/Topic907188-391-1.aspx
Todd Engen
| Here's where you'd find that for SSMS with 2005. \HKCU\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\Open Find\Places\UserDefinedPlaces Add subkeys Place0,Place1,Place(n) Under each subkey add two REG_SZ values Name = "Shortcut Name" Path = "Folder location" |
Tuesday, January 17, 2012
[Network] Specified network name is no longer available
We've seen this in our environment for a while. Tried moving backup times around, that helped some but not in every case. TCP Chimney Offload didn't seem to be our issue either, though it has been for others.
Got this nugget courtesy of kgerde, which seems to be a near-magic-bullet for us.
http://www.sqlservercentral.com/Forums/Topic768429-391-1.aspx
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanworkstation\parameters
Create a New DWORD value with the name: SessTimeout
set the value: 360 keep it Hexadecimal
(This value might not work for your backup but it was high enough for mine. If this doesn't work increase the value and try again.)
Got this nugget courtesy of kgerde, which seems to be a near-magic-bullet for us.
http://www.sqlservercentral.com/Forums/Topic768429-391-1.aspx
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanworkstation\parameters
Create a New DWORD value with the name: SessTimeout
set the value: 360 keep it Hexadecimal
(This value might not work for your backup but it was high enough for mine. If this doesn't work increase the value and try again.)
Subscribe to:
Posts (Atom)