Question regarding .LDF and .MDF files and backup

Hello,
I have pretty basic question regarding databases on my MS SQL server.
There are .MDF and .LDF files. From what I can tell (and I may well be VERY
wrong here), the MDF file is the REAL data. The LDF file are changes yet to
be merged into the live data.
1 - What are the LDF files?
2 - Why are they so big? Do they never flush?
3 - When do "transactions" become part of the "data"?
4 - If I want to make a Full backup of a database can I make a full database
backup or do I need to make a logfiles backup also to get a "full backup".
The database is not written so often to so a full backup each night is
enough.
Thanks in advance.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:C5C38AE4-1C1D-4B1F-BEB7-D22565A8FE8E@.microsoft.com...
> Hello,
> I have pretty basic question regarding databases on my MS SQL server.
> There are .MDF and .LDF files. From what I can tell (and I may well be
VERY
> wrong here), the MDF file is the REAL data. The LDF file are changes yet
to
> be merged into the live data.
> 1 - What are the LDF files?
The transaction log.
> 2 - Why are they so big? Do they never flush?
They grow until you either backup the log, or set the database to Simple
recovery mode
> 3 - When do "transactions" become part of the "data"?
Immediately when the transaction commits
> 4 - If I want to make a Full backup of a database can I make a full
database
> backup or do I need to make a logfiles backup also to get a "full backup".
> The database is not written so often to so a full backup each night is
> enough.
Set the database to Simple recovery mode, then backup the entire database
nightly... You will loose any transactions since the last full backup.
>
> Thanks in advance.
|||There is a section in BOL titled "Physical Database Architecture",
explaining the files and their usage. The section title "Backup/Restore
Architecture" will also be useful.
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:C5C38AE4-1C1D-4B1F-BEB7-D22565A8FE8E@.microsoft.com...
> Hello,
> I have pretty basic question regarding databases on my MS SQL server.
> There are .MDF and .LDF files. From what I can tell (and I may well be
VERY
> wrong here), the MDF file is the REAL data. The LDF file are changes yet
to
> be merged into the live data.
> 1 - What are the LDF files?
> 2 - Why are they so big? Do they never flush?
> 3 - When do "transactions" become part of the "data"?
> 4 - If I want to make a Full backup of a database can I make a full
database
> backup or do I need to make a logfiles backup also to get a "full backup".
> The database is not written so often to so a full backup each night is
> enough.
>
> Thanks in advance.
|||Some information scattered in these articles about why log files are
large...
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471
http://www.aspfaq.com/
(Reverse address to reply.)
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:C5C38AE4-1C1D-4B1F-BEB7-D22565A8FE8E@.microsoft.com...
> Hello,
> I have pretty basic question regarding databases on my MS SQL server.
> There are .MDF and .LDF files. From what I can tell (and I may well be
VERY
> wrong here), the MDF file is the REAL data. The LDF file are changes yet
to
> be merged into the live data.
> 1 - What are the LDF files?
> 2 - Why are they so big? Do they never flush?
> 3 - When do "transactions" become part of the "data"?
> 4 - If I want to make a Full backup of a database can I make a full
database
> backup or do I need to make a logfiles backup also to get a "full backup".
> The database is not written so often to so a full backup each night is
> enough.
>
> Thanks in advance.

Question regarding .LDF and .MDF files and backup

Hello,
I have pretty basic question regarding databases on my MS SQL server.
There are .MDF and .LDF files. From what I can tell (and I may well be VERY
wrong here), the MDF file is the REAL data. The LDF file are changes yet to
be merged into the live data.
1 - What are the LDF files?
2 - Why are they so big? Do they never flush?
3 - When do "transactions" become part of the "data"?
4 - If I want to make a Full backup of a database can I make a full database
backup or do I need to make a logfiles backup also to get a "full backup".
The database is not written so often to so a full backup each night is
enough.
Thanks in advance.--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:C5C38AE4-1C1D-4B1F-BEB7-D22565A8FE8E@.microsoft.com...
> Hello,
> I have pretty basic question regarding databases on my MS SQL server.
> There are .MDF and .LDF files. From what I can tell (and I may well be
VERY
> wrong here), the MDF file is the REAL data. The LDF file are changes yet
to
> be merged into the live data.
> 1 - What are the LDF files?
The transaction log.
> 2 - Why are they so big? Do they never flush?
They grow until you either backup the log, or set the database to Simple
recovery mode
> 3 - When do "transactions" become part of the "data"?
Immediately when the transaction commits
> 4 - If I want to make a Full backup of a database can I make a full
database
> backup or do I need to make a logfiles backup also to get a "full backup".
> The database is not written so often to so a full backup each night is
> enough.
Set the database to Simple recovery mode, then backup the entire database
nightly... You will loose any transactions since the last full backup.
>
> Thanks in advance.|||There is a section in BOL titled "Physical Database Architecture",
explaining the files and their usage. The section title "Backup/Restore
Architecture" will also be useful.
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:C5C38AE4-1C1D-4B1F-BEB7-D22565A8FE8E@.microsoft.com...
> Hello,
> I have pretty basic question regarding databases on my MS SQL server.
> There are .MDF and .LDF files. From what I can tell (and I may well be
VERY
> wrong here), the MDF file is the REAL data. The LDF file are changes yet
to
> be merged into the live data.
> 1 - What are the LDF files?
> 2 - Why are they so big? Do they never flush?
> 3 - When do "transactions" become part of the "data"?
> 4 - If I want to make a Full backup of a database can I make a full
database
> backup or do I need to make a logfiles backup also to get a "full backup".
> The database is not written so often to so a full backup each night is
> enough.
>
> Thanks in advance.|||Some information scattered in these articles about why log files are
large...
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:C5C38AE4-1C1D-4B1F-BEB7-D22565A8FE8E@.microsoft.com...
> Hello,
> I have pretty basic question regarding databases on my MS SQL server.
> There are .MDF and .LDF files. From what I can tell (and I may well be
VERY
> wrong here), the MDF file is the REAL data. The LDF file are changes yet
to
> be merged into the live data.
> 1 - What are the LDF files?
> 2 - Why are they so big? Do they never flush?
> 3 - When do "transactions" become part of the "data"?
> 4 - If I want to make a Full backup of a database can I make a full
database
> backup or do I need to make a logfiles backup also to get a "full backup".
> The database is not written so often to so a full backup each night is
> enough.
>
> Thanks in advance.

Question re: SSIS "Script Task" + VB.NET keyboard configuration

I was pretty excited when my first script ran in this type of task. But I soon noticed that I couldn't find the "watch" or "immediate" windows I was used to in standard VB.NET. Did I miss them or are they simply not available in the Script Task editor?

TIA,

barker

P.S. Also under the VB.NET environment, I can Import the VB.NET default keyboard settings (e.g. Step Into is F8; Step Over is Shift+F8) into the IDE. Is the same option available for the Script task editor?

They are just not visible by default. After you hit the breakpoint you can go to the menu "Debug>>>Windows>>Locals" for example.

The script task editor is just VS wired up for VSA and VB.net. So you should be able to accomplish this though I admit I have not explored that. I know I have re-assigned my own hot keys to things via "tools>>options>>keyboard"

sql

Question on using CASE

Hello All,

I am pretty new to SQL Server and I am trying to create a view to gather the necessary data I need. I am not sure if CASE is what I should be using, or if I can even do what I need, but I am trying to capture the following information (I know the data looks a bit wacky, but I cannot post the real data so it is just an example).

Example data:
ID - DESC - STARTDATE - ENDDATE
1A - Pool - 9/21/06 - 9/23/06
1A - Pool - 9/21/05 - 9/23/05
1B - Garden - 9/2/06 - 9/4/06

I want to return the following data:
ID - DESC - STARTDATE - ENDDATE
1A - Pool - 9/21/05 - 9/23/05
1B - Garden - 9/4/06 - 9/4/06

Basically in my mind I am thinking along the lines of:
IF DESC = "Pool" THEN STARTDATE = "minimum STARTDATE"
ELSE STARTDATE = "ENDDATE"

I am having an issue trying to figure out how to create the syntax for this CASE statement.

Any help is appreciated.

ThanksWell you don't need a CASE statement for this. A simple aggregate query will handle the problem you describe:
select ID,
DESC,
min(STARTDATE) as STARTDATE,
max(ENDDATE) as ENDDATE
from YourTable
group by ID,
DESC...but I bet you will find that your problem is more complex than you describe, and that you are going to have to deal with gaps between date ranges that a simple MIN and MAX will overlook.|||Thanks for the reply.

I had tried that type of query but the real issue is I need the min STARTDATE if the DESC = "Pool". If the DESC is anything else I want the STARTDATE to take the ENDDATE.

Probably not possible?|||select ID,
DESC,
case when DESC = 'Pool' then STARTDATE else ENDDATE end as STARTDATE,
ENDDATE
from (select ID,
DESC,
min(STARTDATE) as STARTDATE,
max(ENDDATE) as ENDDATE
from YourTable
group by ID,
DESC) Subquery

You could probably do this without the subquery as well, but it is a little odd mixing aggregate and non-aggregate values in a single column.

question on recompiles

On Monday I added a whole bunch of indexes. They were pretty much just
indexes on foreign keys. Should I have recomiled all the sp's? Since I
didn't, should I bother now? According to BOL:
But if a new index is added from which the stored procedure might benefit,
optimization does not automatically happen (until the next time the stored
procedure is run after SQL Server is restarted).
Wouldnt they also get recompiled the first time they were run, whether SQL
was restarted or not?
TIA,
ChrisR
ChrisR wrote:
> On Monday I added a whole bunch of indexes. They were pretty much just
> indexes on foreign keys. Should I have recomiled all the sp's? Since I
> didn't, should I bother now? According to BOL:
> But if a new index is added from which the stored procedure might
> benefit, optimization does not automatically happen (until the next
> time the stored procedure is run after SQL Server is restarted).
> Wouldnt they also get recompiled the first time they were run,
> whether SQL was restarted or not?
Yes, the sp will compile the first time it is run. The issue is whether
parameter sniffing is going to bite you because SQL Server decided on an
execution plan for a query before the indexes were applied. It could
continue to use the old plan even with the new index until it is
recompiled. You can flag the stored procedure for recompile using
sp_recompile. You could also use DBCC FREEPROCCACHE, but that will
affect the entire server.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Sorry David but that is not quite how it works in 2000. As soon as the
index is added any plans that reference the associated table is marked for
recompilation. So the very next time anyone tries to run that sp after the
index is created (or dropped) it will create a new plan. That new plan will
take into account the index. Whether it chooses to use it or not is up to
the optimizer but it is considered immediately after being built. So the
only things that will use the old or existing plans are ones that are in the
process of executing at the time the index is finished being created.
Andrew J. Kelly SQL MVP
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ekjWeEGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> ChrisR wrote:
> Yes, the sp will compile the first time it is run. The issue is whether
> parameter sniffing is going to bite you because SQL Server decided on an
> execution plan for a query before the indexes were applied. It could
> continue to use the old plan even with the new index until it is
> recompiled. You can flag the stored procedure for recompile using
> sp_recompile. You could also use DBCC FREEPROCCACHE, but that will affect
> the entire server.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

question on recompiles

