Metadata Services: newbie

Showing posts with label newbie. Show all posts

Wednesday, March 28, 2012

No Fields in Page Header (and Footers)??

I'm a newbie with SSRS and I'd like to place a customer's name in the Page
Header section so that the customer's name (and ID number, and addresss)
appear at the top of every page in the right upper corner. But when I try to
put a field in the Page Header I get "Fields cannot be used in page headers
or footers."
Is MS kidding? Or am I just missing something?
So, how can I get a customer's info to appear at the top of every page?
(this is a regulated requirement).
Thanks for any help.Found this in BOL, but it seems ugly because I don't want the customer name
anywhere else in the body of the report, JUST in the Page Header. Is there
another workaround? (I still believe this is fundamental, core functionality
that should have been included)
In the expression, include references to report items on the page (for
example, you can reference a text box that contains data from a particular
field). Do not include a direct reference to fields in a data set.
You cannot refer directly to a field from a text box in a page header or
footer. (For example, you cannot use the expression =Fields!LastName.Value.)
To display field information in a page header or footer, place the field
expression in a text box in the body of the report, and then refer to that
text box in the page header or footer. The following expression displays the
contents of the first instance of a text box named TXT_LastName:
=First(ReportItems!TXT_LastName.Value)
"Don Miller" <nospam@.nospam.com> wrote in message
news:esBHBnQAIHA.1208@.TK2MSFTNGP03.phx.gbl...
> I'm a newbie with SSRS and I'd like to place a customer's name in the Page
> Header section so that the customer's name (and ID number, and addresss)
> appear at the top of every page in the right upper corner. But when I try
> to put a field in the Page Header I get "Fields cannot be used in page
> headers or footers."
> Is MS kidding? Or am I just missing something?
> So, how can I get a customer's info to appear at the top of every page?
> (this is a regulated requirement).
> Thanks for any help.
>|||Brian Welcker just had a blog post on this very subject:
http://blogs.msdn.com/bwelcker/archive/2007/06/19/marquee-moon-field-references-in-page-headers-and-footers.aspx
"Don Miller" <nospam@.nospam.com> wrote in message
news:esBHBnQAIHA.1208@.TK2MSFTNGP03.phx.gbl...
> I'm a newbie with SSRS and I'd like to place a customer's name in the Page
> Header section so that the customer's name (and ID number, and addresss)
> appear at the top of every page in the right upper corner. But when I try
> to put a field in the Page Header I get "Fields cannot be used in page
> headers or footers."
> Is MS kidding? Or am I just missing something?
> So, how can I get a customer's info to appear at the top of every page?
> (this is a regulated requirement).
> Thanks for any help.
>|||Thanks for the direction to Welcker's blog. I also tried putting the
field-associated textboxes in the report body (even though I didn't want
them there), referenced them in the header, and then hid them (visibility =false) and that seemed to work too.
Welcker's hidden parameters, referencing a field in the body, hiding
textboxes in the body, etc. are all non-obvious and non-intuitive HACKS.
I've spent at least an hour or so researching this issue, probably will go
back to the report later and forgot what all this was about or how I came up
with it. With Crystal Reports, it took all of five minutes to figure out,
was intuitive, and apparently they got around any issues MS encountered
('what happens when it grows?')
Same thing for Report vs. Page Headers. They exist as separate entities in
CR but rely on yet another hack to recreate in SSRS. And this is all in my
first hour with SSRS trying to migrate an existing CR report!
Such simple fundamental things in a report now require "programming" and
programmers. Good thing for MS the price is right :(
"Mike G." <m4053946_noSpam_@.yahoo.com> wrote in message
news:O89Kb3QAIHA.4592@.TK2MSFTNGP03.phx.gbl...
> Brian Welcker just had a blog post on this very subject:
> http://blogs.msdn.com/bwelcker/archive/2007/06/19/marquee-moon-field-references-in-page-headers-and-footers.aspx
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:esBHBnQAIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> I'm a newbie with SSRS and I'd like to place a customer's name in the
>> Page Header section so that the customer's name (and ID number, and
>> addresss) appear at the top of every page in the right upper corner. But
>> when I try to put a field in the Page Header I get "Fields cannot be used
>> in page headers or footers."
>> Is MS kidding? Or am I just missing something?
>> So, how can I get a customer's info to appear at the top of every page?
>> (this is a regulated requirement).
>> Thanks for any help.
>|||:)
Report headers not being a separate entity did take a little getting used
to, but I wouldn't call it a hack to recreate them...
Mike
"Don Miller" <nospam@.nospam.com> wrote in message
news:eRs0UJRAIHA.4584@.TK2MSFTNGP03.phx.gbl...
> Thanks for the direction to Welcker's blog. I also tried putting the
> field-associated textboxes in the report body (even though I didn't want
> them there), referenced them in the header, and then hid them (visibility
> = false) and that seemed to work too.
> Welcker's hidden parameters, referencing a field in the body, hiding
> textboxes in the body, etc. are all non-obvious and non-intuitive HACKS.
> I've spent at least an hour or so researching this issue, probably will go
> back to the report later and forgot what all this was about or how I came
> up with it. With Crystal Reports, it took all of five minutes to figure
> out, was intuitive, and apparently they got around any issues MS
> encountered ('what happens when it grows?')
> Same thing for Report vs. Page Headers. They exist as separate entities in
> CR but rely on yet another hack to recreate in SSRS. And this is all in my
> first hour with SSRS trying to migrate an existing CR report!
> Such simple fundamental things in a report now require "programming" and
> programmers. Good thing for MS the price is right :(
>
> "Mike G." <m4053946_noSpam_@.yahoo.com> wrote in message
> news:O89Kb3QAIHA.4592@.TK2MSFTNGP03.phx.gbl...
>> Brian Welcker just had a blog post on this very subject:
>> http://blogs.msdn.com/bwelcker/archive/2007/06/19/marquee-moon-field-references-in-page-headers-and-footers.aspx
>> "Don Miller" <nospam@.nospam.com> wrote in message
>> news:esBHBnQAIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> I'm a newbie with SSRS and I'd like to place a customer's name in the
>> Page Header section so that the customer's name (and ID number, and
>> addresss) appear at the top of every page in the right upper corner. But
>> when I try to put a field in the Page Header I get "Fields cannot be
>> used in page headers or footers."
>> Is MS kidding? Or am I just missing something?
>> So, how can I get a customer's info to appear at the top of every page?
>> (this is a regulated requirement).
>> Thanks for any help.
>>
>

Wednesday, March 7, 2012

Newbie's Last Question

Current code is below:
||||||||||||||||||||||||||||||||||||||||
||||||||||||||||||||||||||||||||||||
||||||||||||||||||||||||
INSERT INTO FORUM_ALLOWED_MEMBERS
(
Member_ID,
Forum_ID
)
SELECT
FORUM_MEMBERS.Member_ID, FORUM_FORUM.Forum_ID FROM FORUM_MEMBERS
INNER JOIN FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M_M
ARSTATUS + '%'
WHERE ISNULL(FORUM_MEMBERS.M_MARSTATUS,'') <> '' AND
NOT EXISTS(SELECT NULL FROM FORUM_ALLOWED_MEMBERS A
WHERE A.Member_ID = FORUM_MEMBERS.Member_ID AND
A.Forum_ID = FORUM_FORUM.Forum_ID) AND
EXISTS (SELECT FORUM_MEMBERS.M_DATE WHERE M_DATE >= 20050322000000)
||||||||||||||||||||||||||||||||||||||||
||||||||||||||||||||||||||||||||||||
||||||||||||||||||||||||
F_SUBJECT Values = ... "147. BlaBla" "148. BlaBla" "149. BlaBla" "150. BlaBl
a" .......
F_MARSTATUS Values = ... "147" "148" 149" "150" "151" "152" "153" ........
Current code add members to forum which subject is like member's m_marstatus
value.
How to add members to forums which subject contains m_marstatus value's -3 a
nd +3.
For example M_MARSTATUS = 150, member will be allowed by forums between "147
. BlaBla" and "153. BlaBla"
Can i use 7 different inner joins to do thissuch as:
INNER JOIN FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M_M
ARSTATUS + '%'
INNER JOIN FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M_M
ARSTATUS -3 + '%'
INNER JOIN FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M_M
ARSTATUS -2 + '%'
etc.I'm sorry to say that you are now experiencing the difficulties of coding
against a poorly designed database schema (structure) . The first thing to
do would be to redesign the tables so they are at least in "3rd Normal form"
.
If you don't know what that means, read up on it a bt...
Your immediate problem is caused by having violated one of the first rules
of database normalization, that is of putting more than one piece of
information into a single column. Each attribute, or aspect about a "thing"
should be in it's own column. You have Marital Status, and Subject mixed
together in one column. Then, you also have multiple individual data
elements of the SAME type in that single column. If you fix these issues yo
u
will find the coding problem you are facing will just go away.
"LacOniC" wrote:

> Current code is below:
> ||||||||||||||||||||||||||||||||||||||||
||||||||||||||||||||||||||||||||||
||||||||||||||||||||||||||
> INSERT INTO FORUM_ALLOWED_MEMBERS
> (
> Member_ID,
> Forum_ID
> )
> SELECT
> FORUM_MEMBERS.Member_ID, FORUM_FORUM.Forum_ID FROM FORUM_MEMBERS
> INNER JOIN FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M
_MARSTATUS + '%'
> WHERE ISNULL(FORUM_MEMBERS.M_MARSTATUS,'') <> '' AND
> NOT EXISTS(SELECT NULL FROM FORUM_ALLOWED_MEMBERS A
> WHERE A.Member_ID = FORUM_MEMBERS.Member_ID AND
> A.Forum_ID = FORUM_FORUM.Forum_ID) AND
> EXISTS (SELECT FORUM_MEMBERS.M_DATE WHERE M_DATE >= 20050322000000)
> ||||||||||||||||||||||||||||||||||||||||
||||||||||||||||||||||||||||||||||
||||||||||||||||||||||||||
> F_SUBJECT Values = ... "147. BlaBla" "148. BlaBla" "149. BlaBla" "150. Bla
Bla" .......
> F_MARSTATUS Values = ... "147" "148" 149" "150" "151" "152" "153" ......
.
> Current code add members to forum which subject is like member's m_marstat
us value.
> How to add members to forums which subject contains m_marstatus value's -3
and +3.
> For example M_MARSTATUS = 150, member will be allowed by forums between "1
47. BlaBla" and "153. BlaBla"
> Can i use 7 different inner joins to do thissuch as:
> INNER JOIN FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M
_MARSTATUS + '%'
> INNER JOIN FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M
_MARSTATUS -3 + '%'
> INNER JOIN FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M
_MARSTATUS -2 + '%'
> etc.
>
>|||This is Snitz Forum Database. If i edit database structure, i get more probl
ems. (For example when i update
forum version)
"You have Marital Status, and Subject mixed together in one column. "
Didn't understand this one. They are in different tables and columns.
Thank you.|||In your first post, you said...
F_SUBJECT Values = ... "147. BlaBla" "148. BlaBla" "149. BlaBla" "150.
BlaBla"
I assumed the numeric values are Marital Status, and the "BlaBla" (based on
the column name "F_SUBJECT"), have something to do with, Subjects? And thi
s
is all in the same column, no?
"LacOniC" wrote:

> This is Snitz Forum Database. If i edit database structure, i get more
problems. (For example when i update
> forum version)
> "You have Marital Status, and Subject mixed together in one column. "
> Didn't understand this one. They are in different tables and columns.
> Thank you.
>
>|||Probably my mistake. Column values are such as below:
F_SUBJECT
...
147. BlaBla
148. BlaBla
150. BlaBla
...
M_MARSTATUS
...
147
148
149
...
M_MARSTATUS column holds an user information, F_SUBJECT column holds forum s
ubject. They are relative but not
same. So i use LIKE.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:4A0BFEA5-5996-4442-A411-BEFB84A611FA@.microsoft.com...
> In your first post, you said...
> F_SUBJECT Values = ... "147. BlaBla" "148. BlaBla" "149. BlaBla" "150.
> BlaBla"
> I assumed the numeric values are Marital Status, and the "BlaBla" (based o
n
> the column name "F_SUBJECT"), have something to do with, Subjects? And t
his
> is all in the same column, no?
> "LacOniC" wrote:
>
update|||If F_Subject contains...
147. BlaBla
148. BlaBla
150. BlaBla
What is the value "147" in the first row above represent? Is it a Subject?
If it is some Subject, you can't link it (using LIKE) to the values in the
M_MARSTATUS
column. If it IS Marital Status, then it should be in it's own column as a
foreign Key, NOT concatenated wit hSubject data in the F_SUBJET Column.
"LacOniC" wrote:

> Probably my mistake. Column values are such as below:
> F_SUBJECT
> ...
> 147. BlaBla
> 148. BlaBla
> 150. BlaBla
> ...
> M_MARSTATUS
> ...
> 147
> 148
> 149
> ...
> M_MARSTATUS column holds an user information, F_SUBJECT column holds forum
subject. They are relative but not
> same. So i use LIKE.
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:4A0BFEA5-5996-4442-A411-BEFB84A611FA@.microsoft.com...
> update
>
>|||Oh no. I don't hold marital status values in that column or anywhere else. I
use that column to hold user's
sh

information.
If user belongs to "150. period of school", he should see that period and ot
hers that is closed his period.
(147,148,149,151,152,153).
With query that i wrote in first message, user just see 150.|||If you don't "hold marital status values in that column or anywhere else"...
Then how can you be joining this column (FORUM_FORUM.F_SUBJECT), with the
FORUM_MEMBERS.M_MARSTATUS column? If the numbers in this column are NOT
marital statuses, then what is this join doing '
From your original post...
FROM FORUM_MEMBERS
INNER JOIN FORUM_FORUM ON
FORUM_FORUM.F_SUBJECT LIKE '%' +
FORUM_MEMBERS.M_MARSTATUS + '%'
"LacOniC" wrote:

> Oh no. I don't hold marital status values in that column or anywhere e
lse. I use that column to hold user's
> sh

information.
> If user belongs to "150. period of school", he should see that period
and others that is closed his period.
> (147,148,149,151,152,153).
> With query that i wrote in first message, user just see 150.
>
>|||I wrote what column includes which value. I didn't say MaritalStatus cloumn
is empty. It just doesn't hold
what it says. It holds numbers as 150. In fact i can't say what join is doin
g there, lol. But it works very
well. =)|||Laconic,
Well if it works, then the value in there must mean something... Your
first task, before anyone can help you, (including yourself) is t ofind out
what is in the database, (Tables and Columns) and understand what the query
is supposed to be doing, and what it is doing...
That includes knowing, and understanding, the data that is in the database.
Until you do, you really shouldn't even be messing with it. If you're the
DBA, your first task should be to hire someone who knows enough about
database technology to do that. I say that reluctantlly, but, based on your
comments in this thread, it seems that you have a great deal to learn about
SQL and RDBMS technology in general, before your skills will be at the level
necessary to do what needs doing on this system.
"LacOniC" wrote:

> I wrote what column includes which value. I didn't say MaritalStatus c
loumn is empty. It just doesn't hold
> what it says. It holds numbers as 150. In fact i can't say what join is do
ing there, lol. But it works very
> well. =)
>
>

Newbie?: Can This Be Done?- If so please direct

Hello NG
I have been working with Access for a Long Time - Am just now starting
to get my feet wet with SQL Server 2000 - We have a production database that
we have moved the tables to SQLServer and I have been creating stored
procedures to replicate some of the processes done on the access side - the
server does process extremely faster than access - Here is my problem I am
currently pulling info from SQL SERVER to Access once a w

for w

ly
stats I want to create a stored procedure to process the w

ly stats and
push them to an access table upon request - we have multiple warehouses and
i would like to have the process coded once in sql and dump to the access
table based off a warehouse variable passed to the stored procedure - Can
SQLServer from a stored procedure export a recordset into a varible defined
Access Table?
TIAFAH
RandyIt sounds like DTS is what you're looking for. I don't know that I
would recommend this approach to an experienced SQL Server developer
because of its limitations, but in what you're asking for, it seems
ideal. Open Enterprise Manager, right-click your database, select
"Export Data" (it might be under a submenu), follow the wizard. I'm
pretty sure you can specify a stored procedure for the source. If I
remember correctly, you may also schedule the package to run at
intervals.
-Alan
Randy wrote:
> Hello NG
> I have been working with Access for a Long Time - Am just now starting
> to get my feet wet with SQL Server 2000 - We have a production database th
at
> we have moved the tables to SQLServer and I have been creating stored
> procedures to replicate some of the processes done on the access side - th
e
> server does process extremely faster than access - Here is my problem I am
> currently pulling info from SQL SERVER to Access once a w

