DallasDBAs.com

Explaining SQL Server in plain english

SQLDork

TIL: Duplicate Registered Server Entries

by Leave a Comment

@SQLDork continues to make my brain hurt with questions like these – Ed.

So i’m doing my usual set of work, and i think to myself, “What would happen if i made two registered server entries that point to the same server?”. Naturally, i asked Kevin (b|t) for his thoughts on it, and naturally, he told me to test it, so i did:

Running SELECT @@Servername against both these groups gives these results:

Interestingly enough, there’s that _2 appended to the second copy of server SQLPD07, which means somebody at Microsoft (t) thought this same thing at some point, so they included it in the SSMS code.

That’s all i (b|t) got for this post, this is just the part where i link to my twitter several times more than necessary, because reasons™

Thanks for reading,

Kevin and Liz

TIL: Dismount-DbaDatabase and Mount-DbaDatabase

by Leave a Comment

We have a double feature for today’s dbatools blog post, as these two commands go hand-in-hand.

Todays commands: Dismount-DbaDatabase and Mount-DbaDatabase

Dismount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test'

Mount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test'

Detach-DbaDatabase and Attach-DbaDatabase can be used as aliases. For the purpose of this blog i’m using the official names, because reasons™

Note that the .mdf and .ldf files are still present, meaning detaching a database can keep you from deleting a database you were told wasn’t needed anymore, only to instantly have someone ask why they can’t get to the database they need. If the files are still there, you can attach it and it’s good as new.

Dismount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test' `
    -Force

If the database is part of a mirror or something, -Force can be used to detach it anyways.

Dismount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test' `
    -UpdateStatistics

This will update the statistics of the database before it’s detached.

$testAttach = New-Object System.Collections.Specialized.StringCollection
$testAttach.Add("C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\test.mdf")
$testAttach.Add("C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\test_log.ldf")

Mount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test' `
    -FileStructure $testAttach

The -FileStructure parameter can be used to point out where the files to attach are. If not specified, it’ll use backup history to figure out what you mean, if possible.

Mount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test' `
    -DatabaseOwner 'sa'

The -DatabaseOwner parameter lets you set the, well, database owner, when you attach the database. Defaults to sa.

Dismount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test', 'square[]brackets'

The -Database parameter can take multiple values.

That’s all i got for this one, not too complicated unless you’re doing something with credentials and stuff. Follow me (b|t) on twitter!

Previous Dbatools posts:

Backup-DbaDatabase
Restore-DbaDatabase
Get-DbaLastBackup
Get-DbaAgentJobHistory
Measure-DbaBackupThroughput
Get-DbaErrorLog

Future Dbatools posts:

None yet!

TIL: Get-DbaErrorLog

by Leave a Comment

Ed: SQLDork is a Junior DBA on the team here, learning the DBATools in addition to normal DBA stuff™

Today’s dbatools command is Get-DbaErrorLog, which is very similar to a script we frequently run on client servers to check for errors that need fixing.

Get-DbaErrorLog `
    -SqlInstance PRECISION-M7520\SQL2016 `
    | Select-Object `
        SqlInstance, Source, LogDate, Text `
    | Out-Gridview

Basic command usage, using Select-Object to filter out the columns i don’t really care about, then piping the results to Out-Gridview.

Get-DbaErrorLog `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -LogNumber 1, 0 `
    | Select-Object `
        SqlInstance, Source, LogDate, Text `
    | Out-Gridview

Adding the -LogNumber Parameter to check the archived errorlogs, defaults to current log only.
NOTE: If you want the results ordered by date, the values passed into -LogNumber must be in descending order.

Get-DbaErrorLog `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Source spid5s `
    | Select-Object `
        SqlInstance, Source, LogDate, Text `
    | Out-Gridview

The -Source parameter lets us filter out some stuff™. I can’t think of a better way to describe that. The documentation uses -Source Logon as an example, but my sample data didn’t have that so i used a spid.

(Ed: The Source can be a spid, “logon”, “Server”, or anything you see in the errorlog Source column in the SSMS Log Viewer)

