Windows PowerShell 3.0 cmdlet:
get-winevent -FilterXPath “*[System[(Level=1 or Level=2)]]” -MaxEvents 10
February 21, 2013
Gather last 10 Error & critical events from all Windows Event Logs
January 25, 2013
Complete SSMS is available for free in SQL 2012 SP1
In SQL 2012 SP1, Full SSMS is available as a free download.
You can download SSMS (SQL 2012 SP1) from http://www.microsoft.com/en-us/download/details.aspx?id=29062
January 21, 2013
Backup all databases using Backup-SqlDatabase cmdlet
Launch SSMS
Right click on databases node,
Select Menu item “Start PowerShell”
Type in following command to backup all databases to Default backup folder
dir | Backup-SqlDatabase
Databases are backed up to Default backup folder
DMV – Windows Operating System version information
You can use the DMV on SQL 2008 R2 and above to query Windows OS information
SELECT * FROM sys.dm_os_windows_info
References:
MSDN: http://msdn.microsoft.com/en-us/library/hh204565.aspx
Related Forum thread: http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/9753f897-82e3-4602-b7c9-650c7239e83d
January 18, 2013
Set autoclose property for all database
in SSMS , Right click on database node in Object Explorer, Select menu item “Start PowerShell”
Type in following in power shell window to set Auto Close on all databases
dir | foreach {$_.AutoClose = $true; $_.Alter()}
If you would prefer to set on specific database that starts with name “DB”
dir | where-object {$_.Name.StartsWith("DB")} | foreach {$_.AutoClose = $true; $_.Alter()}
Related forum thread: http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/c2780156-7a6f-4b29-a2f6-f4bfa682379c/#0c0c375b-e584-4c3f-9d40-482ca1a15999
January 7, 2013
Multi-server results options to merge results
You could set the Multi Server results Options Merge Results = “False” and rerun the same query. Now you will see multiple result sets. This could help you isolate the issue why the result schema returned by specific server is not the same as other result sets. ( Related forum thread: http://social.msdn.microsoft.com/Forums/en-US/sqlkjappmsmgmt/thread/c4cda4bc-7b4d-4367-99a9-3268d8196feb/ )
in SSMS -> Tools -> Options
Query results
-> SQL Server
———> Multi Server Results
Screenshot:
January 5, 2013
Capturing session level options settings on all sessions in SQL 2012 using XEvents
You could use XEvents to capture options set at all existing sessions & any future new sessions. Here is sample session definition. (I have tested it on SQL 2012 SP1). Please replace the file path in the following script to a valid file path on your machine where SQL Server service account has read, write privileges to create and write to XEvent file.
(Related forum thread: http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/feede34e-401c-44a6-8dcd-6a096ec84c38 )
T-SQL
———
CREATE EVENT SESSION [set_options] ON SERVER ADD EVENT sqlserver.existing_connection(SET collect_database_name=(1),collect_options_text=(1)) ADD TARGET package0.event_file(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\set_options.xel') WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF) GO ALTER EVENT SESSION [set_options] ON SERVER STATE=START
January 3, 2013
Scripting database using Smo.Scripter
You can automate script generation using SMO and power shell using the following power shell script. Script generated would be similar to the script generated using “Generate Script Wizard” in ssms
You can download script from here
This posting is provided “AS IS” with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
January 2, 2013
How to specify DB name in CMS Query
You can set the database name for the registered server in the “Connection Properties” tab. Then open a “New Query” on the server group and run your query
Related forum thread: http://social.msdn.microsoft.com/Forums/en-US/sqlkjappmsmgmt/thread/69576d3a-20f5-4641-9e55-0e805429839a
December 30, 2012
2012 in review
The WordPress.com stats helper monkeys prepared a 2012 annual report for this blog.
Here’s an excerpt:
600 people reached the top of Mt. Everest in 2012. This blog got about 9,500 views in 2012. If every person who reached the top of Mt. Everest viewed this blog, it would have taken 16 years to get that many views.
