Best practice method for shrinking the log file in dev environments

Hi all,
Can someone tell me what the best way to reduce my log file size is when
it gets too big. I can't switch recovery mode to Simple but every so
often I'd like to go in and clear it out.
What is the preffered command to do this?
I've heard the backup command with the TRUNCATE_ONLY isnt the best way
to do this? Is that the case and if so, whats the alternative?
Also, could someone tell me if doing a full backup automatically
truncates the transaction log?
Many thanks
Simon
Hello,
Could someone tell me if doing a full backup automatically truncates the
transaction log?
NO, FULL database backup will not clear the transaction log. You need to
backup the transaction log backup using BACKUP LOG to clear the log or else
if you do not
want the transaction log backup you could use Backup LOG with TRUNCATE_ONLY
to clear the transaction log from LDF file.
If you do not require a Transaction log backup then change the recovery
model for the database to "SIMPLE", in this
case after the commit the transaction log will be cleared. This recovery
mode will not allow transaction log backup.
In the otherway around, if your data is very critical / production data, set
the recovery model to "FULL". This allows you to perform
a transaction log backup. In this model after the commit the transaction log
still remains in the log file and will get cleared
when you perform a backup of log or issue "Truncate_only". So Truncate_only
is not a good option in production server.
If it is production / critical database follow the steps:-
1. Set the database recovery model to "FULL"
2. Perform a Full database backup once
3. Schedule Transaction log backup using (Backup Log dbname to
disk='d:\backup\dbname.tr1'
4. Perform the step 3 every 30 minutes (decide up on the volume of
transaction), but give new file names each backup dbname.tr1,...tr2...tr3
5. After the step 3 and 4 the transaction log will be cleared from
transaction log file
if you follow this step, even if yor database creach you can recover till
the last transaction log backup as well you can do a PINT_IN_TIME recovery
if needed
If it is non production or data is not critical
1. Set the recovery model to "SIMPLE"
2. Perform a Full database backup daily
3. If needed once in a while you can execute backup log dbname with
truncate_only
If you this methodology we can restore only till last backup.
Thanks
Hari
"Simon" <simon@.nothanks.com> wrote in message
news:%23$ozNe8RHHA.3440@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> Can someone tell me what the best way to reduce my log file size is when
> it gets too big. I can't switch recovery mode to Simple but every so often
> I'd like to go in and clear it out.
> What is the preffered command to do this?
> I've heard the backup command with the TRUNCATE_ONLY isnt the best way to
> do this? Is that the case and if so, whats the alternative?
> Also, could someone tell me if doing a full backup automatically truncates
> the transaction log?
> Many thanks
> Simon
|||Thats a great answer - thanks sincerely for your time and advice
Kindest Regards
Simon

Beginners SQL Query i cant suse out

HI,
I have what should be a simple query , but I just dont seem to have the head
for it :
Tables :
Tables
TableID
TableName
OpenedTime
Covers
CoverID
CoverNumber
TableID_FK
The query is to return back the number of times the table was opened in a
given period and how many covers there were also for this given period. What
I have so far that is wrong :
SELECT COUNT(Tables.TableID), COUNT(Covers.CoverNumber)
FROM Table_Archive INNER JOIN
Covers_Archive ON Table_Archive.TableArchiveID =
Covers_Archive.TableArchiveID_FK
ThanksI think I sorted using "DISTINCT"
"Jon Vaughan" <jonnyvaughan@.hotmail.com> wrote in message
news:urHPf.24197$Gh4.17452@.fe07.news.easynews.com...
> HI,
> I have what should be a simple query , but I just dont seem to have the
> head for it :
> Tables :
> Tables
> TableID
> TableName
> OpenedTime
> Covers
> CoverID
> CoverNumber
> TableID_FK
> The query is to return back the number of times the table was opened in a
> given period and how many covers there were also for this given period.
> What I have so far that is wrong :
> SELECT COUNT(Tables.TableID), COUNT(Covers.CoverNumber)
> FROM Table_Archive INNER JOIN
> Covers_Archive ON Table_Archive.TableArchiveID =
> Covers_Archive.TableArchiveID_FK
>
> Thanks
>|||I'm not at all sure I understand what you are asking for, but try
changing the COUNTs to COUNT(distinct):
COUNT(distinct Tables.TableID)
COUNT(distinct Covers.CoverNumber)
Roy Harvey
Beacon Falls, CT
On Wed, 08 Mar 2006 20:49:31 GMT, "Jon Vaughan"
<jonnyvaughan@.hotmail.com> wrote:

>HI,
>I have what should be a simple query , but I just dont seem to have the hea
d
>for it :
>Tables :
>Tables
>TableID
>TableName
>OpenedTime
>Covers
>CoverID
>CoverNumber
>TableID_FK
>The query is to return back the number of times the table was opened in a
>given period and how many covers there were also for this given period. Wha
t
>I have so far that is wrong :
>SELECT COUNT(Tables.TableID), COUNT(Covers.CoverNumber)
>FROM Table_Archive INNER JOIN
> Covers_Archive ON Table_Archive.TableArchiveID =
>Covers_Archive.TableArchiveID_FK
>
>Thanks
>|||First of all, your table name in your list don't appear to match what's
in the SQL code. So I made some Create Table/Insert statements to have
something to work with. Plus, you never explained what data types the
columns are supposed to be. So I made it up. :) Those are:
Create Table Table_Archive (TableID int, TableName nvarchar(20),
OpenedTime DateTime)
Create Table Covers_Archive (CoverID int, CoverNumber int, TableID_FK
int)
Insert Into Table_Archive (TableID,TableName,OpenedTime) Values (1,
'Test1', '3/8/06 1 pm')
Insert Into Table_Archive (TableID,TableName,OpenedTime) Values (1,
'Test1', '3/8/06 3 pm')
Insert Into Table_Archive (TableID,TableName,OpenedTime) Values (1,
'Test1', '3/8/06 7 pm')
Insert Into Table_Archive (TableID,TableName,OpenedTime) Values (1,
'Test1', '3/9/06 1 pm')
Insert Into Table_Archive (TableID,TableName,OpenedTime) Values (2,
'Test2', '3/8/06 1 pm')
Insert Into Table_Archive (TableID,TableName,OpenedTime) Values (2,
'Test2', '3/8/06 3 pm')
Insert Into Table_Archive (TableID,TableName,OpenedTime) Values (2,
'Test2', '3/8/06 7 pm')
Insert Into Table_Archive (TableID,TableName,OpenedTime) Values (2,
'Test2', '3/9/06 1 pm')
Insert Into Covers_Archive (CoverID,CoverNumber,TableID_FK) Values
(1,99,1)
Insert Into Covers_Archive (CoverID,CoverNumber,TableID_FK) Values
(2,99,1)
Insert Into Covers_Archive (CoverID,CoverNumber,TableID_FK) Values
(3,67,2)
Insert Into Covers_Archive (CoverID,CoverNumber,TableID_FK) Values
(4,88,2)
Using the above, the SQL below should give you what I t hink you are
looking for.
SELECT T.TableName,
T.OpenCount,
C.CoverCount
FROM
(SELECT TableName,
TableID,
COUNT(TableID) OpenCount
FROM Table_Archive
GROUP BYTableName,TableID) T
JOIN
(SELECT TableID_FK,
Count(DISTINCT CoverNumber) CoverCount
FROM Covers_Archive
GROUP BY TableID_FK) C
ON T.TableID = C. TableID_FK