Get-DbaErrorLog `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -LogNumber 5, 4, 3, 2, 1, 0 `
    -Text "instance" `
    | Select-Object `
        SqlInstance, Source, LogDate, Text `
    | Out-Gridview

Using -Text as a rudimentary where clause, but this one only supports 1 string. We can do better.

Get-DbaErrorLog `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -LogNumber 5, 4, 3, 2, 1, 0 `
    | Select-Object `
        SqlInstance, Source, LogDate, Text `
    | Where { `
                ( `
                $_.Text -Like "*CheckDB*" `
            -or $_.Text -Like "*Error*" `
            -or $_.Text -Like "*UTC*" `
                ) `
           -and ( `
                $_.Text -NotLike "*found 0 errors*" `
           -and $_.Text -NotLike "*finished without errors*" `
           -and $_.Text -NotLike "*informational message*" `
                ) `
            } `
| Out-GridView

This formatting feels very strange to me, but it does a very similar thing to that errorlog query we use that i mentioned earlier.

(Ed: The Where-Object or Where is PowerShell, not DBATools, click here)

Shoutout to Kevin (b|t) for helping me figure out the syntax on this, and also for pointing out that he changed the -SqlInstance parameter to his own machine name when he was testing this. Saved me a bunch of time troubleshooting connection errors.

Speaking of connection errors, follow me (b|t) on twitter, even though i don’t really tweet that much. Unless i retweet pictures of dogs, that’s always a possibility.

Pluralsight course for new SQL Server DBAs

TIL: Measure-DbaBackupThroughput

by Leave a Comment

Today’s dbatools command: Measure-DbaBackupThroughput

This one’s a smaller command, but that doesn’t mean it’s without use. If you need information on backup speeds (throughput) or early warning of possible hardware issues, such as in a RAID 5 setup, this can help you.

Measure-DbaBackupThroughput `
    -SqlInstance PRECISION-M7520\SQL2016 `
    | Select-Object `
        SqlInstance, Database, AvgThroughput, `
        AvgSize, AvgDuration, MinThroughput, `
        MaxThroughput, MinBackupDate, `
        MaxBackupDate, BackupCount `
    | Out-Gridview

Basic command usage, then we pipe it through a select statement and into a gridview. Selecting only those specific columns to cut down on redundant information, and also to get it all in one picture easier. Only measures full backups by default.

 

Measure-DbaBackupThroughput `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database test `
    -Last `
    | Select-Object `
        SqlInstance, Database, AvgThroughput, `
        AvgSize, AvgDuration, MinThroughput, `
        MaxThroughput, MinBackupDate, `
        MaxBackupDate, BackupCount `
    | Out-Gridview

Maybe we don’t want all the backup history for every database. Maybe we just want the most recent history for one database. The -Database parameter lets us specify which databases to check, and the -Last parameter returns only the most recent backup of each type.



Measure-DbaBackupThroughput `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Since (Get-Date).AddDays(-28) `
    | Select-Object `
        SqlInstance, Database, AvgThroughput, `
        AvgSize, AvgDuration, MinThroughput, `
        MaxThroughput, MinBackupDate, `
        MaxBackupDate, BackupCount `
    | Out-Gridview




The -Since parameter combined with Get-Date lets us get all the information since a certain point in time relative to now. Presumably this can also be done with a hard-coded date, but that seems like an edge-case scenario to me.




 


Measure-DbaBackupThroughput `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database test `
    -Type Differential `
    | Select-Object `
        SqlInstance, Database, AvgThroughput, `
        AvgSize, AvgDuration, MinThroughput, `
        MaxThroughput, MinBackupDate, `
        MaxBackupDate, BackupCount `
    | Out-Gridview




Use  the -Type parameter to get information on different kinds of backups, accepts “Full”, “Log” or “Differential”.

Note: -Type parameter does not accept multiple values with commas, probably because the results would then have to have up to 3 times as many rows, one for each backup type.




 


Measure-DbaBackupThroughput `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -ExcludeDatabase msdb, master, model, tempdb `
    | Select-Object `
        SqlInstance, Database, AvgThroughput, `
        AvgSize, AvgDuration, MinThroughput, `
        MaxThroughput, MinBackupDate, `
        MaxBackupDate, BackupCount `
    | Out-Gridview




If we don’t want specific databases, we can use -ExcludeDatabase for that. Useful for only checking the user databases. You don’t have to specifically exclude tempdb, but i did it anyways, just for fun.




Speaking of just for fun, i also took backups of the test database onto 3 different drives, C and two external drives. The results are somewhat less useful than i’d like, though:




Speaking of external drives, go follow me (b|t) on twitter, where i mostly talk about two things:


    

  1. Things related to SQL Server.
    2. 

  2. Things that may or may not be related to SQL Server.
    3. 



Previous Dbatools posts:


Backup-DbaDatabase

Restore-DbaDatabase

Get-DbaLastBackup

Get-DbaAgentJobHistory


Future Dbatools posts:


None yet!


TIL: Get-DbaAgentJobHistory


 by  Leave a Comment 
Another blog post, another dbatools command.


