Trace Database Activity before dropping

In our company we have decided to drop few databases , but we are not sure which application is being used ? which user is connecting ot working on it ?
So I have followed below steps to decide on dropping Database.

1.Running SQL Server Profiler Trace
Choose,Standard Default Trace:and ADD
1.Database name
2.HOSTNAME
3.NT DOMAIN NAME
4.Session Login name
5.Server Name

 Also,
Choose database name Like:
% ABC%
%XYZ% and EXCLUDE Column which do not match

run the trace for few hours First and  monitor the size of the  files and then run for 24-48 hours.


If no connections found ?
2.OFFLINE the databases  for a week
3.Backup the database and keep in separate folder(DO NOT DELETE)
4.DROP databases after notifying everyone....

Replicated transactions are waiting for next Log backup or for mirroring partner to catch up-Issue in SQL Server Replication

Hi, I have set up Always on Availability Groups on top of Windows Server Failover Cluster(WSFC) with two sql server stand alone installations

Server1--Primary Replica

Server2--Secondary Replica

--Both above configured in Always on Availability Groups( listener name=AGListner)

Server 3-Remote Distributor Server where all sql agent jobs (Log,snapshot,dist jobs)...

Server 4--Subscriber server

I have setup Replication on Server 1 which was primary at the time of configuring replication to server 4(subscriber) 

worked fine, replicated data, when failover happened  Server 2 took over as primary and still replication worked fine...

my Issues are below and i am looking for some assistance 

1.When i shout down Server 1(for testing), Server 2 took over the role as Primary and served the applications, but when i insert data at server 2( which is now primary), server 4 Subscriber did not get the data...once i turn on Server1 , the replication is replicated and subscriber is getting data ...does 2 servers(AG Servers) should be ruining in order to replication work?

2.and when i see the Replication monitor I see the All subscriptions are running, Agents are completed and running (ALL GREEN), also jobs on distributor are still running but server 4 Subscriber did not get the data..

his helped me ..this is a life saviour

DBCC Traceon (1448,-1)
--no sql server restart needed. 

--Enables the replication log reader to move forward even if the async secondaries(AG, MIrror databases) have not acknowledged the reception of a change. 
--Even with this trace flag enabled the log reader always waits for the sync secondaries. 
--The log reader will not go beyond the min ack of the sync secondaries. 
--This trace flag applies to the instance of SQL Server, not just an availability group, an availability database, 
--or a log reader instance. Takes effect immediately without a restart. This trace flag can be activated ahead of
-- time or when an async secondary fails.

SQL Server 2016 Multiple TempDB Database Files

Multiple TempDB Database Files

It has been a best practice for a while to have more than one tempdb data file if you are running on a multi-core machine.  In the past, up through SQL Server 2014, you always had to manually add the additional tempdb data files after you installed SQL Server. With SQL Server 2016 you can now configure the number of tempdb files you need while you are installing SQL Server.  Having this new feature means you will no longer need to manually add additional tempdb files after installing SQL Server.  

