Database Overwritten: company

Showing posts with label company. Show all posts

Thursday, March 29, 2012

database move help

Greetings.
My company is buying a new, more robust server for their SQL database. It's
currently running on a W2K Server and will be moved to a Win 2003 Server
with more horsepower. Can anyone tell me if this hard to do and where I
could find more "how to" info? Thx!
These should help:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues
Andrew J. Kelly SQL MVP
"Mark Adams" <MarkAdams71@.earthlink.net> wrote in message
news:OVmv59ocEHA.2504@.TK2MSFTNGP12.phx.gbl...
> Greetings.
> My company is buying a new, more robust server for their SQL database.
It's
> currently running on a W2K Server and will be moved to a Win 2003 Server
> with more horsepower. Can anyone tell me if this hard to do and where I
> could find more "how to" info? Thx!
>

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 7, 2012

database maintenance for companies that run 24/7

For a company like an airline that runs 24/7 and the database is always
being accessed and they can't shut down, how do they do maintenance on their
databases? How do they do things like defrag or rebuild indexes or shrink
files?
Do they have two databases and they switch back and forth?
Thanks,
Dan> For a company like an airline that runs 24/7 and the database is always
> being accessed and they can't shut down, how do they do maintenance on
their
> databases?
Clustering, perhaps? We use it here...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Generally they use dbcc indexdefrag instead of dbcc dbreindex, you just need to be more diligent with its application
It also becomes critical to size the database correctly so that it does not go offline for database growths/shrinks.|||They do what they have to based on their specific requirements. Almost no
one that is in a 24 x 7 will be doing any shrinking of files and it should
rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be an
option and INDEDXDEFRAG might be the way to go. It is impossableto go into
enough detail of how to maintain a 24 x 7 operation in a newsgroup. If you
have this type of requirement and are asking these questions I would highly
recommend you hire a qualified consultant.
--
Andrew J. Kelly SQL MVP
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:%23T1z%23uaPEHA.252@.TK2MSFTNGP10.phx.gbl...
> For a company like an airline that runs 24/7 and the database is always
> being accessed and they can't shut down, how do they do maintenance on
their
> databases? How do they do things like defrag or rebuild indexes or shrink
> files?
> Do they have two databases and they switch back and forth?
> Thanks,
> Dan
>|||Good points. I was thinking more along the lines of applying security
fixes, windows update, etc.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:#Qh#aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> They do what they have to based on their specific requirements. Almost
no
> one that is in a 24 x 7 will be doing any shrinking of files and it should
> rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be an
> option and INDEDXDEFRAG might be the way to go. It is impossableto go
into
> enough detail of how to maintain a 24 x 7 operation in a newsgroup. If you
> have this type of requirement and are asking these questions I would
highly
> recommend you hire a qualified consultant.|||Ken and Andrew,
So, even though INDEXDEFRAG isn't as efficient as DBREINDEX, I guess it is
sufficient. Isn't there fragmentation that also happens to the data on the
disk as well as to the indexes? How would you handle that kind of
fragmentation?
Dan
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:%23Qh%23aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> They do what they have to based on their specific requirements. Almost
no
> one that is in a 24 x 7 will be doing any shrinking of files and it should
> rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be an
> option and INDEDXDEFRAG might be the way to go. It is impossableto go
into
> enough detail of how to maintain a 24 x 7 operation in a newsgroup. If you
> have this type of requirement and are asking these questions I would
highly
> recommend you hire a qualified consultant.
> --
> Andrew J. Kelly SQL MVP
>
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:%23T1z%23uaPEHA.252@.TK2MSFTNGP10.phx.gbl...
> > For a company like an airline that runs 24/7 and the database is always
> > being accessed and they can't shut down, how do they do maintenance on
> their
> > databases? How do they do things like defrag or rebuild indexes or
shrink
> > files?
> >
> > Do they have two databases and they switch back and forth?
> >
> > Thanks,
> >
> > Dan
> >
> >
>|||Depending on what you mean by 'as efficient' (disk space, log space, cpu
usage, IOs, elapsed time), I didn't design INDEXDEFRAG to be more efficient
that DBREINDEX - it's meant to be an online alternative to rebuilding
indexes. You should read the whitepaper below for more details.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:#FdM04bPEHA.556@.tk2msftngp13.phx.gbl...
> Ken and Andrew,
> So, even though INDEXDEFRAG isn't as efficient as DBREINDEX, I guess it
is
> sufficient. Isn't there fragmentation that also happens to the data on the
> disk as well as to the indexes? How would you handle that kind of
> fragmentation?
> Dan
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:%23Qh%23aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> > They do what they have to based on their specific requirements. Almost
> no
> > one that is in a 24 x 7 will be doing any shrinking of files and it
should
> > rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be
an
> > option and INDEDXDEFRAG might be the way to go. It is impossableto go
> into
> > enough detail of how to maintain a 24 x 7 operation in a newsgroup. If
you
> > have this type of requirement and are asking these questions I would
> highly
> > recommend you hire a qualified consultant.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Dan" <ddonahue@.archermalmo.com> wrote in message
> > news:%23T1z%23uaPEHA.252@.TK2MSFTNGP10.phx.gbl...
> > > For a company like an airline that runs 24/7 and the database is
always
> > > being accessed and they can't shut down, how do they do maintenance on
> > their
> > > databases? How do they do things like defrag or rebuild indexes or
> shrink
> > > files?
> > >
> > > Do they have two databases and they switch back and forth?
> > >
> > > Thanks,
> > >
> > > Dan
> > >
> > >
> >
> >
>|||Thanks. I will.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uRVJRJcPEHA.252@.TK2MSFTNGP10.phx.gbl...
> Depending on what you mean by 'as efficient' (disk space, log space, cpu
> usage, IOs, elapsed time), I didn't design INDEXDEFRAG to be more
efficient
> that DBREINDEX - it's meant to be an online alternative to rebuilding
> indexes. You should read the whitepaper below for more details.
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:#FdM04bPEHA.556@.tk2msftngp13.phx.gbl...
> > Ken and Andrew,
> >
> > So, even though INDEXDEFRAG isn't as efficient as DBREINDEX, I guess it
> is
> > sufficient. Isn't there fragmentation that also happens to the data on
the
> > disk as well as to the indexes? How would you handle that kind of
> > fragmentation?
> >
> > Dan
> >
> > "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> > news:%23Qh%23aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> > > They do what they have to based on their specific requirements.
Almost
> > no
> > > one that is in a 24 x 7 will be doing any shrinking of files and it
> should
> > > rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be
> an
> > > option and INDEDXDEFRAG might be the way to go. It is impossableto go
> > into
> > > enough detail of how to maintain a 24 x 7 operation in a newsgroup. If
> you
> > > have this type of requirement and are asking these questions I would
> > highly
> > > recommend you hire a qualified consultant.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "Dan" <ddonahue@.archermalmo.com> wrote in message
> > > news:%23T1z%23uaPEHA.252@.TK2MSFTNGP10.phx.gbl...
> > > > For a company like an airline that runs 24/7 and the database is
> always
> > > > being accessed and they can't shut down, how do they do maintenance
on
> > > their
> > > > databases? How do they do things like defrag or rebuild indexes or
> > shrink
> > > > files?
> > > >
> > > > Do they have two databases and they switch back and forth?
> > > >
> > > > Thanks,
> > > >
> > > > Dan
> > > >
> > > >
> > >
> > >
> >
> >
>

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.

