file group


what is relation between mdf and ldf.If my ldf is on drive C and mdf is on drive D.then in case of file group running out of space I need to create space on D.But in some of cases when I shrink ldf on C the file group space alert goes of autometically.what login is this?


[Moderator edit]
above line should read "what logi**c** is this?"
more ▼

asked May 04, 2012 at 02:51 PM in Default

pits gravatar image

830 82 91 92

Can you provide the exact error message you're getting? And what's giving it to you?
May 04, 2012 at 03:10 PM ThomasRushton ♦
my ldf is growing very fast for some database and using dbcc open tran I noticed there is some spid 148 which is causing the transaction.i believe "dbcc input buffer 148(spid)" will give me detail picture of same running process? is that rigth or there is any other method to check details of active transactions? please advise
May 10, 2012 at 02:42 PM pits
(comments are locked)
10|1200 characters needed characters left

2 answers: sort oldest

The MDF is the SQL Server database data file; the LDF is the transaction log file.

My first reaction would be to check that your backups are running properly - you should, as well as doing Full backups, be periodically backing up the transaction log (assuming your database isn't in SIMPLE recovery mode).

If the TLogs are being backed up regularly, and you're still getting the problem, then it may be that either you've not got sufficient space for the TLog to grow itself (or you've disabled autogrowth), or you need to reconsider the frequency at which you're doing TLog backups, or you've got some outstanding transaction that's causing issues.

more ▼

answered May 04, 2012 at 03:10 PM

ThomasRushton gravatar image

ThomasRushton ♦
34k 18 20 44

I typed too much. Good answer.
May 04, 2012 at 03:13 PM Grant Fritchey ♦♦
By default mdf is the primary database file for that db, ndf is traditionally used for additional files. http://msdn.microsoft.com/en-us/library/ms179316.aspx
May 04, 2012 at 04:38 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

If all you have are the two files then, by default, they work like this. The mdf is a data file. That's where all your data and structures and stored procedures, etc., are stored for the database in question. The ldf is a log file. That stores the transaction logs for the database. Every transaction gets written to the log. Then, depending on the recovery model, they either get cleaned off automatically (for Simple recovery) or you have to run a log backup (for Bulk Logged & Full recovery).

The filegroup is an additional management mechanism that you can use to rearrange groups of files if you have multiple files assigned to your database. In your case, with only a single .mdf file, you'll only have the default filegroup. If the filegroup is running out of space, it's probably referring to the data file on your D: drive, but it could be that you have limited file growth on one or both files which could also spawn the error.

I don't understand wht you mean by "What login is this?" These are files stored on disks and have no relations directly to logins in your SQL Server instance.
more ▼

answered May 04, 2012 at 03:12 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
101k 19 21 74

my mistake,the question supposed to be "What logic is this",thank you Grant,
May 09, 2012 at 12:13 PM pits
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 04, 2012 at 02:51 PM

Seen: 1032 times

Last Updated: Sep 10, 2012 at 03:17 PM