Check tempdb Performance

 SELECT files.physical_name, files.name,
  stats.num_of_writes, (1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write_stall_ms,
  stats.num_of_reads, (1.0 * stats.io_stall_read_ms / stats.num_of_reads) AS avg_read_stall_ms
FROM sys.dm_io_virtual_file_stats(2, NULL) as stats
INNER JOIN master.sys.master_files AS files
  ON stats.database_id = files.database_id
  AND stats.file_id = files.file_id
WHERE files.type_desc = 'ROWS'


--Are writes being evenly distributed between data files?
--Are writes finishing in 20ms or less

--IF NO then we have a problem
--8 database files are good ..16 data files ? may be, but try with 8 data files and then if still bad performance try adding 16


declare @milli as int
set @milli = 30251

select @milli/3600000 as hours,
(@milli - ((@milli/3600000)*3600000))/60000 as minutes,
(@milli - (((@milli)/60000)*60000)) /1000 as seconds,
@milli-(((@milli)/1000)*1000) as milliseconds

Convert Milli Seconds to seconds and minutes

declare @milli as int
set @milli = 30251

select @milli/3600000 as hours,
(@milli - ((@milli/3600000)*3600000))/60000 as minutes,
(@milli - (((@milli)/60000)*60000)) /1000 as seconds,
@milli-(((@milli)/1000)*1000) as milliseconds

Easy way to find who dropped table

create table  test1234( id int)
go
insert into test1234 Values(1)

drop table test1234
go
 SELECT
Operation,
[Transaction Id],
[Transaction SID],
[Transaction Name],
 [Begin Time],
   [SPID],
   Description
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'
GO
--get [Transaction Id] and you can nopw find the loginname
SELECT SUSER_SNAME(0xCCE723D8CEDF0244B5963964EC5180ED)

Easy way to find who deleted rows in a table

create table  test1234( id int)
go
insert into test1234 Values(1)
go
delete from test1234

--find deleted rows
SELECT
    [Transaction ID],
    Operation,
    Context,
    AllocUnitName
   
FROM
    fn_dblog(NULL, NULL)
WHERE
    Operation = 'LOP_DELETE_ROWS'
--We found the transaction ID from the above command, now get the transaction SID of the user who has deleted the data check table name also
SELECT
    Operation,
    [Transaction ID],
    [Begin Time],
    [Transaction Name],
    [Transaction SID]
FROM
    fn_dblog(NULL, NULL)
WHERE
    [Transaction ID] = '0000:0000076b'
AND
    [Operation] = 'LOP_BEGIN_XACT'


---get login name from  [Transaction SID]
SELECT SUSER_SNAME(0xCCE723D8CEDF0244B5963964EC5180ED)

Notify when SQL SERVER and Agent Restarts (works for both)

-- Alert on SQL Server Agent and/or SQL Server service restarts
-- Replace your recipients in line # 14

-- declare the required variables
DECLARE @Message VARCHAR(MAX)
, @Subject VARCHAR(123)
, @Recipients VARCHAR(123)
, @IsDBMailEnabled BIT
, @MailProfile VARCHAR(123)
, @IsClustered VARCHAR(1) -- this is because SERVERPROPERTY('IsClustered') is a sql_variant data type
, @CurrentNodeName VARCHAR(123)
, @InstanceName VARCHAR(123)
, @RestartTime VARCHAR(123)

set @Recipients = 'naresh.koudagani@xyz.com' -- specify the recipients here, separate with ;

SELECT @InstanceName = CONVERT(VARCHAR, SERVERPROPERTY('ServerName'))
, @IsClustered = CONVERT (VARCHAR, SERVERPROPERTY('IsClustered'))
, @CurrentNodeName = CONVERT (VARCHAR, SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))

-- SQL Server service has been restarted
IF (
SELECT DATEDIFF(MI, CREATE_DATE,GETDATE() )
FROM SYS.DATABASES
WHERE NAME = 'TEMPDB'
) <= 3 -- SQL Server service restarted in the past 3 minutes
BEGIN
SELECT @RestartTime = CONVERT(VARCHAR, create_date, 13)
FROM SYS.databases
WHERE NAME = 'TEMPDB'

SET @Message = @InstanceName + ' SQL Server Agent service and SQL Server service have been restarted at: ' +@RestartTime
SET @Subject = @InstanceName + ' SQL Server Agent service and SQL Server service have been restarted!'


IF @IsClustered = '1'
BEGIN
SET @Message = @Message + ' and the current active node is: ' +@CurrentNodeName+ '.'
SET @Subject = @Subject + ' The current active node is: ' +@CurrentNodeName+ '.'
END
END
ELSE
BEGIN
SET @Message = @InstanceName + ' SQL Server Agent service has been restarted!'
SET @Subject = @InstanceName + ' SQL Server Agent service restarted!'
END

SET @Message = @Message + CHAR(10)
SET @Message = @Message + 'If this is not a planned maintenace. Please verify the status of the databases before restarting any application services and review the log files to find the possible root causes!'

-- Find the database mail profile name
create table #dbmail_profile
(
profileid int null,
profilename varchar(125) null,
accountid int null,
accountname varchar(125) null,
sequencenumber int null
)
insert #dbmail_profile
EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

select @MailProfile = profilename
from #dbmail_profile
where sequencenumber = 1

drop table #dbmail_profile

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMAIL',
@recipients = @Recipients,
@subject = @Subject,
@body_format = 'TEXT',
@body = @Message;

