Cache files are generated when Data collection is enabled and Collection set is running. You can delete the files if not required after disabling the Data Collector. USE msdb; GO EXEC dbo.sp_syscollector_disable_collector; To enabled Data Collector USE msdb; GO EXEC dbo.sp_syscollector_enable_collector ;    

DISABLE TRIGGER [Trigger Name] ON ALL SERVER GO DROP TRIGGER [Trigger Name] on all SERVER  

LOGINPROPERTY function gives you the stored has password. SELECT LOGINPROPERTY(‘LoginName’,’PASSWORDHASH’); Output 0x020056986E23A5C403130F4B2DAD7ADBC835B9A662E70AE1937A7B7097DE2B36C58C3FDCB8912CF28DERP0A348918F559D53BF8868BA42D7FE1E400AE636862F939BC1DACFAEE3DB To use this hashed password , you have to use HASHED keyword. CREATE LOGIN LoginName WITH PASSWORD=0x020056986E23A5C403130F4B2DAD7ADBC835B9A662E70AE1937A7B7097DE2B36C58C3FDCB8912CF28DERP0A348918F559D53BF8868BA42D7FE1E400AE636862F939BC1DACFAEE3DB HASHED;  

    DBCC FREEPROCCACHE (plan_handle)   DBCC FREESYSTEMCACHE (‘ALL’) WITH MARK_IN_USE_FOR_REMOVAL;  

Get List of assembly and there paths SELECT assembly = saa.name, path = af.name FROM sys.assemblies AS sa INNER JOIN sys.assembly_files AS af ON sa.assembly_id = af.assembly_id WHERE sa.is_user_defined = 1;

The Script scripts all the permission granted to each login on Server and Database Level. DECLARE @Start int=1 DECLARE @End int DECLARE @DatabaseName varchar(100) DECLARE @cmd nvarchar(4000) DECLARE @Permission Table (ID int Identity,Script varchar(max)) DECLARE @DbList Table (id int identity,DBName varchar(100)) INSERT INTO @DbList(DBName) SELECT name FROM sys.databases where name not in (‘master’,’model’,’msdb’,’tempdb’) and state=0 […]

How to copy backup files between to different domains having trust relationship? use master go exec xp_cmdshell ‘net use Z: \sourceserverH$Backups password /user:domainusername’ go exec usp_copybackupfiles @searchdirectory=’Z:’, @filefilter=’.bak’, @database=’AdventureWorks2014′, @Destination=’H:Backupscopyfiles’ go Exec master.dbo.xp_cmdshell ‘net use Z: /delete’ go  

Handy Script to Copy the latest backup file between locations /* Written by : Mohammad Sufian Location : Singapore Copywrite : None. Suggestion/Feedback : gosgenq@gmail.com Supported versions: SQL Server 2008 R2 to SQL Server 2014 */ CREATE PROCEDURE dbo.usp_copybackupfiles @searchdirectory nvarchar(4000), @filefilter varchar(7), @database varchar(128), @Destination nvarchar(4000) AS BEGIN SET NOCOUNT ON DECLARE @command varchar(8000) […]

Configure Map Network Drive using T-SQL GO xp_cmdshell “net use Z: \\ServerName\drive$\FolderName <password> /user:domainusername GO Disconnect Mapped Network drives GO Exec master.dbo.xp_cmdshell ‘net use Z: /delete’ GO

CREATE TABLE #_txtrow (_txtrow varchar(5000)); BULK INSERT #_txtrow From ‘H:\<folder name>\filename.extension’ WITH(DATAFILETYPE = ‘char’,ROWTERMINATOR = ‘n’); SELECT * FROM #_txtrow DROP TABLE #_txtrow

  SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY(‘productlevel’), SERVERPROPERTY(‘edition’)  

Copy all files from the Source Folder PS C:> robocopy Source Destination ——————————————————————————- ROBOCOPY     ::     Robust File Copy for Windows ——————————————————————————- Started : Tuesday, June 9, 2015 9:08:16 AM Source : \ServerNamez$BackupFULL Dest : G:BACKUPSVOL01Restore Files : *.* Options : *.* /DCOPY:DA /COPY:DAT /R:1000000 /W:30 Copy all files from the Source Folder excluding files PS […]

How can I find which object belongs to which filegroup. Is there any way to know this?   SELECT quotename(schema_name(o.schema_id)) +’.’+ quotename(o.[name]) ObjectName, o.[type], i.[name], i.[index_id], f.[name] FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id] WHERE i.data_space_id = f.data_space_id AND o.type = ‘U’ –and […]

Table on Filegroup (s) SELECT quotename(schema_name(o.schema_id)) +’.’+ quotename(o.[name]) ObjectName, o.[type], i.[name], i.[index_id], f.[name] FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id] WHERE i.data_space_id = f.data_space_id AND o.type = ‘U’ –and f.name=’DataFileGroup’