Beginners Question: How to write this query

Hi -

I feel stupied b/c I can't figure this out but I hope it's just a few seconds for some of the more senior posters of this forum:

I have a table that looks like this:

Table:Request
ID: bigint
... (a bunch of table specific columns)
User_Status_ID: bigint (holds the bigint ID of the status table, below; always holds the ID to the most recent ID, if several)
Provider_Status_ID: bigint (holds the bigint ID of the status table, below; always holds the ID to the most recent ID, if several)

and then there is a second table that looks like this:

Table: Status
ID: bigint
RequestID: bigint (the request ID, so I can see all status messages for the request)
Time: Timestamp
Code: byte (there are only a few status messages)

The status table holds both the status for users and providers, as they use exactly the same status code. What I'd like to get is a table/view that looks like this:

RequestID:bigint (the request that this status belongs to)
User_Code: byte (the code of th most recent user status)
Provider_Code: byte (the code of the most recent provider status)

Somehow, I am blanking how to create a query that returns both user and provider codes in one row. Any help greatly appreciated!!!!

Oliver

Why has the Status table the column RequestID?

Otherwise the SELECT you want is along the lines of

SELECT RequestID, U.Code, P.Code FROM Request, Status U, Status P WHERE Request.User_Status_Id = U.Id AND Request.Provider_Code = P.Id

|||

Thanks - that improves my life. I had used multiple views before and it was getting messy ...

Beginner at Backups

I've spent the afternoon reading as much as possible on how to backup and
restore databases except there are a couple of small areas I can't quite get
my head round, so apologies for the beginners question but here goes:
I can set up a disk backup device and then every night run the 'Backup
Database' command to back up our database to a local folder, on top of this
I can run the 'backup log with truncate_only' every 20 minutes to provide
even better restore capability. I have tested these functions and
successfully restored the DB, but in this scenario the backup device is one
file and will simlpy keep growing every day. How can I overcome this? Should
I be creating a new file for every day and then deleting old files? Also,
should each transaction log be backed up to a new file - if not then how do
you know when to start a new backup file for the transaction logs without
the possibility of loosing data?
Cheers, Tom
Hi,
Use the INIT option along with BACKUP database command to reinitialize the
file every time.
Backup database dbname to device_name with INIT
See books online for more detail
Thanks
Hari
MCDBA
"Tom Clark" <tom_clark100@.hotmail.com> wrote in message
news:eTN0zsweEHA.140@.TK2MSFTNGP12.phx.gbl...
> I've spent the afternoon reading as much as possible on how to backup and
> restore databases except there are a couple of small areas I can't quite
get
> my head round, so apologies for the beginners question but here goes:
> I can set up a disk backup device and then every night run the 'Backup
> Database' command to back up our database to a local folder, on top of
this
> I can run the 'backup log with truncate_only' every 20 minutes to provide
> even better restore capability. I have tested these functions and
> successfully restored the DB, but in this scenario the backup device is
one
> file and will simlpy keep growing every day. How can I overcome this?
Should
> I be creating a new file for every day and then deleting old files? Also,
> should each transaction log be backed up to a new file - if not then how
do
> you know when to start a new backup file for the transaction logs without
> the possibility of loosing data?
> Cheers, Tom
>
|||1. With TRUNCATE Only will ONLY truncate the logs (Basically smokes Em)
2. Instead of overwriting the backup file each night, it is fairly typical
to move the OLD Backup to an archive location and save a few previous
backups. OR you can just delete it and replace it with the new backup file.
We typically name our Backup Files with a date time element
...\Backups\DatabaseName\Full\DatabaseNameXX_FULL _MMDDYYYYMMSS.Bak
Where XX is the File Number (We create 4 seperate files for each full backup
to improve backup performance as SQL Server can write to multiple files at
once using multiple threads)
3. We write each transaction backup to a seperate file following the same
naming convention.
...\Backups\DatabaseName\TRXN\DatabaseName_Trnxn_ MMDDYYYYMMSS.Bak
we backup our transaction logs every 15 minutes.
AFTER each successful FULL Backup, we move the Old Transaction Logs to an
Archive Location. in the archive folder we keep 48 hours of logs. We delete
files older than 48 hours.
this may not be perfect, but it works for us and is a good start for you.
Cheers,
Greg Jackson
PDX, Oregon
|||Cheers Greg, this was exactly the kind of answer I was after!
Thinking on though, if you had to do a restore and then apply all the logs
up to the last log backup, would you do this by script (or apply each
transaction log by hand) and if so how? The testing I've done has only been
with a small number of log files and so I have applied them one by one.
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:eb68X3weEHA.4068@.TK2MSFTNGP11.phx.gbl...
> 1. With TRUNCATE Only will ONLY truncate the logs (Basically smokes Em)
> 2. Instead of overwriting the backup file each night, it is fairly
typical
> to move the OLD Backup to an archive location and save a few previous
> backups. OR you can just delete it and replace it with the new backup
file.
> We typically name our Backup Files with a date time element
> ...\Backups\DatabaseName\Full\DatabaseNameXX_FULL_ MMDDYYYYMMSS.Bak
> Where XX is the File Number (We create 4 seperate files for each full
backup
> to improve backup performance as SQL Server can write to multiple files at
> once using multiple threads)
> 3. We write each transaction backup to a seperate file following the same
> naming convention.
> ...\Backups\DatabaseName\TRXN\DatabaseName_Trnxn_M MDDYYYYMMSS.Bak
> we backup our transaction logs every 15 minutes.
> AFTER each successful FULL Backup, we move the Old Transaction Logs to an
> Archive Location. in the archive folder we keep 48 hours of logs. We
delete
> files older than 48 hours.
>
> this may not be perfect, but it works for us and is a good start for you.
>
> Cheers,
>
> Greg Jackson
> PDX, Oregon
>
|||you can do it by hand or by script if you wanted.
I've actually done it by hand most often in past.
GAJ