Does SP have a max size?

Hi all. I'm getting a weird error in a stored procedure:
Servidor: mensaje 208, nivel 16, estado 6, procedimiento RegistrarTabla,
lnea 65535
El nombre de objeto 'RegistrarTabla' no es vlido.
That's spanish but should be:
Server: message 208, level 16, state 6, procedure 'RegisterTable', line
65535
The object name 'RegisterTable' is not valid.
The procedure does not have so many lines, and the number 65535 makes me
distrust.
Does SP have a maximum size or something?
Regards,
Diego F.Diego
I think you should be concered about am error and not about a line of code.
Copy the stored procedure's code to Query Analyzer and run it. It may space
or empty strings within a stored procedure.
"Diego F." <diegofrNO@.terra.es> wrote in message
news:e1YpxfolFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi all. I'm getting a weird error in a stored procedure:
> Servidor: mensaje 208, nivel 16, estado 6, procedimiento RegistrarTabla,
> lnea 65535
> El nombre de objeto 'RegistrarTabla' no es vlido.
> That's spanish but should be:
> Server: message 208, level 16, state 6, procedure 'RegisterTable', line
> 65535
> The object name 'RegisterTable' is not valid.
> The procedure does not have so many lines, and the number 65535 makes me
> distrust.
> Does SP have a maximum size or something?
> --
> Regards,
> Diego F.
>
>|||That's QA which gives me that error. Doesn't apply to any line. It's a
strange error.
Regards,
Diego F.
"Uri Dimant" <urid@.iscar.co.il> escribi en el mensaje
news:u3CM8molFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Diego
> I think you should be concered about am error and not about a line of
> code.
> Copy the stored procedure's code to Query Analyzer and run it. It may
> space or empty strings within a stored procedure.
>
> "Diego F." <diegofrNO@.terra.es> wrote in message
> news:e1YpxfolFHA.572@.TK2MSFTNGP15.phx.gbl...
>|||Diego
If you post DDL + samle data we will be able to test and maybe reproduce the
error's behaviour.
"Diego F." <diegofrNO@.terra.es> wrote in message
news:OqS00qolFHA.1968@.TK2MSFTNGP14.phx.gbl...
> That's QA which gives me that error. Doesn't apply to any line. It's a
> strange error.
> --
> Regards,
> Diego F.
>
> "Uri Dimant" <urid@.iscar.co.il> escribi en el mensaje
> news:u3CM8molFHA.1464@.TK2MSFTNGP14.phx.gbl...
>|||Check that u are connected rt database in which 'RegisterTable' is
present|||While we wait for you to send some DDL, let me ask you this: is
'RegistrarTabla' the name of a user table or your procedure?
ML|||65,535 lines? Have you ever considered publishing that stored procedure as a
book?
"Diego F." <diegofrNO@.terra.es> wrote in message
news:e1YpxfolFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi all. I'm getting a weird error in a stored procedure:
> Servidor: mensaje 208, nivel 16, estado 6, procedimiento RegistrarTabla,
> lnea 65535
> El nombre de objeto 'RegistrarTabla' no es vlido.
> That's spanish but should be:
> Server: message 208, level 16, state 6, procedure 'RegisterTable', line
> 65535
> The object name 'RegisterTable' is not valid.
> The procedure does not have so many lines, and the number 65535 makes me
> distrust.
> Does SP have a maximum size or something?
> --
> Regards,
> Diego F.
>
>|||OK, thank you all. It was a stupid problem. I was altering a SP with a name
changed, so the name of that SP didn't exist at all!.
I'm a bit embarrased :-P
Regards,
Diego F.
"Diego F." <diegofrNO@.terra.es> escribi en el mensaje
news:e1YpxfolFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi all. I'm getting a weird error in a stored procedure:
> Servidor: mensaje 208, nivel 16, estado 6, procedimiento RegistrarTabla,
> lnea 65535
> El nombre de objeto 'RegistrarTabla' no es vlido.
> That's spanish but should be:
> Server: message 208, level 16, state 6, procedure 'RegisterTable', line
> 65535
> The object name 'RegisterTable' is not valid.
> The procedure does not have so many lines, and the number 65535 makes me
> distrust.
> Does SP have a maximum size or something?
> --
> Regards,
> Diego F.
>
>

Does shrinking a user database cause tempdb to grow?

I'm looking at all the possible issues that may have contributed to an
inexplicably large jump in size for my tempdb. I recall reading an article
or posting that stated shrinking a user database will cause tempdb to grow.
Can anyone provide insight on this?Sorry, I misread "tempdb" as "log file" ... Disregard my previous reply
(although, you should still read that article anyway -- it's very
informative). I'm not aware of any condition that would cause tempdb to
grow during a shrink.
"Rod Bautista" <rod.bautista@.cox.net> wrote in message
news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I'm looking at all the possible issues that may have contributed to an
> inexplicably large jump in size for my tempdb. I recall reading an
article
> or posting that stated shrinking a user database will cause tempdb to
grow.
> Can anyone provide insight on this?
>|||Yes:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Rod Bautista" <rod.bautista@.cox.net> wrote in message
news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I'm looking at all the possible issues that may have contributed to an
> inexplicably large jump in size for my tempdb. I recall reading an
article
> or posting that stated shrinking a user database will cause tempdb to
grow.
> Can anyone provide insight on this?
>|||AFAIK, tempdb is not used for a shrink. The pages are quite simply moved toward the beginning of the
file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rod Bautista" <rod.bautista@.cox.net> wrote in message news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I'm looking at all the possible issues that may have contributed to an
> inexplicably large jump in size for my tempdb. I recall reading an article
> or posting that stated shrinking a user database will cause tempdb to grow.
> Can anyone provide insight on this?
>|||Thanks Adam and Tibor.
I thought as much...I was certain somebody stated that in a post and the
idea stuck with me. It didn't make any sense for temdb to be affected
during a shrink operation but I had to throw the question out there.
R
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u$OUb8jqEHA.3464@.tk2msftngp13.phx.gbl...
> AFAIK, tempdb is not used for a shrink. The pages are quite simply moved
toward the beginning of the
> file.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rod Bautista" <rod.bautista@.cox.net> wrote in message
news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...
> > I'm looking at all the possible issues that may have contributed to an
> > inexplicably large jump in size for my tempdb. I recall reading an
article
> > or posting that stated shrinking a user database will cause tempdb to
grow.
> > Can anyone provide insight on this?
> >
> >
>|||I know others have replied with the same answer, but I'll also say that from
the code in shrink there's nothing that touches or affects tempdb.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rod Bautista" <rod.bautista@.cox.net> wrote in message
news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I'm looking at all the possible issues that may have contributed to an
> inexplicably large jump in size for my tempdb. I recall reading an
article
> or posting that stated shrinking a user database will cause tempdb to
grow.
> Can anyone provide insight on this?
>

Does shrinking a user database cause tempdb to grow?

I'm looking at all the possible issues that may have contributed to an
inexplicably large jump in size for my tempdb. I recall reading an article
or posting that stated shrinking a user database will cause tempdb to grow.
Can anyone provide insight on this?
Yes:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Rod Bautista" <rod.bautista@.cox.net> wrote in message
news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I'm looking at all the possible issues that may have contributed to an
> inexplicably large jump in size for my tempdb. I recall reading an
article
> or posting that stated shrinking a user database will cause tempdb to
grow.
> Can anyone provide insight on this?
>
|||Sorry, I misread "tempdb" as "log file" ... Disregard my previous reply
(although, you should still read that article anyway -- it's very
informative). I'm not aware of any condition that would cause tempdb to
grow during a shrink.
"Rod Bautista" <rod.bautista@.cox.net> wrote in message
news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I'm looking at all the possible issues that may have contributed to an
> inexplicably large jump in size for my tempdb. I recall reading an
article
> or posting that stated shrinking a user database will cause tempdb to
grow.
> Can anyone provide insight on this?
>
|||AFAIK, tempdb is not used for a shrink. The pages are quite simply moved toward the beginning of the
file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rod Bautista" <rod.bautista@.cox.net> wrote in message news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I'm looking at all the possible issues that may have contributed to an
> inexplicably large jump in size for my tempdb. I recall reading an article
> or posting that stated shrinking a user database will cause tempdb to grow.
> Can anyone provide insight on this?
>
|||Thanks Adam and Tibor.
I thought as much...I was certain somebody stated that in a post and the
idea stuck with me. It didn't make any sense for temdb to be affected
during a shrink operation but I had to throw the question out there.
R
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u$OUb8jqEHA.3464@.tk2msftngp13.phx.gbl...
> AFAIK, tempdb is not used for a shrink. The pages are quite simply moved
toward the beginning of the
> file.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rod Bautista" <rod.bautista@.cox.net> wrote in message
news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
article[vbcol=seagreen]
grow.
>
|||I know others have replied with the same answer, but I'll also say that from
the code in shrink there's nothing that touches or affects tempdb.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rod Bautista" <rod.bautista@.cox.net> wrote in message
news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I'm looking at all the possible issues that may have contributed to an
> inexplicably large jump in size for my tempdb. I recall reading an
article
> or posting that stated shrinking a user database will cause tempdb to
grow.
> Can anyone provide insight on this?
>

Does RS support to change the report size automatically?-URGENT!!!

I will use MSRS to create reports in a .Net Smart Client project later.

For some kinds of report, there are some customized columns which may be displayed or not. I design these reports like this: first create all the columns, then set the "Visibility-Hidden" property of those customized columns using expression which including some report parameters.Thus, these customized columns can be displayed or hidden by setting the value of the report parameters at the runtime. But another problem emerged.The size of report backgroud can not changed automatically along with the length of DataTable.So if there are some columns be hidden, there will be some margin on the right of the report and the Title of report was still in the center of the original report,not changed to the center of the new one. Due to the "Size " property of report and the "Size"&"Location"properties of textbox does not support expression.

Is there any one can give me some solution?

I am not sure whether the current version of MSRS support such kind of requirement?If not,will the MSRS final release comes out on November 7 support it?

Thanks!

Hi!

I also have this problem! I am building subreports that are getting more and more generic, but now I have noticed that the (optionally) hidden rows in the subreport's table is showing up as blank space on the main report. That makes this useful way of making the subreports nothing more than useless.

I have found no way to set the size of the subreport "background" or rectangle smaller than the designed area.

The reporting services seems more and more tied down to me. It has to have much more abilities than it currently has to survive. At the moment automation of Word seems much more flexible, but is client based. Still, in many situations the current RS will not deliver enough to fulfill the requirements. I hope this will change soon.

/Michael

|||The report never shrinks the size of the body so hiding a bunch of columns won't help your report. If you want a title to span the items in a table, put it in the table header, similar to how this matrix report was done: http://blogs.msdn.com/bwelcker/archive/2005/05/11/416720.aspx.|||But I am using a "table" as a way of displaying different checkboxes and texts, as a form, and sometimes one or more of the rows in these (generic) subreports are not to be used and I hide them (that's what makes it more generic).

I am not using it to show a list of information but as a way of showing a dynamic one-page form.

When I hide a row it shows up as approximately half a row of blank space on the main report. In this case the Visiblity setting is useless since there is too much space between subreports on the main report.

Does RS support to change the report size automatically?-URGENT!!!

I will use MSRS to create reports in a .Net Smart Client project later.

For some kinds of report, there are some customized columns which may be displayed or not. I design these reports like this: first create all the columns, then set the "Visibility-Hidden" property of those customized columns using expression which including some report parameters.Thus, these customized columns can be displayed or hidden by setting the value of the report parameters at the runtime. But another problem emerged.The size of report backgroud can not changed automatically along with the length of DataTable.So if there are some columns be hidden, there will be some margin on the right of the report and the Title of report was still in the center of the original report,not changed to the center of the new one. Due to the "Size " property of report and the "Size"&"Location"properties of textbox does not support expression.

Is there any one can give me some solution?

I am not sure whether the current version of MSRS support such kind of requirement?If not,will the MSRS final release comes out on November 7 support it?

Thanks!

Hi!

I also have this problem! I am building subreports that are getting more and more generic, but now I have noticed that the (optionally) hidden rows in the subreport's table is showing up as blank space on the main report. That makes this useful way of making the subreports nothing more than useless.

I have found no way to set the size of the subreport "background" or rectangle smaller than the designed area.

The reporting services seems more and more tied down to me. It has to have much more abilities than it currently has to survive. At the moment automation of Word seems much more flexible, but is client based. Still, in many situations the current RS will not deliver enough to fulfill the requirements. I hope this will change soon.

/Michael

|||The report never shrinks the size of the body so hiding a bunch of columns won't help your report. If you want a title to span the items in a table, put it in the table header, similar to how this matrix report was done: http://blogs.msdn.com/bwelcker/archive/2005/05/11/416720.aspx.|||But I am using a "table" as a way of displaying different checkboxes and texts, as a form, and sometimes one or more of the rows in these (generic) subreports are not to be used and I hide them (that's what makes it more generic).

I am not using it to show a list of information but as a way of showing a dynamic one-page form.

When I hide a row it shows up as approximately half a row of blank space on the main report. In this case the Visiblity setting is useless since there is too much space between subreports on the main report.sql

Does index defrag get logged?

I've noticed a huge transaction log size after having run an
index defragmentation. Does a defrag get written to the transaction
log really? (Assuming the full recovery model.)I've noticed a huge transaction log size after having run an
index defragmentation. Does a defrag get written to the transaction
log really? (Assuming the full recovery model.)

See this ...Link (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx)

does dbcc checkdb increase size of data files ?

Does DBCC checkdb increase the size of data files ? I have a 300GB database
thats almost full and wanted to know whether the size would grow when I run
DBCC checkdb ?
Also, does the maintenance plan under integrity - > check database integrity
issue the DBCC checkdb statement underlying that ? Using SQL 2000
It should not increase the size of your data files, although it could
increase the size of tempdb data files.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eo2NeSELFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Does DBCC checkdb increase the size of data files ? I have a 300GB
> database
> thats almost full and wanted to know whether the size would grow when I
> run
> DBCC checkdb ?
> Also, does the maintenance plan under integrity - > check database
> integrity
> issue the DBCC checkdb statement underlying that ? Using SQL 2000
>
|||Hi,
In addition to Wayne's post, check database integrity in maintenance plan
issues DBCC checkdb command.
Thanks
Yogish

does dbcc checkdb increase size of data files ?

Does DBCC checkdb increase the size of data files ? I have a 300GB database
thats almost full and wanted to know whether the size would grow when I run
DBCC checkdb ?
Also, does the maintenance plan under integrity - > check database integrity
issue the DBCC checkdb statement underlying that ? Using SQL 2000It should not increase the size of your data files, although it could
increase the size of tempdb data files.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eo2NeSELFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Does DBCC checkdb increase the size of data files ? I have a 300GB
> database
> thats almost full and wanted to know whether the size would grow when I
> run
> DBCC checkdb ?
> Also, does the maintenance plan under integrity - > check database
> integrity
> issue the DBCC checkdb statement underlying that ? Using SQL 2000
>|||Hi,
In addition to Wayne's post, check database integrity in maintenance plan
issues DBCC checkdb command.
Thanks
Yogish

does dbcc checkdb increase size of data files ?

Does DBCC checkdb increase the size of data files ? I have a 300GB database
thats almost full and wanted to know whether the size would grow when I run
DBCC checkdb ?
Also, does the maintenance plan under integrity - > check database integrity
issue the DBCC checkdb statement underlying that ? Using SQL 2000It should not increase the size of your data files, although it could
increase the size of tempdb data files.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eo2NeSELFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Does DBCC checkdb increase the size of data files ? I have a 300GB
> database
> thats almost full and wanted to know whether the size would grow when I
> run
> DBCC checkdb ?
> Also, does the maintenance plan under integrity - > check database
> integrity
> issue the DBCC checkdb statement underlying that ? Using SQL 2000
>|||Hi,
In addition to Wayne's post, check database integrity in maintenance plan
issues DBCC checkdb command.
--
Thanks
Yogish

Does DB Size decrease when I delete a huge table ?

Hi,
My DB size (Right click on DB Name, Data Files tab, Space Allocated field) was 10914 MB.
I delete a huge table (1.2 million records * 15 columns).
I checked the db size again. It didnt change.
Shouldn't it decrease because I delete a huge table ??The size will only decrease if you use the shrinkdb command.|||Originally posted by rokslide
The size will only decrease if you use the shrinkdb command.

When and why should we use Shrink DB ??|||Originally posted by rokslide
The size will only decrease if you use the shrinkdb command.

Would the deleted data be still on the database physical file ?|||The use of shrinkdb depends on who you talk to,... I really don't have a theory about it except to say that if the database is taking up too much space, shrink it...

