Filling transaction log

Hi Guys,
I've a problem regarding transaction log, i have a databasse
which is to be accessed very rairaly inspite of that after few days it shows
warning as:
APPROACHING RESOURCE LIMIT
MSSQL Quota DB: ABCD_database has used 66 MB out of 80 MB limit
while i don't have control on my DB some other company takes
care of my DB operation like truncating transaction log etc. when i talked
with them they said that transaction log auto truncates several time in a day.
i don't know where the problem exists,
please help me
They should define "auto truncate". Auto shrink is different from backing
up the log or having the recovery model set to simple.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in message
news:BCCE0C43-64CC-4FF2-92A9-188B3346C6D4@.microsoft.com...
Hi Guys,
I've a problem regarding transaction log, i have a databasse
which is to be accessed very rairaly inspite of that after few days it shows
warning as:
APPROACHING RESOURCE LIMIT
MSSQL Quota DB: ABCD_database has used 66 MB out of 80 MB limit
while i don't have control on my DB some other company takes
care of my DB operation like truncating transaction log etc. when i talked
with them they said that transaction log auto truncates several time in a
day.
i don't know where the problem exists,
please help me
|||What database options are turned on? How much data is actually in the
database?
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in message
news:BCCE0C43-64CC-4FF2-92A9-188B3346C6D4@.microsoft.com...
> Hi Guys,
> I've a problem regarding transaction log, i have a databasse
> which is to be accessed very rairaly inspite of that after few days it
> shows
> warning as:
> APPROACHING RESOURCE LIMIT
> MSSQL Quota DB: ABCD_database has used 66 MB out of 80 MB limit
> while i don't have control on my DB some other company
> takes
> care of my DB operation like truncating transaction log etc. when i talked
> with them they said that transaction log auto truncates several time in a
> day.
> i don't know where the problem exists,
> please help me
>
sql

Filling SQLDataReader with stored procedure recordset

Hi All,

I'm hoping somebody can help me with this as it is driving me mad. I've created a stored procedure which returns Employee information recordset when the windows username is passed to it as a parameter. I want to then store this information in Session variables so content can be filtered depending on employee status, but when I execute my code no records are returned. I know for a fact that the stored procedure works because I bound it sqldatasource and displayed results from it in a Datalist and tested it in sql server. My code is as follows can anybody see any problems with it, in runs through fine with but when I try a read a field from the datareader in says there is no data to read.

Dim CurrentUserAs String, Pos1As Int16, EmployeeIdAs Int32Dim cnAs New System.Data.SqlClient.SqlConnectionDim paramAs New System.Data.SqlClient.SqlParameterDim readerAs System.Data.SqlClient.SqlDataReaderDim cmdAs New System.Data.SqlClient.SqlCommand CurrentUser =CStr(User.Identity.Name) Pos1 = InStr(CurrentUser,"\") + 1 CurrentUser = Mid(CurrentUser, Pos1) Session("User") = CurrentUser Session("CID") =Nothing cn.ConnectionString ="Data Source=LAPTOP-4\SQLEXPRESS;Initial Catalog=SCMdb;Integrated Security=True" cn.Open() cmd.Connection = cn cmd.CommandText ="CurrentUser" cmd.CommandType = CommandType.StoredProcedure param = cmd.CreateParameter param.ParameterName ="@.UserName" param.SqlDbType = SqlDbType.VarChar param.Value = CurrentUser cmd.Parameters.Add(param) reader = cmd.ExecuteReader(CommandBehavior.CloseConnection) EmployeeId = reader.Item("EmployeeID") reader.Close()

Any help would be much appricated this is driving me mad.

Thank You

Shaft

To read items from reader,

reader = cmd.ExecuteReader (CommandBehavior.CloseConnection):

while (reader.Read())
{
int _employeeId = (int) reader["EmployeeID"];
}

Thanks

|||

Hi there,

If you are just reading a single value of the stored procedure I will advice you to use OUTPUT parameters to achieve this.Datareader will also do the job, but you leave yourself more vulnerable with connection not closing properly and also with overhead of creating a reader object.Just a thought.. what you are doing is also correct and will work

|||

e_screw:

To read items from reader,

reader = cmd.ExecuteReader (CommandBehavior.CloseConnection):

while (reader.Read())
{
int _employeeId = (int) reader["EmployeeID"];
}

Thanks

I've tried that all ready but it still says there is no data.

Any other ideas anyone?

|||

The actual error is "Invalid attempt to read when no data is present." so I'm thinking the Command Object hasn't pulled any data which leads me think there is a problem with the way I've declared the parameter because it is running the stored procedure just not retrieving any data.

|||

Sorry may mistake it did work I'd just forgot to comment out the statment that was wrong.

Here's the code that e-screw posted but converted to vb .net as I needed it

Do While reader.Read EmployeeId = reader("EmployeeID")Loop

Cheers E-Screw

fill factor question

Ive always had a hard time getting my head into this topic so please bear
with me. Say Ive got a huge table that gets lots of Inserts. But to do these
Inserts, it needs to do lots of Selects.
#Table3 is the table in question here:
create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
create table #Table2(T2C1 int, T2C2 char(10))
create table #Table3(T3C1 int, T3C2 char(10), T3C3 char(10), T3C4 char(10))
insert into #Table1 (T1C2) values ('T1C2')
insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
insert into #Table3 (T3C1,T3C2,T3C3,T3C4)
select t1.T1C1,T1C2,t2.T2C2, 'test'
from #Table1 t1
inner join #Table2 t2 on t1.t1c1 = t2.t2c1
So, would a table like this call for a lower Fill Factor as it will have
lots of new Inserts. Or would a table like this call for a higher Fill
Factor to increase the Select speed to do those Inserts?
TIA, ChrisR.
What column(s) is the clustered index on?
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:eRXkg55aFHA.2756@.tk2msftngp13.phx.gbl...
> Ive always had a hard time getting my head into this topic so please bear
> with me. Say Ive got a huge table that gets lots of Inserts. But to do
> these Inserts, it needs to do lots of Selects.
> #Table3 is the table in question here:
> create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
> create table #Table2(T2C1 int, T2C2 char(10))
> create table #Table3(T3C1 int, T3C2 char(10), T3C3 char(10), T3C4
> char(10))
> insert into #Table1 (T1C2) values ('T1C2')
> insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
> insert into #Table3 (T3C1,T3C2,T3C3,T3C4)
> select t1.T1C1,T1C2,t2.T2C2, 'test'
> from #Table1 t1
> inner join #Table2 t2 on t1.t1c1 = t2.t2c1
>
> So, would a table like this call for a lower Fill Factor as it will have
> lots of new Inserts. Or would a table like this call for a higher Fill
> Factor to increase the Select speed to do those Inserts?
> TIA, ChrisR.
>
|||Lets say C1 for all 3 tables.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OOZsb$5aFHA.2076@.TK2MSFTNGP15.phx.gbl...
> What column(s) is the clustered index on?
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:eRXkg55aFHA.2756@.tk2msftngp13.phx.gbl...
>
|||If the inserts come in ordered, a higher fillfactor would be ideal. If
not, a high one will still be good as the index will balance out very
quickly. Maximizing the rows per page will greatly help the selection
of rows of data and be fewer I/Os.
|||If all you ever did was inserts or deletes (no Updates to variable length
columns) a 100% fill factor would work well on an Identity column. That way
all new rows simply get appended to the end of the current page and do not
cause pagesplits. If you update with larger rows you need to account for
that extra space on each page. Fill factors are always a trade off between
leaving enough room for inserts and updates vs. keeping it full enough to
not waste memory or disk space by having lots of empty space on each page.
There is no one correct answer as to what it should be. You must determine
that based on the activity of each table and the frequency of the
reindexing. If you reindex each night you can usually get by with a
relatively high fill factor. If it's once a week it probably would be
lower. You need to monitor the fragmentation in between reindexing to see
how fragmented it gets and how many page splits happen. If it is heavily
fragmented you may benefit from a lower fill factor. If not make it higher.
What those values should be we can not say. A good place to start is
usually around 80 or 90 on an index that is frequently Inserted, Updated and
Deleted. Then go from there.
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:e5WhWP6aFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Lets say C1 for all 3 tables.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OOZsb$5aFHA.2076@.TK2MSFTNGP15.phx.gbl...
>
|||Since this was a question regarding the appropriate fill factor, which you
answered quite nicely, I won't beat up on you too badly.
First of all, IDENTITIES are NOT good candidates for the Clustered Index as
is espoused so often.
Second, Identity Clustered Indexes do create page splits, quite often, at
the node levels of the indexes.
Third, Identities are usually only a surrogate key. All tables, wich
represent enities, must at least have a business key in order to use a
surrogate as a proxy; otherwise, you are just making things up with no clear
definition. That key should have a Unique Constraint defined whenever the
Primary Key is placed on the Identity.
Finally, for you select statement that you are using for your Insert
statement, all attributes used within a Join Condition and any highly
selectable columns that participate in a Where Clause should have and index
defined on them.
Sincerely,
Anthony Thomas

"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OJ53NT9aFHA.2900@.TK2MSFTNGP15.phx.gbl...
If all you ever did was inserts or deletes (no Updates to variable length
columns) a 100% fill factor would work well on an Identity column. That way
all new rows simply get appended to the end of the current page and do not
cause pagesplits. If you update with larger rows you need to account for
that extra space on each page. Fill factors are always a trade off between
leaving enough room for inserts and updates vs. keeping it full enough to
not waste memory or disk space by having lots of empty space on each page.
There is no one correct answer as to what it should be. You must determine
that based on the activity of each table and the frequency of the
reindexing. If you reindex each night you can usually get by with a
relatively high fill factor. If it's once a week it probably would be
lower. You need to monitor the fragmentation in between reindexing to see
how fragmented it gets and how many page splits happen. If it is heavily
fragmented you may benefit from a lower fill factor. If not make it higher.
What those values should be we can not say. A good place to start is
usually around 80 or 90 on an index that is frequently Inserted, Updated and
Deleted. Then go from there.
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:e5WhWP6aFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Lets say C1 for all 3 tables.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OOZsb$5aFHA.2076@.TK2MSFTNGP15.phx.gbl...
>
sql

fill factor question

Ive always had a hard time getting my head into this topic so please bear
with me. Say Ive got a huge table that gets lots of Inserts. But to do these
Inserts, it needs to do lots of Selects.
#Table3 is the table in question here:
create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
create table #Table2(T2C1 int, T2C2 char(10))
create table #Table3(T3C1 int, T3C2 char(10), T3C3 char(10), T3C4 char(10))
insert into #Table1 (T1C2) values ('T1C2')
insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
insert into #Table3 (T3C1,T3C2,T3C3,T3C4)
select t1.T1C1,T1C2,t2.T2C2, 'test'
from #Table1 t1
inner join #Table2 t2 on t1.t1c1 = t2.t2c1
So, would a table like this call for a lower Fill Factor as it will have
lots of new Inserts. Or would a table like this call for a higher Fill
Factor to increase the Select speed to do those Inserts?
TIA, ChrisR.What column(s) is the clustered index on?
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:eRXkg55aFHA.2756@.tk2msftngp13.phx.gbl...
> Ive always had a hard time getting my head into this topic so please bear
> with me. Say Ive got a huge table that gets lots of Inserts. But to do
> these Inserts, it needs to do lots of Selects.
> #Table3 is the table in question here:
> create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
> create table #Table2(T2C1 int, T2C2 char(10))
> create table #Table3(T3C1 int, T3C2 char(10), T3C3 char(10), T3C4
> char(10))
> insert into #Table1 (T1C2) values ('T1C2')
> insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
> insert into #Table3 (T3C1,T3C2,T3C3,T3C4)
> select t1.T1C1,T1C2,t2.T2C2, 'test'
> from #Table1 t1
> inner join #Table2 t2 on t1.t1c1 = t2.t2c1
>
> So, would a table like this call for a lower Fill Factor as it will have
> lots of new Inserts. Or would a table like this call for a higher Fill
> Factor to increase the Select speed to do those Inserts?
> TIA, ChrisR.
>|||Lets say C1 for all 3 tables.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OOZsb$5aFHA.2076@.TK2MSFTNGP15.phx.gbl...
> What column(s) is the clustered index on?
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:eRXkg55aFHA.2756@.tk2msftngp13.phx.gbl...
>|||If the inserts come in ordered, a higher fillfactor would be ideal. If
not, a high one will still be good as the index will balance out very
quickly. Maximizing the rows per page will greatly help the selection
of rows of data and be fewer I/Os.|||If all you ever did was inserts or deletes (no Updates to variable length
columns) a 100% fill factor would work well on an Identity column. That way
all new rows simply get appended to the end of the current page and do not
cause pagesplits. If you update with larger rows you need to account for
that extra space on each page. Fill factors are always a trade off between
leaving enough room for inserts and updates vs. keeping it full enough to
not waste memory or disk space by having lots of empty space on each page.
There is no one correct answer as to what it should be. You must determine
that based on the activity of each table and the frequency of the
reindexing. If you reindex each night you can usually get by with a
relatively high fill factor. If it's once a week it probably would be
lower. You need to monitor the fragmentation in between reindexing to see
how fragmented it gets and how many page splits happen. If it is heavily
fragmented you may benefit from a lower fill factor. If not make it higher.
What those values should be we can not say. A good place to start is
usually around 80 or 90 on an index that is frequently Inserted, Updated and
Deleted. Then go from there.
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:e5WhWP6aFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Lets say C1 for all 3 tables.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OOZsb$5aFHA.2076@.TK2MSFTNGP15.phx.gbl...
>|||Since this was a question regarding the appropriate fill factor, which you
answered quite nicely, I won't beat up on you too badly.
First of all, IDENTITIES are NOT good candidates for the Clustered Index as
is espoused so often.
Second, Identity Clustered Indexes do create page splits, quite often, at
the node levels of the indexes.
Third, Identities are usually only a surrogate key. All tables, wich
represent enities, must at least have a business key in order to use a
surrogate as a proxy; otherwise, you are just making things up with no clear
definition. That key should have a Unique Constraint defined whenever the
Primary Key is placed on the Identity.
Finally, for you select statement that you are using for your Insert
statement, all attributes used within a Join Condition and any highly
selectable columns that participate in a Where Clause should have and index
defined on them.
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OJ53NT9aFHA.2900@.TK2MSFTNGP15.phx.gbl...
If all you ever did was inserts or deletes (no Updates to variable length
columns) a 100% fill factor would work well on an Identity column. That way
all new rows simply get appended to the end of the current page and do not
cause pagesplits. If you update with larger rows you need to account for
that extra space on each page. Fill factors are always a trade off between
leaving enough room for inserts and updates vs. keeping it full enough to
not waste memory or disk space by having lots of empty space on each page.
There is no one correct answer as to what it should be. You must determine
that based on the activity of each table and the frequency of the
reindexing. If you reindex each night you can usually get by with a
relatively high fill factor. If it's once a week it probably would be
lower. You need to monitor the fragmentation in between reindexing to see
how fragmented it gets and how many page splits happen. If it is heavily
fragmented you may benefit from a lower fill factor. If not make it higher.
What those values should be we can not say. A good place to start is
usually around 80 or 90 on an index that is frequently Inserted, Updated and
Deleted. Then go from there.
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:e5WhWP6aFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Lets say C1 for all 3 tables.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OOZsb$5aFHA.2076@.TK2MSFTNGP15.phx.gbl...
>

fill factor question

Ive always had a hard time getting my head into this topic so please bear
with me. Say Ive got a huge table that gets lots of Inserts. But to do these
Inserts, it needs to do lots of Selects.
#Table3 is the table in question here:
create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
create table #Table2(T2C1 int, T2C2 char(10))
create table #Table3(T3C1 int, T3C2 char(10), T3C3 char(10), T3C4 char(10))
insert into #Table1 (T1C2) values ('T1C2')
insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
insert into #Table3 (T3C1,T3C2,T3C3,T3C4)
select t1.T1C1,T1C2,t2.T2C2, 'test'
from #Table1 t1
inner join #Table2 t2 on t1.t1c1 = t2.t2c1
So, would a table like this call for a lower Fill Factor as it will have
lots of new Inserts. Or would a table like this call for a higher Fill
Factor to increase the Select speed to do those Inserts?
TIA, ChrisR.What column(s) is the clustered index on?
--
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:eRXkg55aFHA.2756@.tk2msftngp13.phx.gbl...
> Ive always had a hard time getting my head into this topic so please bear
> with me. Say Ive got a huge table that gets lots of Inserts. But to do
> these Inserts, it needs to do lots of Selects.
> #Table3 is the table in question here:
> create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
> create table #Table2(T2C1 int, T2C2 char(10))
> create table #Table3(T3C1 int, T3C2 char(10), T3C3 char(10), T3C4
> char(10))
> insert into #Table1 (T1C2) values ('T1C2')
> insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
> insert into #Table3 (T3C1,T3C2,T3C3,T3C4)
> select t1.T1C1,T1C2,t2.T2C2, 'test'
> from #Table1 t1
> inner join #Table2 t2 on t1.t1c1 = t2.t2c1
>
> So, would a table like this call for a lower Fill Factor as it will have
> lots of new Inserts. Or would a table like this call for a higher Fill
> Factor to increase the Select speed to do those Inserts?
> TIA, ChrisR.
>|||Lets say C1 for all 3 tables.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OOZsb$5aFHA.2076@.TK2MSFTNGP15.phx.gbl...
> What column(s) is the clustered index on?
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:eRXkg55aFHA.2756@.tk2msftngp13.phx.gbl...
>> Ive always had a hard time getting my head into this topic so please bear
>> with me. Say Ive got a huge table that gets lots of Inserts. But to do
>> these Inserts, it needs to do lots of Selects.
>> #Table3 is the table in question here:
>> create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
>> create table #Table2(T2C1 int, T2C2 char(10))
>> create table #Table3(T3C1 int, T3C2 char(10), T3C3 char(10), T3C4
>> char(10))
>> insert into #Table1 (T1C2) values ('T1C2')
>> insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
>> insert into #Table3 (T3C1,T3C2,T3C3,T3C4)
>> select t1.T1C1,T1C2,t2.T2C2, 'test'
>> from #Table1 t1
>> inner join #Table2 t2 on t1.t1c1 = t2.t2c1
>>
>> So, would a table like this call for a lower Fill Factor as it will have
>> lots of new Inserts. Or would a table like this call for a higher Fill
>> Factor to increase the Select speed to do those Inserts?
>> TIA, ChrisR.
>|||If the inserts come in ordered, a higher fillfactor would be ideal. If
not, a high one will still be good as the index will balance out very
quickly. Maximizing the rows per page will greatly help the selection
of rows of data and be fewer I/Os.|||If all you ever did was inserts or deletes (no Updates to variable length
columns) a 100% fill factor would work well on an Identity column. That way
all new rows simply get appended to the end of the current page and do not
cause pagesplits. If you update with larger rows you need to account for
that extra space on each page. Fill factors are always a trade off between
leaving enough room for inserts and updates vs. keeping it full enough to
not waste memory or disk space by having lots of empty space on each page.
There is no one correct answer as to what it should be. You must determine
that based on the activity of each table and the frequency of the
reindexing. If you reindex each night you can usually get by with a
relatively high fill factor. If it's once a week it probably would be
lower. You need to monitor the fragmentation in between reindexing to see
how fragmented it gets and how many page splits happen. If it is heavily
fragmented you may benefit from a lower fill factor. If not make it higher.
What those values should be we can not say. A good place to start is
usually around 80 or 90 on an index that is frequently Inserted, Updated and
Deleted. Then go from there.
--
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:e5WhWP6aFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Lets say C1 for all 3 tables.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OOZsb$5aFHA.2076@.TK2MSFTNGP15.phx.gbl...
>> What column(s) is the clustered index on?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:eRXkg55aFHA.2756@.tk2msftngp13.phx.gbl...
>> Ive always had a hard time getting my head into this topic so please
>> bear with me. Say Ive got a huge table that gets lots of Inserts. But to
>> do these Inserts, it needs to do lots of Selects.
>> #Table3 is the table in question here:
>> create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
>> create table #Table2(T2C1 int, T2C2 char(10))
>> create table #Table3(T3C1 int, T3C2 char(10), T3C3 char(10), T3C4
>> char(10))
>> insert into #Table1 (T1C2) values ('T1C2')
>> insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
>> insert into #Table3 (T3C1,T3C2,T3C3,T3C4)
>> select t1.T1C1,T1C2,t2.T2C2, 'test'
>> from #Table1 t1
>> inner join #Table2 t2 on t1.t1c1 = t2.t2c1
>>
>> So, would a table like this call for a lower Fill Factor as it will have
>> lots of new Inserts. Or would a table like this call for a higher Fill
>> Factor to increase the Select speed to do those Inserts?
>> TIA, ChrisR.
>>
>|||Since this was a question regarding the appropriate fill factor, which you
answered quite nicely, I won't beat up on you too badly.
First of all, IDENTITIES are NOT good candidates for the Clustered Index as
is espoused so often.
Second, Identity Clustered Indexes do create page splits, quite often, at
the node levels of the indexes.
Third, Identities are usually only a surrogate key. All tables, wich
represent enities, must at least have a business key in order to use a
surrogate as a proxy; otherwise, you are just making things up with no clear
definition. That key should have a Unique Constraint defined whenever the
Primary Key is placed on the Identity.
Finally, for you select statement that you are using for your Insert
statement, all attributes used within a Join Condition and any highly
selectable columns that participate in a Where Clause should have and index
defined on them.
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OJ53NT9aFHA.2900@.TK2MSFTNGP15.phx.gbl...
If all you ever did was inserts or deletes (no Updates to variable length
columns) a 100% fill factor would work well on an Identity column. That way
all new rows simply get appended to the end of the current page and do not
cause pagesplits. If you update with larger rows you need to account for
that extra space on each page. Fill factors are always a trade off between
leaving enough room for inserts and updates vs. keeping it full enough to
not waste memory or disk space by having lots of empty space on each page.
There is no one correct answer as to what it should be. You must determine
that based on the activity of each table and the frequency of the
reindexing. If you reindex each night you can usually get by with a
relatively high fill factor. If it's once a week it probably would be
lower. You need to monitor the fragmentation in between reindexing to see
how fragmented it gets and how many page splits happen. If it is heavily
fragmented you may benefit from a lower fill factor. If not make it higher.
What those values should be we can not say. A good place to start is
usually around 80 or 90 on an index that is frequently Inserted, Updated and
Deleted. Then go from there.
--
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:e5WhWP6aFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Lets say C1 for all 3 tables.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OOZsb$5aFHA.2076@.TK2MSFTNGP15.phx.gbl...
>> What column(s) is the clustered index on?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:eRXkg55aFHA.2756@.tk2msftngp13.phx.gbl...
>> Ive always had a hard time getting my head into this topic so please
>> bear with me. Say Ive got a huge table that gets lots of Inserts. But to
>> do these Inserts, it needs to do lots of Selects.
>> #Table3 is the table in question here:
>> create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
>> create table #Table2(T2C1 int, T2C2 char(10))
>> create table #Table3(T3C1 int, T3C2 char(10), T3C3 char(10), T3C4
>> char(10))
>> insert into #Table1 (T1C2) values ('T1C2')
>> insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
>> insert into #Table3 (T3C1,T3C2,T3C3,T3C4)
>> select t1.T1C1,T1C2,t2.T2C2, 'test'
>> from #Table1 t1
>> inner join #Table2 t2 on t1.t1c1 = t2.t2c1
>>
>> So, would a table like this call for a lower Fill Factor as it will have
>> lots of new Inserts. Or would a table like this call for a higher Fill
>> Factor to increase the Select speed to do those Inserts?
>> TIA, ChrisR.
>>
>

