Quickscan on multiple instances - scripting?

Question

Quickscan on multiple instances - scripting?

Joop Gijsbers

SSC Enthusiast

Points: 121
More actions
July 8, 2007 at 12:41 am

#174312

Our team is contacting a new customer with a few hunderd Sql Server 2005 databases on > 100 instances and servers. I am looking for an "easy" way to do a "quickscan" for the intake and i am looking for a way to script that. On Sqlservercentral i found some useful scripts , mostly for performance issues, but i am looking also for a simple way to have the main characteristics/properties of that instance/database:
- authentication mode, recovery mode, last backup, place datafiles, place transaction logfiles, size drives, free space drives, free space in datafiles and logfiles, etc.
I also like to script it in a way that i can run the script against a text/csv file with instance/server name. Maybe in combination with Perl.
Has someone some good suggestions? Maybe i overlooked some scripts in servercentral, there are a lot of scripts there :-).
Joop

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Joop Gijsbers SSC Enthusiast Points: 121 More actions · Answer 1

I found already where i was looking for ... and even much more:

the revised version of full server documentation. I noticed already the old version, but this version has more data and also a way to run it against a list of servernames in a batch file.

Thanks Ken Hui.

Joop

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Could you take 2 minutes of your time and post your findings here? It may help someone else in the future who finds this thread in a search.

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 3

declare @servername varchar(100)

declare @dbname varchar(100)

declare @dbownername varchar(100)

declare @crtdate varchar(100)

declare @totexts int

declare @usedexts int

declare @filename varchar(200)

declare @datsumtot numeric(10,2)

declare @datsumused numeric(10,2)

declare @perused numeric(10,2)

declare @logsize numeric(10,2)

declare @logused numeric(10,2)

declare @collation sql_variant

declare @model sql_variant

declare @fullbackup varchar(30)

declare @diffbackup varchar(30)

declare @logbackup varchar(30)

declare @filebackup varchar(30)

declare @numusers int

declare @rcnt int

SET NOCOUNT ON

create table #filestats(fileid varchar(10),filegroup varchar(10),totextents int,usedextents int,name varchar(100), filename varchar(100))

create table #logdetails(databasename varchar(100), logsize numeric(10,4),logused numeric(10,4),status bit)

set @dbname = 'sugesh1'

select @rcnt=count(*) from sysdatabases where name like @dbname

if @rcnt>0

begin

select @servername=@@servername

select @collation=databasepropertyex(@dbname,'collation')

select @model=databasepropertyex(@dbname,'Recovery')

select @dbownername=suser_sname(sid), @crtdate=convert(varchar(15),crdate,101) from master..sysdatabases where name ='tempdb'

insert into #filestats exec('use '+@dbname+' dbcc showfilestats')

insert into #logdetails exec('dbcc sqlperf(logspace)')

Print '*****************************SERVER AND DATABASE DETAILS*****************************'

print 'Server Name :'+ @servername

print 'Database Name :'+ @dbname

print 'Database Owner Name :'+ @dbownername

print 'Database Creation Date :'+ @crtdate

print 'Database COllation Name:'+ convert(varchar(100),@collation)

print 'Database Recovery Model:'+ convert(varchar(10),@model)

Print '*****************************DATABASE DATA DETAILS*****************************'

Print 'File StatisticsTotal Size) (Used Size) (FileName) For Data Files'

declare fil_cursor cursor for

select (totextents*8*8)/1024,(usedextents*8*8)/1024,filename from #filestats

OPEN fil_cursor

FETCH NEXT FROM fil_cursor into @totexts,@usedexts,@filename

while @@fetch_status=0

begin

print ' '+convert(varchar(10),@Totexts)+' '+convert(varchar(10),@Usedexts)+' '+@filename

FETCH NEXT FROM fil_cursor into @totexts,@usedexts,@filename

end

close fil_cursor

deallocate fil_cursor

Print 'File % StatisticsTotal Size) (Used Size) (%Used) For Data Files'

select @datsumtot=(sum(convert(numeric(10,4),totextents))*8*8)/1024,

@datsumused=(sum(convert(numeric(10,4),usedextents))*8*8)/1024,

@perused=(@datsumused/@datsumtot)*100 from #filestats

Print ' '+convert(varchar(10),@datsumtot)+' '+convert(varchar(10),@datsumused)+' '+convert(varchar(10),@perused)

Print '*****************************DATABASE LOG DETAILS*****************************'

Print 'File % StatisticsTotal Size) (%Used)'

select @logsize=logsize,@logused=logused from #logdetails where databasename = @dbname

Print ' '+convert(varchar(15),@logsize)+' '+convert(varchar(15),@logused)

select @fullbackup=isnull(convert(varchar(30),max(backup_finish_date),101),'No Full backup Taken') from msdb..backupset where database_name = @dbname and type ='D'

select @diffbackup=isnull(convert(varchar(30),max(backup_finish_date),101),'No Diff Backup Taken') from msdb..backupset where database_name = @dbname and type ='I'

select @logbackup=isnull(convert(varchar(30),max(backup_finish_date),101),'No Log Backup Taken') from msdb..backupset where database_name = @dbname and type ='L'

select @filebackup=isnull(convert(varchar(30),max(backup_finish_date),101),'No Filegroup Backup Taken') from msdb..backupset where database_name = @dbname and type ='F'

Print '*****************************DATABASE BACKUP DETAILS*****************************'

Print 'Full Backup Detail for Database :'+@fullbackup

Print 'Diff Backup Detail for Database :'+@diffbackup

Print 'Log Backup Detail for Database :'+@logbackup

Print 'File Group Backup Detail for Database:'+@filebackup

select @numusers = count(*) from sysusers where islogin = 1 and sid is not null

Print '*****************************DATABASE USER DETAILS*****************************'

Print 'Number of Loginable Users:'+convert(varchar(10),@numusers)

drop table #filestats

drop table #logdetails

end

else

begin

Print 'The database name you have specified is not valid for the Server'

end

Check if this script helps you.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Omri Bahat-433962 SSC Eights! Points: 901 More actions · Answer 4

In case it helps, the company I work for (SQL Farms) has a tool with which you can put all your scripts in a project and execute the project against all databases/servers in parallel to get the needed data, and get a single result set from all databases / servers in response to your queries and data collection) - you can also tell the tool to pick up the server/database names from a SQL table so you do no need to register servers.

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 5

I tried the tool it's okay but with more features it will be very good as a single point to be used.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Joop Gijsbers SSC Enthusiast Points: 121 More actions · Answer 6

Sorry, i thought it was clear. i used this script form sqlservercentral:

http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1832

And it helped me a lot.

--

Joop

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 7

ok thanks

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com