disk sizw for a table

I am a novice in SQL.. i need to find a way to find the
total size occupied by a table. Is there any easy way to
accomplish this ?
Thanks in advance
Arunexec sp_SpaceUsed 'mytable'
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Arun" <anonymous@.discussions.microsoft.com> wrote in message
news:021f01c3d953$ef2c0ba0$a601280a@.phx.gbl...
> I am a novice in SQL.. i need to find a way to find the
> total size occupied by a table. Is there any easy way to
> accomplish this ?
> Thanks in advance
> Arun|||Kevin thanks, it gave the info as i wanted ..but another
silly question...the total size for a table would be
Data size + Index size correct ?
>--Original Message--
>exec sp_SpaceUsed 'mytable'
>--
>Kevin Hill
>President
>3NF Consulting
>www.3nf-inc.com/NewsGroups.htm
>"Arun" <anonymous@.discussions.microsoft.com> wrote in
message
>news:021f01c3d953$ef2c0ba0$a601280a@.phx.gbl...
>> I am a novice in SQL.. i need to find a way to find the
>> total size occupied by a table. Is there any easy way to
>> accomplish this ?
>> Thanks in advance
>> Arun
>
>.
>|||Yes, unless there is more space reserved for the table than actually used by
the data and indexes. Check BOL for very detailed info
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
<anonymous@.discussions.microsoft.com> wrote in message
news:026901c3d957$5d6b96a0$a601280a@.phx.gbl...
> Kevin thanks, it gave the info as i wanted ..but another
> silly question...the total size for a table would be
> Data size + Index size correct ?
>
> >--Original Message--
> >exec sp_SpaceUsed 'mytable'
> >
> >--
> >Kevin Hill
> >President
> >3NF Consulting
> >
> >www.3nf-inc.com/NewsGroups.htm
> >
> >"Arun" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:021f01c3d953$ef2c0ba0$a601280a@.phx.gbl...
> >> I am a novice in SQL.. i need to find a way to find the
> >> total size occupied by a table. Is there any easy way to
> >> accomplish this ?
> >> Thanks in advance
> >> Arun
> >
> >
> >.
> >|||(anonymous@.discussions.microsoft.com) writes:
> Kevin thanks, it gave the info as i wanted ..but another
> silly question...the total size for a table would be
> Data size + Index size correct ?
The number you want to look at is "reserved". This is the actual
number of space allocated for the table. SQL Server allocates space
for a table extends of 8 * 8 KB. (Save the for the first row; here
normally only 8 KB is reserved.). So normally, there is always some
free space. If there have been plenty of deletions, there can be
more.
Also, you may want to add a second parameter to sp_spaceused:
sp_spaceused tbl, true
This forces an update of the tables which holds the size data.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

disk sizw for a table

I am a novice in SQL.. i need to find a way to find the
total size occupied by a table. Is there any easy way to
accomplish this ?
Thanks in advance
Arunexec sp_SpaceUsed 'mytable'
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Arun" <anonymous@.discussions.microsoft.com> wrote in message
news:021f01c3d953$ef2c0ba0$a601280a@.phx.gbl...

quote:

---

> I am a novice in SQL.. i need to find a way to find the
> total size occupied by a table. Is there any easy way to
> accomplish this ?
> Thanks in advance
> Arun

---

|||Kevin thanks, it gave the info as i wanted ..but another
silly question...the total size for a table would be
Data size + Index size correct ?

quote:

---

>--Original Message--
>exec sp_SpaceUsed 'mytable'
>--
>Kevin Hill
>President
>3NF Consulting
>www.3nf-inc.com/NewsGroups.htm
>"Arun" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:021f01c3d953$ef2c0ba0$a601280a@.phx.gbl...
>
>.
>

---

|||Yes, unless there is more space reserved for the table than actually used by
the data and indexes. Check BOL for very detailed info
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
<anonymous@.discussions.microsoft.com> wrote in message
news:026901c3d957$5d6b96a0$a601280a@.phx.gbl...[QUOTE]
> Kevin thanks, it gave the info as i wanted ..but another
> silly question...the total size for a table would be
> Data size + Index size correct ?
>
> message|||(anonymous@.discussions.microsoft.com) writes:

quote:

---

> Kevin thanks, it gave the info as i wanted ..but another
> silly question...the total size for a table would be
> Data size + Index size correct ?

---

The number you want to look at is "reserved". This is the actual
number of space allocated for the table. SQL Server allocates space
for a table extends of 8 * 8 KB. (Save the for the first row; here
normally only 8 KB is reserved.). So normally, there is always some
free space. If there have been plenty of deletions, there can be
more.
Also, you may want to add a second parameter to sp_spaceused:
sp_spaceused tbl, true
This forces an update of the tables which holds the size data.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Disk Size

