Resolving function USER

If I log in to Query Analyzer using Windows Auth and run "PRINT USER", it returns "dbo". Is there any way to have the USER function actually return <Domain>\<login> for myself?Look up suser_name and suser_sname.

Resize my LDF file size ! how?

Yes my DB file is 10MB but my LOG file (LDF) is 300MB
is there a way to reduce or compres that one?
thanxTake a backup of database and check the log file size.
|||Run DBCC Shrinkfile SQL Server returns space less what it calls active log. Run a search for DBCC shrinkfile in the BOL(books online) and you can also set up a job to run it as needed. Hope this helps.

reset the log file size

hi ,
the log file of a DB has grown in size. i need to now truncate this file and
reset the log file size.
the db has been configured for unrestricted file growth by 10% for the log
file.
how do i do this.
rgds,
anuAnu,
See if the following helps:
http://support.microsoft.com/defaul...kb;en-us;272318
BG, SQL Server MVP
www.SolidQualityLearning.com
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:A838D0B8-C48A-4B1B-822F-E0773EBDCEBB@.microsoft.com...
> hi ,
> the log file of a DB has grown in size. i need to now truncate this file
> and
> reset the log file size.
> the db has been configured for unrestricted file growth by 10% for the log
> file.
> how do i do this.
> rgds,
> anu|||What recovery model is the database in?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:A838D0B8-C48A-4B1B-822F-E0773EBDCEBB@.microsoft.com...
> hi ,
> the log file of a DB has grown in size. i need to now truncate this file a
nd
> reset the log file size.
> the db has been configured for unrestricted file growth by 10% for the log
> file.
> how do i do this.
> rgds,
> anu

Reset Identity

