Log off Users in shared drive - Access2000

I have an Access2000 app placed on a shred drive. The problem i am having is that users leave it opened and take long breaks. Sometimes they leave it overnight. I would like to make corrections to the form but can't. How can I save and close all users. I would like to send a message telling all users to save any changes and give them 1 minute before shutting down.

ThanksHow do you plan to force them out?

I guess you could look at the timer interval event in Access...|||We have this problem on a small Access DB attached to Sybase. As far as we know, there's nothing you can do. The best I've managed to date is to open the lock file, take the computer names in the lock file and NETMSG them, but it doesn't guarantee they'll close the DB.

Log files - Location

Does the log file have to be on the same drive as the data file?
I know how to detach the database, move location of database files, and attach database pointing to new location. But, I can't get it to work if the log file is on a drive different than the data file.
Is there another way to do this?
Thanks!
No, the log files do not have to be on the same drive as the data files. =
In fact, it is generally recommended that these be located on different =
drives.
Why can't you get it working? What errors do you have?
Another option to sp_detach_db and sp_attach_db is to perform a RESTORE =
DATABASE...WITH MOVE. WITH MOVE allows you to move the data and or log =
file(s) to different disk locations. Documentation and examples can be =
found within Books Online (within the SQL Server program group).
--=20
Keith
"Ysandre" <ysandre@.hotmail.com> wrote in message =
news:6CC23DA7-8294-43DB-AA26-C6A5C1D2FA4C@.microsoft.com...
> Does the log file have to be on the same drive as the data file?
>=20
> I know how to detach the database, move location of database files, =
and attach database pointing to new location. But, I can't get it to =
work if the log file is on a drive different than the data file.
>=20
> Is there another way to do this?
>=20
> Thanks!
|||Hi,
Putting Log file in a different drive is always recommended for better
performance.
How to do:-
(Clear all the users connected to the database using ( ALTER database
<dbname> set single_user with rollback immediate )
sp_detach_db <dbname>
Move the LDF file to new drive using explorer
sp_attach_db
<dbname>,'physical_mdf_file_with_path','Physical_L DF_file_with_new_path'
Thanks
Hari
MCDBA
"Ysandre" <ysandre@.hotmail.com> wrote in message
news:6CC23DA7-8294-43DB-AA26-C6A5C1D2FA4C@.microsoft.com...
> Does the log file have to be on the same drive as the data file?
> I know how to detach the database, move location of database files, and
attach database pointing to new location. But, I can't get it to work if the
log file is on a drive different than the data file.
> Is there another way to do this?
> Thanks!
sql

Log files - Location

Does the log file have to be on the same drive as the data file?
I know how to detach the database, move location of database files, and atta
ch database pointing to new location. But, I can't get it to work if the log
file is on a drive different than the data file.
Is there another way to do this?
Thanks!No, the log files do not have to be on the same drive as the data files. =
In fact, it is generally recommended that these be located on different =
drives.
Why can't you get it working? What errors do you have?
Another option to sp_detach_db and sp_attach_db is to perform a RESTORE =
DATABASE...WITH MOVE. WITH MOVE allows you to move the data and or log =
file(s) to different disk locations. Documentation and examples can be =
found within Books Online (within the SQL Server program group).
--=20
Keith
"Ysandre" <ysandre@.hotmail.com> wrote in message =
news:6CC23DA7-8294-43DB-AA26-C6A5C1D2FA4C@.microsoft.com...
> Does the log file have to be on the same drive as the data file?
>=20
> I know how to detach the database, move location of database files, =
and attach database pointing to new location. But, I can't get it to =
work if the log file is on a drive different than the data file.
>=20
> Is there another way to do this?
>=20
> Thanks!|||Hi,
Putting Log file in a different drive is always recommended for better
performance.
How to do:-
(Clear all the users connected to the database using ( ALTER database
<dbname> set single_user with rollback immediate )
sp_detach_db <dbname>
Move the LDF file to new drive using explorer
sp_attach_db
<dbname> ,'physical_mdf_file_with_path','Physical
_LDF_file_with_new_path'
Thanks
Hari
MCDBA
"Ysandre" <ysandre@.hotmail.com> wrote in message
news:6CC23DA7-8294-43DB-AA26-C6A5C1D2FA4C@.microsoft.com...
> Does the log file have to be on the same drive as the data file?
> I know how to detach the database, move location of database files, and
attach database pointing to new location. But, I can't get it to work if the
log file is on a drive different than the data file.
> Is there another way to do this?
> Thanks!

Log files - Location

Does the log file have to be on the same drive as the data file
I know how to detach the database, move location of database files, and attach database pointing to new location. But, I can't get it to work if the log file is on a drive different than the data file
Is there another way to do this
Thanks!No, the log files do not have to be on the same drive as the data files. = In fact, it is generally recommended that these be located on different =drives.
Why can't you get it working? What errors do you have?
Another option to sp_detach_db and sp_attach_db is to perform a RESTORE =DATABASE...WITH MOVE. WITH MOVE allows you to move the data and or log =file(s) to different disk locations. Documentation and examples can be =found within Books Online (within the SQL Server program group).
-- Keith
"Ysandre" <ysandre@.hotmail.com> wrote in message =news:6CC23DA7-8294-43DB-AA26-C6A5C1D2FA4C@.microsoft.com...
> Does the log file have to be on the same drive as the data file?
> > I know how to detach the database, move location of database files, =and attach database pointing to new location. But, I can't get it to =work if the log file is on a drive different than the data file.
> > Is there another way to do this?
> > Thanks!|||Hi,
Putting Log file in a different drive is always recommended for better
performance.
How to do:-
(Clear all the users connected to the database using ( ALTER database
<dbname> set single_user with rollback immediate )
sp_detach_db <dbname>
Move the LDF file to new drive using explorer
sp_attach_db
<dbname>,'physical_mdf_file_with_path','Physical_LDF_file_with_new_path'
Thanks
Hari
MCDBA
"Ysandre" <ysandre@.hotmail.com> wrote in message
news:6CC23DA7-8294-43DB-AA26-C6A5C1D2FA4C@.microsoft.com...
> Does the log file have to be on the same drive as the data file?
> I know how to detach the database, move location of database files, and
attach database pointing to new location. But, I can't get it to work if the
log file is on a drive different than the data file.
> Is there another way to do this?
> Thanks!

log files

there is controversial topic in my dept. If we put log files of databases on
different mirrored drive we will improve the performance.
Is that true for system dbs too. What are your reccomendations, guys?
In my oppinion sys db should be secured on different mirrored drive with sql
executables.Eli Milkova wrote:
> there is controversial topic in my dept. If we put log files of
> databases on different mirrored drive we will improve the performance.
> Is that true for system dbs too. What are your reccomendations, guys?
> In my oppinion sys db should be secured on different mirrored drive
> with sql executables.
If by system database you mean tempdb, then the answer is yes. Ideally,
tempdb should be on an array with fast sequential write access (RAID 1
or RAID 10). If you're talking about master, model, and msdb, then the
answer is probably no. You can keep those databases on the array that
holds the rest of the DBs since are generally small and are accessed and
changed with less frequency that user databases. I would not generally
recommend they are on placed the OS array. The OS array is best for the
OS and applications, not database files.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

log files

there is controversial topic in my dept. If we put log files of databases o
n
different mirrored drive we will improve the performance.
Is that true for system dbs too. What are your reccomendations, guys?
In my oppinion sys db should be secured on different mirrored drive with sql
executables.Eli Milkova wrote:
> there is controversial topic in my dept. If we put log files of
> databases on different mirrored drive we will improve the performance.
> Is that true for system dbs too. What are your reccomendations, guys?
> In my oppinion sys db should be secured on different mirrored drive
> with sql executables.
If by system database you mean tempdb, then the answer is yes. Ideally,
tempdb should be on an array with fast sequential write access (RAID 1
or RAID 10). If you're talking about master, model, and msdb, then the
answer is probably no. You can keep those databases on the array that
holds the rest of the DBs since are generally small and are accessed and
changed with less frequency that user databases. I would not generally
recommend they are on placed the OS array. The OS array is best for the
OS and applications, not database files.
David Gugick
Quest Software
www.imceda.com
www.quest.com

log files

there is controversial topic in my dept. If we put log files of databases on
different mirrored drive we will improve the performance.
Is that true for system dbs too. What are your reccomendations, guys?
In my oppinion sys db should be secured on different mirrored drive with sql
executables.
Eli Milkova wrote:
> there is controversial topic in my dept. If we put log files of
> databases on different mirrored drive we will improve the performance.
> Is that true for system dbs too. What are your reccomendations, guys?
> In my oppinion sys db should be secured on different mirrored drive
> with sql executables.
If by system database you mean tempdb, then the answer is yes. Ideally,
tempdb should be on an array with fast sequential write access (RAID 1
or RAID 10). If you're talking about master, model, and msdb, then the
answer is probably no. You can keep those databases on the array that
holds the rest of the DBs since are generally small and are accessed and
changed with less frequency that user databases. I would not generally
recommend they are on placed the OS array. The OS array is best for the
OS and applications, not database files.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Log file partition

I don't quite understand the reasons for the "standard 3 drive setup"? can
someone explain it a bit more for me? (who came up with such "standard"?)
if c is for OS
d is for sql excutables
e is for data, then my questions are:
1. in sql 2k, even specify program files being installed on d, there are
some files still being installed on c:\program files\mssql
2. if all data go to e (both mdf, ldf i assume), is that considered a good
performance (I/O) and fault tolerence (if the d drive gose bad, what happen
to trans log?) strategy?
at my previous company, the set up is
C is for os and sql excutables
D is for logs
and F is for data and backup files.
c and d are one partitioned mirror drive. (for fault tolerance)
F drive is raid 5.
can someone tell me if the "standard 3 drive setup" is better than this set
up? and why?
Steve
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
>
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
>
> EdComments Inline
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:eFvkrylJEHA.3628@.TK2MSFTNGP12.phx.gbl...
> I don't quite understand the reasons for the "standard 3 drive setup"? can
> someone explain it a bit more for me? (who came up with such "standard"?)
> if c is for OS
> d is for sql excutables
> e is for data, then my questions are:
> 1. in sql 2k, even specify program files being installed on d, there are
> some files still being installed on c:\program files\mssql
Yep. Most are system databases which are in simple mode anyway. The only
things you might want to move are the tempdb devices. Once SQL is up and
running you can change the default log and data file locations.
> 2. if all data go to e (both mdf, ldf i assume), is that considered a good
> performance (I/O) and fault tolerence (if the d drive gose bad, what
happen
> to trans log?) strategy?
Again, the system databases with the exception of tempdb are now volume and
simple recovery only. They are also fairly small and can be backed up
daily.
> at my previous company, the set up is
> C is for os and sql excutables
> D is for logs
> and F is for data and backup files.
> c and d are one partitioned mirror drive. (for fault tolerance)
> F drive is raid 5.
Ooh, lousy performance AND all the eggs in one basket. Worst practices run
rampant.
> can someone tell me if the "standard 3 drive setup" is better than this
set
> up? and why?
>
Three drives are for fault tolerance and performance. All transactions are
held up until the log entry is physically committed to disk. Also, log
files are sequential writes and database files tend to be random writes.
Disk head movement optimization algorithms can sometimes cause log writes to
be delayed if data is on the same partition.
The biggest reason to separate them is recovery. As long as you have a
decent backup rotation and FULL recovery for the user databases, a three
drive system will always allow you to recover all transactions up to the
last moment before a hardware failure if it is a single partition failure.
> Steve
>
> "Ed" <anonymous@.discussions.microsoft.com> wrote in message
> news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> > Diane,
> >
> > The standard 3 drive setup is to put the operating system on the C
drive,
> the application(SQL Server in this case) on the D drive and the data on
the
> E drive, in your case the .mdf files would be on E and your .ldf files
would
> be on another drive( F for example).
> >
> > Ed
>
>|||Stev
Most sites (and most people) have different views on the perfect disk set up
The way we do it
'C' Operating system (NT4 or Windows 2000/2003
'D' Install SQL Server, System databases, User databases primary filegroups containing system tables onl
'E' User databases user table
'F' User databases transaction log
'G' Backup
'H' Application Dat
D, E and F are usually RAID 1 + 0 all other drives are mirrored
We find this gives us good performance and good resilience
Regard
John

log file getting larger**

Hi
I'm working with SQL server 2000
and I have a question about maintenancing a db:
I have dat and log files located in D: drive
and my drive was getting full and specially the
size of db's log file is larger,to solve
my problem I added another log file which is located in
another drive with more free space,and I shrink the
db periodically,
Now,Is there another way to prevent getting the db
larger (for example someway to clear some old information
of log file) ?
or another ways?
any help would be greatly thankful.Hi,
How to shrink the existing log file:
1. Perform a Transaction log backup (BACKUP LOG - refer books online)
2. Use DBCC SHRINKFILE on trasnaction log file
Have a look into the below link;
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q272318
Check the Recovery model you are using for that database. If it is FULL and
your data is not critical then change the Recovery model to SIMPLE. Simple
recovery model will clear the Transaction log after commiting.
If you need the recovery model as FULL then,
Schedule a Transaction log backup based on your data growth, This can be
used when point in time recovery.
This will ensure that ur log file wont gow much.
Thanks
Hari
MCDBA
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsapfoztqhqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I have a question about maintenancing a db:
> I have dat and log files located in D: drive
> and my drive was getting full and specially the
> size of db's log file is larger,to solve
> my problem I added another log file which is located in
> another drive with more free space,and I shrink the
> db periodically,
> Now,Is there another way to prevent getting the db
> larger (for example someway to clear some old information
> of log file) ?
> or another ways?
> any help would be greatly thankful.
>|||RM
If you don't cary about the data you can set recovery mode to SIMPLE
,otherwise do a regular BACKUP LOG in order to keep the size of the log and
a possibility recover the data at point of time.
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsapfoztqhqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I have a question about maintenancing a db:
> I have dat and log files located in D: drive
> and my drive was getting full and specially the
> size of db's log file is larger,to solve
> my problem I added another log file which is located in
> another drive with more free space,and I shrink the
> db periodically,
> Now,Is there another way to prevent getting the db
> larger (for example someway to clear some old information
> of log file) ?
> or another ways?
> any help would be greatly thankful.
>|||Hi,
If it's SQL Server Ent. Edition default db recovery model is full, so when you create a new database it is always in full recovery model. So if you don't need point in time recovery and your db is not a mission critical OLTP database i recommend you to change the recovery model to simple.
If not then perform a regular log backups. And i recommend you to seperate log file to another physical disk partition.
Adjust your log file size to appopriate size to keep virtual log file number as low as possible.
Regards..
"RM" wrote:
> Hi
> I'm working with SQL server 2000
> and I have a question about maintenancing a db:
> I have dat and log files located in D: drive
> and my drive was getting full and specially the
> size of db's log file is larger,to solve
> my problem I added another log file which is located in
> another drive with more free space,and I shrink the
> db periodically,
> Now,Is there another way to prevent getting the db
> larger (for example someway to clear some old information
> of log file) ?
> or another ways?
> any help would be greatly thankful.
>

log file getting larger**

Hi
I'm working with SQL server 2000
and I have a question about maintenancing a db:
I have dat and log files located in D: drive
and my drive was getting full and specially the
size of db's log file is larger,to solve
my problem I added another log file which is located in
another drive with more free space,and I shrink the
db periodically,
Now,Is there another way to prevent getting the db
larger (for example someway to clear some old information
of log file) ?
or another ways?
any help would be greatly thankful.Hi,
How to shrink the existing log file:
1. Perform a Transaction log backup (BACKUP LOG - refer books online)
2. Use DBCC SHRINKFILE on trasnaction log file
Have a look into the below link;
http://support.microsoft.com/defaul...b;EN-US;q272318
Check the Recovery model you are using for that database. If it is FULL and
your data is not critical then change the Recovery model to SIMPLE. Simple
recovery model will clear the Transaction log after commiting.
If you need the recovery model as FULL then,
Schedule a Transaction log backup based on your data growth, This can be
used when point in time recovery.
This will ensure that ur log file wont gow much.
Thanks
Hari
MCDBA
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsapfoztqhqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I have a question about maintenancing a db:
> I have dat and log files located in D: drive
> and my drive was getting full and specially the
> size of db's log file is larger,to solve
> my problem I added another log file which is located in
> another drive with more free space,and I shrink the
> db periodically,
> Now,Is there another way to prevent getting the db
> larger (for example someway to clear some old information
> of log file) ?
> or another ways?
> any help would be greatly thankful.
>|||RM
If you don't cary about the data you can set recovery mode to SIMPLE
,otherwise do a regular BACKUP LOG in order to keep the size of the log and
a possibility recover the data at point of time.
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsapfoztqhqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I have a question about maintenancing a db:
> I have dat and log files located in D: drive
> and my drive was getting full and specially the
> size of db's log file is larger,to solve
> my problem I added another log file which is located in
> another drive with more free space,and I shrink the
> db periodically,
> Now,Is there another way to prevent getting the db
> larger (for example someway to clear some old information
> of log file) ?
> or another ways?
> any help would be greatly thankful.
>|||Hi,
If it's SQL Server Ent. Edition default db recovery model is full, so when y
ou create a new database it is always in full recovery model. So if you don'
t need point in time recovery and your db is not a mission critical OLTP dat
abase i recommend you to ch
ange the recovery model to simple.
If not then perform a regular log backups. And i recommend you to seperate l
og file to another physical disk partition.
Adjust your log file size to appopriate size to keep virtual log file number
as low as possible.
Regards..
"RM" wrote:

> Hi
> I'm working with SQL server 2000
> and I have a question about maintenancing a db:
> I have dat and log files located in D: drive
> and my drive was getting full and specially the
> size of db's log file is larger,to solve
> my problem I added another log file which is located in
> another drive with more free space,and I shrink the
> db periodically,
> Now,Is there another way to prevent getting the db
> larger (for example someway to clear some old information
> of log file) ?
> or another ways?
> any help would be greatly thankful.
>

log file getting larger**

Hi
I'm working with SQL server 2000
and I have a question about maintenancing a db:
I have dat and log files located in D: drive
and my drive was getting full and specially the
size of db's log file is larger,to solve
my problem I added another log file which is located in
another drive with more free space,and I shrink the
db periodically,
Now,Is there another way to prevent getting the db
larger (for example someway to clear some old information
of log file) ?
or another ways?
any help would be greatly thankful.
Hi,
How to shrink the existing log file:
1. Perform a Transaction log backup (BACKUP LOG - refer books online)
2. Use DBCC SHRINKFILE on trasnaction log file
Have a look into the below link;
http://support.microsoft.com/default...;EN-US;q272318
Check the Recovery model you are using for that database. If it is FULL and
your data is not critical then change the Recovery model to SIMPLE. Simple
recovery model will clear the Transaction log after commiting.
If you need the recovery model as FULL then,
Schedule a Transaction log backup based on your data growth, This can be
used when point in time recovery.
This will ensure that ur log file wont gow much.
Thanks
Hari
MCDBA
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsapfoztqhqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I have a question about maintenancing a db:
> I have dat and log files located in D: drive
> and my drive was getting full and specially the
> size of db's log file is larger,to solve
> my problem I added another log file which is located in
> another drive with more free space,and I shrink the
> db periodically,
> Now,Is there another way to prevent getting the db
> larger (for example someway to clear some old information
> of log file) ?
> or another ways?
> any help would be greatly thankful.
>
|||RM
If you don't cary about the data you can set recovery mode to SIMPLE
,otherwise do a regular BACKUP LOG in order to keep the size of the log and
a possibility recover the data at point of time.
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsapfoztqhqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I have a question about maintenancing a db:
> I have dat and log files located in D: drive
> and my drive was getting full and specially the
> size of db's log file is larger,to solve
> my problem I added another log file which is located in
> another drive with more free space,and I shrink the
> db periodically,
> Now,Is there another way to prevent getting the db
> larger (for example someway to clear some old information
> of log file) ?
> or another ways?
> any help would be greatly thankful.
>
|||Hi,
If it's SQL Server Ent. Edition default db recovery model is full, so when you create a new database it is always in full recovery model. So if you don't need point in time recovery and your db is not a mission critical OLTP database i recommend you to ch
ange the recovery model to simple.
If not then perform a regular log backups. And i recommend you to seperate log file to another physical disk partition.
Adjust your log file size to appopriate size to keep virtual log file number as low as possible.
Regards..
"RM" wrote:

> Hi
> I'm working with SQL server 2000
> and I have a question about maintenancing a db:
> I have dat and log files located in D: drive
> and my drive was getting full and specially the
> size of db's log file is larger,to solve
> my problem I added another log file which is located in
> another drive with more free space,and I shrink the
> db periodically,
> Now,Is there another way to prevent getting the db
> larger (for example someway to clear some old information
> of log file) ?
> or another ways?
> any help would be greatly thankful.
>

log file dilemma! share disk with OS or Data file?

Generally speaking, If you have to pick a poision between sharing a disk
with Operating system drive or sharing a disk with Data file drive, which
one is the worse in terms of system performance?
Here is the scenerio: I have a mirrored drive where I put OS on c$ partition
and created another partition of d$. And we have 4 more drive left.
My options are:
1. Create RAID 5 volume from all 4 drives and put data and log file on that
volume. The advantage is that Bigger disk space usage but very bad on system
performance.
2. Create RAID5 volume from all 4 drive but put only Data file in that RAID
5 and put log file on d$ (The drive where OS files are).
3. Create Two RAID 1 drive and put data on first RAID and log on 2nd RAID.
This is best for performance but I loose lots of disk space.
I know no. 3 option is the best out of 3. But, if you have to choose between
no. 1 and 2, which would you prefer? and how bad the performance hit will be
if we compare no. 3 against no. 1 and no. 3 against no. 2?
I appreciate your answer. SQL SERVER 2005 SP2I would say Option 2. Once the system is up and running there should not be
much access to the OS volume so the logs would not have too much contention.
This is assuming you don't have other issues that might cause problems like
excessive paging or another process writing a lot of data to the OS volume.
-jeff
"James" wrote:

> Generally speaking, If you have to pick a poision between sharing a disk
> with Operating system drive or sharing a disk with Data file drive, which
> one is the worse in terms of system performance?
> Here is the scenerio: I have a mirrored drive where I put OS on c$ partiti
on
> and created another partition of d$. And we have 4 more drive left.
> My options are:
> 1. Create RAID 5 volume from all 4 drives and put data and log file on tha
t
> volume. The advantage is that Bigger disk space usage but very bad on syst
em
> performance.
> 2. Create RAID5 volume from all 4 drive but put only Data file in that RAI
D
> 5 and put log file on d$ (The drive where OS files are).
> 3. Create Two RAID 1 drive and put data on first RAID and log on 2nd RAID.
> This is best for performance but I loose lots of disk space.
> I know no. 3 option is the best out of 3. But, if you have to choose betwe
en
> no. 1 and 2, which would you prefer? and how bad the performance hit will
be
> if we compare no. 3 against no. 1 and no. 3 against no. 2?
> I appreciate your answer. SQL SERVER 2005 SP2
>
>

log file dilemma! share disk with OS or Data file?

Generally speaking, If you have to pick a poision between sharing a disk
with Operating system drive or sharing a disk with Data file drive, which
one is the worse in terms of system performance?
Here is the scenerio: I have a mirrored drive where I put OS on c$ partition
and created another partition of d$. And we have 4 more drive left.
My options are:
1. Create RAID 5 volume from all 4 drives and put data and log file on that
volume. The advantage is that Bigger disk space usage but very bad on system
performance.
2. Create RAID5 volume from all 4 drive but put only Data file in that RAID
5 and put log file on d$ (The drive where OS files are).
3. Create Two RAID 1 drive and put data on first RAID and log on 2nd RAID.
This is best for performance but I loose lots of disk space.
I know no. 3 option is the best out of 3. But, if you have to choose between
no. 1 and 2, which would you prefer? and how bad the performance hit will be
if we compare no. 3 against no. 1 and no. 3 against no. 2?
I appreciate your answer. SQL SERVER 2005 SP2
I would say Option 2. Once the system is up and running there should not be
much access to the OS volume so the logs would not have too much contention.
This is assuming you don't have other issues that might cause problems like
excessive paging or another process writing a lot of data to the OS volume.
-jeff
"James" wrote:

> Generally speaking, If you have to pick a poision between sharing a disk
> with Operating system drive or sharing a disk with Data file drive, which
> one is the worse in terms of system performance?
> Here is the scenerio: I have a mirrored drive where I put OS on c$ partition
> and created another partition of d$. And we have 4 more drive left.
> My options are:
> 1. Create RAID 5 volume from all 4 drives and put data and log file on that
> volume. The advantage is that Bigger disk space usage but very bad on system
> performance.
> 2. Create RAID5 volume from all 4 drive but put only Data file in that RAID
> 5 and put log file on d$ (The drive where OS files are).
> 3. Create Two RAID 1 drive and put data on first RAID and log on 2nd RAID.
> This is best for performance but I loose lots of disk space.
> I know no. 3 option is the best out of 3. But, if you have to choose between
> no. 1 and 2, which would you prefer? and how bad the performance hit will be
> if we compare no. 3 against no. 1 and no. 3 against no. 2?
> I appreciate your answer. SQL SERVER 2005 SP2
>
>

log file dilemma! share disk with OS or Data file?

Generally speaking, If you have to pick a poision between sharing a disk
with Operating system drive or sharing a disk with Data file drive, which
one is the worse in terms of system performance?
Here is the scenerio: I have a mirrored drive where I put OS on c$ partition
and created another partition of d$. And we have 4 more drive left.
My options are:
1. Create RAID 5 volume from all 4 drives and put data and log file on that
volume. The advantage is that Bigger disk space usage but very bad on system
performance.
2. Create RAID5 volume from all 4 drive but put only Data file in that RAID
5 and put log file on d$ (The drive where OS files are).
3. Create Two RAID 1 drive and put data on first RAID and log on 2nd RAID.
This is best for performance but I loose lots of disk space.
I know no. 3 option is the best out of 3. But, if you have to choose between
no. 1 and 2, which would you prefer? and how bad the performance hit will be
if we compare no. 3 against no. 1 and no. 3 against no. 2?
I appreciate your answer. SQL SERVER 2005 SP2I would say Option 2. Once the system is up and running there should not be
much access to the OS volume so the logs would not have too much contention.
This is assuming you don't have other issues that might cause problems like
excessive paging or another process writing a lot of data to the OS volume.
-jeff
"James" wrote:
> Generally speaking, If you have to pick a poision between sharing a disk
> with Operating system drive or sharing a disk with Data file drive, which
> one is the worse in terms of system performance?
> Here is the scenerio: I have a mirrored drive where I put OS on c$ partition
> and created another partition of d$. And we have 4 more drive left.
> My options are:
> 1. Create RAID 5 volume from all 4 drives and put data and log file on that
> volume. The advantage is that Bigger disk space usage but very bad on system
> performance.
> 2. Create RAID5 volume from all 4 drive but put only Data file in that RAID
> 5 and put log file on d$ (The drive where OS files are).
> 3. Create Two RAID 1 drive and put data on first RAID and log on 2nd RAID.
> This is best for performance but I loose lots of disk space.
> I know no. 3 option is the best out of 3. But, if you have to choose between
> no. 1 and 2, which would you prefer? and how bad the performance hit will be
> if we compare no. 3 against no. 1 and no. 3 against no. 2?
> I appreciate your answer. SQL SERVER 2005 SP2
>
>

Log file

My transaction log file (.LDF) has reached 53GB on
a (now full) 80GB hard drive.
Is it safe to delete the log file after a check point
is reached (upon DB shut down, for instance?) And why
does the log file not automatically shrink after check-
points?
Thanks,
Andrew> Is it safe to delete the log file after a check point
> is reached
You can not delete the log file, you can run a backup log dbname with
truncate_only if you do not require point in time recovery, of course, if
you don't require point in time recovery you should set the recovery mode of
the database to simple.
>>And why
> does the log file not automatically shrink after check-
> points?
There are two answers here, one, the data stays in the log even after
checkpoint so you can back it up and have the ability to transactionally
recover. Shrinking is another subject, you can not shrink the actual log
file footprint until it is empty, done by either backing up or truncating
it. Also, if you are doing regular backups of the log and your log file is
growing, it doesn't make sense to keep shrinking it only for it to cause you
a performance hit when it auto-grows again.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Andrew" <anonymous@.discussions.microsoft.com> wrote in message
news:0a5201c3c57f$8bfb3340$a301280a@.phx.gbl...
> My transaction log file (.LDF) has reached 53GB on
> a (now full) 80GB hard drive.
> Is it safe to delete the log file after a check point
> is reached (upon DB shut down, for instance?) And why
> does the log file not automatically shrink after check-
> points?
> Thanks,
> Andrew
>

Log Backups vs. Differential Backups

I was recently educated (thanks Dan) that you cannot create a backup of
a database if you lose your log file (due to drive failure of
corruption). Once I learned this, I began wondering about the merits of
even using a log file with the Full recovery model. Why not just use
the Simple recovery model and use frequent differential backups? Why go
through the overhead of log files if you can get almost the same level
of safety using frequent differntial backups?
Is doing differential backups every 15 minutes close enough to the
level of protection afforded by 15 minute log backups?
The way I see it is that the only thing I lose is
up-to-the-point-of-failure restoration. But, what I gain is not having
to worry about a corrupt log file or losing the drive that has the log
file on it. The processing time seems similar.
Am I wildly wrong anout this?
Thanks in advance for your opinions.
- SeanHi Sean,
Are you say that you would only lose you log file drive? What happens if
it's the data drive that goes? If you lose your log file of the database
and then detach and reattach the drive the systm will create a new log file.
Then applying the log backups will get you back to the point of failure -
the time to the last log file backup.
Backups are also used for non disaster situations and log file give you more
options there. I have always found log file backups quicker that diff
backups when it
comes to large databases
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124756827.011771.28120@.g44g2000cwa.googlegroups.com...
>I was recently educated (thanks Dan) that you cannot create a backup of
> a database if you lose your log file (due to drive failure of
> corruption). Once I learned this, I began wondering about the merits of
> even using a log file with the Full recovery model. Why not just use
> the Simple recovery model and use frequent differential backups? Why go
> through the overhead of log files if you can get almost the same level
> of safety using frequent differntial backups?
> Is doing differential backups every 15 minutes close enough to the
> level of protection afforded by 15 minute log backups?
> The way I see it is that the only thing I lose is
> up-to-the-point-of-failure restoration. But, what I gain is not having
> to worry about a corrupt log file or losing the drive that has the log
> file on it. The processing time seems similar.
> Am I wildly wrong anout this?
> Thanks in advance for your opinions.
> - Sean
>|||Greg - thanks for your reply. I was not able to rebuild a log file. I
took a sample database with a log file, stopped SQL server, deleted the
log file, and restarted SQL server. The DB was "Suspect". I was unable
to get a new log to build with any consistency - I played with setting
the DB to "Emergency Mode" and messed with detaching/reattaching and
would get it to rebuild a log file 50% of the time - still, the problem
is by losing your log file, the data becomes suspect, and you can't
trust it even if you rebuild the log.
I understand how log file backups can get you to the point of failure
if the data drive fails - but I was concerned with the log drive
failing. This led me to post on the topic: "Log Backups - Why bother
with every ten minutes?" There I learned from Dan that if you lose a
log file, your database is hosed, and you can only restore to your last
backup.
This was a disappointment to me - I really like the idea that we can
restore to the point of failure using logs if the data drive fails -
but if the log drive fails, we are right back to only getting back what
we have as a "backup".
So - I am thinking that differentials in "Simple" recovery are pretty
close to as good as log file backups in "Full" Recovery mode. Granted,
you can't restore to the point of failure EVER - but you aren't that
much worse off if the backups are frequent enough.
You mention that log file backups are quicker than differentials. A
little quicker? or significantly quicker? The database sizes I am
concerned with are ~35 Gig. The full backup process to disk takes less
than 10 minutes. Diffs and log backups are much faster, naturally.
Are there other problems with diffs that I am unaware of - locking or
something?|||Hi Sean,
In the situation you have describe to create a suspect database I was not
able to reproduct the issue. Having said that it doesn't really matter.
Any backup system that you employ you should follow the MS recommendation .
They are that transaction logs and Data should be protected while on disk.
That is with a form of RAID. While it is unlikely that a disk goes bad is
is very unlikely that two or more will go bad at the same time. So your
first line or protection is always a RAID system.
Next a diff backup is slower than a log backup. This is the bit from BOL
Note If you have created any file backups since the last full database
backup, those files will be scanned by Microsoft® SQL ServerT 2000 at the
beginning of a differential database backup. This may cause some degradation
of performance in the differential database backup. For more information,
see Using File Backups.
So to do a diff backup it needs to read the backup an check the pages that
have been backed up. Where as a log backup backups the entire log file
(pages). Somethime the LOG backup can be larger than the database bacup
because of high transactions but you can always reduce the time between
backups.
In addition having log backups and transaction logs (Full Recovery Mode)
always the use of log reader type productions to see and recover cahnges to
the data without restoring. This might not be an issue now but at sometime
we have all been asked to get back some details that were changed wrongly.
Backup don't tend to cause locking problems for long periods as they backup
pages and do so quickly.
My recommendation is to continue with log backups , ensure that the data and
log files are on RAID systems (RAID 1 for log, RAID 5 for data is typically
good)
Send your backups and log backups to another server via a script See this
link http://www.sql-scripts.com/members/ScriptDetails.aspx?S_ID=4
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124760340.712510.321430@.g47g2000cwa.googlegroups.com...
> Greg - thanks for your reply. I was not able to rebuild a log file. I
> took a sample database with a log file, stopped SQL server, deleted the
> log file, and restarted SQL server. The DB was "Suspect". I was unable
> to get a new log to build with any consistency - I played with setting
> the DB to "Emergency Mode" and messed with detaching/reattaching and
> would get it to rebuild a log file 50% of the time - still, the problem
> is by losing your log file, the data becomes suspect, and you can't
> trust it even if you rebuild the log.
> I understand how log file backups can get you to the point of failure
> if the data drive fails - but I was concerned with the log drive
> failing. This led me to post on the topic: "Log Backups - Why bother
> with every ten minutes?" There I learned from Dan that if you lose a
> log file, your database is hosed, and you can only restore to your last
> backup.
> This was a disappointment to me - I really like the idea that we can
> restore to the point of failure using logs if the data drive fails -
> but if the log drive fails, we are right back to only getting back what
> we have as a "backup".
> So - I am thinking that differentials in "Simple" recovery are pretty
> close to as good as log file backups in "Full" Recovery mode. Granted,
> you can't restore to the point of failure EVER - but you aren't that
> much worse off if the backups are frequent enough.
> You mention that log file backups are quicker than differentials. A
> little quicker? or significantly quicker? The database sizes I am
> concerned with are ~35 Gig. The full backup process to disk takes less
> than 10 minutes. Diffs and log backups are much faster, naturally.
> Are there other problems with diffs that I am unaware of - locking or
> something?
>|||Point in time restore can be vary valuable. Another thing that log backups allow you to do is to
backup the log after the data file is lost (or corrupted). Read about the NO_TRUNCATE option to the
BACKUP LOG command. I think you should ask yourself: "What advantages does db and diff backups have
in contrast to db and log backups?". I always go for log backups and only *complement* with diff
backups when needed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124756827.011771.28120@.g44g2000cwa.googlegroups.com...
>I was recently educated (thanks Dan) that you cannot create a backup of
> a database if you lose your log file (due to drive failure of
> corruption). Once I learned this, I began wondering about the merits of
> even using a log file with the Full recovery model. Why not just use
> the Simple recovery model and use frequent differential backups? Why go
> through the overhead of log files if you can get almost the same level
> of safety using frequent differntial backups?
> Is doing differential backups every 15 minutes close enough to the
> level of protection afforded by 15 minute log backups?
> The way I see it is that the only thing I lose is
> up-to-the-point-of-failure restoration. But, what I gain is not having
> to worry about a corrupt log file or losing the drive that has the log
> file on it. The processing time seems similar.
> Am I wildly wrong anout this?
> Thanks in advance for your opinions.
> - Sean
>|||I think Tibor's approach is a good one - use a combination of differential
database backups as well as log backups. This will simplify your recovery
procedure and still allow you to recover to the point of failure if you lose
data files.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124756827.011771.28120@.g44g2000cwa.googlegroups.com...
>I was recently educated (thanks Dan) that you cannot create a backup of
> a database if you lose your log file (due to drive failure of
> corruption). Once I learned this, I began wondering about the merits of
> even using a log file with the Full recovery model. Why not just use
> the Simple recovery model and use frequent differential backups? Why go
> through the overhead of log files if you can get almost the same level
> of safety using frequent differntial backups?
> Is doing differential backups every 15 minutes close enough to the
> level of protection afforded by 15 minute log backups?
> The way I see it is that the only thing I lose is
> up-to-the-point-of-failure restoration. But, what I gain is not having
> to worry about a corrupt log file or losing the drive that has the log
> file on it. The processing time seems similar.
> Am I wildly wrong anout this?
> Thanks in advance for your opinions.
> - Sean
>|||Thanks to all.
Greg - knowing that the differential backups have to read the full
backup was a key piece of information in understanding why it is slower
than Log Backups. I'll look into this more thoroughly.
Tibor/Dan - I understand that using all three (full backups,
differentials and Log backups) is the recommended practice - and I
understand how it helps you recover to the point of failure.
What still disappoints me is the fact that losing the log drive makes
you lose the database. It undermines my desire to go through the setup
hassle and expense of keeping and maintaing a transaction log. I have
to setup clients with our products database, and train them to do
backups. none of them are DBAs - many are at the level of novice. The
log backups invariably cause problems, and the drawbacks seem to
outweigh the benefits in these cases.
For myself, in a large installation - I would certainly keep a log and
do log backups - but I'm not sure I can gleefully recommend the same
practice to my clients.
Thanks again for the discussion - I appreciate it.
- Sean

Log Backups vs. Differential Backups

I was recently educated (thanks Dan) that you cannot create a backup of
a database if you lose your log file (due to drive failure of
corruption). Once I learned this, I began wondering about the merits of
even using a log file with the Full recovery model. Why not just use
the Simple recovery model and use frequent differential backups? Why go
through the overhead of log files if you can get almost the same level
of safety using frequent differntial backups?
Is doing differential backups every 15 minutes close enough to the
level of protection afforded by 15 minute log backups?
The way I see it is that the only thing I lose is
up-to-the-point-of-failure restoration. But, what I gain is not having
to worry about a corrupt log file or losing the drive that has the log
file on it. The processing time seems similar.
Am I wildly wrong anout this?
Thanks in advance for your opinions.
- Sean
Hi Sean,
Are you say that you would only lose you log file drive? What happens if
it's the data drive that goes? If you lose your log file of the database
and then detach and reattach the drive the systm will create a new log file.
Then applying the log backups will get you back to the point of failure -
the time to the last log file backup.
Backups are also used for non disaster situations and log file give you more
options there. I have always found log file backups quicker that diff
backups when it
comes to large databases
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124756827.011771.28120@.g44g2000cwa.googlegro ups.com...
>I was recently educated (thanks Dan) that you cannot create a backup of
> a database if you lose your log file (due to drive failure of
> corruption). Once I learned this, I began wondering about the merits of
> even using a log file with the Full recovery model. Why not just use
> the Simple recovery model and use frequent differential backups? Why go
> through the overhead of log files if you can get almost the same level
> of safety using frequent differntial backups?
> Is doing differential backups every 15 minutes close enough to the
> level of protection afforded by 15 minute log backups?
> The way I see it is that the only thing I lose is
> up-to-the-point-of-failure restoration. But, what I gain is not having
> to worry about a corrupt log file or losing the drive that has the log
> file on it. The processing time seems similar.
> Am I wildly wrong anout this?
> Thanks in advance for your opinions.
> - Sean
>
|||Greg - thanks for your reply. I was not able to rebuild a log file. I
took a sample database with a log file, stopped SQL server, deleted the
log file, and restarted SQL server. The DB was "Suspect". I was unable
to get a new log to build with any consistency - I played with setting
the DB to "Emergency Mode" and messed with detaching/reattaching and
would get it to rebuild a log file 50% of the time - still, the problem
is by losing your log file, the data becomes suspect, and you can't
trust it even if you rebuild the log.
I understand how log file backups can get you to the point of failure
if the data drive fails - but I was concerned with the log drive
failing. This led me to post on the topic: "Log Backups - Why bother
with every ten minutes?" There I learned from Dan that if you lose a
log file, your database is hosed, and you can only restore to your last
backup.
This was a disappointment to me - I really like the idea that we can
restore to the point of failure using logs if the data drive fails -
but if the log drive fails, we are right back to only getting back what
we have as a "backup".
So - I am thinking that differentials in "Simple" recovery are pretty
close to as good as log file backups in "Full" Recovery mode. Granted,
you can't restore to the point of failure EVER - but you aren't that
much worse off if the backups are frequent enough.
You mention that log file backups are quicker than differentials. A
little quicker? or significantly quicker? The database sizes I am
concerned with are ~35 Gig. The full backup process to disk takes less
than 10 minutes. Diffs and log backups are much faster, naturally.
Are there other problems with diffs that I am unaware of - locking or
something?
|||Hi Sean,
In the situation you have describe to create a suspect database I was not
able to reproduct the issue. Having said that it doesn't really matter.
Any backup system that you employ you should follow the MS recommendation .
They are that transaction logs and Data should be protected while on disk.
That is with a form of RAID. While it is unlikely that a disk goes bad is
is very unlikely that two or more will go bad at the same time. So your
first line or protection is always a RAID system.
Next a diff backup is slower than a log backup. This is the bit from BOL
Note If you have created any file backups since the last full database
backup, those files will be scanned by Microsoft SQL ServerT 2000 at the
beginning of a differential database backup. This may cause some degradation
of performance in the differential database backup. For more information,
see Using File Backups.
So to do a diff backup it needs to read the backup an check the pages that
have been backed up. Where as a log backup backups the entire log file
(pages). Somethime the LOG backup can be larger than the database bacup
because of high transactions but you can always reduce the time between
backups.
In addition having log backups and transaction logs (Full Recovery Mode)
always the use of log reader type productions to see and recover cahnges to
the data without restoring. This might not be an issue now but at sometime
we have all been asked to get back some details that were changed wrongly.
Backup don't tend to cause locking problems for long periods as they backup
pages and do so quickly.
My recommendation is to continue with log backups , ensure that the data and
log files are on RAID systems (RAID 1 for log, RAID 5 for data is typically
good)
Send your backups and log backups to another server via a script See this
link http://www.sql-scripts.com/members/S...ls.aspx?S_ID=4
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124760340.712510.321430@.g47g2000cwa.googlegr oups.com...
> Greg - thanks for your reply. I was not able to rebuild a log file. I
> took a sample database with a log file, stopped SQL server, deleted the
> log file, and restarted SQL server. The DB was "Suspect". I was unable
> to get a new log to build with any consistency - I played with setting
> the DB to "Emergency Mode" and messed with detaching/reattaching and
> would get it to rebuild a log file 50% of the time - still, the problem
> is by losing your log file, the data becomes suspect, and you can't
> trust it even if you rebuild the log.
> I understand how log file backups can get you to the point of failure
> if the data drive fails - but I was concerned with the log drive
> failing. This led me to post on the topic: "Log Backups - Why bother
> with every ten minutes?" There I learned from Dan that if you lose a
> log file, your database is hosed, and you can only restore to your last
> backup.
> This was a disappointment to me - I really like the idea that we can
> restore to the point of failure using logs if the data drive fails -
> but if the log drive fails, we are right back to only getting back what
> we have as a "backup".
> So - I am thinking that differentials in "Simple" recovery are pretty
> close to as good as log file backups in "Full" Recovery mode. Granted,
> you can't restore to the point of failure EVER - but you aren't that
> much worse off if the backups are frequent enough.
> You mention that log file backups are quicker than differentials. A
> little quicker? or significantly quicker? The database sizes I am
> concerned with are ~35 Gig. The full backup process to disk takes less
> than 10 minutes. Diffs and log backups are much faster, naturally.
> Are there other problems with diffs that I am unaware of - locking or
> something?
>
|||Point in time restore can be vary valuable. Another thing that log backups allow you to do is to
backup the log after the data file is lost (or corrupted). Read about the NO_TRUNCATE option to the
BACKUP LOG command. I think you should ask yourself: "What advantages does db and diff backups have
in contrast to db and log backups?". I always go for log backups and only *complement* with diff
backups when needed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124756827.011771.28120@.g44g2000cwa.googlegro ups.com...
>I was recently educated (thanks Dan) that you cannot create a backup of
> a database if you lose your log file (due to drive failure of
> corruption). Once I learned this, I began wondering about the merits of
> even using a log file with the Full recovery model. Why not just use
> the Simple recovery model and use frequent differential backups? Why go
> through the overhead of log files if you can get almost the same level
> of safety using frequent differntial backups?
> Is doing differential backups every 15 minutes close enough to the
> level of protection afforded by 15 minute log backups?
> The way I see it is that the only thing I lose is
> up-to-the-point-of-failure restoration. But, what I gain is not having
> to worry about a corrupt log file or losing the drive that has the log
> file on it. The processing time seems similar.
> Am I wildly wrong anout this?
> Thanks in advance for your opinions.
> - Sean
>
|||I think Tibor's approach is a good one - use a combination of differential
database backups as well as log backups. This will simplify your recovery
procedure and still allow you to recover to the point of failure if you lose
data files.
Hope this helps.
Dan Guzman
SQL Server MVP
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124756827.011771.28120@.g44g2000cwa.googlegro ups.com...
>I was recently educated (thanks Dan) that you cannot create a backup of
> a database if you lose your log file (due to drive failure of
> corruption). Once I learned this, I began wondering about the merits of
> even using a log file with the Full recovery model. Why not just use
> the Simple recovery model and use frequent differential backups? Why go
> through the overhead of log files if you can get almost the same level
> of safety using frequent differntial backups?
> Is doing differential backups every 15 minutes close enough to the
> level of protection afforded by 15 minute log backups?
> The way I see it is that the only thing I lose is
> up-to-the-point-of-failure restoration. But, what I gain is not having
> to worry about a corrupt log file or losing the drive that has the log
> file on it. The processing time seems similar.
> Am I wildly wrong anout this?
> Thanks in advance for your opinions.
> - Sean
>
|||Thanks to all.
Greg - knowing that the differential backups have to read the full
backup was a key piece of information in understanding why it is slower
than Log Backups. I'll look into this more thoroughly.
Tibor/Dan - I understand that using all three (full backups,
differentials and Log backups) is the recommended practice - and I
understand how it helps you recover to the point of failure.
What still disappoints me is the fact that losing the log drive makes
you lose the database. It undermines my desire to go through the setup
hassle and expense of keeping and maintaing a transaction log. I have
to setup clients with our products database, and train them to do
backups. none of them are DBAs - many are at the level of novice. The
log backups invariably cause problems, and the drawbacks seem to
outweigh the benefits in these cases.
For myself, in a large installation - I would certainly keep a log and
do log backups - but I'm not sure I can gleefully recommend the same
practice to my clients.
Thanks again for the discussion - I appreciate it.
- Sean

Log Backups vs. Differential Backups

I was recently educated (thanks Dan) that you cannot create a backup of
a database if you lose your log file (due to drive failure of
corruption). Once I learned this, I began wondering about the merits of
even using a log file with the Full recovery model. Why not just use
the Simple recovery model and use frequent differential backups? Why go
through the overhead of log files if you can get almost the same level
of safety using frequent differntial backups?
Is doing differential backups every 15 minutes close enough to the
level of protection afforded by 15 minute log backups?
The way I see it is that the only thing I lose is
up-to-the-point-of-failure restoration. But, what I gain is not having
to worry about a corrupt log file or losing the drive that has the log
file on it. The processing time seems similar.
Am I wildly wrong anout this?
Thanks in advance for your opinions.
- SeanHi Sean,
Are you say that you would only lose you log file drive? What happens if
it's the data drive that goes? If you lose your log file of the database
and then detach and reattach the drive the systm will create a new log file.
Then applying the log backups will get you back to the point of failure -
the time to the last log file backup.
Backups are also used for non disaster situations and log file give you more
options there. I have always found log file backups quicker that diff
backups when it
comes to large databases
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124756827.011771.28120@.g44g2000cwa.googlegroups.com...
>I was recently educated (thanks Dan) that you cannot create a backup of
> a database if you lose your log file (due to drive failure of
> corruption). Once I learned this, I began wondering about the merits of
> even using a log file with the Full recovery model. Why not just use
> the Simple recovery model and use frequent differential backups? Why go
> through the overhead of log files if you can get almost the same level
> of safety using frequent differntial backups?
> Is doing differential backups every 15 minutes close enough to the
> level of protection afforded by 15 minute log backups?
> The way I see it is that the only thing I lose is
> up-to-the-point-of-failure restoration. But, what I gain is not having
> to worry about a corrupt log file or losing the drive that has the log
> file on it. The processing time seems similar.
> Am I wildly wrong anout this?
> Thanks in advance for your opinions.
> - Sean
>|||Greg - thanks for your reply. I was not able to rebuild a log file. I
took a sample database with a log file, stopped SQL server, deleted the
log file, and restarted SQL server. The DB was "Suspect". I was unable
to get a new log to build with any consistency - I played with setting
the DB to "Emergency Mode" and messed with detaching/reattaching and
would get it to rebuild a log file 50% of the time - still, the problem
is by losing your log file, the data becomes suspect, and you can't
trust it even if you rebuild the log.
I understand how log file backups can get you to the point of failure
if the data drive fails - but I was concerned with the log drive
failing. This led me to post on the topic: "Log Backups - Why bother
with every ten minutes?" There I learned from Dan that if you lose a
log file, your database is hosed, and you can only restore to your last
backup.
This was a disappointment to me - I really like the idea that we can
restore to the point of failure using logs if the data drive fails -
but if the log drive fails, we are right back to only getting back what
we have as a "backup".
So - I am thinking that differentials in "Simple" recovery are pretty
close to as good as log file backups in "Full" Recovery mode. Granted,
you can't restore to the point of failure EVER - but you aren't that
much worse off if the backups are frequent enough.
You mention that log file backups are quicker than differentials. A
little quicker? or significantly quicker? The database sizes I am
concerned with are ~35 Gig. The full backup process to disk takes less
than 10 minutes. Diffs and log backups are much faster, naturally.
Are there other problems with diffs that I am unaware of - locking or
something?|||Hi Sean,
In the situation you have describe to create a suspect database I was not
able to reproduct the issue. Having said that it doesn't really matter.
Any backup system that you employ you should follow the MS recommendation .
They are that transaction logs and Data should be protected while on disk.
That is with a form of RAID. While it is unlikely that a disk goes bad is
is very unlikely that two or more will go bad at the same time. So your
first line or protection is always a RAID system.
Next a diff backup is slower than a log backup. This is the bit from BOL
Note If you have created any file backups since the last full database
backup, those files will be scanned by Microsoft SQL ServerT 2000 at the
beginning of a differential database backup. This may cause some degradation
of performance in the differential database backup. For more information,
see Using File Backups.
So to do a diff backup it needs to read the backup an check the pages that
have been backed up. Where as a log backup backups the entire log file
(pages). Somethime the LOG backup can be larger than the database bacup
because of high transactions but you can always reduce the time between
backups.
In addition having log backups and transaction logs (Full Recovery Mode)
always the use of log reader type productions to see and recover cahnges to
the data without restoring. This might not be an issue now but at sometime
we have all been asked to get back some details that were changed wrongly.
Backup don't tend to cause locking problems for long periods as they backup
pages and do so quickly.
My recommendation is to continue with log backups , ensure that the data and
log files are on RAID systems (RAID 1 for log, RAID 5 for data is typically
good)
Send your backups and log backups to another server via a script See this
link http://www.sql-scripts.com/members/...ils.aspx?S_ID=4
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124760340.712510.321430@.g47g2000cwa.googlegroups.com...
> Greg - thanks for your reply. I was not able to rebuild a log file. I
> took a sample database with a log file, stopped SQL server, deleted the
> log file, and restarted SQL server. The DB was "Suspect". I was unable
> to get a new log to build with any consistency - I played with setting
> the DB to "Emergency Mode" and messed with detaching/reattaching and
> would get it to rebuild a log file 50% of the time - still, the problem
> is by losing your log file, the data becomes suspect, and you can't
> trust it even if you rebuild the log.
> I understand how log file backups can get you to the point of failure
> if the data drive fails - but I was concerned with the log drive
> failing. This led me to post on the topic: "Log Backups - Why bother
> with every ten minutes?" There I learned from Dan that if you lose a
> log file, your database is hosed, and you can only restore to your last
> backup.
> This was a disappointment to me - I really like the idea that we can
> restore to the point of failure using logs if the data drive fails -
> but if the log drive fails, we are right back to only getting back what
> we have as a "backup".
> So - I am thinking that differentials in "Simple" recovery are pretty
> close to as good as log file backups in "Full" Recovery mode. Granted,
> you can't restore to the point of failure EVER - but you aren't that
> much worse off if the backups are frequent enough.
> You mention that log file backups are quicker than differentials. A
> little quicker? or significantly quicker? The database sizes I am
> concerned with are ~35 Gig. The full backup process to disk takes less
> than 10 minutes. Diffs and log backups are much faster, naturally.
> Are there other problems with diffs that I am unaware of - locking or
> something?
>|||Point in time restore can be vary valuable. Another thing that log backups a
llow you to do is to
backup the log after the data file is lost (or corrupted). Read about the NO
_TRUNCATE option to the
BACKUP LOG command. I think you should ask yourself: "What advantages does d
b and diff backups have
in contrast to db and log backups?". I always go for log backups and only *c
omplement* with diff
backups when needed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124756827.011771.28120@.g44g2000cwa.googlegroups.com...
>I was recently educated (thanks Dan) that you cannot create a backup of
> a database if you lose your log file (due to drive failure of
> corruption). Once I learned this, I began wondering about the merits of
> even using a log file with the Full recovery model. Why not just use
> the Simple recovery model and use frequent differential backups? Why go
> through the overhead of log files if you can get almost the same level
> of safety using frequent differntial backups?
> Is doing differential backups every 15 minutes close enough to the
> level of protection afforded by 15 minute log backups?
> The way I see it is that the only thing I lose is
> up-to-the-point-of-failure restoration. But, what I gain is not having
> to worry about a corrupt log file or losing the drive that has the log
> file on it. The processing time seems similar.
> Am I wildly wrong anout this?
> Thanks in advance for your opinions.
> - Sean
>|||I think Tibor's approach is a good one - use a combination of differential
database backups as well as log backups. This will simplify your recovery
procedure and still allow you to recover to the point of failure if you lose
data files.
Hope this helps.
Dan Guzman
SQL Server MVP
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124756827.011771.28120@.g44g2000cwa.googlegroups.com...
>I was recently educated (thanks Dan) that you cannot create a backup of
> a database if you lose your log file (due to drive failure of
> corruption). Once I learned this, I began wondering about the merits of
> even using a log file with the Full recovery model. Why not just use
> the Simple recovery model and use frequent differential backups? Why go
> through the overhead of log files if you can get almost the same level
> of safety using frequent differntial backups?
> Is doing differential backups every 15 minutes close enough to the
> level of protection afforded by 15 minute log backups?
> The way I see it is that the only thing I lose is
> up-to-the-point-of-failure restoration. But, what I gain is not having
> to worry about a corrupt log file or losing the drive that has the log
> file on it. The processing time seems similar.
> Am I wildly wrong anout this?
> Thanks in advance for your opinions.
> - Sean
>|||Thanks to all.
Greg - knowing that the differential backups have to read the full
backup was a key piece of information in understanding why it is slower
than Log Backups. I'll look into this more thoroughly.
Tibor/Dan - I understand that using all three (full backups,
differentials and Log backups) is the recommended practice - and I
understand how it helps you recover to the point of failure.
What still disappoints me is the fact that losing the log drive makes
you lose the database. It undermines my desire to go through the setup
hassle and expense of keeping and maintaing a transaction log. I have
to setup clients with our products database, and train them to do
backups. none of them are DBAs - many are at the level of novice. The
log backups invariably cause problems, and the drawbacks seem to
outweigh the benefits in these cases.
For myself, in a large installation - I would certainly keep a log and
do log backups - but I'm not sure I can gleefully recommend the same
practice to my clients.
Thanks again for the discussion - I appreciate it.
- Sean