I want to find the size of the physical disks on a SQL
server. I have tried using xp_cmdshell but i cannot find a
command that will return the disk size. The closest that i
have come is forating the results of chkdsk but this takes
time to run. Can anyone offer any other suggestions. Ist
there a command to show this or perhaps there is a XP or
SP that will do the job
Cheers
mat
CREATE table DriveTable (Drive varchar(10),[MB Free] int)
INSERT into Drivetable Exec xp_fixeddrives
SELECT * FROM DriveTable
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:22f4901c45e7c$417e04f0$a601280a@.phx.gbl...
> I want to find the size of the physical disks on a SQL
> server. I have tried using xp_cmdshell but i cannot find a
> command that will return the disk size. The closest that i
> have come is forating the results of chkdsk but this takes
> time to run. Can anyone offer any other suggestions. Ist
> there a command to show this or perhaps there is a XP or
> SP that will do the job
> Cheers
|||Hi,
Execute the command "XP_FIXEDDRIVES" from query analyzer to the Free size in
MB for each of the fixed drives.
Thanks
Hari
MCDBA
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:22f4901c45e7c$417e04f0$a601280a@.phx.gbl...
> I want to find the size of the physical disks on a SQL
> server. I have tried using xp_cmdshell but i cannot find a
> command that will return the disk size. The closest that i
> have come is forating the results of chkdsk but this takes
> time to run. Can anyone offer any other suggestions. Ist
> there a command to show this or perhaps there is a XP or
> SP that will do the job
> Cheers
|||Hi ,
use following
exec master..xp_fixeddrives
it will return free space on the drives
Thanks
Ajay
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:22f4901c45e7c$417e04f0$a601280a@.phx.gbl...
> I want to find the size of the physical disks on a SQL
> server. I have tried using xp_cmdshell but i cannot find a
> command that will return the disk size. The closest that i
> have come is forating the results of chkdsk but this takes
> time to run. Can anyone offer any other suggestions. Ist
> there a command to show this or perhaps there is a XP or
> SP that will do the job
> Cheers
|||I need to determine the actual size of the disks, not the
free space available
>--Original Message--
>mat
>CREATE table DriveTable (Drive varchar(10),[MB Free] int)
>INSERT into Drivetable Exec xp_fixeddrives
>SELECT * FROM DriveTable
>"mat" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:22f4901c45e7c$417e04f0$a601280a@.phx.gbl...
find a[vbcol=seagreen]
that i[vbcol=seagreen]
takes
>
>.
>
|||Jasper Smith (SQL Server MVP) has written very useful stored procedure
use master
go
CREATE PROCEDURE sp_diskspace
AS
SET NOCOUNT ON
DECLARE @.hr int
DECLARE @.fso int
DECLARE @.drive char(1)
DECLARE @.odrive int
DECLARE @.TotalSize varchar(20)
DECLARE @.MB bigint ; SET @.MB = 1048576
CREATE TABLE #drives (drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL)
INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives
EXEC @.hr=sp_OACreate 'Scripting.FileSystemObject',@.fso OUT
IF @.hr <> 0 EXEC sp_OAGetErrorInfo @.fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive
OPEN dcur
FETCH NEXT FROM dcur INTO @.drive
WHILE @.@.FETCH_STATUS=0
BEGIN
EXEC @.hr = sp_OAMethod @.fso,'GetDrive', @.odrive OUT, @.drive
IF @.hr <> 0 EXEC sp_OAGetErrorInfo @.fso
EXEC @.hr = sp_OAGetProperty @.odrive,'TotalSize', @.TotalSize OUT
IF @.hr <> 0 EXEC sp_OAGetErrorInfo @.odrive
UPDATE #drives
SET TotalSize=@.TotalSize/@.MB
WHERE drive=@.drive
FETCH NEXT FROM dcur INTO @.drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @.hr=sp_OADestroy @.fso
IF @.hr <> 0 EXEC sp_OAGetErrorInfo @.fso
SELECT drive,
FreeSpace as 'Free(MB)',
TotalSize as 'Total(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)'
FROM #drives
ORDER BY drive
DROP TABLE #drives
RETURN
go
<anonymous@.discussions.microsoft.com> wrote in message
news:237d901c45e7d$924f25c0$a501280a@.phx.gbl...[vbcol=seagreen]
> I need to determine the actual size of the disks, not the
> free space available
> message
> find a
> that i
> takes
|||The code can be found here http://www.sqldbatips.com/showcode.asp?ID=4
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:237d901c45e7d$924f25c0$a501280a@.phx.gbl...[vbcol=seagreen]
> I need to determine the actual size of the disks, not the
> free space available
> message
> find a
> that i
> takes

Disk Size

I want to find the size of the physical disks on a SQL
server. I have tried using xp_cmdshell but i cannot find a
command that will return the disk size. The closest that i
have come is forating the results of chkdsk but this takes
time to run. Can anyone offer any other suggestions. Ist
there a command to show this or perhaps there is a XP or
SP that will do the job
Cheersmat
CREATE table DriveTable (Drive varchar(10),[MB Free] int)
INSERT into Drivetable Exec xp_fixeddrives
SELECT * FROM DriveTable
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:22f4901c45e7c$417e04f0$a601280a@.phx
.gbl...
> I want to find the size of the physical disks on a SQL
> server. I have tried using xp_cmdshell but i cannot find a
> command that will return the disk size. The closest that i
> have come is forating the results of chkdsk but this takes
> time to run. Can anyone offer any other suggestions. Ist
> there a command to show this or perhaps there is a XP or
> SP that will do the job
> Cheers|||Hi,
Execute the command "XP_FIXEDDRIVES" from query analyzer to the Free size in
MB for each of the fixed drives.
Thanks
Hari
MCDBA
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:22f4901c45e7c$417e04f0$a601280a@.phx
.gbl...
> I want to find the size of the physical disks on a SQL
> server. I have tried using xp_cmdshell but i cannot find a
> command that will return the disk size. The closest that i
> have come is forating the results of chkdsk but this takes
> time to run. Can anyone offer any other suggestions. Ist
> there a command to show this or perhaps there is a XP or
> SP that will do the job
> Cheers|||Hi ,
use following
exec master..xp_fixeddrives
it will return free space on the drives
Thanks
Ajay
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:22f4901c45e7c$417e04f0$a601280a@.phx
.gbl...
> I want to find the size of the physical disks on a SQL
> server. I have tried using xp_cmdshell but i cannot find a
> command that will return the disk size. The closest that i
> have come is forating the results of chkdsk but this takes
> time to run. Can anyone offer any other suggestions. Ist
> there a command to show this or perhaps there is a XP or
> SP that will do the job
> Cheers|||I need to determine the actual size of the disks, not the
free space available
>--Original Message--
>mat
>CREATE table DriveTable (Drive varchar(10),[MB Free] int)
>INSERT into Drivetable Exec xp_fixeddrives
>SELECT * FROM DriveTable
>"mat" <anonymous@.discussions.microsoft.com> wrote in
message
> news:22f4901c45e7c$417e04f0$a601280a@.phx
.gbl...
find a[vbcol=seagreen]
that i[vbcol=seagreen]
takes[vbcol=seagreen]
>
>.
>|||Jasper Smith (SQL Server MVP) has written very useful stored procedure
use master
go
CREATE PROCEDURE sp_diskspace
AS
SET NOCOUNT ON
DECLARE @.hr int
DECLARE @.fso int
DECLARE @.drive char(1)
DECLARE @.odrive int
DECLARE @.TotalSize varchar(20)
DECLARE @.MB bigint ; SET @.MB = 1048576
CREATE TABLE #drives (drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL)
INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives
EXEC @.hr=sp_OACreate 'Scripting.FileSystemObject',@.fso OUT
IF @.hr <> 0 EXEC sp_OAGetErrorInfo @.fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive
OPEN dcur
FETCH NEXT FROM dcur INTO @.drive
WHILE @.@.FETCH_STATUS=0
BEGIN
EXEC @.hr = sp_OAMethod @.fso,'GetDrive', @.odrive OUT, @.drive
IF @.hr <> 0 EXEC sp_OAGetErrorInfo @.fso
EXEC @.hr = sp_OAGetProperty @.odrive,'TotalSize', @.TotalSize OUT
IF @.hr <> 0 EXEC sp_OAGetErrorInfo @.odrive
UPDATE #drives
SET TotalSize=@.TotalSize/@.MB
WHERE drive=@.drive
FETCH NEXT FROM dcur INTO @.drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @.hr=sp_OADestroy @.fso
IF @.hr <> 0 EXEC sp_OAGetErrorInfo @.fso
SELECT drive,
FreeSpace as 'Free(MB)',
TotalSize as 'Total(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)'
FROM #drives
ORDER BY drive
DROP TABLE #drives
RETURN
go
<anonymous@.discussions.microsoft.com> wrote in message
news:237d901c45e7d$924f25c0$a501280a@.phx
.gbl...[vbcol=seagreen]
> I need to determine the actual size of the disks, not the
> free space available
> message
> find a
> that i
> takes|||The code can be found here http://www.sqldbatips.com/showcode.asp?ID=4
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:237d901c45e7d$924f25c0$a501280a@.phx
.gbl...[vbcol=seagreen]
> I need to determine the actual size of the disks, not the
> free space available
> message
> find a
> that i
> takessql

