Database Name

Hi,

Can anyone tell me how to return the current database name using T-SQL.

I have a stored proc that runs in a number of databases and would like qualify some dynamic sql with the database name. I know DB_Name exists but requires the DB_ID and DB_ID needs the DB_Name. Is there a function that just returns the current database i.e.

USE TestDB

GO

SELECT currentDBName?

Use the following function..

Select db_name()

|||

Cheers...

sql

Database Maintenance Plans - Not removing Files Older than x days

I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
database on the server and have created a database maintenance plan that
backups up all the database nightly. I have also configured the maintenance
plan to delete back files older than 3 days old but this isn't currently
happening.
I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
seen this problem first. Or if anyone can confirm SP4 fixes this issue.
You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
SP4 on another system to make sure your apps work correctly first, of course.
Have you checked the various logs SQL Server uses? There's a SQL Server Log,
a SQL Server Agent Log, and the Windows Application Event Log. They might
point out what is happening.
That being said, there is an extended stored procedure that Microsoft uses
to delete the files. If the Agent service doesn't have the proper rights to
the subdirectory you might find that to be the issue.
"Gary S" wrote:

> I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> database on the server and have created a database maintenance plan that
> backups up all the database nightly. I have also configured the maintenance
> plan to delete back files older than 3 days old but this isn't currently
> happening.
> I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> seen this problem first. Or if anyone can confirm SP4 fixes this issue.
>
|||What is the extended store procedure that's used so that I can check it
hasn't been removed.
"Buck Woody - Microsoft SQL Server Team" wrote:
[vbcol=seagreen]
> You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
> SP4 on another system to make sure your apps work correctly first, of course.
> Have you checked the various logs SQL Server uses? There's a SQL Server Log,
> a SQL Server Agent Log, and the Windows Application Event Log. They might
> point out what is happening.
> That being said, there is an extended stored procedure that Microsoft uses
> to delete the files. If the Agent service doesn't have the proper rights to
> the subdirectory you might find that to be the issue.
> "Gary S" wrote:
|||It's sp_delete_file - and it's undocumented, meaning unsupported for direct
use by users. Check this thread:
https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
"Gary S" wrote:
[vbcol=seagreen]
> What is the extended store procedure that's used so that I can check it
> hasn't been removed.
> "Buck Woody - Microsoft SQL Server Team" wrote:
|||Slight correction - that's XP_delete_file. Had an S in there!
"Buck Woody - Microsoft SQL Server Team" wrote:
[vbcol=seagreen]
> It's sp_delete_file - and it's undocumented, meaning unsupported for direct
> use by users. Check this thread:
> https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
>
> "Gary S" wrote:
|||On Feb 14, 8:35 am, Gary S <G...@.discussions.microsoft.com> wrote:
> I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> database on the server and have created a database maintenance plan that
> backups up all the database nightly. I have also configured the maintenance
> plan to delete back files older than 3 days old but this isn't currently
> happening.
> I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> seen this problem first. Or if anyone can confirm SP4 fixes this issue.
There are a couple of common causes for this:
1. The maintenance plan is configured to do transaction log backups,
but one of the databases included is in Simple mode. The plan fails
when it hits this database, and never reaches the "cleanup" step.
2. One of the files to be deleted is "in use" by another process,
such as a tape backup, causing the cleanup process to fail.
Right-click on the maintenance plan itself (not the Agent job), look
at the history, you should be able to get an actual error message from
there.
|||You have to be a sysadmin to run this stored proc, even in SP2 CTP. I have
been told a fix will be raised for a later service pack.
Chris
"Buck Woody - Microsoft SQL Server Team"
<BuckWoodyMicrosoftSQLServerTeam@.discussions.micro soft.com> wrote in message
news:0751636B-0F16-40EA-BAE8-8ADE57217570@.microsoft.com...[vbcol=seagreen]
> It's sp_delete_file - and it's undocumented, meaning unsupported for
> direct
> use by users. Check this thread:
> https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
>
> "Gary S" wrote:
|||Thanks Tracey a number of the database were set to Simple Recovery. I'm not a
big fan of database mainatenance plans (perfer setting up the jobs
individual) so as I'm not 100% certain why the databases are set to simple
I'll create seperate backup jobs.
"Tracy McKibben" wrote:

> On Feb 14, 8:35 am, Gary S <G...@.discussions.microsoft.com> wrote:
> There are a couple of common causes for this:
> 1. The maintenance plan is configured to do transaction log backups,
> but one of the databases included is in Simple mode. The plan fails
> when it hits this database, and never reaches the "cleanup" step.
> 2. One of the files to be deleted is "in use" by another process,
> such as a tape backup, causing the cleanup process to fail.
> Right-click on the maintenance plan itself (not the Agent job), look
> at the history, you should be able to get an actual error message from
> there.
>
|||On Feb 15, 5:13 am, Gary S <G...@.discussions.microsoft.com> wrote:
> Thanks Tracey a number of the database were set to Simple Recovery. I'm not a
> big fan of database mainatenance plans (perfer setting up the jobs
> individual) so as I'm not 100% certain why the databases are set to simple
> I'll create seperate backup jobs.
>
Here's a script of mine that might help you out:
http://realsqlguy.blogspot.com/2007/02/automating-database-backups.html

Database Maintenance Plans - Not removing Files Older than x days

I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
database on the server and have created a database maintenance plan that
backups up all the database nightly. I have also configured the maintenance
plan to delete back files older than 3 days old but this isn't currently
happening.
I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
seen this problem first. Or if anyone can confirm SP4 fixes this issue.You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
SP4 on another system to make sure your apps work correctly first, of course.
Have you checked the various logs SQL Server uses? There's a SQL Server Log,
a SQL Server Agent Log, and the Windows Application Event Log. They might
point out what is happening.
That being said, there is an extended stored procedure that Microsoft uses
to delete the files. If the Agent service doesn't have the proper rights to
the subdirectory you might find that to be the issue.
"Gary S" wrote:
> I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> database on the server and have created a database maintenance plan that
> backups up all the database nightly. I have also configured the maintenance
> plan to delete back files older than 3 days old but this isn't currently
> happening.
> I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> seen this problem first. Or if anyone can confirm SP4 fixes this issue.
>|||What is the extended store procedure that's used so that I can check it
hasn't been removed.
"Buck Woody - Microsoft SQL Server Team" wrote:
> You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
> SP4 on another system to make sure your apps work correctly first, of course.
> Have you checked the various logs SQL Server uses? There's a SQL Server Log,
> a SQL Server Agent Log, and the Windows Application Event Log. They might
> point out what is happening.
> That being said, there is an extended stored procedure that Microsoft uses
> to delete the files. If the Agent service doesn't have the proper rights to
> the subdirectory you might find that to be the issue.
> "Gary S" wrote:
> > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> > database on the server and have created a database maintenance plan that
> > backups up all the database nightly. I have also configured the maintenance
> > plan to delete back files older than 3 days old but this isn't currently
> > happening.
> >
> > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> > seen this problem first. Or if anyone can confirm SP4 fixes this issue.
> >
> >|||It's sp_delete_file - and it's undocumented, meaning unsupported for direct
use by users. Check this thread:
https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
"Gary S" wrote:
> What is the extended store procedure that's used so that I can check it
> hasn't been removed.
> "Buck Woody - Microsoft SQL Server Team" wrote:
> > You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
> > SP4 on another system to make sure your apps work correctly first, of course.
> >
> > Have you checked the various logs SQL Server uses? There's a SQL Server Log,
> > a SQL Server Agent Log, and the Windows Application Event Log. They might
> > point out what is happening.
> >
> > That being said, there is an extended stored procedure that Microsoft uses
> > to delete the files. If the Agent service doesn't have the proper rights to
> > the subdirectory you might find that to be the issue.
> >
> > "Gary S" wrote:
> >
> > > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> > > database on the server and have created a database maintenance plan that
> > > backups up all the database nightly. I have also configured the maintenance
> > > plan to delete back files older than 3 days old but this isn't currently
> > > happening.
> > >
> > > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> > > seen this problem first. Or if anyone can confirm SP4 fixes this issue.
> > >
> > >|||Slight correction - that's XP_delete_file. Had an S in there!
"Buck Woody - Microsoft SQL Server Team" wrote:
> It's sp_delete_file - and it's undocumented, meaning unsupported for direct
> use by users. Check this thread:
> https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
>
> "Gary S" wrote:
> > What is the extended store procedure that's used so that I can check it
> > hasn't been removed.
> >
> > "Buck Woody - Microsoft SQL Server Team" wrote:
> >
> > > You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
> > > SP4 on another system to make sure your apps work correctly first, of course.
> > >
> > > Have you checked the various logs SQL Server uses? There's a SQL Server Log,
> > > a SQL Server Agent Log, and the Windows Application Event Log. They might
> > > point out what is happening.
> > >
> > > That being said, there is an extended stored procedure that Microsoft uses
> > > to delete the files. If the Agent service doesn't have the proper rights to
> > > the subdirectory you might find that to be the issue.
> > >
> > > "Gary S" wrote:
> > >
> > > > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> > > > database on the server and have created a database maintenance plan that
> > > > backups up all the database nightly. I have also configured the maintenance
> > > > plan to delete back files older than 3 days old but this isn't currently
> > > > happening.
> > > >
> > > > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> > > > seen this problem first. Or if anyone can confirm SP4 fixes this issue.
> > > >
> > > >|||On Feb 14, 8:35 am, Gary S <G...@.discussions.microsoft.com> wrote:
> I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> database on the server and have created a database maintenance plan that
> backups up all the database nightly. I have also configured the maintenance
> plan to delete back files older than 3 days old but this isn't currently
> happening.
> I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> seen this problem first. Or if anyone can confirm SP4 fixes this issue.
There are a couple of common causes for this:
1. The maintenance plan is configured to do transaction log backups,
but one of the databases included is in Simple mode. The plan fails
when it hits this database, and never reaches the "cleanup" step.
2. One of the files to be deleted is "in use" by another process,
such as a tape backup, causing the cleanup process to fail.
Right-click on the maintenance plan itself (not the Agent job), look
at the history, you should be able to get an actual error message from
there.|||You have to be a sysadmin to run this stored proc, even in SP2 CTP. I have
been told a fix will be raised for a later service pack.
Chris
"Buck Woody - Microsoft SQL Server Team"
<BuckWoodyMicrosoftSQLServerTeam@.discussions.microsoft.com> wrote in message
news:0751636B-0F16-40EA-BAE8-8ADE57217570@.microsoft.com...
> It's sp_delete_file - and it's undocumented, meaning unsupported for
> direct
> use by users. Check this thread:
> https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
>
> "Gary S" wrote:
>> What is the extended store procedure that's used so that I can check it
>> hasn't been removed.
>> "Buck Woody - Microsoft SQL Server Team" wrote:
>> > You'll want to apply SP4 anyway, for lots of other reasons. Test the
>> > setup of
>> > SP4 on another system to make sure your apps work correctly first, of
>> > course.
>> >
>> > Have you checked the various logs SQL Server uses? There's a SQL Server
>> > Log,
>> > a SQL Server Agent Log, and the Windows Application Event Log. They
>> > might
>> > point out what is happening.
>> >
>> > That being said, there is an extended stored procedure that Microsoft
>> > uses
>> > to delete the files. If the Agent service doesn't have the proper
>> > rights to
>> > the subdirectory you might find that to be the issue.
>> >
>> > "Gary S" wrote:
>> >
>> > > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a
>> > > number of
>> > > database on the server and have created a database maintenance plan
>> > > that
>> > > backups up all the database nightly. I have also configured the
>> > > maintenance
>> > > plan to delete back files older than 3 days old but this isn't
>> > > currently
>> > > happening.
>> > >
>> > > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone
>> > > else has
>> > > seen this problem first. Or if anyone can confirm SP4 fixes this
>> > > issue.
>> > >
>> > >|||> It's sp_delete_file - and it's undocumented, meaning unsupported for direct
> use by users.
Buck,
Above is for 2005. For 2000, the files are removed by sqlmaint.exe (which is called by xp_sqlmaint
if the plan is created by the wizard).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Buck Woody - Microsoft SQL Server Team" <BuckWoodyMicrosoftSQLServerTeam@.discussions.microsoft.com>
wrote in message news:0751636B-0F16-40EA-BAE8-8ADE57217570@.microsoft.com...
> It's sp_delete_file - and it's undocumented, meaning unsupported for direct
> use by users. Check this thread:
> https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=718788&SiteID=17
>
> "Gary S" wrote:
>> What is the extended store procedure that's used so that I can check it
>> hasn't been removed.
>> "Buck Woody - Microsoft SQL Server Team" wrote:
>> > You'll want to apply SP4 anyway, for lots of other reasons. Test the setup of
>> > SP4 on another system to make sure your apps work correctly first, of course.
>> >
>> > Have you checked the various logs SQL Server uses? There's a SQL Server Log,
>> > a SQL Server Agent Log, and the Windows Application Event Log. They might
>> > point out what is happening.
>> >
>> > That being said, there is an extended stored procedure that Microsoft uses
>> > to delete the files. If the Agent service doesn't have the proper rights to
>> > the subdirectory you might find that to be the issue.
>> >
>> > "Gary S" wrote:
>> >
>> > > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
>> > > database on the server and have created a database maintenance plan that
>> > > backups up all the database nightly. I have also configured the maintenance
>> > > plan to delete back files older than 3 days old but this isn't currently
>> > > happening.
>> > >
>> > > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
>> > > seen this problem first. Or if anyone can confirm SP4 fixes this issue.
>> > >
>> > >|||Thanks Tracey a number of the database were set to Simple Recovery. I'm not a
big fan of database mainatenance plans (perfer setting up the jobs
individual) so as I'm not 100% certain why the databases are set to simple
I'll create seperate backup jobs.
"Tracy McKibben" wrote:
> On Feb 14, 8:35 am, Gary S <G...@.discussions.microsoft.com> wrote:
> > I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number of
> > database on the server and have created a database maintenance plan that
> > backups up all the database nightly. I have also configured the maintenance
> > plan to delete back files older than 3 days old but this isn't currently
> > happening.
> >
> > I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
> > seen this problem first. Or if anyone can confirm SP4 fixes this issue.
> There are a couple of common causes for this:
> 1. The maintenance plan is configured to do transaction log backups,
> but one of the databases included is in Simple mode. The plan fails
> when it hits this database, and never reaches the "cleanup" step.
> 2. One of the files to be deleted is "in use" by another process,
> such as a tape backup, causing the cleanup process to fail.
> Right-click on the maintenance plan itself (not the Agent job), look
> at the history, you should be able to get an actual error message from
> there.
>|||On Feb 15, 5:13 am, Gary S <G...@.discussions.microsoft.com> wrote:
> Thanks Tracey a number of the database were set to Simple Recovery. I'm not a
> big fan of database mainatenance plans (perfer setting up the jobs
> individual) so as I'm not 100% certain why the databases are set to simple
> I'll create seperate backup jobs.
>
Here's a script of mine that might help you out:
http://realsqlguy.blogspot.com/2007/02/automating-database-backups.html