for w

ly
> stats I want to create a stored procedure to process the w

ly stats and
> push them to an access table upon request - we have multiple warehouses an
d
> i would like to have the process coded once in sql and dump to the access
> table based off a warehouse variable passed to the stored procedure - Can
> SQLServer from a stored procedure export a recordset into a varible define
d
> Access Table?
> TIAFAH
> Randy

Saturday, February 25, 2012

Newbie: XML as datasource

Hi everyone,
I have a Web Form with data fields. What I want to do is to save these
fields into an XML file (dynamically), then hook this XML (as a data source)
to a SQL Report so that I don't have to save these data into a SQL Server
database. Is it possible? If it is then can someone show me how.
Any suggestion is greatly appreciated.
Many thanks in advance.There is currently no support for accessing an XML data source. You either
have to write your own custom XML data source extension or look on the market
for an XML OLEDB provider. Or search MSDN for sample code that implements an
XML data source extension. My current understanding is that it will be
included with the SQL Server 2005 release of Reporting Services.
HTH
Charles Kangai, MCT, MCDBA
"Calvin KD" wrote:
> Hi everyone,
> I have a Web Form with data fields. What I want to do is to save these
> fields into an XML file (dynamically), then hook this XML (as a data source)
> to a SQL Report so that I don't have to save these data into a SQL Server
> database. Is it possible? If it is then can someone show me how.
> Any suggestion is greatly appreciated.
> Many thanks in advance.|||Hi
I used the "Microsoft OLE DB Simple Provider"
1.) Create new Shared Data Source
General: "Provider=MSDAOSP.1;Data Source=MSXML2.DSOControl.2.6"
Credentials: "Use Windows Authentication (Integrated Security)"
2.) Create new Report using this Shared Data Source
Query: path to the xml-file "c:\temp\test.xml"
Kind Regards
Daniel
"Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in message
news:4A0288C1-9C16-4CDF-981A-88B89475616C@.microsoft.com...
> There is currently no support for accessing an XML data source. You either
> have to write your own custom XML data source extension or look on the
> market
> for an XML OLEDB provider. Or search MSDN for sample code that implements
> an
> XML data source extension. My current understanding is that it will be
> included with the SQL Server 2005 release of Reporting Services.
> HTH
> Charles Kangai, MCT, MCDBA
> "Calvin KD" wrote:
>> Hi everyone,
>> I have a Web Form with data fields. What I want to do is to save these
>> fields into an XML file (dynamically), then hook this XML (as a data
>> source)
>> to a SQL Report so that I don't have to save these data into a SQL Server
>> database. Is it possible? If it is then can someone show me how.
>> Any suggestion is greatly appreciated.
>> Many thanks in advance.

Newbie: working with expressions on tables

I was reading the help, but I don't know if this is possible.
Dataset
first | last | Id
a | b | 1
c | d | 2
e | f | 3
For 1 cell I want to put:
Fields!ID.Value where Fields!First.Value="c" and Fields!Last.Value="d"
I just want to put specific fields in different spots. I didn't see this in
the help and looked around a bit. Is there a section in the help that
covers expressions like this?I think you want this:
=iif(Trim(Fields!First.Value) = "c" AND Trim(Fields!Last.Value) = "d",
Fields!ID.Value, Nothing)
Details on the IIF function are available on MSDN:
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctiif.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cindy Lee" <dangreece@.hotmail.com> wrote in message
news:eBnL6uYVEHA.1928@.TK2MSFTNGP11.phx.gbl...
> I was reading the help, but I don't know if this is possible.
> Dataset
> first | last | Id
> a | b | 1
> c | d | 2
> e | f | 3
>
> For 1 cell I want to put:
> Fields!ID.Value where Fields!First.Value="c" and Fields!Last.Value="d"
> I just want to put specific fields in different spots. I didn't see this
in
> the help and looked around a bit. Is there a section in the help that
> covers expressions like this?
>|||That works, but it only takes the 1st value. It doesn't loop through all
the rows if I do it cell by cell.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:u4AUaamVEHA.2716@.tk2msftngp13.phx.gbl...
> I think you want this:
> =iif(Trim(Fields!First.Value) = "c" AND Trim(Fields!Last.Value) = "d",
> Fields!ID.Value, Nothing)
> Details on the IIF function are available on MSDN:
> http://msdn.microsoft.com/library/en-us/vblr7/html/vafctiif.asp
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Cindy Lee" <dangreece@.hotmail.com> wrote in message
> news:eBnL6uYVEHA.1928@.TK2MSFTNGP11.phx.gbl...
> > I was reading the help, but I don't know if this is possible.
> >
> > Dataset
> >
> > first | last | Id
> > a | b | 1
> > c | d | 2
> > e | f | 3
> >
> >
> > For 1 cell I want to put:
> > Fields!ID.Value where Fields!First.Value="c" and Fields!Last.Value="d"
> >
> > I just want to put specific fields in different spots. I didn't see
this
> in
> > the help and looked around a bit. Is there a section in the help that
> > covers expressions like this?
> >
> >
>

Newbie: which replication strategy should I use?

Hi all

My company is going to open new working centers on different locations of my region. One of the problems we are suffering is that at some locations, the network communications infrastructure is very very very poor. So, in that locations we work with low bandwidths, and connections usually break down.

Because of this, we are thinking in distributing our database. I have been doing some tests on replication, reading the docs, etc... But I am still not sure which replication strategy should we use, and how to organize our database tables to allow replication work properly.

Our offices are going to share some data (a product catalogue, for example) which could be updated an queried from any of the offices. But there is also data which is not going to be shared, as product stocks for each location.

We were thinking on using transactional peer-to-peer replication. But now we are having serious doubts about this since in a previous post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1525643&SiteID=1) we were told we can't store non-shared data (stock) in the same table as the shared data (products) due to how database behaves when a publication is restarted. We know it would not be usual to republish, but we were thinking on crash recovery.

It would be fantastic if somebody could help us to decide what should we do to organize our database to allow the use of a proper replication scheme.

Thanks in advance,

Rubn D. M.

Considering your low bandwidths, have you looked at merge replication? It works best in a disconnected environment and over unreliable links as it has retry capabilities built in. you can also run agents at scheduled intervals. This would work provide you don't have low-latency requirement.

As far as your scenario goes where you want to replicate a table but not track changes in certain columns at both ends, this really won't work. In its simplest form you can do vertical partitioning at the publisher side, but that means any columns you choose to not replicate will not exist at the subscriber.

|||Hi Greg,
thank you very much for your help.

I thought transactional replication has also retry capabilities... So, what happens in a "transactional replication environment with updatable queued subscriptions" if the connection between publisher and subscriber is lost? Would it be needed to "restart" the subscription, or it will continue working correctly when connection is stablished again?

Thanks again ;-)

Regards

|||It does, but it's not as robust as merge replication, which is designed for the scenario you described.

Newbie: what kind of "cube" is this?

1. In sql 2005 Analysis services how can I tell what kind of "cube" I'm viewing -- ROLAP, HOLAP, or MOLAP?

2. During cube creation where do I specify the type of "cube" I want to create

TIA,

Barkingdog

The cube wizard will not ask you for the storage settings that you want to use. After you have run the wizard and created the cube, go to the "Partitions" tab of the cube editor. You will then see information for your partitions. You can click on a partition and then click on the "Storage Settings..." link to specify MOLAP, HOLAP or ROLAP.

HTH,

- Steve

|||Cube and dimensions will initially be MOLAP when creating cubes and databases with Cube or Dimension wizards.

Newbie: what kind of "cube" is this?

1. In sql 2005 Analysis services how can I tell what kind of "cube" I'm viewing -- ROLAP, HOLAP, or MOLAP?

2. During cube creation where do I specify the type of "cube" I want to create

TIA,

Barkingdog

HTH,

- Steve

|||Cube and dimensions will initially be MOLAP when creating cubes and databases with Cube or Dimension wizards.

Newbie: Viewing KPIs thru MOSS

I'm a newbie with SSAS so please bare with me.

I've create a data cube and subsequent KPIs. I now want to be able to publish these KPIs from within MOSS. All of the literature up until now seemed to indicate that I could easily attached to the KPIs from within MOSS, but now I finding that I need to publish them to Excel then to MOSS. Is this correct?

If so, I do not have Excel 2007, so is there a work around to this? Also, similiar to how Reporting Services when deployed provides the ability to render the reports via the web browser...does this functionality exist within SSAS and KPIs?

Thanks for your time and assistance.

SArnwine wrote:

..

but now I finding that I need to publish them to Excel then to MOSS. Is this correct?
...

If you follow the blog entry below, you should be able to create a KPI list, add items to this list for SSAS KPI's, and display the KPI's in the list using the MOSS built-in Key Performance Indicators Web Part:

KPI's in Microsoft Office SharePoint Server (MOSS) 2007

|||thanks.

When I try to connect to the KPI's from SSAS thru MOSS (as depicted in the blog you sent the link to), I'm asked for a ODC connection.

How do I create/determine what my ODC connection is to my SSAS Cube/KPIs? I've seen ways to create it via Excel, but once again, can I bypass Excel and just create the ODC myself?

Thanks.
|||The Office Data Connection Wizard (which I think is shared among Office apps like Excel and InfoPath) allows you to create ODC files - there may be tools to create ODC files outside of Office, but I'm not aware of them.

newbie: viewing data in SQL Server 7.0