Disk Size

I want to find the size of the physical disks on a SQL
server. I have tried using xp_cmdshell but i cannot find a
command that will return the disk size. The closest that i
have come is forating the results of chkdsk but this takes
time to run. Can anyone offer any other suggestions. Ist
there a command to show this or perhaps there is a XP or
SP that will do the job
Cheersmat
CREATE table DriveTable (Drive varchar(10),[MB Free] int)
INSERT into Drivetable Exec xp_fixeddrives
SELECT * FROM DriveTable
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:22f4901c45e7c$417e04f0$a601280a@.phx.gbl...
> I want to find the size of the physical disks on a SQL
> server. I have tried using xp_cmdshell but i cannot find a
> command that will return the disk size. The closest that i
> have come is forating the results of chkdsk but this takes
> time to run. Can anyone offer any other suggestions. Ist
> there a command to show this or perhaps there is a XP or
> SP that will do the job
> Cheers|||Hi,
Execute the command "XP_FIXEDDRIVES" from query analyzer to the Free size in
MB for each of the fixed drives.
--
Thanks
Hari
MCDBA
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:22f4901c45e7c$417e04f0$a601280a@.phx.gbl...
> I want to find the size of the physical disks on a SQL
> server. I have tried using xp_cmdshell but i cannot find a
> command that will return the disk size. The closest that i
> have come is forating the results of chkdsk but this takes
> time to run. Can anyone offer any other suggestions. Ist
> there a command to show this or perhaps there is a XP or
> SP that will do the job
> Cheers|||Hi ,
use following
exec master..xp_fixeddrives
it will return free space on the drives
Thanks
Ajay
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:22f4901c45e7c$417e04f0$a601280a@.phx.gbl...
> I want to find the size of the physical disks on a SQL
> server. I have tried using xp_cmdshell but i cannot find a
> command that will return the disk size. The closest that i
> have come is forating the results of chkdsk but this takes
> time to run. Can anyone offer any other suggestions. Ist
> there a command to show this or perhaps there is a XP or
> SP that will do the job
> Cheers|||I need to determine the actual size of the disks, not the
free space available
>--Original Message--
>mat
>CREATE table DriveTable (Drive varchar(10),[MB Free] int)
>INSERT into Drivetable Exec xp_fixeddrives
>SELECT * FROM DriveTable
>"mat" <anonymous@.discussions.microsoft.com> wrote in
message
>news:22f4901c45e7c$417e04f0$a601280a@.phx.gbl...
>> I want to find the size of the physical disks on a SQL
>> server. I have tried using xp_cmdshell but i cannot
find a
>> command that will return the disk size. The closest
that i
>> have come is forating the results of chkdsk but this
takes
>> time to run. Can anyone offer any other suggestions. Ist
>> there a command to show this or perhaps there is a XP or
>> SP that will do the job
>> Cheers
>
>.
>|||Jasper Smith (SQL Server MVP) has written very useful stored procedure
use master
go
CREATE PROCEDURE sp_diskspace
AS
SET NOCOUNT ON
DECLARE @.hr int
DECLARE @.fso int
DECLARE @.drive char(1)
DECLARE @.odrive int
DECLARE @.TotalSize varchar(20)
DECLARE @.MB bigint ; SET @.MB = 1048576
CREATE TABLE #drives (drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL)
INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives
EXEC @.hr=sp_OACreate 'Scripting.FileSystemObject',@.fso OUT
IF @.hr <> 0 EXEC sp_OAGetErrorInfo @.fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive
OPEN dcur
FETCH NEXT FROM dcur INTO @.drive
WHILE @.@.FETCH_STATUS=0
BEGIN
EXEC @.hr = sp_OAMethod @.fso,'GetDrive', @.odrive OUT, @.drive
IF @.hr <> 0 EXEC sp_OAGetErrorInfo @.fso
EXEC @.hr = sp_OAGetProperty @.odrive,'TotalSize', @.TotalSize OUT
IF @.hr <> 0 EXEC sp_OAGetErrorInfo @.odrive
UPDATE #drives
SET TotalSize=@.TotalSize/@.MB
WHERE drive=@.drive
FETCH NEXT FROM dcur INTO @.drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @.hr=sp_OADestroy @.fso
IF @.hr <> 0 EXEC sp_OAGetErrorInfo @.fso
SELECT drive,
FreeSpace as 'Free(MB)',
TotalSize as 'Total(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)'
FROM #drives
ORDER BY drive
DROP TABLE #drives
RETURN
go
<anonymous@.discussions.microsoft.com> wrote in message
news:237d901c45e7d$924f25c0$a501280a@.phx.gbl...
> I need to determine the actual size of the disks, not the
> free space available
> >--Original Message--
> >mat
> >CREATE table DriveTable (Drive varchar(10),[MB Free] int)
> >INSERT into Drivetable Exec xp_fixeddrives
> >
> >SELECT * FROM DriveTable
> >
> >"mat" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:22f4901c45e7c$417e04f0$a601280a@.phx.gbl...
> >> I want to find the size of the physical disks on a SQL
> >> server. I have tried using xp_cmdshell but i cannot
> find a
> >> command that will return the disk size. The closest
> that i
> >> have come is forating the results of chkdsk but this
> takes
> >> time to run. Can anyone offer any other suggestions. Ist
> >> there a command to show this or perhaps there is a XP or
> >> SP that will do the job
> >>
> >> Cheers
> >
> >
> >.
> >|||The code can be found here http://www.sqldbatips.com/showcode.asp?ID=4
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:237d901c45e7d$924f25c0$a501280a@.phx.gbl...
> I need to determine the actual size of the disks, not the
> free space available
> >--Original Message--
> >mat
> >CREATE table DriveTable (Drive varchar(10),[MB Free] int)
> >INSERT into Drivetable Exec xp_fixeddrives
> >
> >SELECT * FROM DriveTable
> >
> >"mat" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:22f4901c45e7c$417e04f0$a601280a@.phx.gbl...
> >> I want to find the size of the physical disks on a SQL
> >> server. I have tried using xp_cmdshell but i cannot
> find a
> >> command that will return the disk size. The closest
> that i
> >> have come is forating the results of chkdsk but this
> takes
> >> time to run. Can anyone offer any other suggestions. Ist
> >> there a command to show this or perhaps there is a XP or
> >> SP that will do the job
> >>
> >> Cheers
> >
> >
> >.
> >

DIsk quotas

Hello,

I am investigating setting up a shared sql server. I wish to enforce
database size quotas, and hopefully still allow customers to manage
the creation of their own databases.

Any thoughts or suggestions would be appreciated.