Database Maintenance Plans - Not removing Files Older than x days

I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number o
f
database on the server and have created a database maintenance plan that
backups up all the database nightly. I have also configured the maintenance
plan to delete back files older than 3 days old but this isn't currently
happening.
I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else has
seen this problem first. Or if anyone can confirm SP4 fixes this issue.You'll want to apply SP4 anyway, for lots of other reasons. Test the setup o
f
SP4 on another system to make sure your apps work correctly first, of course
.
Have you checked the various logs SQL Server uses? There's a SQL Server Log,
a SQL Server Agent Log, and the Windows Application Event Log. They might
point out what is happening.
That being said, there is an extended stored procedure that Microsoft uses
to delete the files. If the Agent service doesn't have the proper rights to
the subdirectory you might find that to be the issue.
"Gary S" wrote:

> I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number
of
> database on the server and have created a database maintenance plan that
> backups up all the database nightly. I have also configured the maintenanc
e
> plan to delete back files older than 3 days old but this isn't currently
> happening.
> I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else h
as
> seen this problem first. Or if anyone can confirm SP4 fixes this issue.
>|||On Feb 14, 8:35 am, Gary S <G...@.discussions.microsoft.com> wrote:
> I have a Windows 2000 SP4 Cluster running MS SQL 2000 SP3, I have a number
of
> database on the server and have created a database maintenance plan that
> backups up all the database nightly. I have also configured the maintenanc
e
> plan to delete back files older than 3 days old but this isn't currently
> happening.
> I haven't applied SP4 to SQL 2000 yet as I wanted to know if anyone else h
as
> seen this problem first. Or if anyone can confirm SP4 fixes this issue.
There are a couple of common causes for this:
1. The maintenance plan is configured to do transaction log backups,
but one of the databases included is in Simple mode. The plan fails
when it hits this database, and never reaches the "cleanup" step.
2. One of the files to be deleted is "in use" by another process,
such as a tape backup, causing the cleanup process to fail.
Right-click on the maintenance plan itself (not the Agent job), look
at the history, you should be able to get an actual error message from
there.

