Database Statistics

I am trying to find an efficient way to get the number of times that each ro
w
in a table is selected. I want to avoid using triggers.
Any ideas?
Thanks
John
jpd0861@.msn.comIf you're using stored procedures to access data, then I'd simply suggest
extending those to implement table-use auditing. Of course you'd need to kee
p
statistics in a separate table - something like:
PK_column : AccesedTime (default getdate()) : AccessedBy (default system_use
r)
If your db design allows direct access to tables, then maybe your auditing
requirement is just another point in favour of using procedures.
ML|||From the good news/bad news category:
Good news. We don't have SELECT triggers, so you wont have any problem
Bad news, there really isn't any other way to do this unless you are using
stored procedures, and even then it would be a big drag on performance.
Or I am missing something?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:77D095BA-04A6-4EB7-83EB-57B28AA21C51@.microsoft.com...
>I am trying to find an efficient way to get the number of times that each
>row
> in a table is selected. I want to avoid using triggers.
> Any ideas?
> Thanks
> John
> jpd0861@.msn.com|||Thank you Louis, I was not clear in my original message. I know that there
are no SELECT triggers in SQL Server...just good old INSERT, UPDATE, and
DELETE.
FYI, I did get an answer about using table-use auditing from ML that seems
to make sense.
"Louis Davidson" wrote:

> From the good news/bad news category:
> Good news. We don't have SELECT triggers, so you wont have any problem
> Bad news, there really isn't any other way to do this unless you are using
> stored procedures, and even then it would be a big drag on performance.
> Or I am missing something?
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:77D095BA-04A6-4EB7-83EB-57B28AA21C51@.microsoft.com...
>
>|||I agree, I just wanted to be clear that without procs this would not be
possible. I rarely expect that people are actually using stored procedures,
but clearly this is yet another reason why they are so excellent.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:6D10890D-C733-4673-852F-81C747F2EF10@.microsoft.com...
> Thank you Louis, I was not clear in my original message. I know that there
> are no SELECT triggers in SQL Server...just good old INSERT, UPDATE, and
> DELETE.
> FYI, I did get an answer about using table-use auditing from ML that seems
> to make sense.
> "Louis Davidson" wrote:
>

Database startup time

Is it possible to find somewhere in the system table time of sql server startup?

Thanks

Hi, as the tempdb is created at very startup of the SQL Server service you can check the information on

sp_helpdb tempdb

HTH, Jens Suessmeyer,

http://www.sqlserver2005.de

sql

database speed in SQL server

Hi there,
My program in Delphi have 7 table with about 120,000 records and 200 fields.
I use SQL server for my database.
When my program launchs its speed is too low.
What shall I do to increase its speed?
Regards,
Doroodgarit probably has nothing to do with the number of tables and records in the
database. you can try several things:
* delay creating unnecessary objects and forms, and create them as needed
* delay connecting to the database, and again connect only when needed
* move time-consuming processing to background thread, if possible
* show splash screen, it will make the appearance better :)
in general, minimize the amount of work you perform on load.
btw, those '200 fields' seem pretty much..
dean
"doroodgar" <rdoroodgar@.noornet.net> wrote in message
news:uTKCZCpDFHA.148@.TK2MSFTNGP14.phx.gbl...
> Hi there,
> My program in Delphi have 7 table with about 120,000 records and 200
fields.
> I use SQL server for my database.
> When my program launchs its speed is too low.
> What shall I do to increase its speed?
> Regards,
> Doroodgar
>|||HI doroodgar,
You sound convinced SQL slow your application purely on the fact the you
return tons of records so I'll work with that.
Why do you need that size recordset on application launch ? This confuse me
a bit cause you load the records based on what ?
What does your application do ? Is there some sort of logic to the records
that you return, is it possible that you can narrow it down , you present
120k records on one page anyways... should you maybe try and load 100 at a
time ?
How do you application present the 200 fields ?
Is it a straight select statement that populate your recordset or do you use
joins ?
What's the timing compared to when you run it through QA ?
Hope this will help
Can you give more info regarding your query and application ?
"doroodgar" wrote:

> Hi there,
> My program in Delphi have 7 table with about 120,000 records and 200 field
s.
> I use SQL server for my database.
> When my program launchs its speed is too low.
> What shall I do to increase its speed?
> Regards,
> Doroodgar
>
>|||http://www.sql-server-performance.com
Regards
Mike
"Mal" wrote:
> HI doroodgar,
> You sound convinced SQL slow your application purely on the fact the you
> return tons of records so I'll work with that.
> Why do you need that size recordset on application launch ? This confuse m
e
> a bit cause you load the records based on what ?
> What does your application do ? Is there some sort of logic to the records
> that you return, is it possible that you can narrow it down , you present
> 120k records on one page anyways... should you maybe try and load 100 at a
> time ?
> How do you application present the 200 fields ?
> Is it a straight select statement that populate your recordset or do you u
se
> joins ?
> What's the timing compared to when you run it through QA ?
> Hope this will help
> Can you give more info regarding your query and application ?
> "doroodgar" wrote:
>sql

DataBase Space

Hello, All!
I'm creating a script to collect DataBase Space used and DataBase space Total
and after that I will store this information in a table in my SQL Server.
I've tryed using sp_spaceused but I don't know how to use the result of this
StoreProcedure.
If anyone know how to get this information using either sp_spaceused results
or t-sql script example, it would be great for me.
Thanks
Juliano Horta
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200602/1
> I'm creating a script to collect DataBase Space used and DataBase space
> Total
> and after that I will store this information in a table in my SQL Server.
> I've tryed using sp_spaceused but I don't know how to use the result of
> this
> StoreProcedure.
> If anyone know how to get this information using either sp_spaceused
> results
> or t-sql script example, it would be great for me.
Why don't you look at the source code for sp_spaceused and adapt it for your
own needs?
|||Use the undocumented command DBCC ShowFileStats. It supports the WITH
TABLERESULTS option so you can dump the data into a table and work with it.
This is what Enterprise Mangler uses to populate the file used graphs on the
Taskpad pane.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Juliano H via droptable.com" <u13014@.uwe> wrote in message
news:5b3d9946b1a22@.uwe...
> Hello, All!
> I'm creating a script to collect DataBase Space used and DataBase space
> Total
> and after that I will store this information in a table in my SQL Server.
> I've tryed using sp_spaceused but I don't know how to use the result of
> this
> StoreProcedure.
> If anyone know how to get this information using either sp_spaceused
> results
> or t-sql script example, it would be great for me.
> Thanks
> Juliano Horta
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200602/1
|||Hi,
Try this to see whats in sp_spaceused...
sp_helptext 'sp_spaceused'
Thanks,
Sree
"Geoff N. Hiten" wrote:

> Use the undocumented command DBCC ShowFileStats. It supports the WITH
> TABLERESULTS option so you can dump the data into a table and work with it.
> This is what Enterprise Mangler uses to populate the file used graphs on the
> Taskpad pane.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Juliano H via droptable.com" <u13014@.uwe> wrote in message
> news:5b3d9946b1a22@.uwe...
>
>

Database Snapshots Performance

Hi,
I'm developing a Data Mart and i'm experiencing a performance gap between my fact table and its snapshot.
I create snapshot with the istruction:

CREATE DATABASE DB_SNAP ON