Database mail attachments are filling the hard disk

My company started using database mail recently.Most emails sent have relatively large attachments. We've set up procedures for removing emails older than a given period of time from the MSDB database, however we've now had a server completely run out of the disk space due to attachments being stored in the C:\Documents and Settings\User\Local Settings\Temp folder!?! sysmail_delete_mailitems_sp sproc only removes entries in the MSDB tables, and doesn't remove these temporary files in the temp directory.

Somebody claims that those temp files are needed in case the file being attached is deleted before it has been sent for example. I'm agree with the procedure, but... Why SQL server does not delete the temporary file after a sucessfull delivery of the message?

It is not too hard to code some job to delete these files daily, but ... Is it too hard for Microsoft Team to develop this simple functionality?

Thanks,

Philip

I think that temp directory is associated to operating system for such actions its better to code a seperate steps to drop those temp files too. Also if you have any anti-virus or anti-spyware tool installed on the server then these will be in exclusive usage hence operating system may not delete them, hence the explicit deletion is required.

I feel this is a good feature to have, you could provide such feedback on http://connect.microsoft.com site in thsi case to involve on next Service pack or in SQL 2008 release.

Friday, February 17, 2012

database locking malaise / restarting SQL Server fine points

Our company, primarily a software dev house, also manages an SQL Server
DB for one of our clients. A few days ago, we experienced repeated
timeouts in our .NET web and desktop applications, which threw this
error:
System.Data.SqlClient.SqlException: Timeout expired. The timeout
period elapsed prior to completion of the operation or the server is
not responding.
The timeouts seemed to be caused by locks placed on the database by a
stored proc which adds a new customers, when an order is placed. These
locks could be seen in Enterprise Manager / Management / Current
Activity / Locks, some of the locks' properties pointing to this stored
proc.
Now the strange part: we rebooted the server in question, which had no
apparent effect, ie still lots of locks, timeouts, etc. But a few
hours later, we pushed "Stop" and "Start" in the SQL Server Service
Manager - and everything magically started working again, no further
problems since then (24 hours).
So my key question: when you reboot your machine, does SQL server try
to "retain state" in a way that could have retained these locks? And
when you Stop/Start in "Service Manager", is that a different kind of
restart, which may have cleared the state / fixed these issues?
If the answer to these questions is yes, then we can rest a bit easy,
just say the DB got screwy, we had to restart it, but otherwise, we
have some serious investigating to do, to try to prevent this
recurring.
Thanks very much for any advice.
Harlan Wood
sfk3ml4x9w35
_____at_____
thegoldensun.comwhen you reboot your machine, does SQL server try
to "retain state" in a way that could have retained these locks? And
when you Stop/Start in "Service Manager", is that a different kind of
restart, which may have cleared the state / fixed these issues?
--both do the same thing, there is nothing that SQL will retain the
locks..in any case, SQL will release the locks..

