Category Archives: MSSQL

Microsoft SQL

Find Legacy SQL Joins

— will list both Views and Stored procedures in a particular database SELECT OBJECT_NAME(object_id),OBJECT_DEFINITION(object_id) FROM sys.objects WHERE OBJECT_DEFINITION(object_id) LIKE ‘%*=%’ or OBJECT_DEFINITION(object_id) LIKE ‘%=*%’ Advertisements

Posted in Administration | Leave a comment

Actual Database Size

The below script would provide the Actual DB file usage size rather than DB size (Used + Free) should work for SQL 2008 and upwards Borrowed the “dbo.sp_foreachdb” script from Aaron Bertrand (making-a-more-reliable-and-flexible-spmsforeachdb/) to list out all databases in a … Continue reading

Posted in Administration, MSSQL | Leave a comment

Coalesce Inside a Case

Got in to an instance at work where i had to modify a SP to get non-null values out of a case statement code block CASE WHEN condition_expression THEN  COALESCE(Column1, Column2) ELSE Column2 END AS New_Column

Posted in Development | Leave a comment

User and User Group Queries

I get user requests all the time for access rights and i don’t have read access to AD to check groups to see if the user is already a member or not. We can run the below scripts from SQL server Management … Continue reading

Posted in MSSQL, Permissions | Leave a comment

Consistency Errors due to MSG 8914 – PFS free space

Issue While running DBCC CHECKDB() job on SQL 2008 R2 SP1 with SQL 2005 compatibility Level databases, it failed with the below DBCC results for ‘MESTEMPLATEDOCUMENTS’. Msg 8914, Level 16, State 1, Line 1 Incorrect PFS free space information for … Continue reading

Posted in Database Corruption, MSSQL | Leave a comment

Modifying maxAllowedContentLength

Issue:  “ http://dev-perfdb2/olap/msmdpump.dll” is thrown by an application due to default configuration in the IIS sever which throws an error when the content length of the data (the data which is passed from an application to DB is passed through … Continue reading

Posted in IIS, MSSQL | Leave a comment

Providing Permissions for Non-Admin users to use Activity Monitor

Windows 2012 and SQL 2012 To grant permission for Non-Admin users to use Activity Monitor on an SQL 2012 server, follow the below 1) Add [Domain\Username] to [Computer Management] –> [Performance Monitor Users] 2) Run the below script on SQL Instance … Continue reading

Posted in MSSQL, Permissions | Leave a comment