Database Maintenance Plan for SQL Server 2005 Problem

I am new to SQL Server 2005 and just set up a number of DB Maintenance Plan.
However, I find that the transaction log backup job doesn't run as expected.
I have checked and found that SQL Server Agent is already started. Is there
any way to check where the problem is ?
Besides, when I am creating the database maintenance plan, I find that I am
not able to create both DB Backup (early in the morning) and Transaction log
backup (hourly) by using the same DB Maintenance Plan. Does it mean that I
have to create 2 different plans with the wizard ?
Moreover, for weekly maintenance, database integrity check, rebuild index,
database backup ... it seems that they are run at the same time. Is there
any way to change the time they are running ?
Thanks
PeterHi Peter
If you edit the plan in Management Studio, there is a logging button to send
information to a file, which also has an extended logging check box. If these
are set up you may have information in this file.
I would expect you would want transaction log backups and full backups to be
scheduled separately and therefore the easiest solution would be to have two
plans. I don't think you can add conditional execution in the maintenance
wizard, but you could use BIDS to create a SSIS package that does the
maintenance tasks for you and then add a script task to make what backup you
do conditional.
John
"Peter" wrote:
> I am new to SQL Server 2005 and just set up a number of DB Maintenance Plan.
> However, I find that the transaction log backup job doesn't run as expected.
> I have checked and found that SQL Server Agent is already started. Is there
> any way to check where the problem is ?
> Besides, when I am creating the database maintenance plan, I find that I am
> not able to create both DB Backup (early in the morning) and Transaction log
> backup (hourly) by using the same DB Maintenance Plan. Does it mean that I
> have to create 2 different plans with the wizard ?
> Moreover, for weekly maintenance, database integrity check, rebuild index,
> database backup ... it seems that they are run at the same time. Is there
> any way to change the time they are running ?
> Thanks
> Peter
>
>|||> However, I find that the transaction log backup job doesn't run as expected. I have checked and
> found that SQL Server Agent is already started. Is there any way to check where the problem is ?
Most commonly this is because you have included some databases that are in simple recovery mode.
When a maint plan is returned an error inside a task, it will terminate that task.
> Besides, when I am creating the database maintenance plan, I find that I am not able to create
> both DB Backup (early in the morning) and Transaction log backup (hourly) by using the same DB
> Maintenance Plan. Does it mean that I have to create 2 different plans with the wizard ?
Yes. But in sp2, you will be able to have separate schedules for each task. I don't see this as a
big thing since you will still have to create at least two tasks:
One for the databases in simple recovery (master, for example), where you don't do log backup.
And another for the database where you do want to do log backups...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:uUYrZRQHHHA.2236@.TK2MSFTNGP02.phx.gbl...
>I am new to SQL Server 2005 and just set up a number of DB Maintenance Plan.
> However, I find that the transaction log backup job doesn't run as expected. I have checked and
> found that SQL Server Agent is already started. Is there any way to check where the problem is ?
> Besides, when I am creating the database maintenance plan, I find that I am not able to create
> both DB Backup (early in the morning) and Transaction log backup (hourly) by using the same DB
> Maintenance Plan. Does it mean that I have to create 2 different plans with the wizard ?
> Moreover, for weekly maintenance, database integrity check, rebuild index, database backup ... it
> seems that they are run at the same time. Is there any way to change the time they are running ?
> Thanks
> Peter
>|||Dear John and Tibor,
Many thanks for your advice.
To my surprise, I find that the transaction log backup doesn't performed
yesterday (It supposed to start immediately) but it works properly today.
The daily production database backup also doesn't performed last night at
8:00pm. On the other hand, the daily system database backup is performed
successfully at 2:00am this morning. It is pretty weird.
I believe that it may be because when I set up the server, the time is set
at 9:00pm and I change it to 3:30pm later. Would it be a reason for the
problem ?
Besides, from the maintenance log file, it only mentions that the
maintenance plan is executed but it hasn't mentioned any error encountered
(It seems that it hasn't executed and there is of course no error). If
there is problem when the maintenance plan is executed, will the error
message be shown on the log files OR should be found in SQL Error Log ?
Thanks
Peter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:etjWvuRHHHA.4904@.TK2MSFTNGP04.phx.gbl...
>> However, I find that the transaction log backup job doesn't run as
>> expected. I have checked and found that SQL Server Agent is already
>> started. Is there any way to check where the problem is ?
> Most commonly this is because you have included some databases that are in
> simple recovery mode. When a maint plan is returned an error inside a
> task, it will terminate that task.
>
>> Besides, when I am creating the database maintenance plan, I find that I
>> am not able to create both DB Backup (early in the morning) and
>> Transaction log backup (hourly) by using the same DB Maintenance Plan.
>> Does it mean that I have to create 2 different plans with the wizard ?
> Yes. But in sp2, you will be able to have separate schedules for each
> task. I don't see this as a big thing since you will still have to create
> at least two tasks:
> One for the databases in simple recovery (master, for example), where you
> don't do log backup.
> And another for the database where you do want to do log backups...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:uUYrZRQHHHA.2236@.TK2MSFTNGP02.phx.gbl...
>>I am new to SQL Server 2005 and just set up a number of DB Maintenance
>>Plan.
>> However, I find that the transaction log backup job doesn't run as
>> expected. I have checked and found that SQL Server Agent is already
>> started. Is there any way to check where the problem is ?
>> Besides, when I am creating the database maintenance plan, I find that I
>> am not able to create both DB Backup (early in the morning) and
>> Transaction log backup (hourly) by using the same DB Maintenance Plan.
>> Does it mean that I have to create 2 different plans with the wizard ?
>> Moreover, for weekly maintenance, database integrity check, rebuild
>> index, database backup ... it seems that they are run at the same time.
>> Is there any way to change the time they are running ?
>> Thanks
>> Peter
>|||> I believe that it may be because when I set up the server, the time is set at 9:00pm and I change
> it to 3:30pm later. Would it be a reason for the problem ?
I vaguelly recall others seeing similar issues, so I'd say that this is a likely cause.
> Besides, from the maintenance log file, it only mentions that the maintenance plan is executed but
> it hasn't mentioned any error encountered (It seems that it hasn't executed and there is of course
> no error).
You mean the Maint Plan report file? In general, when I don't see any errors, I assume that the
statements were executed with success.
> If there is problem when the maintenance plan is executed, will the error message be shown on the
> log files OR should be found in SQL Error Log ?
I haven't played enough with 2005 maint plans to say for sure what is logged to the various log
destinations (report file, maint plan history tables etc). Whether an error is logged to the event
log depends on that error (see the sys.messages catalog view).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:%23CIMj3oHHHA.816@.TK2MSFTNGP06.phx.gbl...
> Dear John and Tibor,
> Many thanks for your advice.
> To my surprise, I find that the transaction log backup doesn't performed yesterday (It supposed to
> start immediately) but it works properly today. The daily production database backup also doesn't
> performed last night at 8:00pm. On the other hand, the daily system database backup is performed
> successfully at 2:00am this morning. It is pretty weird.
> I believe that it may be because when I set up the server, the time is set at 9:00pm and I change
> it to 3:30pm later. Would it be a reason for the problem ?
> Besides, from the maintenance log file, it only mentions that the maintenance plan is executed but
> it hasn't mentioned any error encountered (It seems that it hasn't executed and there is of course
> no error). If there is problem when the maintenance plan is executed, will the error message be
> shown on the log files OR should be found in SQL Error Log ?
> Thanks
> Peter
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:etjWvuRHHHA.4904@.TK2MSFTNGP04.phx.gbl...
>> However, I find that the transaction log backup job doesn't run as expected. I have checked and
>> found that SQL Server Agent is already started. Is there any way to check where the problem is
>> ?
>> Most commonly this is because you have included some databases that are in simple recovery mode.
>> When a maint plan is returned an error inside a task, it will terminate that task.
>>
>> Besides, when I am creating the database maintenance plan, I find that I am not able to create
>> both DB Backup (early in the morning) and Transaction log backup (hourly) by using the same DB
>> Maintenance Plan. Does it mean that I have to create 2 different plans with the wizard ?
>> Yes. But in sp2, you will be able to have separate schedules for each task. I don't see this as a
>> big thing since you will still have to create at least two tasks:
>> One for the databases in simple recovery (master, for example), where you don't do log backup.
>> And another for the database where you do want to do log backups...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Peter" <Peter@.discussions.microsoft.com> wrote in message
>> news:uUYrZRQHHHA.2236@.TK2MSFTNGP02.phx.gbl...
>>I am new to SQL Server 2005 and just set up a number of DB Maintenance Plan.
>> However, I find that the transaction log backup job doesn't run as expected. I have checked and
>> found that SQL Server Agent is already started. Is there any way to check where the problem is
>> ?
>> Besides, when I am creating the database maintenance plan, I find that I am not able to create
>> both DB Backup (early in the morning) and Transaction log backup (hourly) by using the same DB
>> Maintenance Plan. Does it mean that I have to create 2 different plans with the wizard ?
>> Moreover, for weekly maintenance, database integrity check, rebuild index, database backup ...
>> it seems that they are run at the same time. Is there any way to change the time they are
>> running ?
>> Thanks
>> Peter
>>
>