Multiple tempdb files per logicalCPUs

 When you create multiple data files they will all be in the primary file group and SQL Server uses a proportional fill algorithm to determine which file to use for each request to create an object. If all the files are exactly the same size, then SQL Server uses the files in a “round robin” fashion, spreading the load equally across the files. This is, of course, exactly what you want.

Microsoft recommends up to a 1:1 mapping between the number of files and logical CPU's because during testing of massive workloads they’ve seen performance benefits, even with hundreds of data files.

select DB_NAME(mf.database_id) database_name

, mf.name logical_name, mf.file_id

, CONVERT (DECIMAL (20,2)

, (CONVERT(DECIMAL, size)/128)) as [file_size_MB]

, CASE mf.is_percent_growth

WHEN 1 THEN 'Yes'

ELSE 'No'

END AS [is_percent_growth]

, CASE mf.is_percent_growth

WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'

WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'

END AS [growth_in_increment_of]

, CASE mf.is_percent_growth

WHEN 1 THEN CONVERT(DECIMAL(20,2)

,(((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)

WHEN 0 THEN CONVERT(DECIMAL(20,2)

, (CONVERT(DECIMAL, growth)/128))

END AS [next_auto_growth_size_MB]

, physical_name from sys.master_files mf

where database_id =2 and type_desc= 'rows'

go

SELECT cpu_count AS logicalCPUs FROM sys.dm_os_sys_info

go

--------------------------------Q Drive is SSD-----------------------------------------------------------------------------------------

-----Move tempdb Files, then restart 

ALTER DATABASE TempDB MODIFY FILE

(NAME = tempdev, FILENAME = 'Q:\MSSQL\Tempdb\tempdb.mdf',SIZE =500MB , FILEGROWTH = 500MB)

GO

ALTER DATABASE TempDB MODIFY FILE

(NAME = templog, FILENAME = 'Q:\MSSQL\Tempdb\templog.ldf',SIZE =500MB , FILEGROWTH = 500MB)

GO

---ADD 15 More Tempdb files 

ALTER DATABASE tempdb ADD FILE 

( NAME = N'tempdev2',FILENAME = N'Q:\MSSQL\Tempdb\tempdev2.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO

 

ALTER DATABASE tempdb ADD FILE 

( NAME = N'tempdev3',FILENAME = N'Q:\MSSQL\Tempdb\tempdev3.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO

 

 ALTER DATABASE tempdb ADD FILE 

 ( NAME = N'tempdev4',FILENAME = N'Q:\MSSQL\Tempdb\tempdev4.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO

 

ALTER DATABASE tempdb 

ADD FILE ( NAME = N'tempdev5',FILENAME = N'Q:\MSSQL\Tempdb\tempdev5.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO

 

 ALTER DATABASE tempdb 

 ADD FILE ( NAME = N'tempdev6',FILENAME = N'Q:\MSSQL\Tempdb\tempdev6.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO

 

ALTER DATABASE tempdb ADD FILE 

( NAME = N'tempdev7',FILENAME = N'Q:\MSSQL\Tempdb\tempdev7.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO

 

 ALTER DATABASE tempdb 

 ADD FILE ( NAME = N'tempdev8',FILENAME = N'Q:\MSSQL\Tempdb\tempdev8.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO

 

ALTER DATABASE tempdb 

ADD FILE ( NAME = N'tempdev9',FILENAME = N'Q:\MSSQL\Tempdb\tempdev9.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO

 

 ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev10',

FILENAME = N'Q:\MSSQL\Tempdb\tempdev10.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO

 

ALTER DATABASE tempdb 

ADD FILE ( NAME = N'tempdev11',FILENAME = N'Q:\MSSQL\Tempdb\tempdev11.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO

 

 ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev12',

FILENAME = N'Q:\MSSQL\Tempdb\tempdev12.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO

 

ALTER DATABASE tempdb 

ADD FILE ( NAME = N'tempdev13',FILENAME = N'Q:\MSSQL\Tempdb\tempdev13.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO

 

 ALTER DATABASE tempdb 

 ADD FILE ( NAME = N'tempdev14',FILENAME = N'Q:\MSSQL\Tempdb\tempdev14.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO

 

ALTER DATABASE tempdb 

ADD FILE ( NAME = N'tempdev15',FILENAME = N'Q:\MSSQL\Tempdb\tempdev15.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO