DECLARE @Path nvarchar(500) = 'E:\Test' --Change the path DECLARE @FindFile TABLE (FileNames nvarchar(500) ,depth int ,isFile int) INSERT INTO @FindFile EXEC xp_DirTree @Path,1,1 SELECT FileNames from @FindFile where isFile=1
IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE name='TempIndexSpace') BEGIN DROP TABLE tempdb..TempIndexSpace END CREATE TABLE tempdb..TempIndexSpace (ObjectName nvarchar(100) ,IndexID int ,IndexName nvarchar(100) ,[IndexSize(KB)] int ,Comments nvarchar(max)) exec sp_msforeachtable 'INSERT INTO tempdb..TempIndexSpace (IndexID,IndexName,[IndexSize(KB)],Comments) EXEC sp_MSIndexSpace [?]; UPDATE tempdb..TempIndexSpace SET ObjectName=''?'' WHERE ObjectName IS NULL' -- This gives output per index SELECT * FROM tempdb..TempIndexSpace -- This gives output per table SELECT ObjectName,SUM([IndexSize(KB)]) AS [Index Size (KB)] FROM tempdb..TempIndexSpace GROUP BY ObjectName
DBCC checkprimaryfile ('C:\Users\SANDESH\Desktop\MSDBData.mdf', 0)
DBCC checkprimaryfile ('C:\Users\SANDESH\Desktop\MSDBData.mdf', 1)
DBCC checkprimaryfile ('C:\Users\SANDESH\Desktop\MSDBData.mdf', 2)
DBCC checkprimaryfile ('C:\Users\SANDESH\Desktop\MSDBData.mdf', 3)
Have you ever wondered or got curious to know what will
happen or what are the activities carried out when an SQL Server instance get a restart request?CREATE TABLE #ServerDetails(ID int, Name sysname, Internal_Value int, Value nvarchar(512))
INSERT #ServerDetails EXEC master.dbo.xp_msver
DECLARE @InstanceName nvarchar(50)
DECLARE @value VARCHAR(100)
DECLARE @RegKey_InstanceName nvarchar(500)
DECLARE @RegKey nvarchar(500)
DECLARE @AuditLevel int
DECLARE @DataDirectory nvarchar(500)
DECLARE @LogDirectory nvarchar(500)
DECLARE @BackupDirectory nvarchar(500)
SET @InstanceName=CONVERT(nVARCHAR,isnull(SERVERPROPERTY('INSTANCENAME'),
'MSSQLSERVER'))
if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8
BEGIN
SET @RegKey_InstanceName='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'
EXECUTE xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = @RegKey_InstanceName,
@value_name = @InstanceName,
@value = @value OUTPUT
SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\'
EXEC master..xp_regread
@rootkey='HKEY_LOCAL_MACHINE',
@key=@RegKey,
@value_name='AuditLevel',
@value=@AuditLevel OUTPUT
EXEC master..xp_regread
@rootkey='HKEY_LOCAL_MACHINE',
@key=@RegKey,
@value_name='DefaultData',
@value=@DataDirectory OUTPUT
EXEC master..xp_regread
@rootkey='HKEY_LOCAL_MACHINE',
@key=@RegKey,
@value_name='DefaultLog',
@value=@LogDirectory OUTPUT
EXEC master..xp_regread
@rootkey='HKEY_LOCAL_MACHINE',
@key=@RegKey,
@value_name='BackupDirectory',
@value=@BackupDirectory OUTPUT
END
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') [Machine Name]
,SERVERPROPERTY('ServerName') AS [SQL Server Name]
,SERVERPROPERTY('InstanceName') AS [Instance Name]
,SERVERPROPERTY('Collation') AS [Server Collation]
,'Microsoft SQL Server ' + CAST(SERVERPROPERTY('Edition') AS varchar(250)) AS Edition
,SERVERPROPERTY('ProductLevel') AS [Product Level]
,(SELECT Value FROM #ServerDetails WHERE Name = N'Language') AS [Language]
,(SELECT Value FROM #ServerDetails WHERE Name = N'Platform') AS [Platform]
,(SELECT 'Microsoft Windows NT ' + Value from #ServerDetails where Name = N'WindowsVersion') AS [Operating System]
,(SELECT Internal_Value FROM #ServerDetails WHERE Name = N'ProcessorCount') AS [Processors]
,(SELECT CAST(Internal_Value AS varchar)+ ' (MB)' FROM #ServerDetails WHERE Name = N'PhysicalMemory') AS Memory
, CASE WHEN SERVERPROPERTY('IsClustered') = 1 THEN 'True' ELSE 'False' END AS IsClustered
,(SELECT value from sys.configurations where name='min server memory (MB)') AS 'Min Server Memory (MB)'
,(SELECT value from sys.configurations where name='max server memory (MB)') AS 'Max Server Memory (MB)'
,(SELECT CASE WHEN value=0 THEN 'True' ELSE 'False' END from sys.configurations where name='affinity mask') AS 'Automatically set processor affinity mask for all processor'
,(SELECT CASE WHEN value=0 THEN 'True' ELSE 'False' END from sys.configurations where name='affinity I/O mask') AS 'Automatically set I/O affinity mask for all processor'
,CASE WHEN SERVERPROPERTY('IsIntegratedSecurityOnly')= 1 THEN 'Windows Authentication Mode'
WHEN SERVERPROPERTY('IsIntegratedSecurityOnly')= 0 THEN 'SQL Server and Windows Authentication Mode' END AS [Server Authentication]
,CASE WHEN @AuditLevel = 0 THEN 'None'
WHEN @AuditLevel = 1 THEN 'Successful Logins Only'
WHEN @AuditLevel = 2 THEN 'Failed Logins Only'
WHEN @AuditLevel = 3 THEN 'Both Failed and Successful Logins'
END AS [Audit Level]
,(select CASE WHEN value = 0 THEN 'False' WHEN value = 1 THEN 'True' END from sys.configurations where name='remote access') AS 'Allow remote connections to this Server'
,(select CASE WHEN value = 0 THEN 'unlimited' ELSE value END from sys.configurations where name='user connections') AS 'Max number of concurrent Connections'
,(select CASE WHEN value = 0 THEN 'No Timeout' ELSE value END from sys.configurations where name='remote query timeout (s)') AS 'Query Timeout (s)'
,(select CASE WHEN value = 0 THEN 'False' WHEN value = 1 THEN 'True' END from sys.configurations where name='remote access') AS 'Allow Remote Connections to this server'
,@DataDirectory AS 'Default Data Directory'
,@LogDirectory AS 'Default Log Directory'
,@BackupDirectory AS 'Default Backup Directory'
,(SELECT value from sys.configurations WHERE name='max degree of parallelism') AS 'Max Degree of Parallelism'
,(SELECT value from sys.configurations WHERE name='remote login timeout (s)') AS 'Remote Login Timeout (s)'
,(SELECT CASE WHEN value = 0 THEN 'False' WHEN value = 1 THEN 'True' END from sys.configurations WHERE name='scan for startup procs') AS 'Scan for Startup Procs'
DROP TABLE #ServerDetails
| Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 598, state 1, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion. |
| DECLARE @GetInstances TABLE ( Value nvarchar(100), InstanceNames nvarchar(100), Data nvarchar(100)) Insert into @GetInstances EXECUTE xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = 'SOFTWARE\Microsoft\Microsoft SQL Server', @value_name = 'InstalledInstances' Select InstanceNames from @GetInstances |
| Create Table #GetInstances ( Value nvarchar(100), InstanceNames nvarchar(100), Data nvarchar(100)) Insert into #GetInstances EXECUTE xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = 'SOFTWARE\Microsoft\Microsoft SQL Server', @value_name = 'InstalledInstances' Select InstanceNames from #GetInstances drop table #GetInstances |
| CREATE TABLE #DBInfo ( Id INT IDENTITY(1,1), ParentObject VARCHAR(255), [Object] VARCHAR(255), Field VARCHAR(255), [Value] VARCHAR(255) ) CREATE TABLE #Value( DatabaseName VARCHAR(255), LastDBCCCHeckDB_RunDate VARCHAR(255) ) EXECUTE SP_MSFOREACHDB'INSERT INTO #DBInfo Execute (''DBCC DBINFO ( ''''?'''') WITH TABLERESULTS''); INSERT INTO #Value (DatabaseName) SELECT [Value] FROM #DBInfo WHERE Field IN (''dbi_dbname''); UPDATE #Value SET LastDBCCCHeckDB_RunDate=(SELECT TOP 1 [Value] FROM #DBInfo WHERE Field IN (''dbi_dbccLastKnownGood'')) where LastDBCCCHeckDB_RunDate is NULL; TRUNCATE TABLE #DBInfo'; SELECT * FROM #Value DROP TABLE #DBInfo DROP TABLE #Value |
Query to find publisher_database_id using Transaction Sequence Number and Command ID |
select * from msrepl_commands where xact_seqno = 0x000BF8FB0003411E000400000000 and command_id=6 |
Query to find the command which is causing error |
exec sp_browsereplcmds @xact_seqno_start = '0x000BF8FB0003411E000400000000', @xact_seqno_end = '0x000BF8FB0003411E000400000000', @Command_id=6, @publisher_database_id=60 |
xp_regdeletevalue
This is an extended stored procedure that will delete a particular value for a key in the registry.
EXEC xp_regdeletevalue @rootkey,@key,@value_name
Example:-
EXEC master..xp_regdeletevalue @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Test', @value_name='TestValue'