As for the data still being in the database file... I would guess (and it's just a guess) that it would still be there but it would be unaccessible via the database. I think the delete would work much like deleting a file off your hard drive, all that is really deleted is the file header information, the data is still there, but you can't access it unless you use a tool to specificly locate the data.

Does that make sense? Can anyone else offer more advise??|||Originally posted by rokslide
The use of shrinkdb depends on who you talk to,... I really don't have a theory about it except to say that if the database is taking up too much space, shrink it...

As for the data still being in the database file... I would guess (and it's just a guess) that it would still be there but it would be unaccessible via the database. I think the delete would work much like deleting a file off your hard drive, all that is really deleted is the file header information, the data is still there, but you can't access it unless you use a tool to specificly locate the data.

Does that make sense? Can anyone else offer more advise??

Can u give a quick example of how I use ShrinkDB ?|||Originally posted by forXLDB
Can u give a quick example of how I use ShrinkDB ?
How do I know what size to reduce to ?
(If the size doesnt decrease even after deleting tables, how do i know what must be the exact size occupied by all the tables)...
I would have deleted and created lots of huge files involving around a million records atleast 10 times..

and above all, does the size effect the sql server performance in any way ?|||straight from the books online

This example decreases the size of the files in the UserDB user database to allow 10 percent free space in the files of UserDB.

DBCC SHRINKDATABASE (UserDB, 10)
GO

or you can do it through the EM|||Originally posted by rokslide
straight from the books online

or you can do it through the EM

i just did use for another test database, it shrunk more than half the size...

thx anyway !!1

Still looking for the answer on any performance degradation if the db file size is more ?|||Guessing once again I would say that it shouldn't affect the speed to any great extent,.. fragmentation of the database file would... there are some commands/functions that you can use to find out this kinda stuff but I have never used them so I can't really help alot I'm sorry.|||Originally posted by rokslide
Guessing once again I would say that it shouldn't affect the speed to any great extent,.. fragmentation of the database file would... there are some commands/functions that you can use to find out this kinda stuff but I have never used them so I can't really help alot I'm sorry.
thx again...

lemme know any functions !!!|||Since the log file takes the big size of the database, you can use the following script to reduce the file size:

USE DatabaseName
GO
DBCC SHRINKFILE ('DatabaseName_Log', Size)
GO

Here "Size" is the number of MB, i.e., if you put 100, that means the file will be shrinked to 100 MB.|||Since you are already working in em - when you right click on the database go to all task ->shrink database ->files... Under database file, look at the data file and log file. What is the current size/space used ? Are you backing up the database/transaction log ? What is the recovery model ?

What is the performance issue ?|||You can use "dbcc showcontig" to display fragmentation info. Yes performance will be affected - you need to either drop/recreate the indexes, use dbcc dbreindex or dbcc indexdefrag. Since you deleted so many records (more than half), fragmentation is a good start for performance issues.|||Do you have access to Books Online (BOL) ?|||An addition to rnealejr's post on dbcc indexdefrag and recreating indexes. If you have tables with a large amount of data input or modification take a good look at the indexes. If you use a non-seed value for your index then increasing the fillfactor number can help to reduce fragmentation in the future. I.E if your customer table uses first four characters of the name plus the zipcode for a customerID then you would be inserting new records into the middle of the table frequently. Once the fillfactor is used up then the next record inserted will create a new page and now your data is getting fragmented which does impact performance as mentioned be other posters.

Now that you've opened this pandoras box, you could spend a week in BOL learning how to tweak each tables index based on usage, calculating how many records fit on a page, use of varchar over char, benifits/penalties of seed indexes...............|||Originally posted by rnealejr
Do you have access to Books Online (BOL) ?
Yes. I can read BOL

Does anyone know the Maximum Size of GetRowSet?

Does anyone know the maximum size of sql server's GetRowSet command?
We are trying to a 5gb table using SqlServer2000's GetRowSet command, and
after it fails with a 'connection error', we have found a suspicious temp
file that is almost exactly 2gb in size in the os temp folder.
We have plenty of drive space, plenty of tempdb space, we have checked
everything we can think of, moved the data to the temp HD and still it
fails approx 40% into the load.
The file we are reading is a foxPro file and we are trying to Get the data
into Sql Server.
Anyone have any thoughts on this?
Thanks.
Charles..it is possible this was programmed with 32-bit convention,
in which 2 or 3GB will be the limit. If you have W2K AS,
try booting with the /3GB flag and see if the temp file
goes to exactly 3GB before bombing out
>--Original Message--
>Does anyone know the maximum size of sql server's
GetRowSet command?
>We are trying to a 5gb table using SqlServer2000's
GetRowSet command, and
>after it fails with a 'connection error', we have found a
suspicious temp
>file that is almost exactly 2gb in size in the os temp
folder.
>We have plenty of drive space, plenty of tempdb space, we
have checked
>everything we can think of, moved the data to the temp
HD and still it
>fails approx 40% into the load.
>The file we are reading is a foxPro file and we are
trying to Get the data
>into Sql Server.
>Anyone have any thoughts on this?
>Thanks.
> Charles..
>
>.
>

Does anyone know a utility/batch script to Warn when close to Max DB size

Hello Group
Does anyone know of a utility or Batch script (osql or Windows cmd or mix)
to Warn an Admin when a MSDE DB is closing in on 2 GB in size (or 2005
Express closes in on 4 GB).
Any leads or tips will be appriciated highly
Best regards
Uffe
hi Uffe,
Uffe Bak wrote:
> Hello Group
> Does anyone know of a utility or Batch script (osql or Windows cmd or
> mix) to Warn an Admin when a MSDE DB is closing in on 2 GB in size
> (or 2005 Express closes in on 4 GB).
> Any leads or tips will be appriciated highly
> Best regards
> Uffe
in MSDE you can perhaps schedule a job defined to include a way to gather
that kind of data, using direct access to the sysfiles database table or via
DBCC SHOWFILESTATS..
you can then validate that data against a userdefined treshold and notify
someone via NET SEND or mail via SMPT alternative, like exploded in
http://www.karaszi.com/sqlserver/info_no_mapi.asp,
http://www.dbmaint.com/SmtpAlerter.asp , thus defining a kind of alert..
PRINT 'MSDE';
PRINT '--';
USE Northwind;
PRINT 'sp_spaceused';
EXEC sp_spaceused @.updateusage = 'TRUE';
PRINT '--';
PRINT 'DBCC SHOWFILESTATS';
CREATE TABLE #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(1024),
filename varchar(1024)
);
INSERT INTO #tmp_sfs
EXECUTE('DBCC SHOWFILESTATS');
SELECT LEFT(DB_NAME(),10), (SUM(totalextents) * 64) / 1024 AS [totalextents
in MB], (SUM(usedextents) * 64) /1024 AS [usedextents in MB]
FROM #tmp_sfs;
DROP TABLE #tmp_sfs;
PRINT '--';
PRINT 'sysdatabase query';
SELECT LEFT(DB_NAME(),10) AS [Database], sum(convert(float,size)) * (8192.0
/1024.0) /1024.0 AS [Size in MB for Data files]
FROM dbo.sysfiles
WHERE (status & 0x40) <> 0x40;
SQLExpress does not provide the SQL Server Agent, so you can perhaps rely on
the native OS scheduler for something similar.. as regards the SMPT
integration, you have to (possibly) write your own CLR based mailer
solution.. I personally will... then, from the OS scheduler, you'll execute
SqlCmd command line tool to perform the SQLExpress connection and data
retrival...
just an idea..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks to Andrea and Satya for quick answers
I will look into your suggestions and return if I find a solution.
I also looked up some Scripting options, so there are as usual more than one
road to Rome
Best regards
Uffe Bak

Does Alocating Database files affect Table size?

I am having an issue with allocated sizes verse actual
data and index sizes in some of my large fact tables.
These tables are recreated once a week with a default fill
factor size of 95%. These tables never receive any
insert, update, or delete transactions running against
them.
I have several different RAID arrays that I am using for
performance and maintenance issues. On my data and index
arrays, through enterprise manager, I have allocated 80%
of the disk space to my databases. This was done so that
fields would not have to grow.
My question is because I have allocated a fix size to my
database files does this mean the my tables will receive
this allocated space? In other words does this allocated
space get distributed amongst existing tables in a
database or is it pooled in some way?
Thanks,
Jonathan Lacefield
MCDBAWhen you allocated a fix size of your db or log file, the file grows with
empty space to that size, you shall also disable automatically file grow
since you have allocated all the space that will be ever needed or set it to
0.
Yes as data is added to the table they will be place on the empty space
allocated to the file, Once the the file is full no more data will be able
to be added.
Yovan Fernandez
P.S
If you have 2 dbs on the same disk and yor disk zise is 40GB
you have to remember you cannot allocate 80% of 40GB to each DB.
"Jonathan Lacefield" <jonathan.lacefield@.solutionbuilders.com> wrote in
message news:00fe01c376e4$97c929f0$a101280a@.phx.gbl...
> I am having an issue with allocated sizes verse actual
> data and index sizes in some of my large fact tables.
> These tables are recreated once a week with a default fill
> factor size of 95%. These tables never receive any
> insert, update, or delete transactions running against
> them.
> I have several different RAID arrays that I am using for
> performance and maintenance issues. On my data and index
> arrays, through enterprise manager, I have allocated 80%
> of the disk space to my databases. This was done so that
> fields would not have to grow.
> My question is because I have allocated a fix size to my
> database files does this mean the my tables will receive
> this allocated space? In other words does this allocated
> space get distributed amongst existing tables in a
> database or is it pooled in some way?
> Thanks,
> Jonathan Lacefield
> MCDBA

Does a database need contiguous space?

Hello,
We had a problem that we're trying to figure out. We have a database that's
about 3.5GB in size on a partition with about 10GB of free space. The
database appeared normal, however when the client tried to write data to it
we'd receive a write error message.
We recreated the database on a different partition with 40GB free and it
works fine now. However we're still trying to figure out why the original
database failed and there comes the question-does SQL need contiguous free
space to work? The drive may be fairly fragmented and while there is ~10GB
free there may not be much contiguous space.
Everything else with SQL appears normal.
Thanks!Although a DB doesn't "need" contiguous space, it generally performs better
if it does have it.
What specifically is the error you get? Does the SQL Server service account
have write permission on that partition?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Mike Chiodo" <MikeChiodo@.discussions.microsoft.com> wrote in message
news:B860E70E-2CC7-4644-A624-C21EB0373BCF@.microsoft.com...
Hello,
We had a problem that we're trying to figure out. We have a database that's
about 3.5GB in size on a partition with about 10GB of free space. The
database appeared normal, however when the client tried to write data to it
we'd receive a write error message.
We recreated the database on a different partition with 40GB free and it
works fine now. However we're still trying to figure out why the original
database failed and there comes the question-does SQL need contiguous free
space to work? The drive may be fairly fragmented and while there is ~10GB
free there may not be much contiguous space.
Everything else with SQL appears normal.
Thanks!|||Yes, the SQL server account has permission to write to that partition. As
far as the error message-it was an error generated by the program not a SQL
error.
I suspect the database was simply corrupt but don't know for certain.
Thanks for your answer-it helped clear up a piece of the puzzle.
Mike
"Tom Moreau" wrote:
> Although a DB doesn't "need" contiguous space, it generally performs better
> if it does have it.
> What specifically is the error you get? Does the SQL Server service account
> have write permission on that partition?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Mike Chiodo" <MikeChiodo@.discussions.microsoft.com> wrote in message
> news:B860E70E-2CC7-4644-A624-C21EB0373BCF@.microsoft.com...
> Hello,
> We had a problem that we're trying to figure out. We have a database that's
> about 3.5GB in size on a partition with about 10GB of free space. The
> database appeared normal, however when the client tried to write data to it
> we'd receive a write error message.
> We recreated the database on a different partition with 40GB free and it
> works fine now. However we're still trying to figure out why the original
> database failed and there comes the question-does SQL need contiguous free
> space to work? The drive may be fairly fragmented and while there is ~10GB
> free there may not be much contiguous space.
> Everything else with SQL appears normal.
> Thanks!
>

DOCID Error when upgrading from to SQL 2005 FTK Size

Hi,

How to determine the size of FTK, please help,

Since the DOCID map in SQL Server 2005 is stored in the database, the size requirement on the database side has increased, but has reduced on the full-text catalog side. If a database contains a full-text index, you need to ensure that the file group associated with the base table has enough space to accommodate the additional space requirement for full-text indexes. Use the following formula to estimate the space required.
(2*FTK+ 34bytes) * RC
Where:
FTK = Full-Text Key Size
RC = Row Count of the Table

thanks,

Imran.

Hello, I am going to move this thread over to SQL Server Database Engine, since you are more likely to get help on there. Thanks!|||

Imran,

What specific DOCID error did you get? The below text is directly from the SQL Server 2005 Upgrade Advisor Books Online (BOL) topic "Increase database size to accommodate DOCID map" Did you check the crawl log to identify the error and increase the space of the file group? Note, the craw log is located in the LOG directory on the server where you have installed SQL Server 2005.

Below are two additional references that might be helpful to you depending upon the DOCID error you are getting:

"Additional space requirement for Full-Text Search - Because the document identifier (DOCID) map in SQL Server 2005 is stored in the database, the size requirement on the database side has increased. However, the size requirement on the full-text catalog side has decreased." - from: Breaking Changes to Full-Text Search in SQL Server 2005
http://msdn2.microsoft.com/en-us/library/ms143709.aspx

See Step 10 and screenshot for SQL 2005 upgrade wizard (search on Docid to find it quickly) in:
http://h20331.www2.hp.com/ActiveAnswers/downloads/Migrate_SQL2005_Integrity_1005.pdf

Regards,
John
SQL Full Text Search Blog
http://jtkane.spaces.live.com/

|||I would also like to know how to determine the FTK size....

DOCID Error when upgrading from to SQL 2005 FTK Size

Hi,

How to determine the size of FTK, please help,

Since the DOCID map in SQL Server 2005 is stored in the database, the size requirement on the database side has increased, but has reduced on the full-text catalog side. If a database contains a full-text index, you need to ensure that the file group associated with the base table has enough space to accommodate the additional space requirement for full-text indexes. Use the following formula to estimate the space required.
(2*FTK+ 34bytes) * RC
Where:
FTK = Full-Text Key Size
RC = Row Count of the Table

thanks,

Imran.

Hello, I am going to move this thread over to SQL Server Database Engine, since you are more likely to get help on there. Thanks!|||

Imran,

What specific DOCID error did you get? The below text is directly from the SQL Server 2005 Upgrade Advisor Books Online (BOL) topic "Increase database size to accommodate DOCID map" Did you check the crawl log to identify the error and increase the space of the file group? Note, the craw log is located in the LOG directory on the server where you have installed SQL Server 2005.

Below are two additional references that might be helpful to you depending upon the DOCID error you are getting:

"Additional space requirement for Full-Text Search - Because the document identifier (DOCID) map in SQL Server 2005 is stored in the database, the size requirement on the database side has increased. However, the size requirement on the full-text catalog side has decreased." - from: Breaking Changes to Full-Text Search in SQL Server 2005
http://msdn2.microsoft.com/en-us/library/ms143709.aspx

See Step 10 and screenshot for SQL 2005 upgrade wizard (search on Docid to find it quickly) in:
http://h20331.www2.hp.com/ActiveAnswers/downloads/Migrate_SQL2005_Integrity_1005.pdf

Regards,
John
SQL Full Text Search Blog
http://jtkane.spaces.live.com/

|||I would also like to know how to determine the FTK size....

Do you recommend storing images directly into the DB?

The images each are in Jpeg format (black and white, with some grey scale). File size wise, wouldnt be huge < 50 KB.

There wont be too many people using the system.

Would it be tedious to maintain? Many people recommend storing the path to the graphic instead.

Please advise. Thanks.98% of all articles or opinions I've seen reccomend storing them outside the DB. SQL is not a good storage mechanism for files.

HTH|||Originally posted by fused
The images each are in Jpeg format (black and white, with some grey scale). File size wise, wouldnt be huge < 50 KB.

There wont be too many people using the system.

Would it be tedious to maintain? Many people recommend storing the path to the graphic instead.

Please advise. Thanks.

We have a proffesianally built imaging system that stores all the images on the disk and just references them from the database.

I agree that they should be left outside the DB.|||Originally posted by jimpen
We have a proffesianally built imaging system that stores all the images on the disk and just references them from the database.

I agree that they should be left outside the DB.

Right now, we are planning a project to store some images. What's the scripting language that you use? Any advice? Thanks.

So you guys have a file server that has permission given to everyone? And when you upload the pic, the picture is moved to the shared file server? And a path is inserted into the sql server?

Please advise.|||I have an ASP app that does this sort of thing. I read through the directories, and display the file names as hyperlinks without even using a db connection. This method works really well if you can get the user to name the files in such a way that the links on the page look ok...which is normally not an easy task, but it's worked pretty well for me...But this is an IntrAnet app. , not a web app..|||We use ASP.Net with C# on Windows IIS. You know the IP of the computer, create a sub directory (eg /Images) and use a component that looks in this directory (we use Borland C# Builder but Visual Studio will have this probably as well). To let users upload their images, you can use ASPUpload (www.aspupload.com)|||Originally posted by fused
Right now, we are planning a project to store some images. What's the scripting language that you use? Any advice? Thanks.

So you guys have a file server that has permission given to everyone? And when you upload the pic, the picture is moved to the shared file server? And a path is inserted into the sql server?

Please advise.

Ours is provided by a VAR of http://www.imagesoft.com/.

Read permissions are given by group as well as write. We have different sections that are available to different groups depending on what they need/are allowed to see.