Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Where Are My Files?

In version 7 of SQL Server, each database contains a table called sysfiles that stores a row for each file used by the database. The article describes how to obtain a list of all files associated with SQL Server?

QL Server version 7 is an exciting new product. And although it is called "version 7," so much of it has changed that I consider it a new product rather than an upgrade from version 6.5.

One of the many improvements is how databases are stored. In version 6.5, databases were created on devices. Although for most purposes these devices were simply files, you first had to create the devices before creating a database. In version 7, this artificial abstraction from the file system has been eliminated. Databases are created directly on operating system files.

This architecture allows databases to grow automatically. In version 6.5, you had to allocate space manually. If your DBA didn't watch carefully, a database could run out of space even though the disk had room. However, files always grow automatically, without intervention, so in version 7, databases can also expand as needed without the DBA doing anything.

Where is the information about the files used for databases stored? In version 6.5, a table in master called sysdevices contained a row for each device used by SQL Server. In version 7, each database contains a table called sysfiles that stores a row for each file used by the database. So how does one obtain a list of all files associated with SQL Server?

Here are the steps:

  1. Create a cursor on the names of the databases.
  2. For each row, use dynamic SQL to retrieve the information from the sysfiles of that database and place it in a temporary table.
  3. Interpret the results and display for output.
Feel free to add to the following code or suggest improvements. If any of you are already using version 7, please let me know what you think of it.

set nocount on go DECLARE dbcursor CURSOR FOR select name from master..sysdatabases FOR READ ONLY declare @dbname varchar(50), @sqlstmt varchar(200) create table #filelist ( dbname varchar(128), filename varchar(128), physname varchar(260), size int, maxsize int, growth int, status int ) open dbcursor fetch next from dbcursor into @dbname while @@fetch_status = 0 begin set @sqlstmt = 'insert into #filelist (dbname,filename,physname, size,maxsize,growth,status) ' + 'select ' + "'" + @dbname + "'" + ',name,filename,size,maxsize,growth,status from ' + @dbname + '..sysfiles' -- print @sqlstmt exec (@sqlstmt) fetch next from dbcursor into @dbname end close dbcursor deallocate dbcursor select dbname,filename,physname, cast(size / 128.0 as decimal(6,2)) as 'Size (MB)', case maxsize when -1 then 'NOLIMIT' else cast(cast(maxsize / 128.0 as decimal(6,2)) as varchar(20)) end as 'Max Size (MB)', case when status & 0x100000 > 0 then cast (growth as char(5)) + '%' else cast (cast(growth / 128.0 as decimal(6,2))as varchar(20)) + ' MB' end as 'Growth Factor' from #filelist order by dbname,filename go drop table #filelist

Joe Lax has spent more than 10 years working in various database environments and has been a practicing DBA on all versions of SQL Server from version 4.2 onward. He also is a MCSE and an MCT who recently has started to learn Oracle, which affords him no end of fun. He loves feedback and can be reached here.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date