Database Maintenance Plan for SQL Server 2005 Problem

I am new to SQL Server 2005 and just set up a number of DB Maintenance Plan.
However, I find that the transaction log backup job doesn't run as expected.
I have checked and found that SQL Server Agent is already started. Is there
any way to check where the problem is ?
Besides, when I am creating the database maintenance plan, I find that I am
not able to create both DB Backup (early in the morning) and Transaction log
backup (hourly) by using the same DB Maintenance Plan. Does it mean that I
have to create 2 different plans with the wizard ?
Moreover, for weekly maintenance, database integrity check, rebuild index,
database backup ... it seems that they are run at the same time. Is there
any way to change the time they are running ?
Thanks
PeterHi Peter
If you edit the plan in Management Studio, there is a logging button to send
information to a file, which also has an extended logging check box. If thes
e
are set up you may have information in this file.
I would expect you would want transaction log backups and full backups to be
scheduled separately and therefore the easiest solution would be to have two
plans. I don't think you can add conditional execution in the maintenance
wizard, but you could use BIDS to create a SSIS package that does the
maintenance tasks for you and then add a script task to make what backup you
do conditional.
John
"Peter" wrote:

> I am new to SQL Server 2005 and just set up a number of DB Maintenance Pla
n.
> However, I find that the transaction log backup job doesn't run as expecte
d.
> I have checked and found that SQL Server Agent is already started. Is the
re
> any way to check where the problem is ?
> Besides, when I am creating the database maintenance plan, I find that I a
m
> not able to create both DB Backup (early in the morning) and Transaction l
og
> backup (hourly) by using the same DB Maintenance Plan. Does it mean that
I
> have to create 2 different plans with the wizard ?
> Moreover, for weekly maintenance, database integrity check, rebuild index,
> database backup ... it seems that they are run at the same time. Is there
> any way to change the time they are running ?
> Thanks
> Peter
>
>|||> However, I find that the transaction log backup job doesn't run as expected. I have checke
d and
> found that SQL Server Agent is already started. Is there any way to check where t
he problem is ?
Most commonly this is because you have included some databases that are in s
imple recovery mode.
When a maint plan is returned an error inside a task, it will terminate that
task.