Hi, there is a way to reset identity field of many tables via storeprocedure
?
i try with the scripts below, but they don't work !
BACKUP LOG test_dbWITH TRUNCATE_ONLY
DBCC shrinkdatabase (test_db)
and also with
create table #table(
idTabella int,
nome varchar(4000)
)
insert into #table
SELECT dbo.sysobjects.id, dbo.sysobjects.name
FROM dbo.sysobjects INNER JOIN
dbo.syscolumns ON dbo.sysobjects.id =
dbo.syscolumns.id INNER JOIN
dbo.systypes ON dbo.syscolumns.xtype =
dbo.systypes.xtype
WHERE (dbo.syscolumns.status = 128)
declare @.NomeTabella as varchar(4000)
declare @.TabellaID int
select @.TabellaID =idTabella,@.NomeTabella =nome from #table
while exists(select idTabella from #table)
begin
DBCC CHECKIDENT(@.NomeTabella, RESEED)
delete from #table where idTabella = @.TabellaID
select @.TabellaID =idTabella,@.NomeTabella =nome from #table
end"Alessandro" schrieb:
> Hi, there is a way to reset identity field of many tables via storeprocedu
re
> ?
> i try with the scripts below, but they don't work !
> BACKUP LOG test_dbWITH TRUNCATE_ONLY
> DBCC shrinkdatabase (test_db)
> and also with
> create table #table(
> idTabella int,
> nome varchar(4000)
> )
> insert into #table
> SELECT dbo.sysobjects.id, dbo.sysobjects.name
> FROM dbo.sysobjects INNER JOIN
> dbo.syscolumns ON dbo.sysobjects.id =
> dbo.syscolumns.id INNER JOIN
> dbo.systypes ON dbo.syscolumns.xtype =
> dbo.systypes.xtype
> WHERE (dbo.syscolumns.status = 128)
> declare @.NomeTabella as varchar(4000)
> declare @.TabellaID int
> select @.TabellaID =idTabella,@.NomeTabella =nome from #table
> while exists(select idTabella from #table)
> begin
> DBCC CHECKIDENT(@.NomeTabella, RESEED)
> delete from #table where idTabella = @.TabellaID
> select @.TabellaID =idTabella,@.NomeTabella =nome from #table
> end
The follwing procedure reseeds all ID-cols in the db. Tables without an
ID-col return an error that you can ignore ...
declare @.table varchar(256)
declare cu cursor for select [name] from sysobjects where xtype = 'U'
open cu
fetch next from cu into @.table
while @.@.fetch_status = 0
begin
dbcc checkident (@.table, RESEED)
fetch next from cu into @.table
end
close cu deallocate cu

required to only move data/log files to new SAN, best method?

Hi, our sql 2000 server is configured in such a way that the sql server
resides on a box called Server_A, and has its data files present on a
storage area network called SAN_OLD. Due to limited space, we have to move
these data files across to a new storage area network called SAN_NEW and
have SERVER_A point to these relocated data files. Currently the data files
are located on D: and the log files on L: (logical drives on SAN_OLD). We
are thinking to place the data files on X: and the logs on Y: on SAN_NEW. So
in effect what we want to do is simply move the data/log files to a new
drive location. The environment uses replication, reporting services, and
analysis services.
How can this be achieved? I prefer to use Query Analyzer for this task. I
have to move all databases to SAN_NEW including the master, model etc. Do I
simply (from within query analyser):
--
- BACKUP PHASE -
--
- Make sure no users are attached to the SQL Server
- stop the SQL server agent
- Using Query Analyzer, perform the following on the MASTER, MODEL and MSDB
databases:
use MASTER
BACKUP DATABASE master
TO DISK = 'X:\backups\master_backup.bak'
-- now perform a backup of the model, msdb and tempdb databases
ALTER DATABASE {model, msdbb} SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
BACKUP DATABASE {model, msdb}
TO DISK = 'X:\backups\{model, msdb}_backup.bak'
--
- RESTORE PHASE -
--
- start command session
- type: net stop MSSQLServer
- start SQL in single user mode: sqlservr -m
- from within SQL Query Analyzer execute:
use MASTER
-- get the logical database and log file names
RESTORE FILELISTONLY
FROM DISK = 'X:\backups\master_backup.bak'
-- restore the data and log files (assuming logical names are
master_data/master_log)
RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
- Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
using RESTORE FILELISTONLY and RESTORE DATABASE <database name>)
- from command line issue: net start MSSQLServer
- restart SQL Server
My questions are:
1. are my steps above ok?
2. how do i make sure that there are no users attached whilst im making the
backups?
3. what do i do with the TEMPDB?
4. when I do the RESTORE on the databases, does this simply restore over the
current attached databases? I.e. does a restore of the master database
simply overwrite the presently attached master database?
5. do I have to detach the databases before performing a database backup?
6. I am guessing i wont have any problems getting replication to kick off
again once i've rstored the databases. Also reporting service and analysis
service will work ok.
Is there anything I need to know prior to this move?
Many thanks.
peterKB article titled 'Moving SQL Server databases to a new location with
Detach/Attach' has very useful informtion.
http://support.microsoft.com/kb/224071/EN-US/
"peter walker" wrote:
> Hi, our sql 2000 server is configured in such a way that the sql server
> resides on a box called Server_A, and has its data files present on a
> storage area network called SAN_OLD. Due to limited space, we have to move
> these data files across to a new storage area network called SAN_NEW and
> have SERVER_A point to these relocated data files. Currently the data files
> are located on D: and the log files on L: (logical drives on SAN_OLD). We
> are thinking to place the data files on X: and the logs on Y: on SAN_NEW. So
> in effect what we want to do is simply move the data/log files to a new
> drive location. The environment uses replication, reporting services, and
> analysis services.
> How can this be achieved? I prefer to use Query Analyzer for this task. I
> have to move all databases to SAN_NEW including the master, model etc. Do I
> simply (from within query analyser):
> --
> - BACKUP PHASE -
> --
> - Make sure no users are attached to the SQL Server
> - stop the SQL server agent
> - Using Query Analyzer, perform the following on the MASTER, MODEL and MSDB
> databases:
> use MASTER
> BACKUP DATABASE master
> TO DISK = 'X:\backups\master_backup.bak'
> -- now perform a backup of the model, msdb and tempdb databases
> ALTER DATABASE {model, msdbb} SET SINGLE_USER
> WITH ROLLBACK IMMEDIATE
> BACKUP DATABASE {model, msdb}
> TO DISK = 'X:\backups\{model, msdb}_backup.bak'
> --
> - RESTORE PHASE -
> --
> - start command session
> - type: net stop MSSQLServer
> - start SQL in single user mode: sqlservr -m
> - from within SQL Query Analyzer execute:
> use MASTER
> -- get the logical database and log file names
> RESTORE FILELISTONLY
> FROM DISK = 'X:\backups\master_backup.bak'
> -- restore the data and log files (assuming logical names are
> master_data/master_log)
> RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
> WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
> MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
> - Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
> using RESTORE FILELISTONLY and RESTORE DATABASE <database name>)
> - from command line issue: net start MSSQLServer
> - restart SQL Server
> My questions are:
> 1. are my steps above ok?
> 2. how do i make sure that there are no users attached whilst im making the
> backups?
> 3. what do i do with the TEMPDB?
> 4. when I do the RESTORE on the databases, does this simply restore over the
> current attached databases? I.e. does a restore of the master database
> simply overwrite the presently attached master database?
> 5. do I have to detach the databases before performing a database backup?
> 6. I am guessing i wont have any problems getting replication to kick off
> again once i've rstored the databases. Also reporting service and analysis
> service will work ok.
> Is there anything I need to know prior to this move?
> Many thanks.
> peter
>
>

