Database Overwritten: system

Showing posts with label system. Show all posts

Thursday, March 29, 2012

Database naming convention

Is there database, tables, columns naming convention that I should follow
when I build some information system proposed from Microsoft?
Thanks in advanced
AdnanThere have been many discussions regarding this.
http://groups-beta.google.com/group...ming+convention
-oj
"Adnan Hebibovic" <adnan.hebibovic@.community.nospam> wrote in message
news:et0c85YFFHA.3732@.tk2msftngp13.phx.gbl...
> Is there database, tables, columns naming convention that I should follow
> when I build some information system proposed from Microsoft?
> Thanks in advanced
> Adnan
>|||Yes I know that oj but i will repeat myselft ... "from Microsoft".
"oj" <nospam_ojngo@.home.com> wrote in message
news:OmBDpEZFFHA.3728@.TK2MSFTNGP14.phx.gbl...
> There have been many discussions regarding this.
> http://groups-beta.google.com/group...ming+convention
>
> --
> -oj
>
> "Adnan Hebibovic" <adnan.hebibovic@.community.nospam> wrote in message
> news:et0c85YFFHA.3732@.tk2msftngp13.phx.gbl...
>|||Look up the ISO-11179 Standards first.
I have a new book, SQL PROGRAMMING STYLE, due out the middle of this
year that deals with this topic.
I bothered to research readability, typography, industry standards,
etc. I also get into some common design flaws, heuristics, software
metrics, etc.
I am hoping that people will pick it up and use it as a guide in their
shops. Even if they don't like it, they will have a single set of
rules. And they can yell at me :)|||Thanks
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1108827223.033936.154350@.o13g2000cwo.googlegroups.com...
> Look up the ISO-11179 Standards first.
> I have a new book, SQL PROGRAMMING STYLE, due out the middle of this
> year that deals with this topic.
> I bothered to research readability, typography, industry standards,
> etc. I also get into some common design flaws, heuristics, software
> metrics, etc.
> I am hoping that people will pick it up and use it as a guide in their
> shops. Even if they don't like it, they will have a single set of
> rules. And they can yell at me :)
>sql

Sunday, March 25, 2012

Database mirroring and Scala ERP system

We are using in our company an ERP system called iScala.

This software is using SQL server as DBMS.

We would like to use the SQL mirroring feature from SQL 2005.

The software is using the SQL server instance name to connect to databases and when the Main SQL server fails the application can't connect to the Mirror instance.

Unfortunatelly we can't change the connection string (the software is provided by another company).

Do you have a solution?

Thanks

I believe you have to contact the vendor to provide such changes, as this is application issue to talk to the mirror server in the event of failover. If the application is not db mirror aware then you have to find a route from connectivity to talk to other server in such cases.|||

Thanks

You are right.

I'm checking now some solutions for High Availability from Symantec (VERITAS Storage Foundation HA 4.3 for Windows) and Double-Take.

Do you have any information about them?

|||

You could solve this issue using a manual change to DNS.

i.e. MYLIVESQL -> 10.0.0.1 - connection string uses MYLIVESQL as the server name, in the event of a failure you update the DNS record to MYLIVESQL -> 10.0.0.2 (which is the IP address of the mirror server).

Andrew

|||

If the (former) principal instance is still up and running but the database failover occured, then SNAC will automatically learn the location of the mirror and redirect the connection to the mirror, as described here: http://msdn2.microsoft.com/en-us/library/ms366348.aspx. But this works only if the connection string specifies the database and if the principal instance is still running.

But if the vendor is not providing support for mirroring, is highly unlikely that the application will actually work with mirrored database, it may have dependencies outside the database that are not gonna failover (e.g. logins resulting in orphaned users, jobs in msdb etc)

HTH,
~ Remus

|||

Remus,

MOSS 2007 and WSS V3 fall into the category of not supporting mirroring in the connection strings, hence the need to use the cname approach. Microsoft recommend using stsadm to manually change the settings which I think is less elegant.

Andrew

Wednesday, March 21, 2012

Database mirror error Msg 1447, Level 16, State 21, Line 1

we config our SAP system to use SQL Server 2005 database mirror. but the mirror server hang by accident, after restart mirror server,the server return to normal,but the mirror can't be resume.

ALTER DATABASE R3P
SET PARTNER resume

the error is:
Msg 1447, Level 16, State 21, Line 1
ALTER DATABASE "R3P" command cannot be executed until both partner server instances are up, running, and connected. Start the partner and reissue the command.

Hi David,

Can you please take a look at the SQL Server errorlog (on both the principal and mirror) and post the errors that are reported there at the time the command was executed.

Also, what does "SELECT * FROM sys.database_mirroring" return on each partner?

Thanks,

Kaloian.

|||Hi Kaloian,

thank you for your reply.

now the mirrow can running normally after restart the primary server.i guess that the server's issue caused the mirror can't send log to the mirror server.