> Besides, when I am creating the database maintenance plan, I find that I a
m not able to create
> both DB Backup (early in the morning) and Transaction log backup (hourly)
by using the same DB
> Maintenance Plan. Does it mean that I have to create 2 different plans with the w
izard ?
Yes. But in sp2, you will be able to have separate schedules for each task.
I don't see this as a
big thing since you will still have to create at least two tasks:
One for the databases in simple recovery (master, for example), where you do
n't do log backup.
And another for the database where you do want to do log backups...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:uUYrZRQHHHA.2236@.TK2MSFTNGP02.phx.gbl...
>I am new to SQL Server 2005 and just set up a number of DB Maintenance Plan
.
> However, I find that the transaction log backup job doesn't run as expecte
d. I have checked and
> found that SQL Server Agent is already started. Is there any way to check
where the problem is ?
> Besides, when I am creating the database maintenance plan, I find that I a
m not able to create
> both DB Backup (early in the morning) and Transaction log backup (hourly)
by using the same DB
> Maintenance Plan. Does it mean that I have to create 2 different plans wi
th the wizard ?
> Moreover, for weekly maintenance, database integrity check, rebuild index,
database backup ... it
> seems that they are run at the same time. Is there any way to change the
time they are running ?
> Thanks
> Peter
>|||Dear John and Tibor,
Many thanks for your advice.
To my surprise, I find that the transaction log backup doesn't performed
yesterday (It supposed to start immediately) but it works properly today.
The daily production database backup also doesn't performed last night at
8:00pm. On the other hand, the daily system database backup is performed
successfully at 2:00am this morning. It is pretty weird.
I believe that it may be because when I set up the server, the time is set
at 9:00pm and I change it to 3:30pm later. Would it be a reason for the
problem ?
Besides, from the maintenance log file, it only mentions that the
maintenance plan is executed but it hasn't mentioned any error encountered
(It seems that it hasn't executed and there is of course no error). If
there is problem when the maintenance plan is executed, will the error
message be shown on the log files OR should be found in SQL Error Log ?
Thanks
Peter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:etjWvuRHHHA.4904@.TK2MSFTNGP04.phx.gbl...
> Most commonly this is because you have included some databases that are in
> simple recovery mode. When a maint plan is returned an error inside a
> task, it will terminate that task.
>
> Yes. But in sp2, you will be able to have separate schedules for each
> task. I don't see this as a big thing since you will still have to create
> at least two tasks:
> One for the databases in simple recovery (master, for example), where you
> don't do log backup.
> And another for the database where you do want to do log backups...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:uUYrZRQHHHA.2236@.TK2MSFTNGP02.phx.gbl...
>|||> I believe that it may be because when I set up the server, the time is set at 9:00pm and I
change
> it to 3:30pm later. Would it be a reason for the problem ?
I vaguelly recall others seeing similar issues, so I'd say that this is a li
kely cause.

> Besides, from the maintenance log file, it only mentions that the maintena
nce plan is executed but
> it hasn't mentioned any error encountered (It seems that it hasn't execute
d and there is of course
> no error).
You mean the Maint Plan report file? In general, when I don't see any errors
, I assume that the
statements were executed with success.