I am embarassed even to ask this question here, but I truly don't know.
I can't find a way to graphically view the databases on my server. It is
running Back Office SBS 4.5, which includes SQL Server 7.0. I didn't set up
the server, someone else did, but I do have the installation disks.
I don't know much about SQL Server, but I've had some experience with mySQL
(which I've used phpMyAdmin to view/edit).
I've looked around for applications in the start menu, but nothing is shown
relating to SQL Server. Can anyone please help?
Thanks for the help.
SarahIf the SQL Server administrative tools have been installed, look for an Ente
rprise Manager icon in the Programs\Microsoft SQL Server folder.
If they haven't, rerun the installation and add the admin tools. You will a
lso have to reapply any service packs and hotfixes.
Sincerely,
Anthony Thomas
--
"Sarah S" <psusarah@.ncx.com> wrote in message news:%23bIsDGqzEHA.2192@.TK2M
SFTNGP14.phx.gbl...
I am embarassed even to ask this question here, but I truly don't know.
I can't find a way to graphically view the databases on my server. It is
running Back Office SBS 4.5, which includes SQL Server 7.0. I didn't set u
p
the server, someone else did, but I do have the installation disks.
I don't know much about SQL Server, but I've had some experience with mySQ
L
(which I've used phpMyAdmin to view/edit).
I've looked around for applications in the start menu, but nothing is show
n
relating to SQL Server. Can anyone please help?
Thanks for the help.
Sarah|||Thanks! Does this mean to re-run the OS installation? I'm assuming it does.
No SQL tools were installed originally.
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message news:%23O
5w9q0zEHA.3120@.TK2MSFTNGP12.phx.gbl...
If the SQL Server administrative tools have been installed, look for an Ente
rprise Manager icon in the Programs\Microsoft SQL Server folder.
If they haven't, rerun the installation and add the admin tools. You will a
lso have to reapply any service packs and hotfixes.
Sincerely,
Anthony Thomas
--
"Sarah S" <psusarah@.ncx.com> wrote in message news:%23bIsDGqzEHA.2192@.TK2MSF
TNGP14.phx.gbl...
I am embarassed even to ask this question here, but I truly don't know.
I can't find a way to graphically view the databases on my server. It is
running Back Office SBS 4.5, which includes SQL Server 7.0. I didn't set up
the server, someone else did, but I do have the installation disks.
I don't know much about SQL Server, but I've had some experience with mysql
(which I've used phpMyAdmin to view/edit).
I've looked around for applications in the start menu, but nothing is shown
relating to SQL Server. Can anyone please help?
Thanks for the help.
Sarah

newbie: viewing data in SQL Server 7.0

I am embarassed even to ask this question here, but I truly don't know.
I can't find a way to graphically view the databases on my server. It is
running Back Office SBS 4.5, which includes SQL Server 7.0. I didn't set up
the server, someone else did, but I do have the installation disks.
I don't know much about SQL Server, but I've had some experience with mySQL
(which I've used phpMyAdmin to view/edit).
I've looked around for applications in the start menu, but nothing is shown
relating to SQL Server. Can anyone please help?
Thanks for the help.
SarahThis is a multi-part message in MIME format.
--=_NextPart_000_0014_01C4D0C3.B586D890
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks! Does this mean to re-run the OS installation? I'm assuming it =does. No SQL tools were installed originally.
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message =news:%23O5w9q0zEHA.3120@.TK2MSFTNGP12.phx.gbl...
If the SQL Server administrative tools have been installed, look for =an Enterprise Manager icon in the Programs\Microsoft SQL Server folder.
If they haven't, rerun the installation and add the admin tools. You =will also have to reapply any service packs and hotfixes.
Sincerely,
Anthony Thomas
-- "Sarah S" <psusarah@.ncx.com> wrote in message =news:%23bIsDGqzEHA.2192@.TK2MSFTNGP14.phx.gbl...
I am embarassed even to ask this question here, but I truly don't =know.
I can't find a way to graphically view the databases on my server. =It is running Back Office SBS 4.5, which includes SQL Server 7.0. I didn't =set up the server, someone else did, but I do have the installation disks.
I don't know much about SQL Server, but I've had some experience =with mySQL (which I've used phpMyAdmin to view/edit).
I've looked around for applications in the start menu, but nothing =is shown relating to SQL Server. Can anyone please help?
Thanks for the help.
Sarah
--=_NextPart_000_0014_01C4D0C3.B586D890
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Thanks! Does this mean to re-run the OS =installation? I'm assuming it does. No SQL tools were installed originally.
"AnthonyThomas" wrote in message news:%23O5w9q0zEHA.=3120@.TK2MSFTNGP12.phx.gbl...
If the =SQL Server administrative tools have been installed, look for an Enterprise =Manager icon in the Programs\Microsoft SQL Server folder.

If they =haven't, rerun the installation and add the admin tools. You will also =have to reapply any service packs and hotfixes.

Sincerely,

Anthony = Thomas

--
"Sarah S" wrote in =message news:%23bIsDGqzEHA.=2192@.TK2MSFTNGP14.phx.gbl...I am embarassed even to ask this question here, but I truly don't know.I can't find a way to graphically view the databases on =my server. It is running Back Office SBS 4.5, which includes SQL =Server 7.0. I didn't set up the server, someone else did, but I do have =the installation disks.I don't know much about SQL Server, but =I've had some experience with mySQL (which I've used phpMyAdmin to view/edit).I've looked around for applications in the start =menu, but nothing is shown relating to SQL Server. Can anyone please help?Thanks for the help.Sarah

--=_NextPart_000_0014_01C4D0C3.B586D890--

newbie: viewing data in SQL Server 7.0

I am embarassed even to ask this question here, but I truly don't know.
I can't find a way to graphically view the databases on my server. It is
running Back Office SBS 4.5, which includes SQL Server 7.0. I didn't set up
the server, someone else did, but I do have the installation disks.
I don't know much about SQL Server, but I've had some experience with mySQL
(which I've used phpMyAdmin to view/edit).
I've looked around for applications in the start menu, but nothing is shown
relating to SQL Server. Can anyone please help?
Thanks for the help.
Sarah
If the SQL Server administrative tools have been installed, look for an Enterprise Manager icon in the Programs\Microsoft SQL Server folder.
If they haven't, rerun the installation and add the admin tools. You will also have to reapply any service packs and hotfixes.
Sincerely,
Anthony Thomas

"Sarah S" <psusarah@.ncx.com> wrote in message news:%23bIsDGqzEHA.2192@.TK2MSFTNGP14.phx.gbl...
I am embarassed even to ask this question here, but I truly don't know.
I can't find a way to graphically view the databases on my server. It is
running Back Office SBS 4.5, which includes SQL Server 7.0. I didn't set up
the server, someone else did, but I do have the installation disks.
I don't know much about SQL Server, but I've had some experience with mySQL
(which I've used phpMyAdmin to view/edit).
I've looked around for applications in the start menu, but nothing is shown
relating to SQL Server. Can anyone please help?
Thanks for the help.
Sarah
|||Thanks! Does this mean to re-run the OS installation? I'm assuming it does. No SQL tools were installed originally.
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message news:%23O5w9q0zEHA.3120@.TK2MSFTNGP12.phx.gbl...
If the SQL Server administrative tools have been installed, look for an Enterprise Manager icon in the Programs\Microsoft SQL Server folder.
If they haven't, rerun the installation and add the admin tools. You will also have to reapply any service packs and hotfixes.
Sincerely,
Anthony Thomas

"Sarah S" <psusarah@.ncx.com> wrote in message news:%23bIsDGqzEHA.2192@.TK2MSFTNGP14.phx.gbl...
I am embarassed even to ask this question here, but I truly don't know.
I can't find a way to graphically view the databases on my server. It is
running Back Office SBS 4.5, which includes SQL Server 7.0. I didn't set up
the server, someone else did, but I do have the installation disks.
I don't know much about SQL Server, but I've had some experience with mySQL
(which I've used phpMyAdmin to view/edit).
I've looked around for applications in the start menu, but nothing is shown
relating to SQL Server. Can anyone please help?
Thanks for the help.
Sarah

newbie: value expression... uses an aggregate expression w/o a sco

simply added a report parameter for a working report (which went fine and w/o
issue..)
next thing, went to build the report in VS.NET and got 100 Build Errors
telling me that every TextBox on my summary page has the error:
'The value expression for the textbox '...' uses an aggregate expression
with a scope...'
Newbie (I know), but what on earth did I do that would have screwed up
everything like this?
( Scope for what? The report and parameters already have everything they
need as far as data goes.)
Rob(this is a frickin nightmare...)
So it looks like every time I try to add a new report parameter, after
deleting the original (simply the report parameter became a different report
item), about half of the textboxes which are sums and counts threw this error
(which is nutty as heck, since about 2/3, which have the same thing are OK).
Is this some kind of bug? (Deleted all Report Parameters and I can compile
w/o errors.)
What's going on with this and what does it take to 'set the scope'.
Rob
"RobKaratzas" wrote:
> simply added a report parameter for a working report (which went fine and w/o
> issue..)
> next thing, went to build the report in VS.NET and got 100 Build Errors
> telling me that every TextBox on my summary page has the error:
> 'The value expression for the textbox '...' uses an aggregate expression
> with a scope...'
> Newbie (I know), but what on earth did I do that would have screwed up
> everything like this?
> ( Scope for what? The report and parameters already have everything they
> need as far as data goes.)
> Rob
>

Newbie: Use XMLA to connect to SQL Server

Hi,

I am developing a Java application which is able to browse "XMLA-Type" databases. For testing, we have a SQL Server 2000 and ~2005 installed on a server an I can access them through a web service.

When I query the datasources via XMLA, I get a response like:

DataSourceInfo == Local Analysis Server
ProviderName == Microsoft XML for Analysis
ProviderType == TDP
ProviderType == MDP
ProviderType == DMP
AuthenticationMode == Unauthenticated

(in XML of course, but for simplicity, I leave the XML out...).
Now, this is all well and good, but I do have two -- probably very foolish -- questions:

1. How can I set the SQL Server 2000 to actually use an "authenticated" authentication mode, and
2. (more importantly) How do I actually provide the username/password combination in my XMLA query? Do I set properties? Or restrictions?

For reference: My current XMLA query to discover the datasources is:

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope
xmlns Tongue Tied OAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<Discover xmlns="urnchemas-microsoft-com:xml-analysis"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<RequestType>DISCOVER_DATASOURCES</RequestType>
<Restrictions>
<RestrictionList>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
<Format>Tabular</Format>
<Content>SchemaData</Content>
</PropertyList>
</Properties>
</Discover>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

So, suppose I'll get an "AuthenticationMode==Authenticated" in the response, how would I rephrase my discoverAnything queries? (Which currently look like this -- for catalogs):

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope
xmlnsOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<Discover xmlns="urnchemas-microsoft-com:xml-analysis"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<RequestType>DBSCHEMA_CATALOGS</RequestType>
<Restrictions>
<RestrictionList>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
<DataSourceInfo>Local Analysis Server</DataSourceInfo>
<Format>Tabular</Format>
<Content>SchemaData</Content>
</PropertyList>
</Properties>
</Discover>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Thanks for any help!
Regards,
Philipp
Is my question just hilarilously stupid because I did not understand a concept? In that case, if you could provide me with pointers to where I can find information on authentication with XMLA (I tried google of course, but I did not find much), I'd be thankful.

Regards,
Philipp
|||

Hi Phillip, your question is not stupid, your problem is that there are not many people that actually connect using raw XMLA. There are a lot of moving parts that you need to get right.

Chris Harrington has the best collection of blog posts I have seen on the subject here

http://www.activeinterface.com/thinolap.html

Particularly this one: http://www.activeinterface.com/b2005_11_21.html

Most of his samples are in javascript, but you should be able to figure it out. I know that I was able to setup XMLA and get Chris's samples working on my old laptop, but I do not have it configured on my current one.

How were you planning to authenticate? Were you going to use windows authentication, pass a username/password or are you happy with anonymous authentication?

|||Hi Darren,

thanks for your response -- now I know that at least I did not completely make a fool out of myself. I will check out the blog immediately and try to get it to work.

As for the authentication method: The thing is that at our company, we have set up a web-service with a MS Analysis Server running on it. _I_ can connect to it anonymously (or so it seems: I can just send a "discover catalogs" xmla call and get all that I wanted).
I am of course not at all happy with that. That's why I posted my first question in the post: How do I tell MS Server to use user/password combination (simple authentication)?

So, I'd be most happy if I could support all authentication modes, but for starters, passing a username/password would be just fine.
Again: Thanks for the links. They should get me started,
|||

The key to this in Chris's samples appears to be the following line (which I have simplified a bit) which opens the connection.

oHttp.open("POST", sUrl, false, sDomain + "\\" + sUser, sPass)

So the username/password is specified when opening the connection, before sending any commands. If you have not sent through a username/password, it will be falling back to either anonymous or integrated authentication.|||Thanks again, Darren. Indeed, I found the same line and implemented the same solution in java, that is, I have the code

Authenticator.setDefault(new Authenticator(){
protected PasswordAuthentication getPasswordAuthentication() {
return new PasswordAuthentication(username,
password.toCharArray());
}
});
urlConnection = (HttpURLConnection) url.openConnection();
urlConnection.setRequestMethod("POST");

and this does the trick. Or rather: This _seems to_ do the trick: I am still struggling to get my Microsoft Server to use authentication... It seems as if anyone who has access rights to the computer on which the service runs, can also access the database. But, I am pretty sure, I can get that to work (there's a manual to go through).

Anyway, thanks for your help!
Philipp

Newbie: Use XMLA to connect to SQL Server

Hi,

I am developing a Java application which is able to browse "XMLA-Type" databases. For testing, we have a SQL Server 2000 and ~2005 installed on a server an I can access them through a web service.

When I query the datasources via XMLA, I get a response like:

DataSourceInfo == Local Analysis Server
ProviderName == Microsoft XML for Analysis
ProviderType == TDP
ProviderType == MDP
ProviderType == DMP
AuthenticationMode == Unauthenticated

(in XML of course, but for simplicity, I leave the XML out...).
Now, this is all well and good, but I do have two -- probably very foolish -- questions:

For reference: My current XMLA query to discover the datasources is:

So, suppose I'll get an "AuthenticationMode==Authenticated" in the response, how would I rephrase my discoverAnything queries? (Which currently look like this -- for catalogs):

Regards,
Philipp
|||

Hi Phillip, your question is not stupid, your problem is that there are not many people that actually connect using raw XMLA. There are a lot of moving parts that you need to get right.

Chris Harrington has the best collection of blog posts I have seen on the subject here

http://www.activeinterface.com/thinolap.html

Particularly this one: http://www.activeinterface.com/b2005_11_21.html

How were you planning to authenticate? Were you going to use windows authentication, pass a username/password or are you happy with anonymous authentication?

|||Hi Darren,

thanks for your response -- now I know that at least I did not completely make a fool out of myself. I will check out the blog immediately and try to get it to work.

So, I'd be most happy if I could support all authentication modes, but for starters, passing a username/password would be just fine.
Again: Thanks for the links. They should get me started,
|||

The key to this in Chris's samples appears to be the following line (which I have simplified a bit) which opens the connection.

oHttp.open("POST", sUrl, false, sDomain + "\\" + sUser, sPass)

Anyway, thanks for your help!
Philipp

Newbie: Use XMLA to connect to SQL Server

Hi,

I am developing a Java application which is able to browse "XMLA-Type" databases. For testing, we have a SQL Server 2000 and ~2005 installed on a server an I can access them through a web service.

When I query the datasources via XMLA, I get a response like:

DataSourceInfo == Local Analysis Server
ProviderName == Microsoft XML for Analysis
ProviderType == TDP
ProviderType == MDP
ProviderType == DMP
AuthenticationMode == Unauthenticated

(in XML of course, but for simplicity, I leave the XML out...).
Now, this is all well and good, but I do have two -- probably very foolish -- questions:

For reference: My current XMLA query to discover the datasources is:

So, suppose I'll get an "AuthenticationMode==Authenticated" in the response, how would I rephrase my discoverAnything queries? (Which currently look like this -- for catalogs):

Regards,
Philipp
|||

Hi Phillip, your question is not stupid, your problem is that there are not many people that actually connect using raw XMLA. There are a lot of moving parts that you need to get right.

Chris Harrington has the best collection of blog posts I have seen on the subject here

http://www.activeinterface.com/thinolap.html

Particularly this one: http://www.activeinterface.com/b2005_11_21.html

How were you planning to authenticate? Were you going to use windows authentication, pass a username/password or are you happy with anonymous authentication?

|||Hi Darren,

thanks for your response -- now I know that at least I did not completely make a fool out of myself. I will check out the blog immediately and try to get it to work.

So, I'd be most happy if I could support all authentication modes, but for starters, passing a username/password would be just fine.
Again: Thanks for the links. They should get me started,
|||

The key to this in Chris's samples appears to be the following line (which I have simplified a bit) which opens the connection.

oHttp.open("POST", sUrl, false, sDomain + "\\" + sUser, sPass)

Anyway, thanks for your help!
Philipp

Newbie: Updating record with User information

Hello,

I have a table that has a UserUpdated field which has a default value of
SUSER_SNAME(). This works great when the record is created, but I'd like to
also update this value when the record is changed. If I understand things
correctly, I'll need a trigger to do this, but I've never created a trigger
before. Is this an easy thing to accomplish?

Thanks!

RickRico (you@.me.com) writes:

Quote:

Originally Posted by

I have a table that has a UserUpdated field which has a default value of
SUSER_SNAME(). This works great when the record is created, but I'd
like to also update this value when the record is changed. If I
understand things correctly, I'll need a trigger to do this, but I've
never created a trigger before. Is this an easy thing to accomplish?

CREATE TRIGGER tbltri ON tbl FOR INSERT,UPDATE AS
UPDATE tbl
SET moduser = SYSTEM_USER
FROM tbl t
JOIN inserted i ON t.pkcol = i.pkcol

SYSTEM_USER is the ANSI version of suser_sname() and easier to spell. :-)

The table "inserted" is a virtual tables that holds the rows that
were inserted or the after-image of the updated rows. There is also
a table "deleted" which holds deleted rows and the before-image of
updated rows.

A trigger fires once per statement, so there can be many rows in these
tables.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||That's excellent! Thank you very much for your help Erland, that was
exactly what I needed! (that will make coding in VB much easier. ;)

Rick

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns98ADF09B38EBDYazorman@.127.0.0.1...

Quote:

Originally Posted by

Rico (you@.me.com) writes:

Quote:

Originally Posted by

>I have a table that has a UserUpdated field which has a default value of
>SUSER_SNAME(). This works great when the record is created, but I'd
>like to also update this value when the record is changed. If I
>understand things correctly, I'll need a trigger to do this, but I've
>never created a trigger before. Is this an easy thing to accomplish?

>
CREATE TRIGGER tbltri ON tbl FOR INSERT,UPDATE AS
UPDATE tbl
SET moduser = SYSTEM_USER
FROM tbl t
JOIN inserted i ON t.pkcol = i.pkcol
>
SYSTEM_USER is the ANSI version of suser_sname() and easier to spell. :-)
>
The table "inserted" is a virtual tables that holds the rows that
were inserted or the after-image of the updated rows. There is also
a table "deleted" which holds deleted rows and the before-image of
updated rows.
>
A trigger fires once per statement, so there can be many rows in these
tables.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||On Jan 3, 4:38 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Rico (y...@.me.com) writes:

Quote:

Originally Posted by

I have a table that has a UserUpdatedfieldwhich has adefaultvalueof
SUSER_SNAME(). This works great when the record is created, but I'd
like to alsoupdatethisvaluewhen the record is changed. If I
understand things correctly, I'll need a trigger to do this, but I've
never created a trigger before. Is this an easy thing to accomplish?

>
CREATE TRIGGER tbltri ON tbl FOR INSERT,UPDATEASUPDATEtbl
SET moduser = SYSTEM_USER
FROM tbl t
JOIN inserted i ON t.pkcol = i.pkcol
>
SYSTEM_USER is the ANSI version of suser_sname() and easier to spell. :-)
>
The table "inserted" is a virtual tables that holds the rows that
were inserted or the after-image of the updated rows. There is also
a table "deleted" which holds deleted rows and the before-image of
updated rows.
>
A trigger fires once per statement, so there can be many rows in these
tables.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

I've come across this same issue...so I basically have to create a
trigger for each table in my database to update the username/modified
fields on each update. Is there a way to create a global update
trigger that will fire when any table with those fields is updated?

Thanks.
Rayne|||Rayne (wifetalks@.gmail.com) writes:

Quote:

Originally Posted by

No, but you could write a program to generate them.

You could also consider third-party tools ApexSql (www.apexsql.com)
has SQL Audit, for instance. I have not tried it myself.

Newbie: UPDATE a Table

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:529989
Hi,
I want to insert some new data in a Table. There is a possibility that there
are duplicate rows .
An Insert is obviously not allowed due to PK violatons.
The new data reside on a temporary Table and there are NO duplicates!
Will an Update do?
I simply want to :
"If the row exists replace it, if not insert the new one"
TIA
-steveIf you know you are replacing the old rows when you have a match, then
why not just delete them and then insert this scrubbed data, like this?
BEGIN
DELETE FROM Foo
WHERE Foo.key_col
IN (SELECT key_col FROM Bar);
INSERT INTO Foo
SELECT * FROM Bar;
END;|||Ok, thanx!
But, for info, there is no command that will do this in one step ?
and in my case the PK consists of multiple columns. However i get an error:
DELETE FROM tblMeasQAHor
WHERE
(tblMeasQAHor.MENVid, tblMeasQAHor.date_time, tblMeasQAHor.CodePol,
tblMeasQAHor.Valide, tblMeasQAHor.val)
IN
(SELECT MENVid, date_time, CodePol, Valide, val FROM tempQAHor)
why? i tried a few combinations and it doesn't work. If i only leave one
column then it's OK.
TIA
"--CELKO--" <jcelko212@.earthlink.net> a crit dans le message de news:
1117220035.893367.120570@.g14g2000cwa.googlegroups.com...
> If you know you are replacing the old rows when you have a match, then
> why not just delete them and then insert this scrubbed data, like this?
>
> BEGIN
> DELETE FROM Foo
> WHERE Foo.key_col
> IN (SELECT key_col FROM Bar);
> INSERT INTO Foo
> SELECT * FROM Bar;
> END;
>|||You'll need to do this in 2 steps. Delete then Insert
For the delete, test this out before you execute it on production code.
Replace temp1
with whatever your temp table name is
DELETE FROM tblMeasQAHor
FROM tblMeasQAHor t1
JOIN temp1 t2 on t1.MENVid = t2.MENVid
AND t1.date_time = t2.date_time
AND t1.CodePol = t2.CodePol
AND t1.Valide = t2.Valide
AND t1.val = t2.val
"steve" <steve@.here.com> wrote in message
news:n1Lle.40566$8j3.864446@.weber.videotron.net...
> Ok, thanx!
> But, for info, there is no command that will do this in one step ?
> and in my case the PK consists of multiple columns. However i get an
error:
> DELETE FROM tblMeasQAHor
> WHERE
> (tblMeasQAHor.MENVid, tblMeasQAHor.date_time, tblMeasQAHor.CodePol,
> tblMeasQAHor.Valide, tblMeasQAHor.val)
> IN
> (SELECT MENVid, date_time, CodePol, Valide, val FROM tempQAHor)
>
> why? i tried a few combinations and it doesn't work. If i only leave one
> column then it's OK.
> TIA
> "--CELKO--" <jcelko212@.earthlink.net> a crit dans le message de news:
> 1117220035.893367.120570@.g14g2000cwa.googlegroups.com...
>|||I just want to clarify. If you want to do it the way Joe recommends, you'll
need to do it in 2 steps. You could also do a correlated subquery
(untested)
INSERT INTO tblMeasQAHor
SELECT *
FROM temp1 t1
WHERE NOT EXISTS (
SELECT 'X' FROM tblMeasQAHor t2 WHERE t1.MENVid = t2.MENVid
AND t1.date_time = t2.date_time
AND t1.CodePol = t2.CodePol
AND t1.Valide = t2.Valide
AND t1.val = t2.val)
I'm not sure which you prefer. Also, I generally recommend using column
names in the insert and select. I don't know what the table looks like
structurally so I went with the select * approach.
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:eY77X9vYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> You'll need to do this in 2 steps. Delete then Insert
> For the delete, test this out before you execute it on production code.
> Replace temp1
> with whatever your temp table name is
> DELETE FROM tblMeasQAHor
> FROM tblMeasQAHor t1
> JOIN temp1 t2 on t1.MENVid = t2.MENVid
> AND t1.date_time = t2.date_time
> AND t1.CodePol = t2.CodePol
> AND t1.Valide = t2.Valide
> AND t1.val = t2.val
>
> "steve" <steve@.here.com> wrote in message
> news:n1Lle.40566$8j3.864446@.weber.videotron.net...
> error:
this?
>

newbie: TSQL command for number of rows ?

I couldnt find on google a command to get the number of rows in a table.
Any suggestions ?
TIA
-steveSELECT COUNT(*)
FROM YourTable
David Portas
SQL Server MVP
--|||Use the TOP statement
Example:
USE NORTHWIND
GO
Select TOP 5 From Orders
But remember that TOP is also recommended with the use of order because the
TOP clause will produce an unpredictable subset of n-Rows.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"steve" <steve@.here.com> schrieb im Newsbeitrag
news:Ph4ie.56688$th3.616072@.wagner.videotron.net...
>I couldnt find on google a command to get the number of rows in a table.
> Any suggestions ?
> TIA
> -steve
>|||SELECT COUNT(*) FROM tbl
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"steve" <steve@.here.com> wrote in message news:Ph4ie.56688$th3.616072@.wagner.videotron.net.
.
>I couldnt find on google a command to get the number of rows in a table.
> Any suggestions ?
> TIA
> -steve
>|||Ok, guess I didnt get your questions that right X-)
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> schrieb
im Newsbeitrag news:u7Wq6ljWFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Use the TOP statement
> Example:
> USE NORTHWIND
> GO
> Select TOP 5 From Orders
> But remember that TOP is also recommended with the use of order because
> the TOP clause will produce an unpredictable subset of n-Rows.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "steve" <steve@.here.com> schrieb im Newsbeitrag
> news:Ph4ie.56688$th3.616072@.wagner.videotron.net...
>|||I am useless!
Thanx a lot!!
-steve
"steve" <steve@.here.com> a crit dans le message de news:
Ph4ie.56688$th3.616072@.wagner.videotron.net...
>I couldnt find on google a command to get the number of rows in a table.
> Any suggestions ?
> TIA
> -steve
>

Newbie: Truncating transaction logs

A 3rd party company told us that we need to truncate our SQL Server
Transaction logs because the server's hard disk is getting full. The hard
disk is losing around 10 GB a day. They also told us to change the SQL
Server Backup type from Complex to Simple.
How do I truncate the log files and how do I change the backup type?
Regards
Thief_This is a multi-part message in MIME format.
--030007050705010603010200
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
BACKUP LOG <DBName> with NO_LOG
ALTER DATABASE <DBName> SET RECOVERY SIMPLE
You should be careful doing this though because it will mean no
transactions are getting logged. So you can only do database backups
(not log backups) on that DB and if you lose your DB (eg. corruption)
between database backups, you'll have to restore from your last full
backup (so you will lose all data that had be written to the database
since then). What is a more normal thing to do is to keep your database
in FULL recovery mode and schedule regular transaction log backups
(which is easiest to do with the Database Maintenance Wizard that comes
as part of SQL Enterprise Manager).
If you're going to trash your transaction log and are not interested in
keeping it then you may as well shrink your log file down for your
database as well. To do that you need to know the logical name of the
log file, which you can find with
exec sp_helpdb <DBName>
and then shrink the file with
USE <DBName>
go
DBCC SHRINKFILE('<LogicalFileName>')
Hope that helps.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Thief_ wrote:
>A 3rd party company told us that we need to truncate our SQL Server
>Transaction logs because the server's hard disk is getting full. The hard
>disk is losing around 10 GB a day. They also told us to change the SQL
>Server Backup type from Complex to Simple.
>How do I truncate the log files and how do I change the backup type?
>Regards
>Thief_
>
>
--030007050705010603010200
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>BACKUP LOG <DBName> with NO_LOG 
ALTER DATABASE <DBName> SET RECOVERY SIMPLE 
 
You should be careful doing this though because it will mean no
transactions are getting logged. So you can only do database backups
(not log backups) on that DB and if you lose your DB (eg. corruption)
between database backups, you'll have to restore from your last full
backup (so you will lose all data that had be written to the database
since then). What is a more normal thing to do is to keep your
database in FULL recovery mode and schedule regular transaction log
backups (which is easiest to do with the Database Maintenance Wizard
that comes as part of SQL Enterprise Manager). 
 
If you're going to trash your transaction log and are not interested in
keeping it then you may as well shrink your log file down for your
database as well. To do that you need to know the logical name of the
log file, which you can find with 
</tt>
<blockquote><tt>exec sp_helpdb <DBName></tt> 
</blockquote>
<tt>and then shrink the file with 
</tt>
<blockquote><tt>USE <DBName></tt> 
<tt>go</tt> 
<tt>DBCC SHRINKFILE('<LogicalFileName>') 
</tt></blockquote>
<tt>Hope that helps.</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Thief_ wrote:
<blockquote cite="midOviKzNnvFHA.3688@.tk2msftngp13.phx.gbl" type="cite">
<pre wrap="">A 3rd party company told us that we need to truncate our SQL Server
Transaction logs because the server's hard disk is getting full. The hard
disk is losing around 10 GB a day. They also told us to change the SQL
Server Backup type from Complex to Simple.
How do I truncate the log files and how do I change the backup type?
Regards
Thief_
</pre>
</blockquote>
</body>
</html>
--030007050705010603010200--|||Hi,
For me the best practice i do is
backup log epinav with TRUNCATE_ONLY
dbcc shrinkfile(logical name,size)
this will shrink the size of logfile on disk.
hope this help u
from
Doller|||Hi,
This method is not recommended on the production system or any system which
has critical data.
If you make the recovery model SIMPLE then you can not perform a transaction
log backup. In this case
if you have any database crash you can recover the database only until the
last fulll backup.
So instead of changing the recovery mode; have the mode as "FULL" and
schedule a transaction log backup
using Maintenance plan and put a house keeping mechanism to purge the old
files.
If you data is not critical then follow as dolar pointed out:-
backup log epinav with TRUNCATE_ONLY
go
dbcc shrinkfile(logical name,size)
go
ALTER DATABASE <dbname> set recovery SIMPLE
Thanks
Hari
SQL Server MVP
"doller" <sufianarif@.gmail.com> wrote in message
news:1127292013.451860.127540@.o13g2000cwo.googlegroups.com...
> Hi,
> For me the best practice i do is
> backup log epinav with TRUNCATE_ONLY
> dbcc shrinkfile(logical name,size)
> this will shrink the size of logfile on disk.
> hope this help u
> from
> Doller
>|||Hari,
>>schedule a transaction log backup
>>using Maintenance plan and put a house keeping mechanism to purge the old
>>files.
How specifically do I do that?
Regards
Thief_
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ecyPfHqvFHA.2792@.tk2msftngp13.phx.gbl...
> Hi,
> This method is not recommended on the production system or any system
> which has critical data.
> If you make the recovery model SIMPLE then you can not perform a
> transaction log backup. In this case
> if you have any database crash you can recover the database only until the
> last fulll backup.
> So instead of changing the recovery mode; have the mode as "FULL" and
> schedule a transaction log backup
> using Maintenance plan and put a house keeping mechanism to purge the old
> files.
> If you data is not critical then follow as dolar pointed out:-
> backup log epinav with TRUNCATE_ONLY
> go
> dbcc shrinkfile(logical name,size)
> go
> ALTER DATABASE <dbname> set recovery SIMPLE
>
> Thanks
> Hari
> SQL Server MVP
>
>
> "doller" <sufianarif@.gmail.com> wrote in message
> news:1127292013.451860.127540@.o13g2000cwo.googlegroups.com...
>> Hi,
>> For me the best practice i do is
>> backup log epinav with TRUNCATE_ONLY
>> dbcc shrinkfile(logical name,size)
>> this will shrink the size of logfile on disk.
>> hope this help u
>> from
>> Doller
>|||Why are the server hard disks losing space so quickly? Is it the fact that
the log files are not being deleted and everyday new ones are created adding
to the congestion?
How do I delete the existing log files? Are they simply files on the
server's hard disk?
Regards
Thief_
"Thief_" <thief_@.hotmail.com> wrote in message
news:OviKzNnvFHA.3688@.tk2msftngp13.phx.gbl...
>A 3rd party company told us that we need to truncate our SQL Server
>Transaction logs because the server's hard disk is getting full. The hard
>disk is losing around 10 GB a day. They also told us to change the SQL
>Server Backup type from Complex to Simple.
> How do I truncate the log files and how do I change the backup type?
> Regards
> Thief_
>|||This is a multi-part message in MIME format.
--040606020606020807010101
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Use the Database Maintenance Wizard.
That is, open SQL Enterprise Manager, expand the server in the tree view
in the left pane, expand "management", right click on "Database
Maintenance Plans", click "New Maintenance Plan...". Then just go
through the wizard pages paying particular attention to the "Specify the
Transaction Log Backup Plan" page (which is about 6 pages into the
wizard). On the next page ("Specify Transaction Log Backup Disk
Directory") you define how long to keep old backup files for.
Typically you keep backup files on disk at least long enough to get them
on tape with some other general backup software (like Veritas
BackupExec, etc.), but preferably a few days because most restores are
from backups sometime in the last few days (like a business unit says
they need the database put back to how it was at 3pm yesterday for
example). The backup file retention period is normally dependant on how
much disk space you can spare for the backup files.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Thief_ wrote:
>Hari,
>
>>schedule a transaction log backup
>>using Maintenance plan and put a house keeping mechanism to purge the old
>>files.
>>
>How specifically do I do that?
>Regards
>Thief_
>"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>news:ecyPfHqvFHA.2792@.tk2msftngp13.phx.gbl...
>
>>Hi,
>>This method is not recommended on the production system or any system
>>which has critical data.
>>If you make the recovery model SIMPLE then you can not perform a
>>transaction log backup. In this case
>>if you have any database crash you can recover the database only until the
>>last fulll backup.
>>So instead of changing the recovery mode; have the mode as "FULL" and
>>schedule a transaction log backup
>>using Maintenance plan and put a house keeping mechanism to purge the old
>>files.
>>If you data is not critical then follow as dolar pointed out:-
>>backup log epinav with TRUNCATE_ONLY
>>go
>>dbcc shrinkfile(logical name,size)
>>go
>>ALTER DATABASE <dbname> set recovery SIMPLE
>>
>>Thanks
>>Hari
>>SQL Server MVP
>>
>>
>>"doller" <sufianarif@.gmail.com> wrote in message
>>news:1127292013.451860.127540@.o13g2000cwo.googlegroups.com...
>>
>>Hi,
>>For me the best practice i do is
>>backup log epinav with TRUNCATE_ONLY
>>dbcc shrinkfile(logical name,size)
>>this will shrink the size of logfile on disk.
>>hope this help u
>>from
>>Doller
>>
>>
>
>
--040606020606020807010101
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Use the Database Maintenance Wizard. 
 
That is, open SQL Enterprise Manager, expand the server in the tree
view in the left pane, expand "management", right click on "Database
Maintenance Plans", click "New Maintenance Plan...". Then just go
through the wizard pages paying particular attention to the "Specify
the Transaction Log Backup Plan" page (which is about 6 pages into the
wizard). On the next page ("Specify Transaction Log Backup Disk
Directory") you define how long to keep old backup files for. 
 
Typically you keep backup files on disk at least long enough to get
them on tape with some other general backup software (like Veritas
BackupExec, etc.), but preferably a few days because most restores are
from backups sometime in the last few days (like a business unit says
they need the database put back to how it was at 3pm yesterday for
example). The backup file retention period is normally dependant on
how much disk space you can spare for the backup files.</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Thief_ wrote:
<blockquote cite="midu4Lu0tzvFHA.3864@.TK2MSFTNGP12.phx.gbl" type="cite">
<pre wrap="">Hari,
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">schedule a transaction log backup
using Maintenance plan and put a house keeping mechanism to purge the old
files.
</pre>
</blockquote>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
</body>
</html>
--040606020606020807010101--|||This is a multi-part message in MIME format.
--060209080001050000020506
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Every modification transaction against your database not only changes
the data but is appended to a transaction log. There is 1 transaction
log per database. The transaction log is not a physical file per se,
but is materialised on disk as one or more physical files (but you
cannot just delete the files without corrupting the database).
As I said in my first reply to this thread, you can see where your
physical transaction log file(s) is by using:
exec sp_helpdb <DBName>
This is a Transaction-SQL command that you run from within Query
Analyzer. This stored procedure will return a bunch of data that is
split into 2 sections: 1) summary info about the database and 2) the
names of all the physical files associated with your database. In the
2nd section, one or more rows will have "log only" in the last column of
the resultset. These are the transaction log files.
The way you reduce the size of your transaction logs is by backing them
up (hence all the talk about backing up your log via the Database
Maintenance Wizard). Typically you back up your transaction logs at
least one a day (I backup my production DB logs every 15 minutes).
Immediately after the backup log command is finished (the database
maintenance wizard runs these commands itself once you've set up a plan,
there's no need to do anything yourself other than set up the plan) the
log records automatically get deleted. This does not make the physical
files smaller, it just creates a whole lot of empty space in the
existing physical files that can then be filled up with more transaction
log records. This is by design and for good reason (but I won't go into
that in this thread as it is a tangent and this post is getting long
enough as it is).
If you've let the transaction log of one of your databases grow for a
lengthy period of time, the physical file(s) backing the log has
probably grown to many times the size needed to hold the transactions
you expect to accumulate between your scheduled automated (database
maintenance plan) log backups. So, in that case, it may be worthwhile
shrinking the physical file(s) associated with the log (this is not
something you would normally do in production databases as it pauses all
disk I/O to the file being shrunk while the shrink operation is
happening (and again if the file autogrows)). You shrink physical
files, as I said in my first reply to this thread, by using the DBCC
SHRINKFILE() command. That command requires the name of the logical
file (which sp_helpdb returns). Just make a note of that logical
filename (the first column, "name", in the "files" section of the
sp_helpdb resultset) and use it in the DBCC SHRINKFILE command as follows:
DBCC SHRINKFILE('<LogicalFileName>')
This command has to be run in the database in question with Query Analyzer.
Does this all make sense?
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Thief_ wrote:
>Why are the server hard disks losing space so quickly? Is it the fact that
>the log files are not being deleted and everyday new ones are created adding
>to the congestion?
>How do I delete the existing log files? Are they simply files on the
>server's hard disk?
>Regards
>Thief_
>"Thief_" <thief_@.hotmail.com> wrote in message
>news:OviKzNnvFHA.3688@.tk2msftngp13.phx.gbl...
>
>>A 3rd party company told us that we need to truncate our SQL Server
>>Transaction logs because the server's hard disk is getting full. The hard
>>disk is losing around 10 GB a day. They also told us to change the SQL
>>Server Backup type from Complex to Simple.
>>How do I truncate the log files and how do I change the backup type?
>>Regards
>>Thief_
>>
>
>
--060209080001050000020506
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Every modification transaction against your database not only
changes the data but is appended to a transaction log. There is 1
transaction log per database. The transaction log is not a physical
file per se, but is materialised on disk as one or more physical files
(but you cannot just delete the files without corrupting the database). 
 
As I said in my first reply to this thread, you can see where your
physical transaction log file(s) is by using: 
</tt>
<blockquote><tt>exec sp_helpdb <DBName> 
</tt></blockquote>
<tt>This is a Transaction-SQL command that you run from within Query
Analyzer. This stored procedure will return a bunch of data that is
split into 2 sections: 1) summary info about the database and 2) the
names of all the physical files associated with your database. In the
2nd section, one or more rows will have "log only" in the last column
of the resultset. These are the transaction log files. 
 
The way you reduce the size of your transaction logs is by backing them
up (hence all the talk about backing up your log via the Database
Maintenance Wizard). Typically you back up your transaction logs at
least one a day (I backup my production DB logs every 15 minutes).
Immediately after the backup log command is finished (the database
maintenance wizard runs these commands itself once you've set up a
plan, there's no need to do anything yourself other than set up the
plan) the log records automatically get deleted. This does not make
the physical files smaller, it just creates a whole lot of empty space
in the existing physical files that can then be filled up with more
transaction log records. This is by design and for good reason (but I
won't go into that in this thread as it is a tangent and this post is
getting long enough as it is). 
 
If you've let the transaction log of one of your databases grow for a
lengthy period of time, the physical file(s) backing the log has
probably grown to many times the size needed to hold the transactions
you expect to accumulate between your scheduled automated (database
maintenance plan) log backups. So, in that case, it may be worthwhile
shrinking the physical file(s) associated with the log (this is not
something you would normally do in production databases as it pauses
all disk I/O to the file being shrunk while the shrink operation is
happening (and again if the file autogrows)). You shrink physical
files, as I said in my first reply to this thread, by using the DBCC
SHRINKFILE() command. That command requires the name of the logical
file (which sp_helpdb returns). Just make a note of that logical
filename (the first column, "name", in the "files" section of the
sp_helpdb resultset) and use it in the DBCC SHRINKFILE command as
follows: 
</tt>
<blockquote><tt>DBCC SHRINKFILE('<LogicalFileName>') 
</tt></blockquote>
<tt>This command has to be run in the database in question with Query
Analyzer. 
 
Does this all make sense? 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Thief_ wrote:
<blockquote cite="midOoCW$tzvFHA.2956@.TK2MSFTNGP09.phx.gbl" type="cite">
<pre wrap="">Why are the server hard disks losing space so quickly? Is it the fact that
the log files are not being deleted and everyday new ones are created adding
to the congestion?
How do I delete the existing log files? Are they simply files on the
server's hard disk?
Regards
Thief_
"Thief_" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:thief_@.hotmail.com"><thief_@.hotmail.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:OviKzNnvFHA.3688@.tk2msftngp13.phx.gbl">news:OviKzNnvFHA.3688@.tk2msftngp13.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">A 3rd party company told us that we need to truncate our SQL Server
Transaction logs because the server's hard disk is getting full. The hard
disk is losing around 10 GB a day. They also told us to change the SQL
Server Backup type from Complex to Simple.
How do I truncate the log files and how do I change the backup type?
Regards
Thief_
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
</body>
</html>
--030107030007020107030305--|||This is a multi-part message in MIME format.
--=_NextPart_000_000F_01C5C294.05010B30
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Mike,
WOW! The time you've spent is recorded in the back of my mind- always. =The info you have supplied is super and will become part of my =knowledge-base for ever. As for your questions, the DBs JLRXAPP & =JLRXSYS are our production DBs (although the difference b/w the APP & =SYS DBs is unknown to me at the moment but I've asked our Solomon =support company for details on each) and our test DBs are JLRXTestApp & =JLRXTestSys.
I feel that the shrinking of the DBs will be a time-crunching effort so =I will perform it later tonight when everyone is off the system.
Regards
Thief_
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message =news:urrw9VBwFHA.1148@.TK2MSFTNGP11.phx.gbl...
OK, a number of points...but firstly a couple questions. Is this a =production DB or a test/dev DB? Is it a super-active DB or just a DB =that has relatively normal activity against it but just hasn't had its =log backed up for ages? I'll assume it is a production DB and it's just =a normal OLTP database that hasn't had a log backup for a long time. =Given those assumptions, if this was my DB, I would make these changes:
a.. Do the optimisations once per week (this essentially rebuilds =all your indexes, which is a fairly intense activity). I would schedule =it for Saturday or Sunday night. And don't automatically shrink your =files. Shrinking the data file will cause a large amount of log =activity (because it's shuffling data pages all over the place, hence it =needs to log all that change activity). b.. Integrity checks once a week on the weekend is good. Make sure =you check the SQL errorlog each Monday (minimum) to see if the DBCC =CHECKDB command (that's the command that SQL Server runs in this =Integrity Check stage) logged any errors or warnings. c.. Full database backup once a day is good. Sometime out of peak =hours (like 4am) is good. However, I would make the target of all DB =backups a different physical disk to where the data & logs live. For =example, if your data & logs live on the D: drive, then I would add =another local HDD for the backup files (call it E: drive) and backup to =that disk. This is because, if you have a HDD failure and lose your D: =drive completely, unless it's part of a fault tolerant RAID set (like =RAID 1 or RAID 5) then not only have you lost your data & logs but =you've lost your backup of those DBs too! Better yet would be a backup =disk volume that didn't live inside the server at all (to safe-guard =against the whole server catching fire, etc.), like a disk on a SAN or =NAS device, or even a DAS (like an external RAID cage). However, this =can add considerable initial cost so it can often be hard to justify. d.. A transaction log backup once a day that occurs 1 hour after the =last full DB backup will be next to useless. How much activity occurs =in the 1 hour, from 4am to 5am, between the full DB backup and the =transaction log backup? Remember, the transaction log records changes =to the database, so to restore you'd restore the last full DB backup and =any subsequent transaction log backups that occurred after the DB =backup. If I were you I'd start with backing up the transaction log =once per hour, eg. every hour starting at quarter to one [00:45am] =(offset from 0:00 as often jobs get scheduled on the hour so this can be =a busy time to do a backup), to the same directory you put the DB backup =files. I'd also keep them for the same amount of time as the full DB =backups. If you keep 3 days of DB backups, then keep 3 days of =transaction log backups (you can't use any transaction log backups that =were taken before your oldest DB backup because you have to restore them =AFTER you restore the appropriate DB backup). The points about auto-shrinking files & transaction log backups are =probably the most important points here. Also, I'd adjust the settings =of the DB itself a little. This is how I would set the data file & the =log file:
a.. I'd set your 8.4GB data file to be 9GB and autogrow by 1GB at a =time (nice large, round numbers - neat). This is the command that would =do that: ALTER DATABASE JLRXApp MODIFY FILE
(NAME=3D'JLTESTAPP_Data', SIZE=3D9216MB, MAXSIZE=3DUNLIMITED, =FILEGROWTH=3D1024MB)
a.. I'd set the 42.8GB log file to be 1GB and autogrow by 512MB at a =time. This is the command that would do that: ALTER DATABASE JLRXApp MODIFY FILE
(NAME=3D'JLTESTAPP_Log', SIZE=3D1024MB, MAXSIZE=3DUNLIMITED, =FILEGROWTH=3D512MB)
However, you'll have to shrink your log before you can set it to the =new size. There's not much point in backing up all 44GB - you'd be =better off truncating the log and then doing a full DB backup:
BACKUP LOG JLRXApp WITH NO_LOG
CHECKPOINT
DBCC SHRINKFILE('JLTESTAPP_Log')
BACKUP DATABASE JLRXApp TO
DISK=3D'D:\Program Files\Microsoft SQL =Server\MSSQL\BACKUP\JLRXApp_FixUp.bak' WITH INIT
The database should now be backed up and the log shrunk, so now you =can set the size of the transaction log file with the 2nd ALTER DATABASE =statement above. After a couple weeks, once you're sure things are =humming along nicely, you'll be able to delete that JLRXApp_FixUp.bak =file (which was just a precaution really).
All this should leave you with:
- a 9GB database
- a 1GB log
- that get reindexed & checked on a regular basis (once a week on the =weekends)
- and get backed up on a nice regular schedule that shouldn't overtax =your disk space.
Good DB setup, good maintenance plan. Just make sure you test your =backup files regularly (try restoring the backup files (both database =backups and log backups) to another server (or at least another DB on =the server) once a month or so) because if you can't restore from your =backup files, then they are useless and you may as well not be doing =them (always test your recovery strategy - recovery is the whole reason =you do the backups in the first place).
Remember, this is just a starting place. I'd expect you to monitor =how things are going (disk space, DB size, index performance, etc.) and =tweak things as appropriate (like more/less regular tlog backups or more =regular optimisations or something like that). Don't forget to =regularly test your backup files!
Hope this helps.
--
mike hodgson
blog: http://sqlnerd.blogspot.com=20
Thief_ wrote: Thanks Mike,
Your help has been super-human and much appreciated. This is the =Maintenance Plan I have created:
----=----
DATABASES
JLRXApp
SERVERS
(local)
OPTIMIZATIONS
Occurs every 1 week(s) on Monday, Tuesday, Wednesday, Thursday, Friday, =at 3:00:00 AM.
Perform the following actions:
Reorganize data and index pages, changing the free space to 10 percent =of the original space.
Shrink database when it grows beyound 10000 MB. Leave 10 percent of =data space as free space.
INTEGRITY CHECKS
Occurs every 1 week(s) on Sunday, at 2:00:00 AM.
Perform the following actions:
Check database
COMPLETE BACKUP
Occurs every 1 week(s) on Monday, Tuesday, Wednesday, Thursday, Friday, =at 4:00:00 AM.
Backup media: Disk
Store backup files in the default SQL Server Backup directory.
Delete backup files which are older than 3 Day(s).
Verify the backup after completion.
Create a subdirectory for each database, to store the backup files.
TRANSACTION LOG BACKUP
Occurs every 1 week(s) on Monday, Tuesday, Wednesday, Thursday, Friday, =at 5:00:00 AM.
Backup media: Disk
Store backup files in the default SQL Server Backup directory.
Delete backup files which are older than 4 Day(s).
Verify the backup after completion.
Create a subdirectory for each database, to store the backup files.
Reports will be generated and stored in directory: d:\d:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Log
Operator 'SQL_Backup_Notify' will be e-mailed.
----=----
Here's my DB info:
JLTESTAPP_Data 1 d:\Program Files\Microsoft SQLServer\MSSQL\data\JLRXApp.MDF PRIMARY 8843520 KB Unlimited 10% data only
JLTESTAPP_Log 2 d:\Program Files\Microsoft SQL Server\MSSQL\data\JLRXApp_Log.LDF NULL 44843200 KB Unlimited 10% log only
I hope I have setup the MP properly. I have set it up to shrink the DB automatically- I hope this is OK?!
Regards
Thief_
"Thief_" <thief_@.hotmail.com> wrote in message news:OviKzNnvFHA.3688@.tk2msftngp13.phx.gbl...
A 3rd party company told us that we need to truncate our SQL Server Transaction logs because the server's hard disk is getting full. The =hard disk is losing around 10 GB a day. They also told us to change the SQL Server Backup type from Complex to Simple.
How do I truncate the log files and how do I change the backup type?
Regards
Thief_

--=_NextPart_000_000F_01C5C294.05010B30
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Mike,
WOW! The time you've spent is recorded =in the back of my mind- always. The info you have supplied is super and will become =part of my knowledge-base for ever. As for your questions, the DBs JLRXAPP & =JLRXSYS are our production DBs (although the difference b/w the APP & SYS =DBs is unknown to me at the moment but I've asked our Solomon support company =for details on each) and our test DBs are JLRXTestApp & JLRXTestSys.
I feel that the shrinking of the DBs =will be a time-crunching effort so I will perform it later tonight when everyone =is off the system.
Regards
Thief_
"Mike Hodgson" wrote in message news:urrw9VBwFHA.1148=@.TK2MSFTNGP11.phx.gbl...OK, a number of points...but firstly a couple questions. Is this a production DB or a test/dev DB? Is it a super-active DB or just =a DB that has relatively normal activity against it but just hasn't had its =log backed up for ages? I'll assume it is a production DB and it's =just a normal OLTP database that hasn't had a log backup for a long =time. Given those assumptions, if this was my DB, I would make these =changes:
Do the optimisations once per week (this essentially =rebuilds all your indexes, which is a fairly intense activity). I would =schedule it for Saturday or Sunday night. And don't automatically =shrink your files. Shrinking the data file will cause a large amount =of log activity (because it's shuffling data pages all over the place, =hence it needs to log all that change activity). Integrity checks once a week on the weekend is good. =Make sure you check the SQL errorlog each Monday (minimum) to see if the DBCC =CHECKDB command (that's the command that SQL Server runs in this Integrity =Check stage) logged any errors or warnings. Full database backup once a day is good. Sometime out =of peak hours (like 4am) is good. However, I would make the target of =all DB backups a different physical disk to where the data & logs =live. For example, if your data & logs live on the D: drive, then I =would add another local HDD for the backup files (call it E: drive) and backup =to that disk. This is because, if you have a HDD failure and lose your =D: drive completely, unless it's part of a fault tolerant RAID set =(like RAID 1 or RAID 5) then not only have you lost your data & logs but =you've lost your backup of those DBs too! Better yet would be a backup =disk volume that didn't live inside the server at all (to safe-guard against the =whole server catching fire, etc.), like a disk on a SAN or NAS device, or =even a DAS (like an external RAID cage). However, this can add =considerable initial cost so it can often be hard to justify. A transaction log backup once a day that occurs 1 hour after =the last full DB backup will be next to useless. How much activity =occurs in the 1 hour, from 4am to 5am, between the full DB backup and the transaction log backup? Remember, the transaction log records changes to the database, so to restore you'd restore the last =full DB backup and any subsequent transaction log backups that occurred =after the DB backup. If I were you I'd start with backing up the =transaction log once per hour, eg. every hour starting at quarter to one [00:45am] =(offset from 0:00 as often jobs get scheduled on the hour so this can be a =busy time to do a backup), to the same directory you put the DB backup =files. I'd also keep them for the same amount of time as the full DB =backups. If you keep 3 days of DB backups, then keep 3 days of transaction =log backups (you can't use any transaction log backups that were taken =before your oldest DB backup because you have to restore them AFTER you =restore the appropriate DB backup). The points about =auto-shrinking files & transaction log backups are probably the most important =points here. Also, I'd adjust the settings of the DB itself a =little. This is how I would set the data file & the log file:
I'd set your 8.4GB data file to be 9GB and autogrow by 1GB =at a time (nice large, round numbers - neat). This is the command that =would do that:
ALTER DATABASE JLRXApp MODIFY =FILE (NAME=3D'JLTESTAPP_Data', SIZE=3D9216MB, =MAXSIZE=3DUNLIMITED, FILEGROWTH=3D1024MB)
I'd set the 42.8GB log file to be 1GB and autogrow by 512MB =at a time. This is the command that would do that:
ALTER DATABASE JLRXApp MODIFY =FILE (NAME=3D'JLTESTAPP_Log', SIZE=3D1024MB, MAXSIZE=3DUNLIMITED, FILEGROWTH=3D512MB)However, you'll have to =shrink your log before you can set it to the new size. There's not much =point in backing up all 44GB - you'd be better off truncating the log and then =doing a full DB backup:
BACKUP LOG JLRXApp WITH NO_LOGCHECKPOINTDBCC SHRINKFILE('JLTESTAPP_Log')BACKUP DATABASE JLRXApp TO DISK=3D'D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\JLRXApp_FixUp.bak' WITH =INITThe database should now be backed up and the log shrunk, so now you can =set the size of the transaction log file with the 2nd ALTER DATABASE statement = above. After a couple weeks, once you're sure things are humming =along nicely, you'll be able to delete that JLRXApp_FixUp.bak file =(which was just a precaution really).All this should leave you =with:- a 9GB database- a 1GB log- that get reindexed & checked on a =regular basis (once a week on the weekends)- and get backed up on a nice =regular schedule that shouldn't overtax your disk space.Good DB setup, =good maintenance plan. Just make sure you test your backup files =regularly (try restoring the backup files (both database backups and log =backups) to another server (or at least another DB on the server) once a month =or so) because if you can't restore from your backup files, then they are =useless and you may as well not be doing them (always test your recovery strategy =- recovery is the whole reason you do the backups in the first place).Remember, this is just a starting place. I'd =expect you to monitor how things are going (disk space, DB size, index =performance, etc.) and tweak things as appropriate (like more/less regular tlog backups =or more regular optimisations or something like that). Don't forget to =regularly test your backup files!Hope this helps.
--mike =hodgsonblog: http://sqlnerd.blogspot.com Thief_ wrote: Thanks Mike,
Your help has been super-human and much appreciated. This is the =Maintenance Plan I have created:
----=----
DATABASES
JLRXApp
SERVERS
(local)
OPTIMIZATIONS
Occurs every 1 week(s) on Monday, Tuesday, Wednesday, Thursday, Friday, =at 3:00:00 AM.
Perform the following actions:
Reorganize data and index pages, changing the free space to 10 percent =of the original space.
Shrink database when it grows beyound 10000 MB. Leave 10 percent of =data space as free space.
INTEGRITY CHECKS
Occurs every 1 week(s) on Sunday, at 2:00:00 AM.
Perform the following actions:
Check database
COMPLETE BACKUP
Occurs every 1 week(s) on Monday, Tuesday, Wednesday, Thursday, Friday, =at 4:00:00 AM.
Backup media: Disk
Store backup files in the default SQL Server Backup directory.
Delete backup files which are older than 3 Day(s).
Verify the backup after completion.
Create a subdirectory for each database, to store the backup files.
TRANSACTION LOG BACKUP
Occurs every 1 week(s) on Monday, Tuesday, Wednesday, Thursday, Friday, =at 5:00:00 AM.
Backup media: Disk
Store backup files in the default SQL Server Backup directory.
Delete backup files which are older than 4 Day(s).
Verify the backup after completion.
Create a subdirectory for each database, to store the backup files.
Reports will be generated and stored in directory: d:\d:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Log
Operator 'SQL_Backup_Notify' will be e-mailed.
----=----
Here's my DB info:
JLTESTAPP_Data 1 d:\Program Files\Microsoft SQLServer\MSSQL\data\JLRXApp.MDF PRIMARY 8843520 KB Unlimited 10% data only
JLTESTAPP_Log 2 d:\Program Files\Microsoft SQL Server\MSSQL\data\JLRXApp_Log.LDF NULL 44843200 KB Unlimited 10% log only
I hope I have setup the MP properly. I have set it up to shrink the DB automatically- I hope this is OK?!
Regards
Thief_
"Thief_" wrote =in message news:OviKzNnvFHA.3688=@.tk2msftngp13.phx.gbl...
A 3rd party company told us =that we need to truncate our SQL Server Transaction logs because the server's hard disk is getting full. The =hard disk is losing around 10 GB a day. They also told us to change the SQL Server Backup type from Complex to Simple.
How do I truncate the log files and how do I change the backup type?
Regards
Thief_
--=_NextPart_000_000F_01C5C294.05010B30--|||This is a multi-part message in MIME format.
--050801080804090309010007
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Shrinking the *data file* is time consuming and I/O intensive because
SQL server will actually be shuffling pages around (hence the need to
log those activities in the transaction log). But shrinking
*transaction log files* is very quick & easy because it's just
truncating the inactive portion of the log (not actually moving pages
around). So it's fairly safe to shrink transaction log files in
business hours (given the normal caveats around shrinking files &
autogrowing files).
However, if the active portion of the log is at the end of the file,
then a DBCC SHRINKFILE operation won't achieve much because it can only
truncate back to the end of the active portion (so the log file may
consist of gigabytes of empty space at the beginning of the file
followed by a few megabytes of real transactions at the end of the
file). The reason behind this is that transaction logs are divided up
into virtual log files, which are cyclical - when you reach the end of
the physical file, SQL Server will start writing records again from the
beginning of the physical file until it comes full circle back to where
it started (at that point it must increase the size of the physical file
again). So when you truncate a transaction log, usually by doing a
BACKUP LOG, all the transactions in the log are backed up and deleted,
leaving a largely empty log file. SQL Server doesn't start writing
again from the beginning of the file, it just keeps going from where it
left off - hence the circular/cyclical aspect of the log. This means
that sometimes you can get huge amounts of empty space at the start of
the file and just a few transactions at the end of the file and that
means SQL Server can only truncate the very end of the physical file (if
anything). So your 44GB log file may only shrink down to 43GB for example.
This was the reason for doing a BACKUP LOG and a CHECKPOINT, in my
previous post, before actually doing the DBCC SHRINKFILE, just to make
sure the end of the physical log file has been cleared of active
transactions and the DBCC SHRINKFILE will actually achieve something.
The structure/behaviour of the transaction log is actually fairly
complicated - this explanation is really just an executive
summary/paraphrase. If you want to read up on it, SQL Books Online has
some excellent info on the transaction log (like the Transaction Log
Architecture
<http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_876t.asp>
section). For even more I'd recommend Inside SQL Server 2000 by Kalen
Delaney
<http://www.amazon.com/exec/obidos/tg/detail/-/0735609985/qid=1127709307/sr=8-1/ref=pd_bbs_1/104-8387021-1711117?v=glance&s=books&n=507846>,
chapter 5 (Databases & Database Files) - particularly pp.182-186
(dealing with the transaction log structure & changing the log size).
An essential tome for every professional DBA's bookshelf.
So, you should be able to shrink the transaction log very quickly &
easily with very little impact to the end users, but you should ditch
any transactions currently in there (with BACKUP LOG & CHECKPOINT) just
so the shrink operation shrinks right back to the near the beginning of
the file (so you may want to do that after hours so you can do a full
database backup immediately after the shrink without impacting your
users). Of course, you can do the whole thing during business hours,
users will still be able to work, but the performance of the SQL server
will be sluggish while you are doing the BACKUP DATABASE operation
(depending on your hardware).
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Thief_ wrote:
> Mike,
> WOW! The time you've spent is recorded in the back of my mind- always.
> The info you have supplied is super and will become part of my
> knowledge-base for ever. As for your questions, the DBs JLRXAPP &
> JLRXSYS are our production DBs (although the difference b/w the APP &
> SYS DBs is unknown to me at the moment but I've asked our Solomon
> support company for details on each) and our test DBs are JLRXTestApp
> & JLRXTestSys.
> I feel that the shrinking of the DBs will be a time-crunching effort
> so I will perform it later tonight when everyone is off the system.
> Regards
> Thief_
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in message
> news:urrw9VBwFHA.1148@.TK2MSFTNGP11.phx.gbl...
> OK, a number of points...but firstly a couple questions. Is this
> a production DB or a test/dev DB? Is it a super-active DB or just
> a DB that has relatively normal activity against it but just
> hasn't had its log backed up for ages? I'll assume it is a
> production DB and it's just a normal OLTP database that hasn't had
> a log backup for a long time. Given those assumptions, if this
> was my DB, I would make these changes:
> * Do the optimisations once per week (this essentially
> rebuilds all your indexes, which is a fairly intense
> activity). I would schedule it for Saturday or Sunday
> night. And *don't automatically shrink your files.*
> Shrinking the data file will cause a large amount of log
> activity (because it's shuffling data pages all over the
> place, hence it needs to log all that change activity).
> * Integrity checks once a week on the weekend is good. Make
> sure you check the SQL errorlog each Monday (minimum) to see
> if the DBCC CHECKDB command (that's the command that SQL
> Server runs in this Integrity Check stage) logged any errors
> or warnings.
> * Full database backup once a day is good. Sometime out of
> peak hours (like 4am) is good. However, I would make the
> target of all DB backups a different physical disk to where
> the data & logs live. For example, if your data & logs live
> on the D: drive, then I would add another local HDD for the
> backup files (call it E: drive) and backup to that disk.
> This is because, if you have a HDD failure and lose your D:
> drive completely, unless it's part of a fault tolerant RAID
> set (like RAID 1 or RAID 5) then not only have you lost your
> data & logs but you've lost your backup of those DBs too!
> Better yet would be a backup disk volume that didn't live
> inside the server at all (to safe-guard against the whole
> server catching fire, etc.), like a disk on a SAN or NAS
> device, or even a DAS (like an external RAID cage).
> However, this can add considerable initial cost so it can
> often be hard to justify.
> * A transaction log backup once a day that occurs 1 hour after
> the last full DB backup will be next to useless. How much
> activity occurs in the 1 hour, from 4am to 5am, between the
> full DB backup and the transaction log backup? Remember,
> the transaction log records *changes to the database*, so to
> restore you'd restore the last full DB backup and any
> subsequent transaction log backups that occurred after the
> DB backup. If I were you I'd start with backing up the
> transaction log once per hour, eg. every hour starting at
> quarter to one [00:45am] (offset from 0:00 as often jobs get
> scheduled on the hour so this can be a busy time to do a
> backup), to the same directory you put the DB backup files.
> I'd also keep them for the same amount of time as the full
> DB backups. If you keep 3 days of DB backups, then keep 3
> days of transaction log backups (you can't use any
> transaction log backups that were taken before your oldest
> DB backup because you have to restore them AFTER you restore
> the appropriate DB backup).
> The points about auto-shrinking files & transaction log backups
> are probably the most important points here. Also, I'd adjust the
> settings of the DB itself a little. This is how I would set the
> data file & the log file:
> * I'd set your 8.4GB data file to be 9GB and autogrow by 1GB
> at a time (nice large, round numbers - neat). This is the
> command that would do that:
> ALTER DATABASE JLRXApp MODIFY FILE
> (NAME='JLTESTAPP_Data', SIZE=9216MB, MAXSIZE=UNLIMITED,
> FILEGROWTH=1024MB)
> * I'd set the 42.8GB log file to be 1GB and autogrow by 512MB
> at a time. This is the command that would do that:
> ALTER DATABASE JLRXApp MODIFY FILE
> (NAME='JLTESTAPP_Log', SIZE=1024MB, MAXSIZE=UNLIMITED,
> FILEGROWTH=512MB)
> However, you'll have to shrink your log before you can set it to
> the new size. There's not much point in backing up all 44GB -
> you'd be better off truncating the log and then doing a full DB
> backup:
> BACKUP LOG JLRXApp WITH NO_LOG
> CHECKPOINT
> DBCC SHRINKFILE('JLTESTAPP_Log')
> BACKUP DATABASE JLRXApp TO
> DISK='D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\JLRXApp_FixUp.bak' WITH INIT
> The database should now be backed up and the log shrunk, so now
> you can set the size of the transaction log file with the 2nd
> ALTER DATABASE statement above. After a couple weeks, once you're
> sure things are humming along nicely, you'll be able to delete
> that JLRXApp_FixUp.bak file (which was just a precaution really).
> All this should leave you with:
> - a 9GB database
> - a 1GB log
> - that get reindexed & checked on a regular basis (once a week on
> the weekends)
> - and get backed up on a nice regular schedule that shouldn't
> overtax your disk space.
> Good DB setup, good maintenance plan. Just make sure you test
> your backup files regularly (try restoring the backup files (both
> database backups *and* log backups) to another server (or at least
> another DB on the server) once a month or so) because if you can't
> restore from your backup files, then they are useless and you may
> as well not be doing them (always test your recovery strategy -
> recovery is the whole reason you do the backups in the first place).
> Remember, this is just a starting place. I'd expect you to
> monitor how things are going (disk space, DB size, index
> performance, etc.) and tweak things as appropriate (like more/less
> regular tlog backups or more regular optimisations or something
> like that). Don't forget to regularly test your backup files!
> Hope this helps.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Thief_ wrote:
>>Thanks Mike,
>>Your help has been super-human and much appreciated. This is the Maintenance
>>Plan I have created:
>>------
>>DATABASES
>> JLRXApp
>>SERVERS
>> (local)
>>OPTIMIZATIONS
>> Occurs every 1 week(s) on Monday, Tuesday, Wednesday, Thursday, Friday, at
>>3:00:00 AM.
>> Perform the following actions:
>> Reorganize data and index pages, changing the free space to 10 percent of
>>the original space.
>> Shrink database when it grows beyound 10000 MB. Leave 10 percent of data
>>space as free space.
>>INTEGRITY CHECKS
>> Occurs every 1 week(s) on Sunday, at 2:00:00 AM.
>> Perform the following actions:
>> Check database
>>COMPLETE BACKUP
>> Occurs every 1 week(s) on Monday, Tuesday, Wednesday, Thursday, Friday, at
>>4:00:00 AM.
>> Backup media: Disk
>> Store backup files in the default SQL Server Backup directory.
>> Delete backup files which are older than 3 Day(s).
>> Verify the backup after completion.
>> Create a subdirectory for each database, to store the backup files.
>>TRANSACTION LOG BACKUP
>> Occurs every 1 week(s) on Monday, Tuesday, Wednesday, Thursday, Friday, at
>>5:00:00 AM.
>> Backup media: Disk
>> Store backup files in the default SQL Server Backup directory.
>> Delete backup files which are older than 4 Day(s).
>> Verify the backup after completion.
>> Create a subdirectory for each database, to store the backup files.
>>Reports will be generated and stored in directory: d:\d:\Program
>>Files\Microsoft SQL Server\MSSQL\BACKUP\Log
>>Operator 'SQL_Backup_Notify' will be e-mailed.
>>------
>>Here's my DB info:
>>JLTESTAPP_Data 1 d:\Program Files\Microsoft
>>SQLServer\MSSQL\data\JLRXApp.MDF PRIMARY 8843520 KB
>>Unlimited 10% data only
>>JLTESTAPP_Log 2 d:\Program Files\Microsoft SQL
>>Server\MSSQL\data\JLRXApp_Log.LDF NULL 44843200 KB
>>Unlimited 10% log only
>>
>>I hope I have setup the MP properly. I have set it up to shrink the DB
>>automatically- I hope this is OK?!
>>Regards
>>Thief_
>>
>>"Thief_" <thief_@.hotmail.com> wrote in message
>>news:OviKzNnvFHA.3688@.tk2msftngp13.phx.gbl...
>>
>>A 3rd party company told us that we need to truncate our SQL Server
>>Transaction logs because the server's hard disk is getting full. The hard
>>disk is losing around 10 GB a day. They also told us to change the SQL
>>Server Backup type from Complex to Simple.
>>How do I truncate the log files and how do I change the backup type?
>>Regards
>>Thief_
>>
>>
>>
--050801080804090309010007
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Shrinking the data file is time consuming and I/O intensive
because SQL server will actually be shuffling pages around (hence the
need to log those activities in the transaction log). But shrinking transaction
log files is very quick & easy because it's just truncating the
inactive portion of the log (not actually moving pages around). So
it's fairly safe to shrink transaction log files in business hours
(given the normal caveats around shrinking files & autogrowing
files). 
 
However, if the active portion of the log is at the end of the file,
then a DBCC SHRINKFILE operation won't achieve much because it can only
truncate back to the end of the active portion (so the log file may
consist of gigabytes of empty space at the beginning of the file
followed by a few megabytes of real transactions at the end of the
file). The reason behind this is that transaction logs are divided up
into virtual log files, which are cyclical - when you reach the end of
the physical file, SQL Server will start writing records again from the
beginning of the physical file until it comes full circle back to where
it started (at that point it must increase the size of the physical
file again). So when you truncate a transaction log, usually by doing
a BACKUP LOG, all the transactions in the log are backed up and
deleted, leaving a largely empty log file. SQL Server doesn't start
writing again from the beginning of the file, it just keeps going from
where it left off - hence the circular/cyclical aspect of the log.
This means that sometimes you can get huge amounts of empty space at
the start of the file and just a few transactions at the end of the
file and that means SQL Server can only truncate the very end of the
physical file (if anything). So your 44GB log file may only shrink
down to 43GB for example. 
 
This was the reason for doing a BACKUP LOG and a CHECKPOINT, in my
previous post, before actually doing the DBCC SHRINKFILE, just to make
sure the end of the physical log file has been cleared of active
transactions and the DBCC SHRINKFILE will actually achieve something. 
 
The structure/behaviour of the transaction log is actually fairly
complicated - this explanation is really just an executive
summary/paraphrase. If you want to read up on it, SQL Books Online has
some excellent info on the transaction log (like the <a
href="http://links.10026.com/?link=Transaction">http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_876t.asp">Transaction
Log Architecture</a> section). For even more I'd recommend <a
href="http://links.10026.com/?link=Inside">http://www.amazon.com/exec/obidos/tg/detail/-/0735609985/qid=1127709307/sr=8-1/ref=pd_bbs_1/104-8387021-1711117?v=glance&s=books&n=507846">Inside
SQL Server 2000 by Kalen Delaney</a>, chapter 5 (Databases &
Database Files) - particularly pp.182-186 (dealing with the transaction
log structure & changing the log size). An essential tome for
every professional DBA's bookshelf. 
 
So, you should be able to shrink the transaction log very quickly &
easily with very little impact to the end users, but you should ditch
any transactions currently in there (with BACKUP LOG & CHECKPOINT)
just so the shrink operation shrinks right back to the near the
beginning of the file (so you may want to do that after hours so you
can do a full database backup immediately after the shrink without
impacting your users). Of course, you can do the whole thing during
business hours, users will still be able to work, but the performance
of the SQL server will be sluggish while you are doing the BACKUP
DATABASE operation (depending on your hardware). 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Thief_ wrote:
<blockquote cite="mid%23Xby0AkwFHA.1504@.TK2MSFTNGP10.phx.gbl"
type="cite">
<title></title>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<meta content="MSHTML 6.00.2900.2722" name="GENERATOR">
<style></style>
<div>Mike,</div>
<div> </div>
<div>WOW! The time you've spent is
recorded in the back of my mind- always. The info you have supplied is
super and will become part of my knowledge-base for ever. As for your
questions, the DBs JLRXAPP & JLRXSYS are our production DBs
(although the difference b/w the APP & SYS DBs is unknown to me at
the moment but I've asked our Solomon support company for details on
each) and our test DBs are JLRXTestApp & JLRXTestSys.</div>
<div> </div>
<div>I feel that the shrinking of the DBs
will be a time-crunching effort so I will perform it later tonight when
everyone is off the system.</div>
<div> </div>
<div>Regards</div>
<div> </div>
<div>Thief_</div>
<div> </div>
<blockquote
style="border-left: 2px solid rgb(0, 0, 0); padding-right: 0px; padding-left: 5px; margin-left: 5px; margin-right: 0px;"
dir="ltr">
<div>"Mike Hodgson" <<a
href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">mike.hodgson@.mallesons.nospam.com</a>>
wrote in message <a href="http://links.10026.com/?link=news:urrw9VBwFHA.1148@.TK2MSFTNGP11.phx.gbl">news:urrw9VBwFHA.1148@.TK2MSFTNGP11.phx.gbl</a>...</div>
<tt>OK, a number of points...but firstly a couple questions. Is
this a production DB or a test/dev DB? Is it a super-active DB or just
a DB that has relatively normal activity against it but just hasn't had
its log backed up for ages? I'll assume it is a production DB and it's
just a normal OLTP database that hasn't had a log backup for a long
time. Given those assumptions, if this was my DB, I would make these
changes: 
</tt>
<ul>
<li><tt>Do the optimisations once per week (this essentially
rebuilds all your indexes, which is a fairly intense activity). I
would schedule it for Saturday or Sunday night. And don't
automatically shrink your files. Shrinking the data file will
cause a large amount of log activity (because it's shuffling data pages
all over the place, hence it needs to log all that change activity).</tt>
</li>
<li><tt>Integrity checks once a week on the weekend is good.
Make sure you check the SQL errorlog each Monday (minimum) to see if
the DBCC CHECKDB command (that's the command that SQL Server runs in
this Integrity Check stage) logged any errors or warnings.</tt> </li>
<li><tt>Full database backup once a day is good. Sometime out of
peak hours (like 4am) is good. However, I would make the target of all
DB backups a different physical disk to where the data & logs
live. For example, if your data & logs live on the D: drive, then
I would add another local HDD for the backup files (call it E: drive)
and backup to that disk. This is because, if you have a HDD failure
and lose your D: drive completely, unless it's part of a fault tolerant
RAID set (like RAID 1 or RAID 5) then not only have you lost your data
& logs but you've lost your backup of those DBs too! Better yet
would be a backup disk volume that didn't live inside the server at all
(to safe-guard against the whole server catching fire, etc.), like a
disk on a SAN or NAS device, or even a DAS (like an external RAID
cage). However, this can add considerable initial cost so it can often
be hard to justify.</tt> </li>
<li><tt>A transaction log backup once a day that occurs 1 hour
after the last full DB backup will be next to useless. How much
activity occurs in the 1 hour, from 4am to 5am, between the full DB
backup and the transaction log backup? Remember, the transaction log
records changes to the database, so to restore you'd restore
the last full DB backup and any subsequent transaction log backups that
occurred after the DB backup. If I were you I'd start with backing up
the transaction log once per hour, eg. every hour starting at quarter
to one [00:45am] (offset from 0:00 as often jobs get scheduled on the
hour so this can be a busy time to do a backup), to the same directory
you put the DB backup files. I'd also keep them for the same amount of
time as the full DB backups. If you keep 3 days of DB backups, then
keep 3 days of transaction log backups (you can't use any transaction
log backups that were taken before your oldest DB backup because you
have to restore them AFTER you restore the appropriate DB backup).</tt>
</li>
</ul>
<tt>The points about auto-shrinking files & transaction log
backups are probably the most important points here. Also, I'd adjust
the settings of the DB itself a little. This is how I would set the
data file & the log file: 
</tt>
<ul>
<li><tt>I'd set your 8.4GB data file to be 9GB and autogrow by
1GB at a time (nice large, round numbers - neat). This is the command
that would do that:</tt> </li>
</ul>
<blockquote><tt>ALTER DATABASE JLRXApp MODIFY FILE 
(NAME='JLTESTAPP_Data</tt><tt>', SIZE=9216MB, MAXSIZE=UNLIMITED,
FILEGROWTH=1024MB) 
</tt></blockquote>
<ul>
<li><tt>I'd set the 42.8GB log file to be 1GB and autogrow by
512MB at a time. This is the command that would do that:</tt> </li>
</ul>
<blockquote><tt>ALTER DATABASE JLRXApp MODIFY FILE 
(NAME='JLTESTAPP_Log', SIZE=1024MB, MAXSIZE=UNLIMITED,
FILEGROWTH=512MB) 
</tt></blockquote>
<tt>However, you'll have to shrink your log before you can set it
to the new size. There's not much point in backing up all 44GB - you'd
be better off truncating the log and then doing a full DB backup: 
</tt>
<blockquote><tt>BACKUP LOG JLRXApp WITH NO_LOG 
CHECKPOINT 
DBCC SHRINKFILE('JLTESTAPP_Log') 
BACKUP DATABASE JLRXApp TO 
DISK='D:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\JLRXApp_FixUp.bak' WITH INIT 
</tt></blockquote>
<tt>The database should now be backed up and the log shrunk, so now
you can set the size of the transaction log file with the 2nd ALTER
DATABASE statement above. After a couple weeks, once you're sure
things are humming along nicely, you'll be able to delete that </tt><tt>JLRXApp_FixUp.bak
file (which was just a precaution really). 
 
