SQL Server: Querying SQL Server FQDN
DECLARE @FQDN NVARCHAR(100)
EXEC master.dbo.xp_regread ‘HKEY_LOCAL_MACHINE’, ‘SYSTEM\CurrentControlSet\services\Tcpip\Parameters’, N’Domain’,@FQDN OUTPUT
SELECT Cast(SERVERPROPERTY(‘MachineName’) as nvarchar) + ‘.’ + @FQDN AS FQDN
SQL Server: EXCEPTION_ACCESS_VIOLATION when Updating Statistics
ISSUE:
Hello everyone, I got an incident when running update statistic on SCCM database.
the UpdateStat was generating lots of dump file with the error bellow:
***Stack Dump being sent to I:\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\SQLDump0048.txt
SqlDumpExceptionHandler: Process 55 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 02/29/16 14:38:56 spid 55
*
*
* Exception Address = 000007FEF15DB985 Module(sqllang+000000000068B985)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 0000000000000000
* Input Buffer 50 bytes –
* sp_updatestats
CAUSE:
The issue was caused because database is configured to automatically update statistics and one statistic got corrupted.
RESOLUTION:
– Run sp_updatestats again and check in which table the error is ocurring.
Updating [dbo].[PullDPResponse]
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
– Now we know statistics on [dbo].[PullDPResponse] is corrupted. Lets check stats individually to identify which one is corrupted. That table has two:
DBCC SHOW_STATISTICS(‘PullDPResponse’,’PullDPResponse_PK’); <– It works
DBCC SHOW_STATISTICS(‘PullDPResponse’,’_WA_Sys_08000002_59A78896′) <– Not working
– Lets drop corrupted stats:
DROP STATISTICS PullDPResponse._WA_Sys_08000002_59A78896;
– Now we are good need to update all stats to make sure everything is fine.
sp_updatestats
TNSPING Message 3511 not found; No message file for product=NETWORK, facility=TNSMessage 3512 not found;
I got this error when try to run TNSping after install Oracle client (x86 and x64) on Windows Server 2012 R2:
C:\Users\admlucas_almeida>tnsping
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 – Production on 13-OCT-2015 07:47:32
Copyright (c) 1997, 2010, Oracle. All rights reserved.Message 3511 not found;
No message file for product=NETWORK, facility=TNSMessage 3512 not found;
No message file for product=NETWORK, facility=TNSMessage 3513 not found;
No message file for product=NETWORK, facility=TNSMessage 3509 not found;
No message file for product=NETWORK, facility=TNS
It´s just a oracle client installation, ORACLE_HOME is set correctly.
RESOLUTION:
nlus.msb did exist under %ORACLE_HOME%\network\mesg\, but there were seven more msg files missing. Apparently this was due to an incomplete client installation, and I had copied these file across from another machine with the same version/configuration and got rid of the issue.
SQL Server: Script to verify Backup and Restore progress
SELECT r.session_id,
r.command,
CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],
CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r
WHERE command IN (‘RESTORE DATABASE’,’BACKUP DATABASE’)
SQL Server: Contagem de registro das tabelas
SELECT sc.name +’.’+ ta.name TableName, SUM(pa.rows) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
ORDER BY SUM(pa.rows) DESC
SQL Server: Changing job owner
— Query SQL jobs and owners
select s.name,l.name
from msdb..sysjobs s
left join master.sys.syslogins l on s.owner_sid = l.sid
go
— Change job owner
exec msdb..sp_update_job @job_name = ‘<job_name>’,
@owner_login_name = ‘sa’
go
— Change all jobs owner
DECLARE @name_holder VARCHAR(1000)
DECLARE My_Cursor CURSOR
FOR
SELECT [name] FROM msdb..sysjobs
OPEN My_Cursor
FETCH NEXT FROM My_Cursor INTO @name_holder
WHILE (@@FETCH_STATUS <> -1)
BEGIN
exec msdb..sp_update_job
@job_name = @name_holder,
@owner_login_name = ‘sa’ –Changes the owner of the jobs to sa
FETCH NEXT FROM My_Cursor INTO @name_holder
END
CLOSE My_Cursor
DEALLOCATE My_Cursor
List all database files and spaces
——————————Data file size—————————-
if exists (select * from tempdb.sys.all_objects where name like ‘%#dbsize%’)
drop table #dbsize
create table #dbsize
(Dbname sysname,dbstatus varchar(50),Recovery_Model varchar(40) default (‘NA’), file_Size_MB decimal(30,2)default (0),Space_Used_MB decimal(30,2)default (0),Free_Space_MB decimal(30,2) default (0))
go
insert into #dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB)
exec sp_msforeachdb
‘use [?];
select DB_NAME() AS DbName,
CONVERT(varchar(20),DatabasePropertyEx(”?”,”Status”)) ,
CONVERT(varchar(20),DatabasePropertyEx(”?”,”Recovery”)),
sum(size)/128.0 AS File_Size_MB,
sum(CAST(FILEPROPERTY(name, ”SpaceUsed”) AS INT))/128.0 as Space_Used_MB,
SUM( size)/128.0 – sum(CAST(FILEPROPERTY(name,”SpaceUsed”) AS INT))/128.0 AS Free_Space_MB
from sys.database_files where type=0 group by type’
go
——————-log size————————————–
if exists (select * from tempdb.sys.all_objects where name like ‘#logsize%’)
drop table #logsize
create table #logsize
(Dbname sysname, Log_File_Size_MB decimal(38,2)default (0),log_Space_Used_MB decimal(30,2)default (0),log_Free_Space_MB decimal(30,2)default (0))
go
insert into #logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB)
exec sp_msforeachdb
‘use [?];
select DB_NAME() AS DbName,
sum(size)/128.0 AS Log_File_Size_MB,
sum(CAST(FILEPROPERTY(name, ”SpaceUsed”) AS INT))/128.0 as log_Space_Used_MB,
SUM( size)/128.0 – sum(CAST(FILEPROPERTY(name,”SpaceUsed”) AS INT))/128.0 AS log_Free_Space_MB
from sys.database_files where type=1 group by type’
go
——————————–database free size
if exists (select * from tempdb.sys.all_objects where name like ‘%#dbfreesize%’)
drop table #dbfreesize
create table #dbfreesize
(name sysname,
database_size varchar(50),
Freespace varchar(50)default (0.00))
insert into #dbfreesize(name,database_size,Freespace)
exec sp_msforeachdb
‘use [?];SELECT database_name = db_name()
,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ”MB”)
,”unallocated space” = ltrim(str((
CASE
WHEN dbsize >= reservedpages
THEN (convert(DECIMAL(15, 2), dbsize) – convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576
ELSE 0
END
), 15, 2) + ” MB”)
FROM (
SELECT dbsize = sum(convert(BIGINT, CASE
WHEN type = 0
THEN size
ELSE 0
END))
,logsize = sum(convert(BIGINT, CASE
WHEN type <> 0
THEN size
ELSE 0
END))
FROM sys.database_files
) AS files
,(
SELECT reservedpages = sum(a.total_pages)
,usedpages = sum(a.used_pages)
,pages = sum(CASE
WHEN it.internal_type IN (
202
,204
,211
,212
,213
,214
,215
,216
)
THEN 0
WHEN a.type <> 1
THEN a.used_pages
WHEN p.index_id < 2
THEN a.data_pages
ELSE 0
END)
FROM sys.partitions p
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
LEFT JOIN sys.internal_tables it
ON p.object_id = it.object_id
) AS partitions’
———————————–
if exists (select * from tempdb.sys.all_objects where name like ‘%#alldbstate%’)
drop table #alldbstate
create table #alldbstate
(dbname sysname,
DBstatus varchar(55),
R_model Varchar(30))
–select * from sys.master_files
insert into #alldbstate (dbname,DBstatus,R_model)
select name,CONVERT(varchar(20),DATABASEPROPERTYEX(name,’status’)),recovery_model_desc from sys.databases
–select * from #dbsize
insert into #dbsize(Dbname,dbstatus,Recovery_Model)
select dbname,dbstatus,R_model from #alldbstate where DBstatus <> ‘online’
insert into #logsize(Dbname)
select dbname from #alldbstate where DBstatus <> ‘online’
insert into #dbfreesize(name)
select dbname from #alldbstate where DBstatus <> ‘online’
select @@SERVERNAME ServerName,
d.Dbname,d.dbstatus,d.Recovery_Model,
(file_size_mb + log_file_size_mb) as DBsize,
d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB,
convert(decimal(10,0), (d.Free_Space_MB / (file_size_mb + log_file_size_mb)) *100) as Percent_DB_Used,
l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,
convert(decimal(10,0), (log_Space_Used_MB / l.Log_File_Size_MB) * 100) as Percent_Log_Used,
fs.Freespace as DB_Freespace
from #dbsize d join #logsize l
on d.Dbname=l.Dbname join #dbfreesize fs
on d.Dbname=fs.name
order by Dbname
List all triggers on database
--List all triggers on database
SELECT
TAB.name as Table_Name
, TRIG.name as Trigger_Name
, TRIG.is_disabled --or objectproperty(object_id('TriggerName'), 'ExecIsTriggerDisabled')
FROM [sys].[triggers] as TRIG
inner join sys.tables as TAB
on TRIG.parent_id = TAB.object_id
SQL Server: List objects in a schema
— List all tables in a schema
SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.tables
where TABLE_SCHEMA LIKE ‘MY_SCHEMA%’
— List all stored procedures in a schema
SELECT s.name SchemaName, pr.name ProcedureName
FROM sys.procedures pr
INNER JOIN sys.schemas s ON pr.schema_id = s.schema_id
WHERE s.name LIKE ‘MY_SCHEMA%’
— List all views in a schema
SELECT s.name SchemaName, v.name AS view_name
FROM sys.views v
INNER JOIN sys.schemas s ON v.schema_id = s.schema_id
WHERE s.name LIKE ‘MY_SCHEMA%’
SQL Server: Verify shrink progress
If you are executing a shrink and wants to know the progress, use this select:
SELECT percent_complete, start_time, status, command, estimated_completion_time, cpu_time, total_elapsed_time FROM sys.dm_exec_requests WHERE command = 'DbccFilesCompact'