Kind Regards
Simon Hall"Simon Hall" <itguy@.xtrahost.co.uk> wrote in message
news:84f037fd.0404290756.77945c9@.posting.google.co m...
> Hello,
> I am investigating setting up a shared sql server. I wish to enforce
> database size quotas, and hopefully still allow customers to manage
> the creation of their own databases.
> Any thoughts or suggestions would be appreciated.
> Kind Regards
> Simon Hall

CREATE DATABASE is usually restricted only to administrators, to prevent
someone grabbing all the disk space. One solution might be a custom database
creation/resizing request tool, which could be a web form or whatever. That
would allow you to check a request against other information (eg. billing
status) before processing it, either manually via a DBA or in some automated
way (perhaps using SQLDMO).

Alternatively, when you set up a new client, just create a database with the
largest size you want to allow them, although since different clients may
have different requirements in terms of transaction log use and recovery
models, it may not be that simple.

There may well be other solutions using genuine disk quotas (ie. in the OS,
in SAN software, a commercial app etc.), but since the MSSQL service runs as
one user account, I'm not sure how a quota could be linked to a specific
login/client. Unless you could do something where a single client's files
are all in one folder, then manage that folder. But you'd still probably
need some sort of custom tool in that case to handle new requests and assign
more space if needed.

Simon

Disk Partition Size

Can you please tell me what the largest disk partition size is for a clustered SQL Server 2005 installation (64 bit edition)? I have been told it is 2 Terabytes - can anyone confirm this?

Many thanks

Shirley

If you're asking about logical partitioning, this might help:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5partition.asp

But if you're asking about physical partitions on the hard drive, then Books Online states it's 16 terabytes. Paste this in the "URL" bar of BOL to check out the table of specifications:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/13e95046-0e76-4604-b561-d1a74dd824d7.htm

Buck Woody

Disk Partition Size

Can you please tell me what the largest disk partition size is for a clustered SQL Server 2005 installation (64 bit edition)? I have been told it is 2 Terabytes - can anyone confirm this?

Many thanks

Shirley

If you're asking about logical partitioning, this might help:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5partition.asp

But if you're asking about physical partitions on the hard drive, then Books Online states it's 16 terabytes. Paste this in the "URL" bar of BOL to check out the table of specifications:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/13e95046-0e76-4604-b561-d1a74dd824d7.htm

Buck Woody

Disk Block Size

Hi,
We use internal (local) disk for our sql server 2000, SP3a
databases. We have the OS (Win 2k Advanced Server), log
files, and data files on separate physical spindles. The
disk spins at 15K RPM and has proved to be much quicker
than our previous EMC solution. Lately, we have noticed
that the disk_queue_length numbers on our data spindle are
higher than normal. The physical block size of our disk
is now at 4K. I remember reading that SQL SERVER writes
and reads in 8K sections and it would be best to format
the data and log drives at an 8K block size. We have also
had some database contension issues along with the
disk_queue_length, so we believe our problem could be
either hardware or software related. We would like to get
the hardware as finely tuned before we challenge the
software developers and their queries.
Can anyone provide any insight on the above?
Thanks,
AndyWhere has the database contention been seen? What kind of workload is it
with how many clients?
Long disk queues are usually indicative of the IO system not being able to
handle the load placed upon it. Solutions are tuning/upgrading the IO system
or reducing the IO load - if you're convinced the load should not be too
high, you'll need to start doing query perf analysis.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:11eb01c4263e$a5e76650$a401280a@.phx.gbl...
> Hi,
> We use internal (local) disk for our sql server 2000, SP3a
> databases. We have the OS (Win 2k Advanced Server), log
> files, and data files on separate physical spindles. The
> disk spins at 15K RPM and has proved to be much quicker
> than our previous EMC solution. Lately, we have noticed
> that the disk_queue_length numbers on our data spindle are
> higher than normal. The physical block size of our disk
> is now at 4K. I remember reading that SQL SERVER writes
> and reads in 8K sections and it would be best to format
> the data and log drives at an 8K block size. We have also
> had some database contension issues along with the
> disk_queue_length, so we believe our problem could be
> either hardware or software related. We would like to get
> the hardware as finely tuned before we challenge the
> software developers and their queries.
> Can anyone provide any insight on the above?
> Thanks,
> Andy|||It is a shared SQL SERVER with 10 user databases on it.
Most of the transactions are "reading" or select in
nature. Up until recently everything was fine. I used
the Disk Defagmenter tool that comes with Windows to
analze the log and data drives and found that the ldf and
mdf files had up to 150 fragments within the file. We
have rebuilt the indexes with both dbcc indexdefrag and
dbcc dbreindex as well as looked and analyzed queries from
a server wide trace.
The applications connecting to this machine are using
connection pooling and we don't exactly know the number of
client connections, but we know that there are no more
than 100 concurrent connections at one time. Most of the
time there is no more than 5 active connections or
transactions unless there is locking or contension at the
time. We have thought about moving some databases to less
transactional sql servers, but wanted to rule out disk
fragmentation as a bottleneck first.
Thanks,
Andy
>--Original Message--
>Where has the database contention been seen? What kind of
workload is it
>with how many clients?
>Long disk queues are usually indicative of the IO system
not being able to
>handle the load placed upon it. Solutions are
tuning/upgrading the IO system
>or reducing the IO load - if you're convinced the load
should not be too
>high, you'll need to start doing query perf analysis.
>Regards
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Andy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:11eb01c4263e$a5e76650$a401280a@.phx.gbl...
SP3a[vbcol=seagreen]
The[vbcol=seagreen]
are[vbcol=seagreen]
also[vbcol=seagreen]
get[vbcol=seagreen]
>
>.
>

Disk Block Size

Hi,
We use internal (local) disk for our sql server 2000, SP3a
databases. We have the OS (Win 2k Advanced Server), log
files, and data files on separate physical spindles. The
disk spins at 15K RPM and has proved to be much quicker
than our previous EMC solution. Lately, we have noticed
that the disk_queue_length numbers on our data spindle are
higher than normal. The physical block size of our disk
is now at 4K. I remember reading that SQL SERVER writes
and reads in 8K sections and it would be best to format
the data and log drives at an 8K block size. We have also
had some database contension issues along with the
disk_queue_length, so we believe our problem could be
either hardware or software related. We would like to get
the hardware as finely tuned before we challenge the
software developers and their queries.
Can anyone provide any insight on the above?
Thanks,
Andy
Where has the database contention been seen? What kind of workload is it
with how many clients?
Long disk queues are usually indicative of the IO system not being able to
handle the load placed upon it. Solutions are tuning/upgrading the IO system
or reducing the IO load - if you're convinced the load should not be too
high, you'll need to start doing query perf analysis.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:11eb01c4263e$a5e76650$a401280a@.phx.gbl...
> Hi,
> We use internal (local) disk for our sql server 2000, SP3a
> databases. We have the OS (Win 2k Advanced Server), log
> files, and data files on separate physical spindles. The
> disk spins at 15K RPM and has proved to be much quicker
> than our previous EMC solution. Lately, we have noticed
> that the disk_queue_length numbers on our data spindle are
> higher than normal. The physical block size of our disk
> is now at 4K. I remember reading that SQL SERVER writes
> and reads in 8K sections and it would be best to format
> the data and log drives at an 8K block size. We have also
> had some database contension issues along with the
> disk_queue_length, so we believe our problem could be
> either hardware or software related. We would like to get
> the hardware as finely tuned before we challenge the
> software developers and their queries.
> Can anyone provide any insight on the above?
> Thanks,
> Andy
|||It is a shared SQL SERVER with 10 user databases on it.
Most of the transactions are "reading" or select in
nature. Up until recently everything was fine. I used
the Disk Defagmenter tool that comes with Windows to
analze the log and data drives and found that the ldf and
mdf files had up to 150 fragments within the file. We
have rebuilt the indexes with both dbcc indexdefrag and
dbcc dbreindex as well as looked and analyzed queries from
a server wide trace.
The applications connecting to this machine are using
connection pooling and we don't exactly know the number of
client connections, but we know that there are no more
than 100 concurrent connections at one time. Most of the
time there is no more than 5 active connections or
transactions unless there is locking or contension at the
time. We have thought about moving some databases to less
transactional sql servers, but wanted to rule out disk
fragmentation as a bottleneck first.
Thanks,
Andy
>--Original Message--
>Where has the database contention been seen? What kind of
workload is it
>with how many clients?
>Long disk queues are usually indicative of the IO system
not being able to
>handle the load placed upon it. Solutions are
tuning/upgrading the IO system
>or reducing the IO load - if you're convinced the load
should not be too
>high, you'll need to start doing query perf analysis.
>Regards
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Andy" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:11eb01c4263e$a5e76650$a401280a@.phx.gbl...
SP3a[vbcol=seagreen]
The[vbcol=seagreen]
are[vbcol=seagreen]
also[vbcol=seagreen]
get
>
>.
>

Disk Block Size

Hi,
We use internal (local) disk for our sql server 2000, SP3a
databases. We have the OS (Win 2k Advanced Server), log
files, and data files on separate physical spindles. The
disk spins at 15K RPM and has proved to be much quicker
than our previous EMC solution. Lately, we have noticed
that the disk_queue_length numbers on our data spindle are
higher than normal. The physical block size of our disk
is now at 4K. I remember reading that SQL SERVER writes
and reads in 8K sections and it would be best to format
the data and log drives at an 8K block size. We have also
had some database contension issues along with the
disk_queue_length, so we believe our problem could be
either hardware or software related. We would like to get
the hardware as finely tuned before we challenge the
software developers and their queries.
Can anyone provide any insight on the above?
Thanks,
AndyWhere has the database contention been seen? What kind of workload is it
with how many clients?
Long disk queues are usually indicative of the IO system not being able to
handle the load placed upon it. Solutions are tuning/upgrading the IO system
or reducing the IO load - if you're convinced the load should not be too
high, you'll need to start doing query perf analysis.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:11eb01c4263e$a5e76650$a401280a@.phx.gbl...
> Hi,
> We use internal (local) disk for our sql server 2000, SP3a
> databases. We have the OS (Win 2k Advanced Server), log
> files, and data files on separate physical spindles. The
> disk spins at 15K RPM and has proved to be much quicker
> than our previous EMC solution. Lately, we have noticed
> that the disk_queue_length numbers on our data spindle are
> higher than normal. The physical block size of our disk
> is now at 4K. I remember reading that SQL SERVER writes
> and reads in 8K sections and it would be best to format
> the data and log drives at an 8K block size. We have also
> had some database contension issues along with the
> disk_queue_length, so we believe our problem could be
> either hardware or software related. We would like to get
> the hardware as finely tuned before we challenge the
> software developers and their queries.
> Can anyone provide any insight on the above?
> Thanks,
> Andy|||It is a shared SQL SERVER with 10 user databases on it.
Most of the transactions are "reading" or select in
nature. Up until recently everything was fine. I used
the Disk Defagmenter tool that comes with Windows to
analze the log and data drives and found that the ldf and
mdf files had up to 150 fragments within the file. We
have rebuilt the indexes with both dbcc indexdefrag and
dbcc dbreindex as well as looked and analyzed queries from
a server wide trace.
The applications connecting to this machine are using
connection pooling and we don't exactly know the number of
client connections, but we know that there are no more
than 100 concurrent connections at one time. Most of the
time there is no more than 5 active connections or
transactions unless there is locking or contension at the
time. We have thought about moving some databases to less
transactional sql servers, but wanted to rule out disk
fragmentation as a bottleneck first.
Thanks,
Andy
>--Original Message--
>Where has the database contention been seen? What kind of
workload is it
>with how many clients?
>Long disk queues are usually indicative of the IO system
not being able to
>handle the load placed upon it. Solutions are
tuning/upgrading the IO system
>or reducing the IO load - if you're convinced the load
should not be too
>high, you'll need to start doing query perf analysis.
>Regards
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Andy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:11eb01c4263e$a5e76650$a401280a@.phx.gbl...
>> Hi,
>> We use internal (local) disk for our sql server 2000,
SP3a
>> databases. We have the OS (Win 2k Advanced Server), log
>> files, and data files on separate physical spindles.
The
>> disk spins at 15K RPM and has proved to be much quicker
>> than our previous EMC solution. Lately, we have noticed
>> that the disk_queue_length numbers on our data spindle
are
>> higher than normal. The physical block size of our disk
>> is now at 4K. I remember reading that SQL SERVER writes
>> and reads in 8K sections and it would be best to format
>> the data and log drives at an 8K block size. We have
also
>> had some database contension issues along with the
>> disk_queue_length, so we believe our problem could be
>> either hardware or software related. We would like to
get
>> the hardware as finely tuned before we challenge the
>> software developers and their queries.
>> Can anyone provide any insight on the above?
>> Thanks,
>> Andy
>
>.
>

Disk allocation problem -- Negative Un allocated space

