Server level VLF report

Published 25 July 2007 23:27

I read Tony Rogerson's blog on Virtual Log Files today and it reminded me that i really should knock up a little report to list all databases on a server and the number of VLF's per database. Since I had been busy writing some other Operational reports I was in the right frame of mind so knocked up what you see below.

The Report code

CREATE TABLE #VLFS (fileid int,filesize bigint,startoffset bigint,fseqno bigint,status int,parity int,createlsn varchar(1000))

CREATE TABLE #Results (SRV_Name nvarchar(500),Database_Name nvarchar(500),VLFS INT)

exec master.dbo.sp_msforeachdb

    @command1 = 'USE ? INSERT INTO #VLFS EXEC(''DBCC LOGINFO WITH TABLERESULTS'')',

    @command2 = 'insert into #Results SELECT @@SERVERNAME,''?'',count(*) from #vlfs',

    @command3 = 'truncate table #vlfs'

 

--For this example i`ve just returned the results but you can

--just as easily write the results to a local or central server.

--I write mine to a central server for reporting on.

 
SELECT * FROM #Results

drop table #vlfs

drop table #Results

 

Example output

 Results

For me the beauty of this little report is that i have set it up so that it runs on all 150 of my servers distributed around the world and logs to my central logging server. From here I get one report that tells me the databases and the servers they are hosted on that have excessive VLF's. Having said that in a well managed environment i should never find any databases with lots of VLF's........

You may also be wondering "How many VLF's are too many?". Tony did not cover this but Kimberly Tripp did in a post she put up a few years ago (See point 8) which was when I first learned about VLF's. I don't however think this is a hard and fast number and obviously the performance gains will be less if your not far off this number.

The last thing I am going to mention in this post is part of the code I used in the report. I used the undocumented procedure sp_msforeachdb. This is a great little procedure that will cycle through your databases executing up to 3 commands. To use it, where you would have put a database name you put a '?' and its as simple as that! Incidentally there is also a sp_msforeachtable.

by ACALVETT
Filed under: , ,

Comments

# tonyrogerson said on 26 July 2007 07:39

Very useful Andrew - definitely be using that in the future - thanks.

My entry on VLF came from last weeks user group evening where somebody mentioned it was actually good to have lots of VLF's and use the default autogrowth settings - I just had to show that was wrong.

See you tomorrow evening at the SQLBlogsters meal!

Tony

# GrumpyOldDBA said on 26 July 2007 10:04

Paul Randal covered this in April this year " crucial database main..." , sure you were there Tony ????? It can be quite difficult to bring this figure down on a production database - sometimes the only way is to actually remove the log file / drop the database into simple - not an easy task if your database is large or is almost 7 x 24. Paul indicated any figure over 50 was bad as far as I can remember.