when i run the following query in both server:
select * from sys.dm_db_mirroring_connections
two rows return in primary server and one row return in mirror server.so i restart the primary server after work time,then the mirror resume and about 50G log has been resent successfully.

in the error log,i can't find the ralted error except:
The alert for 'unrecovered log' has been raised. The current value of '30' surpasses the threshold '10'.
and
An I/O operation initiated by the Registry failed unrecoverably. The Registry could not read in, or write out, or flush, one of the files that contain the system's image of the Registry.

Server information:
OS:win 2003 enterprise SP1
SQL:SQL 2005 enterprise SP1
CPU:16
Memory:16G
disk:about 16T

Thanks.

Monday, March 19, 2012

Database marked suspect

I have a database server and a 3 workstations running on
Windows 2000. Due to a power failure (UPS included), the
whole system rebooted. However, the workstations cannot
acces the database. Upon checking the server directory,
the database is there (C:\Program Files\Microsoft SQL
Server\MSSQL\Data). Before attempting anything else we
tried to make a backup copy of the directory; all files
copy except ContinuumLog.ldf, which shows a CRC error and
cannot copy.
Using the Enterprise Manger we tried to look at the
database and found that it is marked "Suspect" by SQL.
Checking the Help menu, there seem to be a number of
procedures to change the "Suspect" mark and recover the
database. As you may have guessed by now, there are no
backups in the disk.
Can anyone help us? Ideally we need a procedure, or,
failing that, where can we look up a procedure to recover
the database.
Thank you all,
TaliaFrom EM or Query Analyzer, are you able to do anything ? If you can, create
a new database and copy all the data from the bad db to the new db.
-Jimmy
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||Hi,
Please restore from good backup if you have CRC errors. If you do not have
the Backup try the below steps.
1. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
2. Restart sql server. now the database will be in emergency mode ( You
could access the database)
3. Create a new database and use DTS to transfer the data and object to the
new database.
4. Verify all the objects and data is available in new database.
Thanks
Hari
MCDBA
"Talia Sara-Lafosse" <saralafosse.t@.pucp.edu.pe> wrote in message
news:138a01c4853a$21db8b10$a301280a@.phx.gbl...
> I have a database server and a 3 workstations running on
> Windows 2000. Due to a power failure (UPS included), the
> whole system rebooted. However, the workstations cannot
> acces the database. Upon checking the server directory,
> the database is there (C:\Program Files\Microsoft SQL
> Server\MSSQL\Data). Before attempting anything else we
> tried to make a backup copy of the directory; all files
> copy except ContinuumLog.ldf, which shows a CRC error and
> cannot copy.
> Using the Enterprise Manger we tried to look at the
> database and found that it is marked "Suspect" by SQL.
> Checking the Help menu, there seem to be a number of
> procedures to change the "Suspect" mark and recover the
> database. As you may have guessed by now, there are no
> backups in the disk.
> Can anyone help us? Ideally we need a procedure, or,
> failing that, where can we look up a procedure to recover
> the database.
> Thank you all,
> Talia
>|||The problem is that i can not copy the damaged file; and i
dont't know what to do with the data with the LDF file
damaged.

>--Original Message--
>From EM or Query Analyzer, are you able to do anything ?
If you can, create a new database and copy all the data
from the bad db to the new db.
>-Jimmy
> ****************************************
******************
************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of
links to ASP & ASP.NET resources...
>.
>

Database marked suspect

I have a database server and a 3 workstations running on
Windows 2000. Due to a power failure (UPS included), the
whole system rebooted. However, the workstations cannot
acces the database. Upon checking the server directory,
the database is there (C:\Program Files\Microsoft SQL
Server\MSSQL\Data). Before attempting anything else we
tried to make a backup copy of the directory; all files
copy except ContinuumLog.ldf, which shows a CRC error and
cannot copy.
Using the Enterprise Manger we tried to look at the
database and found that it is marked "Suspect" by SQL.
Checking the Help menu, there seem to be a number of
procedures to change the "Suspect" mark and recover the
database. As you may have guessed by now, there are no
backups in the disk.
Can anyone help us? Ideally we need a procedure, or,
failing that, where can we look up a procedure to recover
the database.
Thank you all,
Talia
From EM or Query Analyzer, are you able to do anything ? If you can, create a new database and copy all the data from the bad db to the new db.
-Jimmy
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Hi,
Please restore from good backup if you have CRC errors. If you do not have
the Backup try the below steps.
1. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
2. Restart sql server. now the database will be in emergency mode ( You
could access the database)
3. Create a new database and use DTS to transfer the data and object to the
new database.
4. Verify all the objects and data is available in new database.
Thanks
Hari
MCDBA
"Talia Sara-Lafosse" <saralafosse.t@.pucp.edu.pe> wrote in message
news:138a01c4853a$21db8b10$a301280a@.phx.gbl...
> I have a database server and a 3 workstations running on
> Windows 2000. Due to a power failure (UPS included), the
> whole system rebooted. However, the workstations cannot
> acces the database. Upon checking the server directory,
> the database is there (C:\Program Files\Microsoft SQL
> Server\MSSQL\Data). Before attempting anything else we
> tried to make a backup copy of the directory; all files
> copy except ContinuumLog.ldf, which shows a CRC error and
> cannot copy.
> Using the Enterprise Manger we tried to look at the
> database and found that it is marked "Suspect" by SQL.
> Checking the Help menu, there seem to be a number of
> procedures to change the "Suspect" mark and recover the
> database. As you may have guessed by now, there are no
> backups in the disk.
> Can anyone help us? Ideally we need a procedure, or,
> failing that, where can we look up a procedure to recover
> the database.
> Thank you all,
> Talia
>
|||The problem is that i can not copy the damaged file; and i
dont't know what to do with the data with the LDF file
damaged.