required to only move data/log files to new SAN, best method?

Hi, our sql 2000 server is configured in such a way that the sql server
resides on a box called Server_A, and has its data files present on a
storage area network called SAN_OLD. Due to limited space, we have to move
these data files across to a new storage area network called SAN_NEW and
have SERVER_A point to these relocated data files. Currently the data files
are located on D: and the log files on L: (logical drives on SAN_OLD). We
are thinking to place the data files on X: and the logs on Y: on SAN_NEW. So
in effect what we want to do is simply move the data/log files to a new
drive location. The environment uses replication, reporting services, and
analysis services.
How can this be achieved? I prefer to use Query Analyzer for this task. I
have to move all databases to SAN_NEW including the master, model etc. Do I
simply (from within query analyser):
- BACKUP PHASE -
- Make sure no users are attached to the SQL Server
- stop the SQL server agent
- Using Query Analyzer, perform the following on the MASTER, MODEL and MSDB
databases:
use MASTER
BACKUP DATABASE master
TO DISK = 'X:\backups\master_backup.bak'
-- now perform a backup of the model, msdb and tempdb databases
ALTER DATABASE {model, msdbb} SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
BACKUP DATABASE {model, msdb}
TO DISK = 'X:\backups\{model, msdb}_backup.bak'
- RESTORE PHASE -
- start command session
- type: net stop MSSQLServer
- start SQL in single user mode: sqlservr -m
- from within SQL Query Analyzer execute:
use MASTER
-- get the logical database and log file names
RESTORE FILELISTONLY
FROM DISK = 'X:\backups\master_backup.bak'
-- restore the data and log files (assuming logical names are
master_data/master_log)
RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
- Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
using RESTORE FILELISTONLY and RESTORE DATABASE <database name>)
- from command line issue: net start MSSQLServer
- restart SQL Server
My questions are:
1. are my steps above ok?
2. how do i make sure that there are no users attached whilst im making the
backups?
3. what do i do with the TEMPDB?
4. when I do the RESTORE on the databases, does this simply restore over the
current attached databases? I.e. does a restore of the master database
simply overwrite the presently attached master database?
5. do I have to detach the databases before performing a database backup?
6. I am guessing i wont have any problems getting replication to kick off
again once i've rstored the databases. Also reporting service and analysis
service will work ok.
Is there anything I need to know prior to this move?
Many thanks.
peter
KB article titled 'Moving SQL Server databases to a new location with
Detach/Attach' has very useful informtion.
http://support.microsoft.com/kb/224071/EN-US/
"peter walker" wrote:

> Hi, our sql 2000 server is configured in such a way that the sql server
> resides on a box called Server_A, and has its data files present on a
> storage area network called SAN_OLD. Due to limited space, we have to move
> these data files across to a new storage area network called SAN_NEW and
> have SERVER_A point to these relocated data files. Currently the data files
> are located on D: and the log files on L: (logical drives on SAN_OLD). We
> are thinking to place the data files on X: and the logs on Y: on SAN_NEW. So
> in effect what we want to do is simply move the data/log files to a new
> drive location. The environment uses replication, reporting services, and
> analysis services.
> How can this be achieved? I prefer to use Query Analyzer for this task. I
> have to move all databases to SAN_NEW including the master, model etc. Do I
> simply (from within query analyser):
> --
> - BACKUP PHASE -
> --
> - Make sure no users are attached to the SQL Server
> - stop the SQL server agent
> - Using Query Analyzer, perform the following on the MASTER, MODEL and MSDB
> databases:
> use MASTER
> BACKUP DATABASE master
> TO DISK = 'X:\backups\master_backup.bak'
> -- now perform a backup of the model, msdb and tempdb databases
> ALTER DATABASE {model, msdbb} SET SINGLE_USER
> WITH ROLLBACK IMMEDIATE
> BACKUP DATABASE {model, msdb}
> TO DISK = 'X:\backups\{model, msdb}_backup.bak'
> --
> - RESTORE PHASE -
> --
> - start command session
> - type: net stop MSSQLServer
> - start SQL in single user mode: sqlservr -m
> - from within SQL Query Analyzer execute:
> use MASTER
> -- get the logical database and log file names
> RESTORE FILELISTONLY
> FROM DISK = 'X:\backups\master_backup.bak'
> -- restore the data and log files (assuming logical names are
> master_data/master_log)
> RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
> WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
> MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
> - Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
> using RESTORE FILELISTONLY and RESTORE DATABASE <database name>)
> - from command line issue: net start MSSQLServer
> - restart SQL Server
> My questions are:
> 1. are my steps above ok?
> 2. how do i make sure that there are no users attached whilst im making the
> backups?
> 3. what do i do with the TEMPDB?
> 4. when I do the RESTORE on the databases, does this simply restore over the
> current attached databases? I.e. does a restore of the master database
> simply overwrite the presently attached master database?
> 5. do I have to detach the databases before performing a database backup?
> 6. I am guessing i wont have any problems getting replication to kick off
> again once i've rstored the databases. Also reporting service and analysis
> service will work ok.
> Is there anything I need to know prior to this move?
> Many thanks.
> peter
>
>

required to only move data/log files to new SAN, best method?

Hi, our sql 2000 server is configured in such a way that the sql server
resides on a box called Server_A, and has its data files present on a
storage area network called SAN_OLD. Due to limited space, we have to move
these data files across to a new storage area network called SAN_NEW and
have SERVER_A point to these relocated data files. Currently the data files
are located on D: and the log files on L: (logical drives on SAN_OLD). We
are thinking to place the data files on X: and the logs on Y: on SAN_NEW. So
in effect what we want to do is simply move the data/log files to a new
drive location. The environment uses replication, reporting services, and
analysis services.
How can this be achieved? I prefer to use Query Analyzer for this task. I
have to move all databases to SAN_NEW including the master, model etc. Do I
simply (from within query analyser):
- BACKUP PHASE -
--
- Make sure no users are attached to the SQL Server
- stop the SQL server agent
- Using Query Analyzer, perform the following on the MASTER, MODEL and MSDB
databases:
use MASTER
BACKUP DATABASE master
TO DISK = 'X:\backups\master_backup.bak'
-- now perform a backup of the model, msdb and tempdb databases
ALTER DATABASE {model, msdbb} SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
BACKUP DATABASE {model, msdb}
TO DISK = 'X:\backups\{model, msdb}_backup.bak'
- RESTORE PHASE -
--
- start command session
- type: net stop MSSQLServer
- start SQL in single user mode: sqlservr -m
- from within SQL Query Analyzer execute:
use MASTER
-- get the logical database and log file names
RESTORE FILELISTONLY
FROM DISK = 'X:\backups\master_backup.bak'
-- restore the data and log files (assuming logical names are
master_data/master_log)
RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
- Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
using RESTORE FILELISTONLY and RESTORE DATABASE <database name> )
- from command line issue: net start MSSQLServer
- restart SQL Server
My questions are:
1. are my steps above ok?
2. how do i make sure that there are no users attached whilst im making the
backups?
3. what do i do with the TEMPDB?
4. when I do the RESTORE on the databases, does this simply restore over the
current attached databases? I.e. does a restore of the master database
simply overwrite the presently attached master database?
5. do I have to detach the databases before performing a database backup?
6. I am guessing i wont have any problems getting replication to kick off
again once i've rstored the databases. Also reporting service and analysis
service will work ok.
Is there anything I need to know prior to this move?
Many thanks.
peterKB article titled 'Moving SQL Server databases to a new location with
Detach/Attach' has very useful informtion.
http://support.microsoft.com/kb/224071/EN-US/
"peter walker" wrote:

