Gather last 10 Error & critical events from all Windows Event Logs


Windows PowerShell 3.0 cmdlet:
get-winevent -FilterXPath “*[System[(Level=1 or Level=2)]]” -MaxEvents 10

Posted in Uncategorized

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

 

SSMS_Complete

 

Posted in Uncategorized

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

BackupAll_db1

 

 

Databases are backed up to Default backup folder

 

BackupAll_db2

Posted in Backup, Scripting, SQL Power shell, SQL Server

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

Posted in DMV, SQL Server

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

Posted in DB Management, SQL Power shell, SQL Server, SSMS

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:

MerfeResults_MultiServerQuery

Posted in Uncategorized

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 )

XEventSession_SetOptions

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
Posted in SQL Server, XEvents
Follow

Get every new post delivered to your Inbox.