SQL DBA SCRIPTS
Please find the extractor.exe file
on the system and then go to the path using CMD, once you have reached to the
location, use the below command.
Extractor.exe -F Sourcepath\filename.BAK -E Destinationpath\filename_native.bak
If you don't have extractor.exe then copy the same from any server to the destination server and then try to extract it.
Extractor.exe -F Sourcepath\filename.BAK -E Destinationpath\filename_native.bak
If you don't have extractor.exe then copy the same from any server to the destination server and then try to extract it.
Below is TSQL script to restore
database in MSSQL using Litespeed backup file.
exec master.dbo.xp_restore_database @database = N'databasename' ,
@filename = N'Full path of the backup file',
@filenumber = 1,
@with = N'STATS = 10',
@with = N'MOVE N''LogicalFileNameOf MDF file'' TO N''Destinationpath\Logicalfilename.mdf''',
@with = N'MOVE N''LogicalFileNameOf LDF file'' TO N''Destinationpath\Logicalfilename.ldf''',
@affinity = 0,
@logging = 0
GO
Example :-
exec master.dbo.xp_restore_database @database = N'Test' ,
@filename = N'Q:\Backup\Test_20130308.bak',
@filenumber = 1,
@with = N'STATS = 10',
@with = N'MOVE N''Test'' TO N''M:\data\Test.mdf''',
@with = N'MOVE N''Test_log'' TO N''H:\logs\Test_log.ldf''',
@affinity = 0,
@logging = 0
GO
exec master.dbo.xp_restore_database @database = N'databasename' ,
@filename = N'Full path of the backup file',
@filenumber = 1,
@with = N'STATS = 10',
@with = N'MOVE N''LogicalFileNameOf MDF file'' TO N''Destinationpath\Logicalfilename.mdf''',
@with = N'MOVE N''LogicalFileNameOf LDF file'' TO N''Destinationpath\Logicalfilename.ldf''',
@affinity = 0,
@logging = 0
GO
Example :-
exec master.dbo.xp_restore_database @database = N'Test' ,
@filename = N'Q:\Backup\Test_20130308.bak',
@filenumber = 1,
@with = N'STATS = 10',
@with = N'MOVE N''Test'' TO N''M:\data\Test.mdf''',
@with = N'MOVE N''Test_log'' TO N''H:\logs\Test_log.ldf''',
@affinity = 0,
@logging = 0
GO
SET NOCOUNT ON
DECLARE @DBName varchar(100)
DECLARE @FileName varchar(100)
DECLARE @TempTxt varchar(1000)
DECLARE @Cnt smallint
SET @TempTxt = 'USE Master;' + CHAR(13) +
'EXEC xp_fixeddrives;'
DECLARE cmdtxt_cursor CURSOR FOR SELECT RTRIM(master..Sysdatabases.Name) as [DBName],
RTRIM(master..Sysaltfiles.Name) as [FileName]
FROM master..Sysdatabases
Inner join master..Sysaltfiles on master..Sysaltfiles.DBID = master..Sysdatabases.DBID
WHERE master..Sysdatabases.DBID not in (1,2,3,4) and Patindex('%log%', master..Sysaltfiles.Name) > 0
and ((master..Sysaltfiles.Size)*8/1024) > 0 and Patindex ('% %', master..Sysdatabases.Name) = 0
ORDER BY Left(master..Sysaltfiles.FileName, 1)
SET @TempTxt = 'USE Master;' + CHAR(13) +
'EXEC xp_fixeddrives;'
--PRINT @TempTxt
EXEC (@TempTxt)
OPEN cmdtxt_cursor
FETCH NEXT FROM cmdtxt_cursor INTO @DBName, @FileName
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT 'Processing : ' + @DBName
If Exists(Select master..Sysdatabases.[Name] From master..Sysdatabases
Where master..Sysdatabases.[Name] = @DBName)
BEGIN
SET @TempTxt = 'USE [' + @DBName + '];' + CHAR(13) +
'BACKUP LOG [' + @DBName + '] WITH TRUNCATE_ONLY ;' + CHAR(13) +
'DBCC SHRINKFILE (''' + @FileName + ''', 1);'
--PRINT @TempTxt
EXEC (@TempTxt)
FETCH NEXT FROM cmdtxt_cursor INTO @DBName, @FileName
END
END
CLOSE cmdtxt_cursor
DEALLOCATE cmdtxt_cursor
SET @TempTxt = 'USE Master;' + CHAR(13) +
'EXEC xp_fixeddrives;'
--PRINT @TempTxt
EXEC (@TempTxt)
GO
DECLARE @DBName varchar(100)
DECLARE @FileName varchar(100)
DECLARE @TempTxt varchar(1000)
DECLARE @Cnt smallint
SET @TempTxt = 'USE Master;' + CHAR(13) +
'EXEC xp_fixeddrives;'
DECLARE cmdtxt_cursor CURSOR FOR SELECT RTRIM(master..Sysdatabases.Name) as [DBName],
RTRIM(master..Sysaltfiles.Name) as [FileName]
FROM master..Sysdatabases
Inner join master..Sysaltfiles on master..Sysaltfiles.DBID = master..Sysdatabases.DBID
WHERE master..Sysdatabases.DBID not in (1,2,3,4) and Patindex('%log%', master..Sysaltfiles.Name) > 0
and ((master..Sysaltfiles.Size)*8/1024) > 0 and Patindex ('% %', master..Sysdatabases.Name) = 0
ORDER BY Left(master..Sysaltfiles.FileName, 1)
SET @TempTxt = 'USE Master;' + CHAR(13) +
'EXEC xp_fixeddrives;'
--PRINT @TempTxt
EXEC (@TempTxt)
OPEN cmdtxt_cursor
FETCH NEXT FROM cmdtxt_cursor INTO @DBName, @FileName
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT 'Processing : ' + @DBName
If Exists(Select master..Sysdatabases.[Name] From master..Sysdatabases
Where master..Sysdatabases.[Name] = @DBName)
BEGIN
SET @TempTxt = 'USE [' + @DBName + '];' + CHAR(13) +
'BACKUP LOG [' + @DBName + '] WITH TRUNCATE_ONLY ;' + CHAR(13) +
'DBCC SHRINKFILE (''' + @FileName + ''', 1);'
--PRINT @TempTxt
EXEC (@TempTxt)
FETCH NEXT FROM cmdtxt_cursor INTO @DBName, @FileName
END
END
CLOSE cmdtxt_cursor
DEALLOCATE cmdtxt_cursor
SET @TempTxt = 'USE Master;' + CHAR(13) +
'EXEC xp_fixeddrives;'
--PRINT @TempTxt
EXEC (@TempTxt)
GO
Issue :- A domain SQL service
account password has been changes and after that the user is complaining that
he is not able to access xp_cmdshell.
Error 1326 was seen.
Resolution :- We checked the SQL server properties and found that the same account was being used as a proxy account in SQL server. Updated the new password of the service account at proxy account and then it was able to access the xp_cmdshell.
Error 1326 was seen.
Resolution :- We checked the SQL server properties and found that the same account was being used as a proxy account in SQL server. Updated the new password of the service account at proxy account and then it was able to access the xp_cmdshell.
Below script will grant db_owner
role to a newly created login on all user databases. We can use the same script
to grant any database level role to the login on all user database.
Script 1 :- For Windows Login
create login loginname from windows
DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')
BEGIN USE ? EXEC(''create user username for login loginname'') END '
EXEC sp_MSforeachdb @command
DECLARE @command1 varchar(1000)
Select @command1 = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')
BEGIN USE ? EXEC sp_addrolemember ''db_owner'', ''username'' END'
EXEC sp_MSforeachdb @command1
For SQL Login
create login loginname with password = ' Enter the password for the login'
DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')
BEGIN USE ? EXEC(''create user username for login loginname'') END '
EXEC sp_MSforeachdb @command
DECLARE @command1 varchar(1000)
Select @command1 = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')
BEGIN USE ? EXEC sp_addrolemember ''db_owner'', ''username'' END'
EXEC sp_MSforeachdb @command1
create proc LoginsAndUsers
as
begin
create table #systemdbs (name sysname)
insert #systemdbs
select 'master'
union select 'msdb'
union select 'model'
union select 'tempdb'
create table #dbusers (DatabaseName sysname, UserName sysname, GroupName sysname, LoginName sysname null,
DefDBName sysname null, DefSchemaName sysname null, UserID int, SID varbinary(1000))
create table #dbusersbuffer ( UserName sysname, GroupName sysname, LoginName sysname null,
DefDBName sysname null, DefSchemaName sysname null, UserID int, SID varbinary(1000))
declare @Command nvarchar(1012)
set @Command= 'if not exists (select * from #systemdbs where name = ''?'') begin '+char(13)+
+'use ? ; insert #dbusersbuffer exec sp_helpuser'+char(13)
+'insert #dbusers select ''?'', * from #dbusersbuffer'+char(13)
+'truncate table #dbusersbuffer'+char(13)
+'end'
print @Command
exec sp_MSforeachdb @command1 = @Command
select databasename,username,groupname,loginname,defdbname from #dbusers where username != 'dbo' and groupname !='public' order by DatabaseName
drop table #dbusers
drop table #dbusersbuffer
drop table #systemdbs
end
go
exec LoginsAndUsers
drop proc loginsandusers
create proc LoginsAndUsers
as
begin
create table #systemdbs (name sysname)
insert #systemdbs
select 'master'
union select 'msdb'
union select 'model'
union select 'tempdb'
create table #dbusers (DatabaseName sysname, UserName sysname, GroupName
sysname, LoginName sysname null,DefDBName sysname null, UserID int, SID varbinary(1000))
create table #dbusersbuffer ( UserName sysname, GroupName sysname, LoginName
sysname null,DefDBName sysname null, UserID int, SID varbinary(1000))
declare @Command nvarchar(1012)
set @Command= 'if not exists (select * from #systemdbs where name = ''?'') begin '+char(13)+
+'use [?] ; insert #dbusersbuffer exec sp_helpuser'+char(13)
+'insert #dbusers select ''?'', * from #dbusersbuffer'+char(13)
+'truncate table #dbusersbuffer'+char(13)
+'end'
print @Command
exec sp_MSforeachdb @command1 = @Command
select databasename,username,groupname,loginname,defdbname from #dbusers where username != 'dbo' and groupname !='public' order by DatabaseName
drop table #dbusers
drop table #dbusersbuffer
drop table #systemdbs
end
go
exec LoginsAndUsers
drop proc loginsandusers
The below script will assist you to find username and corresponding loginname in SQL SERVER 2005/2008/2008R2
create proc LoginsAndUsers
as
begin
create table #systemdbs (name sysname)
insert #systemdbs
select 'tempdb'
create table #dbusers (DatabaseName sysname, UserName sysname, GroupName sysname, LoginName sysname null,
DefDBName sysname null, DefSchemaName sysname null, UserID int, SID varbinary(1000))
create table #dbusersbuffer ( UserName sysname, GroupName sysname, LoginName sysname null,
DefDBName sysname null, DefSchemaName sysname null, UserID int, SID varbinary(1000))
declare @Command nvarchar(1012)
set @Command= 'if not exists (select * from #systemdbs where name = ''?'') begin '+char(13)+
+'use ? ; insert #dbusersbuffer exec sp_helpuser'+char(13)
+'insert #dbusers select ''?'', * from #dbusersbuffer'+char(13)
+'truncate table #dbusersbuffer'+char(13)
+'end'
print @Command
exec sp_MSforeachdb @command1 = @Command
select databasename,username,groupname,loginname,defdbname from #dbusers where groupname !='public' order by DatabaseName
drop table #dbusers
drop table #dbusersbuffer
drop table #systemdbs
end
go
exec LoginsAndUsers
drop proc loginsandusers
Hi Friends,
Once I faced a issue when I was applying SP2 on SQL Server 2008R2. Whenever I clicked the setup icon the black CMD screen runs and disappears. Nothing happened after that and every time I click the setup icon the result is same. Then I thought that SP2 might me corrupt, so i downloaded a new SP2, but it also behaved like the earlier one. After much effort I was able to rectify the problem using the below steps.
The .Net error : the SQLsetlog gives below error
Error: Cannot load .Net security policy file
Error: InitializeSqlSetupCodeGroupCore(32bit) failed
Error: InitializeSqlSetupCodeGroup failed: 0x80004005
Setup closed with exit code: 0x80004005
Ran in cmd : C:\Windows\Microsoft.NET\Framework\v2.0.50727\caspol.exe -machine -reset
if you have 64 bit then use Framework64
Script 1 :- For Windows Login
create login loginname from windows
DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')
BEGIN USE ? EXEC(''create user username for login loginname'') END '
EXEC sp_MSforeachdb @command
DECLARE @command1 varchar(1000)
Select @command1 = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')
BEGIN USE ? EXEC sp_addrolemember ''db_owner'', ''username'' END'
EXEC sp_MSforeachdb @command1
For SQL Login
create login loginname with password = ' Enter the password for the login'
DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')
BEGIN USE ? EXEC(''create user username for login loginname'') END '
EXEC sp_MSforeachdb @command
DECLARE @command1 varchar(1000)
Select @command1 = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')
BEGIN USE ? EXEC sp_addrolemember ''db_owner'', ''username'' END'
EXEC sp_MSforeachdb @command1
The below script will provide all
the details of the SQL Server 2005/2008/2008R2. This script can be very useful
when you are taking the server in support and you want to keep a record of the
server details at the time of possession.
--------------------------------------------------------
SET NOCOUNT ON
use master
go
print '***************************************************************'
print ' MANUAL ACTIVITIES '
print ' '
print ' A. See database startup parameters '
print ' B. See SQL Server Error Log and NT Event Viewer '
print ' C. See authentication mode ( NATIVE or MIXED ) '
print ' D. See SQL Server and SQL Agent services account startup '
print ' E. See SQL Mail configuration '
print ' F. See backup politic ( full and transaction ) '
print '***************************************************************'
print ''
print '1. General Info'
print '*********************'
print ''
print 'Server Name...............: ' + convert(varchar(30),@@SERVERNAME)
print 'Instance..................: ' + convert(varchar(30),@@SERVICENAME)
print 'Current Date Time.........: ' + convert(varchar(30),getdate(),113)
print 'User......................: ' + USER_NAME()
go
print ''
print '1.1 Database and Operational System versions.'
print '----------------------------------------------'
print ''
select @@version
go
exec master..xp_msver
go
print ''
print'*****************************'
SELECT createdate as Sql_Server_Install_Date
FROM sys.syslogins
where name = 'NT AUTHORITY\SYSTEM'
print ''
print '1.2 Miscelaneous'
print '---------------------------'
print ''
select convert(varchar(30),login_time,109) as 'Servidor inicializado em ' from master..sysprocesses where spid = 1
print 'Number of connections..: ' + convert(varchar(30),@@connections)
print 'Language...............: ' + convert(varchar(30),@@language)
print 'Language Id............: ' + convert(varchar(30),@@langid)
print 'Lock Timeout...........: ' + convert(varchar(30),@@LOCK_TIMEOUT)
print 'Maximum of connections.: ' + convert(varchar(30),@@MAX_CONNECTIONS)
print 'Server Name............: ' + convert(varchar(30),@@SERVERNAME)
print 'Instance...............: ' + convert(varchar(30),@@SERVICENAME)
print ''
print 'CPU Busy...........: ' + convert(varchar(30),@@CPU_BUSY/1000)
print 'CPU Idle...........: ' + convert(varchar(30),@@IDLE/1000)
print 'IO Busy............: ' + convert(varchar(30),@@IO_BUSY/1000)
print 'Packets received...: ' + convert(varchar(30),@@PACK_RECEIVED)
print 'Packets sent.......: ' + convert(varchar(30),@@PACK_SENT)
print 'Packets w errors...: ' + convert(varchar(30),@@PACKET_ERRORS)
print 'TimeTicks..........: ' + convert(varchar(30),@@TIMETICKS)
print 'IO Errors..........: ' + convert(varchar(30),@@TOTAL_ERRORS)
print 'Total Read.........: ' + convert(varchar(30),@@TOTAL_READ)
print 'Total Write.........: ' + convert(varchar(30),@@TOTAL_WRITE)
go
----------------------------------------------------------------------------------------------------------
print ''
print '2. Server Parameters'
print '*************************'
print ''
--exec sp_configure 'show advanced options',1
exec sp_configure
go
----------------------------------------------------------------------------------------------------------
print ''
print '3. Databases parameters'
print '***************************'
print ''
exec sp_helpdb
go
SELECT LEFT(name,30) AS DB,
SUBSTRING(CASE status & 1 WHEN 0 THEN '' ELSE ',autoclose' END +
CASE status & 4 WHEN 0 THEN '' ELSE ',select into/bulk copy' END +
CASE status & 8 WHEN 0 THEN '' ELSE ',trunc. log on chkpt' END +
CASE status & 16 WHEN 0 THEN '' ELSE ',torn page detection' END +
CASE status & 32 WHEN 0 THEN '' ELSE ',loading' END +
CASE status & 64 WHEN 0 THEN '' ELSE ',pre-recovery' END +
CASE status & 128 WHEN 0 THEN '' ELSE ',recovering' END +
CASE status & 256 WHEN 0 THEN '' ELSE ',not recovered' END +
CASE status & 512 WHEN 0 THEN '' ELSE ',offline' END +
CASE status & 1024 WHEN 0 THEN '' ELSE ',read only' END +
CASE status & 2048 WHEN 0 THEN '' ELSE ',dbo USE only' END +
CASE status & 4096 WHEN 0 THEN '' ELSE ',single user' END +
CASE status & 32768 WHEN 0 THEN '' ELSE ',emergency mode' END +
CASE status & 4194304 WHEN 0 THEN '' ELSE ',autoshrink' END +
CASE status & 1073741824 WHEN 0 THEN '' ELSE ',cleanly shutdown' END +
CASE status2 & 16384 WHEN 0 THEN '' ELSE ',ANSI NULL default' END +
CASE status2 & 65536 WHEN 0 THEN '' ELSE ',concat NULL yields NULL' END +
CASE status2 & 131072 WHEN 0 THEN '' ELSE ',recursive triggers' END +
CASE status2 & 1048576 WHEN 0 THEN '' ELSE ',default TO local cursor' END +
CASE status2 & 8388608 WHEN 0 THEN '' ELSE ',quoted identifier' END +
CASE status2 & 33554432 WHEN 0 THEN '' ELSE ',cursor CLOSE on commit' END +
CASE status2 & 67108864 WHEN 0 THEN '' ELSE ',ANSI NULLs' END +
CASE status2 & 268435456 WHEN 0 THEN '' ELSE ',ANSI warnings' END +
CASE status2 & 536870912 WHEN 0 THEN '' ELSE ',full text enabled' END,
2,8000) AS Descr
FROM master..sysdatabases
go
----------------------------------------------------------------------------------------------------------
print ''
print '4. LOG utilization'
print '****************************'
print ''
dbcc sqlperf(logspace)
go
----------------------------------------------------------------------------------------------------------
print ''
print '5. Datafiles list'
print '***********************'
print ''
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TempForFileStats '))
DROP TABLE #TempForFileStats
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TempForDataFile'))
DROP TABLE #TempForDataFile
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TempForLogFile'))
DROP TABLE #TempForLogFile
DECLARE @DBName nvarchar(100)
DECLARE @SQLString nvarchar (4000)
DECLARE c_db CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE status&512 = 0
CREATE TABLE #TempForFileStats([Server Name] nvarchar(40),
[Database Name] nvarchar(100),
[File Name] nvarchar(128),
[Usage Type] varchar (6),
[Size (MB)] real,
[Space Used (MB)] real,
[MaxSize (MB)] real,
[Next Allocation (MB)] real,
[Growth Type] varchar (12),
[File Id] smallint,
[Group Id] smallint,
[Physical File] nvarchar (260),
[Date Checked] datetime)
CREATE TABLE #TempForDataFile ([File Id] smallint,
[Group Id] smallint,
[Total Extents] int,
[Used Extents] int,
[File Name] nvarchar(128),
[Physical File] nvarchar(260))
CREATE TABLE #TempForLogFile ([File Id] int,
[Size (Bytes)] real,
[Start Offset] varchar(30),
[FSeqNo] int,
[Status] int,
[Parity] smallint,
[CreateTime] varchar(30))
OPEN c_db
FETCH NEXT FROM c_db INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString = 'SELECT @@SERVERNAME as ''ServerName'', ' + -- changed by seraj : *8 to *8.000000000
'''' + @DBName + '''' + ' as ''Database'', ' +
' f.name, ' +
' CASE ' +
' WHEN (64 & f.status) = 64 THEN ''Log'' ' +
' ELSE ''Data'' ' +
' END as ''Usage Type'', ' +
' f.size*8.00000000/1024.00 as ''Size (MB)'', ' +
' NULL as ''Space Used (MB)'', ' +
' CASE f.maxsize ' +
' WHEN -1 THEN -1 ' +
' WHEN 0 THEN f.size*8.00000000/1024.00 ' +
' ELSE f.maxsize*8.00000000/1024.00 ' +
' END as ''Max Size (MB)'', ' +
' CASE ' +
' WHEN (1048576&f.status) = 1048576 THEN (growth/100.00)*(f.size*8.00000000/1024.00) ' +
' WHEN f.growth =0 THEN 0 ' +
' ELSE f.growth*8.00000000/1024.00 ' +
' END as ''Next Allocation (MB)'', ' +
' CASE ' +
' WHEN (1048576&f.status) = 1048576 THEN ''Percentage'' ' +
' ELSE ''Pages'' ' +
' END as ''Usage Type'', ' +
' f.fileid, ' +
' f.groupid, ' +
' filename, ' +
' getdate() ' +
' FROM [' + @DBName + '].dbo.sysfiles f' -- Seraj Alam added []
INSERT #TempForFileStats
EXECUTE(@SQLString)
------------------------------------------------------------------------
SET @SQLString = 'USE [' + @DBName + '] DBCC SHOWFILESTATS' -- seraj alam added []
INSERT #TempForDataFile
EXECUTE(@SQLString)
--
UPDATE #TempForFileStats
SET [Space Used (MB)] = s.[Used Extents]*64/1024.00
FROM #TempForFileStats f,
#TempForDataFile s
WHERE f.[File Id] = s.[File Id]
AND f.[Group Id] = s.[Group Id]
AND f.[Database Name] = @DBName
--
TRUNCATE TABLE #TempForDataFile
-------------------------------------------------------------------------
SET @SQLString = 'USE [' + @DBName + '] DBCC LOGINFO' -- seraj alam added []
INSERT #TempForLogFile
EXECUTE(@SQLString)
--
UPDATE #TempForFileStats
SET [Space Used (MB)] = (SELECT (MIN(l.[Start Offset]) +
SUM(CASE
WHEN l.Status <> 0 THEN l.[Size (Bytes)]
ELSE 0
END))/1048576.00
FROM #TempForLogFile l
WHERE l.[File Id] = f.[File Id])
FROM #TempForFileStats f
WHERE f.[Database Name] = @DBName
AND f.[Usage Type] = 'Log'
--
TRUNCATE TABLE #TempForLogFile
-------------------------------------------------------------------------
FETCH NEXT FROM c_db INTO @DBName
END
DEALLOCATE c_db
SELECT * FROM #TempForFileStats
------------
DROP TABLE #TempForFileStats
DROP TABLE #TempForDataFile
DROP TABLE #TempForLogFile
go
----------------------------------------------------------------------------------------------------------
print ''
print '6. IO per datafile'
print '******************'
print ''
use tempdb
go
if exists (select [id] from sysobjects where [id] = OBJECT_ID ('#TBL_DATABASEFILES'))
DROP TABLE #TBL_DATABASEFILES
if exists (select [id] from sysobjects where [id] = OBJECT_ID ('#TBL_FILESTATISTICS'))
DROP TABLE #TBL_FILESTATISTICS
DECLARE @INT_LOOPCOUNTER INTEGER
DECLARE @INT_MAXCOUNTER INTEGER
DECLARE @INT_DBID INTEGER
DECLARE @INT_FILEID INTEGER
DECLARE @SNM_DATABASENAME SYSNAME
DECLARE @SNM_FILENAME SYSNAME
DECLARE @NVC_EXECUTESTRING NVARCHAR(4000)
DECLARE @MTB_DATABASES TABLE (
ID INT IDENTITY,
DBID INT,
DBNAME SYSNAME )
CREATE TABLE #TBL_DATABASEFILES (
ID INT IDENTITY,
DBID INT,
FILEID INT,
FILENAME SYSNAME,
FILENAME1 varchar(600),
DATABASENAME SYSNAME)
INSERT INTO @MTB_DATABASES (DBID,DBNAME) SELECT DBID,NAME FROM MASTER.DBO.SYSDATABASES ORDER BY DBID
SET @INT_LOOPCOUNTER = 1
SELECT @INT_MAXCOUNTER=MAX(ID) FROM @MTB_DATABASES
WHILE @INT_LOOPCOUNTER <= @INT_MAXCOUNTER
BEGIN
SELECT @INT_DBID = DBID,@SNM_DATABASENAME=DBNAME FROM @MTB_DATABASES WHERE ID = @INT_LOOPCOUNTER
SET @NVC_EXECUTESTRING = 'INSERT INTO #TBL_DATABASEFILES(DBID,FILEID,FILENAME, FILENAME1,DATABASENAME) SELECT '+STR(@INT_DBID)+' AS DBID,FILEID,NAME AS FILENAME, FILENAME AS FILENAME1,'''+@SNM_DATABASENAME+''' AS DATABASENAME FROM ['+@SNM_DATABASENAME+'].DBO.SYSFILES'
EXEC SP_EXECUTESQL @NVC_EXECUTESTRING
SET @INT_LOOPCOUNTER = @INT_LOOPCOUNTER + 1
END
--'OK WE NOW HAVE ALL THE DATABASES AND FILENAMES ETC....
CREATE TABLE #TBL_FILESTATISTICS (
ID INT IDENTITY,
DBID INT,
FILEID INT,
DATABASENAME SYSNAME,
FILENAME SYSNAME,
SAMPLETIME DATETIME,
NUMBERREADS BIGINT,
NUMBERWRITES BIGINT,
BYTESREAD BIGINT,
BYTESWRITTEN BIGINT,
IOSTALLMS BIGINT)
SELECT @INT_MAXCOUNTER=MAX(ID) FROM #TBL_DATABASEFILES
SET @INT_LOOPCOUNTER = 1
WHILE @INT_LOOPCOUNTER <= @INT_MAXCOUNTER
BEGIN
SELECT @INT_DBID = DBID,@INT_FILEID=FILEID,@SNM_DATABASENAME=DATABASENAME,@SNM_FILENAME=FILENAME FROM #TBL_DATABASEFILES WHERE ID = @INT_LOOPCOUNTER
INSERT INTO #TBL_FILESTATISTICS(DBID,FILEID,SAMPLETIME,NUMBERREADS,NUMBERWRITES,BYTESREAD,BYTESWRITTEN,IOSTALLMS,DATABASENAME,FILENAME)
SELECT DBID,FILEID,GETDATE(),NUMBERREADS,NUMBERWRITES,BYTESREAD,BYTESWRITTEN,IOSTALLMS,@SNM_DATABASENAME AS DATABASENAME,@SNM_FILENAME AS FILENAME FROM :: FN_VIRTUALFILESTATS(@INT_DBID,@INT_FILEID)
SET @INT_LOOPCOUNTER = @INT_LOOPCOUNTER + 1
END
select * from #TBL_FILESTATISTICS
drop table #TBL_DATABASEFILES
drop table #TBL_FILESTATISTICS
go
---------------------------------------------------------------------------------------
print ''
print '7. List of last backup full''s'
print '*************************************'
print ''
select SUBSTRING(s.name,1,40) AS 'Database',
CAST(b.backup_start_date AS char(11)) AS 'Backup Date ',
CASE WHEN b.backup_start_date > DATEADD(dd,-1,getdate())
THEN 'Backup is current within a day'
WHEN b.backup_start_date > DATEADD(dd,-7,getdate())
THEN 'Backup is current within a week'
ELSE '*****CHECK BACKUP!!!*****'
END
AS 'Comment'
from master..sysdatabases s
LEFT OUTER JOIN msdb..backupset b
ON s.name = b.database_name
AND b.backup_start_date = (SELECT MAX(backup_start_date)
FROM msdb..backupset
WHERE database_name = b.database_name
AND type = 'D') -- full database backups only, not log backups
WHERE s.name <> 'tempdb'
ORDER BY s.name
go
----------------------------------------------------------------------------------------------------------
print ''
print '8. List of logins'
print '********************'
print ''
exec sp_helplogins
go
----------------------------------------------------------------------------------------------------------
print ''
print '9. List of users per role'
print '*******************************'
print ''
exec sp_helpsrvrolemember
go
----------------------------------------------------------------------------------------------------------
print ''
print '10.List of special users per database'
print '*************************************'
print ''
declare @name sysname,
@SQL nvarchar(600)
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#tmpTable'))
drop table #tmpTable
CREATE TABLE #tmpTable (
[DATABASE_NAME] sysname NOT NULL ,
[USER_NAME] sysname NOT NULL,
[ROLE_NAME] sysname NOT NULL)
declare c1 cursor for
select name from master.dbo.sysdatabases
open c1
fetch c1 into @name
while @@fetch_status >= 0
begin
select @SQL =
'insert into #tmpTable
select N'''+ @name + ''', a.name, c.name
from ' + QuoteName(@name) + '.dbo.sysusers a
join ' + QuoteName(@name) + '.dbo.sysmembers b on b.memberuid = a.uid
join ' + QuoteName(@name) + '.dbo.sysusers c on c.uid = b.groupuid
where a.name != ''dbo'''
/* Insert row for each database */
execute (@SQL)
fetch c1 into @name
end
close c1
deallocate c1
select * from #tmpTable
drop table #tmpTable
go
----------------------------------------------------------------------------------------------------------
print ''
print '11. Information about remote servers '
print '*****************************************'
print ''
Print 'Linked Servers'
print ''
exec sp_linkedservers
print 'linked Server login mappings'
print ''
exec sp_helplinkedsrvlogin
print 'Remote Logins'
print ''
--exec sp_helpremotelogin
go
----------------------------------------------------------------------------------------------------------
print ''
print '12. List of jobs '
print '*******************'
print ''
exec msdb..sp_help_job
go
----------------------------------------------------------------------------------------------------------
print ''
print '13. Cache Hit Ratio '
print '*******************'
print ''
select distinct counter_name,
(select isnull(sum(convert(dec(15,0),B.cntr_value)),0)
from master..sysperfinfo as B (nolock)
where Lower(B.counter_name) like '%hit ratio%'
and A.counter_name = B.counter_name) as CurrHit,
(select isnull(sum(convert(dec(15,0),B.cntr_value)),0)
from master..sysperfinfo as B (nolock)
where Lower(B.counter_name) like '%hit ratio base%'
and lower(B.counter_name) = (lower(ltrim(rtrim(A.counter_name))) + ' base')) as CurrBase,
(select isnull(sum(convert(dec(15,0),B.cntr_value)),0)
from master..sysperfinfo as B (nolock)
where Lower(B.counter_name) like '%hit ratio%'
and A.counter_name = B.counter_name) /
(select isnull(sum(convert(dec(15,0),B.cntr_value)),0)
from master..sysperfinfo as B (nolock)
where Lower(B.counter_name) like '%hit ratio base%'
and lower(B.counter_name) = (lower(ltrim(rtrim(A.counter_name))) + ' base')) as HitRatio
from master..sysperfinfo as A (nolock)
where Lower(A.counter_name) like '%hit ratio%'
and Lower(A.counter_name) not like '%hit ratio base%'
-- Audit list as a double verification
select counter_name,isnull(sum(convert(dec(15,0),cntr_value)),0) as Value
from master..sysperfinfo (nolock)
where Lower(counter_name) like '%hit ratio%'
or Lower(counter_name) like '%hit ratio base%'
group by counter_name
go
----------------------------------------------------------------------------------------------------------
print ''
print '14. SP_WHO '
print '***********'
print ''
exec sp_who
exec sp_who2
go
----------------------------------------------------------------------------------------------------------
print ''
print '14. SP_LOCKS '
print '***********'
print ''
exec sp_lock
go
set nocount on
use msdb
go
Print ''
Print 'Linked Servers'
print '***********'
print ''
exec sp_linkedservers
Print ''
print '15. Maintenance Plans'
print '***********'
print ''
go
--select @@servername "ServerName", smp.plan_id, plan_name, owner, smpd.database_name, smpj.job_id, sj.name, sjs.name "Job Part Name", sjs.enabled "Job Enabled", sjs.freq_type "Frequency", sjs.active_start_time "Job Start Time"
--from sysdbmaintplans smp, sysdbmaintplan_databases smpd, sysdbmaintplan_jobs smpj, sysjobs sj, sysjobschedules sjs
--where smp.plan_id = smpd.plan_id and smp.plan_id = smpj.plan_id and sj.job_id = smpj.job_id and sj.job_id = sjs.job_id
go
Print ''
print 'Maintenance Plan 2005'
SELECT sv.name AS [Name], sv.job_id AS [JobID],sv.originating_server AS [OriginatingServer],
CAST(sv.enabled AS bit) AS [IsEnabled],
ISNULL(sv.description,N'') AS [Description],sv.start_step_id AS [StartStepID],ISNULL(suser_sname(sv.owner_sid), N'') AS [OwnerLoginName],
sv.notify_level_eventlog AS [EventLogLevel],sv.notify_level_email AS [EmailLevel],sv.notify_level_netsend AS [NetSendLevel],
sv.notify_level_page AS [PageLevel],sv.delete_level AS [DeleteLevel],sv.date_created AS [DateCreated],sv.date_modified AS [DateLastModified],
sv.version_number AS [VersionNumber]FROM msdb.dbo.sysjobs_view AS sv
go
set dateformat dmy
use msdb
go
select y.Server_name, y.database_name, y.database_creation_date, y.backup_start_Date, y.backup_finish_Date , y.backup_file, y.backup_size
from (
select c.Server_name, c.database_name, c.database_creation_date, c.backup_start_Date, c.backup_finish_Date , a.physical_device_name as backup_file, c.backup_size
from Backupmediafamily a inner join backupset c on a.media_set_id=c.media_set_id
inner join backupfile b on
c.backup_set_id=b.backup_set_id and b.file_type='D'
) y
inner join
(select distinct c.database_name, c.backup_finish_Date as backup_finish_date from Backupmediafamily a inner join backupset c on a.media_set_id=c.media_set_id
inner join backupfile b on
c.backup_set_id=b.backup_set_id and b.file_type='D' where c.backup_finish_date > getdate()-10
) z
on y.database_name=z.database_name and y.backup_finish_Date=z.backup_finish_date
Print ''
Print '16. OTHER'
PRINT 'DTS Packages'
print '***********'
print ''
go
select @@servername "ServerName", sd.name, sd.id, sd.categoryid, sdc.name "Category", sd.description, owner, createdate
from sysdtspackages sd, sysdtscategories sdc
where sd.categoryid = sdc.id
go
exec sp_MSgetalertinfo
print ''
print 'Alerts settings'
print '***********'
print ''
exec msdb..sp_help_alert
Print ''
Print 'Operators'
print '***********'
print ''
EXECUTE sp_help_operator
print ''
print 'SQL Mails'
EXECUTE master.dbo.xp_sqlagent_notify N'M',null,null,null,N'E'
print ''
Print 'SQL Agent Propertes'
EXECUTE msdb.dbo.sp_get_sqlagent_properties
print ''
print 'Startup Parameters 01'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg0'
print ''
print 'Startup Parameters 02'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg1'
print ''
print 'Startup Parameters 03'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg2'
print ''
print 'Startup Parameters 04'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg3'
-- Though there is very little chance of more than 4 parameters, still it is good to check.
print ''
print 'Startup Parameters 05'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg4'
print ''
print 'Startup Parameters 06'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg5'
print ''
--exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath'
print ''
print 'Login Mode'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', 'LoginMode'
go
print ''
Print 'Audit Level'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel'
Print ''
Print 'Clustered?'
select convert(int, serverproperty(N'isclustered'))
go
Print ''
Print 'Is Mapi set?'
DECLARE @retval varchar(255) EXECUTE master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Windows Messaging Subsystem','MAPIX',@retval OUTPUT SELECT @retval
go
Print ''
Print 'Mail Account'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'MailAccountName'
Print ''
set nocount off
print '******************************************************************'
print ' FIM '
print '******************************************************************'
----------------------------------------------------------------------------------------------------------
set nocount off
print ''
print 'disabled logins'
use master
go
select name,type,is_disabled,create_date from sys.server_principals where is_disabled = '1'
go
-------------------------------------------------------------------------------------------------------
print ''
print 'deny logins'
use master
go
select name,loginname,denylogin,createdate,hasaccess from sys.syslogins where denylogin ='1'
go
-----------------------------------------------------------------------------------------------------
print '********************************************'
print ''
print ' SQL server Ip and Port'
use master
select distinct local_net_address,local_tcp_port from sys.dm_exec_connections
where client_net_address != '<local machine>'
go
----------------------------------------------------------------------------------------------------------------
--------------------------------------------------------
SET NOCOUNT ON
use master
go
print '***************************************************************'
print ' MANUAL ACTIVITIES '
print ' '
print ' A. See database startup parameters '
print ' B. See SQL Server Error Log and NT Event Viewer '
print ' C. See authentication mode ( NATIVE or MIXED ) '
print ' D. See SQL Server and SQL Agent services account startup '
print ' E. See SQL Mail configuration '
print ' F. See backup politic ( full and transaction ) '
print '***************************************************************'
print ''
print '1. General Info'
print '*********************'
print ''
print 'Server Name...............: ' + convert(varchar(30),@@SERVERNAME)
print 'Instance..................: ' + convert(varchar(30),@@SERVICENAME)
print 'Current Date Time.........: ' + convert(varchar(30),getdate(),113)
print 'User......................: ' + USER_NAME()
go
print ''
print '1.1 Database and Operational System versions.'
print '----------------------------------------------'
print ''
select @@version
go
exec master..xp_msver
go
print ''
print'*****************************'
SELECT createdate as Sql_Server_Install_Date
FROM sys.syslogins
where name = 'NT AUTHORITY\SYSTEM'
print ''
print '1.2 Miscelaneous'
print '---------------------------'
print ''
select convert(varchar(30),login_time,109) as 'Servidor inicializado em ' from master..sysprocesses where spid = 1
print 'Number of connections..: ' + convert(varchar(30),@@connections)
print 'Language...............: ' + convert(varchar(30),@@language)
print 'Language Id............: ' + convert(varchar(30),@@langid)
print 'Lock Timeout...........: ' + convert(varchar(30),@@LOCK_TIMEOUT)
print 'Maximum of connections.: ' + convert(varchar(30),@@MAX_CONNECTIONS)
print 'Server Name............: ' + convert(varchar(30),@@SERVERNAME)
print 'Instance...............: ' + convert(varchar(30),@@SERVICENAME)
print ''
print 'CPU Busy...........: ' + convert(varchar(30),@@CPU_BUSY/1000)
print 'CPU Idle...........: ' + convert(varchar(30),@@IDLE/1000)
print 'IO Busy............: ' + convert(varchar(30),@@IO_BUSY/1000)
print 'Packets received...: ' + convert(varchar(30),@@PACK_RECEIVED)
print 'Packets sent.......: ' + convert(varchar(30),@@PACK_SENT)
print 'Packets w errors...: ' + convert(varchar(30),@@PACKET_ERRORS)
print 'TimeTicks..........: ' + convert(varchar(30),@@TIMETICKS)
print 'IO Errors..........: ' + convert(varchar(30),@@TOTAL_ERRORS)
print 'Total Read.........: ' + convert(varchar(30),@@TOTAL_READ)
print 'Total Write.........: ' + convert(varchar(30),@@TOTAL_WRITE)
go
----------------------------------------------------------------------------------------------------------
print ''
print '2. Server Parameters'
print '*************************'
print ''
--exec sp_configure 'show advanced options',1
exec sp_configure
go
----------------------------------------------------------------------------------------------------------
print ''
print '3. Databases parameters'
print '***************************'
print ''
exec sp_helpdb
go
SELECT LEFT(name,30) AS DB,
SUBSTRING(CASE status & 1 WHEN 0 THEN '' ELSE ',autoclose' END +
CASE status & 4 WHEN 0 THEN '' ELSE ',select into/bulk copy' END +
CASE status & 8 WHEN 0 THEN '' ELSE ',trunc. log on chkpt' END +
CASE status & 16 WHEN 0 THEN '' ELSE ',torn page detection' END +
CASE status & 32 WHEN 0 THEN '' ELSE ',loading' END +
CASE status & 64 WHEN 0 THEN '' ELSE ',pre-recovery' END +
CASE status & 128 WHEN 0 THEN '' ELSE ',recovering' END +
CASE status & 256 WHEN 0 THEN '' ELSE ',not recovered' END +
CASE status & 512 WHEN 0 THEN '' ELSE ',offline' END +
CASE status & 1024 WHEN 0 THEN '' ELSE ',read only' END +
CASE status & 2048 WHEN 0 THEN '' ELSE ',dbo USE only' END +
CASE status & 4096 WHEN 0 THEN '' ELSE ',single user' END +
CASE status & 32768 WHEN 0 THEN '' ELSE ',emergency mode' END +
CASE status & 4194304 WHEN 0 THEN '' ELSE ',autoshrink' END +
CASE status & 1073741824 WHEN 0 THEN '' ELSE ',cleanly shutdown' END +
CASE status2 & 16384 WHEN 0 THEN '' ELSE ',ANSI NULL default' END +
CASE status2 & 65536 WHEN 0 THEN '' ELSE ',concat NULL yields NULL' END +
CASE status2 & 131072 WHEN 0 THEN '' ELSE ',recursive triggers' END +
CASE status2 & 1048576 WHEN 0 THEN '' ELSE ',default TO local cursor' END +
CASE status2 & 8388608 WHEN 0 THEN '' ELSE ',quoted identifier' END +
CASE status2 & 33554432 WHEN 0 THEN '' ELSE ',cursor CLOSE on commit' END +
CASE status2 & 67108864 WHEN 0 THEN '' ELSE ',ANSI NULLs' END +
CASE status2 & 268435456 WHEN 0 THEN '' ELSE ',ANSI warnings' END +
CASE status2 & 536870912 WHEN 0 THEN '' ELSE ',full text enabled' END,
2,8000) AS Descr
FROM master..sysdatabases
go
----------------------------------------------------------------------------------------------------------
print ''
print '4. LOG utilization'
print '****************************'
print ''
dbcc sqlperf(logspace)
go
----------------------------------------------------------------------------------------------------------
print ''
print '5. Datafiles list'
print '***********************'
print ''
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TempForFileStats '))
DROP TABLE #TempForFileStats
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TempForDataFile'))
DROP TABLE #TempForDataFile
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TempForLogFile'))
DROP TABLE #TempForLogFile
DECLARE @DBName nvarchar(100)
DECLARE @SQLString nvarchar (4000)
DECLARE c_db CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE status&512 = 0
CREATE TABLE #TempForFileStats([Server Name] nvarchar(40),
[Database Name] nvarchar(100),
[File Name] nvarchar(128),
[Usage Type] varchar (6),
[Size (MB)] real,
[Space Used (MB)] real,
[MaxSize (MB)] real,
[Next Allocation (MB)] real,
[Growth Type] varchar (12),
[File Id] smallint,
[Group Id] smallint,
[Physical File] nvarchar (260),
[Date Checked] datetime)
CREATE TABLE #TempForDataFile ([File Id] smallint,
[Group Id] smallint,
[Total Extents] int,
[Used Extents] int,
[File Name] nvarchar(128),
[Physical File] nvarchar(260))
CREATE TABLE #TempForLogFile ([File Id] int,
[Size (Bytes)] real,
[Start Offset] varchar(30),
[FSeqNo] int,
[Status] int,
[Parity] smallint,
[CreateTime] varchar(30))
OPEN c_db
FETCH NEXT FROM c_db INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString = 'SELECT @@SERVERNAME as ''ServerName'', ' + -- changed by seraj : *8 to *8.000000000
'''' + @DBName + '''' + ' as ''Database'', ' +
' f.name, ' +
' CASE ' +
' WHEN (64 & f.status) = 64 THEN ''Log'' ' +
' ELSE ''Data'' ' +
' END as ''Usage Type'', ' +
' f.size*8.00000000/1024.00 as ''Size (MB)'', ' +
' NULL as ''Space Used (MB)'', ' +
' CASE f.maxsize ' +
' WHEN -1 THEN -1 ' +
' WHEN 0 THEN f.size*8.00000000/1024.00 ' +
' ELSE f.maxsize*8.00000000/1024.00 ' +
' END as ''Max Size (MB)'', ' +
' CASE ' +
' WHEN (1048576&f.status) = 1048576 THEN (growth/100.00)*(f.size*8.00000000/1024.00) ' +
' WHEN f.growth =0 THEN 0 ' +
' ELSE f.growth*8.00000000/1024.00 ' +
' END as ''Next Allocation (MB)'', ' +
' CASE ' +
' WHEN (1048576&f.status) = 1048576 THEN ''Percentage'' ' +
' ELSE ''Pages'' ' +
' END as ''Usage Type'', ' +
' f.fileid, ' +
' f.groupid, ' +
' filename, ' +
' getdate() ' +
' FROM [' + @DBName + '].dbo.sysfiles f' -- Seraj Alam added []
INSERT #TempForFileStats
EXECUTE(@SQLString)
------------------------------------------------------------------------
SET @SQLString = 'USE [' + @DBName + '] DBCC SHOWFILESTATS' -- seraj alam added []
INSERT #TempForDataFile
EXECUTE(@SQLString)
--
UPDATE #TempForFileStats
SET [Space Used (MB)] = s.[Used Extents]*64/1024.00
FROM #TempForFileStats f,
#TempForDataFile s
WHERE f.[File Id] = s.[File Id]
AND f.[Group Id] = s.[Group Id]
AND f.[Database Name] = @DBName
--
TRUNCATE TABLE #TempForDataFile
-------------------------------------------------------------------------
SET @SQLString = 'USE [' + @DBName + '] DBCC LOGINFO' -- seraj alam added []
INSERT #TempForLogFile
EXECUTE(@SQLString)
--
UPDATE #TempForFileStats
SET [Space Used (MB)] = (SELECT (MIN(l.[Start Offset]) +
SUM(CASE
WHEN l.Status <> 0 THEN l.[Size (Bytes)]
ELSE 0
END))/1048576.00
FROM #TempForLogFile l
WHERE l.[File Id] = f.[File Id])
FROM #TempForFileStats f
WHERE f.[Database Name] = @DBName
AND f.[Usage Type] = 'Log'
--
TRUNCATE TABLE #TempForLogFile
-------------------------------------------------------------------------
FETCH NEXT FROM c_db INTO @DBName
END
DEALLOCATE c_db
SELECT * FROM #TempForFileStats
------------
DROP TABLE #TempForFileStats
DROP TABLE #TempForDataFile
DROP TABLE #TempForLogFile
go
----------------------------------------------------------------------------------------------------------
print ''
print '6. IO per datafile'
print '******************'
print ''
use tempdb
go
if exists (select [id] from sysobjects where [id] = OBJECT_ID ('#TBL_DATABASEFILES'))
DROP TABLE #TBL_DATABASEFILES
if exists (select [id] from sysobjects where [id] = OBJECT_ID ('#TBL_FILESTATISTICS'))
DROP TABLE #TBL_FILESTATISTICS
DECLARE @INT_LOOPCOUNTER INTEGER
DECLARE @INT_MAXCOUNTER INTEGER
DECLARE @INT_DBID INTEGER
DECLARE @INT_FILEID INTEGER
DECLARE @SNM_DATABASENAME SYSNAME
DECLARE @SNM_FILENAME SYSNAME
DECLARE @NVC_EXECUTESTRING NVARCHAR(4000)
DECLARE @MTB_DATABASES TABLE (
ID INT IDENTITY,
DBID INT,
DBNAME SYSNAME )
CREATE TABLE #TBL_DATABASEFILES (
ID INT IDENTITY,
DBID INT,
FILEID INT,
FILENAME SYSNAME,
FILENAME1 varchar(600),
DATABASENAME SYSNAME)
INSERT INTO @MTB_DATABASES (DBID,DBNAME) SELECT DBID,NAME FROM MASTER.DBO.SYSDATABASES ORDER BY DBID
SET @INT_LOOPCOUNTER = 1
SELECT @INT_MAXCOUNTER=MAX(ID) FROM @MTB_DATABASES
WHILE @INT_LOOPCOUNTER <= @INT_MAXCOUNTER
BEGIN
SELECT @INT_DBID = DBID,@SNM_DATABASENAME=DBNAME FROM @MTB_DATABASES WHERE ID = @INT_LOOPCOUNTER
SET @NVC_EXECUTESTRING = 'INSERT INTO #TBL_DATABASEFILES(DBID,FILEID,FILENAME, FILENAME1,DATABASENAME) SELECT '+STR(@INT_DBID)+' AS DBID,FILEID,NAME AS FILENAME, FILENAME AS FILENAME1,'''+@SNM_DATABASENAME+''' AS DATABASENAME FROM ['+@SNM_DATABASENAME+'].DBO.SYSFILES'
EXEC SP_EXECUTESQL @NVC_EXECUTESTRING
SET @INT_LOOPCOUNTER = @INT_LOOPCOUNTER + 1
END
--'OK WE NOW HAVE ALL THE DATABASES AND FILENAMES ETC....
CREATE TABLE #TBL_FILESTATISTICS (
ID INT IDENTITY,
DBID INT,
FILEID INT,
DATABASENAME SYSNAME,
FILENAME SYSNAME,
SAMPLETIME DATETIME,
NUMBERREADS BIGINT,
NUMBERWRITES BIGINT,
BYTESREAD BIGINT,
BYTESWRITTEN BIGINT,
IOSTALLMS BIGINT)
SELECT @INT_MAXCOUNTER=MAX(ID) FROM #TBL_DATABASEFILES
SET @INT_LOOPCOUNTER = 1
WHILE @INT_LOOPCOUNTER <= @INT_MAXCOUNTER
BEGIN
SELECT @INT_DBID = DBID,@INT_FILEID=FILEID,@SNM_DATABASENAME=DATABASENAME,@SNM_FILENAME=FILENAME FROM #TBL_DATABASEFILES WHERE ID = @INT_LOOPCOUNTER
INSERT INTO #TBL_FILESTATISTICS(DBID,FILEID,SAMPLETIME,NUMBERREADS,NUMBERWRITES,BYTESREAD,BYTESWRITTEN,IOSTALLMS,DATABASENAME,FILENAME)
SELECT DBID,FILEID,GETDATE(),NUMBERREADS,NUMBERWRITES,BYTESREAD,BYTESWRITTEN,IOSTALLMS,@SNM_DATABASENAME AS DATABASENAME,@SNM_FILENAME AS FILENAME FROM :: FN_VIRTUALFILESTATS(@INT_DBID,@INT_FILEID)
SET @INT_LOOPCOUNTER = @INT_LOOPCOUNTER + 1
END
select * from #TBL_FILESTATISTICS
drop table #TBL_DATABASEFILES
drop table #TBL_FILESTATISTICS
go
---------------------------------------------------------------------------------------
print ''
print '7. List of last backup full''s'
print '*************************************'
print ''
select SUBSTRING(s.name,1,40) AS 'Database',
CAST(b.backup_start_date AS char(11)) AS 'Backup Date ',
CASE WHEN b.backup_start_date > DATEADD(dd,-1,getdate())
THEN 'Backup is current within a day'
WHEN b.backup_start_date > DATEADD(dd,-7,getdate())
THEN 'Backup is current within a week'
ELSE '*****CHECK BACKUP!!!*****'
END
AS 'Comment'
from master..sysdatabases s
LEFT OUTER JOIN msdb..backupset b
ON s.name = b.database_name
AND b.backup_start_date = (SELECT MAX(backup_start_date)
FROM msdb..backupset
WHERE database_name = b.database_name
AND type = 'D') -- full database backups only, not log backups
WHERE s.name <> 'tempdb'
ORDER BY s.name
go
----------------------------------------------------------------------------------------------------------
print ''
print '8. List of logins'
print '********************'
print ''
exec sp_helplogins
go
----------------------------------------------------------------------------------------------------------
print ''
print '9. List of users per role'
print '*******************************'
print ''
exec sp_helpsrvrolemember
go
----------------------------------------------------------------------------------------------------------
print ''
print '10.List of special users per database'
print '*************************************'
print ''
declare @name sysname,
@SQL nvarchar(600)
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#tmpTable'))
drop table #tmpTable
CREATE TABLE #tmpTable (
[DATABASE_NAME] sysname NOT NULL ,
[USER_NAME] sysname NOT NULL,
[ROLE_NAME] sysname NOT NULL)
declare c1 cursor for
select name from master.dbo.sysdatabases
open c1
fetch c1 into @name
while @@fetch_status >= 0
begin
select @SQL =
'insert into #tmpTable
select N'''+ @name + ''', a.name, c.name
from ' + QuoteName(@name) + '.dbo.sysusers a
join ' + QuoteName(@name) + '.dbo.sysmembers b on b.memberuid = a.uid
join ' + QuoteName(@name) + '.dbo.sysusers c on c.uid = b.groupuid
where a.name != ''dbo'''
/* Insert row for each database */
execute (@SQL)
fetch c1 into @name
end
close c1
deallocate c1
select * from #tmpTable
drop table #tmpTable
go
----------------------------------------------------------------------------------------------------------
print ''
print '11. Information about remote servers '
print '*****************************************'
print ''
Print 'Linked Servers'
print ''
exec sp_linkedservers
print 'linked Server login mappings'
print ''
exec sp_helplinkedsrvlogin
print 'Remote Logins'
print ''
--exec sp_helpremotelogin
go
----------------------------------------------------------------------------------------------------------
print ''
print '12. List of jobs '
print '*******************'
print ''
exec msdb..sp_help_job
go
----------------------------------------------------------------------------------------------------------
print ''
print '13. Cache Hit Ratio '
print '*******************'
print ''
select distinct counter_name,
(select isnull(sum(convert(dec(15,0),B.cntr_value)),0)
from master..sysperfinfo as B (nolock)
where Lower(B.counter_name) like '%hit ratio%'
and A.counter_name = B.counter_name) as CurrHit,
(select isnull(sum(convert(dec(15,0),B.cntr_value)),0)
from master..sysperfinfo as B (nolock)
where Lower(B.counter_name) like '%hit ratio base%'
and lower(B.counter_name) = (lower(ltrim(rtrim(A.counter_name))) + ' base')) as CurrBase,
(select isnull(sum(convert(dec(15,0),B.cntr_value)),0)
from master..sysperfinfo as B (nolock)
where Lower(B.counter_name) like '%hit ratio%'
and A.counter_name = B.counter_name) /
(select isnull(sum(convert(dec(15,0),B.cntr_value)),0)
from master..sysperfinfo as B (nolock)
where Lower(B.counter_name) like '%hit ratio base%'
and lower(B.counter_name) = (lower(ltrim(rtrim(A.counter_name))) + ' base')) as HitRatio
from master..sysperfinfo as A (nolock)
where Lower(A.counter_name) like '%hit ratio%'
and Lower(A.counter_name) not like '%hit ratio base%'
-- Audit list as a double verification
select counter_name,isnull(sum(convert(dec(15,0),cntr_value)),0) as Value
from master..sysperfinfo (nolock)
where Lower(counter_name) like '%hit ratio%'
or Lower(counter_name) like '%hit ratio base%'
group by counter_name
go
----------------------------------------------------------------------------------------------------------
print ''
print '14. SP_WHO '
print '***********'
print ''
exec sp_who
exec sp_who2
go
----------------------------------------------------------------------------------------------------------
print ''
print '14. SP_LOCKS '
print '***********'
print ''
exec sp_lock
go
set nocount on
use msdb
go
Print ''
Print 'Linked Servers'
print '***********'
print ''
exec sp_linkedservers
Print ''
print '15. Maintenance Plans'
print '***********'
print ''
go
--select @@servername "ServerName", smp.plan_id, plan_name, owner, smpd.database_name, smpj.job_id, sj.name, sjs.name "Job Part Name", sjs.enabled "Job Enabled", sjs.freq_type "Frequency", sjs.active_start_time "Job Start Time"
--from sysdbmaintplans smp, sysdbmaintplan_databases smpd, sysdbmaintplan_jobs smpj, sysjobs sj, sysjobschedules sjs
--where smp.plan_id = smpd.plan_id and smp.plan_id = smpj.plan_id and sj.job_id = smpj.job_id and sj.job_id = sjs.job_id
go
Print ''
print 'Maintenance Plan 2005'
SELECT sv.name AS [Name], sv.job_id AS [JobID],sv.originating_server AS [OriginatingServer],
CAST(sv.enabled AS bit) AS [IsEnabled],
ISNULL(sv.description,N'') AS [Description],sv.start_step_id AS [StartStepID],ISNULL(suser_sname(sv.owner_sid), N'') AS [OwnerLoginName],
sv.notify_level_eventlog AS [EventLogLevel],sv.notify_level_email AS [EmailLevel],sv.notify_level_netsend AS [NetSendLevel],
sv.notify_level_page AS [PageLevel],sv.delete_level AS [DeleteLevel],sv.date_created AS [DateCreated],sv.date_modified AS [DateLastModified],
sv.version_number AS [VersionNumber]FROM msdb.dbo.sysjobs_view AS sv
go
set dateformat dmy
use msdb
go
select y.Server_name, y.database_name, y.database_creation_date, y.backup_start_Date, y.backup_finish_Date , y.backup_file, y.backup_size
from (
select c.Server_name, c.database_name, c.database_creation_date, c.backup_start_Date, c.backup_finish_Date , a.physical_device_name as backup_file, c.backup_size
from Backupmediafamily a inner join backupset c on a.media_set_id=c.media_set_id
inner join backupfile b on
c.backup_set_id=b.backup_set_id and b.file_type='D'
) y
inner join
(select distinct c.database_name, c.backup_finish_Date as backup_finish_date from Backupmediafamily a inner join backupset c on a.media_set_id=c.media_set_id
inner join backupfile b on
c.backup_set_id=b.backup_set_id and b.file_type='D' where c.backup_finish_date > getdate()-10
) z
on y.database_name=z.database_name and y.backup_finish_Date=z.backup_finish_date
Print ''
Print '16. OTHER'
PRINT 'DTS Packages'
print '***********'
print ''
go
select @@servername "ServerName", sd.name, sd.id, sd.categoryid, sdc.name "Category", sd.description, owner, createdate
from sysdtspackages sd, sysdtscategories sdc
where sd.categoryid = sdc.id
go
exec sp_MSgetalertinfo
print ''
print 'Alerts settings'
print '***********'
print ''
exec msdb..sp_help_alert
Print ''
Print 'Operators'
print '***********'
print ''
EXECUTE sp_help_operator
print ''
print 'SQL Mails'
EXECUTE master.dbo.xp_sqlagent_notify N'M',null,null,null,N'E'
print ''
Print 'SQL Agent Propertes'
EXECUTE msdb.dbo.sp_get_sqlagent_properties
print ''
print 'Startup Parameters 01'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg0'
print ''
print 'Startup Parameters 02'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg1'
print ''
print 'Startup Parameters 03'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg2'
print ''
print 'Startup Parameters 04'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg3'
-- Though there is very little chance of more than 4 parameters, still it is good to check.
print ''
print 'Startup Parameters 05'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg4'
print ''
print 'Startup Parameters 06'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg5'
print ''
--exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath'
print ''
print 'Login Mode'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', 'LoginMode'
go
print ''
Print 'Audit Level'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel'
Print ''
Print 'Clustered?'
select convert(int, serverproperty(N'isclustered'))
go
Print ''
Print 'Is Mapi set?'
DECLARE @retval varchar(255) EXECUTE master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Windows Messaging Subsystem','MAPIX',@retval OUTPUT SELECT @retval
go
Print ''
Print 'Mail Account'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'MailAccountName'
Print ''
set nocount off
print '******************************************************************'
print ' FIM '
print '******************************************************************'
----------------------------------------------------------------------------------------------------------
set nocount off
print ''
print 'disabled logins'
use master
go
select name,type,is_disabled,create_date from sys.server_principals where is_disabled = '1'
go
-------------------------------------------------------------------------------------------------------
print ''
print 'deny logins'
use master
go
select name,loginname,denylogin,createdate,hasaccess from sys.syslogins where denylogin ='1'
go
-----------------------------------------------------------------------------------------------------
print '********************************************'
print ''
print ' SQL server Ip and Port'
use master
select distinct local_net_address,local_tcp_port from sys.dm_exec_connections
where client_net_address != '<local machine>'
go
----------------------------------------------------------------------------------------------------------------
The below script will provide all
the details of the SQL Server 2000. This script can be very useful when you are
taking the server in support and you want to keep a record of the server
details at the time of possession.
--------------------------------------------------------
SET NOCOUNT ON
use master
go
print '***************************************************************'
print ' MANUAL ACTIVITIES '
print ' '
print ' A. See database startup parameters '
print ' B. See SQL Server Error Log and NT Event Viewer '
print ' C. See authentication mode ( NATIVE or MIXED ) '
print ' D. See SQL Server and SQL Agent services account startup '
print ' E. See SQL Mail configuration '
print ' F. See backup politic ( full and transaction ) '
print '***************************************************************'
print ''
print '1. General Info'
print '*********************'
print ''
print 'Server Name...............: ' + convert(varchar(30),@@SERVERNAME)
print 'Instance..................: ' + convert(varchar(30),@@SERVICENAME)
print 'Current Date Time.........: ' + convert(varchar(30),getdate(),113)
print 'User......................: ' + USER_NAME()
go
print ''
print '1.1 Database and Operational System versions.'
print '----------------------------------------------'
print ''
select @@version
go
exec master..xp_msver
go
print ''
print'*****************************'
SELECT createdate as Sql_Server_Install_Date
FROM syslogins
where name = 'NT AUTHORITY\SYSTEM'
print ''
print '1.2 Miscelaneous'
print '---------------------------'
print ''
select convert(varchar(30),login_time,109) as 'Servidor inicializado em ' from master..sysprocesses where spid = 1
print 'Number of connections..: ' + convert(varchar(30),@@connections)
print 'Language...............: ' + convert(varchar(30),@@language)
print 'Language Id............: ' + convert(varchar(30),@@langid)
print 'Lock Timeout...........: ' + convert(varchar(30),@@LOCK_TIMEOUT)
print 'Maximum of connections.: ' + convert(varchar(30),@@MAX_CONNECTIONS)
print 'Server Name............: ' + convert(varchar(30),@@SERVERNAME)
print 'Instance...............: ' + convert(varchar(30),@@SERVICENAME)
print ''
print 'CPU Busy...........: ' + convert(varchar(30),@@CPU_BUSY/1000)
print 'CPU Idle...........: ' + convert(varchar(30),@@IDLE/1000)
print 'IO Busy............: ' + convert(varchar(30),@@IO_BUSY/1000)
print 'Packets received...: ' + convert(varchar(30),@@PACK_RECEIVED)
print 'Packets sent.......: ' + convert(varchar(30),@@PACK_SENT)
print 'Packets w errors...: ' + convert(varchar(30),@@PACKET_ERRORS)
print 'TimeTicks..........: ' + convert(varchar(30),@@TIMETICKS)
print 'IO Errors..........: ' + convert(varchar(30),@@TOTAL_ERRORS)
print 'Total Read.........: ' + convert(varchar(30),@@TOTAL_READ)
print 'Total Write.........: ' + convert(varchar(30),@@TOTAL_WRITE)
go
----------------------------------------------------------------------------------------------------------
print ''
print '2. Server Parameters'
print '*************************'
print ''
--exec sp_configure 'show advanced options',1
exec sp_configure
go
----------------------------------------------------------------------------------------------------------
print ''
print '3. Databases parameters'
print '***************************'
print ''
exec sp_helpdb
go
SELECT LEFT(name,30) AS DB,
SUBSTRING(CASE status & 1 WHEN 0 THEN '' ELSE ',autoclose' END +
CASE status & 4 WHEN 0 THEN '' ELSE ',select into/bulk copy' END +
CASE status & 8 WHEN 0 THEN '' ELSE ',trunc. log on chkpt' END +
CASE status & 16 WHEN 0 THEN '' ELSE ',torn page detection' END +
CASE status & 32 WHEN 0 THEN '' ELSE ',loading' END +
CASE status & 64 WHEN 0 THEN '' ELSE ',pre-recovery' END +
CASE status & 128 WHEN 0 THEN '' ELSE ',recovering' END +
CASE status & 256 WHEN 0 THEN '' ELSE ',not recovered' END +
CASE status & 512 WHEN 0 THEN '' ELSE ',offline' END +
CASE status & 1024 WHEN 0 THEN '' ELSE ',read only' END +
CASE status & 2048 WHEN 0 THEN '' ELSE ',dbo USE only' END +
CASE status & 4096 WHEN 0 THEN '' ELSE ',single user' END +
CASE status & 32768 WHEN 0 THEN '' ELSE ',emergency mode' END +
CASE status & 4194304 WHEN 0 THEN '' ELSE ',autoshrink' END +
CASE status & 1073741824 WHEN 0 THEN '' ELSE ',cleanly shutdown' END +
CASE status2 & 16384 WHEN 0 THEN '' ELSE ',ANSI NULL default' END +
CASE status2 & 65536 WHEN 0 THEN '' ELSE ',concat NULL yields NULL' END +
CASE status2 & 131072 WHEN 0 THEN '' ELSE ',recursive triggers' END +
CASE status2 & 1048576 WHEN 0 THEN '' ELSE ',default TO local cursor' END +
CASE status2 & 8388608 WHEN 0 THEN '' ELSE ',quoted identifier' END +
CASE status2 & 33554432 WHEN 0 THEN '' ELSE ',cursor CLOSE on commit' END +
CASE status2 & 67108864 WHEN 0 THEN '' ELSE ',ANSI NULLs' END +
CASE status2 & 268435456 WHEN 0 THEN '' ELSE ',ANSI warnings' END +
CASE status2 & 536870912 WHEN 0 THEN '' ELSE ',full text enabled' END,
2,8000) AS Descr
FROM master..sysdatabases
go
----------------------------------------------------------------------------------------------------------
print ''
print '4. LOG utilization'
print '****************************'
print ''
dbcc sqlperf(logspace)
go
----------------------------------------------------------------------------------------------------------
print ''
print '5. Datafiles list'
print '***********************'
print ''
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TempForFileStats '))
DROP TABLE #TempForFileStats
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TempForDataFile'))
DROP TABLE #TempForDataFile
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TempForLogFile'))
DROP TABLE #TempForLogFile
DECLARE @DBName nvarchar(100)
DECLARE @SQLString nvarchar (4000)
DECLARE c_db CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE status&512 = 0
CREATE TABLE #TempForFileStats([Server Name] nvarchar(40),
[Database Name] nvarchar(100),
[File Name] nvarchar(128),
[Usage Type] varchar (6),
[Size (MB)] real,
[Space Used (MB)] real,
[MaxSize (MB)] real,
[Next Allocation (MB)] real,
[Growth Type] varchar (12),
[File Id] smallint,
[Group Id] smallint,
[Physical File] nvarchar (260),
[Date Checked] datetime)
CREATE TABLE #TempForDataFile ([File Id] smallint,
[Group Id] smallint,
[Total Extents] int,
[Used Extents] int,
[File Name] nvarchar(128),
[Physical File] nvarchar(260))
CREATE TABLE #TempForLogFile ([File Id] int,
[Size (Bytes)] real,
[Start Offset] varchar(30),
[FSeqNo] int,
[Status] int,
[Parity] smallint,
[CreateTime] varchar(30))
OPEN c_db
FETCH NEXT FROM c_db INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString = 'SELECT @@SERVERNAME as ''ServerName'', ' + -- changed by seraj : *8 to *8.000000000
'''' + @DBName + '''' + ' as ''Database'', ' +
' f.name, ' +
' CASE ' +
' WHEN (64 & f.status) = 64 THEN ''Log'' ' +
' ELSE ''Data'' ' +
' END as ''Usage Type'', ' +
' f.size*8.00000000/1024.00 as ''Size (MB)'', ' +
' NULL as ''Space Used (MB)'', ' +
' CASE f.maxsize ' +
' WHEN -1 THEN -1 ' +
' WHEN 0 THEN f.size*8.00000000/1024.00 ' +
' ELSE f.maxsize*8.00000000/1024.00 ' +
' END as ''Max Size (MB)'', ' +
' CASE ' +
' WHEN (1048576&f.status) = 1048576 THEN (growth/100.00)*(f.size*8.00000000/1024.00) ' +
' WHEN f.growth =0 THEN 0 ' +
' ELSE f.growth*8.00000000/1024.00 ' +
' END as ''Next Allocation (MB)'', ' +
' CASE ' +
' WHEN (1048576&f.status) = 1048576 THEN ''Percentage'' ' +
' ELSE ''Pages'' ' +
' END as ''Usage Type'', ' +
' f.fileid, ' +
' f.groupid, ' +
' filename, ' +
' getdate() ' +
' FROM [' + @DBName + '].dbo.sysfiles f' -- Seraj Alam added []
INSERT #TempForFileStats
EXECUTE(@SQLString)
------------------------------------------------------------------------
SET @SQLString = 'USE [' + @DBName + '] DBCC SHOWFILESTATS' -- seraj alam added []
INSERT #TempForDataFile
EXECUTE(@SQLString)
--
UPDATE #TempForFileStats
SET [Space Used (MB)] = s.[Used Extents]*64/1024.00
FROM #TempForFileStats f,
#TempForDataFile s
WHERE f.[File Id] = s.[File Id]
AND f.[Group Id] = s.[Group Id]
AND f.[Database Name] = @DBName
--
TRUNCATE TABLE #TempForDataFile
-------------------------------------------------------------------------
SET @SQLString = 'USE [' + @DBName + '] DBCC LOGINFO' -- seraj alam added []
INSERT #TempForLogFile
EXECUTE(@SQLString)
--
UPDATE #TempForFileStats
SET [Space Used (MB)] = (SELECT (MIN(l.[Start Offset]) +
SUM(CASE
WHEN l.Status <> 0 THEN l.[Size (Bytes)]
ELSE 0
END))/1048576.00
FROM #TempForLogFile l
WHERE l.[File Id] = f.[File Id])
FROM #TempForFileStats f
WHERE f.[Database Name] = @DBName
AND f.[Usage Type] = 'Log'
--
TRUNCATE TABLE #TempForLogFile
-------------------------------------------------------------------------
FETCH NEXT FROM c_db INTO @DBName
END
DEALLOCATE c_db
SELECT * FROM #TempForFileStats
------------
DROP TABLE #TempForFileStats
DROP TABLE #TempForDataFile
DROP TABLE #TempForLogFile
go
----------------------------------------------------------------------------------------------------------
print ''
print '6. IO per datafile'
print '******************'
print ''
use tempdb
go
if exists (select [id] from sysobjects where [id] = OBJECT_ID ('#TBL_DATABASEFILES'))
DROP TABLE #TBL_DATABASEFILES
if exists (select [id] from sysobjects where [id] = OBJECT_ID ('#TBL_FILESTATISTICS'))
DROP TABLE #TBL_FILESTATISTICS
DECLARE @INT_LOOPCOUNTER INTEGER
DECLARE @INT_MAXCOUNTER INTEGER
DECLARE @INT_DBID INTEGER
DECLARE @INT_FILEID INTEGER
DECLARE @SNM_DATABASENAME SYSNAME
DECLARE @SNM_FILENAME SYSNAME
DECLARE @NVC_EXECUTESTRING NVARCHAR(4000)
DECLARE @MTB_DATABASES TABLE (
ID INT IDENTITY,
DBID INT,
DBNAME SYSNAME )
CREATE TABLE #TBL_DATABASEFILES (
ID INT IDENTITY,
DBID INT,
FILEID INT,
FILENAME SYSNAME,
FILENAME1 varchar(600),
DATABASENAME SYSNAME)
INSERT INTO @MTB_DATABASES (DBID,DBNAME) SELECT DBID,NAME FROM MASTER.DBO.SYSDATABASES ORDER BY DBID
SET @INT_LOOPCOUNTER = 1
SELECT @INT_MAXCOUNTER=MAX(ID) FROM @MTB_DATABASES
WHILE @INT_LOOPCOUNTER <= @INT_MAXCOUNTER
BEGIN
SELECT @INT_DBID = DBID,@SNM_DATABASENAME=DBNAME FROM @MTB_DATABASES WHERE ID = @INT_LOOPCOUNTER
SET @NVC_EXECUTESTRING = 'INSERT INTO #TBL_DATABASEFILES(DBID,FILEID,FILENAME, FILENAME1,DATABASENAME) SELECT '+STR(@INT_DBID)+' AS DBID,FILEID,NAME AS FILENAME, FILENAME AS FILENAME1,'''+@SNM_DATABASENAME+''' AS DATABASENAME FROM ['+@SNM_DATABASENAME+'].DBO.SYSFILES'
EXEC SP_EXECUTESQL @NVC_EXECUTESTRING
SET @INT_LOOPCOUNTER = @INT_LOOPCOUNTER + 1
END
--'OK WE NOW HAVE ALL THE DATABASES AND FILENAMES ETC....
CREATE TABLE #TBL_FILESTATISTICS (
ID INT IDENTITY,
DBID INT,
FILEID INT,
DATABASENAME SYSNAME,
FILENAME SYSNAME,
SAMPLETIME DATETIME,
NUMBERREADS BIGINT,
NUMBERWRITES BIGINT,
BYTESREAD BIGINT,
BYTESWRITTEN BIGINT,
IOSTALLMS BIGINT)
SELECT @INT_MAXCOUNTER=MAX(ID) FROM #TBL_DATABASEFILES
SET @INT_LOOPCOUNTER = 1
WHILE @INT_LOOPCOUNTER <= @INT_MAXCOUNTER
BEGIN
SELECT @INT_DBID = DBID,@INT_FILEID=FILEID,@SNM_DATABASENAME=DATABASENAME,@SNM_FILENAME=FILENAME FROM #TBL_DATABASEFILES WHERE ID = @INT_LOOPCOUNTER
INSERT INTO #TBL_FILESTATISTICS(DBID,FILEID,SAMPLETIME,NUMBERREADS,NUMBERWRITES,BYTESREAD,BYTESWRITTEN,IOSTALLMS,DATABASENAME,FILENAME)
SELECT DBID,FILEID,GETDATE(),NUMBERREADS,NUMBERWRITES,BYTESREAD,BYTESWRITTEN,IOSTALLMS,@SNM_DATABASENAME AS DATABASENAME,@SNM_FILENAME AS FILENAME FROM :: FN_VIRTUALFILESTATS(@INT_DBID,@INT_FILEID)
SET @INT_LOOPCOUNTER = @INT_LOOPCOUNTER + 1
END
select * from #TBL_FILESTATISTICS
drop table #TBL_DATABASEFILES
drop table #TBL_FILESTATISTICS
go
---------------------------------------------------------------------------------------
print ''
print '7. List of last backup full''s'
print '*************************************'
print ''
select SUBSTRING(s.name,1,40) AS 'Database',
CAST(b.backup_start_date AS char(11)) AS 'Backup Date ',
CASE WHEN b.backup_start_date > DATEADD(dd,-1,getdate())
THEN 'Backup is current within a day'
WHEN b.backup_start_date > DATEADD(dd,-7,getdate())
THEN 'Backup is current within a week'
ELSE '*****CHECK BACKUP!!!*****'
END
AS 'Comment'
from master..sysdatabases s
LEFT OUTER JOIN msdb..backupset b
ON s.name = b.database_name
AND b.backup_start_date = (SELECT MAX(backup_start_date)
FROM msdb..backupset
WHERE database_name = b.database_name
AND type = 'D') -- full database backups only, not log backups
WHERE s.name <> 'tempdb'
ORDER BY s.name
go
----------------------------------------------------------------------------------------------------------
print ''
print '8. List of logins'
print '********************'
print ''
exec sp_helplogins
go
----------------------------------------------------------------------------------------------------------
print ''
print '9. List of users per role'
print '*******************************'
print ''
exec sp_helpsrvrolemember
go
----------------------------------------------------------------------------------------------------------
print ''
print '10.List of special users per database'
print '*************************************'
print ''
declare @name sysname,
@SQL nvarchar(600)
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#tmpTable'))
drop table #tmpTable
CREATE TABLE #tmpTable (
[DATABASE_NAME] sysname NOT NULL ,
[USER_NAME] sysname NOT NULL,
[ROLE_NAME] sysname NOT NULL)
declare c1 cursor for
select name from master.dbo.sysdatabases
open c1
fetch c1 into @name
while @@fetch_status >= 0
begin
select @SQL =
'insert into #tmpTable
select N'''+ @name + ''', a.name, c.name
from ' + QuoteName(@name) + '.dbo.sysusers a
join ' + QuoteName(@name) + '.dbo.sysmembers b on b.memberuid = a.uid
join ' + QuoteName(@name) + '.dbo.sysusers c on c.uid = b.groupuid
where a.name != ''dbo'''
/* Insert row for each database */
execute (@SQL)
fetch c1 into @name
end
close c1
deallocate c1
select * from #tmpTable
drop table #tmpTable
go
----------------------------------------------------------------------------------------------------------
print ''
print '11. Information about remote servers '
print '*****************************************'
print ''
Print 'Linked Servers'
print ''
exec sp_linkedservers
print 'linked Server login mappings'
print ''
exec sp_helplinkedsrvlogin
print 'Remote Logins'
print ''
--exec sp_helpremotelogin
go
----------------------------------------------------------------------------------------------------------
print ''
print '12. List of jobs '
print '*******************'
print ''
exec msdb..sp_help_job
go
----------------------------------------------------------------------------------------------------------
print ''
print '13. Cache Hit Ratio '
print '*******************'
print ''
select distinct counter_name,
(select isnull(sum(convert(dec(15,0),B.cntr_value)),0)
from master..sysperfinfo as B (nolock)
where Lower(B.counter_name) like '%hit ratio%'
and A.counter_name = B.counter_name) as CurrHit,
(select isnull(sum(convert(dec(15,0),B.cntr_value)),0)
from master..sysperfinfo as B (nolock)
where Lower(B.counter_name) like '%hit ratio base%'
and lower(B.counter_name) = (lower(ltrim(rtrim(A.counter_name))) + ' base')) as CurrBase,
(select isnull(sum(convert(dec(15,0),B.cntr_value)),0)
from master..sysperfinfo as B (nolock)
where Lower(B.counter_name) like '%hit ratio%'
and A.counter_name = B.counter_name) /
(select isnull(sum(convert(dec(15,0),B.cntr_value)),0)
from master..sysperfinfo as B (nolock)
where Lower(B.counter_name) like '%hit ratio base%'
and lower(B.counter_name) = (lower(ltrim(rtrim(A.counter_name))) + ' base')) as HitRatio
from master..sysperfinfo as A (nolock)
where Lower(A.counter_name) like '%hit ratio%'
and Lower(A.counter_name) not like '%hit ratio base%'
-- Audit list as a double verification
select counter_name,isnull(sum(convert(dec(15,0),cntr_value)),0) as Value
from master..sysperfinfo (nolock)
where Lower(counter_name) like '%hit ratio%'
or Lower(counter_name) like '%hit ratio base%'
group by counter_name
go
----------------------------------------------------------------------------------------------------------
print ''
print '14. SP_WHO '
print '***********'
print ''
exec sp_who
exec sp_who2
go
----------------------------------------------------------------------------------------------------------
print ''
print '14. SP_LOCKS '
print '***********'
print ''
exec sp_lock
go
set nocount on
use msdb
go
Print ''
Print 'Linked Servers'
print '***********'
print ''
exec sp_linkedservers
Print ''
print '15. Maintenance Plans'
print '***********'
print ''
go
select @@servername "ServerName", smp.plan_id, plan_name, owner, smpd.database_name, smpj.job_id, sj.name, sjs.name "Job Part Name", sjs.enabled "Job Enabled", sjs.freq_type "Frequency", sjs.active_start_time "Job Start Time"
from sysdbmaintplans smp, sysdbmaintplan_databases smpd, sysdbmaintplan_jobs smpj, sysjobs sj, sysjobschedules sjs
where smp.plan_id = smpd.plan_id and smp.plan_id = smpj.plan_id and sj.job_id = smpj.job_id and sj.job_id = sjs.job_id
go
Print ''
print 'Maintenance Plan 2005'
SELECT sv.name AS [Name], sv.job_id AS [JobID],sv.originating_server AS [OriginatingServer],
CAST(sv.enabled AS bit) AS [IsEnabled],
ISNULL(sv.description,N'') AS [Description],sv.start_step_id AS [StartStepID],ISNULL(suser_sname(sv.owner_sid), N'') AS [OwnerLoginName],
sv.notify_level_eventlog AS [EventLogLevel],sv.notify_level_email AS [EmailLevel],sv.notify_level_netsend AS [NetSendLevel],
sv.notify_level_page AS [PageLevel],sv.delete_level AS [DeleteLevel],sv.date_created AS [DateCreated],sv.date_modified AS [DateLastModified],
sv.version_number AS [VersionNumber]FROM msdb.dbo.sysjobs_view AS sv
go
set dateformat dmy
use msdb
go
select y.Server_name, y.database_name, y.database_creation_date, y.backup_start_Date, y.backup_finish_Date , y.backup_file, y.backup_size
from (
select c.Server_name, c.database_name, c.database_creation_date, c.backup_start_Date, c.backup_finish_Date , a.physical_device_name as backup_file, c.backup_size
from Backupmediafamily a inner join backupset c on a.media_set_id=c.media_set_id
inner join backupfile b on
c.backup_set_id=b.backup_set_id and b.file_type='D'
) y
inner join
(select distinct c.database_name, c.backup_finish_Date as backup_finish_date from Backupmediafamily a inner join backupset c on a.media_set_id=c.media_set_id
inner join backupfile b on
c.backup_set_id=b.backup_set_id and b.file_type='D' where c.backup_finish_date > getdate()-10
) z
on y.database_name=z.database_name and y.backup_finish_Date=z.backup_finish_date
Print ''
Print '16. OTHER'
PRINT 'DTS Packages'
print '***********'
print ''
go
select @@servername "ServerName", sd.name, sd.id, sd.categoryid, sdc.name "Category", sd.description, owner, createdate
from sysdtspackages sd, sysdtscategories sdc
where sd.categoryid = sdc.id
go
exec sp_MSgetalertinfo
print ''
print 'Alerts settings'
print '***********'
print ''
exec msdb..sp_help_alert
Print ''
Print 'Operators'
print '***********'
print ''
EXECUTE sp_help_operator
print ''
print 'SQL Mails'
EXECUTE master.dbo.xp_sqlagent_notify N'M',null,null,null,N'E'
print ''
Print 'SQL Agent Propertes'
EXECUTE msdb.dbo.sp_get_sqlagent_properties
print ''
print 'Startup Parameters 01'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg0'
print ''
print 'Startup Parameters 02'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg1'
print ''
print 'Startup Parameters 03'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg2'
print ''
print 'Startup Parameters 04'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg3'
-- Though there is very little chance of more than 4 parameters, still it is good to check.
print ''
print 'Startup Parameters 05'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg4'
print ''
print 'Startup Parameters 06'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg5'
print ''
--exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath'
print ''
print 'Login Mode'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', 'LoginMode'
go
print ''
Print 'Audit Level'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel'
Print ''
Print 'Clustered?'
select convert(int, serverproperty(N'isclustered'))
go
Print ''
Print 'Is Mapi set?'
DECLARE @retval varchar(255) EXECUTE master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Windows Messaging Subsystem','MAPIX',@retval OUTPUT SELECT @retval
go
Print ''
Print 'Mail Account'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'MailAccountName'
Print ''
set nocount off
print '******************************************************************'
print ' FIM '
print '******************************************************************'
----------------------------------------------------------------------------------------------------------
set nocount off
-----------------------------
print ''
print 'deny logins'
use master
go
select name,loginname,denylogin,createdate,hasaccess from syslogins where denylogin ='1'
go
--------------------------------------------------------
SET NOCOUNT ON
use master
go
print '***************************************************************'
print ' MANUAL ACTIVITIES '
print ' '
print ' A. See database startup parameters '
print ' B. See SQL Server Error Log and NT Event Viewer '
print ' C. See authentication mode ( NATIVE or MIXED ) '
print ' D. See SQL Server and SQL Agent services account startup '
print ' E. See SQL Mail configuration '
print ' F. See backup politic ( full and transaction ) '
print '***************************************************************'
print ''
print '1. General Info'
print '*********************'
print ''
print 'Server Name...............: ' + convert(varchar(30),@@SERVERNAME)
print 'Instance..................: ' + convert(varchar(30),@@SERVICENAME)
print 'Current Date Time.........: ' + convert(varchar(30),getdate(),113)
print 'User......................: ' + USER_NAME()
go
print ''
print '1.1 Database and Operational System versions.'
print '----------------------------------------------'
print ''
select @@version
go
exec master..xp_msver
go
print ''
print'*****************************'
SELECT createdate as Sql_Server_Install_Date
FROM syslogins
where name = 'NT AUTHORITY\SYSTEM'
print ''
print '1.2 Miscelaneous'
print '---------------------------'
print ''
select convert(varchar(30),login_time,109) as 'Servidor inicializado em ' from master..sysprocesses where spid = 1
print 'Number of connections..: ' + convert(varchar(30),@@connections)
print 'Language...............: ' + convert(varchar(30),@@language)
print 'Language Id............: ' + convert(varchar(30),@@langid)
print 'Lock Timeout...........: ' + convert(varchar(30),@@LOCK_TIMEOUT)
print 'Maximum of connections.: ' + convert(varchar(30),@@MAX_CONNECTIONS)
print 'Server Name............: ' + convert(varchar(30),@@SERVERNAME)
print 'Instance...............: ' + convert(varchar(30),@@SERVICENAME)
print ''
print 'CPU Busy...........: ' + convert(varchar(30),@@CPU_BUSY/1000)
print 'CPU Idle...........: ' + convert(varchar(30),@@IDLE/1000)
print 'IO Busy............: ' + convert(varchar(30),@@IO_BUSY/1000)
print 'Packets received...: ' + convert(varchar(30),@@PACK_RECEIVED)
print 'Packets sent.......: ' + convert(varchar(30),@@PACK_SENT)
print 'Packets w errors...: ' + convert(varchar(30),@@PACKET_ERRORS)
print 'TimeTicks..........: ' + convert(varchar(30),@@TIMETICKS)
print 'IO Errors..........: ' + convert(varchar(30),@@TOTAL_ERRORS)
print 'Total Read.........: ' + convert(varchar(30),@@TOTAL_READ)
print 'Total Write.........: ' + convert(varchar(30),@@TOTAL_WRITE)
go
----------------------------------------------------------------------------------------------------------
print ''
print '2. Server Parameters'
print '*************************'
print ''
--exec sp_configure 'show advanced options',1
exec sp_configure
go
----------------------------------------------------------------------------------------------------------
print ''
print '3. Databases parameters'
print '***************************'
print ''
exec sp_helpdb
go
SELECT LEFT(name,30) AS DB,
SUBSTRING(CASE status & 1 WHEN 0 THEN '' ELSE ',autoclose' END +
CASE status & 4 WHEN 0 THEN '' ELSE ',select into/bulk copy' END +
CASE status & 8 WHEN 0 THEN '' ELSE ',trunc. log on chkpt' END +
CASE status & 16 WHEN 0 THEN '' ELSE ',torn page detection' END +
CASE status & 32 WHEN 0 THEN '' ELSE ',loading' END +
CASE status & 64 WHEN 0 THEN '' ELSE ',pre-recovery' END +
CASE status & 128 WHEN 0 THEN '' ELSE ',recovering' END +
CASE status & 256 WHEN 0 THEN '' ELSE ',not recovered' END +
CASE status & 512 WHEN 0 THEN '' ELSE ',offline' END +
CASE status & 1024 WHEN 0 THEN '' ELSE ',read only' END +
CASE status & 2048 WHEN 0 THEN '' ELSE ',dbo USE only' END +
CASE status & 4096 WHEN 0 THEN '' ELSE ',single user' END +
CASE status & 32768 WHEN 0 THEN '' ELSE ',emergency mode' END +
CASE status & 4194304 WHEN 0 THEN '' ELSE ',autoshrink' END +
CASE status & 1073741824 WHEN 0 THEN '' ELSE ',cleanly shutdown' END +
CASE status2 & 16384 WHEN 0 THEN '' ELSE ',ANSI NULL default' END +
CASE status2 & 65536 WHEN 0 THEN '' ELSE ',concat NULL yields NULL' END +
CASE status2 & 131072 WHEN 0 THEN '' ELSE ',recursive triggers' END +
CASE status2 & 1048576 WHEN 0 THEN '' ELSE ',default TO local cursor' END +
CASE status2 & 8388608 WHEN 0 THEN '' ELSE ',quoted identifier' END +
CASE status2 & 33554432 WHEN 0 THEN '' ELSE ',cursor CLOSE on commit' END +
CASE status2 & 67108864 WHEN 0 THEN '' ELSE ',ANSI NULLs' END +
CASE status2 & 268435456 WHEN 0 THEN '' ELSE ',ANSI warnings' END +
CASE status2 & 536870912 WHEN 0 THEN '' ELSE ',full text enabled' END,
2,8000) AS Descr
FROM master..sysdatabases
go
----------------------------------------------------------------------------------------------------------
print ''
print '4. LOG utilization'
print '****************************'
print ''
dbcc sqlperf(logspace)
go
----------------------------------------------------------------------------------------------------------
print ''
print '5. Datafiles list'
print '***********************'
print ''
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TempForFileStats '))
DROP TABLE #TempForFileStats
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TempForDataFile'))
DROP TABLE #TempForDataFile
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TempForLogFile'))
DROP TABLE #TempForLogFile
DECLARE @DBName nvarchar(100)
DECLARE @SQLString nvarchar (4000)
DECLARE c_db CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE status&512 = 0
CREATE TABLE #TempForFileStats([Server Name] nvarchar(40),
[Database Name] nvarchar(100),
[File Name] nvarchar(128),
[Usage Type] varchar (6),
[Size (MB)] real,
[Space Used (MB)] real,
[MaxSize (MB)] real,
[Next Allocation (MB)] real,
[Growth Type] varchar (12),
[File Id] smallint,
[Group Id] smallint,
[Physical File] nvarchar (260),
[Date Checked] datetime)
CREATE TABLE #TempForDataFile ([File Id] smallint,
[Group Id] smallint,
[Total Extents] int,
[Used Extents] int,
[File Name] nvarchar(128),
[Physical File] nvarchar(260))
CREATE TABLE #TempForLogFile ([File Id] int,
[Size (Bytes)] real,
[Start Offset] varchar(30),
[FSeqNo] int,
[Status] int,
[Parity] smallint,
[CreateTime] varchar(30))
OPEN c_db
FETCH NEXT FROM c_db INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString = 'SELECT @@SERVERNAME as ''ServerName'', ' + -- changed by seraj : *8 to *8.000000000
'''' + @DBName + '''' + ' as ''Database'', ' +
' f.name, ' +
' CASE ' +
' WHEN (64 & f.status) = 64 THEN ''Log'' ' +
' ELSE ''Data'' ' +
' END as ''Usage Type'', ' +
' f.size*8.00000000/1024.00 as ''Size (MB)'', ' +
' NULL as ''Space Used (MB)'', ' +
' CASE f.maxsize ' +
' WHEN -1 THEN -1 ' +
' WHEN 0 THEN f.size*8.00000000/1024.00 ' +
' ELSE f.maxsize*8.00000000/1024.00 ' +
' END as ''Max Size (MB)'', ' +
' CASE ' +
' WHEN (1048576&f.status) = 1048576 THEN (growth/100.00)*(f.size*8.00000000/1024.00) ' +
' WHEN f.growth =0 THEN 0 ' +
' ELSE f.growth*8.00000000/1024.00 ' +
' END as ''Next Allocation (MB)'', ' +
' CASE ' +
' WHEN (1048576&f.status) = 1048576 THEN ''Percentage'' ' +
' ELSE ''Pages'' ' +
' END as ''Usage Type'', ' +
' f.fileid, ' +
' f.groupid, ' +
' filename, ' +
' getdate() ' +
' FROM [' + @DBName + '].dbo.sysfiles f' -- Seraj Alam added []
INSERT #TempForFileStats
EXECUTE(@SQLString)
------------------------------------------------------------------------
SET @SQLString = 'USE [' + @DBName + '] DBCC SHOWFILESTATS' -- seraj alam added []
INSERT #TempForDataFile
EXECUTE(@SQLString)
--
UPDATE #TempForFileStats
SET [Space Used (MB)] = s.[Used Extents]*64/1024.00
FROM #TempForFileStats f,
#TempForDataFile s
WHERE f.[File Id] = s.[File Id]
AND f.[Group Id] = s.[Group Id]
AND f.[Database Name] = @DBName
--
TRUNCATE TABLE #TempForDataFile
-------------------------------------------------------------------------
SET @SQLString = 'USE [' + @DBName + '] DBCC LOGINFO' -- seraj alam added []
INSERT #TempForLogFile
EXECUTE(@SQLString)
--
UPDATE #TempForFileStats
SET [Space Used (MB)] = (SELECT (MIN(l.[Start Offset]) +
SUM(CASE
WHEN l.Status <> 0 THEN l.[Size (Bytes)]
ELSE 0
END))/1048576.00
FROM #TempForLogFile l
WHERE l.[File Id] = f.[File Id])
FROM #TempForFileStats f
WHERE f.[Database Name] = @DBName
AND f.[Usage Type] = 'Log'
--
TRUNCATE TABLE #TempForLogFile
-------------------------------------------------------------------------
FETCH NEXT FROM c_db INTO @DBName
END
DEALLOCATE c_db
SELECT * FROM #TempForFileStats
------------
DROP TABLE #TempForFileStats
DROP TABLE #TempForDataFile
DROP TABLE #TempForLogFile
go
----------------------------------------------------------------------------------------------------------
print ''
print '6. IO per datafile'
print '******************'
print ''
use tempdb
go
if exists (select [id] from sysobjects where [id] = OBJECT_ID ('#TBL_DATABASEFILES'))
DROP TABLE #TBL_DATABASEFILES
if exists (select [id] from sysobjects where [id] = OBJECT_ID ('#TBL_FILESTATISTICS'))
DROP TABLE #TBL_FILESTATISTICS
DECLARE @INT_LOOPCOUNTER INTEGER
DECLARE @INT_MAXCOUNTER INTEGER
DECLARE @INT_DBID INTEGER
DECLARE @INT_FILEID INTEGER
DECLARE @SNM_DATABASENAME SYSNAME
DECLARE @SNM_FILENAME SYSNAME
DECLARE @NVC_EXECUTESTRING NVARCHAR(4000)
DECLARE @MTB_DATABASES TABLE (
ID INT IDENTITY,
DBID INT,
DBNAME SYSNAME )
CREATE TABLE #TBL_DATABASEFILES (
ID INT IDENTITY,
DBID INT,
FILEID INT,
FILENAME SYSNAME,
FILENAME1 varchar(600),
DATABASENAME SYSNAME)
INSERT INTO @MTB_DATABASES (DBID,DBNAME) SELECT DBID,NAME FROM MASTER.DBO.SYSDATABASES ORDER BY DBID
SET @INT_LOOPCOUNTER = 1
SELECT @INT_MAXCOUNTER=MAX(ID) FROM @MTB_DATABASES
WHILE @INT_LOOPCOUNTER <= @INT_MAXCOUNTER
BEGIN
SELECT @INT_DBID = DBID,@SNM_DATABASENAME=DBNAME FROM @MTB_DATABASES WHERE ID = @INT_LOOPCOUNTER
SET @NVC_EXECUTESTRING = 'INSERT INTO #TBL_DATABASEFILES(DBID,FILEID,FILENAME, FILENAME1,DATABASENAME) SELECT '+STR(@INT_DBID)+' AS DBID,FILEID,NAME AS FILENAME, FILENAME AS FILENAME1,'''+@SNM_DATABASENAME+''' AS DATABASENAME FROM ['+@SNM_DATABASENAME+'].DBO.SYSFILES'
EXEC SP_EXECUTESQL @NVC_EXECUTESTRING
SET @INT_LOOPCOUNTER = @INT_LOOPCOUNTER + 1
END
--'OK WE NOW HAVE ALL THE DATABASES AND FILENAMES ETC....
CREATE TABLE #TBL_FILESTATISTICS (
ID INT IDENTITY,
DBID INT,
FILEID INT,
DATABASENAME SYSNAME,
FILENAME SYSNAME,
SAMPLETIME DATETIME,
NUMBERREADS BIGINT,
NUMBERWRITES BIGINT,
BYTESREAD BIGINT,
BYTESWRITTEN BIGINT,
IOSTALLMS BIGINT)
SELECT @INT_MAXCOUNTER=MAX(ID) FROM #TBL_DATABASEFILES
SET @INT_LOOPCOUNTER = 1
WHILE @INT_LOOPCOUNTER <= @INT_MAXCOUNTER
BEGIN
SELECT @INT_DBID = DBID,@INT_FILEID=FILEID,@SNM_DATABASENAME=DATABASENAME,@SNM_FILENAME=FILENAME FROM #TBL_DATABASEFILES WHERE ID = @INT_LOOPCOUNTER
INSERT INTO #TBL_FILESTATISTICS(DBID,FILEID,SAMPLETIME,NUMBERREADS,NUMBERWRITES,BYTESREAD,BYTESWRITTEN,IOSTALLMS,DATABASENAME,FILENAME)
SELECT DBID,FILEID,GETDATE(),NUMBERREADS,NUMBERWRITES,BYTESREAD,BYTESWRITTEN,IOSTALLMS,@SNM_DATABASENAME AS DATABASENAME,@SNM_FILENAME AS FILENAME FROM :: FN_VIRTUALFILESTATS(@INT_DBID,@INT_FILEID)
SET @INT_LOOPCOUNTER = @INT_LOOPCOUNTER + 1
END
select * from #TBL_FILESTATISTICS
drop table #TBL_DATABASEFILES
drop table #TBL_FILESTATISTICS
go
---------------------------------------------------------------------------------------
print ''
print '7. List of last backup full''s'
print '*************************************'
print ''
select SUBSTRING(s.name,1,40) AS 'Database',
CAST(b.backup_start_date AS char(11)) AS 'Backup Date ',
CASE WHEN b.backup_start_date > DATEADD(dd,-1,getdate())
THEN 'Backup is current within a day'
WHEN b.backup_start_date > DATEADD(dd,-7,getdate())
THEN 'Backup is current within a week'
ELSE '*****CHECK BACKUP!!!*****'
END
AS 'Comment'
from master..sysdatabases s
LEFT OUTER JOIN msdb..backupset b
ON s.name = b.database_name
AND b.backup_start_date = (SELECT MAX(backup_start_date)
FROM msdb..backupset
WHERE database_name = b.database_name
AND type = 'D') -- full database backups only, not log backups
WHERE s.name <> 'tempdb'
ORDER BY s.name
go
----------------------------------------------------------------------------------------------------------
print ''
print '8. List of logins'
print '********************'
print ''
exec sp_helplogins
go
----------------------------------------------------------------------------------------------------------
print ''
print '9. List of users per role'
print '*******************************'
print ''
exec sp_helpsrvrolemember
go
----------------------------------------------------------------------------------------------------------
print ''
print '10.List of special users per database'
print '*************************************'
print ''
declare @name sysname,
@SQL nvarchar(600)
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#tmpTable'))
drop table #tmpTable
CREATE TABLE #tmpTable (
[DATABASE_NAME] sysname NOT NULL ,
[USER_NAME] sysname NOT NULL,
[ROLE_NAME] sysname NOT NULL)
declare c1 cursor for
select name from master.dbo.sysdatabases
open c1
fetch c1 into @name
while @@fetch_status >= 0
begin
select @SQL =
'insert into #tmpTable
select N'''+ @name + ''', a.name, c.name
from ' + QuoteName(@name) + '.dbo.sysusers a
join ' + QuoteName(@name) + '.dbo.sysmembers b on b.memberuid = a.uid
join ' + QuoteName(@name) + '.dbo.sysusers c on c.uid = b.groupuid
where a.name != ''dbo'''
/* Insert row for each database */
execute (@SQL)
fetch c1 into @name
end
close c1
deallocate c1
select * from #tmpTable
drop table #tmpTable
go
----------------------------------------------------------------------------------------------------------
print ''
print '11. Information about remote servers '
print '*****************************************'
print ''
Print 'Linked Servers'
print ''
exec sp_linkedservers
print 'linked Server login mappings'
print ''
exec sp_helplinkedsrvlogin
print 'Remote Logins'
print ''
--exec sp_helpremotelogin
go
----------------------------------------------------------------------------------------------------------
print ''
print '12. List of jobs '
print '*******************'
print ''
exec msdb..sp_help_job
go
----------------------------------------------------------------------------------------------------------
print ''
print '13. Cache Hit Ratio '
print '*******************'
print ''
select distinct counter_name,
(select isnull(sum(convert(dec(15,0),B.cntr_value)),0)
from master..sysperfinfo as B (nolock)
where Lower(B.counter_name) like '%hit ratio%'
and A.counter_name = B.counter_name) as CurrHit,
(select isnull(sum(convert(dec(15,0),B.cntr_value)),0)
from master..sysperfinfo as B (nolock)
where Lower(B.counter_name) like '%hit ratio base%'
and lower(B.counter_name) = (lower(ltrim(rtrim(A.counter_name))) + ' base')) as CurrBase,
(select isnull(sum(convert(dec(15,0),B.cntr_value)),0)
from master..sysperfinfo as B (nolock)
where Lower(B.counter_name) like '%hit ratio%'
and A.counter_name = B.counter_name) /
(select isnull(sum(convert(dec(15,0),B.cntr_value)),0)
from master..sysperfinfo as B (nolock)
where Lower(B.counter_name) like '%hit ratio base%'
and lower(B.counter_name) = (lower(ltrim(rtrim(A.counter_name))) + ' base')) as HitRatio
from master..sysperfinfo as A (nolock)
where Lower(A.counter_name) like '%hit ratio%'
and Lower(A.counter_name) not like '%hit ratio base%'
-- Audit list as a double verification
select counter_name,isnull(sum(convert(dec(15,0),cntr_value)),0) as Value
from master..sysperfinfo (nolock)
where Lower(counter_name) like '%hit ratio%'
or Lower(counter_name) like '%hit ratio base%'
group by counter_name
go
----------------------------------------------------------------------------------------------------------
print ''
print '14. SP_WHO '
print '***********'
print ''
exec sp_who
exec sp_who2
go
----------------------------------------------------------------------------------------------------------
print ''
print '14. SP_LOCKS '
print '***********'
print ''
exec sp_lock
go
set nocount on
use msdb
go
Print ''
Print 'Linked Servers'
print '***********'
print ''
exec sp_linkedservers
Print ''
print '15. Maintenance Plans'
print '***********'
print ''
go
select @@servername "ServerName", smp.plan_id, plan_name, owner, smpd.database_name, smpj.job_id, sj.name, sjs.name "Job Part Name", sjs.enabled "Job Enabled", sjs.freq_type "Frequency", sjs.active_start_time "Job Start Time"
from sysdbmaintplans smp, sysdbmaintplan_databases smpd, sysdbmaintplan_jobs smpj, sysjobs sj, sysjobschedules sjs
where smp.plan_id = smpd.plan_id and smp.plan_id = smpj.plan_id and sj.job_id = smpj.job_id and sj.job_id = sjs.job_id
go
Print ''
print 'Maintenance Plan 2005'
SELECT sv.name AS [Name], sv.job_id AS [JobID],sv.originating_server AS [OriginatingServer],
CAST(sv.enabled AS bit) AS [IsEnabled],
ISNULL(sv.description,N'') AS [Description],sv.start_step_id AS [StartStepID],ISNULL(suser_sname(sv.owner_sid), N'') AS [OwnerLoginName],
sv.notify_level_eventlog AS [EventLogLevel],sv.notify_level_email AS [EmailLevel],sv.notify_level_netsend AS [NetSendLevel],
sv.notify_level_page AS [PageLevel],sv.delete_level AS [DeleteLevel],sv.date_created AS [DateCreated],sv.date_modified AS [DateLastModified],
sv.version_number AS [VersionNumber]FROM msdb.dbo.sysjobs_view AS sv
go
set dateformat dmy
use msdb
go
select y.Server_name, y.database_name, y.database_creation_date, y.backup_start_Date, y.backup_finish_Date , y.backup_file, y.backup_size
from (
select c.Server_name, c.database_name, c.database_creation_date, c.backup_start_Date, c.backup_finish_Date , a.physical_device_name as backup_file, c.backup_size
from Backupmediafamily a inner join backupset c on a.media_set_id=c.media_set_id
inner join backupfile b on
c.backup_set_id=b.backup_set_id and b.file_type='D'
) y
inner join
(select distinct c.database_name, c.backup_finish_Date as backup_finish_date from Backupmediafamily a inner join backupset c on a.media_set_id=c.media_set_id
inner join backupfile b on
c.backup_set_id=b.backup_set_id and b.file_type='D' where c.backup_finish_date > getdate()-10
) z
on y.database_name=z.database_name and y.backup_finish_Date=z.backup_finish_date
Print ''
Print '16. OTHER'
PRINT 'DTS Packages'
print '***********'
print ''
go
select @@servername "ServerName", sd.name, sd.id, sd.categoryid, sdc.name "Category", sd.description, owner, createdate
from sysdtspackages sd, sysdtscategories sdc
where sd.categoryid = sdc.id
go
exec sp_MSgetalertinfo
print ''
print 'Alerts settings'
print '***********'
print ''
exec msdb..sp_help_alert
Print ''
Print 'Operators'
print '***********'
print ''
EXECUTE sp_help_operator
print ''
print 'SQL Mails'
EXECUTE master.dbo.xp_sqlagent_notify N'M',null,null,null,N'E'
print ''
Print 'SQL Agent Propertes'
EXECUTE msdb.dbo.sp_get_sqlagent_properties
print ''
print 'Startup Parameters 01'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg0'
print ''
print 'Startup Parameters 02'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg1'
print ''
print 'Startup Parameters 03'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg2'
print ''
print 'Startup Parameters 04'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg3'
-- Though there is very little chance of more than 4 parameters, still it is good to check.
print ''
print 'Startup Parameters 05'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg4'
print ''
print 'Startup Parameters 06'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg5'
print ''
--exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath'
print ''
print 'Login Mode'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', 'LoginMode'
go
print ''
Print 'Audit Level'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel'
Print ''
Print 'Clustered?'
select convert(int, serverproperty(N'isclustered'))
go
Print ''
Print 'Is Mapi set?'
DECLARE @retval varchar(255) EXECUTE master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Windows Messaging Subsystem','MAPIX',@retval OUTPUT SELECT @retval
go
Print ''
Print 'Mail Account'
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'MailAccountName'
Print ''
set nocount off
print '******************************************************************'
print ' FIM '
print '******************************************************************'
----------------------------------------------------------------------------------------------------------
set nocount off
-----------------------------
print ''
print 'deny logins'
use master
go
select name,loginname,denylogin,createdate,hasaccess from syslogins where denylogin ='1'
go
Below is the list of command that we
can use the verify the backup file.
Information for media set of backup -> restore labelonly from disk = 'Path' (details about the media set on which backup is taken)
Information to check the backup set is valid -> restore verify only from disk = 'path' (to check that my backup is valid)
Information to check the details of the files in backup - > restore filelistonly from disk = 'path' (information about database files, size and files detail)
Information to check the details of the backup file -> restore headeronly from disk = 'path'( details of backup,who has taken the backup,backup of which database, collation of database, compressed, backup of which server)
Information for media set of backup -> restore labelonly from disk = 'Path' (details about the media set on which backup is taken)
Information to check the backup set is valid -> restore verify only from disk = 'path' (to check that my backup is valid)
Information to check the details of the files in backup - > restore filelistonly from disk = 'path' (information about database files, size and files detail)
Information to check the details of the backup file -> restore headeronly from disk = 'path'( details of backup,who has taken the backup,backup of which database, collation of database, compressed, backup of which server)
create proc LoginsAndUsers
as
begin
create table #systemdbs (name sysname)
insert #systemdbs
select 'master'
union select 'msdb'
union select 'model'
union select 'tempdb'
create table #dbusers (DatabaseName sysname, UserName sysname, GroupName sysname, LoginName sysname null,
DefDBName sysname null, DefSchemaName sysname null, UserID int, SID varbinary(1000))
create table #dbusersbuffer ( UserName sysname, GroupName sysname, LoginName sysname null,
DefDBName sysname null, DefSchemaName sysname null, UserID int, SID varbinary(1000))
declare @Command nvarchar(1012)
set @Command= 'if not exists (select * from #systemdbs where name = ''?'') begin '+char(13)+
+'use ? ; insert #dbusersbuffer exec sp_helpuser'+char(13)
+'insert #dbusers select ''?'', * from #dbusersbuffer'+char(13)
+'truncate table #dbusersbuffer'+char(13)
+'end'
print @Command
exec sp_MSforeachdb @command1 = @Command
select databasename,username,groupname,loginname,defdbname from #dbusers where username != 'dbo' and groupname !='public' order by DatabaseName
drop table #dbusers
drop table #dbusersbuffer
drop table #systemdbs
end
go
exec LoginsAndUsers
drop proc loginsandusers
create proc LoginsAndUsers
as
begin
create table #systemdbs (name sysname)
insert #systemdbs
select 'master'
union select 'msdb'
union select 'model'
union select 'tempdb'
create table #dbusers (DatabaseName sysname, UserName sysname, GroupName
sysname, LoginName sysname null,DefDBName sysname null, UserID int, SID varbinary(1000))
create table #dbusersbuffer ( UserName sysname, GroupName sysname, LoginName
sysname null,DefDBName sysname null, UserID int, SID varbinary(1000))
declare @Command nvarchar(1012)
set @Command= 'if not exists (select * from #systemdbs where name = ''?'') begin '+char(13)+
+'use [?] ; insert #dbusersbuffer exec sp_helpuser'+char(13)
+'insert #dbusers select ''?'', * from #dbusersbuffer'+char(13)
+'truncate table #dbusersbuffer'+char(13)
+'end'
print @Command
exec sp_MSforeachdb @command1 = @Command
select databasename,username,groupname,loginname,defdbname from #dbusers where username != 'dbo' and groupname !='public' order by DatabaseName
drop table #dbusers
drop table #dbusersbuffer
drop table #systemdbs
end
go
exec LoginsAndUsers
drop proc loginsandusers
The below script will assist you to find username and corresponding loginname in SQL SERVER 2005/2008/2008R2
create proc LoginsAndUsers
as
begin
create table #systemdbs (name sysname)
insert #systemdbs
select 'tempdb'
create table #dbusers (DatabaseName sysname, UserName sysname, GroupName sysname, LoginName sysname null,
DefDBName sysname null, DefSchemaName sysname null, UserID int, SID varbinary(1000))
create table #dbusersbuffer ( UserName sysname, GroupName sysname, LoginName sysname null,
DefDBName sysname null, DefSchemaName sysname null, UserID int, SID varbinary(1000))
declare @Command nvarchar(1012)
set @Command= 'if not exists (select * from #systemdbs where name = ''?'') begin '+char(13)+
+'use ? ; insert #dbusersbuffer exec sp_helpuser'+char(13)
+'insert #dbusers select ''?'', * from #dbusersbuffer'+char(13)
+'truncate table #dbusersbuffer'+char(13)
+'end'
print @Command
exec sp_MSforeachdb @command1 = @Command
select databasename,username,groupname,loginname,defdbname from #dbusers where groupname !='public' order by DatabaseName
drop table #dbusers
drop table #dbusersbuffer
drop table #systemdbs
end
go
exec LoginsAndUsers
drop proc loginsandusers
The below script will list down all
the username and its associated loginname in SQL Server 2000.
create proc LoginsAndUsers
as
begin
create table #systemdbs (name sysname)
insert #systemdbs
select 'tempdb'
create table #dbusers (DatabaseName sysname, UserName sysname, GroupName
sysname, LoginName sysname null,DefDBName sysname null, UserID int, SID varbinary(1000))
create table #dbusersbuffer ( UserName sysname, GroupName sysname, LoginName
sysname null,DefDBName sysname null, UserID int, SID varbinary(1000))
declare @Command nvarchar(1012)
set @Command= 'if not exists (select * from #systemdbs where name = ''?'') begin '+char(13)+
+'use [?] ; insert #dbusersbuffer exec sp_helpuser'+char(13)
+'insert #dbusers select ''?'', * from #dbusersbuffer'+char(13)
+'truncate table #dbusersbuffer'+char(13)
+'end'
print @Command
exec sp_MSforeachdb @command1 = @Command
select databasename,username,groupname,loginname,defdbname from #dbusers where username != 'dbo' and groupname !='public' order by DatabaseName
drop table #dbusers
drop table #dbusersbuffer
drop table #systemdbs
end
go
exec LoginsAndUsers
drop proc loginsandusers
create proc LoginsAndUsers
as
begin
create table #systemdbs (name sysname)
insert #systemdbs
select 'tempdb'
create table #dbusers (DatabaseName sysname, UserName sysname, GroupName
sysname, LoginName sysname null,DefDBName sysname null, UserID int, SID varbinary(1000))
create table #dbusersbuffer ( UserName sysname, GroupName sysname, LoginName
sysname null,DefDBName sysname null, UserID int, SID varbinary(1000))
declare @Command nvarchar(1012)
set @Command= 'if not exists (select * from #systemdbs where name = ''?'') begin '+char(13)+
+'use [?] ; insert #dbusersbuffer exec sp_helpuser'+char(13)
+'insert #dbusers select ''?'', * from #dbusersbuffer'+char(13)
+'truncate table #dbusersbuffer'+char(13)
+'end'
print @Command
exec sp_MSforeachdb @command1 = @Command
select databasename,username,groupname,loginname,defdbname from #dbusers where username != 'dbo' and groupname !='public' order by DatabaseName
drop table #dbusers
drop table #dbusersbuffer
drop table #systemdbs
end
go
exec LoginsAndUsers
drop proc loginsandusers
The below script will give you the
total used size of all database inside SQL Server.
create table #dbusedsize ( used_mb int)
DECLARE @SQL VARCHAR(1000)
DECLARE @DB sysname
DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR
SELECT [name]
FROM master..sysdatabases
WHERE DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
OPEN curDB
FETCH NEXT FROM curDB INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'USE [' + @DB +']' + CHAR(13) + 'INSERT INTO #dbusedsize( used_mb )
select FILEPROPERTY([name], ''SpaceUsed'')/128
from sysfiles sf left outer join sysfilegroups sfg on sf.groupid=sfg.groupid
order by 1' + CHAR(13)
Exec (@SQL)
FETCH NEXT FROM curDB INTO @DB
END
CLOSE curDB
DEALLOCATE curDB
select SUM(used_mb) from #dbusedsize
drop table #dbusedsize
create table #dbusedsize ( used_mb int)
DECLARE @SQL VARCHAR(1000)
DECLARE @DB sysname
DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR
SELECT [name]
FROM master..sysdatabases
WHERE DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
OPEN curDB
FETCH NEXT FROM curDB INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'USE [' + @DB +']' + CHAR(13) + 'INSERT INTO #dbusedsize( used_mb )
select FILEPROPERTY([name], ''SpaceUsed'')/128
from sysfiles sf left outer join sysfilegroups sfg on sf.groupid=sfg.groupid
order by 1' + CHAR(13)
Exec (@SQL)
FETCH NEXT FROM curDB INTO @DB
END
CLOSE curDB
DEALLOCATE curDB
select SUM(used_mb) from #dbusedsize
drop table #dbusedsize
Below is the script that will give
you the total size of all the databases present in SQL Server.
create table #dbsize ( size_mb int)
DECLARE @SQL VARCHAR(1000)
DECLARE @DB sysname
DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR
SELECT [name]
FROM master..sysdatabases
WHERE DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
OPEN curDB
FETCH NEXT FROM curDB INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'USE [' + @DB +']' + CHAR(13) + 'INSERT INTO #dbsize( size_mb )
select size/128
from sysfiles sf left outer join sysfilegroups sfg on sf.groupid=sfg.groupid
order by 1' + CHAR(13)
Exec (@SQL)
FETCH NEXT FROM curDB INTO @DB
END
CLOSE curDB
DEALLOCATE curDB
select SUM(size_mb) from #dbsize
drop table #dbsize
create table #dbsize ( size_mb int)
DECLARE @SQL VARCHAR(1000)
DECLARE @DB sysname
DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR
SELECT [name]
FROM master..sysdatabases
WHERE DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
OPEN curDB
FETCH NEXT FROM curDB INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'USE [' + @DB +']' + CHAR(13) + 'INSERT INTO #dbsize( size_mb )
select size/128
from sysfiles sf left outer join sysfilegroups sfg on sf.groupid=sfg.groupid
order by 1' + CHAR(13)
Exec (@SQL)
FETCH NEXT FROM curDB INTO @DB
END
CLOSE curDB
DEALLOCATE curDB
select SUM(size_mb) from #dbsize
drop table #dbsize
Hi Friends,
Once I faced a issue when I was applying SP2 on SQL Server 2008R2. Whenever I clicked the setup icon the black CMD screen runs and disappears. Nothing happened after that and every time I click the setup icon the result is same. Then I thought that SP2 might me corrupt, so i downloaded a new SP2, but it also behaved like the earlier one. After much effort I was able to rectify the problem using the below steps.
The .Net error : the SQLsetlog gives below error
Error: Cannot load .Net security policy file
Error: InitializeSqlSetupCodeGroupCore(32bit) failed
Error: InitializeSqlSetupCodeGroup failed: 0x80004005
Setup closed with exit code: 0x80004005
Ran in cmd : C:\Windows\Microsoft.NET\Framework\v2.0.50727\caspol.exe -machine -reset
if you have 64 bit then use Framework64
No comments:
Post a Comment