Hi NG,
I defined a database maintenance task as a job. The job will normally be
executed by the SQL-Server according it's schedule. Furthermore I'd like to
start the job on demand.
Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_name'
I have to check if it is already running. This could be checked with
sp_help_job.
The stored procedure sp_help_job returns multiple rowsets, how can I found
out if the job is still running or if it is idle?
DECLARE @.retval int
EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
@.execution_status=4 returns 4 rowsets. If a job is already running I get the
following error:
Server: Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
sa) refused because the job already has a pending request from User sa.
I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related to
the last rowset of SP sp_help_job .
Thanks for suggestions, reneHi
The column msdb.dbo.sysjobhistory.run_status will give you the status of the
job.
Integer
Status of the job execution:
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
Regards
Mike
"Re Fo" wrote:
> Hi NG,
> I defined a database maintenance task as a job. The job will normally be
> executed by the SQL-Server according it's schedule. Furthermore I'd like to
> start the job on demand.
> Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_name'
> I have to check if it is already running. This could be checked with
> sp_help_job.
> The stored procedure sp_help_job returns multiple rowsets, how can I found
> out if the job is still running or if it is idle?
> DECLARE @.retval int
> EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
> @.execution_status=4 returns 4 rowsets. If a job is already running I get the
> following error:
> Server: Msg 22022, Level 16, State 1, Line 0
> SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
> sa) refused because the job already has a pending request from User sa.
> I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related to
> the last rowset of SP sp_help_job .
> Thanks for suggestions, rene
>
>
>
>
Showing posts with label normally. Show all posts
Showing posts with label normally. Show all posts
Sunday, March 25, 2012
Check Job-Status
Hi NG,
I defined a database maintenance task as a job. The job will normally be
executed by the SQL-Server according it's schedule. Furthermore I'd like to
start the job on demand.
Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_name'
I have to check if it is already running. This could be checked with
sp_help_job.
The stored procedure sp_help_job returns multiple rowsets, how can I found
out if the job is still running or if it is idle?
DECLARE @.retval int
EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
@.execution_status=4 returns 4 rowsets. If a job is already running I get the
following error:
Server: Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
sa) refused because the job already has a pending request from User sa.
I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related to
the last rowset of SP sp_help_job .
Thanks for suggestions, reneHi
The column msdb.dbo.sysjobhistory.run_status will give you the status of the
job.
Integer
Status of the job execution:
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
Regards
Mike
"Re Fo" wrote:
> Hi NG,
> I defined a database maintenance task as a job. The job will normally be
> executed by the SQL-Server according it's schedule. Furthermore I'd like t
o
> start the job on demand.
> Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_nam
e'
> I have to check if it is already running. This could be checked with
> sp_help_job.
> The stored procedure sp_help_job returns multiple rowsets, how can I found
> out if the job is still running or if it is idle?
> DECLARE @.retval int
> EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
> @.execution_status=4 returns 4 rowsets. If a job is already running I get t
he
> following error:
> Server: Msg 22022, Level 16, State 1, Line 0
> SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
> sa) refused because the job already has a pending request from User sa.
> I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related t
o
> the last rowset of SP sp_help_job .
> Thanks for suggestions, rene
>
>
>
>
I defined a database maintenance task as a job. The job will normally be
executed by the SQL-Server according it's schedule. Furthermore I'd like to
start the job on demand.
Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_name'
I have to check if it is already running. This could be checked with
sp_help_job.
The stored procedure sp_help_job returns multiple rowsets, how can I found
out if the job is still running or if it is idle?
DECLARE @.retval int
EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
@.execution_status=4 returns 4 rowsets. If a job is already running I get the
following error:
Server: Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
sa) refused because the job already has a pending request from User sa.
I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related to
the last rowset of SP sp_help_job .
Thanks for suggestions, reneHi
The column msdb.dbo.sysjobhistory.run_status will give you the status of the
job.
Integer
Status of the job execution:
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
Regards
Mike
"Re Fo" wrote:
> Hi NG,
> I defined a database maintenance task as a job. The job will normally be
> executed by the SQL-Server according it's schedule. Furthermore I'd like t
o
> start the job on demand.
> Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_nam
e'
> I have to check if it is already running. This could be checked with
> sp_help_job.
> The stored procedure sp_help_job returns multiple rowsets, how can I found
> out if the job is still running or if it is idle?
> DECLARE @.retval int
> EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
> @.execution_status=4 returns 4 rowsets. If a job is already running I get t
he
> following error:
> Server: Msg 22022, Level 16, State 1, Line 0
> SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
> sa) refused because the job already has a pending request from User sa.
> I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related t
o
> the last rowset of SP sp_help_job .
> Thanks for suggestions, rene
>
>
>
>
Check Job-Status
Hi NG,
I defined a database maintenance task as a job. The job will normally be
executed by the SQL-Server according it's schedule. Furthermore I'd like to
start the job on demand.
Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_name'
I have to check if it is already running. This could be checked with
sp_help_job.
The stored procedure sp_help_job returns multiple rowsets, how can I found
out if the job is still running or if it is idle?
DECLARE @.retval int
EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
@.execution_status=4 returns 4 rowsets. If a job is already running I get the
following error:
Server: Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
sa) refused because the job already has a pending request from User sa.
I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related to
the last rowset of SP sp_help_job .
Thanks for suggestions, rene
Hi
The column msdb.dbo.sysjobhistory.run_status will give you the status of the
job.
Integer
Status of the job execution:
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
Regards
Mike
"Re Fo" wrote:
> Hi NG,
> I defined a database maintenance task as a job. The job will normally be
> executed by the SQL-Server according it's schedule. Furthermore I'd like to
> start the job on demand.
> Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_name'
> I have to check if it is already running. This could be checked with
> sp_help_job.
> The stored procedure sp_help_job returns multiple rowsets, how can I found
> out if the job is still running or if it is idle?
> DECLARE @.retval int
> EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
> @.execution_status=4 returns 4 rowsets. If a job is already running I get the
> following error:
> Server: Msg 22022, Level 16, State 1, Line 0
> SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
> sa) refused because the job already has a pending request from User sa.
> I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related to
> the last rowset of SP sp_help_job .
> Thanks for suggestions, rene
>
>
>
>
sql
I defined a database maintenance task as a job. The job will normally be
executed by the SQL-Server according it's schedule. Furthermore I'd like to
start the job on demand.
Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_name'
I have to check if it is already running. This could be checked with
sp_help_job.
The stored procedure sp_help_job returns multiple rowsets, how can I found
out if the job is still running or if it is idle?
DECLARE @.retval int
EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
@.execution_status=4 returns 4 rowsets. If a job is already running I get the
following error:
Server: Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
sa) refused because the job already has a pending request from User sa.
I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related to
the last rowset of SP sp_help_job .
Thanks for suggestions, rene
Hi
The column msdb.dbo.sysjobhistory.run_status will give you the status of the
job.
Integer
Status of the job execution:
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
Regards
Mike
"Re Fo" wrote:
> Hi NG,
> I defined a database maintenance task as a job. The job will normally be
> executed by the SQL-Server according it's schedule. Furthermore I'd like to
> start the job on demand.
> Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_name'
> I have to check if it is already running. This could be checked with
> sp_help_job.
> The stored procedure sp_help_job returns multiple rowsets, how can I found
> out if the job is still running or if it is idle?
> DECLARE @.retval int
> EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
> @.execution_status=4 returns 4 rowsets. If a job is already running I get the
> following error:
> Server: Msg 22022, Level 16, State 1, Line 0
> SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
> sa) refused because the job already has a pending request from User sa.
> I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related to
> the last rowset of SP sp_help_job .
> Thanks for suggestions, rene
>
>
>
>
sql
Sunday, March 11, 2012
Check Data and Index Linkage FAILED
Hi,
I'm running SQL Server 2000 and I have a maintenance plan for our
database to backup every 2 hours. Our users normally get in about 5 AM
to 9 AM. I was checking our logs this morning to make sure things went
smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried
to Check Data and Index Linkage and it failed with this message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
After that the 9 AM backup didn't execute. I'm assuming that since
users are logged in, it must have put the database in multi-user mode?
Or is it because I have my Enterprise Manager open? I don't have the
live database extended. I normally work on our test database and then
update the live accordingly. Thanks for any help.
Marc Ferguson
The message implies you were not just checking for problems but you were
checking and trying to repair any problems.
If you are doing a DBCC CHECKTABLE with one of the following optional
parameters then the DB must be in Single User mode :-
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Suggest running DBCC CHECKDB, this will report any errors it finds. You can
then try to repair any single table at a later data during your maintenance
windows.
To put your db into single_user mode :-
ALTER DATABASE dbname SET Single_user with rollback immediate
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database
> to backup every 2 hours. Our users normally get in about 5 AM to 9 AM. I
> was checking our logs this morning to make sure things went smoothly and
> from 1 AM to 7 AM, everything went fine. At 9 AM it tried to Check Data
> and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users
> are logged in, it must have put the database in multi-user mode? Or is it
> because I have my Enterprise Manager open? I don't have the live database
> extended. I normally work on our test database and then update the live
> accordingly. Thanks for any help.
> Marc Ferguson
|||Don't check the "fix minor problems" in the maint plan. If it turns out you do have a problem, you
don't want an automated routine to fox this (I have an article on this on my website,
www.karaszi.com, but the site seems to be down at the moment, so I can't give exact UTL).
If you do check this option, the database indeed need to be in single user mode. Main plan tries to
set it to single user, but that will fail if any user is in the database, which seems to have been
your case. But, as I mentioned, remove that option and you will be fine.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database to backup every 2
> hours. Our users normally get in about 5 AM to 9 AM. I was checking our logs this morning to
> make sure things went smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried to
> Check Data and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to
> be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users are logged in, it must
> have put the database in multi-user mode? Or is it because I have my Enterprise Manager open? I
> don't have the live database extended. I normally work on our test database and then update the
> live accordingly. Thanks for any help.
> Marc Ferguson
|||Thanks Ryan for your response. I have a question though..
I'm not a real guru with MS SQL Server's maintenance and extra features.
What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to
get to my maintenance plan:
Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host Name] | Management | Database Maintenance Plans
From there I created a new plan with:
1. Optimizations (w/ defaults)
2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1
AM to 11:59:59 PM)
4. Transaction Log Backup and Reporting w/ defaults.
From your response, I assumed that it's the Integrity tab that I should
modify. So I unchecked "Attempt to repair any minor problems". Is that
correct or am I way off. Thanks again.
Marc F.
Ryan wrote:
> The message implies you were not just checking for problems but you were
> checking and trying to repair any problems.
> If you are doing a DBCC CHECKTABLE with one of the following optional
> parameters then the DB must be in Single User mode :-
> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> Suggest running DBCC CHECKDB, this will report any errors it finds. You can
> then try to repair any single table at a later data during your maintenance
> windows.
> To put your db into single_user mode :-
> ALTER DATABASE dbname SET Single_user with rollback immediate
>
>
>
|||> From your response, I assumed that it's the Integrity tab that I should modify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks again.
See my response. This is indeed what I was referring to. So you are set now. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features. What is DBCC CHECKTABLE?
> Here's my path through Enterprise Manager to get to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server Host Name] | Management |
> Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should modify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:
|||If you're in EnterPrise Manager try the following :-
Tools\SQL Query Analyser
USE yourdatabasename
GO
DBCC CHECKDB
It's safe to run this as it won't place any data locks only schema locks
(people will be able to modify data but not change table schema)
It should return to you any tables that have corruption. You can then
attempt to repair this corruption with DBCC CHECKTABLE.
SQL Server Books Online is a good resource to get started with. Alternitavly
have a look at Tibor's site (if it's available now) www.karaszi.com,
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features.
> What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to get
> to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server Host
> Name] | Management | Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM
> to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should
> modify. So I unchecked "Attempt to repair any minor problems". Is that
> correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:
|||Thanks Ryan and Tibor.
Tibor, I saw your response after my last post. ;)
Marc F.
Tibor Karaszi wrote:
>
> See my response. This is indeed what I was referring to. So you are set
> now. :-)
>
I'm running SQL Server 2000 and I have a maintenance plan for our
database to backup every 2 hours. Our users normally get in about 5 AM
to 9 AM. I was checking our logs this morning to make sure things went
smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried
to Check Data and Index Linkage and it failed with this message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
After that the 9 AM backup didn't execute. I'm assuming that since
users are logged in, it must have put the database in multi-user mode?
Or is it because I have my Enterprise Manager open? I don't have the
live database extended. I normally work on our test database and then
update the live accordingly. Thanks for any help.
Marc Ferguson
The message implies you were not just checking for problems but you were
checking and trying to repair any problems.
If you are doing a DBCC CHECKTABLE with one of the following optional
parameters then the DB must be in Single User mode :-
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Suggest running DBCC CHECKDB, this will report any errors it finds. You can
then try to repair any single table at a later data during your maintenance
windows.
To put your db into single_user mode :-
ALTER DATABASE dbname SET Single_user with rollback immediate
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database
> to backup every 2 hours. Our users normally get in about 5 AM to 9 AM. I
> was checking our logs this morning to make sure things went smoothly and
> from 1 AM to 7 AM, everything went fine. At 9 AM it tried to Check Data
> and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users
> are logged in, it must have put the database in multi-user mode? Or is it
> because I have my Enterprise Manager open? I don't have the live database
> extended. I normally work on our test database and then update the live
> accordingly. Thanks for any help.
> Marc Ferguson
|||Don't check the "fix minor problems" in the maint plan. If it turns out you do have a problem, you
don't want an automated routine to fox this (I have an article on this on my website,
www.karaszi.com, but the site seems to be down at the moment, so I can't give exact UTL).
If you do check this option, the database indeed need to be in single user mode. Main plan tries to
set it to single user, but that will fail if any user is in the database, which seems to have been
your case. But, as I mentioned, remove that option and you will be fine.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database to backup every 2
> hours. Our users normally get in about 5 AM to 9 AM. I was checking our logs this morning to
> make sure things went smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried to
> Check Data and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to
> be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users are logged in, it must
> have put the database in multi-user mode? Or is it because I have my Enterprise Manager open? I
> don't have the live database extended. I normally work on our test database and then update the
> live accordingly. Thanks for any help.
> Marc Ferguson
|||Thanks Ryan for your response. I have a question though..
I'm not a real guru with MS SQL Server's maintenance and extra features.
What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to
get to my maintenance plan:
Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host Name] | Management | Database Maintenance Plans
From there I created a new plan with:
1. Optimizations (w/ defaults)
2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1
AM to 11:59:59 PM)
4. Transaction Log Backup and Reporting w/ defaults.
From your response, I assumed that it's the Integrity tab that I should
modify. So I unchecked "Attempt to repair any minor problems". Is that
correct or am I way off. Thanks again.
Marc F.
Ryan wrote:
> The message implies you were not just checking for problems but you were
> checking and trying to repair any problems.
> If you are doing a DBCC CHECKTABLE with one of the following optional
> parameters then the DB must be in Single User mode :-
> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> Suggest running DBCC CHECKDB, this will report any errors it finds. You can
> then try to repair any single table at a later data during your maintenance
> windows.
> To put your db into single_user mode :-
> ALTER DATABASE dbname SET Single_user with rollback immediate
>
>
>
|||> From your response, I assumed that it's the Integrity tab that I should modify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks again.
See my response. This is indeed what I was referring to. So you are set now. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features. What is DBCC CHECKTABLE?
> Here's my path through Enterprise Manager to get to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server Host Name] | Management |
> Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should modify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:
|||If you're in EnterPrise Manager try the following :-
Tools\SQL Query Analyser
USE yourdatabasename
GO
DBCC CHECKDB
It's safe to run this as it won't place any data locks only schema locks
(people will be able to modify data but not change table schema)
It should return to you any tables that have corruption. You can then
attempt to repair this corruption with DBCC CHECKTABLE.
SQL Server Books Online is a good resource to get started with. Alternitavly
have a look at Tibor's site (if it's available now) www.karaszi.com,
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features.
> What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to get
> to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server Host
> Name] | Management | Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM
> to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should
> modify. So I unchecked "Attempt to repair any minor problems". Is that
> correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:
|||Thanks Ryan and Tibor.
Tibor, I saw your response after my last post. ;)
Marc F.
Tibor Karaszi wrote:
>
> See my response. This is indeed what I was referring to. So you are set
> now. :-)
>
Check Data and Index Linkage FAILED
Hi,
I'm running SQL Server 2000 and I have a maintenance plan for our
database to backup every 2 hours. Our users normally get in about 5 AM
to 9 AM. I was checking our logs this morning to make sure things went
smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried
to Check Data and Index Linkage and it failed with this message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
After that the 9 AM backup didn't execute. I'm assuming that since
users are logged in, it must have put the database in multi-user mode'
Or is it because I have my Enterprise Manager open? I don't have the
live database extended. I normally work on our test database and then
update the live accordingly. Thanks for any help.
Marc FergusonThe message implies you were not just checking for problems but you were
checking and trying to repair any problems.
If you are doing a DBCC CHECKTABLE with one of the following optional
parameters then the DB must be in Single User mode :-
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Suggest running DBCC CHECKDB, this will report any errors it finds. You can
then try to repair any single table at a later data during your maintenance
windows.
To put your db into single_user mode :-
ALTER DATABASE dbname SET Single_user with rollback immediate
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database
> to backup every 2 hours. Our users normally get in about 5 AM to 9 AM. I
> was checking our logs this morning to make sure things went smoothly and
> from 1 AM to 7 AM, everything went fine. At 9 AM it tried to Check Data
> and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users
> are logged in, it must have put the database in multi-user mode' Or is it
> because I have my Enterprise Manager open? I don't have the live database
> extended. I normally work on our test database and then update the live
> accordingly. Thanks for any help.
> Marc Ferguson|||Don't check the "fix minor problems" in the maint plan. If it turns out you do have a problem, you
don't want an automated routine to fox this (I have an article on this on my website,
www.karaszi.com, but the site seems to be down at the moment, so I can't give exact UTL).
If you do check this option, the database indeed need to be in single user mode. Main plan tries to
set it to single user, but that will fail if any user is in the database, which seems to have been
your case. But, as I mentioned, remove that option and you will be fine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database to backup every 2
> hours. Our users normally get in about 5 AM to 9 AM. I was checking our logs this morning to
> make sure things went smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried to
> Check Data and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to
> be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users are logged in, it must
> have put the database in multi-user mode' Or is it because I have my Enterprise Manager open? I
> don't have the live database extended. I normally work on our test database and then update the
> live accordingly. Thanks for any help.
> Marc Ferguson|||Thanks Ryan for your response. I have a question though..
I'm not a real guru with MS SQL Server's maintenance and extra features.
What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to
get to my maintenance plan:
Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host Name] | Management | Database Maintenance Plans
From there I created a new plan with:
1. Optimizations (w/ defaults)
2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1
AM to 11:59:59 PM)
4. Transaction Log Backup and Reporting w/ defaults.
From your response, I assumed that it's the Integrity tab that I should
modify. So I unchecked "Attempt to repair any minor problems". Is that
correct or am I way off. Thanks again.
Marc F.
Ryan wrote:
> The message implies you were not just checking for problems but you were
> checking and trying to repair any problems.
> If you are doing a DBCC CHECKTABLE with one of the following optional
> parameters then the DB must be in Single User mode :-
> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> Suggest running DBCC CHECKDB, this will report any errors it finds. You can
> then try to repair any single table at a later data during your maintenance
> windows.
> To put your db into single_user mode :-
> ALTER DATABASE dbname SET Single_user with rollback immediate
>
>
>|||> From your response, I assumed that it's the Integrity tab that I should modify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks again.
See my response. This is indeed what I was referring to. So you are set now. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features. What is DBCC CHECKTABLE?
> Here's my path through Enterprise Manager to get to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server Host Name] | Management |
> Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should modify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:
>> The message implies you were not just checking for problems but you were checking and trying to
>> repair any problems.
>> If you are doing a DBCC CHECKTABLE with one of the following optional parameters then the DB must
>> be in Single User mode :-
>> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
>> Suggest running DBCC CHECKDB, this will report any errors it finds. You can then try to repair
>> any single table at a later data during your maintenance windows.
>> To put your db into single_user mode :-
>> ALTER DATABASE dbname SET Single_user with rollback immediate
>>
>>|||If you're in EnterPrise Manager try the following :-
Tools\SQL Query Analyser
USE yourdatabasename
GO
DBCC CHECKDB
It's safe to run this as it won't place any data locks only schema locks
(people will be able to modify data but not change table schema)
It should return to you any tables that have corruption. You can then
attempt to repair this corruption with DBCC CHECKTABLE.
SQL Server Books Online is a good resource to get started with. Alternitavly
have a look at Tibor's site (if it's available now) www.karaszi.com,
--
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features.
> What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to get
> to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server Host
> Name] | Management | Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM
> to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should
> modify. So I unchecked "Attempt to repair any minor problems". Is that
> correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:
>> The message implies you were not just checking for problems but you were
>> checking and trying to repair any problems.
>> If you are doing a DBCC CHECKTABLE with one of the following optional
>> parameters then the DB must be in Single User mode :-
>> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
>> Suggest running DBCC CHECKDB, this will report any errors it finds. You
>> can then try to repair any single table at a later data during your
>> maintenance windows.
>> To put your db into single_user mode :-
>> ALTER DATABASE dbname SET Single_user with rollback immediate
>>
>>|||Thanks Ryan and Tibor.
Tibor, I saw your response after my last post. ;)
Marc F.
Tibor Karaszi wrote:
>> From your response, I assumed that it's the Integrity tab that I
>> should modify. So I unchecked "Attempt to repair any minor
>> problems". Is that correct or am I way off. Thanks again.
>
> See my response. This is indeed what I was referring to. So you are set
> now. :-)
>
I'm running SQL Server 2000 and I have a maintenance plan for our
database to backup every 2 hours. Our users normally get in about 5 AM
to 9 AM. I was checking our logs this morning to make sure things went
smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried
to Check Data and Index Linkage and it failed with this message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
After that the 9 AM backup didn't execute. I'm assuming that since
users are logged in, it must have put the database in multi-user mode'
Or is it because I have my Enterprise Manager open? I don't have the
live database extended. I normally work on our test database and then
update the live accordingly. Thanks for any help.
Marc FergusonThe message implies you were not just checking for problems but you were
checking and trying to repair any problems.
If you are doing a DBCC CHECKTABLE with one of the following optional
parameters then the DB must be in Single User mode :-
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Suggest running DBCC CHECKDB, this will report any errors it finds. You can
then try to repair any single table at a later data during your maintenance
windows.
To put your db into single_user mode :-
ALTER DATABASE dbname SET Single_user with rollback immediate
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database
> to backup every 2 hours. Our users normally get in about 5 AM to 9 AM. I
> was checking our logs this morning to make sure things went smoothly and
> from 1 AM to 7 AM, everything went fine. At 9 AM it tried to Check Data
> and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users
> are logged in, it must have put the database in multi-user mode' Or is it
> because I have my Enterprise Manager open? I don't have the live database
> extended. I normally work on our test database and then update the live
> accordingly. Thanks for any help.
> Marc Ferguson|||Don't check the "fix minor problems" in the maint plan. If it turns out you do have a problem, you
don't want an automated routine to fox this (I have an article on this on my website,
www.karaszi.com, but the site seems to be down at the moment, so I can't give exact UTL).
If you do check this option, the database indeed need to be in single user mode. Main plan tries to
set it to single user, but that will fail if any user is in the database, which seems to have been
your case. But, as I mentioned, remove that option and you will be fine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database to backup every 2
> hours. Our users normally get in about 5 AM to 9 AM. I was checking our logs this morning to
> make sure things went smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried to
> Check Data and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to
> be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users are logged in, it must
> have put the database in multi-user mode' Or is it because I have my Enterprise Manager open? I
> don't have the live database extended. I normally work on our test database and then update the
> live accordingly. Thanks for any help.
> Marc Ferguson|||Thanks Ryan for your response. I have a question though..
I'm not a real guru with MS SQL Server's maintenance and extra features.
What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to
get to my maintenance plan:
Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host Name] | Management | Database Maintenance Plans
From there I created a new plan with:
1. Optimizations (w/ defaults)
2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1
AM to 11:59:59 PM)
4. Transaction Log Backup and Reporting w/ defaults.
From your response, I assumed that it's the Integrity tab that I should
modify. So I unchecked "Attempt to repair any minor problems". Is that
correct or am I way off. Thanks again.
Marc F.
Ryan wrote:
> The message implies you were not just checking for problems but you were
> checking and trying to repair any problems.
> If you are doing a DBCC CHECKTABLE with one of the following optional
> parameters then the DB must be in Single User mode :-
> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> Suggest running DBCC CHECKDB, this will report any errors it finds. You can
> then try to repair any single table at a later data during your maintenance
> windows.
> To put your db into single_user mode :-
> ALTER DATABASE dbname SET Single_user with rollback immediate
>
>
>|||> From your response, I assumed that it's the Integrity tab that I should modify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks again.
See my response. This is indeed what I was referring to. So you are set now. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features. What is DBCC CHECKTABLE?
> Here's my path through Enterprise Manager to get to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server Host Name] | Management |
> Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should modify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:
>> The message implies you were not just checking for problems but you were checking and trying to
>> repair any problems.
>> If you are doing a DBCC CHECKTABLE with one of the following optional parameters then the DB must
>> be in Single User mode :-
>> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
>> Suggest running DBCC CHECKDB, this will report any errors it finds. You can then try to repair
>> any single table at a later data during your maintenance windows.
>> To put your db into single_user mode :-
>> ALTER DATABASE dbname SET Single_user with rollback immediate
>>
>>|||If you're in EnterPrise Manager try the following :-
Tools\SQL Query Analyser
USE yourdatabasename
GO
DBCC CHECKDB
It's safe to run this as it won't place any data locks only schema locks
(people will be able to modify data but not change table schema)
It should return to you any tables that have corruption. You can then
attempt to repair this corruption with DBCC CHECKTABLE.
SQL Server Books Online is a good resource to get started with. Alternitavly
have a look at Tibor's site (if it's available now) www.karaszi.com,
--
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features.
> What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to get
> to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server Host
> Name] | Management | Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM
> to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should
> modify. So I unchecked "Attempt to repair any minor problems". Is that
> correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:
>> The message implies you were not just checking for problems but you were
>> checking and trying to repair any problems.
>> If you are doing a DBCC CHECKTABLE with one of the following optional
>> parameters then the DB must be in Single User mode :-
>> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
>> Suggest running DBCC CHECKDB, this will report any errors it finds. You
>> can then try to repair any single table at a later data during your
>> maintenance windows.
>> To put your db into single_user mode :-
>> ALTER DATABASE dbname SET Single_user with rollback immediate
>>
>>|||Thanks Ryan and Tibor.
Tibor, I saw your response after my last post. ;)
Marc F.
Tibor Karaszi wrote:
>> From your response, I assumed that it's the Integrity tab that I
>> should modify. So I unchecked "Attempt to repair any minor
>> problems". Is that correct or am I way off. Thanks again.
>
> See my response. This is indeed what I was referring to. So you are set
> now. :-)
>
Check Data and Index Linkage FAILED
Hi,
I'm running SQL Server 2000 and I have a maintenance plan for our
database to backup every 2 hours. Our users normally get in about 5 AM
to 9 AM. I was checking our logs this morning to make sure things went
smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried
to Check Data and Index Linkage and it failed with this message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement
not
processed. Database needs to be in single user mode.
After that the 9 AM backup didn't execute. I'm assuming that since
users are logged in, it must have put the database in multi-user mode'
Or is it because I have my Enterprise Manager open? I don't have the
live database extended. I normally work on our test database and then
update the live accordingly. Thanks for any help.
Marc FergusonThe message implies you were not just checking for problems but you were
checking and trying to repair any problems.
If you are doing a DBCC CHECKTABLE with one of the following optional
parameters then the DB must be in Single User mode :-
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Suggest running DBCC CHECKDB, this will report any errors it finds. You can
then try to repair any single table at a later data during your maintenance
windows.
To put your db into single_user mode :-
ALTER DATABASE dbname SET Single_user with rollback immediate
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database
> to backup every 2 hours. Our users normally get in about 5 AM to 9 AM. I
> was checking our logs this morning to make sure things went smoothly and
> from 1 AM to 7 AM, everything went fine. At 9 AM it tried to Check Data
> and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statemen
t not
> processed. Database needs to be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users
> are logged in, it must have put the database in multi-user mode' Or is it
> because I have my Enterprise Manager open? I don't have the live database
> extended. I normally work on our test database and then update the live
> accordingly. Thanks for any help.
> Marc Ferguson|||Don't check the "fix minor problems" in the maint plan. If it turns out you
do have a problem, you
don't want an automated routine to fox this (I have an article on this on my
website,
www.karaszi.com, but the site seems to be down at the moment, so I can't giv
e exact UTL).
If you do check this option, the database indeed need to be in single user m
ode. Main plan tries to
set it to single user, but that will fail if any user is in the database, wh
ich seems to have been
your case. But, as I mentioned, remove that option and you will be fine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database
to backup every 2
> hours. Our users normally get in about 5 AM to 9 AM. I was checking our
logs this morning to
> make sure things went smoothly and from 1 AM to 7 AM, everything went fine
. At 9 AM it tried to
> Check Data and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statemen
t not processed. Database needs to
> be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users
are logged in, it must
> have put the database in multi-user mode' Or is it because I have my Ente
rprise Manager open? I
> don't have the live database extended. I normally work on our test databa
se and then update the
> live accordingly. Thanks for any help.
> Marc Ferguson|||Thanks Ryan for your response. I have a question though..
I'm not a real guru with MS SQL Server's maintenance and extra features.
What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to
get to my maintenance plan:
Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host Name] | Management | Database Maintenance Plans
From there I created a new plan with:
1. Optimizations (w/ defaults)
2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1
AM to 11:59:59 PM)
4. Transaction Log Backup and Reporting w/ defaults.
From your response, I assumed that it's the Integrity tab that I should
modify. So I unchecked "Attempt to repair any minor problems". Is that
correct or am I way off. Thanks again.
Marc F.
Ryan wrote:
> The message implies you were not just checking for problems but you were
> checking and trying to repair any problems.
> If you are doing a DBCC CHECKTABLE with one of the following optional
> parameters then the DB must be in Single User mode :-
> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> Suggest running DBCC CHECKDB, this will report any errors it finds. You ca
n
> then try to repair any single table at a later data during your maintenanc
e
> windows.
> To put your db into single_user mode :-
> ALTER DATABASE dbname SET Single_user with rollback immediate
>
>
>|||> From your response, I assumed that it's the Integrity tab that I should modify. So I unch
ecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks
again.
See my response. This is indeed what I was referring to. So you are set now.
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features. W
hat is DBCC CHECKTABLE?
> Here's my path through Enterprise Manager to get to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host Name] | Management |
> Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM
to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should mo
dify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off.
Thanks again.
> Marc F.
> Ryan wrote:|||If you're in EnterPrise Manager try the following :-
Tools\SQL Query Analyser
USE yourdatabasename
GO
DBCC CHECKDB
It's safe to run this as it won't place any data locks only schema locks
(people will be able to modify data but not change table schema)
It should return to you any tables that have corruption. You can then
attempt to repair this corruption with DBCC CHECKTABLE.
SQL Server Books Online is a good resource to get started with. Alternitavly
have a look at Tibor's site (if it's available now) www.karaszi.com,
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features.
> What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to get
> to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host
> Name] | Management | Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM
> to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should
> modify. So I unchecked "Attempt to repair any minor problems". Is that
> correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:|||Thanks Ryan and Tibor.
Tibor, I saw your response after my last post. ;)
Marc F.
Tibor Karaszi wrote:
>
> See my response. This is indeed what I was referring to. So you are set
> now. :-)
>
I'm running SQL Server 2000 and I have a maintenance plan for our
database to backup every 2 hours. Our users normally get in about 5 AM
to 9 AM. I was checking our logs this morning to make sure things went
smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried
to Check Data and Index Linkage and it failed with this message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement
not
processed. Database needs to be in single user mode.
After that the 9 AM backup didn't execute. I'm assuming that since
users are logged in, it must have put the database in multi-user mode'
Or is it because I have my Enterprise Manager open? I don't have the
live database extended. I normally work on our test database and then
update the live accordingly. Thanks for any help.
Marc FergusonThe message implies you were not just checking for problems but you were
checking and trying to repair any problems.
If you are doing a DBCC CHECKTABLE with one of the following optional
parameters then the DB must be in Single User mode :-
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Suggest running DBCC CHECKDB, this will report any errors it finds. You can
then try to repair any single table at a later data during your maintenance
windows.
To put your db into single_user mode :-
ALTER DATABASE dbname SET Single_user with rollback immediate
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database
> to backup every 2 hours. Our users normally get in about 5 AM to 9 AM. I
> was checking our logs this morning to make sure things went smoothly and
> from 1 AM to 7 AM, everything went fine. At 9 AM it tried to Check Data
> and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statemen
t not
> processed. Database needs to be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users
> are logged in, it must have put the database in multi-user mode' Or is it
> because I have my Enterprise Manager open? I don't have the live database
> extended. I normally work on our test database and then update the live
> accordingly. Thanks for any help.
> Marc Ferguson|||Don't check the "fix minor problems" in the maint plan. If it turns out you
do have a problem, you
don't want an automated routine to fox this (I have an article on this on my
website,
www.karaszi.com, but the site seems to be down at the moment, so I can't giv
e exact UTL).
If you do check this option, the database indeed need to be in single user m
ode. Main plan tries to
set it to single user, but that will fail if any user is in the database, wh
ich seems to have been
your case. But, as I mentioned, remove that option and you will be fine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database
to backup every 2
> hours. Our users normally get in about 5 AM to 9 AM. I was checking our
logs this morning to
> make sure things went smoothly and from 1 AM to 7 AM, everything went fine
. At 9 AM it tried to
> Check Data and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statemen
t not processed. Database needs to
> be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users
are logged in, it must
> have put the database in multi-user mode' Or is it because I have my Ente
rprise Manager open? I
> don't have the live database extended. I normally work on our test databa
se and then update the
> live accordingly. Thanks for any help.
> Marc Ferguson|||Thanks Ryan for your response. I have a question though..
I'm not a real guru with MS SQL Server's maintenance and extra features.
What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to
get to my maintenance plan:
Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host Name] | Management | Database Maintenance Plans
From there I created a new plan with:
1. Optimizations (w/ defaults)
2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1
AM to 11:59:59 PM)
4. Transaction Log Backup and Reporting w/ defaults.
From your response, I assumed that it's the Integrity tab that I should
modify. So I unchecked "Attempt to repair any minor problems". Is that
correct or am I way off. Thanks again.
Marc F.
Ryan wrote:
> The message implies you were not just checking for problems but you were
> checking and trying to repair any problems.
> If you are doing a DBCC CHECKTABLE with one of the following optional
> parameters then the DB must be in Single User mode :-
> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> Suggest running DBCC CHECKDB, this will report any errors it finds. You ca
n
> then try to repair any single table at a later data during your maintenanc
e
> windows.
> To put your db into single_user mode :-
> ALTER DATABASE dbname SET Single_user with rollback immediate
>
>
>|||> From your response, I assumed that it's the Integrity tab that I should modify. So I unch
ecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks
again.
See my response. This is indeed what I was referring to. So you are set now.
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features. W
hat is DBCC CHECKTABLE?
> Here's my path through Enterprise Manager to get to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host Name] | Management |
> Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM
to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should mo
dify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off.
Thanks again.
> Marc F.
> Ryan wrote:|||If you're in EnterPrise Manager try the following :-
Tools\SQL Query Analyser
USE yourdatabasename
GO
DBCC CHECKDB
It's safe to run this as it won't place any data locks only schema locks
(people will be able to modify data but not change table schema)
It should return to you any tables that have corruption. You can then
attempt to repair this corruption with DBCC CHECKTABLE.
SQL Server Books Online is a good resource to get started with. Alternitavly
have a look at Tibor's site (if it's available now) www.karaszi.com,
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features.
> What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to get
> to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host
> Name] | Management | Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM
> to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should
> modify. So I unchecked "Attempt to repair any minor problems". Is that
> correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:|||Thanks Ryan and Tibor.
Tibor, I saw your response after my last post. ;)
Marc F.
Tibor Karaszi wrote:
>
> See my response. This is indeed what I was referring to. So you are set
> now. :-)
>
Saturday, February 25, 2012
Charts
I have a Custom Dundas Charting library that I utilize normally. I'm having
troubles getting my chart object (and tried to return and image) to render
within my reports. I'm confident I have the custom assemblie referenced. I
don't want to use the chart field from the toolbox, I want to set it to what
my assemblie returns. Anyway to get around this? I'm aware of the custom
data extensions, although, I wanted to just have my existing assemblie which
returns charts do it instead. so all my reports that need graphs can just
reference the assemblie and graph that is needed. Anyway to accomplish this?
I'm getting data type reference errors etc.
ClintPlease try this:
* Add an image to your report
* Set the image type to Database
* Set the image mimetype to e.g. image/png
* For the image value use an expression like
=MyCustomAssembly.GenerateChart()
Note: your custom assembly call has to return the image as byte[].
Here is a code snippet which should convert the Dundas chart output into a
byte array:
public static byte[] GenerateChart()
{
// chart rendering code
...
// save chart image to byte[]
System.IO.MemoryStream renderedImage = new MemoryStream();
dundasChart.ImageType = ChartImageType.Png;
dundasChart.Save(renderedImage);
renderedImage.Position = 0;
return renderedImage.ToArray();
}
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Clint Jennings" <cjennings@.fusionAlliance.com> wrote in message
news:%237PwafckEHA.896@.TK2MSFTNGP12.phx.gbl...
> I have a Custom Dundas Charting library that I utilize normally. I'm
having
> troubles getting my chart object (and tried to return and image) to render
> within my reports. I'm confident I have the custom assemblie referenced.
I
> don't want to use the chart field from the toolbox, I want to set it to
what
> my assemblie returns. Anyway to get around this? I'm aware of the custom
> data extensions, although, I wanted to just have my existing assemblie
which
> returns charts do it instead. so all my reports that need graphs can just
> reference the assemblie and graph that is needed. Anyway to accomplish
this?
> I'm getting data type reference errors etc.
> Clint
>|||Thanks Robert. Created a public function in the library to return a byte
stream.
"Robert Bruckner [MSFT]" wrote:
> Please try this:
> * Add an image to your report
> * Set the image type to Database
> * Set the image mimetype to e.g. image/png
> * For the image value use an expression like
> =MyCustomAssembly.GenerateChart()
> Note: your custom assembly call has to return the image as byte[].
> Here is a code snippet which should convert the Dundas chart output into a
> byte array:
> public static byte[] GenerateChart()
> {
> // chart rendering code
> ...
> // save chart image to byte[]
> System.IO.MemoryStream renderedImage = new MemoryStream();
> dundasChart.ImageType = ChartImageType.Png;
> dundasChart.Save(renderedImage);
> renderedImage.Position = 0;
> return renderedImage.ToArray();
> }
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Clint Jennings" <cjennings@.fusionAlliance.com> wrote in message
> news:%237PwafckEHA.896@.TK2MSFTNGP12.phx.gbl...
> > I have a Custom Dundas Charting library that I utilize normally. I'm
> having
> > troubles getting my chart object (and tried to return and image) to render
> > within my reports. I'm confident I have the custom assemblie referenced.
> I
> > don't want to use the chart field from the toolbox, I want to set it to
> what
> > my assemblie returns. Anyway to get around this? I'm aware of the custom
> > data extensions, although, I wanted to just have my existing assemblie
> which
> > returns charts do it instead. so all my reports that need graphs can just
> > reference the assemblie and graph that is needed. Anyway to accomplish
> this?
> > I'm getting data type reference errors etc.
> >
> > Clint
> >
> >
>
>
troubles getting my chart object (and tried to return and image) to render
within my reports. I'm confident I have the custom assemblie referenced. I
don't want to use the chart field from the toolbox, I want to set it to what
my assemblie returns. Anyway to get around this? I'm aware of the custom
data extensions, although, I wanted to just have my existing assemblie which
returns charts do it instead. so all my reports that need graphs can just
reference the assemblie and graph that is needed. Anyway to accomplish this?
I'm getting data type reference errors etc.
ClintPlease try this:
* Add an image to your report
* Set the image type to Database
* Set the image mimetype to e.g. image/png
* For the image value use an expression like
=MyCustomAssembly.GenerateChart()
Note: your custom assembly call has to return the image as byte[].
Here is a code snippet which should convert the Dundas chart output into a
byte array:
public static byte[] GenerateChart()
{
// chart rendering code
...
// save chart image to byte[]
System.IO.MemoryStream renderedImage = new MemoryStream();
dundasChart.ImageType = ChartImageType.Png;
dundasChart.Save(renderedImage);
renderedImage.Position = 0;
return renderedImage.ToArray();
}
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Clint Jennings" <cjennings@.fusionAlliance.com> wrote in message
news:%237PwafckEHA.896@.TK2MSFTNGP12.phx.gbl...
> I have a Custom Dundas Charting library that I utilize normally. I'm
having
> troubles getting my chart object (and tried to return and image) to render
> within my reports. I'm confident I have the custom assemblie referenced.
I
> don't want to use the chart field from the toolbox, I want to set it to
what
> my assemblie returns. Anyway to get around this? I'm aware of the custom
> data extensions, although, I wanted to just have my existing assemblie
which
> returns charts do it instead. so all my reports that need graphs can just
> reference the assemblie and graph that is needed. Anyway to accomplish
this?
> I'm getting data type reference errors etc.
> Clint
>|||Thanks Robert. Created a public function in the library to return a byte
stream.
"Robert Bruckner [MSFT]" wrote:
> Please try this:
> * Add an image to your report
> * Set the image type to Database
> * Set the image mimetype to e.g. image/png
> * For the image value use an expression like
> =MyCustomAssembly.GenerateChart()
> Note: your custom assembly call has to return the image as byte[].
> Here is a code snippet which should convert the Dundas chart output into a
> byte array:
> public static byte[] GenerateChart()
> {
> // chart rendering code
> ...
> // save chart image to byte[]
> System.IO.MemoryStream renderedImage = new MemoryStream();
> dundasChart.ImageType = ChartImageType.Png;
> dundasChart.Save(renderedImage);
> renderedImage.Position = 0;
> return renderedImage.ToArray();
> }
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Clint Jennings" <cjennings@.fusionAlliance.com> wrote in message
> news:%237PwafckEHA.896@.TK2MSFTNGP12.phx.gbl...
> > I have a Custom Dundas Charting library that I utilize normally. I'm
> having
> > troubles getting my chart object (and tried to return and image) to render
> > within my reports. I'm confident I have the custom assemblie referenced.
> I
> > don't want to use the chart field from the toolbox, I want to set it to
> what
> > my assemblie returns. Anyway to get around this? I'm aware of the custom
> > data extensions, although, I wanted to just have my existing assemblie
> which
> > returns charts do it instead. so all my reports that need graphs can just
> > reference the assemblie and graph that is needed. Anyway to accomplish
> this?
> > I'm getting data type reference errors etc.
> >
> > Clint
> >
> >
>
>
Subscribe to:
Posts (Atom)