( NAME = DB_SNAP_Data, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB_SNAP_Data.ss' )

AS SNAPSHOT OF DB;

And it works. But executing queries on the snapshot result very "low".
Can anyone tell me why?

Tanks.

F.

There is a section in Books Online titled "How Database Snapshots Work" which shows the extra level of redirection for snapshots. For a newly created snapshot, the data will not be in cache so it might take a little while to pull the data into the buffer pool. Once it is "warmed up" though, the performance should not be that different.

You can look at the sys.dm_db_index_operational_stats and sys.dm_io_virtual_file_stats DMVs to try to determine where the issues are.

|||Yes, but I always have a "newly created snapshot". Infact, to update the snapshot, i need to drop and re-create. And i do this every day at least.|||

So, do the performance problems persist, or are they temporary until the cache is populated?

This will help narrow down where the problem may be.

Database Snapshots Performance

Hi,
I'm developing a Data Mart and i'm experiencing a performance gap between my fact table and its snapshot.
I create snapshot with the istruction:

CREATE DATABASE DB_SNAP ON

( NAME = DB_SNAP_Data, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB_SNAP_Data.ss' )

AS SNAPSHOT OF DB;

And it works. But executing queries on the snapshot result very "low".
Can anyone tell me why?

Tanks.

F.

There is a section in Books Online titled "How Database Snapshots Work" which shows the extra level of redirection for snapshots. For a newly created snapshot, the data will not be in cache so it might take a little while to pull the data into the buffer pool. Once it is "warmed up" though, the performance should not be that different.

You can look at the sys.dm_db_index_operational_stats and sys.dm_io_virtual_file_stats DMVs to try to determine where the issues are.

|||Yes, but I always have a "newly created snapshot". Infact, to update the snapshot, i need to drop and re-create. And i do this every day at least.|||

So, do the performance problems persist, or are they temporary until the cache is populated?

This will help narrow down where the problem may be.

Database Snapshots

Hi,
I'm developing a Data Mart and i'm experiencing a performance gap between my fact table and its snapshot.
I create snapshot with the istruction:

CREATE DATABASE DB_SNAP ON

( NAME = DB_SNAP_Data, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB_SNAP_Data.ss' )

AS SNAPSHOT OF DB;

And it works. But executing queries on the snapshot result very "low".
Can anyone tell me why?

Tanks.

F.Can you please explain the term about 'low', is it the data size or data?|||

Satya SKJ wrote:

Can you please explain the term about 'low', is it the data size or data?

It was "Slow" and not "low". Sorry.
I mean the time to execute a query.|||

It is by design@.

Performance is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.

|||

Satya SKJ wrote:

It is by design@.

Performance is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.

Ok. I read that note.
But what does it mean "Performance is reduced"?
Query on the normal table executed in about 40 seconds; on the snapshot it's over 8 minutes...

Database Snapshot Performance

Hi,
I'm developing a Data Mart and i'm experiencing a performance gap
between my fact table and its snapshot.
I create snapshot with the istruction:
CREATE DATABASE DB_SNAP ON ( NAME = DB_SNAP_Data, FILENAME = 'C:
\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
\DB_SNAP_Data.ss' ) AS SNAPSHOT OF DB;
And it works. But executing queries on the snapshot result very
"Slow".
Can anyone tell me why?
Tanks.
F.
A snapshot is slow because it makes a copy of of modified data in tempdb.
Thus reads are scattered all over. The real question is why would you NEED
a snapshot of a fact table? This is non-standard DW practice AFAIK.
TheSQLGuru
President
Indicium Resources, Inc.
"Johnny" <xxx.johnny@.gmail.com> wrote in message
news:1171290125.043815.129030@.v33g2000cwv.googlegr oups.com...
> Hi,
> I'm developing a Data Mart and i'm experiencing a performance gap
> between my fact table and its snapshot.
> I create snapshot with the istruction:
> CREATE DATABASE DB_SNAP ON ( NAME = DB_SNAP_Data, FILENAME = 'C:
> \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
> \DB_SNAP_Data.ss' ) AS SNAPSHOT OF DB;
> And it works. But executing queries on the snapshot result very
> "Slow".
> Can anyone tell me why?
> Tanks.
> F.
>

Database Snapshot Performance

Hi,
I'm developing a Data Mart and i'm experiencing a performance gap
between my fact table and its snapshot.
I create snapshot with the istruction:
CREATE DATABASE DB_SNAP ON ( NAME = DB_SNAP_Data, FILENAME = 'C:
\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
\DB_SNAP_Data.ss' ) AS SNAPSHOT OF DB;
And it works. But executing queries on the snapshot result very
"Slow".
Can anyone tell me why?
Tanks.
F.A snapshot is slow because it makes a copy of of modified data in tempdb.
Thus reads are scattered all over. The real question is why would you NEED
a snapshot of a fact table? This is non-standard DW practice AFAIK.
TheSQLGuru
President
Indicium Resources, Inc.
"Johnny" <xxx.johnny@.gmail.com> wrote in message
news:1171290125.043815.129030@.v33g2000cwv.googlegroups.com...
> Hi,
> I'm developing a Data Mart and i'm experiencing a performance gap
> between my fact table and its snapshot.
> I create snapshot with the istruction:
> CREATE DATABASE DB_SNAP ON ( NAME = DB_SNAP_Data, FILENAME = 'C:
> \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
> \DB_SNAP_Data.ss' ) AS SNAPSHOT OF DB;
> And it works. But executing queries on the snapshot result very
> "Slow".
> Can anyone tell me why?
> Tanks.
> F.
>

Database sizing question

Hi,

I have 1.3 GB ASII file for each month to be tranferred to a table. I want to get 18 months( 18 files) so 18*1.3 GB will be my total file size. Now I am transferring one file at a time to the table using INSERT. I have an index on one column only ( NON CLUSTERED NON UNIQUE). My question is what should be my data file size and transaction log file size for this operation. I will only be doing SELECT and INSERT in this table. Any other SQL server parameters that I need to take care of then please let me know.

ThanksHowdy

Without knowing the table datatypes used in the table columns, its difficult to know. It can be calculated but its time consuming and probably more hassle than you want.

Try this ( its easier and faster ) :

(1) Create the table you are importing data to.

(2) Switch the database into Bulk Insert or Simple recovery mode during the data load ( this will stop the transaction log becoming huge and is good practice anyway during a large data load into a database ).

(3) Once the data is loaded, re-create the index. This will ensure the database is "clean" and the tran logs is as small as possible. During normal database operation ( inserts & updates ), the tran log will grow of course, if the recovery mode is FULL or BULK LOGGED.

(4) Change the database back into FULL recovery mode ( assuming you want to be able to recover the database to apoint in time ) and start using it.

Cheers,

SG.

Database size vs total table and index size

I use SQL Server 2000.
My database is 1912.69 MB with no available free space.
My logfile is 1 MB.
The size of all tables and indexes add up to 200 MB.
I have no diagrams, two views, fifty stored procedures, six users, ten
roles, no rules, no defaults, no user defined data types, no user
defined functions.
Autoshrink is set to true.
My question:
How can the database be almost 2 GB when the tables and indexes add up
to only 200MB?
When I try to shrink manually in SQL Enterprise Manager I get no error
message, but no shrinking occurs.
I am grateful for any help.
Regards,
Jan Nordgreen
Reply"damezumari" wrote:
> I use SQL Server 2000.
> My database is 1912.69 MB with no available free space.
> My logfile is 1 MB.
> The size of all tables and indexes add up to 200 MB.
> I have no diagrams, two views, fifty stored procedures, six users, ten
> roles, no rules, no defaults, no user defined data types, no user
> defined functions.
> Autoshrink is set to true.
> My question:
> How can the database be almost 2 GB when the tables and indexes add up
> to only 200MB?
> When I try to shrink manually in SQL Enterprise Manager I get no error
> message, but no shrinking occurs.
> I am grateful for any help.
> Regards,
> Jan Nordgreen
This sounds VERY unusual: database sizes that are 10 times bigger than the
actual datasize are absolutely normal and there's a lot of reasons for that
-
but they usually have something to do with the TA-Log. Please verify that
your transaction log file is really that tiny, and publish the syntax of you
r
shrink statement ...|||First, I don=B4t think that this is TA related, because your TA size is
1MB which is really , really small.
Your issue could be based on several things like:
You can=B4t shrink the database size under the initial size, so if the
initial size was 2GB (which isn=B4t unusal and not that big) you can=B4t
shrink it with DBCC Shrinkdatabase. Look in the BOL for more
information:
"The target size for data and log files as calculated by DBCC
SHRINKDATABASE can never be smaller than the minimum size of a file.
The minimum size of a file is the size specified when the file was
originally created, or the last explicit size set with a file size
changing operation, such as DBCC SHRINKFILE."
You can shrink the database using DBCC Shrinkfile, where you can
specify a new size of a single file. Look in the BOL for more
information.
Anyway, shrinking your file to a smaller size than 2GB could decrease
performance if your database is growing and gaining automatically new
space. So shrinking the database to 200MB would cause a halt if the
size has to be extended, causing waiting processes to be stopped until
the new size is aquired from the OS:
HTH, Jens Suessmeyer.

Database size vs total table and index size

I use SQL Server 2000.
My database is 1912.69 MB with no available free space.
My logfile is 1 MB.
The size of all tables and indexes add up to 200 MB.
I have no diagrams, two views, fifty stored procedures, six users, ten
roles, no rules, no defaults, no user defined data types, no user
defined functions.
Autoshrink is set to true.
My question:
How can the database be almost 2 GB when the tables and indexes add up
to only 200MB?
When I try to shrink manually in SQL Enterprise Manager I get no error
message, but no shrinking occurs.
I am grateful for any help.
Regards,
Jan Nordgreen
Reply
"damezumari" wrote:
> I use SQL Server 2000.
> My database is 1912.69 MB with no available free space.
> My logfile is 1 MB.
> The size of all tables and indexes add up to 200 MB.
> I have no diagrams, two views, fifty stored procedures, six users, ten
> roles, no rules, no defaults, no user defined data types, no user
> defined functions.
> Autoshrink is set to true.
> My question:
> How can the database be almost 2 GB when the tables and indexes add up
> to only 200MB?
> When I try to shrink manually in SQL Enterprise Manager I get no error
> message, but no shrinking occurs.
> I am grateful for any help.
> Regards,
> Jan Nordgreen
This sounds VERY unusual: database sizes that are 10 times bigger than the
actual datasize are absolutely normal and there's a lot of reasons for that -
but they usually have something to do with the TA-Log. Please verify that
your transaction log file is really that tiny, and publish the syntax of your
shrink statement ...
|||First, I don=B4t think that this is TA related, because your TA size is
1MB which is really , really small.
Your issue could be based on several things like:
You can=B4t shrink the database size under the initial size, so if the
initial size was 2GB (which isn=B4t unusal and not that big) you can=B4t
shrink it with DBCC Shrinkdatabase. Look in the BOL for more
information:
"The target size for data and log files as calculated by DBCC
SHRINKDATABASE can never be smaller than the minimum size of a file.
The minimum size of a file is the size specified when the file was
originally created, or the last explicit size set with a file size
changing operation, such as DBCC SHRINKFILE."
You can shrink the database using DBCC Shrinkfile, where you can
specify a new size of a single file. Look in the BOL for more
information.
Anyway, shrinking your file to a smaller size than 2GB could decrease
performance if your database is growing and gaining automatically new
space. So shrinking the database to 200MB would cause a halt if the
size has to be extended, causing waiting processes to be stopped until
the new size is aquired from the OS:
HTH, Jens Suessmeyer.

Database size vs total table and index size

I use SQL Server 2000.
My database is 1912.69 MB with no available free space.
My logfile is 1 MB.
The size of all tables and indexes add up to 200 MB.
I have no diagrams, two views, fifty stored procedures, six users, ten
roles, no rules, no defaults, no user defined data types, no user
defined functions.
Autoshrink is set to true.
My question:
How can the database be almost 2 GB when the tables and indexes add up
to only 200MB?
When I try to shrink manually in SQL Enterprise Manager I get no error
message, but no shrinking occurs.
I am grateful for any help.
Regards,
Jan Nordgreen
Reply"damezumari" wrote:
> I use SQL Server 2000.
> My database is 1912.69 MB with no available free space.
> My logfile is 1 MB.
> The size of all tables and indexes add up to 200 MB.
> I have no diagrams, two views, fifty stored procedures, six users, ten
> roles, no rules, no defaults, no user defined data types, no user
> defined functions.
> Autoshrink is set to true.
> My question:
> How can the database be almost 2 GB when the tables and indexes add up
> to only 200MB?
> When I try to shrink manually in SQL Enterprise Manager I get no error
> message, but no shrinking occurs.
> I am grateful for any help.
> Regards,
> Jan Nordgreen
This sounds VERY unusual: database sizes that are 10 times bigger than the
actual datasize are absolutely normal and there's a lot of reasons for that -
but they usually have something to do with the TA-Log. Please verify that
your transaction log file is really that tiny, and publish the syntax of your
shrink statement ...|||First, I don=B4t think that this is TA related, because your TA size is
1MB which is really , really small.
Your issue could be based on several things like:
You can=B4t shrink the database size under the initial size, so if the
initial size was 2GB (which isn=B4t unusal and not that big) you can=B4t
shrink it with DBCC Shrinkdatabase. Look in the BOL for more
information:
"The target size for data and log files as calculated by DBCC
SHRINKDATABASE can never be smaller than the minimum size of a file.
The minimum size of a file is the size specified when the file was
originally created, or the last explicit size set with a file size
changing operation, such as DBCC SHRINKFILE."
You can shrink the database using DBCC Shrinkfile, where you can
specify a new size of a single file. Look in the BOL for more
information.
Anyway, shrinking your file to a smaller size than 2GB could decrease
performance if your database is growing and gaining automatically new
space. So shrinking the database to 200MB would cause a halt if the
size has to be extended, causing waiting processes to be stopped until
the new size is aquired from the OS:
HTH, Jens Suessmeyer.

Database Size problems

I have a database that has grown much larger than I expect. There is a
specific table that seems to be unusally large. If I copy the data from that
table out into a temp table then copy it back in, it frees up over 90% of th
e
space that it was taking up.
I cannot alter the database structure at this point so is there a better way
to free this space up ? I have tried things like shrinkdb and shrinkfile and
whilst they create a little space its not anywhere near the level of moving
the data.
Does anyone have any ideas ?
SiSimon
How large is your database? Do you have limited space on the disk?
1) You can put the large table on different physical disk array
2) You use partition to "divide" the table
BTW , what is the version are you using?
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:A58E5518-5D24-4FBC-AE57-C50D822F75BB@.microsoft.com...
>I have a database that has grown much larger than I expect. There is a
> specific table that seems to be unusally large. If I copy the data from
> that
> table out into a temp table then copy it back in, it frees up over 90% of
> the
> space that it was taking up.
> I cannot alter the database structure at this point so is there a better
> way
> to free this space up ? I have tried things like shrinkdb and shrinkfile
> and
> whilst they create a little space its not anywhere near the level of
> moving
> the data.
> Does anyone have any ideas ?
> Si|||How many indexes do you have on that table? How often do you rebuild
the indexes?
It looks like your table had a lot of fragmentation, which as a result
made t use a lot more space than necessary.
Markus|||The database is around 40Gig
But one table is 35G of that. When I copy the data out and then back in it
becomes around 3G.
I`m using SQL2000.
I know I can split the data onto seperate disks but I was hoping to write a
job that will shrink the data down automatically.
"Uri Dimant" wrote:

> Simon
> How large is your database? Do you have limited space on the disk?
> 1) You can put the large table on different physical disk array
> 2) You use partition to "divide" the table
> BTW , what is the version are you using?
>
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:A58E5518-5D24-4FBC-AE57-C50D822F75BB@.microsoft.com...
>
>|||Simon wrote:
> The database is around 40Gig
> But one table is 35G of that. When I copy the data out and then back in it
> becomes around 3G.
> I`m using SQL2000.
> I know I can split the data onto seperate disks but I was hoping to write
a
> job that will shrink the data down automatically.
>
As Markus said, this sounds like a fragmentation problem. Use DBCC
SHOWCONTIG to check the fragmentation of the indexes on that table, and
have a look at this script of mine:
http://realsqlguy.com/twiki/bin/vie...IndexesAsNeeded
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Database Size problems

I have a database that has grown much larger than I expect. There is a
specific table that seems to be unusally large. If I copy the data from that
table out into a temp table then copy it back in, it frees up over 90% of the
space that it was taking up.
I cannot alter the database structure at this point so is there a better way
to free this space up ? I have tried things like shrinkdb and shrinkfile and
whilst they create a little space its not anywhere near the level of moving
the data.
Does anyone have any ideas ?
SiSimon
How large is your database? Do you have limited space on the disk?
1) You can put the large table on different physical disk array
2) You use partition to "divide" the table
BTW , what is the version are you using?
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:A58E5518-5D24-4FBC-AE57-C50D822F75BB@.microsoft.com...
>I have a database that has grown much larger than I expect. There is a
> specific table that seems to be unusally large. If I copy the data from
> that
> table out into a temp table then copy it back in, it frees up over 90% of
> the
> space that it was taking up.
> I cannot alter the database structure at this point so is there a better
> way
> to free this space up ? I have tried things like shrinkdb and shrinkfile
> and
> whilst they create a little space its not anywhere near the level of
> moving
> the data.
> Does anyone have any ideas ?
> Si|||How many indexes do you have on that table? How often do you rebuild
the indexes?
It looks like your table had a lot of fragmentation, which as a result
made t use a lot more space than necessary.
Markus|||The database is around 40Gig
But one table is 35G of that. When I copy the data out and then back in it
becomes around 3G.
I`m using SQL2000.
I know I can split the data onto seperate disks but I was hoping to write a
job that will shrink the data down automatically.
"Uri Dimant" wrote:
> Simon
> How large is your database? Do you have limited space on the disk?
> 1) You can put the large table on different physical disk array
> 2) You use partition to "divide" the table
> BTW , what is the version are you using?
>
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:A58E5518-5D24-4FBC-AE57-C50D822F75BB@.microsoft.com...
> >I have a database that has grown much larger than I expect. There is a
> > specific table that seems to be unusally large. If I copy the data from
> > that
> > table out into a temp table then copy it back in, it frees up over 90% of
> > the
> > space that it was taking up.
> > I cannot alter the database structure at this point so is there a better
> > way
> > to free this space up ? I have tried things like shrinkdb and shrinkfile
> > and
> > whilst they create a little space its not anywhere near the level of
> > moving
> > the data.
> >
> > Does anyone have any ideas ?
> >
> > Si
>
>|||Simon wrote:
> The database is around 40Gig
> But one table is 35G of that. When I copy the data out and then back in it
> becomes around 3G.
> I`m using SQL2000.
> I know I can split the data onto seperate disks but I was hoping to write a
> job that will shrink the data down automatically.
>
As Markus said, this sounds like a fragmentation problem. Use DBCC
SHOWCONTIG to check the fragmentation of the indexes on that table, and
have a look at this script of mine:
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql

database size increased twice

We had to move exsiting database on new server and ran querey copying all re
cords into new table on new server.
System said that new table was created successfully and all records were cop
ied.
We decided to backup this new database, but it failed. Investigating of back
up problem led us to the point where we realised that our actuall database b
ecome 2 times larger - 150GB insted of 75 (original size).
Kind of lost where this additional chunk came from.
Will be thankful for any suggestions,
Alan.Alan,
Did you create new database with the same data device and log device as the
original one?
Did you try to shrink the data files using DBCC?
larry
"ALan" <anonymous@.discussions.microsoft.com> wrote in message
news:49C0FD90-FFFA-4CE5-B8F8-0A43E1E8B22D@.microsoft.com...

quote:

---

> We had to move exsiting database on new server and ran querey copying all

---

records into new table on new server.

quote:

---

> System said that new table was created successfully and all records were

---

copied.

quote:

---

> We decided to backup this new database, but it failed. Investigating of

---

backup problem led us to the point where we realised that our actuall
database become 2 times larger - 150GB insted of 75 (original size).

quote:

---

> Kind of lost where this additional chunk came from.
> Will be thankful for any suggestions,
> Alan.

---

Database size entry?

Which system table is the currently defined size (hopefully the total size)
of the data and log devices found?
I'm assuming in master somewhere? sysobjects? I just can't find it...
thanksDave,
Check out:
sysfiles
HTH
Jerry
"Dave H" <DaveH@.noemail.nospam> wrote in message
news:JpydnanWHeJCj6HeRVn-ug@.comcast.com...
> Which system table is the currently defined size (hopefully the total
> size)
> of the data and log devices found?
> I'm assuming in master somewhere? sysobjects? I just can't find it...
> thanks
>|||That's what I'm doing now.. is that how 'properties' figures the size?
lol: I totally looked past size there, and was just getting the file
names...
Thanks...
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:ODlqHFRxFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Dave,
> Check out:
> sysfiles
> HTH
> Jerry
> "Dave H" <DaveH@.noemail.nospam> wrote in message
> news:JpydnanWHeJCj6HeRVn-ug@.comcast.com...
>|||Hi,
Its been taken from sysfiles table. You could just run a prfiler and get the
query. See the query I get for Master database property.
SELECT o.fileid, o.name, o.filename, o.groupid, o.size, o.maxsize, o.growth,
o.status FROM dbo.sysfiles o WHERE o.groupid = (SELECT u.groupid FROM
dbo.sysfilegroups u WHERE u.groupname = N'PRIMARY') and (o.status & 0x40) =
0
go
SELECT fileid, name, filename, size, growth, status, maxsize FROM
dbo.sysfiles WHERE (status & 0x40) <> 0
Thanks
Hari
SQL Server MVP
"Dave H" <DaveH@.noemail.nospam> wrote in message
news:ec-dnRXjTNF0iKHeRVn-rw@.comcast.com...
> That's what I'm doing now.. is that how 'properties' figures the size?
> lol: I totally looked past size there, and was just getting the file
> names...
> Thanks...
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:ODlqHFRxFHA.1256@.TK2MSFTNGP09.phx.gbl...
>

Database size allocation - where other than tables, logs, etc ?

I have a Db that is 1.7 gigs. The table data takes approximately 200megs. The transaction logs were truncated. Where else can this large size be coming from and how can I confirm?

DB is generally small. ~25 tables, 100 SPs, 10 views, etc.

Note:

I have 4 queues using SQL Notifications, but when selecting from them results in no data.

Thanks

Scott

sp_spaceused can give you some details about data space. sp_helpfile will show data and log file sizes.

You may just need to shrink your database to get the file sizes down using DBCC SHRINKDATABASE or SHRINKFILE.

Database Size

In the table called system files, we have a field called size = Size of the file (in 8-KB pages). And if i look at the physical drive on the explorer this Size is different from the one in the database on system tables. Could someone tell me how to calculate them.

Secondly, i can see that the dumpfile is stored in sysdevices table, but where can i get the size of this dumpfile (.bak) because is always 0 after i dumped the file and again is not 0 on the physical drive of the explorerAbout your 2nd question:

The lines located on the system table only provide the physical location of a logical dump files. The size is 0 in any case.sql

Database size

Hi all,
I need to estimate the disk space required for SQL Server
2000 and our application databases. Apart from the
table/index sizes (calculated as per logic given in BOL),
are there any other parameters to be considered to size
the disks ? Like more space for tempdb?
Is there any standard approach to estimate the disk space
required based on data growth?
Thanks,
HariHi Hari
Yes, the transaction log is a significant consideration. It is not possible
to accurately calculate size requirements for log space / growth as the log
storage format is not even published. Best bet with this is to make regular
observations, collect empirical data on growth rates between backup windows
& ensure you have enough space for log use / growth.
There's information on calculating database size in Books Online at:
http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_02_2h45.asp
The topic's also covered widely in books, including:
http://www.microsoft.com/mspress/books/toc/4944.asp#TableOfContents
HTH
Regards,
Greg Linwood
SQL Server MVP
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:295d801c464af$6021d1a0$a301280a@.phx.gbl...
> Hi all,
> I need to estimate the disk space required for SQL Server
> 2000 and our application databases. Apart from the
> table/index sizes (calculated as per logic given in BOL),
> are there any other parameters to be considered to size
> the disks ? Like more space for tempdb?
> Is there any standard approach to estimate the disk space
> required based on data growth?
> Thanks,
> Hari