New to Replication

I have a SQL 2000 database used in a data entry / transactional mode. We
have few users, but they do occassionally run reports which are server
intensive. It is a small business. We would like to begin using the data in
the database on the website. I'm wondering if I should replicate the DB on
the same server. Does that really provide any performance protection for the
data entry folks? We don't think the traffic on the Internet will be intense
to begin with because it will only be for members. Does a snapshot
replication make sense (we only need a refresh each night) or should I just
let the web hit the data entry db? Thanks
Hi,
I understand that some of your users occasionally ran reports on your
server. The operations impacted your server performance. You want to know
if you can replicate the DB on the same server to improve the performance.
If I have misunderstood, please let me know.
Generally producing report is CPU, memory and I/O intensive, so I recommend
that you replicate your database to a different server (dedicated for
report server) and have your web application use this report server for
producing report. Since reports are for history data, snapshot replication
is enough.
For implementing replication, please refer to:
Implementing Replication (Enterprise Manager)
http://msdn2.microsoft.com/en-us/library/aa216618(sql.80).aspx
Implementing Master-Slave Snapshot Replication Using SQL Server
http://msdn2.microsoft.com/en-us/library/ms998561.aspx
Planning for Snapshot Replication
http://msdn2.microsoft.com/en-us/library/aa237481(SQL.80).aspx
If you have any other questions or concerns, please let me know. Have a
nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||That answers my question. Thanks Charles!
"Charles Wang[MSFT]" wrote:

> Hi,
> I understand that some of your users occasionally ran reports on your
> server. The operations impacted your server performance. You want to know
> if you can replicate the DB on the same server to improve the performance.
> If I have misunderstood, please let me know.
> Generally producing report is CPU, memory and I/O intensive, so I recommend
> that you replicate your database to a different server (dedicated for
> report server) and have your web application use this report server for
> producing report. Since reports are for history data, snapshot replication
> is enough.
> For implementing replication, please refer to:
> Implementing Replication (Enterprise Manager)
> http://msdn2.microsoft.com/en-us/library/aa216618(sql.80).aspx
> Implementing Master-Slave Snapshot Replication Using SQL Server
> http://msdn2.microsoft.com/en-us/library/ms998561.aspx
> Planning for Snapshot Replication
> http://msdn2.microsoft.com/en-us/library/aa237481(SQL.80).aspx
> If you have any other questions or concerns, please let me know. Have a
> nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
>
>
>
>
>
sql

New Subscriptions Not Running

Hello,
I am running Microsoft SQL Server 2000.
For some reason all of the sudden any subscription I make in reporting
services will not run.
The status just stays at "New Subscription". I made a test schedule
that runs every 5 minutes.
I have even added old subscriptions to this schedule and I get email
every 5 minutes. Why would any new subscription I make not be running?
I don't even get an error message under the status heading in the
subscription list.
Thanks,
JeffJeffrae wrote:
> Hello,
> I am running Microsoft SQL Server 2000.
> For some reason all of the sudden any subscription I make in reporting
> services will not run.
> The status just stays at "New Subscription". I made a test schedule
> that runs every 5 minutes.
> I have even added old subscriptions to this schedule and I get email
> every 5 minutes. Why would any new subscription I make not be running?
>
> I don't even get an error message under the status heading in the
> subscription list.
>
> Thanks,
> Jeff
Ok... Here is something that I forgot to mention.. This is actually a
big thing.. I recently upgraded my computer so had to migrate the SQL
server and reporting services.. All of the existing reports run like a
charm.. I just can not get any new ones to run..
Thanks,
Jeff|||Jeffrae wrote:
> Jeffrae wrote:
> > Hello,
> >
> > I am running Microsoft SQL Server 2000.
> >
> > For some reason all of the sudden any subscription I make in reporting
> > services will not run.
> >
> > The status just stays at "New Subscription". I made a test schedule
> > that runs every 5 minutes.
> >
> > I have even added old subscriptions to this schedule and I get email
> > every 5 minutes. Why would any new subscription I make not be running?
> >
> >
> > I don't even get an error message under the status heading in the
> > subscription list.
> >
> >
> > Thanks,
> > Jeff
> Ok... Here is something that I forgot to mention.. This is actually a
> big thing.. I recently upgraded my computer so had to migrate the SQL
> server and reporting services.. All of the existing reports run like a
> charm.. I just can not get any new ones to run..
> Thanks,
> Jeff
Is there another newsgroup that I may want to post this question in?
Thanks,
Jeff

New Subscriptions Never Run.

Hello,
For some reason all of the suden any subscription I make will not run..
The status just stays at "New Subscription". I made a test schedule
that runs every 5 minutes.
I have even added old subscriptions to this schedule and I get email
every 5 minutes. Why would Any new subscription I make not be running?
I don't even get an error message under the status heading in the
Subscription list.
Thanks,
JeffJeffrae wrote:
> Hello,
> For some reason all of the sudden any subscription I make will not run..
> The status just stays at "New Subscription". I made a test schedule
> that runs every 5 minutes.
> I have even added old subscriptions to this schedule and I get email
> every 5 minutes. Why would Any new subscription I make not be running?
> I don't even get an error message under the status heading in the
> Subscription list.
> Thanks,
> Jeff
I notice that Subscriptions.MatchData appears to be different then all
of the rest... What is this field?|||Jeffrae wrote:
> Hello,
> For some reason all of the suden any subscription I make will not run..
> The status just stays at "New Subscription". I made a test schedule
> that runs every 5 minutes.
> I have even added old subscriptions to this schedule and I get email
> every 5 minutes. Why would Any new subscription I make not be running?
> I don't even get an error message under the status heading in the
> Subscription list.
> Thanks,
> Jeff
Microsoft SQL Server 2000.

new sql tool, 'Diana', is here

Hello,
I wanted to bring to your attention a new helpful SQL tool that's coming
out. It enables you to run a search & replace throughout your entire TSQL
code - Stored procedures, Functions, and Triggers. In addition, it helps
you find the database entities you are looking for, across databases and
servers using multiple criteria, and then presents search results in a very
intuitive, yet powerful format. A 30 day trial is available for free.
Download your own copy at:
via=forum%20ms%20sql%20server" target="_blank">http://www.nobhillsoft.com/EnterCus...%20sql%20server
And have a productive time!
Nob Hill Software
www.nobhillsoft.comDoes anyone know this person and/or company? Is this a honest posting?
TIA
"News.microsoft.com" wrote:

> Hello,
> I wanted to bring to your attention a new helpful SQL tool that's coming
> out. It enables you to run a search & replace throughout your entire TSQL
> code - Stored procedures, Functions, and Triggers. In addition, it helps
> you find the database entities you are looking for, across databases and
> servers using multiple criteria, and then presents search results in a ver
y
> intuitive, yet powerful format. A 30 day trial is available for free.
>
> Download your own copy at:
>
> d_via=forum%20ms%20sql%20server" target="_blank">http://www.nobhillsoft.com/EnterCus...%20sql%20server
>
> And have a productive time!
>
> Nob Hill Software
> www.nobhillsoft.com
>
>|||Has been reported as SPAM to newsspam@.microsoft.com
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
news:25AF797D-642E-43CE-AB6C-33F5623CFAFC@.microsoft.com...[vbcol=seagreen]
> Does anyone know this person and/or company? Is this a honest posting?
> TIA
> "News.microsoft.com" wrote:
>
TSQL[vbcol=seagreen]
helps[vbcol=seagreen]
very[vbcol=seagreen]
0sql%20server" target="_blank">http://www.nobhillsoft.com/EnterCus...
0sql%20server[vbcol=seagreen]|||I've been using Diana for the past few weeks, and it is as super cool as
advertised! A real time saver and a nice looking gui too!
I recommend to all heavy MSSQL users, the sql compare feature is a real
helper!
Ron
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

new sql tool, 'Diana', is here

Hello,
I wanted to bring to your attention a new helpful SQL tool that's coming
out. It enables you to run a search & replace throughout your entire TSQL
code - Stored procedures, Functions, and Triggers. In addition, it helps
you find the database entities you are looking for, across databases and
servers using multiple criteria, and then presents search results in a very
intuitive, yet powerful format. A 30 day trial is available for free.
Download your own copy at:
http://www.nobhillsoft.com/EnterCust...20sql%20server
And have a productive time!
Nob Hill Software
www.nobhillsoft.com
Does anyone know this person and/or company? Is this a honest posting?
TIA
"News.microsoft.com" wrote:

> Hello,
> I wanted to bring to your attention a new helpful SQL tool that's coming
> out. It enables you to run a search & replace throughout your entire TSQL
> code - Stored procedures, Functions, and Triggers. In addition, it helps
> you find the database entities you are looking for, across databases and
> servers using multiple criteria, and then presents search results in a very
> intuitive, yet powerful format. A 30 day trial is available for free.
>
> Download your own copy at:
>
> http://www.nobhillsoft.com/EnterCust...20sql%20server
>
> And have a productive time!
>
> Nob Hill Software
> www.nobhillsoft.com
>
>
|||Has been reported as SPAM to newsspam@.microsoft.com
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
news:25AF797D-642E-43CE-AB6C-33F5623CFAFC@.microsoft.com...[vbcol=seagreen]
> Does anyone know this person and/or company? Is this a honest posting?
> TIA
> "News.microsoft.com" wrote:
TSQL[vbcol=seagreen]
helps[vbcol=seagreen]
very[vbcol=seagreen]
http://www.nobhillsoft.com/EnterCust...20sql%20server[vbcol=seagreen]
|||I've been using Diana for the past few weeks, and it is as super cool as
advertised! A real time saver and a nice looking gui too!
I recommend to all heavy MSSQL users, the sql compare feature is a real
helper!
Ron
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
sql

new sql tool, 'Diana', is here

Hello,
I wanted to bring to your attention a new helpful SQL tool that's coming
out. It enables you to run a search & replace throughout your entire TSQL
code - Stored procedures, Functions, and Triggers. In addition, it helps
you find the database entities you are looking for, across databases and
servers using multiple criteria, and then presents search results in a very
intuitive, yet powerful format. A 30 day trial is available for free.
Download your own copy at:
http://www.nobhillsoft.com/EnterCust_download.php?prod_id=1&prod_name=diana&heard_via=forum%20ms%20sql%20server
And have a productive time!
Nob Hill Software
www.nobhillsoft.comHas been reported as SPAM to newsspam@.microsoft.com
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
news:25AF797D-642E-43CE-AB6C-33F5623CFAFC@.microsoft.com...
> Does anyone know this person and/or company? Is this a honest posting?
> TIA
> "News.microsoft.com" wrote:
> > Hello,
> >
> > I wanted to bring to your attention a new helpful SQL tool that's coming
> > out. It enables you to run a search & replace throughout your entire
TSQL
> > code - Stored procedures, Functions, and Triggers. In addition, it
helps
> > you find the database entities you are looking for, across databases and
> > servers using multiple criteria, and then presents search results in a
very
> > intuitive, yet powerful format. A 30 day trial is available for free.
> >
> >
> >
> > Download your own copy at:
> >
> >
> >
> >
http://www.nobhillsoft.com/EnterCust_download.php?prod_id=1&prod_name=diana&heard_via=forum%20ms%20sql%20server
> >
> >
> >
> > And have a productive time!
> >
> >
> >
> > Nob Hill Software
> >
> > www.nobhillsoft.com
> >
> >
> >

New SQL Server.....

I have just goten in my gruby little hands a brand spanking new server to run SQL on. Im talking about a beast of a box with 8 processers and 16 gig of ram. Im loading SQL server enterprise on it and have been raised with a question. The server peps here, "it profesionals" seem to think that i need to keep 2 processors free for the os. from everything that i have read threw my studies of SQL i have seen that that is crap, that SQL will give the os its resources as it needs them. Any thoughts on this and or any Microsoft articles that prove your positions? Any help would be great here thanx
JimFirst have some respect and wash your hands!

Now... If this server is ONLY used for MSSQL server then reserving 2 processers for the OS is stupid. If you run allot of jobs or have software, other than MSQL stuff, running then reserve 1 processer AFTER seeing the remaining 7 running at 100% utilization 100% of the time. You can alter the cpu affinity at any time.

If you follow the recommendations I would be you would see the 2 OS cpus peg 100% about 0.000001% of the time and idle the remaining 99.999999% of the time!

I would ask the "professionals" to defend their point.|||Originally posted by Paul Young
First have some respect and wash your hands!

Now... If this server is ONLY used for MSSQL server then reserving 2 processers for the OS is stupid. If you run allot of jobs or have software, other than MSQL stuff, running then reserve 1 processer AFTER seeing the remaining 7 running at 100% utilization 100% of the time. You can alter the cpu affinity at any time.

If you follow the recommendations I would be you would see the 2 OS cpus peg 100% about 0.000001% of the time and idle the remaining 99.999999% of the time!

I would ask the "professionals" to defend their point.

Im with u on this one. When i set this thing up its gona go full throtel :)
thanx
any more feed back would also be nice
Regards Jim

New SQL Server 2005 Installation

I have just purchased SQL Server 2005 and a new Dell 2800 server to run it on. I was told by my Dell rep to configure the server with the following drives. 2 - 36GB drives in a RAID 0 (15,000 RPM), 2 - 73GB drives in a RAID 0 (10,000 RPM) and 3 - 146GB drives in a RAID 5 (10,000 RPM). Dell pre-installed the OS on the 36GB drive. I was told to install SQL on the 73GB drives and the data (primarily TIFF images) on the 146GB. The server is a 64bit machine with Windows 2003 Server and 4GB of RAM. I was told to put the transaction logs on a different physical drive that the database to speed the writes. Is this a correct installation? Is there any information on a best practice installation? I have spent about 6 hours on MS and Google trying to find an answer. Thanks for any help you can give. Recap of Dell recommendation:

2 - 36GB Mirrored Drives = OS

2 - 73BG Mirrored Drives = SQL Server 2005

3 - 146GB RAID 5 Drives = Data (Images)

Is this correct?

Where should the Transaction Logs Be?

Because transaction logging is a mostly write operation the most efficient place for them is on a mirrored drive set. I usually install SQL Server executables on the same drive as the operating system. (The network admins here configure 10GB for the OS and SQL Server, and it's always been plenty.) I place the data files on the RAID array, and the transaction logs on the mirrored set. When you're running setup specify the RAID drive for the data and the 36GB mirror set for the program files, then after installation set your default location for the log files to the 73GB mirror set and you should be all set.

New setup of MySQL on my machine....

Hello friends, I am a begginer in MySQL. Kindly guide me how can I
install and run MySQL server on my machine (Runnig Wnidows XP
professional and SQL server 2000). Is it neccessary to remove SQL
server from the machine before installing MySQL and what version of IIS
is required? Kindly help me in this regard, it will be appreciated.
Thanks in advance.This is an MSSQL (Microsoft SQL Server) group, so I guess you will get
a better response in a MySQL forum.

Simon

New Pull Subscription - Trans Repli - Use old snapshot...

How do I created a new Pull subscription from a transaction replication
publisher without having to run the Sanpshot again?
When I create a new Pull Subscription, it says "Snapshot not ready". After I
run Sanpshot, then it starts.
Running the snapshot forces me to take the production server down for 2
hours, and can not always do this.
Thanks and God Bless,
ThomBeaux
use the concurrent snapshot option. God bless u2.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ThomBeaux" <ThomBeaux@.NoSpam.Com> wrote in message
news:60B3F87F-ED59-4030-B30B-E61B145754C5@.microsoft.com...
> How do I created a new Pull subscription from a transaction replication
> publisher without having to run the Sanpshot again?
> When I create a new Pull Subscription, it says "Snapshot not ready". After
> I
> run Sanpshot, then it starts.
> Running the snapshot forces me to take the production server down for 2
> hours, and can not always do this.
> --
> Thanks and God Bless,
> ThomBeaux
|||What part on the Wizard asks for for the concurrent snapshot optin
Thanks and God Bless,
ThomBeaux
"Hilary Cotter" wrote:

> use the concurrent snapshot option. God bless u2.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "ThomBeaux" <ThomBeaux@.NoSpam.Com> wrote in message
> news:60B3F87F-ED59-4030-B30B-E61B145754C5@.microsoft.com...
>
>
|||As well as Hilary's advice, if you are using SQL Server 2005 there is the
option to "initialize with backup". In the CTP SP2 there is also the option
of 'database snapshot' for the sync type AFAIR.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||after creating your publication right click on it and go to the snapshot
tab, you should find it there. Its something about minimize locking during
snapshot generation.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ThomBeaux" <ThomBeaux@.NoSpam.Com> wrote in message
news:EDD53985-2C00-4151-8902-C08AAC5CEE52@.microsoft.com...[vbcol=seagreen]
> What part on the Wizard asks for for the concurrent snapshot optin
> --
> Thanks and God Bless,
> ThomBeaux
>
> "Hilary Cotter" wrote:

New Processes after upgrade

Hi There

After upgrading to Sql Server 2005 Enterprise Edition , SP1.

if i run the following query:

select * from sysprocesses where waittime <> 0 order by waittime desc

I get the following result:

spid waittime lastwaittype status

10 4480 0 0x007E 78424187 ONDEMAND_TASK_QUEUE background
4 5620 0 0x009D 78418906 KSOURCE_WAKEUP background
16 4512 0 0x00A9 78418890 BROKER_TRANSMITTER background
17 4516 0 0x00A9 78418875 BROKER_TRANSMITTER background
12 5056 0 0x00AD 78418796 BROKER_EVENTHANDLER background
11 5728 0 0x0081 179140 CHECKPOINT_QUEUE background

Basscially the waittime just keeps going up infinitely for these processes, is this normal, should i be worried ?

Thanx

Have same issue with BROKER_EVENTHANDLER and BROKER_TRANSMITTER.

Normal behavior?

|||

Hi

This link from microsoft describes the various Wait Types (of which there a lot more in SQL Server 2005):

http://msdn2.microsoft.com/en-us/library/ms179984.aspx

Here is some information from it about your specific items.

ONDEMAND_TASK_QUEUE waits for high priority requests (and high wait times simply indicates there have been none of these.)

KSOURCE_WAKEUP waits for requests from the Service Control Manager and long waits are to be expected (pause then unpause the SQL Server service to cause this to reset).

CHECKPOINT_QUEUE - This is the Checkpoint task waiting for the next checkpoint request (few checkpoints = large wait - and that's only 3 minutes).

BROKER_* - These are associated with the service broker and whilst it does not specifically say, I suspect if you do not have service broker servicing requests then these just wait and wait.

If you are interested in monitoring blocking then you might like to read this knowledge base article:

http://support.microsoft.com/kb/271509 - (LiveID required)

This describes sp_blocker_pss80 - a stored procedure to monitor blocked processes in SQL server 2000 up (it does however still produce these system processes in it's list).

New Log or append?

I'm backing into the DBA position from being an app / web designer. Using SQL 2005 and hating wizards, I'm trying to code a BACKUP LOG query to run every 15 minutes and create a separate date-time-stamped TRN. I have several questions starting with - should I be doing this? Is it better to create separate LOGs or append to one? Isn't it true that the only time I'll need the LOGs is when I have a crash and that then I'll need to RESTORE in date-time-order?

I'm doing a FULL backup every 6 hours and intend on automating deleting the TRNs on a successful completion of the BAK.

The following code runs fine and creates a date-time-stamped TRN but doesn't do what I want, as it overwrites the first TRN, so how do I create, say, four JOBSTEPs and get a JOBSTEP @.COMMAND to run the SETs?

USE msdb

IFEXISTS(SELECTNameFROM sysjobs WHEREName='jobDBBackupWedb_1Log')

EXECsp_delete_job @.job_name ='jobDBBackupWedb_1Log'

GO

DECLARE @.now char(14)-- current date in the form of yyyymmddhhmmss

DECLARE @.dbName sysname-- database name to include date-time-stamp

DECLARE @.Cmd nvarchar(260)-- variable used to build JobStep command

SET @.now =REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50),GETDATE(), 120),'-',''),' ',''),':','')

SET @.dbName ='Wedb_1_'+ @.now +'_.trn'

SET @.Cmd ='BACKUP LOG Wedb_1 TO DISK = '+CHAR(39)+'\\SERVER1\D$\SQLDatabases\Backup\WebEoc\'+ @.dbName +CHAR(39)

EXECsp_add_job

@.Job_Name ='jobDBBackupWedb_1Log',

@.Description ='Run Wedb_1 DB Backup Transaction Log Every 15 Minutes',

@.Category_Name ='Database Maintenance'

EXECsp_add_jobstep

@.Job_Name ='jobDBBackupWedb_1Log',

@.step_name ='DB_Backup_Log',

@.step_id = 1,

@.Database_Name ='Master',

@.subsystem ='TSQL',

@.command = @.Cmd

/*

Start time for the Transaction Log backups is seven and one half minutes after midnight

to not conflict with the Full database backups run every 6 hours starting at midnight.

The Transaction Log backups are to be run every fifteen minutes.

*/

EXECsp_Add_JobSchedule

@.Job_Name ='jobDBBackupWedb_1Log',

@.Name ='schedDB_BackupLog',

@.Freq_Type = 4,-- Daily

@.Freq_Interval = 1,-- Each day

@.Freq_Subday_Type = 0x4,-- Interval in minutes

@.Freq_Subday_Interval = 15,-- Every fifteen minutes