> Hi, our sql 2000 server is configured in such a way that the sql server
> resides on a box called Server_A, and has its data files present on a
> storage area network called SAN_OLD. Due to limited space, we have to move
> these data files across to a new storage area network called SAN_NEW and
> have SERVER_A point to these relocated data files. Currently the data file
s
> are located on D: and the log files on L: (logical drives on SAN_OLD). We
> are thinking to place the data files on X: and the logs on Y: on SAN_NEW.
So
> in effect what we want to do is simply move the data/log files to a new
> drive location. The environment uses replication, reporting services, and
> analysis services.
> How can this be achieved? I prefer to use Query Analyzer for this task. I
> have to move all databases to SAN_NEW including the master, model etc. Do
I
> simply (from within query analyser):
> --
> - BACKUP PHASE -
> --
> - Make sure no users are attached to the SQL Server
> - stop the SQL server agent
> - Using Query Analyzer, perform the following on the MASTER, MODEL and MSD
B
> databases:
> use MASTER
> BACKUP DATABASE master
> TO DISK = 'X:\backups\master_backup.bak'
> -- now perform a backup of the model, msdb and tempdb databases
> ALTER DATABASE {model, msdbb} SET SINGLE_USER
> WITH ROLLBACK IMMEDIATE
> BACKUP DATABASE {model, msdb}
> TO DISK = 'X:\backups\{model, msdb}_backup.bak'
> --
> - RESTORE PHASE -
> --
> - start command session
> - type: net stop MSSQLServer
> - start SQL in single user mode: sqlservr -m
> - from within SQL Query Analyzer execute:
> use MASTER
> -- get the logical database and log file names
> RESTORE FILELISTONLY
> FROM DISK = 'X:\backups\master_backup.bak'
> -- restore the data and log files (assuming logical names are
> master_data/master_log)
> RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
> WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
> MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
> - Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
> using RESTORE FILELISTONLY and RESTORE DATABASE <database name> )
> - from command line issue: net start MSSQLServer
> - restart SQL Server
> My questions are:
> 1. are my steps above ok?
> 2. how do i make sure that there are no users attached whilst im making th
e
> backups?
> 3. what do i do with the TEMPDB?
> 4. when I do the RESTORE on the databases, does this simply restore over t
he
> current attached databases? I.e. does a restore of the master database
> simply overwrite the presently attached master database?
> 5. do I have to detach the databases before performing a database backup?
> 6. I am guessing i wont have any problems getting replication to kick off
> again once i've rstored the databases. Also reporting service and analysis
> service will work ok.
> Is there anything I need to know prior to this move?
> Many thanks.
> peter
>
>

REPOST: optimizations job for db maintenance plan failed