database locking malaise / restarting SQL Server fine points

Our company, primarily a software dev house, also manages an SQL Server
DB for one of our clients. A few days ago, we experienced repeated
timeouts in our .NET web and desktop applications, which threw this
error:
System.Data.SqlClient.SqlException: Timeout expired. The timeout
period elapsed prior to completion of the operation or the server is
not responding.
The timeouts seemed to be caused by locks placed on the database by a
stored proc which adds a new customers, when an order is placed. These
locks could be seen in Enterprise Manager / Management / Current
Activity / Locks, some of the locks' properties pointing to this stored
proc.
Now the strange part: we rebooted the server in question, which had no
apparent effect, ie still lots of locks, timeouts, etc. But a few
hours later, we pushed "Stop" and "Start" in the SQL Server Service
Manager - and everything magically started working again, no further
problems since then (24 hours).
So my key question: when you reboot your machine, does SQL server try
to "retain state" in a way that could have retained these locks? And
when you Stop/Start in "Service Manager", is that a different kind of
restart, which may have cleared the state / fixed these issues?
If the answer to these questions is yes, then we can rest a bit easy,
just say the DB got screwy, we had to restart it, but otherwise, we
have some serious investigating to do, to try to prevent this
recurring.
Thanks very much for any advice.
Harlan Wood
sfk3ml4x9w35
_____at_____
thegoldensun.com
when you reboot your machine, does SQL server try
to "retain state" in a way that could have retained these locks? And
when you Stop/Start in "Service Manager", is that a different kind of
restart, which may have cleared the state / fixed these issues?
--both do the same thing, there is nothing that SQL will retain the
locks..in any case, SQL will release the locks..

Thursday, March 29, 2012

database move help

Sunday, March 25, 2012

Database mirroring and Scala ERP system

Wednesday, March 7, 2012

database maintenance for companies that run 24/7

database maintenance for companies that run 24/7

Friday, February 24, 2012

Database mail attachments filling up the system drive!

Database mail attachments are filling the hard disk

Friday, February 17, 2012

database locking malaise / restarting SQL Server fine points

database locking malaise / restarting SQL Server fine points

database locking malaise / restarting SQL Server fine points

Database Overwritten

Blog Archive

About Me