For the past few days we are facing problem in some of our
databases. The no of dead locks are increasing and when we
check the database size with SP_spaceused, the amount of
unallocated space is showing a negative value. Actually,
there is lot of unused space also. The database has been
configure to autogrow.
We are surprised to see the negative Un-allocated space as
normaly system should allocate new extents if the space is
not available. When we used sp_spaceused @.updateusage
= 'true', then the allocation is comming alright.
Any one who has faced the same problem may please share
how to solve this permanently.This is a known issue with sp_spaceused. Also look into using DBCC
UPDATEUSAGE.
Note, this may not be related to why you are seeing deadlocks.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Lokesh" <lokesh.raju@.moserbaer.net> wrote in message
news:07db01c35366$261732d0$a101280a@.phx.gbl...
For the past few days we are facing problem in some of our
databases. The no of dead locks are increasing and when we
check the database size with SP_spaceused, the amount of
unallocated space is showing a negative value. Actually,
there is lot of unused space also. The database has been
configure to autogrow.
We are surprised to see the negative Un-allocated space as
normaly system should allocate new extents if the space is
not available. When we used sp_spaceused @.updateusage
= 'true', then the allocation is comming alright.
Any one who has faced the same problem may please share
how to solve this permanently.|||I'd like to rely on taskpad view for quick examination of db size, or run my
customized stored proc or function to get the correct figures in real time
(use DBCC SHOWFILESIZE" and "DBCC SQLPERF(LOGSPACE)").
I do NOT need to run DBCC UPDATEUSAGE like I had to with sp_spaceused.
Richard
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:eonUPt2UDHA.1836@.TK2MSFTNGP09.phx.gbl...
> This is a known issue with sp_spaceused. Also look into using DBCC
> UPDATEUSAGE.
> Note, this may not be related to why you are seeing deadlocks.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> What hardware is your SQL Server running on?
> http://vyaskn.tripod.com/poll.htm
>
>
> "Lokesh" <lokesh.raju@.moserbaer.net> wrote in message
> news:07db01c35366$261732d0$a101280a@.phx.gbl...
> For the past few days we are facing problem in some of our
> databases. The no of dead locks are increasing and when we
> check the database size with SP_spaceused, the amount of
> unallocated space is showing a negative value. Actually,
> there is lot of unused space also. The database has been
> configure to autogrow.
> We are surprised to see the negative Un-allocated space as
> normaly system should allocate new extents if the space is
> not available. When we used sp_spaceused @.updateusage
> = 'true', then the allocation is comming alright.
> Any one who has faced the same problem may please share
> how to solve this permanently.
>
>|||If you want to rely on the GUI, then run DBCC UPDATEUSAGE on a regular basis
(like as part of stats update or index defrag jobs would be good) and run it
after any major changes (like copying in or out a bunch of data, or if the
thing gets full)
"Richard Ding" <dingr@.cleanharbors.com> wrote in message
news:%23g91osFVDHA.2068@.TK2MSFTNGP11.phx.gbl...
> I'd like to rely on taskpad view for quick examination of db size, or run
my
> customized stored proc or function to get the correct figures in real time
> (use DBCC SHOWFILESIZE" and "DBCC SQLPERF(LOGSPACE)").
> I do NOT need to run DBCC UPDATEUSAGE like I had to with sp_spaceused.
>
> Richard
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:eonUPt2UDHA.1836@.TK2MSFTNGP09.phx.gbl...
> > This is a known issue with sp_spaceused. Also look into using DBCC
> > UPDATEUSAGE.
> >
> > Note, this may not be related to why you are seeing deadlocks.
> >
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > http://vyaskn.tripod.com/
> > What hardware is your SQL Server running on?
> > http://vyaskn.tripod.com/poll.htm
> >
> >
> >
> >
> > "Lokesh" <lokesh.raju@.moserbaer.net> wrote in message
> > news:07db01c35366$261732d0$a101280a@.phx.gbl...
> > For the past few days we are facing problem in some of our
> > databases. The no of dead locks are increasing and when we
> > check the database size with SP_spaceused, the amount of
> > unallocated space is showing a negative value. Actually,
> > there is lot of unused space also. The database has been
> > configure to autogrow.
> >
> > We are surprised to see the negative Un-allocated space as
> > normaly system should allocate new extents if the space is
> > not available. When we used sp_spaceused @.updateusage
> > = 'true', then the allocation is comming alright.
> >
> > Any one who has faced the same problem may please share
> > how to solve this permanently.
> >
> >
> >
> >
>

Discussion about SDL

As we all know, SDL is represented as XML format. I found that representing
the report layout in XML make the size of report grow very fast and hurt the
performance significantly.
I am currently working on a project with complicated reports. When the
report size grow to several MB, editing the report costs a lot of time.
Does anyone have similar experience? And is this essential to represent the
report layout in XML rather than binary? If the report is in binary and can
be exported to XML, would it be better?Using the RDL reader/writer you can have your report in Assemblies and
serialize or deserialze whenever you want:
http://www.rdlcomponents.com
Thanks
Jerry
"Johnny Chow" wrote:
> As we all know, SDL is represented as XML format. I found that representing
> the report layout in XML make the size of report grow very fast and hurt the
> performance significantly.
> I am currently working on a project with complicated reports. When the
> report size grow to several MB, editing the report costs a lot of time.
> Does anyone have similar experience? And is this essential to represent the
> report layout in XML rather than binary? If the report is in binary and can
> be exported to XML, would it be better?
>|||SDL?
Do you mean RDL?
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Johnny Chow" <JohnnyChow@.discussions.microsoft.com> wrote in message
news:52EB2EFC-A3B8-4481-85D9-7128F96B606C@.microsoft.com...
> As we all know, SDL is represented as XML format. I found that
> representing
> the report layout in XML make the size of report grow very fast and hurt
> the
> performance significantly.
> I am currently working on a project with complicated reports. When the
> report size grow to several MB, editing the report costs a lot of time.
> Does anyone have similar experience? And is this essential to represent
> the
> report layout in XML rather than binary? If the report is in binary and
> can
> be exported to XML, would it be better?
>

Discrepancy in number of rows and size of database

I'm trying to help a customer extract some data out of their SQL Server
database and am confused as to what I see.
First of all, this is my second day working with MS SQL Server so I'm not at
all up to speed on it as I am with other RDBMs.
This database is almost a gigabyte is size. There are roughly 25 user
tables in it. None of these tables have more than 250 records and none of
these records are very large.
According to the customer they thousands of records when they run the
application tha tuses this database. But I don't see them.
Could they be under a different owner? I see all of the "DBO" owned tables.
Unfortunately this customer doesn't have any documentation on the system and
the consultant who sold the system to them is long gone.
Any ideas?
TIA
Scott Huerta
Distinctive Solutions
Scott,
Database has reserved space, it does not have to be full. So I guess this is
the discrepancy you see.
If you are a member of the sysadmin role, you see all objects, no matter of
owner.
Where are the missing rows? I don't know. Check the Profiler tool in Books
OnLine, with this tool you can catch all commands SQL Server is receiving.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Scott Huerta" <Scott Huerta@.discussions.microsoft.com> wrote in message
news:EDE920CD-F82C-48BC-AA10-FC5885BCC4D3@.microsoft.com...
> I'm trying to help a customer extract some data out of their SQL Server
> database and am confused as to what I see.
> First of all, this is my second day working with MS SQL Server so I'm not
at
> all up to speed on it as I am with other RDBMs.
> This database is almost a gigabyte is size. There are roughly 25 user
> tables in it. None of these tables have more than 250 records and none of
> these records are very large.
> According to the customer they thousands of records when they run the
> application tha tuses this database. But I don't see them.
> Could they be under a different owner? I see all of the "DBO" owned
tables.
> Unfortunately this customer doesn't have any documentation on the system
and
> the consultant who sold the system to them is long gone.
> Any ideas?
> TIA
> Scott Huerta
> Distinctive Solutions
|||Thanks for the reply Dejan.
How do I make sure that I'm a member of the SysAdmin role? I think I might
already be as I can see system (i.e. "SYS...") tables and objects.
"Dejan Sarka" wrote:

> Scott,
> Database has reserved space, it does not have to be full. So I guess this is
> the discrepancy you see.
> If you are a member of the sysadmin role, you see all objects, no matter of
> owner.
> Where are the missing rows? I don't know. Check the Profiler tool in Books
> OnLine, with this tool you can catch all commands SQL Server is receiving.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "Scott Huerta" <Scott Huerta@.discussions.microsoft.com> wrote in message
> news:EDE920CD-F82C-48BC-AA10-FC5885BCC4D3@.microsoft.com...
> at
> tables.
> and
>
>
|||try sp_helpsrvrolemember @.srvrolename = 'sysadmin'
Bob Castleman
SuccessWare Software
"Scott Huerta" <Scott Huerta@.discussions.microsoft.com> wrote in message
news:76B2BB80-962A-4AE3-805A-3240D8EC5BCE@.microsoft.com...
> Thanks for the reply Dejan.
> How do I make sure that I'm a member of the SysAdmin role? I think I
> might
> already be as I can see system (i.e. "SYS...") tables and objects.
>

Discrepancy in number of rows and size of database

I'm trying to help a customer extract some data out of their SQL Server
database and am confused as to what I see.
First of all, this is my second day working with MS SQL Server so I'm not at
all up to speed on it as I am with other RDBMs.
This database is almost a gigabyte is size. There are roughly 25 user
tables in it. None of these tables have more than 250 records and none of
these records are very large.
According to the customer they thousands of records when they run the
application tha tuses this database. But I don't see them.
Could they be under a different owner? I see all of the "DBO" owned tables.
Unfortunately this customer doesn't have any documentation on the system and
the consultant who sold the system to them is long gone.
Any ideas?
TIA
Scott Huerta
Distinctive SolutionsScott,
Database has reserved space, it does not have to be full. So I guess this is
the discrepancy you see.
If you are a member of the sysadmin role, you see all objects, no matter of
owner.
Where are the missing rows? I don't know. Check the Profiler tool in Books
OnLine, with this tool you can catch all commands SQL Server is receiving.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Scott Huerta" <Scott Huerta@.discussions.microsoft.com> wrote in message
news:EDE920CD-F82C-48BC-AA10-FC5885BCC4D3@.microsoft.com...
> I'm trying to help a customer extract some data out of their SQL Server
> database and am confused as to what I see.
> First of all, this is my second day working with MS SQL Server so I'm not
at
> all up to speed on it as I am with other RDBMs.
> This database is almost a gigabyte is size. There are roughly 25 user
> tables in it. None of these tables have more than 250 records and none of
> these records are very large.
> According to the customer they thousands of records when they run the
> application tha tuses this database. But I don't see them.
> Could they be under a different owner? I see all of the "DBO" owned
tables.
> Unfortunately this customer doesn't have any documentation on the system
and
> the consultant who sold the system to them is long gone.
> Any ideas?
> TIA
> Scott Huerta
> Distinctive Solutions|||Thanks for the reply Dejan.
How do I make sure that I'm a member of the SysAdmin role? I think I might
already be as I can see system (i.e. "SYS...") tables and objects.
"Dejan Sarka" wrote:
> Scott,
> Database has reserved space, it does not have to be full. So I guess this is
> the discrepancy you see.
> If you are a member of the sysadmin role, you see all objects, no matter of
> owner.
> Where are the missing rows? I don't know. Check the Profiler tool in Books
> OnLine, with this tool you can catch all commands SQL Server is receiving.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "Scott Huerta" <Scott Huerta@.discussions.microsoft.com> wrote in message
> news:EDE920CD-F82C-48BC-AA10-FC5885BCC4D3@.microsoft.com...
> > I'm trying to help a customer extract some data out of their SQL Server
> > database and am confused as to what I see.
> >
> > First of all, this is my second day working with MS SQL Server so I'm not
> at
> > all up to speed on it as I am with other RDBMs.
> >
> > This database is almost a gigabyte is size. There are roughly 25 user
> > tables in it. None of these tables have more than 250 records and none of
> > these records are very large.
> >
> > According to the customer they thousands of records when they run the
> > application tha tuses this database. But I don't see them.
> >
> > Could they be under a different owner? I see all of the "DBO" owned
> tables.
> >
> > Unfortunately this customer doesn't have any documentation on the system
> and
> > the consultant who sold the system to them is long gone.
> >
> > Any ideas?
> >
> > TIA
> >
> > Scott Huerta
> > Distinctive Solutions
>
>|||try sp_helpsrvrolemember @.srvrolename = 'sysadmin'
Bob Castleman
SuccessWare Software
"Scott Huerta" <Scott Huerta@.discussions.microsoft.com> wrote in message
news:76B2BB80-962A-4AE3-805A-3240D8EC5BCE@.microsoft.com...
> Thanks for the reply Dejan.
> How do I make sure that I'm a member of the SysAdmin role? I think I
> might
> already be as I can see system (i.e. "SYS...") tables and objects.
>

Disaster....

Scenario:
w2k server sp4
sql server 2k sp3a
I had on USB external disk (160gb from maxtor) 3,34 GB MDF file and LDF
of some size.
Yesterday night I encountered some read errors on it.
W2k suggests me to run CHKDSK /F, that I obviously do.
When system restarts that 3,34 GB MDF file is now size 0kb!!!
Now...
I know that I should have a backup but these data aren't so
important...but If I can retrieve them is better than nothing !!!!!
I'm trying to restore some files with lots of recovery tools but I
notice that all of them find thousands of files...
How can I do, if possible, to obtain my MDF back "full" of data?
Any help MUCH MUCH appreciated...
Best Regards.
F.No need to multi-post... I posted an answer in *.setup...
Steve
"Fabri" <no@.sp.am> wrote in message
news:UP5Md.485761$b5.22987015@.news3.tin.it...
> Scenario:
> w2k server sp4
> sql server 2k sp3a
>
> I had on USB external disk (160gb from maxtor) 3,34 GB MDF file and LDF
> of some size.
> Yesterday night I encountered some read errors on it.
<snip>

Disaster..

Scenario:
w2k server sp4
sql server 2k sp3a
I had on USB external disk (160gb from maxtor) 3,34 GB MDF file and LDF
of some size.
Yesterday night I encountered some read errors on it.
W2k suggests me to run CHKDSK /F, that I obviously do.
When system restarts that 3,34 GB MDF file is now size 0kb!!!
Now...
I know that I should have a backup but these data aren't so
important...but If I can retrieve them is better than nothing !!!!!
I'm trying to restore some files with lots of recovery tools but I
notice that all of them find thousands of files...
How can I do, if possible, to obtain my MDF back "full" of data?
Any help MUCH MUCH appreciated...
Best Regards.
F.
Fabri,
You really shouldn't put SQL Server data files on an exernal USB drive.
Anyway, there's not much you can do in this instance unless you send the
drive off to a specialist for repair (expensive). I have heard that
putting it in the freezer for a few hours can revive it. However, if
your CHKDSK has overwritten or "fixed" sectors on the disk, it is
unlikely you will get the data back without a corrupt MDF file.
I think your only choices here are to restore from backup, or start again.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Fabri wrote:
> Scenario:
> w2k server sp4
> sql server 2k sp3a
>
> I had on USB external disk (160gb from maxtor) 3,34 GB MDF file and LDF
> of some size.
> Yesterday night I encountered some read errors on it.
> W2k suggests me to run CHKDSK /F, that I obviously do.
> When system restarts that 3,34 GB MDF file is now size 0kb!!!
> Now...
> I know that I should have a backup but these data aren't so
> important...but If I can retrieve them is better than nothing !!!!!
> I'm trying to restore some files with lots of recovery tools but I
> notice that all of them find thousands of files...
> How can I do, if possible, to obtain my MDF back "full" of data?
> Any help MUCH MUCH appreciated...
> Best Regards.
>

Disaster..

Scenario:
w2k server sp4
sql server 2k sp3a
I had on USB external disk (160gb from maxtor) 3,34 GB MDF file and LDF
of some size.
Yesterday night I encountered some read errors on it.
W2k suggests me to run CHKDSK /F, that I obviously do.
When system restarts that 3,34 GB MDF file is now size 0kb!!!
Now...
I know that I should have a backup but these data aren't so
important...but If I can retrieve them is better than nothing !!!!!
I'm trying to restore some files with lots of recovery tools but I
notice that all of them find thousands of files...
How can I do, if possible, to obtain my MDF back "full" of data?
Any help MUCH MUCH appreciated...
Best Regards.
F.Fabri,
You really shouldn't put SQL Server data files on an exernal USB drive.
Anyway, there's not much you can do in this instance unless you send the
drive off to a specialist for repair (expensive). I have heard that
putting it in the freezer for a few hours can revive it. However, if
your CHKDSK has overwritten or "fixed" sectors on the disk, it is
unlikely you will get the data back without a corrupt MDF file.
I think your only choices here are to restore from backup, or start again.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Fabri wrote:
> Scenario:
> w2k server sp4
> sql server 2k sp3a
>
> I had on USB external disk (160gb from maxtor) 3,34 GB MDF file and LDF
> of some size.
> Yesterday night I encountered some read errors on it.
> W2k suggests me to run CHKDSK /F, that I obviously do.
> When system restarts that 3,34 GB MDF file is now size 0kb!!!
> Now...
> I know that I should have a backup but these data aren't so
> important...but If I can retrieve them is better than nothing !!!!!
> I'm trying to restore some files with lots of recovery tools but I
> notice that all of them find thousands of files...
> How can I do, if possible, to obtain my MDF back "full" of data?
> Any help MUCH MUCH appreciated...
> Best Regards.
>

Disaster..

Scenario:
w2k server sp4
sql server 2k sp3a
I had on USB external disk (160gb from maxtor) 3,34 GB MDF file and LDF
of some size.
Yesterday night I encountered some read errors on it.
W2k suggests me to run CHKDSK /F, that I obviously do.
When system restarts that 3,34 GB MDF file is now size 0kb!!!
Now...
I know that I should have a backup but these data aren't so
important...but If I can retrieve them is better than nothing !!!!!
I'm trying to restore some files with lots of recovery tools but I
notice that all of them find thousands of files...
How can I do, if possible, to obtain my MDF back "full" of data?
Any help MUCH MUCH appreciated...
Best Regards.
--
F.Fabri,
You really shouldn't put SQL Server data files on an exernal USB drive.
Anyway, there's not much you can do in this instance unless you send the
drive off to a specialist for repair (expensive). I have heard that
putting it in the freezer for a few hours can revive it. However, if
your CHKDSK has overwritten or "fixed" sectors on the disk, it is
unlikely you will get the data back without a corrupt MDF file.
I think your only choices here are to restore from backup, or start again.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Fabri wrote:
> Scenario:
> w2k server sp4
> sql server 2k sp3a
>
> I had on USB external disk (160gb from maxtor) 3,34 GB MDF file and LDF
> of some size.
> Yesterday night I encountered some read errors on it.
> W2k suggests me to run CHKDSK /F, that I obviously do.
> When system restarts that 3,34 GB MDF file is now size 0kb!!!
> Now...
> I know that I should have a backup but these data aren't so
> important...but If I can retrieve them is better than nothing !!!!!
> I'm trying to restore some files with lots of recovery tools but I
> notice that all of them find thousands of files...
> How can I do, if possible, to obtain my MDF back "full" of data?
> Any help MUCH MUCH appreciated...
> Best Regards.
>

disable logging

Is there is a way to disable logging as i need to
transfer 4000000 records from oracle..I am doing throgh
this DTS..my transaction los size grows more than 10 GB..
Hi,
To prevent the transaction log from running out of space, a minimally logged
bulk copy can be performed if all of these conditions are met:-
a.. The recovery model is simple or bulk-logged.
b.. The target table is not being replicated.
c.. The target table does not have any triggers.
d.. The target table with no indexes (Remove and create after loading).
The model bulk_logged / simple recovery will prevent the bulk copy
operations from using excessive log space and possibly filling the log.
However, even with bulk-logged recovery, some transaction log space will be
used. You may want to create transaction log backups during the bulk copy
operation to free up transaction log space.
Note:
After the operation change the recovery to FULL and then perform afull
system backup and then follow ur transaction log backup (If you have).
This bring back ur backup chain.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:ba7e01c43801$d0b6f330$a401280a@.phx.gbl...
> Is there is a way to disable logging as i need to
> transfer 4000000 records from oracle..I am doing throgh
> this DTS..my transaction los size grows more than 10 GB..
>
|||Hi,
To prevent the transaction log from running out of space, a minimally logged
bulk copy can be performed if all of these conditions are met:-
a.. The recovery model is simple or bulk-logged.
b.. The target table is not being replicated.
c.. The target table does not have any triggers.
d.. The target table with no indexes (Remove and create after loading).
The model bulk_logged / simple recovery will prevent the bulk copy
operations from using excessive log space and possibly filling the log.
However, even with bulk-logged recovery, some transaction log space will be
used. You may want to create transaction log backups during the bulk copy
operation to free up transaction log space.
Note:
After the operation change the recovery to FULL and then perform afull
system backup and then follow ur transaction log backup (If you have).
This bring back ur backup chain.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:ba7e01c43801$d0b6f330$a401280a@.phx.gbl...
> Is there is a way to disable logging as i need to
> transfer 4000000 records from oracle..I am doing throgh
> this DTS..my transaction los size grows more than 10 GB..
>