>--Original Message--
>From EM or Query Analyzer, are you able to do anything ?
If you can, create a new database and copy all the data
from the bad db to the new db.
>-Jimmy
>************************************************* *********
************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of
links to ASP & ASP.NET resources...
>.
>

Database marked suspect

I have a database server and a 3 workstations running on
Windows 2000. Due to a power failure (UPS included), the
whole system rebooted. However, the workstations cannot
acces the database. Upon checking the server directory,
the database is there (C:\Program Files\Microsoft SQL
Server\MSSQL\Data). Before attempting anything else we
tried to make a backup copy of the directory; all files
copy except ContinuumLog.ldf, which shows a CRC error and
cannot copy.
Using the Enterprise Manger we tried to look at the
database and found that it is marked "Suspect" by SQL.
Checking the Help menu, there seem to be a number of
procedures to change the "Suspect" mark and recover the
database. As you may have guessed by now, there are no
backups in the disk.
Can anyone help us? Ideally we need a procedure, or,
failing that, where can we look up a procedure to recover
the database.
Thank you all,
TaliaFrom EM or Query Analyzer, are you able to do anything ? If you can, create a new database and copy all the data from the bad db to the new db.
-Jimmy
**********************************************************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...|||Hi,
Please restore from good backup if you have CRC errors. If you do not have
the Backup try the below steps.
1. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
2. Restart sql server. now the database will be in emergency mode ( You
could access the database)
3. Create a new database and use DTS to transfer the data and object to the
new database.
4. Verify all the objects and data is available in new database.
Thanks
Hari
MCDBA
"Talia Sara-Lafosse" <saralafosse.t@.pucp.edu.pe> wrote in message
news:138a01c4853a$21db8b10$a301280a@.phx.gbl...
> I have a database server and a 3 workstations running on
> Windows 2000. Due to a power failure (UPS included), the
> whole system rebooted. However, the workstations cannot
> acces the database. Upon checking the server directory,
> the database is there (C:\Program Files\Microsoft SQL
> Server\MSSQL\Data). Before attempting anything else we
> tried to make a backup copy of the directory; all files
> copy except ContinuumLog.ldf, which shows a CRC error and
> cannot copy.
> Using the Enterprise Manger we tried to look at the
> database and found that it is marked "Suspect" by SQL.
> Checking the Help menu, there seem to be a number of
> procedures to change the "Suspect" mark and recover the
> database. As you may have guessed by now, there are no
> backups in the disk.
> Can anyone help us? Ideally we need a procedure, or,
> failing that, where can we look up a procedure to recover
> the database.
> Thank you all,
> Talia
>|||The problem is that i can not copy the damaged file; and i
dont't know what to do with the data with the LDF file
damaged.
>--Original Message--
>From EM or Query Analyzer, are you able to do anything ?
If you can, create a new database and copy all the data
from the bad db to the new db.
>-Jimmy
>**********************************************************
************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of
links to ASP & ASP.NET resources...
>.
>

Database marked as suspect please HELP!

Hi,

I restored my Windows XP system and discovered that I cannot access a
MSDE DB that was saved in D: drive (I restored my primary partition in
C). How can I restore it?
Thanks a lot.did you try reattaching it ?|||On 6 Jan 2005 10:25:09 -0800, "getinked" <glenn@.imsocial.com> wrote:

>did you try reattaching it ?

How can I do that? I have the MDF and LDF files but I've never done a
backup. I'm using the trial version of Teratrax Database Manager and
DbaMGR2K.
Thanks a lot for your help.|||On 6 Jan 2005 10:25:09 -0800, "getinked" <glenn@.imsocial.com> wrote:

>did you try reattaching it ?

I noticed the attach command in Teratrax but don't know how to use it.
It tells me

sp_attach_db 'database_name', 'file_name_and_path_relative_to_server'