> If there is problem when the maintenance plan is executed, will the error
message be shown on the
> log files OR should be found in SQL Error Log ?
I haven't played enough with 2005 maint plans to say for sure what is logged
to the various log
destinations (report file, maint plan history tables etc). Whether an error
is logged to the event
log depends on that error (see the sys.messages catalog view).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:%23CIMj3oHHHA.816@.TK2MSFTNGP06.phx.gbl...
> Dear John and Tibor,
> Many thanks for your advice.
> To my surprise, I find that the transaction log backup doesn't performed y
esterday (It supposed to
> start immediately) but it works properly today. The daily production datab
ase backup also doesn't
> performed last night at 8:00pm. On the other hand, the daily system datab
ase backup is performed
> successfully at 2:00am this morning. It is pretty weird.
> I believe that it may be because when I set up the server, the time is set
at 9:00pm and I change
> it to 3:30pm later. Would it be a reason for the problem ?
> Besides, from the maintenance log file, it only mentions that the maintena
nce plan is executed but
> it hasn't mentioned any error encountered (It seems that it hasn't execute
d and there is of course
> no error). If there is problem when the maintenance plan is executed, wil
l the error message be
> shown on the log files OR should be found in SQL Error Log ?
> Thanks
> Peter
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:etjWvuRHHHA.4904@.TK2MSFTNGP04.phx.gbl...
>

Database Maintenance - no backups being removed

Database maintenance plan doesn't allow me to choose to
delete files older than x number of days/weeks. I can
choose the option, input a number, but the drop down for
days/weeks contains no items.
I Jon,
a friend tell me to add in the registry the string value BackupDirectory
with default C: at the address
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\"Instance_Na me"\MSSQLServer...
and this work!!!
Mirco Scott
mailto: mscotta@.giada.it
Giada s.r.l.
<jon.epstein@.infov{REMOVE}.com> ha scritto nel messaggio
news:2cde01c42886$1f8f4bf0$a401280a@.phx.gbl...
> Database maintenance plan doesn't allow me to choose to
> delete files older than x number of days/weeks. I can
> choose the option, input a number, but the drop down for
> days/weeks contains no items.

Database Limititations

Is there a maximum number of databases, tables, columns
for SQL 2000? I have found much info on max RAM, but
nothing on these 3 characteristics, i.e. databases,
tables, columns.http://www.aspfaq.com/2345
http://www.aspfaq.com/
(Reverse address to reply.)
"Charlie Johnson" <charlie.johnson@.ahlcorp.com> wrote in message
news:1715901c448b2$cbd24470$a301280a@.phx
.gbl...
> Is there a maximum number of databases, tables, columns
> for SQL 2000? I have found much info on max RAM, but
> nothing on these 3 characteristics, i.e. databases,
> tables, columns.|||Maximum number of databases: 32,767
Maximum number of objects in a databases: 2,147,483,647 (see here for more
information on this
8dbn.asp" target="_blank">http://msdn.microsoft.com/library/d...br />
8dbn.asp)
Not sure about the number of columns, but since a row size can only be 8 K
you will more than likely max out on row size prior to maxing out on the
number of columns.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Charlie Johnson" <charlie.johnson@.ahlcorp.com> wrote in message
news:1715901c448b2$cbd24470$a301280a@.phx
.gbl...
> Is there a maximum number of databases, tables, columns
> for SQL 2000? I have found much info on max RAM, but
> nothing on these 3 characteristics, i.e. databases,
> tables, columns.|||Just ran a test of creating a table with lots of columns and got this
message:
CREATE TABLE failed because column 'c1025' in table 'test' exceeds the
maximum of 1024 columns.
So it looks like 1024 is the limit on the number of columns.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:eeKW9ULSEHA.1208@.TK2MSFTNGP09.phx.gbl...
> Maximum number of databases: 32,767
> Maximum number of objects in a databases: 2,147,483,647 (see here for more
> information on this
>
http://msdn.microsoft.com/library/d..._ar_ts_8dbn.asp)[
vbcol=seagreen]
> Not sure about the number of columns, but since a row size can only be 8 K
> you will more than likely max out on row size prior to maxing out on the
> number of columns.
>
> --
> ----[/vbcol]
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Charlie Johnson" <charlie.johnson@.ahlcorp.com> wrote in message
> news:1715901c448b2$cbd24470$a301280a@.phx
.gbl...
>|||Maximum Capacity Specifications:
http://msdn.microsoft.com/library/d...y/en-us/archite
c/8_ar_ts_8dbn.asp
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Database Limititations