On Monday I added a whole bunch of indexes. They were pretty much just
indexes on foreign keys. Should I have recomiled all the sp's? Since I
didn't, should I bother now? According to BOL:
But if a new index is added from which the stored procedure might benefit,
optimization does not automatically happen (until the next time the stored
procedure is run after SQL Server is restarted).
Wouldnt they also get recompiled the first time they were run, whether SQL
was restarted or not?
--
TIA,
ChrisRChrisR wrote:
> On Monday I added a whole bunch of indexes. They were pretty much just
> indexes on foreign keys. Should I have recomiled all the sp's? Since I
> didn't, should I bother now? According to BOL:
> But if a new index is added from which the stored procedure might
> benefit, optimization does not automatically happen (until the next
> time the stored procedure is run after SQL Server is restarted).
> Wouldnt they also get recompiled the first time they were run,
> whether SQL was restarted or not?
Yes, the sp will compile the first time it is run. The issue is whether
parameter sniffing is going to bite you because SQL Server decided on an
execution plan for a query before the indexes were applied. It could
continue to use the old plan even with the new index until it is
recompiled. You can flag the stored procedure for recompile using
sp_recompile. You could also use DBCC FREEPROCCACHE, but that will
affect the entire server.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Sorry David but that is not quite how it works in 2000. As soon as the
index is added any plans that reference the associated table is marked for
recompilation. So the very next time anyone tries to run that sp after the
index is created (or dropped) it will create a new plan. That new plan will
take into account the index. Whether it chooses to use it or not is up to
the optimizer but it is considered immediately after being built. So the
only things that will use the old or existing plans are ones that are in the
process of executing at the time the index is finished being created.
Andrew J. Kelly SQL MVP
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ekjWeEGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> ChrisR wrote:
>> On Monday I added a whole bunch of indexes. They were pretty much just
>> indexes on foreign keys. Should I have recomiled all the sp's? Since I
>> didn't, should I bother now? According to BOL:
>> But if a new index is added from which the stored procedure might
>> benefit, optimization does not automatically happen (until the next
>> time the stored procedure is run after SQL Server is restarted).
>> Wouldnt they also get recompiled the first time they were run,
>> whether SQL was restarted or not?
> Yes, the sp will compile the first time it is run. The issue is whether
> parameter sniffing is going to bite you because SQL Server decided on an
> execution plan for a query before the indexes were applied. It could
> continue to use the old plan even with the new index until it is
> recompiled. You can flag the stored procedure for recompile using
> sp_recompile. You could also use DBCC FREEPROCCACHE, but that will affect
> the entire server.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

question on recompiles

On Monday I added a whole bunch of indexes. They were pretty much just
indexes on foreign keys. Should I have recomiled all the sp's? Since I
didn't, should I bother now? According to BOL:
But if a new index is added from which the stored procedure might benefit,
optimization does not automatically happen (until the next time the stored
procedure is run after SQL Server is restarted).
Wouldnt they also get recompiled the first time they were run, whether SQL
was restarted or not?
--
TIA,
ChrisRChrisR wrote:
> On Monday I added a whole bunch of indexes. They were pretty much just
> indexes on foreign keys. Should I have recomiled all the sp's? Since I
> didn't, should I bother now? According to BOL:
> But if a new index is added from which the stored procedure might
> benefit, optimization does not automatically happen (until the next
> time the stored procedure is run after SQL Server is restarted).
> Wouldnt they also get recompiled the first time they were run,
> whether SQL was restarted or not?
Yes, the sp will compile the first time it is run. The issue is whether
parameter sniffing is going to bite you because SQL Server decided on an
execution plan for a query before the indexes were applied. It could
continue to use the old plan even with the new index until it is
recompiled. You can flag the stored procedure for recompile using
sp_recompile. You could also use DBCC FREEPROCCACHE, but that will
affect the entire server.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Sorry David but that is not quite how it works in 2000. As soon as the
index is added any plans that reference the associated table is marked for
recompilation. So the very next time anyone tries to run that sp after the
index is created (or dropped) it will create a new plan. That new plan will
take into account the index. Whether it chooses to use it or not is up to
the optimizer but it is considered immediately after being built. So the
only things that will use the old or existing plans are ones that are in the
process of executing at the time the index is finished being created.
Andrew J. Kelly SQL MVP
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ekjWeEGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> ChrisR wrote:
> Yes, the sp will compile the first time it is run. The issue is whether
> parameter sniffing is going to bite you because SQL Server decided on an
> execution plan for a query before the indexes were applied. It could
> continue to use the old plan even with the new index until it is
> recompiled. You can flag the stored procedure for recompile using
> sp_recompile. You could also use DBCC FREEPROCCACHE, but that will affect
> the entire server.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

Question on Changing Jobs in SQL Server 2000

We have a bunch of databases on this SQL Server 2000 and for each
database we pretty much have the same kind of job running daily and
weekly. We are running out of disk space where we used to normally
backup the databases so we now have a new drive which will be used only
for backups so I need to change all my jobs to backup the databases to
the new drive. I can do that manually by going into each job to change
the drive path, but I was wondering if there was a way to update the
system table so that I did not have to manually change all my jobs.
Any help, ideas in this regard will be greatly appreciated.
ThanksHi
This is where having devices would make the task easier! But you can script
the jobs from Enterprise manager and then globally edit the file created.
John
"shub" wrote:

> We have a bunch of databases on this SQL Server 2000 and for each
> database we pretty much have the same kind of job running daily and
> weekly. We are running out of disk space where we used to normally
> backup the databases so we now have a new drive which will be used only
> for backups so I need to change all my jobs to backup the databases to
> the new drive. I can do that manually by going into each job to change
> the drive path, but I was wondering if there was a way to update the
> system table so that I did not have to manually change all my jobs.
> Any help, ideas in this regard will be greatly appreciated.
> Thanks
>|||John
Thanks for your response. Wouldnt the script drop the existing job and
create a brand new one which would cause me to lose all my job history?
Is there a way to get around that?
Thanks|||Hi
Yes it would, the jobs would have different Ids when re-created so you would
have to export everything before doing the scripting. Another reason for
using devices.
John
"shub" wrote:

> John
> Thanks for your response. Wouldnt the script drop the existing job and
> create a brand new one which would cause me to lose all my job history?
> Is there a way to get around that?
> Thanks
>|||John,
If we had devices in place would't we still need to change tha path off
all the devices that we set up for all the different databases? Also
can you set up devices if the physical location of the back up files
are in different computers in the network, basically does it support
UNC? I will look it up myself, but I would sure appreciate your input
as well.
Thanks again|||Hi
If you need to change the job to make it use a device it will not change the
problems of loosing the history, only make it more flexible in the future.
You should only be keeping a certain amount of history as this information
will bloat your msdb, if you need to retain this for a prolonged period then
you may want to put into place a system that will copy it elsewhere.
Devices can use UNC paths, there is an example of sp_adddumpdevice in books
online that shows exactly this. If you are backing up to another machine it
is worth considering a dedicated (1GB) subnet and adding extra cards to
improve performance.
John
"shub" wrote:

> John,
> If we had devices in place would't we still need to change tha path off
> all the devices that we set up for all the different databases? Also
> can you set up devices if the physical location of the back up files
> are in different computers in the network, basically does it support
> UNC? I will look it up myself, but I would sure appreciate your input
> as well.
> Thanks again
>|||I believe when all our jobs were created originally they were all
created by The maintenance plan wizard and I don't see you have an
option of backing up databases to devices.
Maybe in the near future we need to look at redoing all our jobs to use
the devices.
Thank you for all your help.

Question on Changing Jobs in SQL Server 2000

We have a bunch of databases on this SQL Server 2000 and for each
database we pretty much have the same kind of job running daily and
weekly. We are running out of disk space where we used to normally
backup the databases so we now have a new drive which will be used only
for backups so I need to change all my jobs to backup the databases to
the new drive. I can do that manually by going into each job to change
the drive path, but I was wondering if there was a way to update the
system table so that I did not have to manually change all my jobs.
Any help, ideas in this regard will be greatly appreciated.
ThanksHi
This is where having devices would make the task easier! But you can script
the jobs from Enterprise manager and then globally edit the file created.
John
"shub" wrote:
> We have a bunch of databases on this SQL Server 2000 and for each
> database we pretty much have the same kind of job running daily and
> weekly. We are running out of disk space where we used to normally
> backup the databases so we now have a new drive which will be used only
> for backups so I need to change all my jobs to backup the databases to
> the new drive. I can do that manually by going into each job to change
> the drive path, but I was wondering if there was a way to update the
> system table so that I did not have to manually change all my jobs.
> Any help, ideas in this regard will be greatly appreciated.
> Thanks
>|||John
Thanks for your response. Wouldnt the script drop the existing job and
create a brand new one which would cause me to lose all my job history?
Is there a way to get around that?
Thanks|||Hi
Yes it would, the jobs would have different Ids when re-created so you would
have to export everything before doing the scripting. Another reason for
using devices.
John
"shub" wrote:
> John
> Thanks for your response. Wouldnt the script drop the existing job and
> create a brand new one which would cause me to lose all my job history?
> Is there a way to get around that?
> Thanks
>|||John,
If we had devices in place would't we still need to change tha path off
all the devices that we set up for all the different databases? Also
can you set up devices if the physical location of the back up files
are in different computers in the network, basically does it support
UNC? I will look it up myself, but I would sure appreciate your input
as well.
Thanks again|||Hi
If you need to change the job to make it use a device it will not change the
problems of loosing the history, only make it more flexible in the future.
You should only be keeping a certain amount of history as this information
will bloat your msdb, if you need to retain this for a prolonged period then
you may want to put into place a system that will copy it elsewhere.
Devices can use UNC paths, there is an example of sp_adddumpdevice in books
online that shows exactly this. If you are backing up to another machine it
is worth considering a dedicated (1GB) subnet and adding extra cards to
improve performance.
John
"shub" wrote:
> John,
> If we had devices in place would't we still need to change tha path off
> all the devices that we set up for all the different databases? Also
> can you set up devices if the physical location of the back up files
> are in different computers in the network, basically does it support
> UNC? I will look it up myself, but I would sure appreciate your input
> as well.
> Thanks again
>|||I believe when all our jobs were created originally they were all
created by The maintenance plan wizard and I don't see you have an
option of backing up databases to devices.
Maybe in the near future we need to look at redoing all our jobs to use
the devices.
Thank you for all your help.

Question on a large .LDF file

Ok...I'll let you know up front that I know pretty much nothing about SQL.

Here's my dilema. I've got a db being used on a server. It's got an MDF and an LDF. I've figured out that the latter is (I think) a log file while the former is the actual database.

The MDF is around 560MB and the LDF is around 1.9 GB. What I want to know is if there is any (fairly simple) way to compress/truncate/reduce the size of that 1.9 gig LDF file.

Any info anyone can offer would be appreciated.

Thanks!See: http://dbforums.com/showthread.php?threadid=561221|||DBA's solution is a good one-time fix and one that I have used before. For an ongoing solution, you will need to check a few things:

1. What's your business requirement? Up to the point of failure recovery, or something less than that?

2. What backup media options do you have available? Tape? Disk?

Depending on your answers to 1 and 2, you will need to create a backup strategy for the database. I suspect from your initial problem statement that you do not have database backups (aka dumps) enabled. I further suspect that the database is set for full transaction logging. See note below

See the SQL books on line, or just try walking through the Database Maintenance wizard.

To give you an idea, we have three strategies in our organization:

1. Transaction Logging. Where we backup the database and ship the log files over to another server for a "warm spare" capability. This is expensive and requires a fair knowledge of backups and SQL Administration.

2. Full and Transaction Log Backup to Disk. We have sufficient disk space on a separate partition, we do full backups daily and transaction log backups every hour. The backups are all stored to disk for 24 hours. In that time, they are copied to tape for archive retrieval. A variant is to back everything up directly to tape, but that is slow and may impact SQL performance.

3. Simple Recovery. A few of our databases (mostly development databases) do not need point-in-time recovery capability. These are set up to use Simple logging. This keeps the transaction log (.LDF file) nice and small and simplifies the backup plan (we back the full database once daily). But you can only restore the database to the last time it was fully backed up (meaning wwe could potentially lose 24 hours' worth of work).

Note: If I am wrong about the database backups not being enabled, then I humbly apologize. It's just that that's how I learned my lesson (the hard way). If you do have backup plans enabled, you might investigate shrinking the amount of time between transaction log backups.

HTH,

Hugh Scott

Originally posted by bprell
Ok...I'll let you know up front that I know pretty much nothing about SQL.

Here's my dilema. I've got a db being used on a server. It's got an MDF and an LDF. I've figured out that the latter is (I think) a log file while the former is the actual database.

The MDF is around 560MB and the LDF is around 1.9 GB. What I want to know is if there is any (fairly simple) way to compress/truncate/reduce the size of that 1.9 gig LDF file.

Any info anyone can offer would be appreciated.

Thanks!