Showing posts with label available. Show all posts
Showing posts with label available. Show all posts

Friday, February 24, 2012

Cannot shrink Transaction Log

Hello. I've got a situation where a live database has stopped working because
the transaction logs have filled all available disk space. I would really
appreciate any advice on how to get it running again. My knowledge of SQL
Server 2000 is very limited so I would need so quite detailed instructions! I
am happy with entering statements into Query Analyzer.
I've tried to shrink the transaction log by running a backup on it in
Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
enough disk space to back this up.
So somehow I need to shrink these .LDF transaction log files. Also, if
anyone could advise on how to stop this situation happening again, that'd be
fantastic. If anyone can give advice on how to do this, you really would be
saving my life!
1. Consider the recovery model for the database. If not "simple" then SQL Server will not empty the
log files automatically, and the log will keep growing. When not "simple", only backup of the
transaction log will empty the log files.
2. After either setting to simple or scheduling regular transaction log backups, verify that the log
files are more or less empty. Use:
DBCC SQLPERF(LOGSPACE)
3. Now time for the shrink. Use DBCC SHRINKFILE, (not SHRINKDATABASE), as documented in Books
Online.
Also, there are some links and general info about shrink on
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Fluffy_Ninja" <FluffyNinja@.discussions.microsoft.com> wrote in message
news:C338EACF-9E37-47B3-B307-D9A15A87539C@.microsoft.com...
> Hello. I've got a situation where a live database has stopped working because
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd be
> fantastic. If anyone can give advice on how to do this, you really would be
> saving my life!
|||Hi,
What is your recovery model? Full, Simple ?
If you are using Full recovery model then you need to backup the transaction
log regularly.
If you don't need to set recovery model to Full then use Simple Recovery
Model you can find detailed information about it from Books Online.
Check out BACKUP LOG WITH TRUNCATE_ONLY (if it's acceptable for you..)
FROM BOL:
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are
synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Fluffy_Ninja" wrote:

> Hello. I've got a situation where a live database has stopped working because
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd be
> fantastic. If anyone can give advice on how to do this, you really would be
> saving my life!

Cannot shrink Transaction Log

Hello. I've got a situation where a live database has stopped working becaus
e
the transaction logs have filled all available disk space. I would really
appreciate any advice on how to get it running again. My knowledge of SQL
Server 2000 is very limited so I would need so quite detailed instructions!
I
am happy with entering statements into Query Analyzer.
I've tried to shrink the transaction log by running a backup on it in
Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
enough disk space to back this up.
So somehow I need to shrink these .LDF transaction log files. Also, if
anyone could advise on how to stop this situation happening again, that'd be
fantastic. If anyone can give advice on how to do this, you really would be
saving my life!1. Consider the recovery model for the database. If not "simple" then SQL Se
rver will not empty the
log files automatically, and the log will keep growing. When not "simple", o
nly backup of the
transaction log will empty the log files.
2. After either setting to simple or scheduling regular transaction log back
ups, verify that the log
files are more or less empty. Use:
DBCC SQLPERF(LOGSPACE)
3. Now time for the shrink. Use DBCC SHRINKFILE, (not SHRINKDATABASE), as do
cumented in Books
Online.
Also, there are some links and general info about shrink on
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Fluffy_Ninja" <FluffyNinja@.discussions.microsoft.com> wrote in message
news:C338EACF-9E37-47B3-B307-D9A15A87539C@.microsoft.com...
> Hello. I've got a situation where a live database has stopped working beca
use
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions
! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is no
t
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd
be
> fantastic. If anyone can give advice on how to do this, you really would b
e
> saving my life!|||Hi,
What is your recovery model? Full, Simple ?
If you are using Full recovery model then you need to backup the transaction
log regularly.
If you don't need to set recovery model to Full then use Simple Recovery
Model you can find detailed information about it from Books Online.
Check out BACKUP LOG WITH TRUNCATE_ONLY (if it's acceptable for you..)
FROM BOL:
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY ar
e
synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Fluffy_Ninja" wrote:

> Hello. I've got a situation where a live database has stopped working beca
use
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions
! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is no
t
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd
be
> fantastic. If anyone can give advice on how to do this, you really would b
e
> saving my life!

Cannot shrink Transaction Log

Hello. I've got a situation where a live database has stopped working because
the transaction logs have filled all available disk space. I would really
appreciate any advice on how to get it running again. My knowledge of SQL
Server 2000 is very limited so I would need so quite detailed instructions! I
am happy with entering statements into Query Analyzer.
I've tried to shrink the transaction log by running a backup on it in
Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
enough disk space to back this up.
So somehow I need to shrink these .LDF transaction log files. Also, if
anyone could advise on how to stop this situation happening again, that'd be
fantastic. If anyone can give advice on how to do this, you really would be
saving my life!1. Consider the recovery model for the database. If not "simple" then SQL Server will not empty the
log files automatically, and the log will keep growing. When not "simple", only backup of the
transaction log will empty the log files.
2. After either setting to simple or scheduling regular transaction log backups, verify that the log
files are more or less empty. Use:
DBCC SQLPERF(LOGSPACE)
3. Now time for the shrink. Use DBCC SHRINKFILE, (not SHRINKDATABASE), as documented in Books
Online.
Also, there are some links and general info about shrink on
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Fluffy_Ninja" <FluffyNinja@.discussions.microsoft.com> wrote in message
news:C338EACF-9E37-47B3-B307-D9A15A87539C@.microsoft.com...
> Hello. I've got a situation where a live database has stopped working because
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd be
> fantastic. If anyone can give advice on how to do this, you really would be
> saving my life!|||Hi,
What is your recovery model? Full, Simple ?
If you are using Full recovery model then you need to backup the transaction
log regularly.
If you don't need to set recovery model to Full then use Simple Recovery
Model you can find detailed information about it from Books Online.
Check out BACKUP LOG WITH TRUNCATE_ONLY (if it's acceptable for you..)
FROM BOL:
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are
synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Fluffy_Ninja" wrote:
> Hello. I've got a situation where a live database has stopped working because
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd be
> fantastic. If anyone can give advice on how to do this, you really would be
> saving my life!

Sunday, February 19, 2012

Cannot setup SQL Mail on SBS 2003

I have SQL Server 2000 setup on a SBS 2003 Premium server, with Exchange
2003.
I set up SQL Mail to use the only profile available, which is "ExchangeAdmin
SBS (0x codes following)".
When I try a simple xp_sendmail to an existing email address, I get the
following:
xp_sendmail: failed with mail error 0x80004005
When I search Microsoft.com, or all of the Web, I see that there is a
workaround specified by Microsoft:
http://support.microsoft.com/kb/293422/EN-US/
The problem is that I cannot "Locate the user object directly associated
with the Exchange 2000 mailbox". First of, I have Exchange 2003, not 2000.
Second, I cannot find the specified "ExchangeAdmin SBS ..." account.
I also tried installing Outlook, creating a profile with a user called
"SQLEmailAccount", so that I could locate it in AD and apply the workaround,
but when I open Enterprise manager, and try to select this email profile for
SQL Mail (as described at the top of this email), it simply does not
appear...
It seems that I can nonetheless use a workaround, as I found a few SPs which
create a CDO object (a bit like what you would do in ASP) and talk to it,
but I'd like to be able to use xp_sendmail in order to allow contractors to
use standard programming techniques.
Thanks a lot in advance for your help!Nicolas Verhaeghe wrote:
>I have SQL Server 2000 setup on a SBS 2003 Premium server, with Exchange
>2003.
>I set up SQL Mail to use the only profile available, which is
>"ExchangeAdmin SBS (0x codes following)".
Hmmm, not sure where that's from, since SBS normally has no MAPI profiles
at all by default.
Have you configured SQL Server and all associated services to run under a
user account, rather than LocalSystem (the default)?
>When I try a simple xp_sendmail to an existing email address, I get the
>following:
>xp_sendmail: failed with mail error 0x80004005
>When I search Microsoft.com, or all of the Web, I see that there is a
>workaround specified by Microsoft:
>http://support.microsoft.com/kb/293422/EN-US/
>The problem is that I cannot "Locate the user object directly associated
>with the Exchange 2000 mailbox". First of, I have Exchange 2003, not 2000.
>Second, I cannot find the specified "ExchangeAdmin SBS ..." account.
It's not an account, it's a MAPI profile belonging to a user account. MAPI
profiles belong to user accounts, and can only be accessed and manipulated
by the user account they belong to.
>I also tried installing Outlook, creating a profile with a user called
>"SQLEmailAccount", so that I could locate it in AD and apply the
>workaround, but when I open Enterprise manager, and try to select this
>email profile for SQL Mail (as described at the top of this email), it
>simply does not appear...
AARGH. Get Outlook *OFF* your SBS as fast as possible. Outlook is *not*
supported for installation on an Exchange Server (like SBS).
>It seems that I can nonetheless use a workaround, as I found a few SPs
>which create a CDO object (a bit like what you would do in ASP) and talk
>to it, but I'd like to be able to use xp_sendmail in order to allow
>contractors to use standard programming techniques.
The KB articles you need are:
304967 How to Configure SQL Server to Send Email on SBS 2000
http://support.microsoft.com/?id=304967
and
228736 SAMPLE Profman2.exe - MAPI Profile Manager v2.0
http://support.microsoft.com/?id=228736
While the first article says SBS2000, it applies equally to SBS2003. But
creating the MAPI profile is much easier to do with PROFMAN (from the
second KB) than all the registry hacking the first article suggests you do.
Steve Foster [SBS MVP]
---
MVPs do not work for Microsoft. Please reply only to the newsgroups.

Cannot setup SQL Mail on SBS 2003

I have SQL Server 2000 setup on a SBS 2003 Premium server, with Exchange
2003.
I set up SQL Mail to use the only profile available, which is "ExchangeAdmin
SBS (0x codes following)".
When I try a simple xp_sendmail to an existing email address, I get the
following:
xp_sendmail: failed with mail error 0x80004005
When I search Microsoft.com, or all of the Web, I see that there is a
workaround specified by Microsoft:
http://support.microsoft.com/kb/293422/EN-US/
The problem is that I cannot "Locate the user object directly associated
with the Exchange 2000 mailbox". First of, I have Exchange 2003, not 2000.
Second, I cannot find the specified "ExchangeAdmin SBS ..." account.
I also tried installing Outlook, creating a profile with a user called
"SQLEmailAccount", so that I could locate it in AD and apply the workaround,
but when I open Enterprise manager, and try to select this email profile for
SQL Mail (as described at the top of this email), it simply does not
appear...
It seems that I can nonetheless use a workaround, as I found a few SPs which
create a CDO object (a bit like what you would do in ASP) and talk to it,
but I'd like to be able to use xp_sendmail in order to allow contractors to
use standard programming techniques.
Thanks a lot in advance for your help!
Nicolas Verhaeghe wrote:

>I have SQL Server 2000 setup on a SBS 2003 Premium server, with Exchange
>2003.
>I set up SQL Mail to use the only profile available, which is
>"ExchangeAdmin SBS (0x codes following)".
Hmmm, not sure where that's from, since SBS normally has no MAPI profiles
at all by default.
Have you configured SQL Server and all associated services to run under a
user account, rather than LocalSystem (the default)?

>When I try a simple xp_sendmail to an existing email address, I get the
>following:
>xp_sendmail: failed with mail error 0x80004005
>When I search Microsoft.com, or all of the Web, I see that there is a
>workaround specified by Microsoft:
>http://support.microsoft.com/kb/293422/EN-US/
>The problem is that I cannot "Locate the user object directly associated
>with the Exchange 2000 mailbox". First of, I have Exchange 2003, not 2000.
>Second, I cannot find the specified "ExchangeAdmin SBS ..." account.
It's not an account, it's a MAPI profile belonging to a user account. MAPI
profiles belong to user accounts, and can only be accessed and manipulated
by the user account they belong to.

>I also tried installing Outlook, creating a profile with a user called
>"SQLEmailAccount", so that I could locate it in AD and apply the
>workaround, but when I open Enterprise manager, and try to select this
>email profile for SQL Mail (as described at the top of this email), it
>simply does not appear...
AARGH. Get Outlook *OFF* your SBS as fast as possible. Outlook is *not*
supported for installation on an Exchange Server (like SBS).

>It seems that I can nonetheless use a workaround, as I found a few SPs
>which create a CDO object (a bit like what you would do in ASP) and talk
>to it, but I'd like to be able to use xp_sendmail in order to allow
>contractors to use standard programming techniques.
The KB articles you need are:
304967 How to Configure SQL Server to Send Email on SBS 2000
http://support.microsoft.com/?id=304967
and
228736 SAMPLE Profman2.exe - MAPI Profile Manager v2.0
http://support.microsoft.com/?id=228736
While the first article says SBS2000, it applies equally to SBS2003. But
creating the MAPI profile is much easier to do with PROFMAN (from the
second KB) than all the registry hacking the first article suggests you do.
Steve Foster [SBS MVP]
MVPs do not work for Microsoft. Please reply only to the newsgroups.

Cannot setup SQL Mail on SBS 2003

I have SQL Server 2000 setup on a SBS 2003 Premium server, with Exchange
2003.
I set up SQL Mail to use the only profile available, which is "ExchangeAdmin
SBS (0x codes following)".
When I try a simple xp_sendmail to an existing email address, I get the
following:
xp_sendmail: failed with mail error 0x80004005
When I search Microsoft.com, or all of the Web, I see that there is a
workaround specified by Microsoft:
http://support.microsoft.com/kb/293422/EN-US/
The problem is that I cannot "Locate the user object directly associated
with the Exchange 2000 mailbox". First of, I have Exchange 2003, not 2000.
Second, I cannot find the specified "ExchangeAdmin SBS ..." account.
I also tried installing Outlook, creating a profile with a user called
"SQLEmailAccount", so that I could locate it in AD and apply the workaround,
but when I open Enterprise manager, and try to select this email profile for
SQL Mail (as described at the top of this email), it simply does not
appear...
It seems that I can nonetheless use a workaround, as I found a few SPs which
create a CDO object (a bit like what you would do in ASP) and talk to it,
but I'd like to be able to use xp_sendmail in order to allow contractors to
use standard programming techniques.
Thanks a lot in advance for your help!Nicolas XXXXXXXXX wrote:

>I have SQL Server 2000 setup on a SBS 2003 Premium server, with Exchange
>2003.
>I set up SQL Mail to use the only profile available, which is
>"ExchangeAdmin SBS (0x codes following)".
Hmmm, not sure where that's from, since SBS normally has no MAPI profiles
at all by default.
Have you configured SQL Server and all associated services to run under a
user account, rather than LocalSystem (the default)?

>When I try a simple xp_sendmail to an existing email address, I get the
>following:
>xp_sendmail: failed with mail error 0x80004005
>When I search Microsoft.com, or all of the Web, I see that there is a
>workaround specified by Microsoft:
>http://support.microsoft.com/kb/293422/EN-US/
>The problem is that I cannot "Locate the user object directly associated
>with the Exchange 2000 mailbox". First of, I have Exchange 2003, not 2000.
>Second, I cannot find the specified "ExchangeAdmin SBS ..." account.
It's not an account, it's a MAPI profile belonging to a user account. MAPI
profiles belong to user accounts, and can only be accessed and manipulated
by the user account they belong to.

>I also tried installing Outlook, creating a profile with a user called
>"SQLEmailAccount", so that I could locate it in AD and apply the
>workaround, but when I open Enterprise manager, and try to select this
>email profile for SQL Mail (as described at the top of this email), it
>simply does not appear...
AARGH. Get Outlook *OFF* your SBS as fast as possible. Outlook is *not*
supported for installation on an Exchange Server (like SBS).

>It seems that I can nonetheless use a workaround, as I found a few SPs
>which create a CDO object (a bit like what you would do in ASP) and talk
>to it, but I'd like to be able to use xp_sendmail in order to allow
>contractors to use standard programming techniques.
The KB articles you need are:
304967 How to Configure SQL Server to Send Email on SBS 2000
http://support.microsoft.com/?id=304967
and
228736 SAMPLE Profman2.exe - MAPI Profile Manager v2.0
http://support.microsoft.com/?id=228736
While the first article says SBS2000, it applies equally to SBS2003. But
creating the MAPI profile is much easier to do with PROFMAN (from the
second KB) than all the registry hacking the first article suggests you do.
Steve Foster [SBS MVP]
---
MVPs do not work for Microsoft. Please reply only to the newsgroups.

Tuesday, February 14, 2012

cannot save storedproc after SP3

hi
we found a bug in EM. we are unable to save/export/transfer a DTS package to
another remote box. is there any workaround or hotfix available?
AlexAlex
See if this helps
http://www.sqldts.com/default.aspx?6,105,204,0,1
Regards
John|||How are you trying ?
File | Save As | SQL Server ?
File | Save As | Structured Storage File ?
Transferring DTS Packages
http://www.sqldts.com/default.aspx?6,105,204,0,1
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"alex" <ntnewsNO@.SPAM.hrz3.hrz.tu-darmstadt.de> wrote in message
news:OgI#tPoUDHA.1280@.tk2msftngp13.phx.gbl...
> hi
> we found a bug in EM. we are unable to save/export/transfer a DTS package
to
> another remote box. is there any workaround or hotfix available?
>
> Alex
>|||hi
> How are you trying ?
> File | Save As | SQL Server ?
> File | Save As | Structured Storage File ?
this are 3 "save as" options in SP3 and 4 "save as" options in SP2 and
prior.
the third in SP3 is "Visual Basic File". in SP2 there is another Option
"Save as DTS package file" (sorry - the options is gone - i don't know the
exact name). we need this DTS package option for transfer...
Alex