This is happening on two of our servers.
We get the warning in the application log as seen here:
http://support.microsoft.com/kb/902388/
But we don't get the SQL Server log entry that is mentioned in that KB
article.
Here are the commands from the jobs (after adding the
option -SupportComputedColumn , as recommended in the KB article) :
Server 1:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
10 -SupportComputedColumn '
Server 2:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt
"G:\MSSQL\MSSQL\LOG\User DB Maintenance0.txt" -WriteHistory -UpdOptiStats
10 -SupportComputedColumn '
Any suggestions, anyone?
Regards,
JimIf you don't get the SQL Server log entry that is mentioned in the KB
you posted, it may not related at all. By just knowing the warning in
the application event, it is not sufficient to say more.
To find out why the job failed, go to the individual job in EM, right
click and select 'show job history' and check on 'Show Details' box.
It should give you more ideas what went wrong. It could be disk space,
permission, resources conflict issues etc.
Mel|||Hi Mel,
It shows the same message as is in the KB article:
"The job failed. The Job was invoked by User
<computer_system_administrator>. The last step to run was step 1 (Step 1)."
There is only one step:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
10 -SupportComputedColumn '
It's not clear to me how to troubleshoot further.
Jim
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1145009319.702671.27150@.v46g2000cwv.googlegroups.com...
> If you don't get the SQL Server log entry that is mentioned in the KB
> you posted, it may not related at all. By just knowing the warning in
> the application event, it is not sufficient to say more.
> To find out why the job failed, go to the individual job in EM, right
> click and select 'show job history' and check on 'Show Details' box.
> It should give you more ideas what went wrong. It could be disk space,
> permission, resources conflict issues etc.
> Mel
>|||The error message isn't very helpful, is it :)
Okay last attempt, change the job owner to 'sa', to see if it is
because of that.
If still not joys, back to the old classic rule - re-create the plan
(delete the existing one and create a new one). Did you create the job
manually? If so, try to use the DB Maint Wizard to create the job and
compare the two.
Mel|||Thanks for the tip, Mel!
I've changed job owner to "sa". This job is part of a maintenance that runs
once a month, on the first of the month. We'll see how it goes in a two and
one-half weeks!
The plan was recently recreated, but it could be re-recreated to see if that
helps.
Good day,
Jim
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1145046790.093217.94260@.g10g2000cwb.googlegroups.com...
> The error message isn't very helpful, is it :)
> Okay last attempt, change the job owner to 'sa', to see if it is
> because of that.
> If still not joys, back to the old classic rule - re-create the plan
> (delete the existing one and create a new one). Did you create the job
> manually? If so, try to use the DB Maint Wizard to create the job and
> compare the two.
> Mel
>

REPOST: optimizations job for db maintenance plan failed

This is happening on two of our servers.
We get the warning in the application log as seen here:
http://support.microsoft.com/kb/902388/
But we don't get the SQL Server log entry that is mentioned in that KB
article.
Here are the commands from the jobs (after adding the
option -SupportComputedColumn , as recommended in the KB article) :
Server 1:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
10 -SupportComputedColumn '
Server 2:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt
"G:\MSSQL\MSSQL\LOG\User DB Maintenance0.txt" -WriteHistory -UpdOptiStats
10 -SupportComputedColumn '
Any suggestions, anyone?
Regards,
JimIf you don't get the SQL Server log entry that is mentioned in the KB
you posted, it may not related at all. By just knowing the warning in
the application event, it is not sufficient to say more.
To find out why the job failed, go to the individual job in EM, right
click and select 'show job history' and check on 'Show Details' box.
It should give you more ideas what went wrong. It could be disk space,
permission, resources conflict issues etc.
Mel|||Hi Mel,
It shows the same message as is in the KB article:
"The job failed. The Job was invoked by User
<computer_system_administrator>. The last step to run was step 1 (Step 1)."
There is only one step:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
10 -SupportComputedColumn '
It's not clear to me how to troubleshoot further.
Jim
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1145009319.702671.27150@.v46g2000cwv.googlegroups.com...
> If you don't get the SQL Server log entry that is mentioned in the KB
> you posted, it may not related at all. By just knowing the warning in
> the application event, it is not sufficient to say more.
> To find out why the job failed, go to the individual job in EM, right
> click and select 'show job history' and check on 'Show Details' box.
> It should give you more ideas what went wrong. It could be disk space,
> permission, resources conflict issues etc.
> Mel
>|||The error message isn't very helpful, is it
Okay last attempt, change the job owner to 'sa', to see if it is
because of that.
If still not joys, back to the old classic rule - re-create the plan
(delete the existing one and create a new one). Did you create the job
manually? If so, try to use the DB Maint Wizard to create the job and
compare the two.
Mel|||Thanks for the tip, Mel!
I've changed job owner to "sa". This job is part of a maintenance that runs
once a month, on the first of the month. We'll see how it goes in a two and
one-half weeks!
The plan was recently recreated, but it could be re-recreated to see if that
helps.
Good day,
Jim
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1145046790.093217.94260@.g10g2000cwb.googlegroups.com...
> The error message isn't very helpful, is it
> Okay last attempt, change the job owner to 'sa', to see if it is
> because of that.
> If still not joys, back to the old classic rule - re-create the plan
> (delete the existing one and create a new one). Did you create the job
> manually? If so, try to use the DB Maint Wizard to create the job and
> compare the two.
> Mel
>