All this should leave you with: 
- a 9GB database 
- a 1GB log 
- that get reindexed & checked on a regular basis (once a week on
the weekends) 
- and get backed up on a nice regular schedule that shouldn't overtax
your disk space. 
 
Good DB setup, good maintenance plan. Just make sure you test your
backup files regularly (try restoring the backup files (both database
backups and log backups) to another server (or at least another
DB on the server) once a month or so) because if you can't restore from
your backup files, then they are useless and you may as well not be
doing them (always test your recovery strategy - recovery is the whole
reason you do the backups in the first place). 
 
Remember, this is just a starting place. I'd expect you to monitor how
things are going (disk space, DB size, index performance, etc.) and
tweak things as appropriate (like more/less regular tlog backups or
more regular optimisations or something like that). Don't forget to
regularly test your backup files! 
 
Hope this helps. 
</tt>
<div class="moz-signature">
-- 
mike
hodgson 
blog: <a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Thief_ wrote:
<blockquote cite="mid%23KJJ4S$vFHA.2008@.TK2MSFTNGP10.phx.gbl"
type="cite">
<pre wrap="">Thanks Mike,
Your help has been super-human and much appreciated. This is the Maintenance
Plan I have created:
------
DATABASES
JLRXApp
SERVERS
(local)
OPTIMIZATIONS
Occurs every 1 week(s) on Monday, Tuesday, Wednesday, Thursday, Friday, at
3:00:00 AM.
Perform the following actions:
Reorganize data and index pages, changing the free space to 10 percent of
the original space.
Shrink database when it grows beyound 10000 MB. Leave 10 percent of data
space as free space.
INTEGRITY CHECKS
Occurs every 1 week(s) on Sunday, at 2:00:00 AM.
Perform the following actions:
Check database
COMPLETE BACKUP
Occurs every 1 week(s) on Monday, Tuesday, Wednesday, Thursday, Friday, at
4:00:00 AM.
Backup media: Disk
Store backup files in the default SQL Server Backup directory.
Delete backup files which are older than 3 Day(s).
Verify the backup after completion.
Create a subdirectory for each database, to store the backup files.
TRANSACTION LOG BACKUP
Occurs every 1 week(s) on Monday, Tuesday, Wednesday, Thursday, Friday, at
5:00:00 AM.
Backup media: Disk
Store backup files in the default SQL Server Backup directory.
Delete backup files which are older than 4 Day(s).
Verify the backup after completion.
Create a subdirectory for each database, to store the backup files.
Reports will be generated and stored in directory: d:\d:\Program
Files\Microsoft SQL Server\MSSQL\BACKUP\Log
Operator 'SQL_Backup_Notify' will be e-mailed.
------
Here's my DB info:
JLTESTAPP_Data 1 d:\Program Files\Microsoft
SQLServer\MSSQL\data\JLRXApp.MDF PRIMARY 8843520 KB
Unlimited 10% data only
JLTESTAPP_Log 2 d:\Program Files\Microsoft SQL
Server\MSSQL\data\JLRXApp_Log.LDF NULL 44843200 KB
Unlimited 10% log only
I hope I have setup the MP properly. I have set it up to shrink the DB
automatically- I hope this is OK?!
Regards
Thief_
"Thief_" <a class="moz-txt-link-rfc2396E"
href="http://links.10026.com/?link=mailto:thief_@.hotmail.com"><thief_@.hotmail.com></a> wrote in message
<a class="moz-txt-link-freetext"
href="http://links.10026.com/?link=news:OviKzNnvFHA.3688@.tk2msftngp13.phx.gbl">news:OviKzNnvFHA.3688@.tk2msftngp13.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">A 3rd party company told us that we need to truncate our SQL Server
Transaction logs because the server's hard disk is getting full. The hard
disk is losing around 10 GB a day. They also told us to change the SQL
Server Backup type from Complex to Simple.
How do I truncate the log files and how do I change the backup type?
Regards
Thief_
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
</body>
</html>
--000200050202090204030303--|||This is a multi-part message in MIME format.
--=_NextPart_000_000A_01C5C3B5.C4081C90
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Mike,
I found that the logical names for the JLRXApp are actually:
a.. JLRXApp_Data
b.. JLRXApp_Log
For some reason, the logical names returned by the "exec sp_helpdb =<DBName>" command, and thus the data in the sysfiles table, is =incorrect! I've noticed a few people on forums around the net have had =the same problem and sorted it out by renaming the logical filenames! I =feel this might be a bug in SQL Server 2000.
Anyhow, with your amazing help, me DB is now:
a.. JLRXApp_Data was 8GB and is now 9GB with Filegrowth of 1GB
b.. JLRXApp_Log was 50GB and is now 1GB with Filegrowth of 512MB
Thanks for your patience and mentoring- I'm now 50% more intuitive in =SQL Server!
Regards
Thief_
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message =news:%236fvIRywFHA.1412@.TK2MSFTNGP09.phx.gbl...
Something is amiss then because these results do not marry up with the =DB info you posted before:
> Here's my DB info:
> > JLTESTAPP_Data 1 d:\Program Files\Microsoft > SQLServer\MSSQL\data\JLRXApp.MDF PRIMARY 8843520 KB > Unlimited 10% data only
> JLTESTAPP_Log 2 d:\Program Files\Microsoft SQL > Server\MSSQL\data\JLRXApp_Log.LDF NULL 44843200 KB > Unlimited 10% log onlyAccording to this info, the logical filename =of the data file is "JLTESTAPP_Data" so the ALTER DATABASE for the data =file should have worked fine. Somewhere between you posting the above =info and then posting the below info the logical name of the data file =has changed from JLTESTAPP_Data to JLRXApp. Anyway, you figured that =one out.
With the DBCC SHRINKFILE('JLTESTAPP_Log'), 1) has the logical name of =the log file changed? and 2) are you executing the statement from within =the JLRXApp database? DBCC SHRINKFILE looks for the appropriate entry =in the dbo.sysfiles table in the current database. If the current =database is not JLRXApp then just run "USE JLRXApp" before doing the =DBCC SHRINKFILE. Also, make sure JLTESTAPP_Log is still the logical =name of the log file and that it hasn't changed (like the data file =did).
The dbo.sysfiles table you've dumped below matches what you posted =above but is not consistent with your initial ALTER DATABASE statement =that you used to change the size of the data file. You're not telling =us the whole story here.
--
mike hodgson
blog: http://sqlnerd.blogspot.com=20
Thief_ wrote: Mike,
The SQLs to alter the databases failed.
ALTER DATABASE JLRXApp MODIFY FILE
(NAME=3D'JLTESTAPP_Data', SIZE=3D9216MB, MAXSIZE=3DUNLIMITED, FILEGROWTH=3D1024MB)
I get this error:
MODIFY FILE failed. File 'JLTESTAPP_Data' does not exist.
I changed it to:
ALTER DATABASE JLRXApp MODIFY FILE
(NAME=3D'JLRXApp', SIZE=3D9216MB, MAXSIZE=3DUNLIMITED,
FILEGROWTH=3D1024MB);
And it seems to have worked. However, the SQL to shrink the log would =not work no matter what I tried:
BACKUP LOG JLRXApp WITH NO_LOG
CHECKPOINT
DBCC SHRINKFILE('JLTESTAPP_Log')
BACKUP DATABASE JLRXAPP TO
DISK=3D'E:\BACKUP\JLRXApp\JLRXAPP_LOG_TRUNCLOG.BAK' WITH INIT;
I get the error:
Could not locate file 'JLTESTAPP_Log' in sysfiles.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
The backup worked file (I now have a file called "JLRXAPP_LOG_TRUNCLOG.BAK"). What could be the problem? Heres the =SYSFILES table:
1 1 1179648 -1 131072 32770 0 JLTESTAPP_Data d:\Program Files\Microsoft SQL Server\MSSQL\data\JLRXApp.MDF
2 0 6165944 -1 10 1081410 0 JLTESTAPP_Log d:\Program Files\Microsoft SQL Server\MSSQL\data\JLRXApp_Log.LDF
?
Regards
Thief_
"Thief_" <thief_@.hotmail.com> wrote in message news:OviKzNnvFHA.3688@.tk2msftngp13.phx.gbl...
A 3rd party company told us that we need to truncate our SQL Server Transaction logs because the server's hard disk is getting full. The =hard disk is losing around 10 GB a day. They also told us to change the SQL Server Backup type from Complex to Simple.
How do I truncate the log files and how do I change the backup type?
Regards
Thief_