Today’s command: Get-DbaAgentJobHistory


Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    | Select-Object * `
    | Out-Gridview 


Basic command, gets all the job history info and pipes it to gridview, because there’s a LOT of info here. Probably a good idea to filter it down to the interesting stuff.


The Duration column behaves a bit weird:

For durations under 1 minute, it’ll say x number of seconds or milliseconds. (Yellow arrow in picture below)

Anything over a minute gets formatted as minutes:seconds:milliseconds. (Red arrow)

Unless it’s over an hour, in which case it’s hours:minutes:seconds, which looks exactly the same. (Blue arrow)




Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    | Out-Gridview


Not using Select-Object * cuts down on columns a lot, but we can do better.


Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -StartDate 2020-05-10 `
    | Out-Gridview




Using -StartDate to only return all the job history from the last 3 days, default value is Jan 1, 1900.


I’m like, 90% sure this job doesn’t have any results from the 20th century though.


Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -StartDate 1900-01-01 `
    -EndDate 1999-12-31 `
    | Out-Gridview


This doesn’t even pull up a gridview on account of there’s no data, which means my earlier theory was right! On a more useful note, -EndDate lets us exclude results from a given date onwards. Default value is $(Get-Date).


Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -StartDate 2020-05-01 `
    -ExcludeJobSteps `
    | Out-Gridview




If you don’t care about which steps were successful and which failed, -ExcludeJobSteps cuts it down to reporting the job run attempt as a whole.


Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -StartDate 2020-05-01 `
    -ExcludeJobSteps `
    | Select-Object * `
    | where Status `
        -Match Failed `
    | Out-GridView




Filter to just the failure results. Useful if you want to check which jobs failed and when.


Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -StartDate 2020-05-01 `
    -ExcludeJobSteps `
    | Select-Object `
        SqlInstance, StartDate, EndDate, `
        Duration, Status, Job, InstanceID, `
        Message, StepName, SqlSeverity, JobName, `
        RunStatus, RunDate, RunDuration, RetriesAttempted `
    | where Status `
        -Match Failed `
    | Export-Csv `
        -Path C:\output.csv `
        -NoTypeInformation




Two things here:

One: Filter out the columns we don’t care about in Select-Object, we don’t need to be doing Select * From * Where * = * here.

Two: Output the data to a csv file so we can play around with it in excel.


Three: There is no three, this is the end of the post. This is also the part where i (b|t) link to my twitter a bunch of times to generate traffic. Also because it’s funny.


Previous Dbatools posts:


Backup-DbaDatabase

Restore-DbaDatabase

Get-DbaLastBackup


Future Dbatools posts:


None yet, unless there are and i forgot to update this part. If that’s the case, go yell at me on twitter!


TIL: Get-DbaLastBackup


 by  Leave a Comment 
(This post comes from one of our Junior DBAs – @SQLDork)


I’ve been learning dbatools over the past week or so, you can read the previous blog posts here and here.


You can read the current one here, or just scroll down i guess.


Today’s Command: Get-DbaLastBackup


Get-DbaLastBackup `
    -SqlInstance PRECISION-M7520\SQL2016


Nothing fancy, just spits out the last backup info for each database on the box.




Get-DbaLastBackup `
    -SqlInstance PRECISION-M7520\SQL2016 | `
    Select-Object *


This gives us some more useful information, telling us the number of days since the database was created/backed up, the backup type, etc.




Get-DbaLastBackup `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database test


Specify which database you want to check, defaults to all of them.


Get-DbaLastBackup `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -ExcludeDatabase test


Specify that you don’t want to check a certain database. Again, not using this or -Database will return data on all of them.


Get-DbaLastBackup `
    -SqlInstance PRECISION-M7520\SQL2016 | `
    Select-Object * | `
    Out-Gridview


Formatting, puts the data in a grid. Useful for pasting into excel or something. Though there’s no way to copy the headers as far as i can tell, so you’ll have to put those in manually.


(Click image to enlarge in new tab)


Get-DbaLastBackup `
    -SqlInstance PRECISION-M7520\SQL2016 | `
    Select-Object Database,RecoveryModel | `
    Out-Gridview


Only select some columns, rather than all of them.




Get-DbaLastBackup `
    -SqlInstance PRECISION-M7520\SQL2016 | `
    Select-Object * | `
    Export-Csv `
    -Path C:\output.csv `
    -NoTypeInformation


How about we pipe the output to excel/CSV directly instead of pasting it in? Sounds like a good idea to me!


(Click image to enlarge in new tab)


 


That’s all i (b|t) have for today, but it wouldn’t be a SQLDork blog post without several links to my twitter at the end!


(follow my twitter)