Is the database_name the filename? What's the relative path? I've my
DB saved in D:\database while the server is in C:\InetPub I think.
Thanks.|||yea the relative path would be where you are storing the data...thus
being on your D drive.|||On 6 Jan 2005 13:23:38 -0800, "getinked" <glenn@.imsocial.com> wrote:

>yea the relative path would be where you are storing the data...thus
>being on your D drive.

Could you be more specific, what string should I enter? Thanks.|||kurotsuke (kurotsuke@.yahoo.it) writes:
> On 6 Jan 2005 13:23:38 -0800, "getinked" <glenn@.imsocial.com> wrote:
>>yea the relative path would be where you are storing the data...thus
>>being on your D drive.
> Could you be more specific, what string should I enter? Thanks.

The exact path to where the database file is located. (The Send To Cliboard
as Name from the Win95 Powertoys comes in extremely handy here.) For
instance D:\Program Files\Microsoft SQL Server\MSSQL\Data\yourdatabase.mdf.
Note that you have two files to enter: the .MDF and the .LDF.

However, since your database is marked as suspect, it still attached
to SQL Server in some sense. You may have to drop it first. Then again,
you could attach the database under a new name.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Sunday, March 11, 2012

Database Maintenance Plans

Hello everyone,
I'm new to DB Maint Plans, so let me apologize upfront. I've taken over a system from a DBA who is no longer working here, and he set up Maint Plans for all of the existing DBs. The plans show up in the Enterprise Manager under "Management->Database Maintenance Plans" like they should, but there are also entries in the "Management->SQL Server Agent->Jobs" area. When I set up a new DB Maint Plan for a new DB, it seems to be working fine, but I don't have any corresponding entries in Jobs. Did the other DBA set these up manually? Does anyone know why he might have done this? Is it needed? The jobs and job steps look like the following:

[DBName]Full:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 33C423D0-CC31-40BD-A357-7DCCAB1DC262 -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "W:\sqldata\MSSQL$P001\Backup" -DelBkUps 1WEEKS -CrBkSubDir -BkExt "BAK"'

[DBName]Maint Integrity Checks:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 33C423D0-CC31-40BD-A357-7DCCAB1DC262 -WriteHistory -CkDB '

[DBName]Maint Optimizations:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 33C423D0-CC31-40BD-A357-7DCCAB1DC262 -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '

Any help or insight would be greatly appreciated!
Thanks in advance,
CatWhen you set up a plan, it should set up a job. In Enterprise Manager folders window, right-click on Jobs and select "Refresh". I'm betting the GUI just isn't aware of the change you made...|||I would suggest that you start with BOL (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/entrmgr/em_6x7w.asp) for Database Maintenance Plans, and go from there.

-PatP|||When you set up a plan, it should set up a job. In Enterprise Manager folders window, right-click on Jobs and select "Refresh". I'm betting the GUI just isn't aware of the change you made...

Hi Blindman,
Thanks for your reply. I refreshed the GUI for Jobs, but I still don't see my maint plan tasks. Strange! I figured that it must have set up the jobs automatically, but since it didn't set up anything for mine, I didn't know what to think. Do you know if I might have done something incorrectly in setting up my plan? They appear to be working fine.

Well, any further insight would be appreciated.
Thanks for your time.
Cat|||Sorry. I'm not sure why they aren't showing up in your job list. I don't use the Maintenance Plans anyway.|||I would suggest that you start with BOL (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/entrmgr/em_6x7w.asp) for Database Maintenance Plans, and go from there.

-PatP