--=_NextPart_000_000A_01C5C3B5.C4081C90
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Mike,
I found that the logical names for the =JLRXApp are actually:
JLRXApp_Data
JLRXApp_Log
For some reason, the logical names =returned by the "exec sp_helpdb " command, =and thus the data in the sysfiles table, is incorrect! I've noticed a few people =on forums around the net have had the same problem and sorted it out by =renaming the logical filenames! I feel this might be a bug in SQL Server 2000.
Anyhow, with your amazing help, =me DB is now:
JLRXApp_Data was 8GB and is now =9GB with Filegrowth of 1GB
JLRXApp_Log was 50GB and is now 1GB =with Filegrowth of 512MB
Thanks for your patience and mentoring- I'm now 50% more intuitive =in SQL Server!
Regards
Thief_
"Mike Hodgson" wrote in message news:%236fvIRywFHA.=1412@.TK2MSFTNGP09.phx.gbl...Something is amiss then because these results do not marry up with the DB info =you posted before:
> Here's my DB info:
> > JLTESTAPP_Data 1 d:\Program Files\Microsoft > SQLServer\MSSQL\data\JLRXApp.MDF PRIMARY 8843520 KB > Unlimited 10% data only
> JLTESTAPP_Log 2 d:\Program Files\Microsoft SQL > Server\MSSQL\data\JLRXApp_Log.LDF NULL 44843200 KB > Unlimited 10% log onlyAccording to this =info, the logical filename of the data file is "JLTESTAPP_Data" so =the ALTER DATABASE for the data file should have worked fine. =Somewhere between you posting the above info and then posting the below info the =logical name of the data file has changed from JLTESTAPP_Data to =JLRXApp. Anyway, you figured that one out.With the DBCC SHRINKFILE('JLTESTAPP_Log'), 1) has the logical name of the log file =changed? and 2) are you executing the statement from within the JLRXApp =database? DBCC SHRINKFILE looks for the appropriate entry in the dbo.sysfiles =table in the current database. If the =current database is not JLRXApp then just run "USE =JLRXApp" before doing the DBCC SHRINKFILE. Also, make sure JLTESTAPP_Log =is still the logical name of the log file and that it hasn't changed (like the =data file did).The dbo.sysfiles table you've dumped below matches =what you posted above but is not consistent with your initial ALTER DATABASE =statement that you used to change the size of the data file. You're not =telling us the whole story here.
--mike =hodgsonblog: http://sqlnerd.blogspot.com Thief_ wrote: Mike,
The SQLs to alter the databases failed.
ALTER DATABASE JLRXApp MODIFY FILE
(NAME=3D'JLTESTAPP_Data', SIZE=3D9216MB, MAXSIZE=3DUNLIMITED, FILEGROWTH=3D1024MB)
I get this error:
MODIFY FILE failed. File 'JLTESTAPP_Data' does not exist.
I changed it to:
ALTER DATABASE JLRXApp MODIFY FILE
(NAME=3D'JLRXApp', SIZE=3D9216MB, MAXSIZE=3DUNLIMITED,
FILEGROWTH=3D1024MB);
And it seems to have worked. However, the SQL to shrink the log would =not work no matter what I tried:
BACKUP LOG JLRXApp WITH NO_LOG
CHECKPOINT
DBCC SHRINKFILE('JLTESTAPP_Log')
BACKUP DATABASE JLRXAPP TO
DISK=3D'E:\BACKUP\JLRXApp\JLRXAPP_LOG_TRUNCLOG.BAK' WITH INIT;
I get the error:
Could not locate file 'JLTESTAPP_Log' in sysfiles.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
The backup worked file (I now have a file called "JLRXAPP_LOG_TRUNCLOG.BAK"). What could be the problem? Heres the =SYSFILES table:
1 1 1179648 -1 131072 32770 0 JLTESTAPP_Data d:\Program Files\Microsoft SQL Server\MSSQL\data\JLRXApp.MDF
2 0 6165944 -1 10 1081410 0 JLTESTAPP_Log d:\Program Files\Microsoft SQL Server\MSSQL\data\JLRXApp_Log.LDF
?
Regards
Thief_
"Thief_" wrote =in message news:OviKzNnvFHA.3688=@.tk2msftngp13.phx.gbl...
A 3rd party company told us =that we need to truncate our SQL Server Transaction logs because the server's hard disk is getting full. The =hard disk is losing around 10 GB a day. They also told us to change the SQL Server Backup type from Complex to Simple.
How do I truncate the log files and how do I change the backup type?
Regards
Thief_
--=_NextPart_000_000A_01C5C3B5.C4081C90--|||This is a multi-part message in MIME format.
--040000030307020406040900
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
No prob, glad I could help.
Although the logical filename issue you're seeing I think is more likely
a confusion over DB names, logical filenames & physical filenames, all
of which can be different for any given database, than a bug in SQL
Server. The confusion may have arisen from the name changes that
implicitly occur when you restore a database from another DB backup
(like restoring JLRXApp from a backup of JLTESTAPP for example).
Anyway, I don't want to flog a dead horse - I'm glad you're all sorted.
(Just remember to do test restores from your backup files every month or
two to make sure 1) that they are valid backups and 2) that you're well
practised at recovery in case of a crisis - makes for a cool head when
the real thing happens...and it will, eventually.)
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Thief_ wrote:
> Mike,
> I found that the logical names for the JLRXApp are actually:
> * JLRXApp_Data
> * JLRXApp_Log
> For some reason, the logical names returned by the "exec sp_helpdb
> <DBName>" command, and thus the data in the sysfiles table, is
> incorrect! I've noticed a few people on forums around the net have had
> the same problem and sorted it out by renaming the logical filenames!
> I feel this might be a bug in SQL Server 2000.
> Anyhow, with your amazing help, me DB is now:
> * JLRXApp_Data was 8GB and is now 9GB with Filegrowth of 1GB
> * JLRXApp_Log was 50GB and is now 1GB with Filegrowth of 512MB
> Thanks for your patience and mentoring- I'm now 50% more intuitive in
> SQL Server!
> Regards
> Thief_
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in message
> news:%236fvIRywFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Something is amiss then because these results do not marry up with
> the DB info you posted before:
>> Here's my DB info:
>> JLTESTAPP_Data 1 d:\Program Files\Microsoft
>> SQLServer\MSSQL\data\JLRXApp.MDF PRIMARY 8843520 KB
>> Unlimited 10% data only
>> JLTESTAPP_Log 2 d:\Program Files\Microsoft SQL
>> Server\MSSQL\data\JLRXApp_Log.LDF NULL 44843200 KB
>> Unlimited 10% log only
> According to this info, the logical filename of the data file is
> "JLTESTAPP_Data" so the ALTER DATABASE for the data file should
> have worked fine. Somewhere between you posting the above info
> and then posting the below info the logical name of the data file
> has changed from JLTESTAPP_Data to JLRXApp. Anyway, you figured
> that one out.
> With the DBCC SHRINKFILE('JLTESTAPP_Log'), 1) has the logical name
> of the log file changed? and 2) are you executing the statement
> from within the JLRXApp database? DBCC SHRINKFILE looks for the
> appropriate entry in the dbo.sysfiles table in the *current
> database*. If the current database is not JLRXApp then just run
> "USE JLRXApp" before doing the DBCC SHRINKFILE. Also, make sure
> JLTESTAPP_Log is still the logical name of the log file and that
> it hasn't changed (like the data file did).
> The dbo.sysfiles table you've dumped below matches what you posted
> above but is not consistent with your initial ALTER DATABASE
> statement that you used to change the size of the data file.
> You're not telling us the whole story here.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Thief_ wrote:
>>Mike,
>>The SQLs to alter the databases failed.
>>ALTER DATABASE JLRXApp MODIFY FILE
>> (NAME='JLTESTAPP_Data', SIZE=9216MB, MAXSIZE=UNLIMITED,
>>FILEGROWTH=1024MB)
>>I get this error:
>>MODIFY FILE failed. File 'JLTESTAPP_Data' does not exist.
>>I changed it to:
>>ALTER DATABASE JLRXApp MODIFY FILE
>>(NAME='JLRXApp', SIZE=9216MB, MAXSIZE=UNLIMITED,
>>FILEGROWTH=1024MB);
>>And it seems to have worked. However, the SQL to shrink the log would not
>>work no matter what I tried:
>>BACKUP LOG JLRXApp WITH NO_LOG
>>CHECKPOINT
>>DBCC SHRINKFILE('JLTESTAPP_Log')
>>BACKUP DATABASE JLRXAPP TO
>>DISK='E:\BACKUP\JLRXApp\JLRXAPP_LOG_TRUNCLOG.BAK' WITH INIT;
>>I get the error:
>>Could not locate file 'JLTESTAPP_Log' in sysfiles.
>>DBCC execution completed. If DBCC printed error messages, contact your
>>system administrator.
>>The backup worked file (I now have a file called
>>"JLRXAPP_LOG_TRUNCLOG.BAK"). What could be the problem? Heres the SYSFILES
>>table:
>> 1 1 1179648 -1 131072 32770 0 JLTESTAPP_Data
>>d:\Program Files\Microsoft SQL Server\MSSQL\data\JLRXApp.MDF
>> 2 0 6165944 -1 10 1081410 0 JLTESTAPP_Log
>>d:\Program Files\Microsoft SQL Server\MSSQL\data\JLRXApp_Log.LDF
>>?
>>Regards
>>Thief_
>>
>>"Thief_" <thief_@.hotmail.com> wrote in message
>>news:OviKzNnvFHA.3688@.tk2msftngp13.phx.gbl...
>>
>>A 3rd party company told us that we need to truncate our SQL Server
>>Transaction logs because the server's hard disk is getting full. The hard
>>disk is losing around 10 GB a day. They also told us to change the SQL
>>Server Backup type from Complex to Simple.
>>How do I truncate the log files and how do I change the backup type?
>>Regards
>>Thief_
>>
>>
>>
--040000030307020406040900
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>No prob, glad I could help. 
 
Although the logical filename issue you're seeing I think is more
likely a confusion over DB names, logical filenames & physical
filenames, all of which can be different for any given database, than a
bug in SQL Server. The confusion may have arisen from the name changes
that implicitly occur when you restore a database from another DB
backup (like restoring JLRXApp from a backup of JLTESTAPP for example). 
 
Anyway, I don't want to flog a dead horse - I'm glad you're all
sorted. (Just remember to do test restores from your backup files
every month or two to make sure 1) that they are valid backups and 2)
that you're well practised at recovery in case of a crisis - makes for
a cool head when the real thing happens...and it will, eventually.) 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Thief_ wrote:
<blockquote cite="miduH210H2wFHA.3000@.TK2MSFTNGP12.phx.gbl" type="cite">
<title></title>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<meta content="MSHTML 6.00.2900.2722" name="GENERATOR">
<style></style>
<div>Mike,</div>
<div> </div>
<div>I found that the logical names for
the JLRXApp are actually:</div>
<ul>
<li>JLRXApp_Data</li>
<li>JLRXApp_Log</li>
</ul>
<div>For some reason, the logical names
returned by the "exec sp_helpdb
<DBName>"
command, and thus the data in the sysfiles table, is incorrect! I've
noticed a few people on forums around the net have had the same problem
and sorted it out by renaming the logical filenames! I feel this might
be a bug in SQL Server 2000.</div>
<div> </div>
<div>Anyhow, with your amazing
help, me DB is now:</div>
<ul>
<li>JLRXApp_Data was 8GB and is
now 9GB with Filegrowth of 1GB</li>
 <li>JLRXApp_Log
was 50GB and is now 1GB with Filegrowth of 512MB</li>

</ul>
<div>Thanks for your patience and
mentoring- I'm now 50% more intuitive in SQL Server!</div>
<div> </div>
<div>Regards</div>
<div> </div>
<div>Thief_</div>
<div> </div>
<blockquote
style="border-left: 2px solid rgb(0, 0, 0); padding-right: 0px; padding-left: 5px; margin-left: 5px; margin-right: 0px;"
dir="ltr">
<div>"Mike Hodgson" <<a
href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">mike.hodgson@.mallesons.nospam.com</a>>
wrote in message <a href="http://links.10026.com/?link=news:%236fvIRywFHA.1412@.TK2MSFTNGP09.phx.gbl">news:%236fvIRywFHA.1412@.TK2MSFTNGP09.phx.gbl</a>...</div>
<tt>Something is amiss then because these results do not marry up
with the DB info you posted before: 
</tt>
<blockquote>
<pre wrap="">> Here's my DB info:
> JLTESTAPP_Data 1 d:\Program Files\Microsoft
> SQLServer\MSSQL\data\JLRXApp.MDF PRIMARY 8843520 KB
> Unlimited 10% data only
> JLTESTAPP_Log 2 d:\Program Files\Microsoft SQL
> Server\MSSQL\data\JLRXApp_Log.LDF NULL 44843200 KB
> Unlimited 10% log only</pre>
</blockquote>
<tt>According to this info, the logical filename of the data file
is "JLTESTAPP_Data</tt><tt>" so the ALTER DATABASE for the data file
should have worked fine. Somewhere between you posting the above info
and then posting the below info the logical name of the data file has
changed from JLTESTAPP_Data to JLRXApp. Anyway, you figured that one
out. 
 
With the DBCC SHRINKFILE('JLTESTAPP_Log'), 1) has the logical name of
the log file changed? and 2) are you executing the statement from
within the JLRXApp database? DBCC SHRINKFILE looks for the appropriate
entry in the dbo.sysfiles table in the current database. If </tt><tt>the
current database </tt><tt>is not </tt><tt>JLRXApp then </tt><tt>just
run "USE JLRXApp" before doing the DBCC SHRINKFILE. Also, make sure
JLTESTAPP_Log is still the logical name of the log file and that it
hasn't changed (like the data file did). 
 
The dbo.sysfiles table you've dumped below matches what you posted
above but is not consistent with your initial ALTER DATABASE statement
that you used to change the size of the data file. You're not telling
us the whole story here. 
</tt>
<div class="moz-signature">
-- 
mike
hodgson 
blog: <a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Thief_ wrote:
<blockquote cite="midOk06OhpwFHA.3800@.TK2MSFTNGP11.phx.gbl"
type="cite">
<pre wrap="">Mike,
The SQLs to alter the databases failed.
ALTER DATABASE JLRXApp MODIFY FILE
(NAME='JLTESTAPP_Data', SIZE=9216MB, MAXSIZE=UNLIMITED,
FILEGROWTH=1024MB)
I get this error:
MODIFY FILE failed. File 'JLTESTAPP_Data' does not exist.
I changed it to:
ALTER DATABASE JLRXApp MODIFY FILE
(NAME='JLRXApp', SIZE=9216MB, MAXSIZE=UNLIMITED,
FILEGROWTH=1024MB);
And it seems to have worked. However, the SQL to shrink the log would not
work no matter what I tried:
BACKUP LOG JLRXApp WITH NO_LOG
CHECKPOINT
DBCC SHRINKFILE('JLTESTAPP_Log')
BACKUP DATABASE JLRXAPP TO
DISK='E:\BACKUP\JLRXApp\JLRXAPP_LOG_TRUNCLOG.BAK' WITH INIT;
I get the error:
Could not locate file 'JLTESTAPP_Log' in sysfiles.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
The backup worked file (I now have a file called
"JLRXAPP_LOG_TRUNCLOG.BAK"). What could be the problem? Heres the SYSFILES
table:
1 1 1179648 -1 131072 32770 0 JLTESTAPP_Data
d:\Program Files\Microsoft SQL Server\MSSQL\data\JLRXApp.MDF
2 0 6165944 -1 10 1081410 0 JLTESTAPP_Log
d:\Program Files\Microsoft SQL Server\MSSQL\data\JLRXApp_Log.LDF
?
Regards
Thief_
"Thief_" <a class="moz-txt-link-rfc2396E"
href="http://links.10026.com/?link=mailto:thief_@.hotmail.com"><thief_@.hotmail.com></a> wrote in message
<a class="moz-txt-link-freetext"
href="http://links.10026.com/?link=news:OviKzNnvFHA.3688@.tk2msftngp13.phx.gbl">news:OviKzNnvFHA.3688@.tk2msftngp13.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">A 3rd party company told us that we need to truncate our SQL Server
Transaction logs because the server's hard disk is getting full. The hard
disk is losing around 10 GB a day. They also told us to change the SQL
Server Backup type from Complex to Simple.
How do I truncate the log files and how do I change the backup type?
Regards
Thief_
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</blockquote>
</blockquote>
</body>
</html>
--040000030307020406040900--

Wednesday, March 28, 2012

No Fields in Page Header (and Footers)??

Wednesday, March 7, 2012

Newbie's Last Question

Newbie?: Can This Be Done?- If so please direct

Saturday, February 25, 2012

Newbie: XML as datasource

Newbie: working with expressions on tables

Newbie: which replication strategy should I use?

Newbie: what kind of "cube" is this?

Newbie: what kind of "cube" is this?

Newbie: Viewing KPIs thru MOSS

newbie: viewing data in SQL Server 7.0

newbie: viewing data in SQL Server 7.0

newbie: viewing data in SQL Server 7.0

newbie: value expression... uses an aggregate expression w/o a sco

Newbie: Use XMLA to connect to SQL Server

Newbie: Use XMLA to connect to SQL Server

Newbie: Use XMLA to connect to SQL Server

Newbie: Updating record with User information

Newbie: UPDATE a Table

newbie: TSQL command for number of rows ?

Newbie: Truncating transaction logs

Metadata Services

Blog Archive

About Me