steps taken to discover source of account lockout

February 26, 2015

*microsoft network monitor with authentication filter to capture pre authentication failures (program files)
*eventcombmt to parse security logs (success and failure) on domain controller event code 644 675 4771 4625 which revealed pre authentication failures and source machine, (logs stored in c:\temp, app in desktop\lockout)
*lockout status to unlock account, (located on desktop\lockout)

used to above to track the lockouts to a server. temporarily disabled kerberos pre authentication until we can work out what on the server is causing the lockouts

view database permissions

February 20, 2015

SELECT princ.name principal_name ,
princ.type_desc principal_type ,
perm.permission_name ,
perm.state_desc permission_type ,
obj.name object_name ,
s.name schema_name ,
obj.type_desc object_type
FROM sys.database_principals princ
LEFT JOIN sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id]
LEFT JOIN sys.schemas s ON s.schema_id = obj.schema_id

Create a Gantt chart effect in SSRS

February 19, 2015

I want to create something like this:

Capture2

http://pnarayanaswamy.blogspot.co.uk/2010/09/range-bar-chart-gantt-chart-using-ssrs.html

Narayana Swamy Palla’s Blog_ Range Bar Chart (Gantt Chart) using SSRS 2008

example rdl, change extension to rdl

Database is stuck ‘In Recovery’ with an unacceptably long delay before it is available and you dont care about any pending rollbacks completing.

February 19, 2015

Stop the sql server service
Rename the log file of the database that is in recovery
start the service

alter database XXX set emergency, single_user
alter database xxx rebuild log on (NAME = ‘log file name’, filename = ‘G:\Logs\log file name.ldf’)
alter database xxx set online, multi_user

the database is back online and the rollback has been partially completed

sort tempdb session usage by size

February 10, 2015

select * from tempdb.sys.dm_db_session_space_usage
order by internal_objects_alloc_page_count desc

Start up server in master only recovery mode, allowing only access to sqlcmd

January 28, 2015

on a command line
net start MSSQL$instancename /f /T3608 /m”SQLCMD”

then logon using sqlcmd

Access Denied error when attempting to access Oracle data via a linked server

January 27, 2015

Problem: The OLE DB provider “OraOLEDB.Oracle” for linked server “X” reported an error. Access Denied
Cannot get the column information from OLE DB provider “OraOLEDB.Oracle” for linked server “X”

Solution: Go to the OraOLEDB.Oracle provider, check Allow in process. Then restart the server service.

Can be scripted using EXEC master.dbo.sp_MSset_oledb_prop N’OraOLEDB.Oracle’, N’AllowInProcess’, 1

administrative share

January 20, 2015

\\servername\c$

this is an administrative share that only administrators can access. If your account is not an admin then you will not have access. Instead set up sharing via the security tab within windows explorer.

Resource semaphore waits/queries not running when there is seemingly enough memory for them to run

January 5, 2015

http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx

•Resource Semaphore checks for waiting queries in its queue.
•If it finds a waiting query, it puts the new query into a wait queue for fairness.
•The wait queue is designed on first-come-first-served basis with small weight to favour small queries.

Small queries are classed as queries requesting than 5MB

Example: if there is 10GB memory free on the server then the following will happen

•A query requesting 11GB is executed it will wait for memory to become available with a resource_semaphore wait
•A query requesting 1GB is then executed, it will see that the above query is already waiting and so go into the queue. This query is not small (not less than 5MB) and there is already something in the queue ahead of it so the query will also go into resource_semaphore wait status.

The query goes into a wait status despite there being enough memory available for the query to run because 1. queue is first come first served 2. Query is not classed as small

email csv attachment using sp_send_dbmail formatted for Excel

January 5, 2015

Sometimes you want to email the result of a query as an attachment and you would like it to open nicely in excel. By default the attachment will look horrific in excel but you can force it to look nice (1 field from result set mapping to 1 column in excel) by adding the following line to the top of your attachment

“sep=,”

To automatically do this use the following as a template when using sp_send_dbmail . Substitute in your column names and table name where appropiate

SET @Column1Name = ‘[sep=,’ + CHAR(13) + CHAR(10) + ‘Column1]’
SET @Query = ‘SELECT Column1 AS ‘ + @Column1Name + ‘, Column2, Column3 FROM myTable’

Youll also want to make sure that you use the following parameters modified to your needs

,@query=@Query
,@attach_query_result_as_file=1
,@query_attachment_filename=’QueryResults.csv’
,@query_result_separator=’,’ –enforce csv
,@query_result_no_padding=1 –trim
,@query_result_width=32767 –stop wordwrap

SOURCE: http://www.purplefrogsystems.com/blog/2014/04/excel-doesnt-open-csv-files-correctly-from-sp_send_dbmail/


Follow

Get every new post delivered to your Inbox.