sql report manager: production

Showing posts with label production. Show all posts

Sunday, March 25, 2012

Can't Shrink log File

Hi, All,
I tried to shrink a log file which was restored from production backup, but
got error. The log file is about only 10% usage according to the result of
DBCC SQLPERF ( LOGSPACE ).
When I tried this command: DBCC ShrinkFile(productionlog, 30), I got this
message:
Cannot shrink log file 2 (productionlog) because all logical log files are
in use.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
-- -- -- -- -- --
14 2 92888 76753 92888 76752
We do have Replication set up in Production, but I drop all replications
after restored it to Test environment. We are using SQL Server 2000.
Any help will be appreciated.
PerayuDid you backup the log first? Refer to:
http://support.microsoft.com/?id=272318
-Sue
On Thu, 8 Dec 2005 08:37:43 -0600, "Perayu"
<yu.he@.state.mn.us.Remove4Replay> wrote:
>Hi, All,
>I tried to shrink a log file which was restored from production backup, but
>got error. The log file is about only 10% usage according to the result of
>DBCC SQLPERF ( LOGSPACE ).
>When I tried this command: DBCC ShrinkFile(productionlog, 30), I got this
>message:
>Cannot shrink log file 2 (productionlog) because all logical log files are
>in use.
>DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
>-- -- -- -- -- --
>14 2 92888 76753 92888 76752
>We do have Replication set up in Production, but I drop all replications
>after restored it to Test environment. We are using SQL Server 2000.
>Any help will be appreciated.
>Perayu
>|||I did not run Backup before run ShrintFile. But we backup log file everyday.
I just want to truncate the unused portion because the SQLPERF shows there
are only 10% space are used.
Thanks for your help.
Perayu
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0dthp1pqj5hnpejtc0gao5hr1a5rcaju27@.4ax.com...
> Did you backup the log first? Refer to:
> http://support.microsoft.com/?id=272318
> -Sue
> On Thu, 8 Dec 2005 08:37:43 -0600, "Perayu"
> <yu.he@.state.mn.us.Remove4Replay> wrote:
>>Hi, All,
>>I tried to shrink a log file which was restored from production backup,
>>but
>>got error. The log file is about only 10% usage according to the result of
>>DBCC SQLPERF ( LOGSPACE ).
>>When I tried this command: DBCC ShrinkFile(productionlog, 30), I got this
>>message:
>>Cannot shrink log file 2 (productionlog) because all logical log files are
>>in use.
>>DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
>>-- -- -- -- -- --
>>14 2 92888 76753 92888 76752
>>We do have Replication set up in Production, but I drop all replications
>>after restored it to Test environment. We are using SQL Server 2000.
>>Any help will be appreciated.
>>Perayu
>|||Here's some comments on the topic: 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/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:%23XBv5XM$FHA.3444@.TK2MSFTNGP10.phx.gbl...
>I did not run Backup before run ShrintFile. But we backup log file everyday. I just want to
>truncate the unused portion because the SQLPERF shows there are only 10% space are used.
> Thanks for your help.
> Perayu
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:0dthp1pqj5hnpejtc0gao5hr1a5rcaju27@.4ax.com...
>> Did you backup the log first? Refer to:
>> http://support.microsoft.com/?id=272318
>> -Sue
>> On Thu, 8 Dec 2005 08:37:43 -0600, "Perayu"
>> <yu.he@.state.mn.us.Remove4Replay> wrote:
>>Hi, All,
>>I tried to shrink a log file which was restored from production backup, but
>>got error. The log file is about only 10% usage according to the result of
>>DBCC SQLPERF ( LOGSPACE ).
>>When I tried this command: DBCC ShrinkFile(productionlog, 30), I got this
>>message:
>>Cannot shrink log file 2 (productionlog) because all logical log files are
>>in use.
>>DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
>>-- -- -- -- -- --
>>14 2 92888 76753 92888 76752
>>We do have Replication set up in Production, but I drop all replications
>>after restored it to Test environment. We are using SQL Server 2000.
>>Any help will be appreciated.
>>Perayu
>>
>

Thursday, March 22, 2012

Can't Send mail using MultiServer Admin

Hi,

I have a Master server which I want to use to handle jobs for our Production Servers. the master server has Outlook installed but the Target servers do not. I need to find a way to get the job to send a mail confirming whether the job has succeeded or failed but without having a mail client installed on any of the target servers. Currently the actual backups do run but the send mail section fails with the following error.

xp_sendmail: Procedure expects parameter @.user, which was not supplied. [SQLSTATE 42000] (Error 17985). The step failed.

I can't see any parameter @.User on BOL, it may be that using sendmail is not the best way foward here

Any ideas anyone?

Hi, I had this problem when I migrated my server to new hardware. I had not yet set up the Outlook account which SQL Server uses to send the mail on the new hardware. You have to login to your box with the same account that you use for the SQL Server startup service and create an outlook profile on that account before you can send the mail using xp_sendmail. Hope this helps!|||

Hi Jason,

Thanks for the response, we don't want to install an Outlook client on the target servers which was cauing the problem, I managed to resolve the issue by using smtp instead

