sql bit data type: file

Showing posts with label file. Show all posts

Sunday, March 25, 2012

Bookmarks (Management Studio)

How do you save bookmarks so you can use them next time you edit the saved query file?

I asked the same quesiton with 0 responses, I am creating a feedback suggestion now for it.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=351510&SiteID=1

Derek

vote here:

someone allready suggested this...

http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=7e600860-0662-44cf-bc9b-b6565e3d213c

|||Bookmarks in Management Studio are consist with Visual Studio. To use bookmarks your files need to be part of a solution/project, rather than just loose files. Yes, the environment allows the creation of bookmarks on files that are not part of a solution but these bookmarks are not persisted. When you create a solution, add a file, create some bookmarks, the bookmarks will be persisted and show up each time the solution is opened.|||My bookmarks do not show up when I open the solution. Is there a special way to save the bookmarks with the solution?|||

Here's the answer:

Bookmarks in Management Studio are consist with Visual Studio. To use bookmarks your files need to be part of a solution/project, rather than just loose files. Yes, the environment allows the creation of bookmarks on files that are not part of a solution but these bookmarks are not persisted. When you create a solution, add a file, create some bookmarks, the bookmarks will be persisted and show up each time the solution is opened.

|||My files are part of a solution. When I open the solution the query files are displayed in the solution file. I add bookmark to the query files, save all the files, and then close the solution. When I open the solution again the bookmarks that I created are all gone. It is not working like you stated.|||If you add bookmarks to a script in a database solution created in Visual Studio 2005, when you save the script the bookmarks persist.|||

Dan, I created a new project. Then I opened my .sql, added bookmarks, then added the .sql to the project. I closed and saved both the .sql and the project but, when I opened the project again, the bookmarks are gone. I'm a little new so I had our DBA come over and help me. He, too, doesn't understand why the bookmarks aren't there.

Do you have any suggestions? Any obscure tip or setting that I should know about? If not, can I assume it's a bug in SQL Server 2005? I see that someone "opened a ticket" but I wanted to ask before I assumed there was no answer.

Thanks for your help -- or anyone else with a suggestion,

Kate

|||Is this a bug with SQL Server Management Studio?|||

FYI:

There is feedback to Microsoft concerning this issue and even though it was suppose to be in this version, they said they would be considering it for the next release. If anyone else want bookmarks to persist in Management Studio, let Microsoft know in their Feedback.

sql

Bookmarks (Management Studio)

How do you save bookmarks so you can use them next time you edit the saved query file?

I asked the same quesiton with 0 responses, I am creating a feedback suggestion now for it.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=351510&SiteID=1

Derek

vote here:

someone allready suggested this...

http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=7e600860-0662-44cf-bc9b-b6565e3d213c

Here's the answer:

Thanks for your help -- or anyone else with a suggestion,

Kate

|||Is this a bug with SQL Server Management Studio?|||

FYI:

Monday, March 19, 2012

Blue shortcut arrow on Replication Monitor

What does a small blue arrow (like a file/folder shortcut) mean on the Repli
cation Monitor? It
shows on Replication Monitor, Publishers, Agents and Distribution Agents.See if this helps:
http://msdn.microsoft.com/library/e...ltools_8o6r.asp
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Shawn Clabough" <shawnc@.uidaho.edu> wrote in message
news:OvtlRu2nFHA.2860@.TK2MSFTNGP15.phx.gbl...
> What does a small blue arrow (like a file/folder shortcut) mean on the
> Replication Monitor? It shows on Replication Monitor, Publishers, Agents
> and Distribution Agents.

Blue shortcut arrow on Replication Monitor

What does a small blue arrow (like a file/folder shortcut) mean on the Replication Monitor? It
shows on Replication Monitor, Publishers, Agents and Distribution Agents.
See if this helps:
http://msdn.microsoft.com/library/en...tools_8o6r.asp
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Shawn Clabough" <shawnc@.uidaho.edu> wrote in message
news:OvtlRu2nFHA.2860@.TK2MSFTNGP15.phx.gbl...
> What does a small blue arrow (like a file/folder shortcut) mean on the
> Replication Monitor? It shows on Replication Monitor, Publishers, Agents
> and Distribution Agents.

Blue shortcut arrow on Replication Monitor

What does a small blue arrow (like a file/folder shortcut) mean on the Replication Monitor? It
shows on Replication Monitor, Publishers, Agents and Distribution Agents.See if this helps:
http://msdn.microsoft.com/library/en-us/replsql/repltools_8o6r.asp
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Shawn Clabough" <shawnc@.uidaho.edu> wrote in message
news:OvtlRu2nFHA.2860@.TK2MSFTNGP15.phx.gbl...
> What does a small blue arrow (like a file/folder shortcut) mean on the
> Replication Monitor? It shows on Replication Monitor, Publishers, Agents
> and Distribution Agents.

Sunday, March 11, 2012

Blocking seen when two filegroups are backed up in parallel

When I backup two filegroups in parallel I see one backup
blocking the other.
eg two filegroups consisting of one data file each. Both
data files live on the f drive. One filegroup backup
writes to the h drive, the other to the i drive. Running
the backups in parallel results in one backup blocking the
other.
I suspect this is because both my data files live on the f
drive. Can anyone confirm this as the problem ?
Thanks.It's probably more to do with the fact they share the same log file and
backups need access to the logs and will put certain locks on them that
prevents some things from happening in parallel like that.
--
Andrew J. Kelly
SQL Server MVP
"anon" <anonymous@.discussions.microsoft.com> wrote in message
news:0db801c39f02$40fa1cb0$a401280a@.phx.gbl...
> When I backup two filegroups in parallel I see one backup
> blocking the other.
> eg two filegroups consisting of one data file each. Both
> data files live on the f drive. One filegroup backup
> writes to the h drive, the other to the i drive. Running
> the backups in parallel results in one backup blocking the
> other.
> I suspect this is because both my data files live on the f
> drive. Can anyone confirm this as the problem ?
> Thanks.
>|||Andrew - thanks for the reply. I think you're right.
Despite what SQL 2000 Books Online says under the
section 'Optimizing Database, Differential Database, and
File Backup Performance', I never see parallel backups.
I've now placed my two data files on different logical
drives and create the backups on two other logical drives
and still I get one backup blocked by the other.
Seems like this particular performance benefit of using
file groups doesn't really exist :( Again, nice if someone
could confirm this.
>--Original Message--
>It's probably more to do with the fact they share the
same log file and
>backups need access to the logs and will put certain
locks on them that
>prevents some things from happening in parallel like that.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"anon" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0db801c39f02$40fa1cb0$a401280a@.phx.gbl...
>> When I backup two filegroups in parallel I see one
backup
>> blocking the other.
>> eg two filegroups consisting of one data file each. Both
>> data files live on the f drive. One filegroup backup
>> writes to the h drive, the other to the i drive. Running
>> the backups in parallel results in one backup blocking
the
>> other.
>> I suspect this is because both my data files live on
the f
>> drive. Can anyone confirm this as the problem ?
>> Thanks.
>
>.
>|||It can do "Parallel" backups as long as they aren't to the same database.
They are referring to backing up multiple db's at the same time.
--
Andrew J. Kelly
SQL Server MVP
"anon" <anonymous@.discussions.microsoft.com> wrote in message
news:08e001c39fa9$ea087ca0$a501280a@.phx.gbl...
> Andrew - thanks for the reply. I think you're right.
> Despite what SQL 2000 Books Online says under the
> section 'Optimizing Database, Differential Database, and
> File Backup Performance', I never see parallel backups.
> I've now placed my two data files on different logical
> drives and create the backups on two other logical drives
> and still I get one backup blocked by the other.
> Seems like this particular performance benefit of using
> file groups doesn't really exist :( Again, nice if someone
> could confirm this.
> >--Original Message--
> >It's probably more to do with the fact they share the
> same log file and
> >backups need access to the logs and will put certain
> locks on them that
> >prevents some things from happening in parallel like that.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"anon" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0db801c39f02$40fa1cb0$a401280a@.phx.gbl...
> >> When I backup two filegroups in parallel I see one
> backup
> >> blocking the other.
> >>
> >> eg two filegroups consisting of one data file each. Both
> >> data files live on the f drive. One filegroup backup
> >> writes to the h drive, the other to the i drive. Running
> >> the backups in parallel results in one backup blocking
> the
> >> other.
> >>
> >> I suspect this is because both my data files live on
> the f
> >> drive. Can anyone confirm this as the problem ?
> >>
> >> Thanks.
> >>
> >
> >
> >.
> >