I have looked there (if I hadn't I would not have been able to set up a plan in the first place). If I had found the answers to my specific questions, I would not have posted them here. I realize that there are a lot of people who post questions on these boards without taking any time to research the answers themselves, but... 1) I am not one of them, and 2) Sometimes it is easier and less frustrating to ask a human for a quick answer to a question than to try and weed through the enormous amount of information contained in the BOL. If you are going to take the time to respond to a post, it would be more helpful if you would offer more specific areas or keywords to search on to reduce the amount of time spent pouring over information that does not apply.

Thank you for your time.
Cat|||Sorry. I'm not sure why they aren't showing up in your job list. I don't use the Maintenance Plans anyway.

Well, thank you! At least I know that the previous DBA did not manually set up the jobs. That's one mystery solved.

Thanks again, you're a sweetheart!
Cat|||Not necessarily. I set up jobs manually. The Maintenance Plan wizard sucks.|||I have If you are going to take the time to respond to a post, it would be more helpful if you would offer more specific areas or keywords to search on to reduce the amount of time spent pouring over information that does not apply.

Thank you for your time.
Cat
Hi Cat,
I think you have meet with some of the best and wonderful people of this forum.They are gurus in this field and they spend their valuable and busy time to answer questions from the most difficult one to the most silliest one.I bet that their suggestions are most helpful and have a deep insight.They have a huge patience and delicate word style to deal with everybody with extreme politeness.I suggest you to follow them without any hesitation,bcoz they have the best intention that you could think of. I am saying this not only they are my favourite but they are really a nice human being.|||Well, I'm not always delicate... ;)|||As Blindman noted, the Maintenance Plan Wizard is better than nothing, but to a DBA that's been around for a while it isn't a lot better than nothing. I'm one of the "crusty old pharts" that still prefers the level of control that comes from writing the maintenance scripts myself at the Transact-SQL level.

Usually when I see what you've decribed (Maintenance plans set up, then jobs set up to further customize the plan), it usually means that whoever set them up originally figured out they needed more, so they manually scheduled the job to allow them to customize the existing setup using parameters.

Sorry if I was a bit short up front. I've been running into a lot of posters/users complaining something to the effect of: "My toy is broke, you need to fix it for me, NOW" and that apparently carried forward into my response to your original question. I should have read it more closely before I responded.

-PatP|||Delicately handled, Pat.|||Sorry if I was a bit short up front. I've been running into a lot of posters/users complaining something to the effect of: "My toy is broke, you need to fix it for me, NOW" and that apparently carried forward into my response to your original question. I should have read it more closely before I responded.

-PatP

Thank you for that! And I'm sorry too if my tone was a bit harsh, I had been having a frustrating day. I have seen many of the posts on this board which you describe and I know that it can be frustrating.

I suspect that the previous DBA either did not set up the jobs himself and they were set up by SQL Server (like blindman suggested), OR he did set them up but only because he thought he had to rather than because he wanted to tweek them. I have come to find many of his DBA practises quite suspect to say the least.

Thanks again to everyone for their responses!
Cat

Database Maintenance Plan Setup Failed

I attempted to setup a Database Maintenance Plan in SQL Server 2005 for a nightly full backup of all system & user databases on my development server. When I tried to save the job, the error, "Create failed for job x" popped up. Any suggestions/ideas? No errors in event log.

This isn't really a question about setup, so I've moved your post to the Tools forum where you're more likely to get help.

Paul

|||

Can you please check whether job with that name already exists ? If that is the case please try with a new name for the mainteance plan.

Thanks,

Gops Dwarak

Thursday, March 8, 2012

Database maintenance plan

I use the database maintenance plan to do my backups, I take all the defaults
and this works well for me but one system will not make a backup. Under jobs
all jobs fail but the database maintenance plan history is empty. the plan
name, server name and database name drop down boxes show all but not the
names. I have deleted and remade the jos still not working. What else can I
do.
ssovie
If all the scheduled jobs on that server failed, check the account that runs
SQLServerAgent. Maybe the account doesn't have proper rights to the server?
Incorrect password? Did you check Event Viewer? SQL Server Logs?
"SSovie" wrote:

> I use the database maintenance plan to do my backups, I take all the defaults
> and this works well for me but one system will not make a backup. Under jobs
> all jobs fail but the database maintenance plan history is empty. the plan
> name, server name and database name drop down boxes show all but not the
> names. I have deleted and remade the jos still not working. What else can I
> do.
> --
> ssovie
|||The localsystem account starts SqlServerAgent, I tried to set ti to the
administrator but it had a logon failure when I tried to start it. I do not
see any errors in the event viewer or Sql logs that have to do with teh
maintenance plan
"Jack" wrote:
[vbcol=seagreen]
> If all the scheduled jobs on that server failed, check the account that runs
> SQLServerAgent. Maybe the account doesn't have proper rights to the server?
> Incorrect password? Did you check Event Viewer? SQL Server Logs?
> "SSovie" wrote:
|||Interesting... Why is it that you can't start the service using the
administrator account? Check User Right Assignment under Local Security
Settings in Administrative Tools and make sure the admin account can "Log On
As A Service"
"SSovie" wrote:
[vbcol=seagreen]
> The localsystem account starts SqlServerAgent, I tried to set ti to the
> administrator but it had a logon failure when I tried to start it. I do not
> see any errors in the event viewer or Sql logs that have to do with teh
> maintenance plan
> "Jack" wrote:
|||I checked local security settings and found under log on as a service local
policy setting for the administrator was not checked so I checked it. Also
checked Deny logon as a service was empty as was domain security settings. I
still can not start it as administrator.
"SSovie" wrote:

> I use the database maintenance plan to do my backups, I take all the defaults
> and this works well for me but one system will not make a backup. Under jobs
> all jobs fail but the database maintenance plan history is empty. the plan
> name, server name and database name drop down boxes show all but not the
> names. I have deleted and remade the jos still not working. What else can I
> do.
> --
> ssovie

Database maintenance plan

I use the database maintenance plan to do my backups, I take all the defaults
and this works well for me but one system will not make a backup. Under jobs
all jobs fail but the database maintenance plan history is empty. the plan
name, server name and database name drop down boxes show all but not the
names. I have deleted and remade the jos still not working. What else can I
do.
--
ssovieIf all the scheduled jobs on that server failed, check the account that runs
SQLServerAgent. Maybe the account doesn't have proper rights to the server?
Incorrect password? Did you check Event Viewer? SQL Server Logs?
"SSovie" wrote:
> I use the database maintenance plan to do my backups, I take all the defaults
> and this works well for me but one system will not make a backup. Under jobs
> all jobs fail but the database maintenance plan history is empty. the plan
> name, server name and database name drop down boxes show all but not the
> names. I have deleted and remade the jos still not working. What else can I
> do.
> --
> ssovie|||The localsystem account starts SqlServerAgent, I tried to set ti to the
administrator but it had a logon failure when I tried to start it. I do not
see any errors in the event viewer or Sql logs that have to do with teh
maintenance plan
"Jack" wrote:
> If all the scheduled jobs on that server failed, check the account that runs
> SQLServerAgent. Maybe the account doesn't have proper rights to the server?
> Incorrect password? Did you check Event Viewer? SQL Server Logs?
> "SSovie" wrote:
> > I use the database maintenance plan to do my backups, I take all the defaults
> > and this works well for me but one system will not make a backup. Under jobs
> > all jobs fail but the database maintenance plan history is empty. the plan
> > name, server name and database name drop down boxes show all but not the
> > names. I have deleted and remade the jos still not working. What else can I
> > do.
> > --
> > ssovie|||Interesting... Why is it that you can't start the service using the
administrator account? Check User Right Assignment under Local Security
Settings in Administrative Tools and make sure the admin account can "Log On
As A Service"
"SSovie" wrote:
> The localsystem account starts SqlServerAgent, I tried to set ti to the
> administrator but it had a logon failure when I tried to start it. I do not
> see any errors in the event viewer or Sql logs that have to do with teh
> maintenance plan
> "Jack" wrote:
> > If all the scheduled jobs on that server failed, check the account that runs
> > SQLServerAgent. Maybe the account doesn't have proper rights to the server?
> > Incorrect password? Did you check Event Viewer? SQL Server Logs?
> >
> > "SSovie" wrote:
> >
> > > I use the database maintenance plan to do my backups, I take all the defaults
> > > and this works well for me but one system will not make a backup. Under jobs
> > > all jobs fail but the database maintenance plan history is empty. the plan
> > > name, server name and database name drop down boxes show all but not the
> > > names. I have deleted and remade the jos still not working. What else can I
> > > do.
> > > --
> > > ssovie|||I checked local security settings and found under log on as a service local
policy setting for the administrator was not checked so I checked it. Also
checked Deny logon as a service was empty as was domain security settings. I
still can not start it as administrator.
"SSovie" wrote:
> I use the database maintenance plan to do my backups, I take all the defaults
> and this works well for me but one system will not make a backup. Under jobs
> all jobs fail but the database maintenance plan history is empty. the plan
> name, server name and database name drop down boxes show all but not the
> names. I have deleted and remade the jos still not working. What else can I
> do.
> --
> ssovie

Wednesday, March 7, 2012

Database maintenance plan

I use the database maintenance plan to do my backups, I take all the default
s
and this works well for me but one system will not make a backup. Under jobs
all jobs fail but the database maintenance plan history is empty. the plan
name, server name and database name drop down boxes show all but not the
names. I have deleted and remade the jos still not working. What else can I
do.
--
ssovieIf all the scheduled jobs on that server failed, check the account that runs
SQLServerAgent. Maybe the account doesn't have proper rights to the server?
Incorrect password? Did you check Event Viewer? SQL Server Logs?
"SSovie" wrote:

> I use the database maintenance plan to do my backups, I take all the defau
lts
> and this works well for me but one system will not make a backup. Under jo
bs
> all jobs fail but the database maintenance plan history is empty. the plan
> name, server name and database name drop down boxes show all but not the
> names. I have deleted and remade the jos still not working. What else can
I
> do.
> --
> ssovie|||The localsystem account starts SqlServerAgent, I tried to set ti to the
administrator but it had a logon failure when I tried to start it. I do not
see any errors in the event viewer or Sql logs that have to do with teh
maintenance plan
"Jack" wrote:
[vbcol=seagreen]
> If all the scheduled jobs on that server failed, check the account that ru
ns
> SQLServerAgent. Maybe the account doesn't have proper rights to the serve
r?
> Incorrect password? Did you check Event Viewer? SQL Server Logs?
> "SSovie" wrote:
>|||Interesting... Why is it that you can't start the service using the
administrator account? Check User Right Assignment under Local Security
Settings in Administrative Tools and make sure the admin account can "Log On
As A Service"
"SSovie" wrote:
[vbcol=seagreen]
> The localsystem account starts SqlServerAgent, I tried to set ti to the
> administrator but it had a logon failure when I tried to start it. I do no
t
> see any errors in the event viewer or Sql logs that have to do with teh
> maintenance plan
> "Jack" wrote:
>|||I checked local security settings and found under log on as a service local
policy setting for the administrator was not checked so I checked it. Also
checked Deny logon as a service was empty as was domain security settings. I
still can not start it as administrator.
"SSovie" wrote:

> I use the database maintenance plan to do my backups, I take all the defau
lts
> and this works well for me but one system will not make a backup. Under jo
bs
> all jobs fail but the database maintenance plan history is empty. the plan
> name, server name and database name drop down boxes show all but not the
> names. I have deleted and remade the jos still not working. What else can
I
> do.
> --
> ssovie

database maint plan failed

Hi ,
i am running sql server 2000 (evaluation copy) on Win 2003 server and i
just set up a Database Maint Plan to baackup my system database
of all the 4 jobs created only the optimization job suceed and rest such
as txn log bkup ,db bkup failed with the following errors :
sqlmaint.exe failed (SQLSTATE 42000) (Error 22029) the step failed
however, i did go to the location where the files were supposed to be saved
to and i found there are some files (but i did not check if the data is ok)
what could be the reason ? appreciate any advice
tk s& rdgsSpecify a report file for the maint plan and check that report file for specific error messages
after an execution of the job(s).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:083F3AEB-13DB-474C-B1BC-EC78501D139D@.microsoft.com...
> Hi ,
> i am running sql server 2000 (evaluation copy) on Win 2003 server and i
> just set up a Database Maint Plan to baackup my system database
> of all the 4 jobs created only the optimization job suceed and rest such
> as txn log bkup ,db bkup failed with the following errors :
> sqlmaint.exe failed (SQLSTATE 42000) (Error 22029) the step failed
> however, i did go to the location where the files were supposed to be saved
> to and i found there are some files (but i did not check if the data is ok)
> what could be the reason ? appreciate any advice
> tk s& rdgs|||MS says it's related to SP but mine is with the latest sp3a but the problem
still there...
http://support.microsoft.com/kb/295022/EN-US/
MS guys, can you guys please help
"Tibor Karaszi" wrote:
> Specify a report file for the maint plan and check that report file for specific error messages
> after an execution of the job(s).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:083F3AEB-13DB-474C-B1BC-EC78501D139D@.microsoft.com...
> > Hi ,
> >
> > i am running sql server 2000 (evaluation copy) on Win 2003 server and i
> > just set up a Database Maint Plan to baackup my system database
> >
> > of all the 4 jobs created only the optimization job suceed and rest such
> > as txn log bkup ,db bkup failed with the following errors :
> >
> > sqlmaint.exe failed (SQLSTATE 42000) (Error 22029) the step failed
> >
> > however, i did go to the location where the files were supposed to be saved
> > to and i found there are some files (but i did not check if the data is ok)
> >
> > what could be the reason ? appreciate any advice
> >
> > tk s& rdgs
>
>|||That article is for MSDE. You are running eval edition. So the article doesn't apply to you.
Do don't you do as suggested? Specify a report file so we have an error message to go on. "My car
doesn't work. What is the problem." Pretty hard to answer. Inside the report file, you find detailed
error messages, which will tell us why the job failed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:C9C496F5-5DEA-433A-9C1E-6DC76E21659D@.microsoft.com...
> MS says it's related to SP but mine is with the latest sp3a but the problem
> still there...
> http://support.microsoft.com/kb/295022/EN-US/
> MS guys, can you guys please help
> "Tibor Karaszi" wrote:
>> Specify a report file for the maint plan and check that report file for specific error messages
>> after an execution of the job(s).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
>> news:083F3AEB-13DB-474C-B1BC-EC78501D139D@.microsoft.com...
>> > Hi ,
>> >
>> > i am running sql server 2000 (evaluation copy) on Win 2003 server and i
>> > just set up a Database Maint Plan to baackup my system database
>> >
>> > of all the 4 jobs created only the optimization job suceed and rest such
>> > as txn log bkup ,db bkup failed with the following errors :
>> >
>> > sqlmaint.exe failed (SQLSTATE 42000) (Error 22029) the step failed
>> >
>> > however, i did go to the location where the files were supposed to be saved
>> > to and i found there are some files (but i did not check if the data is ok)
>> >
>> > what could be the reason ? appreciate any advice
>> >
>> > tk s& rdgs
>>

database maint plan failed

Saturday, February 25, 2012

Database Mail is not working for me on 64 bit SQL 2005

hmm..what login is the mail service running under? Are you sure that user
has permissions to read the msdb system database?
"John D." <JohnD@.discussions.microsoft.com> wrote in message
news:28596E29-6110-4A44-806F-6E82C2A86983@.microsoft.com...
>I am trying to get Database mail working on our win 2003 EE 64 bit SQL 2005
> SP 1 EE 64bit server. I have done a ton of searching around other posts &
> have checked everything that people have said would work. Nothing has
> worked
> so far with me. The mail service is enabled, broker service is enabled &
> relay permission is allowed for the service. Here is the error message I
> am
> getting: Could not retrieve items from the queue. If I look at the
> queue,
> it does have several test messages in there waiting to be sent. Any ideas
> on
> what else I could try to get this to work?
> Thanks!
> John
>I am trying to get Database mail working on our win 2003 EE 64 bit SQL 2005
SP 1 EE 64bit server. I have done a ton of searching around other posts &
have checked everything that people have said would work. Nothing has worke
d
so far with me. The mail service is enabled, broker service is enabled &
relay permission is allowed for the service. Here is the error message I am
getting: Could not retrieve items from the queue. If I look at the queue,
it does have several test messages in there waiting to be sent. Any ideas o
n
what else I could try to get this to work?
Thanks!
John|||hmm..what login is the mail service running under? Are you sure that user
has permissions to read the msdb system database?
"John D." <JohnD@.discussions.microsoft.com> wrote in message
news:28596E29-6110-4A44-806F-6E82C2A86983@.microsoft.com...
>I am trying to get Database mail working on our win 2003 EE 64 bit SQL 2005
> SP 1 EE 64bit server. I have done a ton of searching around other posts &
> have checked everything that people have said would work. Nothing has
> worked
> so far with me. The mail service is enabled, broker service is enabled &
> relay permission is allowed for the service. Here is the error message I
> am
> getting: Could not retrieve items from the queue. If I look at the
> queue,
> it does have several test messages in there waiting to be sent. Any ideas
> on
> what else I could try to get this to work?
> Thanks!
> John
>|||Pardon my ignorance but how where do you set what login the mail service is
running under? Does it use the login that SQL Agent is running under? I
enabled it in the surface area configuration tool.
"Tim Greenwood" wrote:

> hmm..what login is the mail service running under? Are you sure that user
> has permissions to read the msdb system database?
>
> "John D." <JohnD@.discussions.microsoft.com> wrote in message
> news:28596E29-6110-4A44-806F-6E82C2A86983@.microsoft.com...
>
>|||Pardon my ignorance but how where do you set what login the mail service is
running under? Does it use the login that SQL Agent is running under? I
enabled it in the surface area configuration tool.
"Tim Greenwood" wrote:

> hmm..what login is the mail service running under? Are you sure that user
> has permissions to read the msdb system database?
>
> "John D." <JohnD@.discussions.microsoft.com> wrote in message
> news:28596E29-6110-4A44-806F-6E82C2A86983@.microsoft.com...
>
>

Friday, February 24, 2012

Database mail attachments filling up the system drive!

Hi all,

Has anyone run into this issue before?

My company uses database mail extensively for alerting and the like, and most emails sent have attachments. We've put in place procedures for removing emails older than a month from the MSDB database, however we've now had a client server completely run out of the disk space due to attachments being stored in the C:\Documents and Settings\User\Local Settings\Temp folder!?!

When I check the sysmail_delete_mailitems_sp sproc, it only removes entries in the MSDB tables, and doesn't remove these temporary files in the temp directory.

Does anyone know why SQL Server stores them in the temp directory of the service account user, as well as how we can remove them (apart from manually deleting the files), as we need to have an automated process for cleaning up emails, and at the moment SQL Server 2005 doesn't handle this very well.

I've checked this on our test server as well, and we're getting the same there, and this server is running the latest SP2 + hot fixes.

Thanks,

Reece.

It happens because when you attach a file to an email, the email system makes a copy of the file to attach -so that if the file is in use, or even deleted, the email attachment will send without error.

However, then temp folder is not being cleared -normally that happens when the user logs out. Since the mail account belongs to a service account, it is not logging out.

You may wish to create a SQL Agent job (or a Windows Scheduler task) to run daily and delete files older than one day.

|||

Thanks for the info. We're in the process of testing a job to remove the files, but it would be definitely nice if SQL Server cleaned up these files itself when it does the DB Mail cleanup on the database.

Cheers,

Reece.

Thursday, March 29, 2012

Database naming convention

Sunday, March 25, 2012

Database mirroring and Scala ERP system

Wednesday, March 21, 2012

Database mirror error Msg 1447, Level 16, State 21, Line 1

Monday, March 19, 2012

Database marked suspect

Database marked suspect

Database marked suspect

Database marked as suspect please HELP!

Sunday, March 11, 2012

Database Maintenance Plans

Database Maintenance Plan Setup Failed

Thursday, March 8, 2012

Database maintenance plan

Database maintenance plan

Wednesday, March 7, 2012

Database maintenance plan

database maint plan failed

database maint plan failed

database maint plan failed

Saturday, February 25, 2012

Database Mail is not working for me on 64 bit SQL 2005

Friday, February 24, 2012

Database mail attachments filling up the system drive!

Database Overwritten

Blog Archive

About Me