Regards

Lisa

|||

how do you set up the smtp on your target server? I'd like to do the same but have not any idea, I am a newbie in both SQL and network. Thanks.

|||Try using http://www.sqldev.net/xp/sp_smtp_sendmail.htm

Can't Send mail using MultiServer Admin

Hi,

xp_sendmail: Procedure expects parameter @.user, which was not supplied. [SQLSTATE 42000] (Error 17985). The step failed.

I can't see any parameter @.User on BOL, it may be that using sendmail is not the best way foward here

Any ideas anyone?

Hi Jason,

Thanks for the response, we don't want to install an Outlook client on the target servers which was cauing the problem, I managed to resolve the issue by using smtp instead

Regards

Lisa

|||

how do you set up the smtp on your target server? I'd like to do the same but have not any idea, I am a newbie in both SQL and network. Thanks.

|||Try using http://www.sqldev.net/xp/sp_smtp_sendmail.htm

Can't Send mail using MultiServer Admin

Hi,

xp_sendmail: Procedure expects parameter @.user, which was not supplied. [SQLSTATE 42000] (Error 17985). The step failed.

I can't see any parameter @.User on BOL, it may be that using sendmail is not the best way foward here

Any ideas anyone?

Hi Jason,

Thanks for the response, we don't want to install an Outlook client on the target servers which was cauing the problem, I managed to resolve the issue by using smtp instead

Regards

Lisa

|||

how do you set up the smtp on your target server? I'd like to do the same but have not any idea, I am a newbie in both SQL and network. Thanks.

|||Try using http://www.sqldev.net/xp/sp_smtp_sendmail.htm

Sunday, February 12, 2012

Cant insert data through view

I am a database administrator at my company.
Users report that insert statements against the view named
Production.viewTest do not succeed. But i firmed that the insert
statements that are being used are valid.
The only table that the view uses is named Production.ExpiredProduct.
The table has the following definition:
CREATE TABLE Production.ExpiredProduct (
ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
KEY,
Name NVARCHAR(50) NOT NULL,
ListPrice MONEY NULL)
How can i ensure that insert statement can be complete successfull by
using SQL Server Management Studio (SSMS).Any possible issue like
"Schema","Indexes", or "Statictis" caused this problem?Hi
What does not mean "does not work"? What is the error? Why would you want to
use SSMS rather than Query Builder?
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>|||> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).
You can't ensure the insert will succeed but you can test the insert by
running an insert from a query window. Assuming your view DDL is:
CREATE VIEW Production.viewTest
AS
SELECT
ExpiredProductID,
Name,
ListPrice
FROM Production.ExpiredProduct
GO
You can test with something like:
INSERT INTO Production.viewTest (Name, ListPrice)
VALUES('test', 1.0)
GO
The insert will fail if you try to specify an explicit identity value for
ExpiredProductID (unless you turn on IDENTITY_INSERT):
INSERT INTO Production.viewTest (ExpiredProductID, Name, ListPrice)
VALUES(1, 'test', 1.0)
--
Hope this helps.
Dan Guzman
SQL Server MVP
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>|||1) We need more information to assist you properly. At a minimum we would
need the view definition, the INSERT statement that fails as well as the
error reported after the failure.
2) From BOL:
Updatable Views
You can modify the data of an underlying base table through a view, as long
as the following conditions are true:
a.. Any modifications, including UPDATE, INSERT, and DELETE statements,
must reference columns from only one base table.
b.. The columns being modified in the view must directly reference the
underlying data in the table columns. The columns cannot be derived in any
other way, such as through the following:
a.. An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV,
STDEVP, VAR, and VARP.
b.. A computation. The column cannot be computed from an expression that
uses other columns. Columns that are formed by using the set operators
UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation
and are also not updatable.
c.. The columns being modified are not affected by GROUP BY, HAVING, or
DISTINCT clauses.
d.. TOP is not used anywhere in the select_statement of the view together
with the WITH CHECK OPTION clause.
INSTEAD OF Trigger is a possible alternative to attempting to directly aply
DML to a view.
--
TheSQLGuru
President
Indicium Resources, Inc.
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>

can''t import views and store procedures

I recently set up a new sql 2005 standard edition and planning to mirgrate our production sql 2000 data. but only the tables were migrated from the copy data or import / export task. I cannot find a way to recreate the store procedures and views on the new server without create one view and one store procedure at a time unless all hundreds of views and procedures were rescripted. can anyone help

Thank

Andy Wong

awong@.virginiadare.com

Right click the database > Taks > Generate Scripts, this will give you the option to put it in one output-file

Jens K. Suessmeyer

http://www.sqlserver2005.de

Sunday, March 25, 2012

Can't Shrink log File

Can't Shrink log File

Can't Shrink log File

Thursday, March 22, 2012

Can't Send mail using MultiServer Admin

Can't Send mail using MultiServer Admin

Can't Send mail using MultiServer Admin

Sunday, February 12, 2012

Cant insert data through view

can''t import views and store procedures

sql report manager

Blog Archive

About Me