filegroups

hi,
how can i see all tables/indexes in a specfic filegroups...
ive 3 filegroups..want to check all tables belonging to one filegroups..or al..
thnksSanjay,
Query to see which tables are placed on which filegroup is:
select distinct (select groupname from sysfilegroups where groupid = a.groupid) as filegroup,
object_name(id) as 'object name' from sysindexes a
where groupid <> 0
--undocumented stored procedure
sp_objectfilegroup @.objid
--
- Vishal
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:85246813-F9AF-438A-9AB5-FAC13AA5E676@.microsoft.com...
> hi,
> how can i see all tables/indexes in a specfic filegroups...
> ive 3 filegroups..want to check all tables belonging to one filegroups..or al..
> thnks
>|||Something like this?
SELECT o.name, f.name
FROM
sysobjects o
inner join
sysindexes i
on o.id = i.id
inner join
sysfiles f
on f.groupid = i.groupid
Note that it will show many tables spread across multiple filegroups, unless
you explicitly specified a filegroup in the create table or create clustered
index statements.
A
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:85246813-F9AF-438A-9AB5-FAC13AA5E676@.microsoft.com...
> hi,
> how can i see all tables/indexes in a specfic filegroups...
> ive 3 filegroups..want to check all tables belonging to one filegroups..or
al..
> thnks
>|||Sorry, this gets the files, not the filegroups. For filegroups:
SELECT DISTINCT o.name, g.groupname
FROM
sysobjects o
inner join
sysindexes i
on o.id = i.id
inner join
sysfilegroups g
on g.groupid = i.groupid
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:egqYkHTpDHA.2676@.TK2MSFTNGP11.phx.gbl...
> Something like this?
> SELECT o.name, f.name
> FROM
> sysobjects o
> inner join
> sysindexes i
> on o.id = i.id
> inner join
> sysfiles f
> on f.groupid = i.groupid
> Note that it will show many tables spread across multiple filegroups,
unless
> you explicitly specified a filegroup in the create table or create
clustered
> index statements.
> A
>
> "sanjay" <anonymous@.discussions.microsoft.com> wrote in message
> news:85246813-F9AF-438A-9AB5-FAC13AA5E676@.microsoft.com...
> > hi,
> > how can i see all tables/indexes in a specfic filegroups...
> > ive 3 filegroups..want to check all tables belonging to one
filegroups..or
> al..
> > thnks
> >
>

