Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

find out data file is splits to multiple files? Expand / Collapse
Author
Message
Posted Thursday, January 17, 2013 1:51 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 23, 2014 4:13 AM
Points: 73, Visits: 333
Query to find out 'is data and log file splits to multiple files '?

Post #1408215
Posted Thursday, January 17, 2013 1:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:03 AM
Points: 5,132, Visits: 4,925
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
Posted Thursday, January 17, 2013 2:10 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 23, 2014 4:13 AM
Points: 73, Visits: 333
I MEAN ANY QUERY TO INDICATE THAT THERE IS MULTIPLE DATA FILES FOR SINGLE DB.....
Post #1408226
Posted Thursday, January 17, 2013 2:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:03 AM
Points: 5,132, Visits: 4,925
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.




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 #1408228
Posted Thursday, January 17, 2013 2:17 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 23, 2014 4:13 AM
Points: 73, Visits: 333
THANK YOU MY DEAR FRIEND
Post #1408232
Posted Thursday, January 17, 2013 2:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:03 AM
Points: 5,132, Visits: 4,925
sumith1andonly1 (1/17/2013)
THANK YOU MY DEAR FRIEND


Might want to switch your caps lock off




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 #1408234
Posted Thursday, January 17, 2013 2:21 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 23, 2014 4:13 AM
Points: 73, Visits: 333
yes i will...friend
Post #1408237
Posted Thursday, January 17, 2013 1:15 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:08 PM
Points: 1,963, Visits: 2,898


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




SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1408590
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse