database maintenance plan question

What type of backup does the SQL maintenance plan wizard use? Complete,
differencial?
Thanks
John
When using a Maintenance Plan to perform a Backup the SQLMAINT utility is
called with the -BkUpDB switch. This switch performs a full database backup,
backing up the entire database.
- Peter Ward
WARDY IT Solutions
"JP Breton" wrote:

> What type of backup does the SQL maintenance plan wizard use? Complete,
> differencial?
> Thanks
> John
>
>
>
|||Thanks for the quick reply P
Regards
JP
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:21181E36-3453-4FCB-8F12-D49BC1DBD200@.microsoft.com...[vbcol=seagreen]
> When using a Maintenance Plan to perform a Backup the SQLMAINT utility is
> called with the -BkUpDB switch. This switch performs a full database
> backup,
> backing up the entire database.
> - Peter Ward
> WARDY IT Solutions
> "JP Breton" wrote:
|||Maint Wiz supports Log and Db backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JP Breton" <jpbreton@.videotron.ca> wrote in message news:uBFiOUkPFHA.2144@.TK2MSFTNGP09.phx.gbl...
> What type of backup does the SQL maintenance plan wizard use? Complete, differencial?
> Thanks
> John
>
>

database maintenance plan question

What type of backup does the SQL maintenance plan wizard use? Complete,
differencial?
Thanks
JohnWhen using a Maintenance Plan to perform a Backup the SQLMAINT utility is
called with the -BkUpDB switch. This switch performs a full database backup
,
backing up the entire database.
- Peter Ward
WARDY IT Solutions
"JP Breton" wrote:

> What type of backup does the SQL maintenance plan wizard use? Complete,
> differencial?
> Thanks
> John
>
>
>|||Thanks for the quick reply P
Regards
JP
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:21181E36-3453-4FCB-8F12-D49BC1DBD200@.microsoft.com...[vbcol=seagreen]
> When using a Maintenance Plan to perform a Backup the SQLMAINT utility is
> called with the -BkUpDB switch. This switch performs a full database
> backup,
> backing up the entire database.
> - Peter Ward
> WARDY IT Solutions
> "JP Breton" wrote:
>|||Maint Wiz supports Log and Db backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JP Breton" <jpbreton@.videotron.ca> wrote in message news:uBFiOUkPFHA.2144@.TK2MSFTNGP09.phx.
gbl...
> What type of backup does the SQL maintenance plan wizard use? Complete, di
fferencial?
> Thanks
> John
>
>

database maintenance plan question

What type of backup does the SQL maintenance plan wizard use? Complete,
differencial?
Thanks
JohnWhen using a Maintenance Plan to perform a Backup the SQLMAINT utility is
called with the -BkUpDB switch. This switch performs a full database backup,
backing up the entire database.
- Peter Ward
WARDY IT Solutions
"JP Breton" wrote:
> What type of backup does the SQL maintenance plan wizard use? Complete,
> differencial?
> Thanks
> John
>
>
>|||Thanks for the quick reply P
Regards
JP
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:21181E36-3453-4FCB-8F12-D49BC1DBD200@.microsoft.com...
> When using a Maintenance Plan to perform a Backup the SQLMAINT utility is
> called with the -BkUpDB switch. This switch performs a full database
> backup,
> backing up the entire database.
> - Peter Ward
> WARDY IT Solutions
> "JP Breton" wrote:
>> What type of backup does the SQL maintenance plan wizard use? Complete,
>> differencial?
>> Thanks
>> John
>>
>>|||Maint Wiz supports Log and Db backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JP Breton" <jpbreton@.videotron.ca> wrote in message news:uBFiOUkPFHA.2144@.TK2MSFTNGP09.phx.gbl...
> What type of backup does the SQL maintenance plan wizard use? Complete, differencial?
> Thanks
> John
>
>

Database Logs are being truncated

Hi,
Can someone help me with a SQL error that I'm getting in my event log. The
error is listed below.
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (6)
Event ID: 17055
Date: 12/15/2003
Time: 12:03:47 AM
User: N/A
Computer: SQL-SERVER
Description:
18278 :
Database log truncated: Database: model.
Data:
0000: 66 47 00 00 10 00 00 00 fG.....
0008: 0b 00 00 00 53 00 51 00 ...S.Q.
0010: 4c 00 2d 00 53 00 45 00 L.-.S.E.
0018: 52 00 56 00 45 00 52 00 R.V.E.R.
0020: 00 00 06 00 00 00 6d 00 .....m.
0028: 6f 00 64 00 65 00 6c 00 o.d.e.l.
0030: 00 00 ..
This is happening on 4 of my databases. 2 created by me, Crystal Reports
database (CE8), and the model database.
I'm backing up all db's and the logs for the 4 db's mentioned above full
every day. This problem has been happening long before I decided to backup
the logs though. I thought backing up the logs would fix this problem. The
4 db's above all have Recovery set to "Full", because log backups were
failing when Recovery was set to "Simple" and the error suggested that
recovery be set to something other than "Simple".
I increased the size of the db's and logs to 100 meg, but that didn't help.
Also the size kept going back to a small number. Now I set the databases to
grow automatically, but I set the maximum size allowed to grow to 100 meg
for both db's and logs. The db's are between 2 to 5 megs though.
We write about 100 - 200 lines of data to the server every day. We don't
use Crystal Reports anymore and I'm not sure what the model db is for. The
db grows very slowly.
Thanks!!!
DaveRegarding the error you see, check this article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;818202&Product=sql2k
replies in line.
Steve
"Dave" <none@.none.com> wrote in message
news:O06b7MMxDHA.1596@.TK2MSFTNGP10.phx.gbl...
> Can someone help me with a SQL error that I'm getting in my event log.
The
> error is listed below.
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (6)
> Event ID: 17055
> Date: 12/15/2003
> Time: 12:03:47 AM
> User: N/A
> Computer: SQL-SERVER
> Description:
> 18278 :
> Database log truncated: Database: model.
> Data:
> 0000: 66 47 00 00 10 00 00 00 fG.....
> 0008: 0b 00 00 00 53 00 51 00 ...S.Q.
> 0010: 4c 00 2d 00 53 00 45 00 L.-.S.E.
> 0018: 52 00 56 00 45 00 52 00 R.V.E.R.
> 0020: 00 00 06 00 00 00 6d 00 .....m.
> 0028: 6f 00 64 00 65 00 6c 00 o.d.e.l.
> 0030: 00 00 ..
> This is happening on 4 of my databases. 2 created by me, Crystal Reports
> database (CE8), and the model database.
> I'm backing up all db's and the logs for the 4 db's mentioned above full
> every day. This problem has been happening long before I decided to
backup
> the logs though. I thought backing up the logs would fix this problem.
The
> 4 db's above all have Recovery set to "Full", because log backups were
> failing when Recovery was set to "Simple" and the error suggested that
> recovery be set to something other than "Simple".
> I increased the size of the db's and logs to 100 meg, but that didn't
help.
> Also the size kept going back to a small number. Now I set the databases
to
> grow automatically, but I set the maximum size allowed to grow to 100 meg
> for both db's and logs. The db's are between 2 to 5 megs though.
> We write about 100 - 200 lines of data to the server every day. We don't
> use Crystal Reports anymore and I'm not sure what the model db is for. Th
e
> db grows very slowly.
There is no reason to set the recovery mode on model to full, simple would
be fine. Model is used as a template when you create new databases.
Steve|||Hi Steve,
Thanks for article. Here's the code for the log backups. It looks like
that I don't have the NO_LOG or the TRUNCATE_ONLY options set. I changed
the recover mode of the databases to bulk logged. I'll see if that helps.
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
E79DF926-E729-4FA2-8769-62EE4F86120E -Rpt "C:\Program Files\Microsoft SQL
Server\MSSQL\LOG\DB Maintenance Plan - Log Backups6.txt" -DelTxtRpt
2WEEKS -WriteHistory -VrfyBackup -BkUpMedia
ISK -BkUpLog -UseDefDir -DelBkUps 2WEEKS -CrBkSubDir -BkExt "TRN"'
Thanks!!!|||Hi Dave,
Let us know if that helps... if it doesn't we can give you some alternatives
to maintenance plans.
Steve
"Dave" <none@.none.com> wrote in message
news:#$gd3DNxDHA.2156@.TK2MSFTNGP09.phx.gbl...
> Hi Steve,
> Thanks for article. Here's the code for the log backups. It looks like
> that I don't have the NO_LOG or the TRUNCATE_ONLY options set. I changed
> the recover mode of the databases to bulk logged. I'll see if that helps.
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> E79DF926-E729-4FA2-8769-62EE4F86120E -Rpt "C:\Program Files\Microsoft SQL
> Server\MSSQL\LOG\DB Maintenance Plan - Log Backups6.txt" -DelTxtRpt
> 2WEEKS -WriteHistory -VrfyBackup -BkUpMedia
> ISK -BkUpLog -UseDefDir -DelBkUps 2WEEKS -CrBkSubDir -BkExt "TRN"'
> Thanks!!!
>|||Still getting the error after I set the recovery mode to bulk logged.
Is it possible that my space allocation for the logs isn't working right?
Here's what my setup looks like for one of my db's that's generating the
error.
&Transaction Log Files
Space Allocated = 3Mb
File Properties
Automatically Grow File = Checked
In Megabytes = 3
Restrict File Growth (MB) = 100
What else can I look at?
Thanks!
Dave|||Sometimes autogrow doesn't seem to "catch up". I suggest that you pre-allocate storage.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dave" <none@.none.com> wrote in message news:OudZx1YxDHA.2328@.TK2MSFTNGP10.phx.gbl...
> Still getting the error after I set the recovery mode to bulk logged.
> Is it possible that my space allocation for the logs isn't working right?
> Here's what my setup looks like for one of my db's that's generating the
> error.
> &Transaction Log Files
> Space Allocated = 3Mb
> File Properties
> Automatically Grow File = Checked
> In Megabytes = 3
> Restrict File Growth (MB) = 100
> What else can I look at?
> Thanks!
> Dave
>|||I agree with Tibor -- pre-allocate the database and transaction log size to
the estimated total size needed. Also, consider setting the recovery mode to
'simple' for the system databases; master, msdb, model & tempdb. Do place
the first three on a full backup cycle though...
Steve
"Dave" <none@.none.com> wrote in message
news:OudZx1YxDHA.2328@.TK2MSFTNGP10.phx.gbl...
> Still getting the error after I set the recovery mode to bulk logged.
> Is it possible that my space allocation for the logs isn't working right?
> Here's what my setup looks like for one of my db's that's generating the
> error.
> &Transaction Log Files
> Space Allocated = 3Mb
> File Properties
> Automatically Grow File = Checked
> In Megabytes = 3
> Restrict File Growth (MB) = 100
> What else can I look at?
> Thanks!
> Dave
>|||I have tried the following in the past and have not had success.
Under database properties->Transaction Log->&Transaction Log Files I
increased the Space Allocated (MB) field to 100 MB. It seemed to work, but
when I checked this again the next day, the space allocated value was down
to a small number (3 MB). Am I doing this right?
Thanks!!!
Dave|||Hi Steve,
I set model back to Simple recovery mode so I have all databases on Simple
recovery mode except for the 2 that we created and the Crystal Reports db.
I noticed something strange in the event log that I hadn't noticed before.
My backup schedule for the databases(not the logs) was at 5am, but the event
log says they're being backed up at around 12 am and this is when the errors
are occuring. The backups excecute again again at 5am successfully with no
event log errors. The logs get backed up successfully on schedule at 6:00
am. I'm guessing that the 12 o'clock backups are caused by our tape backup
software(Tapeware) which has a sql server connector option that we bought.
The Tapeware backups are scheduled at 12:01am every day. This is the only
thing I can think of that would cause the backups at around 12am.
Anyway, I'm going to disable the sql backups on Tapeware for a day and see
if I still get errors.
Should I not back up the logs for model? Simple recovery models cause log
backup errors.
Thanks!!!
Dave|||> Should I not back up the logs for model?
I'd say its overkill.
> Simple recovery models cause log
> backup errors.
IF you set model to simple, then don't do log backups for model, quite
simply.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dave" <none@.none.com> wrote in message
news:u8j09FlxDHA.2568@.TK2MSFTNGP09.phx.gbl...
> Hi Steve,
> I set model back to Simple recovery mode so I have all databases on Simple
> recovery mode except for the 2 that we created and the Crystal Reports db.
> I noticed something strange in the event log that I hadn't noticed before.
> My backup schedule for the databases(not the logs) was at 5am, but the
event
> log says they're being backed up at around 12 am and this is when the
errors
> are occuring. The backups excecute again again at 5am successfully with
no
> event log errors. The logs get backed up successfully on schedule at 6:00
> am. I'm guessing that the 12 o'clock backups are caused by our tape
backup
> software(Tapeware) which has a sql server connector option that we bought.
> The Tapeware backups are scheduled at 12:01am every day. This is the only
> thing I can think of that would cause the backups at around 12am.
> Anyway, I'm going to disable the sql backups on Tapeware for a day and see
> if I still get errors.
> Should I not back up the logs for model? Simple recovery models cause log
> backup errors.
> Thanks!!!
> Dave
>
>|||Thanks Tibor,
I disabled our tape backup software sql server backup function and the
errors went away.
My maintenance plan backups are working without any errors.
I'm going to assume that the sql server is working fine even though I'm
getting the log truncated errors. I don't think I can do anything to fix
that. Is this a good assumtion?
Thanks!!!

Database log truncated: Database: MyDatabase.

Hi,

My Database recovery model is set to Bulk Logged. I recently got this error in the Application Event Log.

EVENT LOG Application
EVENT TYPE Error
SOURCE MSSQLSERVER
CATEGORY Backup
EVENT ID 17055
MESSAGE 18278 :
Database log truncated: Database: MyDatabase.

I've checked the MS Knowledgebase, however article 818202 relates to Full Recovery model not Bulk Logged. I would like to know why this error has occurred and what it really means.

Cheers

DamianThis is not an error, it's just information only message. It's no different than when you issue a Truncate_only log backup.

Database log truncated: Database: MyDatabase.

Hi,
My Database recovery model is set to Bulk Logged. I
recently got this error in the Application Event Log.
EVENT LOG Application
EVENT TYPE Error
SOURCE MSSQLSERVER
CATEGORY Backup
EVENT ID 17055
MESSAGE 18278 :
Database log truncated: Database: MyDatabase.
I've checked the MS Knowledgebase, however article 818202
relates to Full Recovery model not Bulk Logged. I would
like to know why this error has occurred and what it really
means.
Cheers
DamianIt means you (or someone) did a BACKUP LOG WITH TRUNCATE_ONLY or =similar statement. And the log files can not be used for recovery until =the next full db backup is taken.
Mike John
"Damian Jolly" <damian.jolly@.sageautomation.com> wrote in message =news:38fe01c3c8dc$ba792e10$7d02280a@.phx.gbl...
> Hi,
> > My Database recovery model is set to Bulk Logged. I
> recently got this error in the Application Event Log.
> > EVENT LOG Application > EVENT TYPE Error > SOURCE MSSQLSERVER > CATEGORY Backup > EVENT ID 17055 > MESSAGE 18278 :
> Database log truncated: Database: MyDatabase.
> > I've checked the MS Knowledgebase, however article 818202
> relates to Full Recovery model not Bulk Logged. I would
> like to know why this error has occurred and what it really
> means.
> > Cheers
> > Damian|||To add to Mike's response, I ran the repro script in the MSKB article but
with the recovery model set to BULK_LOGGED. The error also appeared in the
log with BULK_LOGGED recovery.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Damian Jolly" <damian.jolly@.sageautomation.com> wrote in message
news:38fe01c3c8dc$ba792e10$7d02280a@.phx.gbl...
> Hi,
> My Database recovery model is set to Bulk Logged. I
> recently got this error in the Application Event Log.
> EVENT LOG Application
> EVENT TYPE Error
> SOURCE MSSQLSERVER
> CATEGORY Backup
> EVENT ID 17055
> MESSAGE 18278 :
> Database log truncated: Database: MyDatabase.
> I've checked the MS Knowledgebase, however article 818202
> relates to Full Recovery model not Bulk Logged. I would
> like to know why this error has occurred and what it really
> means.
> Cheers
> Damian

database load balancing using sql clustering

can sql server 2000 database load balancing be achived by using sql clustering?
Say an active/active/passive type set up?
Thank you.
No, clustering is a hardware fail over solution only. There are no true
load balancing solutions for sql server at the moment. If you had strictly
read only data you could use NLB to point to multiple servers but falls
apart when you get into updates and such. You can use Distributed
Partitioned Views across servers but it has nothing to do with clustering
and is still not a true load balancing solution. SQL Server works best as a
scale up solution not a scale out.
Andrew J. Kelly SQL MVP
"RobinMC" <RobinMC@.discussions.microsoft.com> wrote in message
news:F09CE8B4-4140-441E-A801-038AB6B34DFD@.microsoft.com...
> can sql server 2000 database load balancing be achived by using sql
clustering?
> Say an active/active/passive type set up?
> Thank you.
|||With SQL 2000 clustering each instance runs on a single machine, so the
Databases are not load balanced in any way.
You can A/A/P cluster, but each server would have its own instance or two.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
"RobinMC" <RobinMC@.discussions.microsoft.com> wrote in message
news:F09CE8B4-4140-441E-A801-038AB6B34DFD@.microsoft.com...
> can sql server 2000 database load balancing be achived by using sql
clustering?
> Say an active/active/passive type set up?
> Thank you.

Database level setting : Allow user input chinese character~

Dear all,

To allow users enter chinese character into table, I did try to change the field type to nchar and this is workable. But I have few hundred tables.. Is there anyway to change the setting in easier way? by instead change the field type one by one for each table?

I tried change the collation to Chinese_PRC_90_BIN for the database, but it is not support chinese input..

Any Idea?

Thanks.

You will have to ALTER each table, changing the datatype from char()/varchar() to nchar()/nvarchar() on each column that will contain Chinese characters.

There is a undocumented system stored procedure, sp_MSForEachTable that may be useful in combination with using dynamic SQL.

There have been recent postings related to a similar issue, perhaps a search in the forums for 'sp_MSForEachTable', as well as a Google? search would also be useful.

|||

Hi,

you can use the following script to change every char / varchar column to contain unicode data. The statement will create ALTER statements which can be examined (in the case that you do not want to change every columns and want to sort out the appropiate ones)

SELECT
'ALTER TABLE [' + T.TABLE_NAME + ']' +
' ALTER COLUMN' + '[' + COLUMN_NAME + '] N' + DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(50)) + ');'
FROM [INFORMATION_SCHEMA].[COLUMNS] C
INNER JOIN [INFORMATION_SCHEMA].[TABLES] t
ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND
C.TABLE_NAME = t.TABLE_NAME
WHERE data_type IN ('CHAR','VARCHAR')
AND t.TABLE_TYPE = 'BASE TABLE'

Jens K. Suessmeyer.

http://www.sqlserver2005.de