Using .NET CLR Based Methods In SQL Server

— Please change the database name according to environment
ALTER DATABASE <DBNAME> SET TRUSTWORTHY ON;
GO
declare @user varchar(50)
SELECT @user = quotename(SL.Name)
FROM master..sysdatabases SD inner join master..syslogins SL
on SD.SID = SL.SID
Where SD.Name = DB_NAME()
exec(‘exec sp_changedbowner ‘ + @user)
GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = ‘SP_Name’)
DROP Procedure SP_Name;
go

IF EXISTS (SELECT name FROM sys.assemblies WHERE name = ‘CLRInterface’)
DROP ASSEMBLY CLRInterface;
go

— Please change the path according environment
CREATE ASSEMBLY CLRInterface FROM ‘C:\AssemblyName.dll’
WITH PERMISSION_SET = UNSAFE;
GO

CREATE Procedure SP_Name(@ConfigName nVarchar(100))
AS EXTERNAL NAME InterfaceName.ClassName.[MethodName];
go

EXEC sp_configure ‘show advanced options’ , ‘1’;
reconfigure;

EXEC sp_configure ‘clr enabled’ , ‘1’ ;
reconfigure;

EXEC sp_configure ‘show advanced options’ , ‘0’;
reconfigure;
GO

Find a Table/Table Column usage across DB – SQL Server

— Following query is helpful to find all db objects that used to set value for the given columnname of a table …

select distinct name,o.type
from syscomments c
join sysobjects o on c.id = o.id
where TEXT like ‘%TableName%’ and TEXT like ‘%Set%ColumnName%’
order by name

— Finding table dependencies using SP

sp_depends ‘<Table Name>’

SQL Server – SA account locked out – Links

http://v-consult.be/2011/05/26/recover-sa-password-microsoft-sql-server-2008-r2/

http://community.spiceworks.com/topic/127162-sql-2008-sa-account-locked-out-no-other-known-credentials

http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/e3783055-09c3-458d-8cf0-602ad16cac6f/

http://msdn.microsoft.com/en-in/library/dd207004(v=sql.100).aspx

Compare Data In 2 Similar Tables – SQL Server

— Below query gives all difference records between old & new tables one below other
(SELECT * FROM Table_Old
UNION
SELECT * FROM Table_New)
EXCEPT
(SELECT * FROM Table_Old
INTERSECT
SELECT * FROM Table_New)

— Below query gives all difference records from old table first and then all difference records from new table
select * from
(
select * from Table_Old
except
select * from Table_New
) as T
union all
select * from
(
select * from Table_New
except
select * from Table_Old
) as T

Query to see Jobs that run in SQL Server

1)

select *
from (
select
name,(CONVERT(DATETIME, RTRIM(run_date)) + (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4) run_date,run_duration
,(case run_status when 1 then ‘Success’
when 0 then ‘Failed’
when 2 then ‘Retry’
when 3 then ‘Canceled’
end) run_status
,message
from msdb..sysjobhistory h
inner join msdb..sysjobs j on h.job_id = j.job_id
where step_id=0) t
where
run_date >= CONVERT(VARCHAR(10),GETDATE()-1,111)
order by run_date desc

——————

2) sp_who3

3)

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
cast(req.total_elapsed_time / (1000 * 60) as varchar(10)) + ‘ minutes’,
cast(req.total_elapsed_time / (1000 * 60 * 60) as varchar(10)) + ‘+ hours’
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

4)

select distinct
job_name,
run_datetime_original,
run_datetime,
SUBSTRING(run_duration, 1, 2) + ‘:’ + SUBSTRING(run_duration, 3, 2) + ‘:’ + SUBSTRING(run_duration, 5, 2) AS run_duration
from
(
select
job_name,
run_datetime run_datetime_original,
DATEADD(hh, -7, run_datetime) as run_datetime,
run_duration = RIGHT(‘000000’ + CONVERT(varchar(6), run_duration), 6)
from
(
select
j.name as job_name,
run_datetime = max(CONVERT(DATETIME, RTRIM(run_date)) + (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4)
from msdb..sysjobhistory h
inner join msdb..sysjobs j on h.job_id = j.job_id
group by j.name
) t
inner join msdb..sysjobs j on t.job_name = j.name
inner join msdb..sysjobhistory h on j.job_id = h.job_id and t.run_datetime = CONVERT(DATETIME, RTRIM(h.run_date)) + (h.run_time * 9 + h.run_time % 10000 * 6 + h.run_time % 100 * 10) / 216e4
) dt
order by run_datetime desc

Check, Disable, Enable Foreign Key(s) – SQL Server

— Query to see all available foreign keys in the DB
SELECT
f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName,
f.is_disabled
FROM
sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
where
f.name = ‘<FK Name>’

— Disable foreign key
ALTER TABLE <Table Name> NOCHECK CONSTRAINT <FK Name>
— Enable foreign key
ALTER TABLE <Table Name> CHECK CONSTRAINT <FK Name>

More references …

http://blog.sqlauthority.com/2007/09/04/sql-server-2005-find-tables-with-foreign-key-constraint-in-database/

http://www.mssqltips.com/sqlservertip/1376/disable-enable-drop-and-recreate-sql-server-foreign-keys/

Restore SQL Server DB using RESTORE FILELISTONLY sql command

— See the logical db files info for the backup file
RESTORE FILELISTONLY FROM DISK = N’F:\DBBackup\DB_local.bak’ With file = 1

— Restore DB to different hard drive location (existing logical data/log name need to be mapped to new mdf/ldf file names in the new location)
RESTORE DATABASE MYDB1 FROM DISK = N’F:\DBBackup\DB_local.bak’
WITH MOVE N’MYDB_Data’ TO N’E:\SQL\Data\DB.mdf’,
MOVE N’MYDB_Log’ TO N’E:\SQL\Log\DB_log.ldf’,
MOVE N’MYDB_Log1′ TO N’E:\SQL\Log\DB_Log1.ldf’
GO

 

Check the DB Restore Status Using SQL

Following sql is helpful to see the DB restore status from time to time. Especially DB’s that take longer time to restore and when sql server management studio is not showing proper status then this query is very handy to check the status.

SELECT
percent_complete AS [PercentComplete]
,estimated_completion_time/1000.0/60.0 AS [RemainingMinutes]
,total_elapsed_time/1000.0/60.0 AS [ElapsedMinutes]
,(estimated_completion_time+total_elapsed_time)/1000.0/60.0 AS [TotalMinutes]
,DATEADD(MILLISECOND, estimated_completion_time, GETDATE()) AS [EstimatedTimeOfCompletion]
,st.TEXT AS [CommandSQL]
FROM sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) st
WHERE command LIKE ‘%RESTORE DATABASE%’

Shrink the SQL Server DB Log File

— Check to see log size and log space used
DBCC sqlperf(logspace)

— Check to see if there are any open transactions
DBCC OPENTRAN

— Shrink the log

–The following example shrinks the DB log file to 1 MB.
–To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.

— Ex: AdventureWorks DB
— Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE;
GO
— Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks_Log, 1);
GO
— Reset the database recovery model.
ALTER DATABASE AdventureWorks SET RECOVERY FULL;
GO

— Following command will help to see the progress of the shrink operation
SELECT percent_complete, start_time, status, command, estimated_completion_time, cpu_time, total_elapsed_time
FROM sys.dm_exec_requests

Reference:

http://stackoverflow.com/questions/646845/sql-server-2008-log-will-not-truncate-driving-me-crazy

http://technet.microsoft.com/en-us/library/ms190757.aspx

http://www.calazan.com/how-to-check-the-progress-of-the-shrink-database-task-in-sql-server-2005/

http://blog.sqlauthority.com/2008/07/25/sql-server-dbcc-shrinkfile-takes-long-time-to-run/

ERROR: The number of requests for XXXServer\XXXUser has exceeded the maximum number allowed for a single user

The reason for this error is the default value  for MaxActiveReqForOneUser key in RSREPORTSERVER.config file is 20 which need to be changed  to 0 (unlimited access) or increased to some higher value. After changing this setting IIS need to be reset in the reporting server machine.

Reportserver.config file exist in the following path (path may be little different based on the sql server reporting server version that is installed).

C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer

Reference:

http://www.sqlservercentral.com/Forums/Topic410741-162-1.aspx#bm507713

http://blogs.msdn.com/b/selvar/archive/2007/12/13/error-the-number-of-requests-for-xxxserver-xxxuser-has-exceeded-the-maximum-number-allowed-for-a-single-user.aspx