SQL Server Performance

Query to return number of rows from a dbcc command

Discussion in 'SQL Server 2005 General Developer Questions' started by madduri, Dec 8, 2009.

  1. madduri New Member

    Hi,
    I would like to create a query that runs dbcc loginfo command on all the databases and returns only the no. of rows returned for each database. Is there a way that this could be achieved. I would also want to save the results (database name and no. of rows returned for each database) to a file or table.
    Your help is highly appreciated.
    Thanks,
    Madduri
  2. madduri New Member

    I found exactly what i was looking for. The script below gets the vlfs for all the databases on your server in one hit.
    ref: http://sqlblog.com/blogs/linchi_she...large-number-of-virtual-log-files-part-i.aspxCREATE
    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'SELECT
    * FROM #Resultsdrop
    table #vlfsdrop
    table #Results
  3. FrankKalis Moderator

    Thanks for the feedback! Now maybe others can benefit from this as well.

Share This Page