Showing posts with label image. Show all posts
Showing posts with label image. Show all posts

Thursday, March 8, 2012

Cannot upload file to database

Can anyone correct my code?
I will like to add a url to my database and upload my image to web server.
txtAddTitle will become my title name
txtFileName allow user to type in image name that will upload to webserver and at the meantime will insert into database as url references
value for @.chapterid will get from drop down list(ddlLesson) and the index value will store in database.

now i am able to upload my images with the file name that i have given to web server, but I am unable to insert data to my database.

Sub DoUpload(ByVal Sender As Object, ByVal e As System.EventArgs)
Dim sPath As String
Dim sFile As String
Dim sFullPath As String
Dim sSplit() As String
Dim sPathFriendly As String


'Upload to same path as script. Internet Anonymous User must have write permissions
sPath = Server.MapPath("../Tutorial")
'sPath = Server.MapPath(".")
If Right(sPath, 1) <> "\" Then
sPathFriendly = sPath 'Friendly path name for display
sPath = sPath & "\"
Else
sPathFriendly = Left(sPath, Len(sPath) - 1)
End If


'Save as same file name being posted
'The code below resolves the file name
'(removes path info)
sFile = txtFileName.Text
'sFile = txtUpload.PostedFile.FileName
sSplit = Split(sFile, "\")
sFile = sSplit(UBound(sSplit))


sFullPath = sPath & sFile
Try
txtUpload.PostedFile.SaveAs(sFullPath)
lblResults.Text = "<br>Upload of File " & sFile & " to " & sPathFriendly & " succeeded"

Catch Ex As Exception

lblResults.Text = "<br>Upload of File " & sFile & " to " & sPathFriendly & " failed for the following reason: " & Ex.Message
Finally
lblResults.Font.Bold = True
lblResults.Visible = True
End Try

Dim mycommand As SqlCommand
Dim myConnection As SqlConnection
Message.InnerHtml = ""
'to all the valur to database
If IsValid Then

myConnection = New SqlConnection("Server=localhost;UID=sa;pwd=;database=u")
mycommand = New SqlCommand("INSERT INTO t_linkTitle(link_chapterid,link_name,link_url) VALUES (@.chapterid,@.titleName,@.titleUrl)", myConnection)

mycommand.Parameters.Add("@.chapterid", SqlDbType.VarChar, 50).Value = ddlLesson.SelectedItem.Value
mycommand.Parameters.Add("@.titleName", SqlDbType.VarChar, 50).Value = txtAddTitle.Text
mycommand.Parameters.Add("@.titleUrl", SqlDbType.VarChar, 100).Value = txtFileName.Text

mycommand.Connection.Open()
msgErrorTitle.Style("color") = "OrangeRed"
Try
mycommand.ExecuteNonQuery()
msgErrorTitle.InnerHtml = "New title <b>" + txtAddTitle.Text + "</b> Added to " + "<b>" + ddlLesson.SelectedItem.Text + "</b><br>" + mycommand.ToString()
Catch Exp As SqlException

If Exp.Number = 2627 Then
msgErrorTitle.InnerHtml = "ERROR: Title already exists. Please use another title"
Else
msgErrorTitle.InnerHtml = "ERROR: Could not add record, please ensure the fields are correctly filled out"
End If

End Try

mycommand.Connection.Close()
End If

LoadTitle()

End Sub

So what sql server error number are you getting when you attempt an insert?|||

emulti wrote:

So what sql server error number are you getting when you attempt an insert?

It show this"ERROR: Could not add record, please ensure the fields are correctly filled out"

Friday, February 24, 2012

Cannot shrink database because of StatBlob data on sysindexes

SQL Server 2000 SP4
Data pages from the image column StatBlob on sysIndexes exist right at the
end of my data file. They are stopping me truncating the end of my datafile.
I had other tables with text columns. These were moved by either copying the
table or bcp/truncate.
It there a way to move the data on sysIndexes without doing a "shrinkfile
move datapages"?
Regards
Paul Cahill
You can try dropping all the statistics and see if it frees up that extent.
Just make sure you know which stats you may want to put back. By the way
this is a good example of one reason why you should keep only the system
objects in the Primary filegroup and place the user objects in secondary
filegroups.
Andrew J. Kelly SQL MVP
"Paul Cahill" <anon@.anon.com> wrote in message
news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
> SQL Server 2000 SP4
> Data pages from the image column StatBlob on sysIndexes exist right at the
> end of my data file. They are stopping me truncating the end of my
> datafile.
> I had other tables with text columns. These were moved by either copying
> the table or bcp/truncate.
> It there a way to move the data on sysIndexes without doing a "shrinkfile
> move datapages"?
> Regards
> Paul Cahill
>
|||Hi Andrew
How do I drop statistics for indexed columns? The StatBlob values are for
indexes not for stats manually created with CREATE STATISTICS.
I could rebuild all stats on the database but I do not know if this will
move the pages where StatBlob is stored.
I tried doing a full sp_updatestats on a test database and the pages did not
seem to move for sysIndexes
(I use dbcc extentinfo)
Regards
Paul
PS No he tells me
PPS Where does it say in the manual "DO NOT USE PRIMARY"
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
> You can try dropping all the statistics and see if it frees up that
> extent. Just make sure you know which stats you may want to put back. By
> the way this is a good example of one reason why you should keep only the
> system objects in the Primary filegroup and place the user objects in
> secondary filegroups.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <anon@.anon.com> wrote in message
> news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>
|||Ahh, well then you may have to drop the index itself. updating stats most
likely will not do anything since it just updates the row in sysindexes that
already exists. It has no need to move it. Where as is you had a lot of
stats or indexes that you drop there is a potential for the extent they are
on to be deallocated and thus allowing the shrink.
Andrew J. Kelly SQL MVP
"Paul Cahill" <anon@.anon.com> wrote in message
news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
> Hi Andrew
> How do I drop statistics for indexed columns? The StatBlob values are for
> indexes not for stats manually created with CREATE STATISTICS.
> I could rebuild all stats on the database but I do not know if this will
> move the pages where StatBlob is stored.
> I tried doing a full sp_updatestats on a test database and the pages did
> not seem to move for sysIndexes
> (I use dbcc extentinfo)
> Regards
> Paul
> PS No he tells me
> PPS Where does it say in the manual "DO NOT USE PRIMARY"
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>
|||Hi Andrew.
Given a values from dbcc ententinfo can this be linked back to the row in
sysIndexes?
I.e. there may be a single index I could recreate that would clear my block.
I had never thought about not using primary for user data.
With hindsight it makes sense. I have not seen any Microsoft documents that
mention this.
It would make more sense if SQL Server simply kept it's system tables on a
private filegroup.
Paul
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e1Qn9Uo3GHA.1256@.TK2MSFTNGP04.phx.gbl...
> Ahh, well then you may have to drop the index itself. updating stats most
> likely will not do anything since it just updates the row in sysindexes
> that already exists. It has no need to move it. Where as is you had a lot
> of stats or indexes that you drop there is a potential for the extent they
> are on to be deallocated and thus allowing the shrink.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <anon@.anon.com> wrote in message
> news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
>
|||To be honest I don't know if it can be linked back or not but it would not
be a single index as that would only take up one row in sysindexes. You
would need to clear the entire extent so it can be deallocated. Going
forward in 2005 there is even more reason to separate the filegroups (user
from primary) due to how you deal with partial restores. I have to admit
this is the first time I have seen sysindexes be an issue for shrinking data
files.
Andrew J. Kelly SQL MVP
"Paul Cahill" <noname@.noname.com> wrote in message
news:Oa86zOw3GHA.4972@.TK2MSFTNGP03.phx.gbl...
> Hi Andrew.
> Given a values from dbcc ententinfo can this be linked back to the row in
> sysIndexes?
> I.e. there may be a single index I could recreate that would clear my
> block.
> I had never thought about not using primary for user data.
> With hindsight it makes sense. I have not seen any Microsoft documents
> that mention this.
> It would make more sense if SQL Server simply kept it's system tables on a
> private filegroup.
> Paul
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e1Qn9Uo3GHA.1256@.TK2MSFTNGP04.phx.gbl...
>
|||Too many text columns. Too many tables. Our system dates back to SQL 6.5.
I noticed SQL 2005 has a text cleanup.
I think the only way out of this for me is a long slow process of moving out
followed by shrinkfile with move data.
Disk space permitting.
It's a big database and it's being logshipped across a wan with litespeed.
Thanks
Paul
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23YJd2Vx3GHA.1588@.TK2MSFTNGP02.phx.gbl...
> To be honest I don't know if it can be linked back or not but it would not
> be a single index as that would only take up one row in sysindexes. You
> would need to clear the entire extent so it can be deallocated. Going
> forward in 2005 there is even more reason to separate the filegroups (user
> from primary) due to how you deal with partial restores. I have to admit
> this is the first time I have seen sysindexes be an issue for shrinking
> data files.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <noname@.noname.com> wrote in message
> news:Oa86zOw3GHA.4972@.TK2MSFTNGP03.phx.gbl...
>

Cannot shrink database because of StatBlob data on sysindexes

SQL Server 2000 SP4
Data pages from the image column StatBlob on sysIndexes exist right at the
end of my data file. They are stopping me truncating the end of my datafile.
I had other tables with text columns. These were moved by either copying the
table or bcp/truncate.
It there a way to move the data on sysIndexes without doing a "shrinkfile
move datapages"?
Regards
Paul CahillYou can try dropping all the statistics and see if it frees up that extent.
Just make sure you know which stats you may want to put back. By the way
this is a good example of one reason why you should keep only the system
objects in the Primary filegroup and place the user objects in secondary
filegroups.
Andrew J. Kelly SQL MVP
"Paul Cahill" <anon@.anon.com> wrote in message
news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
> SQL Server 2000 SP4
> Data pages from the image column StatBlob on sysIndexes exist right at the
> end of my data file. They are stopping me truncating the end of my
> datafile.
> I had other tables with text columns. These were moved by either copying
> the table or bcp/truncate.
> It there a way to move the data on sysIndexes without doing a "shrinkfile
> move datapages"?
> Regards
> Paul Cahill
>|||Hi Andrew
How do I drop statistics for indexed columns? The StatBlob values are for
indexes not for stats manually created with CREATE STATISTICS.
I could rebuild all stats on the database but I do not know if this will
move the pages where StatBlob is stored.
I tried doing a full sp_updatestats on a test database and the pages did not
seem to move for sysIndexes
(I use dbcc extentinfo)
Regards
Paul
PS No he tells me
PPS Where does it say in the manual "DO NOT USE PRIMARY"
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
> You can try dropping all the statistics and see if it frees up that
> extent. Just make sure you know which stats you may want to put back. By
> the way this is a good example of one reason why you should keep only the
> system objects in the Primary filegroup and place the user objects in
> secondary filegroups.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <anon@.anon.com> wrote in message
> news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>|||Ahh, well then you may have to drop the index itself. updating stats most
likely will not do anything since it just updates the row in sysindexes that
already exists. It has no need to move it. Where as is you had a lot of
stats or indexes that you drop there is a potential for the extent they are
on to be deallocated and thus allowing the shrink.
Andrew J. Kelly SQL MVP
"Paul Cahill" <anon@.anon.com> wrote in message
news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
> Hi Andrew
> How do I drop statistics for indexed columns? The StatBlob values are for
> indexes not for stats manually created with CREATE STATISTICS.
> I could rebuild all stats on the database but I do not know if this will
> move the pages where StatBlob is stored.
> I tried doing a full sp_updatestats on a test database and the pages did
> not seem to move for sysIndexes
> (I use dbcc extentinfo)
> Regards
> Paul
> PS No he tells me
> PPS Where does it say in the manual "DO NOT USE PRIMARY"
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>|||Hi Andrew.
Given a values from dbcc ententinfo can this be linked back to the row in
sysIndexes?
I.e. there may be a single index I could recreate that would clear my block.
I had never thought about not using primary for user data.
With hindsight it makes sense. I have not seen any Microsoft documents that
mention this.
It would make more sense if SQL Server simply kept it's system tables on a
private filegroup.
Paul
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e1Qn9Uo3GHA.1256@.TK2MSFTNGP04.phx.gbl...
> Ahh, well then you may have to drop the index itself. updating stats most
> likely will not do anything since it just updates the row in sysindexes
> that already exists. It has no need to move it. Where as is you had a lot
> of stats or indexes that you drop there is a potential for the extent they
> are on to be deallocated and thus allowing the shrink.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <anon@.anon.com> wrote in message
> news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
>|||To be honest I don't know if it can be linked back or not but it would not
be a single index as that would only take up one row in sysindexes. You
would need to clear the entire extent so it can be deallocated. Going
forward in 2005 there is even more reason to separate the filegroups (user
from primary) due to how you deal with partial restores. I have to admit
this is the first time I have seen sysindexes be an issue for shrinking data
files.
Andrew J. Kelly SQL MVP
"Paul Cahill" <noname@.noname.com> wrote in message
news:Oa86zOw3GHA.4972@.TK2MSFTNGP03.phx.gbl...
> Hi Andrew.
> Given a values from dbcc ententinfo can this be linked back to the row in
> sysIndexes?
> I.e. there may be a single index I could recreate that would clear my
> block.
> I had never thought about not using primary for user data.
> With hindsight it makes sense. I have not seen any Microsoft documents
> that mention this.
> It would make more sense if SQL Server simply kept it's system tables on a
> private filegroup.
> Paul
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e1Qn9Uo3GHA.1256@.TK2MSFTNGP04.phx.gbl...
>|||Too many text columns. Too many tables. Our system dates back to SQL 6.5.
I noticed SQL 2005 has a text cleanup.
I think the only way out of this for me is a long slow process of moving out
followed by shrinkfile with move data.
Disk space permitting.
It's a big database and it's being logshipped across a wan with litespeed.
Thanks
Paul
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23YJd2Vx3GHA.1588@.TK2MSFTNGP02.phx.gbl...
> To be honest I don't know if it can be linked back or not but it would not
> be a single index as that would only take up one row in sysindexes. You
> would need to clear the entire extent so it can be deallocated. Going
> forward in 2005 there is even more reason to separate the filegroups (user
> from primary) due to how you deal with partial restores. I have to admit
> this is the first time I have seen sysindexes be an issue for shrinking
> data files.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <noname@.noname.com> wrote in message
> news:Oa86zOw3GHA.4972@.TK2MSFTNGP03.phx.gbl...
>

Cannot shrink database because of StatBlob data on sysindexes

SQL Server 2000 SP4
Data pages from the image column StatBlob on sysIndexes exist right at the
end of my data file. They are stopping me truncating the end of my datafile.
I had other tables with text columns. These were moved by either copying the
table or bcp/truncate.
It there a way to move the data on sysIndexes without doing a "shrinkfile
move datapages"?
Regards
Paul CahillYou can try dropping all the statistics and see if it frees up that extent.
Just make sure you know which stats you may want to put back. By the way
this is a good example of one reason why you should keep only the system
objects in the Primary filegroup and place the user objects in secondary
filegroups.
--
Andrew J. Kelly SQL MVP
"Paul Cahill" <anon@.anon.com> wrote in message
news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
> SQL Server 2000 SP4
> Data pages from the image column StatBlob on sysIndexes exist right at the
> end of my data file. They are stopping me truncating the end of my
> datafile.
> I had other tables with text columns. These were moved by either copying
> the table or bcp/truncate.
> It there a way to move the data on sysIndexes without doing a "shrinkfile
> move datapages"?
> Regards
> Paul Cahill
>|||Hi Andrew
How do I drop statistics for indexed columns? The StatBlob values are for
indexes not for stats manually created with CREATE STATISTICS.
I could rebuild all stats on the database but I do not know if this will
move the pages where StatBlob is stored.
I tried doing a full sp_updatestats on a test database and the pages did not
seem to move for sysIndexes
(I use dbcc extentinfo)
Regards
Paul
PS No he tells me
PPS Where does it say in the manual "DO NOT USE PRIMARY"
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
> You can try dropping all the statistics and see if it frees up that
> extent. Just make sure you know which stats you may want to put back. By
> the way this is a good example of one reason why you should keep only the
> system objects in the Primary filegroup and place the user objects in
> secondary filegroups.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <anon@.anon.com> wrote in message
> news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 SP4
>> Data pages from the image column StatBlob on sysIndexes exist right at
>> the end of my data file. They are stopping me truncating the end of my
>> datafile.
>> I had other tables with text columns. These were moved by either copying
>> the table or bcp/truncate.
>> It there a way to move the data on sysIndexes without doing a "shrinkfile
>> move datapages"?
>> Regards
>> Paul Cahill
>|||Ahh, well then you may have to drop the index itself. updating stats most
likely will not do anything since it just updates the row in sysindexes that
already exists. It has no need to move it. Where as is you had a lot of
stats or indexes that you drop there is a potential for the extent they are
on to be deallocated and thus allowing the shrink.
--
Andrew J. Kelly SQL MVP
"Paul Cahill" <anon@.anon.com> wrote in message
news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
> Hi Andrew
> How do I drop statistics for indexed columns? The StatBlob values are for
> indexes not for stats manually created with CREATE STATISTICS.
> I could rebuild all stats on the database but I do not know if this will
> move the pages where StatBlob is stored.
> I tried doing a full sp_updatestats on a test database and the pages did
> not seem to move for sysIndexes
> (I use dbcc extentinfo)
> Regards
> Paul
> PS No he tells me
> PPS Where does it say in the manual "DO NOT USE PRIMARY"
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>> You can try dropping all the statistics and see if it frees up that
>> extent. Just make sure you know which stats you may want to put back. By
>> the way this is a good example of one reason why you should keep only the
>> system objects in the Primary filegroup and place the user objects in
>> secondary filegroups.
>> --
>> Andrew J. Kelly SQL MVP
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 SP4
>> Data pages from the image column StatBlob on sysIndexes exist right at
>> the end of my data file. They are stopping me truncating the end of my
>> datafile.
>> I had other tables with text columns. These were moved by either copying
>> the table or bcp/truncate.
>> It there a way to move the data on sysIndexes without doing a
>> "shrinkfile move datapages"?
>> Regards
>> Paul Cahill
>>
>|||Hi Andrew.
Given a values from dbcc ententinfo can this be linked back to the row in
sysIndexes?
I.e. there may be a single index I could recreate that would clear my block.
I had never thought about not using primary for user data.
With hindsight it makes sense. I have not seen any Microsoft documents that
mention this.
It would make more sense if SQL Server simply kept it's system tables on a
private filegroup.
Paul
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e1Qn9Uo3GHA.1256@.TK2MSFTNGP04.phx.gbl...
> Ahh, well then you may have to drop the index itself. updating stats most
> likely will not do anything since it just updates the row in sysindexes
> that already exists. It has no need to move it. Where as is you had a lot
> of stats or indexes that you drop there is a potential for the extent they
> are on to be deallocated and thus allowing the shrink.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <anon@.anon.com> wrote in message
> news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
>> Hi Andrew
>> How do I drop statistics for indexed columns? The StatBlob values are for
>> indexes not for stats manually created with CREATE STATISTICS.
>> I could rebuild all stats on the database but I do not know if this will
>> move the pages where StatBlob is stored.
>> I tried doing a full sp_updatestats on a test database and the pages did
>> not seem to move for sysIndexes
>> (I use dbcc extentinfo)
>> Regards
>> Paul
>> PS No he tells me
>> PPS Where does it say in the manual "DO NOT USE PRIMARY"
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>> You can try dropping all the statistics and see if it frees up that
>> extent. Just make sure you know which stats you may want to put back.
>> By the way this is a good example of one reason why you should keep only
>> the system objects in the Primary filegroup and place the user objects
>> in secondary filegroups.
>> --
>> Andrew J. Kelly SQL MVP
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 SP4
>> Data pages from the image column StatBlob on sysIndexes exist right at
>> the end of my data file. They are stopping me truncating the end of my
>> datafile.
>> I had other tables with text columns. These were moved by either
>> copying the table or bcp/truncate.
>> It there a way to move the data on sysIndexes without doing a
>> "shrinkfile move datapages"?
>> Regards
>> Paul Cahill
>>
>>
>|||To be honest I don't know if it can be linked back or not but it would not
be a single index as that would only take up one row in sysindexes. You
would need to clear the entire extent so it can be deallocated. Going
forward in 2005 there is even more reason to separate the filegroups (user
from primary) due to how you deal with partial restores. I have to admit
this is the first time I have seen sysindexes be an issue for shrinking data
files.
--
Andrew J. Kelly SQL MVP
"Paul Cahill" <noname@.noname.com> wrote in message
news:Oa86zOw3GHA.4972@.TK2MSFTNGP03.phx.gbl...
> Hi Andrew.
> Given a values from dbcc ententinfo can this be linked back to the row in
> sysIndexes?
> I.e. there may be a single index I could recreate that would clear my
> block.
> I had never thought about not using primary for user data.
> With hindsight it makes sense. I have not seen any Microsoft documents
> that mention this.
> It would make more sense if SQL Server simply kept it's system tables on a
> private filegroup.
> Paul
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e1Qn9Uo3GHA.1256@.TK2MSFTNGP04.phx.gbl...
>> Ahh, well then you may have to drop the index itself. updating stats
>> most likely will not do anything since it just updates the row in
>> sysindexes that already exists. It has no need to move it. Where as is
>> you had a lot of stats or indexes that you drop there is a potential for
>> the extent they are on to be deallocated and thus allowing the shrink.
>> --
>> Andrew J. Kelly SQL MVP
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
>> Hi Andrew
>> How do I drop statistics for indexed columns? The StatBlob values are
>> for indexes not for stats manually created with CREATE STATISTICS.
>> I could rebuild all stats on the database but I do not know if this will
>> move the pages where StatBlob is stored.
>> I tried doing a full sp_updatestats on a test database and the pages did
>> not seem to move for sysIndexes
>> (I use dbcc extentinfo)
>> Regards
>> Paul
>> PS No he tells me
>> PPS Where does it say in the manual "DO NOT USE PRIMARY"
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>> You can try dropping all the statistics and see if it frees up that
>> extent. Just make sure you know which stats you may want to put back.
>> By the way this is a good example of one reason why you should keep
>> only the system objects in the Primary filegroup and place the user
>> objects in secondary filegroups.
>> --
>> Andrew J. Kelly SQL MVP
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 SP4
>> Data pages from the image column StatBlob on sysIndexes exist right at
>> the end of my data file. They are stopping me truncating the end of my
>> datafile.
>> I had other tables with text columns. These were moved by either
>> copying the table or bcp/truncate.
>> It there a way to move the data on sysIndexes without doing a
>> "shrinkfile move datapages"?
>> Regards
>> Paul Cahill
>>
>>
>>
>|||Too many text columns. Too many tables. Our system dates back to SQL 6.5.
I noticed SQL 2005 has a text cleanup.
I think the only way out of this for me is a long slow process of moving out
followed by shrinkfile with move data.
Disk space permitting.
It's a big database and it's being logshipped across a wan with litespeed.
Thanks
Paul
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23YJd2Vx3GHA.1588@.TK2MSFTNGP02.phx.gbl...
> To be honest I don't know if it can be linked back or not but it would not
> be a single index as that would only take up one row in sysindexes. You
> would need to clear the entire extent so it can be deallocated. Going
> forward in 2005 there is even more reason to separate the filegroups (user
> from primary) due to how you deal with partial restores. I have to admit
> this is the first time I have seen sysindexes be an issue for shrinking
> data files.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <noname@.noname.com> wrote in message
> news:Oa86zOw3GHA.4972@.TK2MSFTNGP03.phx.gbl...
>> Hi Andrew.
>> Given a values from dbcc ententinfo can this be linked back to the row in
>> sysIndexes?
>> I.e. there may be a single index I could recreate that would clear my
>> block.
>> I had never thought about not using primary for user data.
>> With hindsight it makes sense. I have not seen any Microsoft documents
>> that mention this.
>> It would make more sense if SQL Server simply kept it's system tables on
>> a private filegroup.
>> Paul
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:e1Qn9Uo3GHA.1256@.TK2MSFTNGP04.phx.gbl...
>> Ahh, well then you may have to drop the index itself. updating stats
>> most likely will not do anything since it just updates the row in
>> sysindexes that already exists. It has no need to move it. Where as is
>> you had a lot of stats or indexes that you drop there is a potential for
>> the extent they are on to be deallocated and thus allowing the shrink.
>> --
>> Andrew J. Kelly SQL MVP
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
>> Hi Andrew
>> How do I drop statistics for indexed columns? The StatBlob values are
>> for indexes not for stats manually created with CREATE STATISTICS.
>> I could rebuild all stats on the database but I do not know if this
>> will move the pages where StatBlob is stored.
>> I tried doing a full sp_updatestats on a test database and the pages
>> did not seem to move for sysIndexes
>> (I use dbcc extentinfo)
>> Regards
>> Paul
>> PS No he tells me
>> PPS Where does it say in the manual "DO NOT USE PRIMARY"
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>> You can try dropping all the statistics and see if it frees up that
>> extent. Just make sure you know which stats you may want to put back.
>> By the way this is a good example of one reason why you should keep
>> only the system objects in the Primary filegroup and place the user
>> objects in secondary filegroups.
>> --
>> Andrew J. Kelly SQL MVP
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 SP4
>> Data pages from the image column StatBlob on sysIndexes exist right
>> at the end of my data file. They are stopping me truncating the end
>> of my datafile.
>> I had other tables with text columns. These were moved by either
>> copying the table or bcp/truncate.
>> It there a way to move the data on sysIndexes without doing a
>> "shrinkfile move datapages"?
>> Regards
>> Paul Cahill
>>
>>
>>
>>
>