{"id":6101,"date":"2015-05-17T00:01:00","date_gmt":"2015-05-17T00:01:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2015\/05\/17\/weekend-scripter-manage-sql-server-virtual-log-file-with-powershell\/"},"modified":"2019-02-18T10:29:34","modified_gmt":"2019-02-18T17:29:34","slug":"weekend-scripter-manage-sql-server-virtual-log-file-with-powershell","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/weekend-scripter-manage-sql-server-virtual-log-file-with-powershell\/","title":{"rendered":"Weekend Scripter: Manage SQL Server Virtual Log File with PowerShell"},"content":{"rendered":"<p><b style=\"font-size:12px\">Summary<\/b><span style=\"font-size:12px\">: Guest blogger, Microsoft PFE, Jonathan Yong, talks about using Windows PowerShell to manage the SQL Server virtual log file.<\/span>\nMicrosoft Scripting Guy, Ed Wilson, is here. This is the week of new guest bloggers it seems. Today, please welcome Microsoft PFE, Jonathan Yong&#8230;\nSome time back, I was discussing a SQL Server administration use case with another engineer. The customer&rsquo;s DBA wanted to get a list of all virtual log files for each database in the instance to decide which database&rsquo;s log file needed tuning. The easy part is that <b>DBCC LOGINFO<\/b> will provide this information.\nThe tricky part is that the DBA wanted to store the output data from <b>DBCC LOGINFO<\/b> into a database table. In the output of <b>DBCC LOGINFO<\/b>, there is a column call <b>CreateLSN<\/b>, which is a very long field. We couldn&rsquo;t find the right data type and size to store it (despite what the SQL Server documentation claims).\nDue to its length, an error occurred when we tried to insert the value of <b>CreateLSN<\/b> into the database table. We couldn&rsquo;t ignore this field by using a subquery. We also wanted to avoid using <b>OPENROWSET<\/b> because it requires enabling different permissions.\nWhat came to my mind is to try to use Windows PowerShell to automate this. This approach worked, and the solution is not that complicated. The general idea is as follows:<\/p>\n<ul>\n<li>Store the list of SQL Server instances that you want to target in a text file.<\/li>\n<li>Get the list of databases for each instance in the text file, and create a PSObject with the instance and database name.<\/li>\n<li>For each instance-DB object pair, run DBCC LOGINFO against the database, and create a PSObject with a custom property that maps to the output column of DBCC LOGINFO (except <b>CreateLSN<\/b>, which is the problematic column).<\/li>\n<li>With this information in hand, the rest is up to your imagination as to what you want to do with it.<\/li>\n<\/ul>\n<p>Following is the complete script. You can also download the script from the Script Center Repository: <a href=\"https:\/\/gallery.technet.microsoft.com\/scriptcenter\/Managing-SQL-Server-1e2b10cd\" target=\"_blank\">Managing SQL Server Virtual Log File with Powershell<\/a>.<\/p>\n<p style=\"margin-left:30px\">$sqlListAllDbs = &#8220;sp_databases&#8221;<\/p>\n<p style=\"margin-left:30px\">$sqlInstance = get-content .sqlserverInstanceList.txt<\/p>\n<p style=\"margin-left:30px\">$dbList&nbsp; = $sqlInstance | % {<\/p>\n<p style=\"margin-left:30px\">&nbsp;&nbsp;&nbsp; $instanceName = $_<\/p>\n<p style=\"margin-left:30px\">&nbsp;&nbsp;&nbsp; #Write-verbose $instanceName<\/p>\n<p style=\"margin-left:30px\">&nbsp;&nbsp;&nbsp; Invoke-Sqlcmd -Query $sqlListAllDbs -ServerInstance $instanceName&nbsp; | % {<\/p>\n<p style=\"margin-left:30px\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $dbName = $_.Database_name<\/p>\n<p style=\"margin-left:30px\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; #Write-verbose $dbName<\/p>\n<p style=\"margin-left:30px\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $o = new-object psobject -Property @{<\/p>\n<p style=\"margin-left:30px\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; InstanceName = $sqlInstance<\/p>\n<p style=\"margin-left:30px\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DbName = $dbName<\/p>\n<p style=\"margin-left:30px\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<\/p>\n<p style=\"margin-left:30px\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $o &nbsp;&nbsp;<\/p>\n<p style=\"margin-left:30px\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<\/p>\n<p style=\"margin-left:30px\">}<\/p>\n<p style=\"margin-left:30px\">$dbList | % {<\/p>\n<p style=\"margin-left:30px\">&nbsp;&nbsp;&nbsp; $dbObject = $_<\/p>\n<p style=\"margin-left:30px\">&nbsp;&nbsp;&nbsp; Invoke-Sqlcmd -Query &#8220;dbcc loginfo&#8221; -ServerInstance $dbObject.InstanceName&nbsp; -Database $dbObject.DbName&nbsp; | % {<\/p>\n<p style=\"margin-left:30px\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $o = new-object psobject -Property @{<\/p>\n<p style=\"margin-left:30px\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Instance = $dbObject.InstanceName<\/p>\n<p style=\"margin-left:30px\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Database =&nbsp; $dbObject.DbName<\/p>\n<p style=\"margin-left:30px\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;FileId = $_.FileId<\/p>\n<p style=\"margin-left:30px\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FileSize = $_.FileSize<\/p>\n<p style=\"margin-left:30px\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; StartOffset = $_.StartOffset<\/p>\n<p style=\"margin-left:30px\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FSeqNo = $_.FSeqNo<\/p>\n<p style=\"margin-left:30px\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Status = $_.Status<\/p>\n<p style=\"margin-left:30px\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Parity = $_.Parity<\/p>\n<p style=\"margin-left:30px\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<\/p>\n<p style=\"margin-left:30px\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $o&nbsp;&nbsp;&nbsp;<\/p>\n<p style=\"margin-left:30px\">&nbsp;&nbsp;&nbsp; }<\/p>\n<p style=\"margin-left:30px\">} |&nbsp; sort Instance,Database |<\/p>\n<p style=\"margin-left:30px\">Out-GridView<\/p>\n<p style=\"margin-left:30px\">#ft Instance,Database,FileId,FileSize,StartOffset,FSeqNo,Status,Parity\nThank you, Jonathan, for an interesting post.\nI invite you to follow me on <a href=\"http:\/\/bit.ly\/scriptingguystwitter\" target=\"_blank\">Twitter<\/a> and <a href=\"http:\/\/bit.ly\/scriptingguysfacebook\" target=\"_blank\">Facebook<\/a>. If you have any questions, send email to me at <a href=\"http:\/\/blogs.technet.commailto:scripter@microsoft.com\" target=\"_blank\">scripter@microsoft.com<\/a>, or post your questions on the <a href=\"http:\/\/bit.ly\/scriptingforum\" target=\"_blank\">Official Scripting Guys Forum<\/a>. See you tomorrow. Until then, peace.\n<b>Ed Wilson, Microsoft Scripting Guy<\/b><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Summary: Guest blogger, Microsoft PFE, Jonathan Yong, talks about using Windows PowerShell to manage the SQL Server virtual log file. Microsoft Scripting Guy, Ed Wilson, is here. This is the week of new guest bloggers it seems. Today, please welcome Microsoft PFE, Jonathan Yong&#8230; Some time back, I was discussing a SQL Server administration use [&hellip;]<\/p>\n","protected":false},"author":596,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[56,581,3,477,61,45],"class_list":["post-6101","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-guest-blogger","tag-jonathan-yong","tag-scripting-guy","tag-sql","tag-weekend-scripter","tag-windows-powershell"],"acf":[],"blog_post_summary":"<p>Summary: Guest blogger, Microsoft PFE, Jonathan Yong, talks about using Windows PowerShell to manage the SQL Server virtual log file. Microsoft Scripting Guy, Ed Wilson, is here. This is the week of new guest bloggers it seems. Today, please welcome Microsoft PFE, Jonathan Yong&#8230; Some time back, I was discussing a SQL Server administration use [&hellip;]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/6101","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/596"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=6101"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/6101\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=6101"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=6101"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=6101"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}