@.Active_Start_Time = 000730 -- Start Time is seven and one half minutes after midnight

--@.Active_End_Time = 235959 -- End Time is next midnight

EXECsp_Add_JobServer

@.Job_Name ='jobDBBackupWedb_1Log'

GO

My opinion, I'd create DB Maint Plans to do database and trans log backups, and not write my own, unless there is some real good reason to. Main plans aren't perfect, but still better then trying to reproduce the same end result. One of the points of backing up the trans log is for disaster recovery. You could backup your trans log every 30 minutes, then copy/move those backups off to a different server/tape, etc... So, I wouldn't stack them on one file, becuase you need to move them continually to a different box, or yea, backup cross server, ehhhh.... I prefer local backups and then copying them somewhere else, like with RoboCopy. The times you nee the trans logs are for recovery, or to do log shipping to a different server (similar concept to database mirroring, but still more in use then mirroring is I think). If doing a full backup every 6 hours, same thing, back it up and move it off somewhere safe, not leave that on teh same drives as your database files! I'd still leave the old TRN files on teh other location, there COULD be a case where you need to go back 9 hours, then you'd have the old trans and backups to get there, I wouldnt delete the TRN files every 6 hours as you said.

Bruce

|||

Thanks ... but ...

I'm backing up to a separate / different network drive on a different / separate server that my network admin has established as a SANs - Storage Area Network - drive. That SANs drive is data specific and is being snap-shot-backed-up every 5 minutes. Then it goes to tape every night at 11:00 PM.

The nature of the DB is that the WebEoc is for running the SEOC - State Emergency Operations Center - during hurricanes, earthquakes, etc.

|||

I think that the problem with your original script is that you are creating the backup command when you create the job rather than dynamically creating and executing the command when executing the job.

Try the re-worked version below.

Chris

Code Snippet

USE msdb

IF EXISTS ( SELECT Name
FROM sysjobs
WHERE Name = 'jobDBBackupWedb_1Log' )
EXEC sp_delete_job @.job_name = 'jobDBBackupWedb_1Log'

GO

DECLARE @.cmd NVARCHAR(3201)

SET @.cmd = N'DECLARE @.sql VARCHAR(8000)

DECLARE @.now char(14)
-- current date in the form of yyyymmddhhmmss

DECLARE @.dbName sysname
-- database name to include date-time-stamp

SET @.now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), ''-'',
''''), '' '', ''''), '':'', '''')

SET @.dbName = ''Wedb_1_'' + @.now + ''_.trn''

SET @.sql = ''BACKUP LOG Wedb_1 TO DISK = '' + CHAR(39)
+ ''\\SERVER1\D$\SQLDatabases\Backup\WebEoc\'' + @.dbName + CHAR(39)

EXEC(@.sql)'
EXEC sp_add_job @.Job_Name = 'jobDBBackupWedb_1Log',
@.Description = 'Run Wedb_1 DB Backup Transaction Log Every 15 Minutes',
@.Category_Name = 'Database Maintenance'

EXEC sp_add_jobstep @.Job_Name = 'jobDBBackupWedb_1Log',
@.step_name = 'DB_Backup_Log', @.step_id = 1, @.Database_Name = 'Master',
@.subsystem = 'TSQL', @.command = @.Cmd

/*

Start time for the Transaction Log backups is seven and one half minutes after midnight

to not conflict with the Full database backups run every 6 hours starting at midnight.

The Transaction Log backups are to be run every fifteen minutes.

*/

EXEC sp_Add_JobSchedule @.Job_Name = 'jobDBBackupWedb_1Log',
@.Name = 'schedDB_BackupLog', @.Freq_Type = 4, -- Daily
@.Freq_Interval = 1, -- Each day
@.Freq_Subday_Type = 0x4, -- Interval in minutes
@.Freq_Subday_Interval = 15, -- Every fifteen minutes
@.Active_Start_Time = 000730
-- Start Time is seven and one half minutes after midnight

--@.Active_End_Time = 235959 -- End Time is next midnight

EXEC sp_Add_JobServer @.Job_Name = 'jobDBBackupWedb_1Log'

GO

|||

Chris,

Thank you very kindly! I take your comment about run-time, create/execute; that was spot on. Thanks for the code change - works like a charm ...

... but, I've looked and not found an answer, what is the "N" doing for me in SET @.cmd = N'DECLARE ... ?

Is it declaring that what follows is all an NVARCHAR string?