Filegroup is full

Hi.
I've a msde database and I'm getting the following error every minute
Error 1105, Severity: 17, ErrState: 2
Could not allocate space for object 'ProductSettings' in database
'epo_Srv-ms-avd' because the 'Primary' filegroup is full
I've cleanup the database but the error keeps on.
I need urgent help!!!
Thank you very much
Hi Eduardo,
How big is the database? Any chance it's up to 2G (ie the limit)?
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Eduardo Crespo" <crespo_santamaria@.NOSPAMhotmail.com> wrote in message
news:OoFIRcmuEHA.1992@.TK2MSFTNGP10.phx.gbl...
> Hi.
> I've a msde database and I'm getting the following error every minute
> Error 1105, Severity: 17, ErrState: 2
> Could not allocate space for object 'ProductSettings' in database
> 'epo_Srv-ms-avd' because the 'Primary' filegroup is full
> I've cleanup the database but the error keeps on.
> I need urgent help!!!
> Thank you very much
>
>
|||hi Eduardo,
"Eduardo Crespo" <crespo_santamaria@.NOSPAMhotmail.com> ha scritto nel
messaggio news:OoFIRcmuEHA.1992@.TK2MSFTNGP10.phx.gbl
> Hi.
> I've a msde database and I'm getting the following error every minute
> Error 1105, Severity: 17, ErrState: 2
> Could not allocate space for object 'ProductSettings' in database
> 'epo_Srv-ms-avd' because the 'Primary' filegroup is full
> I've cleanup the database but the error keeps on.
> I need urgent help!!!
> Thank you very much
in addition to Greg's answer, try executing
EXEC sp_helpdb 'your_db_name'
to see, in the 2nd resultset, if the datafile has limited file maxsize
option set or it can growth till 2gb limit...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thank you very much. Database size is 2 GB
"Eduardo Crespo" <crespo_santamaria@.NOSPAMhotmail.com> escribi en el
mensaje news:OoFIRcmuEHA.1992@.TK2MSFTNGP10.phx.gbl...
> Hi.
> I've a msde database and I'm getting the following error every minute
> Error 1105, Severity: 17, ErrState: 2
> Could not allocate space for object 'ProductSettings' in database
> 'epo_Srv-ms-avd' because the 'Primary' filegroup is full
> I've cleanup the database but the error keeps on.
> I need urgent help!!!
> Thank you very much
>
>