find out data file is splits to multiple files?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » find out data file is splits to multiple...

find out data file is splits to multiple files?

Rate Topic

Display Mode

Topic Options

Author

Message

sumith1andonly1

Posted Thursday, January 17, 2013 1:51 AM

Valued Member

Group: General Forum Members
Last Login: Sunday, May 05, 2013 11:09 PM
Points: 66, Visits: 260

Query to find out 'is data and log file splits to multiple files '?

Post #1408215

anthony.green

Posted Thursday, January 17, 2013 1:56 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

you cant split a data or log file to multiple files.

data and log files are always seperate, you cant have an data file which is your log file, unless for some reason you have named your LDF a MDF NDF instead but as the log file doesnt have a filegroup SQL knows its a log file.

you can add a filegroup and then add multiple files to the file group.

logs dont have a file group and having a second log is useless, but you can get that from sys.master_files, using the data_space_id which referes to the filegroup in the database

0 = Log files
1 = Primary
>1 = User defined filegroup

If you have more than one file in the data_space_id you have move than 1 file in the filegroup

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1408218

sumith1andonly1

Posted Thursday, January 17, 2013 2:10 AM

Valued Member

Group: General Forum Members
Last Login: Sunday, May 05, 2013 11:09 PM
Points: 66, Visits: 260

I MEAN ANY QUERY TO INDICATE THAT THERE IS MULTIPLE DATA FILES FOR SINGLE DB.....

Post #1408226

anthony.green

Posted Thursday, January 17, 2013 2:12 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

sumith1andonly1 (1/17/2013)

I MEAN ANY QUERY TO INDICATE THAT THERE IS MULTIPLE DATA FILES FOR SINGLE DB.....

No need to shout.

Again sys.master_files, filter out all your log files (data_space_id = 0) and then count the remaining grouped by the database name anything bigger than 1 you have multiple data files in that DB.

Post #1408228

sumith1andonly1

Posted Thursday, January 17, 2013 2:17 AM

Valued Member

Group: General Forum Members
Last Login: Sunday, May 05, 2013 11:09 PM
Points: 66, Visits: 260

THANK YOU MY DEAR FRIEND

Post #1408232

anthony.green

Posted Thursday, January 17, 2013 2:18 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

sumith1andonly1 (1/17/2013)

THANK YOU MY DEAR FRIEND

Might want to switch your caps lock off

Post #1408234

sumith1andonly1

Posted Thursday, January 17, 2013 2:21 AM

Valued Member

Group: General Forum Members
Last Login: Sunday, May 05, 2013 11:09 PM
Points: 66, Visits: 260

yes i will...friend

Post #1408237

ScottPletcher

Posted Thursday, January 17, 2013 1:15 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:38 PM
Points: 1,318, Visits: 1,763


SELECT *
FROM <db_name>.sys.database_files 
WHERE
    type_desc <> 'LOG'

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson

Post #1408590

« Prev Topic | Next Topic »

Permissions