보미네

select
   name
 , case when status&1 = 1 then 'autoclose(ALTER DATABASE)' else null end
 , case when status&4 = 4 then 'select into/bulkcopy(SET RECOVERY를 사용한 ALTER DATABASE)' else null end
 , case when status&8 = 8 then 'trunc. log on chkpt(SET RECOVERY를 사용한 ALTER DATABASE)' else null end
 , case when status&16 = 16 then 'torn page detection(ALTER DATABASE) ' else null end
 , case when status&32 = 32 then 'loading  ' else null end
 , case when status&64 = 64 then 'pre recovery' else null end
 , case when status&128 = 128 then 'recovering ' else null end
 , case when status&256 = 256 then 'not recovered ' else null end
 , case when status&512 = 512 then 'offline(ALTER DATABASE)' else null end
 , case when status&1024 = 1024 then 'read only(ALTER DATABASE)' else null end
 , case when status&2048 = 2048 then 'dbo use only(SET RESTRICTED_USER를 사용한 ALTER DATABASE)' else null end
 , case when status&4096 = 4096 then 'single user(ALTER DATABASE)' else null end
 , case when status&32768 = 32768 then 'emergency mode ' else null end
 , case when status&65536 = 65536 then 'CHECKSUM(ALTER DATABASE)' else null end
 , case when status&4194304 = 4194304 then 'autoshrink(ALTER DATABASE)' else null end
 , case when status&1073741824 = 1073741824 then 'cleanly shutdown' else null end

 , case when status2&16384 = 16384 then 'ANSI null default(ALTER DATABASE)' else null end
 , case when status2&65536 = 65536 then 'concat null yields null(ALTER DATABASE)' else null end
 , case when status2&131072 = 131072 then 'recursive triggers(ALTER DATABASE)' else null end
 , case when status2&1048576 = 1048576 then 'default to local cursor(ALTER DATABASE)' else null end
 , case when status2&8388608 = 8388608 then 'quoted identifier(ALTER DATABASE)' else null end
 , case when status2&33554432 = 33554432 then 'cursor close on commit(ALTER DATABASE)' else null end
 , case when status2&67108864 = 67108864 then 'ANSI nulls(ALTER DATABASE)' else null end
 , case when status2&268435456 = 268435456 then 'ANSI warnings(ALTER DATABASE)' else null end
 , case when status2&536870912 = 536870912 then 'full text enabled(sp_fulltext_database로 설정)' else null end
from master.dbo.sysdatabases

--ALTER DATABASE BAMMA
--SET PAGE_VERIFY CHECKSUM;
--GO

--ALTER DATABASE joymdb
--SET auto_shrink off;
--GO

--exec sp_fulltext_database 'disable'

dbcc sqlperf(logspace)

-- vlf 갯수

DECLARE @query varchar(1000),
 @dbname varchar(1000),
 @count int

SET NOCOUNT ON

DECLARE csr CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT name
FROM master.dbo.sysdatabases

--drop table ##loginfo
CREATE TABLE ##loginfo
(
 dbname varchar(100),
 num_of_rows int)

OPEN csr

FETCH NEXT FROM csr INTO @dbname

WHILE (@@fetch_status <> -1)
BEGIN

CREATE TABLE #log_info
(
recoveryUnitId int,
 fileid tinyint,
 file_size bigint,
 start_offset bigint,
 FSeqNo int,
[status] tinyint,
 parity tinyint,
 create_lsn numeric(25,0)
)

SET @query = 'DBCC loginfo (' + '''' + @dbname + ''') '

INSERT INTO #log_info
EXEC (@query)

SET @count = @@rowcount

DROP TABLE #log_info

INSERT ##loginfo
VALUES(@dbname, @count)

FETCH NEXT FROM csr INTO @dbname

END

CLOSE csr
DEALLOCATE csr

SELECT dbname,
 num_of_rows
FROM ##loginfo
WHERE num_of_rows >= 50 --My rule of thumb is 50 VLFs. Your mileage may vary.
ORDER BY dbname

DROP TABLE ##loginfo

-- 마지막 full backup
SELECT top 1 s.database_name,
m.physical_device_name,
cast(s.backup_size/1000000 as varchar(14))+' '+'MB' as bkSize,
CAST (DATEDIFF(second,s.backup_start_date , s.backup_finish_date)AS VARCHAR(4))+' '+'Seconds' TimeTaken,
s.backup_start_date,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m
ON s.media_set_id = m.media_set_id
WHERE s.database_name = 'joymdb'
and
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END ='full'
ORDER BY database_name, backup_finish_date desc

-- 마지막 로그백업
SELECT top 1 s.database_name,
m.physical_device_name,
cast(s.backup_size/1000000 as varchar(14))+' '+'MB' as bkSize,
CAST (DATEDIFF(second,s.backup_start_date , s.backup_finish_date)AS VARCHAR(4))+' '+'Seconds' TimeTaken,
s.backup_start_date,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m
ON s.media_set_id = m.media_set_id
WHERE s.database_name = 'joymdb'
and
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END ='Transaction Log'
ORDER BY database_name, backup_finish_date desc

-- 계정 없을때

net stop mssql$mynewins5
net start mssql$mynewins5 /m /f /T3608 /T7806
sqlcmd -S.\mynewins5 -A
USE [master]
GO
CREATE LOGIN [a] WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [a]
GO
exit
net stop mssql$mynewins5
net start mssql$mynewins5