backup 'creep'

SQL runs on a clustered server and during the week the time it takes for the
transactional backup slowly increases, and as it does it takes the majority
of the system resources. the backup is sent to a drive on a remote system.
In cluster admin if i take the service off-line and then back on-line it's
back to running fast again. THe transactional backups take ~1minute after a
off/on line and work their way to 7/8 minutes.
any suggestions?
Richard
Are you using any third part tools to do the backup? What is your memory
configuration like?
Andrew J. Kelly SQL MVP
"Richard Roche" <RichardRoche@.discussions.microsoft.com> wrote in message
news:93EE3C78-6686-4E18-A3BC-5B5A66EFB2A6@.microsoft.com...
> SQL runs on a clustered server and during the week the time it takes for
> the
> transactional backup slowly increases, and as it does it takes the
> majority
> of the system resources. the backup is sent to a drive on a remote
> system.
> In cluster admin if i take the service off-line and then back on-line it's
> back to running fast again. THe transactional backups take ~1minute after
> a
> off/on line and work their way to 7/8 minutes.
> any suggestions?
> --
> Richard
|||pretty standard maintainence plan from w/in SQL server; Agent; doing
transaction backups every 2 hours;
2gb memory on server; letting SQL manage it's memory desires
"Andrew J. Kelly" wrote:

> Are you using any third part tools to do the backup? What is your memory
> configuration like?
> --
> Andrew J. Kelly SQL MVP
>
> "Richard Roche" <RichardRoche@.discussions.microsoft.com> wrote in message
> news:93EE3C78-6686-4E18-A3BC-5B5A66EFB2A6@.microsoft.com...
>
>
|||Have you looked to see if you are doing any paging at the OS level and if
this increases when the log file time increases? Are you running any other
apps on this server? it almost sounds like you have a memory leak
somewhere. While this is rare these days it does happen, especially if you
are running xp's or dlls outside of sql server. Anything with XML by any
chance?
Andrew J. Kelly SQL MVP
"Richard Roche" <RichardRoche@.discussions.microsoft.com> wrote in message
news:65EFE346-1F7F-4036-B60A-1C939C19EC89@.microsoft.com...[vbcol=seagreen]
> pretty standard maintainence plan from w/in SQL server; Agent; doing
> transaction backups every 2 hours;
> 2gb memory on server; letting SQL manage it's memory desires
> "Andrew J. Kelly" wrote:
|||You might also want to have a look at
FIX: Performance decreases over time when you back up files in SQL Server
2000
http://support.microsoft.com/?kbid=824430
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Richard Roche" <RichardRoche@.discussions.microsoft.com> wrote in message
news:93EE3C78-6686-4E18-A3BC-5B5A66EFB2A6@.microsoft.com...
> SQL runs on a clustered server and during the week the time it takes for
> the
> transactional backup slowly increases, and as it does it takes the
> majority
> of the system resources. the backup is sent to a drive on a remote
> system.
> In cluster admin if i take the service off-line and then back on-line it's
> back to running fast again. THe transactional backups take ~1minute after
> a
> off/on line and work their way to 7/8 minutes.
> any suggestions?
> --
> Richard
|||Thanks Jasper. I was sure there was a KB on that but i could not find it
for some reason. I couldn't remember the exact reasons but remembered there
was a KB related to this.
Andrew J. Kelly SQL MVP
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:ORb9sAxAFHA.3016@.tk2msftngp13.phx.gbl...
> You might also want to have a look at
> FIX: Performance decreases over time when you back up files in SQL Server
> 2000
> http://support.microsoft.com/?kbid=824430
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Richard Roche" <RichardRoche@.discussions.microsoft.com> wrote in message
> news:93EE3C78-6686-4E18-A3BC-5B5A66EFB2A6@.microsoft.com...
>
|||I also remember a KB, and it was nearly two years ago. The long and the
short was run your backups locally, then use a script to "transfer" to the
remote storage. That way SQL Server does not have to handle the file
detatils in keeping a remote connection alive.
Larry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23p08tGxAFHA.1392@.tk2msftngp13.phx.gbl...
> Thanks Jasper. I was sure there was a KB on that but i could not find it
> for some reason. I couldn't remember the exact reasons but remembered
> there was a KB related to this.
> --
> Andrew J. Kelly SQL MVP
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:ORb9sAxAFHA.3016@.tk2msftngp13.phx.gbl...
>

Backing Up the Database (Newbie Question)

I have a Database on a MS-SQL Server wich was at the beginning 55 MB (size).
Every time I Backup the database the size increases (50 mb each time) !!!
Now it is over 700 MB of size and I can not store it in a CD !!!
Somebody told me that the size wich is increasing is that of the LOG FILE...
What is this exactly and how can I empty it ?

Any solutions appreciated....
Thanks in advance

massiveTDM850@.hotmail.com
gkarm@.softhome.netGeorge (gkarm@.softhome.net) writes:
> I have a Database on a MS-SQL Server wich was at the beginning 55 MB
> (size). Every time I Backup the database the size increases (50 mb each
> time) !!! Now it is over 700 MB of size and I can not store it in a CD
> !!! Somebody told me that the size wich is increasing is that of the LOG
> FILE... What is this exactly and how can I empty it ?

First you need to decide whether you want up-to-the-point recovery or not.
If you are content with restoring from the last backup in case of a crash,
and thus lose work since the last backup, set the recovery mode for the
database to simple. SQL Server will then truncate the transaction log every
now and then.

If you want up-to-the-point recovery, you should run with full recovery
(or bulk-logged), and make a habit to also backup the transaction log
regularly.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp