|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL Server 2000: Stored Procedure to tell file size & free spaceHello all,
Is there a stored procedure or function that I can run in QA that will tell me the size of each data & log file and free space/space used for each of those files for EVERY database I have on my server? (the equivelent of Right-click DB>All Tasks>Shrink Database...>Files... button in EM -- but will list this data for every file for every DB). Our server is running out of space on the HD and I would like to see a list of all the data & log files so I know which DB's I can shrink to free up some space. -- Thanks for any help anyone can provide, Conan Kelly You could try the undocumented/unsupported sp_MSForEachDB:
EXEC sp_MSForEachDB 'USE ?; EXEC sp_spaceused; -- other queries here'; Show quote "Conan Kelly" <CTBarbarin at msn dot com> wrote in message news:eLy88Pd$GHA.4992@TK2MSFTNGP03.phx.gbl... > Hello all, > > Is there a stored procedure or function that I can run in QA that will > tell me the size of each data & log file and free space/space used for > each of those files for EVERY database I have on my server? (the > equivelent of Right-click DB>All Tasks>Shrink Database...>Files... button > in EM -- but will list this data for every file for every DB). > > Our server is running out of space on the HD and I would like to see a > list of all the data & log files so I know which DB's I can shrink to free > up some space. > > -- > Thanks for any help anyone can provide, > > Conan Kelly > Aaron,
Thank you for the feedback. That worked pretty good, but it returns the info in separate result sets for each DB. I noticed in the SP's definition some comments on returning the info in one result set for all DB's by using the @precommand and @postcommand arguments. I've been trying this, but I'm inexperienced with using SP's. Can you re-write the statement you provided to get one result set for all DB's using the @precomman/@postcommand? Thanks again for all of your help, Conan Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:u5ZuGUd$GHA.3560@TK2MSFTNGP03.phx.gbl... > You could try the undocumented/unsupported sp_MSForEachDB: > > EXEC sp_MSForEachDB 'USE ?; EXEC sp_spaceused; -- other queries here'; > > > > > > > > > "Conan Kelly" <CTBarbarin at msn dot com> wrote in message news:eLy88Pd$GHA.4992@TK2MSFTNGP03.phx.gbl... >> Hello all, >> >> Is there a stored procedure or function that I can run in QA that will tell me the size of each data & log file and free >> space/space used for each of those files for EVERY database I have on my server? (the equivelent of Right-click DB>All >> Tasks>Shrink Database...>Files... button in EM -- but will list this data for every file for every DB). >> >> Our server is running out of space on the HD and I would like to see a list of all the data & log files so I know which DB's I >> can shrink to free up some space. >> >> -- >> Thanks for any help anyone can provide, >> >> Conan Kelly >> > > > That worked pretty good, but it returns the info in separate result sets Unfortunately, since sp_spaceused returns multiple result sets, there is no > for each DB. easy workaround that I know of, other than writing your own version and creating it in each database. I have never used @precommand / @postcommand. A Conan Kelly (CTBarbarin at msn dot com) writes:
> That worked pretty good, but it returns the info in separate result sets Here is a single-row query that gives the pertinent information for> for each DB. > > I noticed in the SP's definition some comments on returning the info in > one result set for all DB's by using the @precommand and @postcommand > arguments. I've been trying this, but I'm inexperienced with using > SP's. > > Can you re-write the statement you provided to get one result set for > all DB's using the @precomman/@postcommand? each database: select i.inuse, f1.datafilesize, f2.logfilesize from (SELECT inuse = sum(reserved) FROM sysindexes WHERE indid in (1,0)) AS i cross join (SELECT datafilesize = SUM(size) FROM sysfiles WHERE fileproperty(name, 'IsLogFile') = 0) AS f1 cross join (SELECT logfilesize = SUM(size) FROM sysfiles WHERE fileproperty(name, 'IsLogFile') = 1) AS f2 inuse is how much of the database file that has been allocated for extents. This query returns the numbers in 8K blocks. You can run this query with sp_MSforeachdb, in this way: INSERT #temp (...) EXEC sp_MSforeachdb '...' INSERT EXEC works where, because while there are multiple result sets they all the same appearance. I did not include information on how much is in use of the log file. I don't think this information is available with a single query on SQL 2000, which I have assumed. If you are using SQL 2005, is another kettle of fish. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Hi Erland
Your SQL 2000 query is only accounting for the table storage structures (indid in (1, 0)). NCIX storage can be a substiantial portion of a database so it be quite a bit more acccurate if it included the size reserved for all NCIXs as well, eg: select i.inuse, f1.datafilesize, f2.logfilesize from (SELECT inuse = sum(reserved) FROM sysindexes WHERE indid < 255) AS i cross join (SELECT datafilesize = SUM(size) FROM sysfiles WHERE fileproperty(name, 'IsLogFile') = 0) AS f1 cross join (SELECT logfilesize = SUM(size) FROM sysfiles WHERE fileproperty(name, 'IsLogFile') = 1) AS f2 Regards, Greg Linwood SQL Server MVP http://blogs.sqlserver.org.au/blogs/greg_linwood Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns986FF2C2773AFYazorman@127.0.0.1... > Conan Kelly (CTBarbarin at msn dot com) writes: >> That worked pretty good, but it returns the info in separate result sets >> for each DB. >> >> I noticed in the SP's definition some comments on returning the info in >> one result set for all DB's by using the @precommand and @postcommand >> arguments. I've been trying this, but I'm inexperienced with using >> SP's. >> >> Can you re-write the statement you provided to get one result set for >> all DB's using the @precomman/@postcommand? > > Here is a single-row query that gives the pertinent information for > each database: > > select i.inuse, f1.datafilesize, f2.logfilesize > from (SELECT inuse = sum(reserved) > FROM sysindexes > WHERE indid in (1,0)) AS i > cross join (SELECT datafilesize = SUM(size) > FROM sysfiles > WHERE fileproperty(name, 'IsLogFile') = 0) AS f1 > cross join (SELECT logfilesize = SUM(size) > FROM sysfiles > WHERE fileproperty(name, 'IsLogFile') = 1) AS f2 > > > inuse is how much of the database file that has been allocated for > extents. This query returns the numbers in 8K blocks. > > You can run this query with sp_MSforeachdb, in this way: > > INSERT #temp (...) > EXEC sp_MSforeachdb '...' > > INSERT EXEC works where, because while there are multiple result sets > they all the same appearance. > > I did not include information on how much is in use of the log file. > I don't think this information is available with a single query on > SQL 2000, which I have assumed. > > If you are using SQL 2005, is another kettle of fish. > > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Greg Linwood (g_linw***@hotmail.com) writes:
> Your SQL 2000 query is only accounting for the table storage structures Yeah, but there is no reason to count the NCIX:s twice. :-) They are > (indid in (1, 0)). NCIX storage can be a substiantial portion of a > database so it be quite a bit more acccurate if it included the size > reserved for all NCIXs as well, eg: > > select i.inuse, f1.datafilesize, f2.logfilesize > from (SELECT inuse = sum(reserved) > FROM sysindexes > WHERE indid < 255) AS i included in the value for indid 0/1. This is an old trap of sysindexes. Books Online says: For indid = 0 or indid = 1, reserved is the count of pages allocated for all indexes and table data. For indid = 255, reserved is a count of the pages allocated for text or image data. Otherwise, it is the count of pages allocated for the index. Also, try this: CREATE TABLE corny (a char(800) NOT NULL, b smallint NOT NULL) go INSERT corny (a, b) SELECT CustomerID, OrderID FROM Northwind..Orders go DBCC UPDATEUSAGE (tempdb, corny) go SELECT reserved FROM sysindexes WHERE id = object_id('corny') AND indid = 0 go CREATE INDEX corny ON corny (a) go SELECT reserved FROM sysindexes WHERE id = object_id('corny') AND indid = 0 go DROP TABLE corny -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics