Tuesday, May 13, 2014

SSMS Crashes while opening New Query on SQL Server 2012 on windows 2012 R2

We installed SQL Server 2012 on Windows Server 2012 R2, After completion of installation I click on New Query from SSMS then SSMS is getting crashed.

Fix :

You need to start the printer spooler service and set to Automatic on the local server to get it fixed.

Search on the technet site to get complete information about this error.

 

Alwayson replica is showing database in Synchronizing state only


On AlwaysOn High availability if database is showing Synchronizing for a while, then the below steps may helps you to fix the issue, I observed if t-log is not applied on secondary replica then we may see that error.

From Secondary Replica

1. Verify the secondary database is in Synchronizing state or not if it is in synchronizing state move to 2nd step

2. AlwaysOnHigh Availability -- Availability Groups -- Availability Databases

3.  Right click on the database which is not in sync -- Remove Secondary Database - OK

4. Now the database will not completely remove from the Availabililty Databases, If you right click
on it, it shows Join Availability group
5. SSMS - Database -- Check the problematic database should come from Synchronizing state to
restoring state

6. Now Right click on database - Tasks - Restore - Transaction Log

7. Verify what is the last transaction log backup restored on this database from "select the transaction
log backups to restore" window
8. We need to restore the remaining backups after last backup to latest transaction log manually
9. Make sure you should select NoRecovery option till last transaction log backup restores

10. After completion of all T-Log backups the database status should display Restoring
11. AlwaysOnHigh Availability -- Availability Groups -- Availability Databases
12. Right click on database which you are working on -- Join Availability Group -- OK
13. Right click on database - Refresh (It should display green sync icon)
14. Verify the Alwayson Dashboard -- Now the database will be in sync

 

Friday, May 9, 2014

How to recover unsaved queries from SQL Server Management Studio

How to recover unsaved queries from SQL Server Management Studio

We developed a script and suddently SSMS crashed and we lost our script, how can we recover that unsaved query

Method  - 1
Try on the following locations on Windows 7
C:\Users\\Documents\SQL Server Management Studio\Backup Files\Solution1
(OR)
C:\Users\AppData\Local\Temp  -- Search for *.sql files and try your luck.

Method - 2   -- Use the below query and try to recover the query

Select t.text, q.last_execution_time from sys.dm_exec_query_stats as q
cross apply sys.dm_exec_sql_text(q.sql_handle) as t
order by q.last_execution_time desc

Note : This will not work always but you can try your luck.

Thursday, May 8, 2014

Allow trigger to fire others in SQL Server


Allow trigger to fire others

SQL Server allows nested triggers upto 32 levels, Usually nested triggers achieves to perform a series of tasks in a transaction. An event initiates a trigger which can start firing second trigger and so on. Because this trigger is handling within a transaction, a failure at any level causes entire transaction to be roll back. If any trigger causes infinite loop as a safe measure if it exceeds maximum nested level it will automatically terminated.

To Allow triggers to fire other triggers

On SSMS – Right click on server name – properties – Advanced – Allow trigger to fire others – True / False

Using T-SQL

EXEC sys.sp_configure 'nested triggers', '1'
GO
RECONFIGURE WITH OVERRIDE
GO

 

Wednesday, May 7, 2014

Generate Disk Space HTML report using Powershell


 

$Serverslist = get-content(".\clientlist.txt")

$html =""

$SizeInGB=@{Name="Size(GB)"; Expression={"{0:N2}" -f ($_.Capacity/1GB)}}
$FreespaceInGB=@{Name="Freespace(GB)"; Expression={"{0:N2}" -f ($_.Freespace/1GB)}}
$PercentFree=@{Name="PercentFree(%)";Expression={[int](($_.Freespace/$_.Capacity)*100)}}
$ServerName = @{Expression={$_.__Server};Label="Server Name"}

$outputvalues =@()
$serverlist = @()
$notaccessible= @() 

Write-output "Gathering Disk Usage Information........"
foreach ($server in $Serverslist) {
try
{
$ErrorActionPreference = 'Stop'

$outputvalues = gwmi -query "Select  __SERVER,  Name, Capacity, FreeSpace FROM win32_volume where not Name like '\\%Volume%' and drivetype <>5" -Computer $server -Credential $credential |
             Select-Object $ServerName,  Name, $SizeInGB, $FreespaceInGB, $PercentFree             
$serverlist = $serverlist + $outputvalues 
}
catch
{
 $FailedList = write-warning('failed to access "{0}":{1} in "{2}"' -f $server, $_.Exception.Message, $_.InvocationInfo.ScriptName) 
 $FailedList
 $notaccessible += $server + " "  + $_.Exception.Message + "
" } } $serverlist | ConvertTo-HTML -as table -head $html -body "<|h2> Offline Machines

$notaccessible

<|h2> Disk Space Report" | out-file "C:\diskspace_report.html--$((get-date).Tostring('yyyymmdd')).html"

Monday, May 5, 2014

Review Login permissions in SQL Server

How to check whether my user has permissions on any database using T-SQL

HAS_PERMS_BY_NAME -- Evaluates the permissions of the current user

Below query returns 1 if the user has permissions on the msdb database else 0

Use msdb
go
SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'ANY');

Below query is to verify server level permission for the current user

SELECT HAS_PERMS_BY_NAME(null, null, 'VIEW SERVER STATE');


fn_my_permissions   -- Returns permissions of the current user on database / server / an object

Below query returns what permissions does the user has on the database

SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO

Below query returns what permissions does the user has on the server

SELECT * FROM fn_my_permissions(NULL, 'SERVER');
GO

Below query returns what permissions does the user has on the specified object (ie., table / view)

SELECT * FROM fn_my_permissions('dbo.tbl_references', 'OBJECT')
GO

Below query helps to review what are the permissions for login User3 on the current database

Use KalyanDB
EXECUTE AS USER = 'User3';
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO
Revert

If you dont specify Revert then the current session will get user3 privileges only so we need to specify revert after completion of Execute as user.