Saturday, February 25, 2012

Blobs: File System or DB?

The conventional wisdom on where best to store large binary objects used to
be to store them on the file system because they were slowly processed in
the database. Is this still the case with the latest SQL Server?
We are designing a new system that will store many thousands of TIF files.
We would like to store them in the database because they would then be
coordinated with Backup, Restore, and Transaction issues. How much slower
is the processing of varbinary fields in the db versus the file system?
Thanks,
T"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:Oy9qbdKSIHA.6060@.TK2MSFTNGP05.phx.gbl...
> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
> Thanks,
> T
>
The following article discusses file system storage vs SQL Server 2005
BLOBs:
ftp://ftp.research.microsoft.com/pub/tr/TR-2006-45.pdf
In SQL Server 2008 there is a new feature called FILESTREAM that directly
addresses the problem. FILESTREAM permits both SQL and file-system access to
binary data while managing the files as if they were part of the database
for backup and transaction control purposes. From my own experience I have
found that FILESTREAM can outperform file-system access for files larger
than about 2MB (YMMV).
http://msdn2.microsoft.com/en-us/library/bb895234(SQL.100).aspx
David Portas|||> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
If you mean SQL Server 2005, then yes; if you mean SQL Server 2008, then
no... as David pointed out, FILESTREAM will address part of this problem
(but I am a little skeptical about performance of backups, concurrency, and
translation layer).

> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
Naturally I would expect a layer of overhead, depending on the number and
size of files you have, in translating the data back and forth between the
database and the application(s).
But, more importantly, and again depending on the number and size of files
you have, I am very sensitive to using expensive disk. I would be very
careful to place the files in a filegroup which is not on a very expensive
SAN (which your database might be using). High performance, fault-tolerant
disk space is very expensive, and it seems a waste to take this space away
from your database to use for, essentially, flat files. If you are using
locally attached storage then this isn't really an issue, but if the file
usage is going to be large, you might consider using a less expensive array
for the files themselves. (You can always keep backups of directory
structures as well, it;'s just very difficult to keep them point-in-time in
sync with the database.) Also keep this in mind if you use a 3rd party for
database hosting; they typically charge a lot more per TB of database
storage, versus flat file storage, so in that case make sure you weigh the
benefits you get from synchronizing your files and data, versus the hit it
takes on your budget.
A

Blobs: File System or DB?

The conventional wisdom on where best to store large binary objects used to
be to store them on the file system because they were slowly processed in
the database. Is this still the case with the latest SQL Server?
We are designing a new system that will store many thousands of TIF files.
We would like to store them in the database because they would then be
coordinated with Backup, Restore, and Transaction issues. How much slower
is the processing of varbinary fields in the db versus the file system?
Thanks,
T
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:Oy9qbdKSIHA.6060@.TK2MSFTNGP05.phx.gbl...
> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
> Thanks,
> T
>
The following article discusses file system storage vs SQL Server 2005
BLOBs:
ftp://ftp.research.microsoft.com/pub/tr/TR-2006-45.pdf
In SQL Server 2008 there is a new feature called FILESTREAM that directly
addresses the problem. FILESTREAM permits both SQL and file-system access to
binary data while managing the files as if they were part of the database
for backup and transaction control purposes. From my own experience I have
found that FILESTREAM can outperform file-system access for files larger
than about 2MB (YMMV).
http://msdn2.microsoft.com/en-us/library/bb895234(SQL.100).aspx
David Portas
|||> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
If you mean SQL Server 2005, then yes; if you mean SQL Server 2008, then
no... as David pointed out, FILESTREAM will address part of this problem
(but I am a little skeptical about performance of backups, concurrency, and
translation layer).

> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
Naturally I would expect a layer of overhead, depending on the number and
size of files you have, in translating the data back and forth between the
database and the application(s).
But, more importantly, and again depending on the number and size of files
you have, I am very sensitive to using expensive disk. I would be very
careful to place the files in a filegroup which is not on a very expensive
SAN (which your database might be using). High performance, fault-tolerant
disk space is very expensive, and it seems a waste to take this space away
from your database to use for, essentially, flat files. If you are using
locally attached storage then this isn't really an issue, but if the file
usage is going to be large, you might consider using a less expensive array
for the files themselves. (You can always keep backups of directory
structures as well, it;'s just very difficult to keep them point-in-time in
sync with the database.) Also keep this in mind if you use a 3rd party for
database hosting; they typically charge a lot more per TB of database
storage, versus flat file storage, so in that case make sure you weigh the
benefits you get from synchronizing your files and data, versus the hit it
takes on your budget.
A

Blobs: File System or DB?

The conventional wisdom on where best to store large binary objects used to
be to store them on the file system because they were slowly processed in
the database. Is this still the case with the latest SQL Server?
We are designing a new system that will store many thousands of TIF files.
We would like to store them in the database because they would then be
coordinated with Backup, Restore, and Transaction issues. How much slower
is the processing of varbinary fields in the db versus the file system?
Thanks,
T"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:Oy9qbdKSIHA.6060@.TK2MSFTNGP05.phx.gbl...
> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
> Thanks,
> T
>
The following article discusses file system storage vs SQL Server 2005
BLOBs:
ftp://ftp.research.microsoft.com/pub/tr/TR-2006-45.pdf
In SQL Server 2008 there is a new feature called FILESTREAM that directly
addresses the problem. FILESTREAM permits both SQL and file-system access to
binary data while managing the files as if they were part of the database
for backup and transaction control purposes. From my own experience I have
found that FILESTREAM can outperform file-system access for files larger
than about 2MB (YMMV).
http://msdn2.microsoft.com/en-us/library/bb895234(SQL.100).aspx
--
David Portas|||> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
If you mean SQL Server 2005, then yes; if you mean SQL Server 2008, then
no... as David pointed out, FILESTREAM will address part of this problem
(but I am a little skeptical about performance of backups, concurrency, and
translation layer).
> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
Naturally I would expect a layer of overhead, depending on the number and
size of files you have, in translating the data back and forth between the
database and the application(s).
But, more importantly, and again depending on the number and size of files
you have, I am very sensitive to using expensive disk. I would be very
careful to place the files in a filegroup which is not on a very expensive
SAN (which your database might be using). High performance, fault-tolerant
disk space is very expensive, and it seems a waste to take this space away
from your database to use for, essentially, flat files. If you are using
locally attached storage then this isn't really an issue, but if the file
usage is going to be large, you might consider using a less expensive array
for the files themselves. (You can always keep backups of directory
structures as well, it;'s just very difficult to keep them point-in-time in
sync with the database.) Also keep this in mind if you use a 3rd party for
database hosting; they typically charge a lot more per TB of database
storage, versus flat file storage, so in that case make sure you weigh the
benefits you get from synchronizing your files and data, versus the hit it
takes on your budget.
A

BLOBS vs. regular files

Over the weekend I was told that my SQL Server was going to start doubling as
a file server. I guess now the other option is to store files as BLOBS. I
have no experience with either scenario. Can anyone give me the procs/ cons
of doing it either way?
--
TIA,
ChrisRChris,
If they meant a file server for Word documents, executables, and so forth,
then the SQL Server will not be any help to you. You will have a server
running the SQL Server process and also hosting file services. This will
affect how you configure resources on the server since it will no longer be
dedicated.
If you are going to be getting only files associated with a database, then
there are plenty of discussions and opinions.
Try Googling 'blob file sql versus' and read the several hundred comments.
:-)
Here is a link to the thinking behind what the TerraServer guys did.
www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx
RLF
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:8BCD21FE-97EC-4B1E-84FA-57A40844A4A1@.microsoft.com...
> Over the weekend I was told that my SQL Server was going to start doubling
> as
> a file server. I guess now the other option is to store files as BLOBS. I
> have no experience with either scenario. Can anyone give me the procs/
> cons
> of doing it either way?
> --
> TIA,
> ChrisR|||The pro for BLOBs is very simple, the "files" are part of the database, are
kept in sync transactionally, participate in the backups, and can be
processed by SQL. In other words, they have all the traditional benefits of
a database system.
The cons are more extensive. They don't appear in the filesystem namespace.
Before they can be processed by a regular program they have to be copied
into a temporary file, they aren't structured as efficiently as files for
bulk copying (and can't be directly copied by kernel mode services). In
other words, performance is generally poor by comparison.
Now as a database guy I'd be inclined to store just about everything in the
database, get the management/integrity benefits, and give up some
performance. But realistically
usage would dictate which model I would use. The more the object will need
to be processed by an external file-based application the more likely I'd be
to store it in the file system. The more important it was to keep it
tightly bound to the data in the database system the more likely I'd be to
store it in a blob. For example, say you want to store an employee's photo
with their personnel data. I'd store that in a Blob in the database.
But if I were creating a library of photographs that were going to be
primarily accessed by Photoshop, and the purpose of the database was to make
searching etc easier, then I'd probably keep the photographs in the
filesystem with pointers from the database. Of course, then I'd have to
create more complex backup/resore procedures for my shop.
--
Hal Berenson, President
PredictableIT, LLC
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:8BCD21FE-97EC-4B1E-84FA-57A40844A4A1@.microsoft.com...
> Over the weekend I was told that my SQL Server was going to start doubling
> as
> a file server. I guess now the other option is to store files as BLOBS. I
> have no experience with either scenario. Can anyone give me the procs/
> cons
> of doing it either way?
> --
> TIA,
> ChrisR

BLOBS vs. regular files

Over the weekend I was told that my SQL Server was going to start doubling as
a file server. I guess now the other option is to store files as BLOBS. I
have no experience with either scenario. Can anyone give me the procs/ cons
of doing it either way?
TIA,
ChrisR
Chris,
If they meant a file server for Word documents, executables, and so forth,
then the SQL Server will not be any help to you. You will have a server
running the SQL Server process and also hosting file services. This will
affect how you configure resources on the server since it will no longer be
dedicated.
If you are going to be getting only files associated with a database, then
there are plenty of discussions and opinions.
Try Googling 'blob file sql versus' and read the several hundred comments.
:-)
Here is a link to the thinking behind what the TerraServer guys did.
http://www.microsoft.com/technet/pro...rt3/c1161.mspx
RLF
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:8BCD21FE-97EC-4B1E-84FA-57A40844A4A1@.microsoft.com...
> Over the weekend I was told that my SQL Server was going to start doubling
> as
> a file server. I guess now the other option is to store files as BLOBS. I
> have no experience with either scenario. Can anyone give me the procs/
> cons
> of doing it either way?
> --
> TIA,
> ChrisR
|||The pro for BLOBs is very simple, the "files" are part of the database, are
kept in sync transactionally, participate in the backups, and can be
processed by SQL. In other words, they have all the traditional benefits of
a database system.
The cons are more extensive. They don't appear in the filesystem namespace.
Before they can be processed by a regular program they have to be copied
into a temporary file, they aren't structured as efficiently as files for
bulk copying (and can't be directly copied by kernel mode services). In
other words, performance is generally poor by comparison.
Now as a database guy I'd be inclined to store just about everything in the
database, get the management/integrity benefits, and give up some
performance. But realistically
usage would dictate which model I would use. The more the object will need
to be processed by an external file-based application the more likely I'd be
to store it in the file system. The more important it was to keep it
tightly bound to the data in the database system the more likely I'd be to
store it in a blob. For example, say you want to store an employee's photo
with their personnel data. I'd store that in a Blob in the database.
But if I were creating a library of photographs that were going to be
primarily accessed by Photoshop, and the purpose of the database was to make
searching etc easier, then I'd probably keep the photographs in the
filesystem with pointers from the database. Of course, then I'd have to
create more complex backup/resore procedures for my shop.
Hal Berenson, President
PredictableIT, LLC
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:8BCD21FE-97EC-4B1E-84FA-57A40844A4A1@.microsoft.com...
> Over the weekend I was told that my SQL Server was going to start doubling
> as
> a file server. I guess now the other option is to store files as BLOBS. I
> have no experience with either scenario. Can anyone give me the procs/
> cons
> of doing it either way?
> --
> TIA,
> ChrisR

BLOBS vs. regular files

Over the weekend I was told that my SQL Server was going to start doubling a
s
a file server. I guess now the other option is to store files as BLOBS. I
have no experience with either scenario. Can anyone give me the procs/ cons
of doing it either way?
--
TIA,
ChrisRChris,
If they meant a file server for Word documents, executables, and so forth,
then the SQL Server will not be any help to you. You will have a server
running the SQL Server process and also hosting file services. This will
affect how you configure resources on the server since it will no longer be
dedicated.
If you are going to be getting only files associated with a database, then
there are plenty of discussions and opinions.
Try Googling 'blob file sql versus' and read the several hundred comments.
:-)
Here is a link to the thinking behind what the TerraServer guys did.
www.microsoft.com/technet/prodtechn...art3/c1161.mspx
RLF
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:8BCD21FE-97EC-4B1E-84FA-57A40844A4A1@.microsoft.com...
> Over the weekend I was told that my SQL Server was going to start doubling
> as
> a file server. I guess now the other option is to store files as BLOBS. I
> have no experience with either scenario. Can anyone give me the procs/
> cons
> of doing it either way?
> --
> TIA,
> ChrisR|||The pro for BLOBs is very simple, the "files" are part of the database, are
kept in sync transactionally, participate in the backups, and can be
processed by SQL. In other words, they have all the traditional benefits of
a database system.
The cons are more extensive. They don't appear in the filesystem namespace.
Before they can be processed by a regular program they have to be copied
into a temporary file, they aren't structured as efficiently as files for
bulk copying (and can't be directly copied by kernel mode services). In
other words, performance is generally poor by comparison.
Now as a database guy I'd be inclined to store just about everything in the
database, get the management/integrity benefits, and give up some
performance. But realistically
usage would dictate which model I would use. The more the object will need
to be processed by an external file-based application the more likely I'd be
to store it in the file system. The more important it was to keep it
tightly bound to the data in the database system the more likely I'd be to
store it in a blob. For example, say you want to store an employee's photo
with their personnel data. I'd store that in a Blob in the database.
But if I were creating a library of photographs that were going to be
primarily accessed by Photoshop, and the purpose of the database was to make
searching etc easier, then I'd probably keep the photographs in the
filesystem with pointers from the database. Of course, then I'd have to
create more complex backup/resore procedures for my shop.
--
Hal Berenson, President
PredictableIT, LLC
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:8BCD21FE-97EC-4B1E-84FA-57A40844A4A1@.microsoft.com...
> Over the weekend I was told that my SQL Server was going to start doubling
> as
> a file server. I guess now the other option is to store files as BLOBS. I
> have no experience with either scenario. Can anyone give me the procs/
> cons
> of doing it either way?
> --
> TIA,
> ChrisR

BLOBS in a sql server database - yes or no?

Hi,
we have a sql server 2000 database running that manages amout 10 million
invoices (growing at 10000/day). in addition, we have a file server that
holds all invoices as pdf files. the problem is that it is extremely
difficult to backup these files or to do other file-realted task since there
are so many of them.
thats why we started thinking abut moving all these files directly into the
database as binary objects (BLOBs) so that invoice meta data and content is
at the same place. the average file size is about 40k. im not a big fan of
blobs but in this case it seems to be a good thing since backup and file
management wouldnt be a problem anymore (among other advantages).
my question is if you already experienced having large amounts of blobs in
the database and if you think its a good idea or not. i don't want our
overall query performance to be affected in any way (i know that sql server
stores pointer to the blob data in the tables so it shouldnt have a great
impact on performance, should it?). i'm interested in your experiences and
recommendations.
btw.: we would upgrade the hardware to something like this:
2 x Xeon 64, 3.6 GHz
8 GB RAM
10-14 disk drives for a total of 1-2 TB
i think it should be enough.
thanks in advance,
Benjamin Janecke
The knock on blobs in the database is really that they are inefficient for
the application that is going to process them. You can't directly stream
from the database out over a network file/web protocol as you can from the
filesystem, and most applications read from the file system so that you have
to first copy the blob into a file in order to process it. The benefit of
storing what are essentially files in blobs is, as you describe below, the
management benefits. From syncronized backup to avoiding broken links. We,
for example, put the T&Cs for our promotion codes in the database. If you
want a description of the promotion then you click on a hyperlink and we pop
up a page populated from the database. This isn't a super high volume
occurence so we aren't worried about the extra overhead involved. What we
gain is we don't have to worry about a link to a file describing the promo
code getting broken and the user getting either no description or the wrong
description.
As you already know the blob (unless it is very small) isn't stored in the
row and thus there is little or no performance impact on normal queries from
having the blob in the database.
The preeminent example of storing blobs in the database is the Terraserver
(http://terraserver.microsoft.com) which stores multiple Terabytes of
imagery in blobs and serves it out over the internet. This has been running
successfully since 1998, at times handling a few million hits per day. I
spent a few sleepless nights helping deal with problems the week it went
live (and took a lot more hits then we'd expected), and none of those were
related to the use of blobs.
Personally, my criteria for storing your invoices in the database would
involve two decisions. One is how long you wish to retain them (short, then
don't bother storing them in the database) and the other is how frequently
they are accessed. Since keeping invoices in this manner would typically be
for archival purposes and they would almost never be opened, you really
don't care about the performance hit versus keeping them in the file system
when one needs to be displayed. So the management benefits would outweigh
the performance negatives.
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in
message news:A5EF2AD5-B2F1-48BD-A9A7-41D42D63301F@.microsoft.com...
> Hi,
> we have a sql server 2000 database running that manages amout 10 million
> invoices (growing at 10000/day). in addition, we have a file server that
> holds all invoices as pdf files. the problem is that it is extremely
> difficult to backup these files or to do other file-realted task since
> there
> are so many of them.
> thats why we started thinking abut moving all these files directly into
> the
> database as binary objects (BLOBs) so that invoice meta data and content
> is
> at the same place. the average file size is about 40k. im not a big fan of
> blobs but in this case it seems to be a good thing since backup and file
> management wouldnt be a problem anymore (among other advantages).
> my question is if you already experienced having large amounts of blobs in
> the database and if you think its a good idea or not. i don't want our
> overall query performance to be affected in any way (i know that sql
> server
> stores pointer to the blob data in the tables so it shouldnt have a great
> impact on performance, should it?). i'm interested in your experiences and
> recommendations.
> btw.: we would upgrade the hardware to something like this:
> 2 x Xeon 64, 3.6 GHz
> 8 GB RAM
> 10-14 disk drives for a total of 1-2 TB
> i think it should be enough.
> thanks in advance,
> Benjamin Janecke
|||thanks for your very informative post.
we store the invoices for both archive purposes and daily access. the
database is part of an application that enables our customers to view their
invoices online. the invoices can also be accessed by our customer care
employees. in addition, we provide functionality to send invoices via e-mail
etc. in total, i think we have more than 1000 requests a day for pdf files,
but much less than 10000, so its not a big deal.
the good news is that the application doesn't depend on accessing the
invoices as files cause we only read them to deliver them as binary data to
the webbrowser (using special content type). therefore it wouldn't be a
problem to get binary data back from the database instead of opening physical
files. for e-mail-sending it is also possible to include binary data as
attachment instead of providing attachment files. thus, i don't think there
will be a big negative performance impact on the application except that the
data has to go through the network.
So, we will propably give it a try...
best regards,
Benjamin Janecke
"Hal Berenson" wrote:

> The knock on blobs in the database is really that they are inefficient for
> the application that is going to process them. You can't directly stream
> from the database out over a network file/web protocol as you can from the
> filesystem, and most applications read from the file system so that you have
> to first copy the blob into a file in order to process it. The benefit of
> storing what are essentially files in blobs is, as you describe below, the
> management benefits. From syncronized backup to avoiding broken links. We,
> for example, put the T&Cs for our promotion codes in the database. If you
> want a description of the promotion then you click on a hyperlink and we pop
> up a page populated from the database. This isn't a super high volume
> occurence so we aren't worried about the extra overhead involved. What we
> gain is we don't have to worry about a link to a file describing the promo
> code getting broken and the user getting either no description or the wrong
> description.
> As you already know the blob (unless it is very small) isn't stored in the
> row and thus there is little or no performance impact on normal queries from
> having the blob in the database.
> The preeminent example of storing blobs in the database is the Terraserver
> (http://terraserver.microsoft.com) which stores multiple Terabytes of
> imagery in blobs and serves it out over the internet. This has been running
> successfully since 1998, at times handling a few million hits per day. I
> spent a few sleepless nights helping deal with problems the week it went
> live (and took a lot more hits then we'd expected), and none of those were
> related to the use of blobs.
> Personally, my criteria for storing your invoices in the database would
> involve two decisions. One is how long you wish to retain them (short, then
> don't bother storing them in the database) and the other is how frequently
> they are accessed. Since keeping invoices in this manner would typically be
> for archival purposes and they would almost never be opened, you really
> don't care about the performance hit versus keeping them in the file system
> when one needs to be displayed. So the management benefits would outweigh
> the performance negatives.
> --
> Hal Berenson, President
> PredictableIT, LLC
> http://www.predictableit.com
>
> "Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in
> message news:A5EF2AD5-B2F1-48BD-A9A7-41D42D63301F@.microsoft.com...
>
>

BLOBS in a sql server database - yes or no?

Hi,
we have a sql server 2000 database running that manages amout 10 million
invoices (growing at 10000/day). in addition, we have a file server that
holds all invoices as pdf files. the problem is that it is extremely
difficult to backup these files or to do other file-realted task since there
are so many of them.
thats why we started thinking abut moving all these files directly into the
database as binary objects (BLOBs) so that invoice meta data and content is
at the same place. the average file size is about 40k. im not a big fan of
blobs but in this case it seems to be a good thing since backup and file
management wouldnt be a problem anymore (among other advantages).
my question is if you already experienced having large amounts of blobs in
the database and if you think its a good idea or not. i don't want our
overall query performance to be affected in any way (i know that sql server
stores pointer to the blob data in the tables so it shouldnt have a great
impact on performance, should it?). i'm interested in your experiences and
recommendations.
btw.: we would upgrade the hardware to something like this:
2 x Xeon 64, 3.6 GHz
8 GB RAM
10-14 disk drives for a total of 1-2 TB
i think it should be enough.
thanks in advance,
Benjamin JaneckeThe knock on blobs in the database is really that they are inefficient for
the application that is going to process them. You can't directly stream
from the database out over a network file/web protocol as you can from the
filesystem, and most applications read from the file system so that you have
to first copy the blob into a file in order to process it. The benefit of
storing what are essentially files in blobs is, as you describe below, the
management benefits. From syncronized backup to avoiding broken links. We,
for example, put the T&Cs for our promotion codes in the database. If you
want a description of the promotion then you click on a hyperlink and we pop
up a page populated from the database. This isn't a super high volume
occurence so we aren't worried about the extra overhead involved. What we
gain is we don't have to worry about a link to a file describing the promo
code getting broken and the user getting either no description or the wrong
description.
As you already know the blob (unless it is very small) isn't stored in the
row and thus there is little or no performance impact on normal queries from
having the blob in the database.
The preeminent example of storing blobs in the database is the Terraserver
(http://terraserver.microsoft.com) which stores multiple Terabytes of
imagery in blobs and serves it out over the internet. This has been running
successfully since 1998, at times handling a few million hits per day. I
spent a few sleepless nights helping deal with problems the week it went
live (and took a lot more hits then we'd expected), and none of those were
related to the use of blobs.
Personally, my criteria for storing your invoices in the database would
involve two decisions. One is how long you wish to retain them (short, then
don't bother storing them in the database) and the other is how frequently
they are accessed. Since keeping invoices in this manner would typically be
for archival purposes and they would almost never be opened, you really
don't care about the performance hit versus keeping them in the file system
when one needs to be displayed. So the management benefits would outweigh
the performance negatives.
--
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in
message news:A5EF2AD5-B2F1-48BD-A9A7-41D42D63301F@.microsoft.com...
> Hi,
> we have a sql server 2000 database running that manages amout 10 million
> invoices (growing at 10000/day). in addition, we have a file server that
> holds all invoices as pdf files. the problem is that it is extremely
> difficult to backup these files or to do other file-realted task since
> there
> are so many of them.
> thats why we started thinking abut moving all these files directly into
> the
> database as binary objects (BLOBs) so that invoice meta data and content
> is
> at the same place. the average file size is about 40k. im not a big fan of
> blobs but in this case it seems to be a good thing since backup and file
> management wouldnt be a problem anymore (among other advantages).
> my question is if you already experienced having large amounts of blobs in
> the database and if you think its a good idea or not. i don't want our
> overall query performance to be affected in any way (i know that sql
> server
> stores pointer to the blob data in the tables so it shouldnt have a great
> impact on performance, should it?). i'm interested in your experiences and
> recommendations.
> btw.: we would upgrade the hardware to something like this:
> 2 x Xeon 64, 3.6 GHz
> 8 GB RAM
> 10-14 disk drives for a total of 1-2 TB
> i think it should be enough.
> thanks in advance,
> Benjamin Janecke|||thanks for your very informative post.
we store the invoices for both archive purposes and daily access. the
database is part of an application that enables our customers to view their
invoices online. the invoices can also be accessed by our customer care
employees. in addition, we provide functionality to send invoices via e-mail
etc. in total, i think we have more than 1000 requests a day for pdf files,
but much less than 10000, so its not a big deal.
the good news is that the application doesn't depend on accessing the
invoices as files cause we only read them to deliver them as binary data to
the webbrowser (using special content type). therefore it wouldn't be a
problem to get binary data back from the database instead of opening physical
files. for e-mail-sending it is also possible to include binary data as
attachment instead of providing attachment files. thus, i don't think there
will be a big negative performance impact on the application except that the
data has to go through the network.
So, we will propably give it a try...
best regards,
Benjamin Janecke
"Hal Berenson" wrote:
> The knock on blobs in the database is really that they are inefficient for
> the application that is going to process them. You can't directly stream
> from the database out over a network file/web protocol as you can from the
> filesystem, and most applications read from the file system so that you have
> to first copy the blob into a file in order to process it. The benefit of
> storing what are essentially files in blobs is, as you describe below, the
> management benefits. From syncronized backup to avoiding broken links. We,
> for example, put the T&Cs for our promotion codes in the database. If you
> want a description of the promotion then you click on a hyperlink and we pop
> up a page populated from the database. This isn't a super high volume
> occurence so we aren't worried about the extra overhead involved. What we
> gain is we don't have to worry about a link to a file describing the promo
> code getting broken and the user getting either no description or the wrong
> description.
> As you already know the blob (unless it is very small) isn't stored in the
> row and thus there is little or no performance impact on normal queries from
> having the blob in the database.
> The preeminent example of storing blobs in the database is the Terraserver
> (http://terraserver.microsoft.com) which stores multiple Terabytes of
> imagery in blobs and serves it out over the internet. This has been running
> successfully since 1998, at times handling a few million hits per day. I
> spent a few sleepless nights helping deal with problems the week it went
> live (and took a lot more hits then we'd expected), and none of those were
> related to the use of blobs.
> Personally, my criteria for storing your invoices in the database would
> involve two decisions. One is how long you wish to retain them (short, then
> don't bother storing them in the database) and the other is how frequently
> they are accessed. Since keeping invoices in this manner would typically be
> for archival purposes and they would almost never be opened, you really
> don't care about the performance hit versus keeping them in the file system
> when one needs to be displayed. So the management benefits would outweigh
> the performance negatives.
> --
> Hal Berenson, President
> PredictableIT, LLC
> http://www.predictableit.com
>
> "Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in
> message news:A5EF2AD5-B2F1-48BD-A9A7-41D42D63301F@.microsoft.com...
> > Hi,
> >
> > we have a sql server 2000 database running that manages amout 10 million
> > invoices (growing at 10000/day). in addition, we have a file server that
> > holds all invoices as pdf files. the problem is that it is extremely
> > difficult to backup these files or to do other file-realted task since
> > there
> > are so many of them.
> >
> > thats why we started thinking abut moving all these files directly into
> > the
> > database as binary objects (BLOBs) so that invoice meta data and content
> > is
> > at the same place. the average file size is about 40k. im not a big fan of
> > blobs but in this case it seems to be a good thing since backup and file
> > management wouldnt be a problem anymore (among other advantages).
> >
> > my question is if you already experienced having large amounts of blobs in
> > the database and if you think its a good idea or not. i don't want our
> > overall query performance to be affected in any way (i know that sql
> > server
> > stores pointer to the blob data in the tables so it shouldnt have a great
> > impact on performance, should it?). i'm interested in your experiences and
> > recommendations.
> >
> > btw.: we would upgrade the hardware to something like this:
> >
> > 2 x Xeon 64, 3.6 GHz
> > 8 GB RAM
> > 10-14 disk drives for a total of 1-2 TB
> >
> > i think it should be enough.
> >
> > thanks in advance,
> > Benjamin Janecke
>
>

BLOB or VARBINARY(MAX) as SSIS source.

Hi all,

Can a SSIS package treat a file from a table ( VARBINARY(MAX) or BLOB) as a source for migration ?

Thanks in advance,

DBAnalyst

Sure. The varbinary(max) SQL 2005 data type maps to the SSIS type DT_IMAGE, which is just a bunch of bytes of which the file consists.

So what you have at that point is a byte stream, or byte array.

To use those bytes as file source, you could write them to a file in one dataflow and read them in a second dataflow.

Another way to use varbinary(max) data as a "file source" would be to use a script or custom source component (its not that painful) to wrap a StreamReader object arround the SQL byte stream. Using this approach, the varbinary(max) never touches down to disk as an intermediate step.

There are certainly other techniques, but those come to mind.|||Thanks a lot Jaegd.

BLOB Field in a table causing huge wastage?

Our backups are extremely slow as the docs table in the site database is
extremely large.
I've tried to shrink the file and shrink the database using DBCC
ShrinkDatabase and then DBCC ShrinkFile with no luck.
On examining the database i found this...
Table_Name data_KB unused_KB unused_pct
-- -- -- --
Docs 16490400 KB 24676360 KB 60%
DocVersions 1957800 KB 3915816 KB 67%
DBCC ShowContig (Docs) shows the following
DBCC SHOWCONTIG scanning 'Docs' table...
Table: 'Docs' (1993058136); index ID: 1, database ID: 15
TABLE level scan performed.
- Pages Scanned........................: 13643
- Extents Scanned.......................: 1761
- Extent Switches.......................: 1760
- Avg. Pages per Extent..................: 7.7
- Scan Density [Best Count:Actual Count]......: 96.88% [1706:1761]
- Logical Scan Fragmentation ..............: 0.01%
- Extent Scan Fragmentation ...............: 0.28%
- Avg. Bytes Free per Page................: 210.6
- Avg. Page Density (full)................: 97.40%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
so as far as i can see the table isnt fragmented but is extremely wasteful.
My thinking is this...
Files in the docs table range widely in size from a few hundred KB to 50MB.
Am i right in thinking that the "content" column in Docs is set to a size
that accomodates the largest file (i.e. 50MB) and therefore up to 49.90MB are
wasted on other rows?
I really need someones help in reducing the size of the database, i've tried
reindexing the database too, again with no difference other than the extent
scan fragmentation then goes shooting up to 99.97%Hi Mauro
> Files in the docs table range widely in size from a few hundred KB to
> 50MB.
There are no files in a table. Do you mean LOB data (text, image or ntext)?
How is the table defined?
How is the LOB data inserted?
Is the LOB data ever modified? If so, how?
What version are you using?
> Am i right in thinking that the "content" column in Docs is set to a size
> that accomodates the largest file (i.e. 50MB) and therefore up to 49.90MB
> are
> wasted on other rows?
This is NOT correct. How would SQL Server know the largest size you are
planning to insert? The largest size a LOB column can hold is 2 GB and it
obviously isn't reserving 2 GB per row.
--
HTH
Kalen Delaney, SQL Server MVP
"Mauro Masucci" <MauroMasucci@.discussions.microsoft.com> wrote in message
news:16A3A1DE-D023-49B2-9CDF-9D00CFCB6DA8@.microsoft.com...
> Our backups are extremely slow as the docs table in the site database is
> extremely large.
> I've tried to shrink the file and shrink the database using DBCC
> ShrinkDatabase and then DBCC ShrinkFile with no luck.
> On examining the database i found this...
> Table_Name data_KB unused_KB unused_pct
> -- -- -- --
> Docs 16490400 KB 24676360 KB 60%
> DocVersions 1957800 KB 3915816 KB 67%
> DBCC ShowContig (Docs) shows the following
> DBCC SHOWCONTIG scanning 'Docs' table...
> Table: 'Docs' (1993058136); index ID: 1, database ID: 15
> TABLE level scan performed.
> - Pages Scanned........................: 13643
> - Extents Scanned.......................: 1761
> - Extent Switches.......................: 1760
> - Avg. Pages per Extent..................: 7.7
> - Scan Density [Best Count:Actual Count]......: 96.88% [1706:1761]
> - Logical Scan Fragmentation ..............: 0.01%
> - Extent Scan Fragmentation ...............: 0.28%
> - Avg. Bytes Free per Page................: 210.6
> - Avg. Page Density (full)................: 97.40%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> so as far as i can see the table isnt fragmented but is extremely
> wasteful.
> My thinking is this...
> Files in the docs table range widely in size from a few hundred KB to
> 50MB.
> Am i right in thinking that the "content" column in Docs is set to a size
> that accomodates the largest file (i.e. 50MB) and therefore up to 49.90MB
> are
> wasted on other rows?
> I really need someones help in reducing the size of the database, i've
> tried
> reindexing the database too, again with no difference other than the
> extent
> scan fragmentation then goes shooting up to 99.97%
>|||I noticed full text indexing was on in the table, so i disabled it for the
table and across the entire sharepoint site. this again had no effect. For
curiosity's sake, i am going to DTS the entire table row by row to another
table and see if it is 26GB as it reports or 40GB as is shown by the file
size.
"Kalen Delaney" wrote:
> Hi Mauro
> > Files in the docs table range widely in size from a few hundred KB to
> > 50MB.
> There are no files in a table. Do you mean LOB data (text, image or ntext)?
> How is the table defined?
its defined by Sharepoint Server 2003 (in its unmodified state) there are
three blob fields
> How is the LOB data inserted?
as far as i know its inserted using a normal insert statement, and also
inserted by sharepoint through the sharepoint user interface.
> Is the LOB data ever modified? If so, how?
not as such, it can be overwritten or deleted by a client app. no one can
actually edit the LOB that is inserted directly to the database, however,
sharepoint does allow microsoft office integration which allows us to edit MS
Office documents (and any other documents which have internet explorer
integration).
> What version are you using?
its SQL Server 2000 with SP4.
> > Am i right in thinking that the "content" column in Docs is set to a size
> > that accomodates the largest file (i.e. 50MB) and therefore up to 49.90MB
> > are
> > wasted on other rows?
> This is NOT correct. How would SQL Server know the largest size you are
> planning to insert? The largest size a LOB column can hold is 2 GB and it
> obviously isn't reserving 2 GB per row.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>|||thanks for the help, on closer inspection (looking at the file size's
themselves) i've found sql server was reporting the wrong size. Ive since
forced the db to update the stats and its now reporting the correct size.
"Kalen Delaney" wrote:
> Hi Mauro
> > Files in the docs table range widely in size from a few hundred KB to
> > 50MB.
> There are no files in a table. Do you mean LOB data (text, image or ntext)?
> How is the table defined?
> How is the LOB data inserted?
> Is the LOB data ever modified? If so, how?
> What version are you using?
> > Am i right in thinking that the "content" column in Docs is set to a size
> > that accomodates the largest file (i.e. 50MB) and therefore up to 49.90MB
> > are
> > wasted on other rows?
> This is NOT correct. How would SQL Server know the largest size you are
> planning to insert? The largest size a LOB column can hold is 2 GB and it
> obviously isn't reserving 2 GB per row.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Mauro Masucci" <MauroMasucci@.discussions.microsoft.com> wrote in message
> news:16A3A1DE-D023-49B2-9CDF-9D00CFCB6DA8@.microsoft.com...
> > Our backups are extremely slow as the docs table in the site database is
> > extremely large.
> >
> > I've tried to shrink the file and shrink the database using DBCC
> > ShrinkDatabase and then DBCC ShrinkFile with no luck.
> >
> > On examining the database i found this...
> > Table_Name data_KB unused_KB unused_pct
> > -- -- -- --
> > Docs 16490400 KB 24676360 KB 60%
> > DocVersions 1957800 KB 3915816 KB 67%
> >
> > DBCC ShowContig (Docs) shows the following
> > DBCC SHOWCONTIG scanning 'Docs' table...
> > Table: 'Docs' (1993058136); index ID: 1, database ID: 15
> > TABLE level scan performed.
> > - Pages Scanned........................: 13643
> > - Extents Scanned.......................: 1761
> > - Extent Switches.......................: 1760
> > - Avg. Pages per Extent..................: 7.7
> > - Scan Density [Best Count:Actual Count]......: 96.88% [1706:1761]
> > - Logical Scan Fragmentation ..............: 0.01%
> > - Extent Scan Fragmentation ...............: 0.28%
> > - Avg. Bytes Free per Page................: 210.6
> > - Avg. Page Density (full)................: 97.40%
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >
> > so as far as i can see the table isnt fragmented but is extremely
> > wasteful.
> >
> > My thinking is this...
> >
> > Files in the docs table range widely in size from a few hundred KB to
> > 50MB.
> > Am i right in thinking that the "content" column in Docs is set to a size
> > that accomodates the largest file (i.e. 50MB) and therefore up to 49.90MB
> > are
> > wasted on other rows?
> >
> > I really need someones help in reducing the size of the database, i've
> > tried
> > reindexing the database too, again with no difference other than the
> > extent
> > scan fragmentation then goes shooting up to 99.97%
> >
>
>|||Full text indexes are stored completely separately and are not considered
part of the space used by the table.
--
HTH
Kalen Delaney, SQL Server MVP
"Mauro Masucci" <MauroMasucci@.discussions.microsoft.com> wrote in message
news:BD339FF2-F59C-434D-B270-83746525FD87@.microsoft.com...
>I noticed full text indexing was on in the table, so i disabled it for the
> table and across the entire sharepoint site. this again had no effect.
> For
> curiosity's sake, i am going to DTS the entire table row by row to another
> table and see if it is 26GB as it reports or 40GB as is shown by the file
> size.
> "Kalen Delaney" wrote:
>> Hi Mauro
>> > Files in the docs table range widely in size from a few hundred KB to
>> > 50MB.
>> There are no files in a table. Do you mean LOB data (text, image or
>> ntext)?
>> How is the table defined?
> its defined by Sharepoint Server 2003 (in its unmodified state) there are
> three blob fields
>> How is the LOB data inserted?
> as far as i know its inserted using a normal insert statement, and also
> inserted by sharepoint through the sharepoint user interface.
>> Is the LOB data ever modified? If so, how?
> not as such, it can be overwritten or deleted by a client app. no one can
> actually edit the LOB that is inserted directly to the database, however,
> sharepoint does allow microsoft office integration which allows us to edit
> MS
> Office documents (and any other documents which have internet explorer
> integration).
>> What version are you using?
> its SQL Server 2000 with SP4.
>> > Am i right in thinking that the "content" column in Docs is set to a
>> > size
>> > that accomodates the largest file (i.e. 50MB) and therefore up to
>> > 49.90MB
>> > are
>> > wasted on other rows?
>> This is NOT correct. How would SQL Server know the largest size you are
>> planning to insert? The largest size a LOB column can hold is 2 GB and it
>> obviously isn't reserving 2 GB per row.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>|||I'm glad you figured this out.
I usually suggest that running with updateusage should be your first step
if you are concerned about the values returned by sp_spaceused.
--
HTH
Kalen Delaney, SQL Server MVP
"Mauro Masucci" <MauroMasucci@.discussions.microsoft.com> wrote in message
news:7945DA78-EAA0-4248-8C66-9C33B4BF9BB3@.microsoft.com...
> thanks for the help, on closer inspection (looking at the file size's
> themselves) i've found sql server was reporting the wrong size. Ive since
> forced the db to update the stats and its now reporting the correct size.
> "Kalen Delaney" wrote:
>> Hi Mauro
>> > Files in the docs table range widely in size from a few hundred KB to
>> > 50MB.
>> There are no files in a table. Do you mean LOB data (text, image or
>> ntext)?
>> How is the table defined?
>> How is the LOB data inserted?
>> Is the LOB data ever modified? If so, how?
>> What version are you using?
>> > Am i right in thinking that the "content" column in Docs is set to a
>> > size
>> > that accomodates the largest file (i.e. 50MB) and therefore up to
>> > 49.90MB
>> > are
>> > wasted on other rows?
>> This is NOT correct. How would SQL Server know the largest size you are
>> planning to insert? The largest size a LOB column can hold is 2 GB and it
>> obviously isn't reserving 2 GB per row.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Mauro Masucci" <MauroMasucci@.discussions.microsoft.com> wrote in message
>> news:16A3A1DE-D023-49B2-9CDF-9D00CFCB6DA8@.microsoft.com...
>> > Our backups are extremely slow as the docs table in the site database
>> > is
>> > extremely large.
>> >
>> > I've tried to shrink the file and shrink the database using DBCC
>> > ShrinkDatabase and then DBCC ShrinkFile with no luck.
>> >
>> > On examining the database i found this...
>> > Table_Name data_KB unused_KB
>> > unused_pct
>> > -- -- -- --
>> > Docs 16490400 KB 24676360 KB 60%
>> > DocVersions 1957800 KB 3915816 KB 67%
>> >
>> > DBCC ShowContig (Docs) shows the following
>> > DBCC SHOWCONTIG scanning 'Docs' table...
>> > Table: 'Docs' (1993058136); index ID: 1, database ID: 15
>> > TABLE level scan performed.
>> > - Pages Scanned........................: 13643
>> > - Extents Scanned.......................: 1761
>> > - Extent Switches.......................: 1760
>> > - Avg. Pages per Extent..................: 7.7
>> > - Scan Density [Best Count:Actual Count]......: 96.88% [1706:1761]
>> > - Logical Scan Fragmentation ..............: 0.01%
>> > - Extent Scan Fragmentation ...............: 0.28%
>> > - Avg. Bytes Free per Page................: 210.6
>> > - Avg. Page Density (full)................: 97.40%
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> >
>> > so as far as i can see the table isnt fragmented but is extremely
>> > wasteful.
>> >
>> > My thinking is this...
>> >
>> > Files in the docs table range widely in size from a few hundred KB to
>> > 50MB.
>> > Am i right in thinking that the "content" column in Docs is set to a
>> > size
>> > that accomodates the largest file (i.e. 50MB) and therefore up to
>> > 49.90MB
>> > are
>> > wasted on other rows?
>> >
>> > I really need someones help in reducing the size of the database, i've
>> > tried
>> > reindexing the database too, again with no difference other than the
>> > extent
>> > scan fragmentation then goes shooting up to 99.97%
>> >
>>

BLOB Field in a table causing huge wastage?

Friday, February 24, 2012

Bloated database wont shrink after backup/restore SQL 2000

Help,
I have a database that has a data file of 2GB and a log file of 31GB.
In enterprise manager, when I choose shrink it says there is 30GB of
unused space. When I shrink the database, it does not shrink,
(however it says it has completed).
I've done a complete backup, tried shrink again, no dice. I then
backed up the database (which the backup was 1.9GB), deleted the
database and made a new database with 2,048MB for the data and same
for the log file.
When I restore, the log file jumps up to 31GB again. When I check the
space when I use the shrink, it again says I have 30GB of unused
space.
How on earth do I get this file to shrink?
I've been able to shrink other databases, but not this one.
TIA
RobHopefully one of these articles may help you out

317375 INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/?id=317375
272318 INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://support.microsoft.com/?id=272318
256650 INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/?id=256650
315512 INF: Considerations for Autogrow and Autoshrink Configuration
http://support.microsoft.com/?id=315512
272093 INF: Effects of Nonlogged and Minimally Logged Operations on
Transaction http://support.microsoft.com/?id=272093
230785 INF: SQL Server 7.0 and SQL Server 2000 Logging and Data Storage
http://support.microsoft.com/?id=230785
295108 INF: Incomplete Transaction May Hold Large Number of Locks and Cause
http://support.microsoft.com/?id=295108

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know

"R Camarda" <rcamarda@.cablespeed.com> wrote in message
news:d7938492.0407280405.62108ba2@.posting.google.c om...
> Help,
> I have a database that has a data file of 2GB and a log file of 31GB.
> In enterprise manager, when I choose shrink it says there is 30GB of
> unused space. When I shrink the database, it does not shrink,
> (however it says it has completed).
> I've done a complete backup, tried shrink again, no dice. I then
> backed up the database (which the backup was 1.9GB), deleted the
> database and made a new database with 2,048MB for the data and same
> for the log file.
> When I restore, the log file jumps up to 31GB again. When I check the
> space when I use the shrink, it again says I have 30GB of unused
> space.
> How on earth do I get this file to shrink?
> I've been able to shrink other databases, but not this one.
> TIA
> Rob

blank textarea

Hi,
I can't get a blank textarea when applying my .xslt to a .xml file. I know
this is a common error because I've already found some solutions but none
worked :(
The parser always translates the solutions to: <textarea/>
Thanks in advance ;)
CheersFuria wrote:

> Hi,
> I can't get a blank textarea when applying my .xslt to a .xml file. I
> know this is a common error because I've already found some solutions
> but none worked :(
> The parser always translates the solutions to: <textarea/>
Have you tried inserting an
<textarea>
<xsl:text> </xsl:text>
</textarea>
///Peter
--
XML FAQ: http://xml.silmaril.ie/|||"Peter Flynn" wrote:
> Have you tried inserting an
><textarea>
><xsl:text> </xsl:text>
></textarea>
Hi,
gona try that but i've tried similar things such as:
<textarea>
<xsl:text> </xsl:text>
</textarea>
and
<textarea>
<xsl:text>&nbps;</xsl:text>
None worked.
Feedback soon, thanks Peter ;)
</textarea>|||Nop...:(
None works...it still has a blank space...grrr
Cheers
Furia

blank textarea

Hi,
I can't get a blank textarea when applying my .xslt to a .xml file. I know
this is a common error because I've already found some solutions but none
worked

The parser always translates the solutions to: <textarea/>
Thanks in advance ;)
Cheers
Furia wrote:

> Hi,
> I can't get a blank textarea when applying my .xslt to a .xml file. I
> know this is a common error because I've already found some solutions
> but none worked

> The parser always translates the solutions to: <textarea/>
Have you tried inserting an  
<textarea>
<xsl:text> </xsl:text>
</textarea>
///Peter
XML FAQ: http://xml.silmaril.ie/
|||"Peter Flynn" wrote:
> Have you tried inserting an
><textarea>
><xsl:text> </xsl:text>
></textarea>
Hi,
gona try that but i've tried similar things such as:
<textarea>
<xsl:text> </xsl:text>
</textarea>
and
<textarea>
<xsl:text>&nbps;</xsl:text>
None worked.
Feedback soon, thanks Peter ;)
</textarea>
|||Nop...

None works...it still has a blank space...grrr
Cheers
Furia

Sunday, March 25, 2012

Bookmarks (Management Studio)

Bookmarks (Management Studio)

Monday, March 19, 2012

Blue shortcut arrow on Replication Monitor

Blue shortcut arrow on Replication Monitor

Blue shortcut arrow on Replication Monitor

Sunday, March 11, 2012

Blocking seen when two filegroups are backed up in parallel

Saturday, February 25, 2012

Blobs: File System or DB?

Blobs: File System or DB?

Blobs: File System or DB?

BLOBS vs. regular files

BLOBS vs. regular files

BLOBS vs. regular files

BLOBS in a sql server database - yes or no?

BLOBS in a sql server database - yes or no?

BLOB or VARBINARY(MAX) as SSIS source.

BLOB Field in a table causing huge wastage?

BLOB Field in a table causing huge wastage?

Friday, February 24, 2012

Bloated database wont shrink after backup/restore SQL 2000

blank textarea

blank textarea

sql bit data type

Blog Archive

About Me