Is there a maximum number of databases, tables, columns
for SQL 2000? I have found much info on max RAM, but
nothing on these 3 characteristics, i.e. databases,
tables, columns.
http://www.aspfaq.com/2345
http://www.aspfaq.com/
(Reverse address to reply.)
"Charlie Johnson" <charlie.johnson@.ahlcorp.com> wrote in message
news:1715901c448b2$cbd24470$a301280a@.phx.gbl...
> Is there a maximum number of databases, tables, columns
> for SQL 2000? I have found much info on max RAM, but
> nothing on these 3 characteristics, i.e. databases,
> tables, columns.
|||Maximum number of databases: 32,767
Maximum number of objects in a databases: 2,147,483,647 (see here for more
information on this
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp)
Not sure about the number of columns, but since a row size can only be 8 K
you will more than likely max out on row size prior to maxing out on the
number of columns.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Charlie Johnson" <charlie.johnson@.ahlcorp.com> wrote in message
news:1715901c448b2$cbd24470$a301280a@.phx.gbl...
> Is there a maximum number of databases, tables, columns
> for SQL 2000? I have found much info on max RAM, but
> nothing on these 3 characteristics, i.e. databases,
> tables, columns.
|||Just ran a test of creating a table with lots of columns and got this
message:
CREATE TABLE failed because column 'c1025' in table 'test' exceeds the
maximum of 1024 columns.
So it looks like 1024 is the limit on the number of columns.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:eeKW9ULSEHA.1208@.TK2MSFTNGP09.phx.gbl...
> Maximum number of databases: 32,767
> Maximum number of objects in a databases: 2,147,483,647 (see here for more
> information on this
>
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp)
> Not sure about the number of columns, but since a row size can only be 8 K
> you will more than likely max out on row size prior to maxing out on the
> number of columns.
>
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Charlie Johnson" <charlie.johnson@.ahlcorp.com> wrote in message
> news:1715901c448b2$cbd24470$a301280a@.phx.gbl...
>
|||Maximum Capacity Specifications:
http://msdn.microsoft.com/library/de.../en-us/archite
c/8_ar_ts_8dbn.asp
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Database Limititations

Is there a maximum number of databases, tables, columns
for SQL 2000? I have found much info on max RAM, but
nothing on these 3 characteristics, i.e. databases,
tables, columns.http://www.aspfaq.com/2345
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Charlie Johnson" <charlie.johnson@.ahlcorp.com> wrote in message
news:1715901c448b2$cbd24470$a301280a@.phx.gbl...
> Is there a maximum number of databases, tables, columns
> for SQL 2000? I have found much info on max RAM, but
> nothing on these 3 characteristics, i.e. databases,
> tables, columns.|||Maximum number of databases: 32,767
Maximum number of objects in a databases: 2,147,483,647 (see here for more
information on this
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp)
Not sure about the number of columns, but since a row size can only be 8 K
you will more than likely max out on row size prior to maxing out on the
number of columns.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Charlie Johnson" <charlie.johnson@.ahlcorp.com> wrote in message
news:1715901c448b2$cbd24470$a301280a@.phx.gbl...
> Is there a maximum number of databases, tables, columns
> for SQL 2000? I have found much info on max RAM, but
> nothing on these 3 characteristics, i.e. databases,
> tables, columns.|||Just ran a test of creating a table with lots of columns and got this
message:
CREATE TABLE failed because column 'c1025' in table 'test' exceeds the
maximum of 1024 columns.
So it looks like 1024 is the limit on the number of columns.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:eeKW9ULSEHA.1208@.TK2MSFTNGP09.phx.gbl...
> Maximum number of databases: 32,767
> Maximum number of objects in a databases: 2,147,483,647 (see here for more
> information on this
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp)
> Not sure about the number of columns, but since a row size can only be 8 K
> you will more than likely max out on row size prior to maxing out on the
> number of columns.
>
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Charlie Johnson" <charlie.johnson@.ahlcorp.com> wrote in message
> news:1715901c448b2$cbd24470$a301280a@.phx.gbl...
> > Is there a maximum number of databases, tables, columns
> > for SQL 2000? I have found much info on max RAM, but
> > nothing on these 3 characteristics, i.e. databases,
> > tables, columns.
>

Database limit?

Is there a limit to the number of databases that a MSDE instance can support?
AlwaysLearning
Not in number only in size (2GB)
HTH, Jens Smeyer
https://www.sqlserver2005.de
"AlwaysLearning" <AlwaysLearning@.discussions.microsoft.com> schrieb im
Newsbeitrag news:585A5332-B8E9-48CF-A6A8-848603A00B4F@.microsoft.com...
> Is there a limit to the number of databases that a MSDE instance can
> support?
> --
> AlwaysLearning