Thursday, March 29, 2012

Determine the business day

If I have a calendar table, that has the business days of the entire year ie 2007.--How do I assign it a business day number for eg JUNE 2007 has 21 business days.. Hence on my calendar tbl, I have 21 records for June.. My question is:

How do i assign a business day number eg 6-1-07 is BUSDAY1, 6-4-07 Is Busday2, and so on..

Basicallly, I need to run an update on my calendar table, to indicate, what BUSDAY number it is ie 6-4-07 is BUSDAY 2, 6-5-07 IS Busday 3 and so on, for the entire year 2007.

Pl advise.

if you use sql server 2005 then you can use the Row_Number() ..

Code Snippet

Update MyCalendar

Set

BusinessDayNumber = 'BUSDAY' + Cast(data.Number as varchar)

From

(Select Date,Row_Number() OVER(Partition By Year,Month Order By Date) From MyCalendar) as Data

Where

Data.Date = MyCalendar.Date

if you use SQL Server 2000,

Code Snippet

Update MyCalendar

Set

BusinessDayNumber = (

Select 'BUSDAY' + Cast(Count(*) as Varchar) From MyCalendar Sub

Where

MyCalendar.Year = Sub.Year

and MyCalendar.Month = Sub.Month

and Sub.Date <= MyCalendar.Date

)

|||

Hi Tarana,

Which version of SS are you using?

-- 2005

;with cte

as

(

select

[date], BSnumber,

row_number() over(partition by year([date]), month([date]) order by [date]) as rn

from

dbo.calendar

where

IsBusinessDay = 1

)

update cte

set BDnumber = rn

-- 2000 / 2005

update dbo.calendar

set BDnumber = (

select

count(*)

from

dbo.calendar as c

where

year(c.[date]) = year(dbo.calendar.[date])

and month(c.[date]) = month(dbo.calendar.[date])

and c.[date] <= dbo.calendar.[date]

and c.IsBusinessDay = 1

)

where IsBusinessDay = 1

go

AMB

|||

You can determine the Day of the Week very easily:

selectdatepart(weekday,) from

Depending on the settings this will return 1 for sunday, 2 for Monday, 3 for Wednesday etc.

So:

Code Snippet

selectdatepart(weekday,<DateField>)-1 as [WorkDay]

from<TableName>

wheredatepart(weekday,<DateField>)between 2 and 6

|||THis code worked beautiful.sql

determine the assigned identity range

Is there any way to find out a subscriber's assigned identity range (at the
publisher)?
It would be helpful to have an idea where a row was added from its identity
value.
No, it only maintains the last range assigned - which will be the highest
one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<tedcorpus@.hotmail.com> wrote in message
news:%23F7cc61eFHA.412@.tk2msftngp13.phx.gbl...
> Is there any way to find out a subscriber's assigned identity range (at
the
> publisher)?
> It would be helpful to have an idea where a row was added from its
identity
> value.
>
|||In the distribution database, MSrepl_identity_range holds the next ranges,
so You'll have to work with the metadata tables on the subscribers for this:
You could use this type of query:
SELECT sysobjects.name AS TableName, *
FROM MSrepl_identity_range INNER JOIN
sysobjects ON MSrepl_identity_range.objid =
sysobjects.id
The data from each subscriber could be amalgamated at the publisher using
linked servers,
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks!
So I could compare max_identity/current_max columns across servers
(accounting for the range value). But linking the servers just for this
purpose might be more administration work than benefit at this point.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ehDGtl7eFHA.2076@.TK2MSFTNGP15.phx.gbl...
> In the distribution database, MSrepl_identity_range holds the next ranges,
> so You'll have to work with the metadata tables on the subscribers for
> this:
> You could use this type of query:
> SELECT sysobjects.name AS TableName, *
> FROM MSrepl_identity_range INNER JOIN
> sysobjects ON MSrepl_identity_range.objid =
> sysobjects.id
> The data from each subscriber could be amalgamated at the publisher using
> linked servers,
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||You only need to look at the max value on the publisher's distribution
database. That will be the next assigned range.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"TCorp" <tcorpus@.hotmail.com> wrote in message
news:eg1MgYagFHA.2548@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Thanks!
> So I could compare max_identity/current_max columns across servers
> (accounting for the range value). But linking the servers just for this
> purpose might be more administration work than benefit at this point.
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:ehDGtl7eFHA.2076@.TK2MSFTNGP15.phx.gbl...
ranges,[vbcol=seagreen]
using
>

determine tables that are Simple recovery

I need a sql statment to return a list of tables for a given database
where the Recovery Model option is Simple.
TIA
Rob"rcamarda" <rcamarda@.cablespeed.com> wrote in message
news:1129311952.833612.171530@.g14g2000cwa.googlegr oups.com...
>I need a sql statment to return a list of tables for a given database
> where the Recovery Model option is Simple.
> TIA
> Rob

The recovery model is a database-wide setting. There is no recovery model
option for individual tables.

You can determine the recovery model like this:

SELECT DATABASEPROPERTYEX('database_name', 'RECOVERY')

--
David Portas
SQL Server MVP
--|||Better Question. How can it tell the databases in my sql instance that
have a recovery model of 'Simple'?
Hope this makes sense now
Thanks|||Erp! Yes, replace(question,'Tables','Database')
Thanks!|||"rcamarda" <rcamarda@.cablespeed.com> wrote in message
news:1129328233.934256.231310@.g49g2000cwa.googlegr oups.com...
> Better Question. How can it tell the databases in my sql instance that
> have a recovery model of 'Simple'?
> Hope this makes sense now
> Thanks

SELECT catalog_name
FROM information_schema.schemata
WHERE DATABASEPROPERTYEX(catalog_name,'RECOVERY') = 'SIMPLE' ;

--
David Portas
SQL Server MVP
--|||Thank you very much! Ill be able to use this in my backup using SQLsafe
when I perform a log backup. I kept getting errors when it tried to
backup databases with simple model. Now I can skip them.

Determine Table's PK Columns

I want to do a query on a db's system tables to determine what column(s) are
part of the primary key.
Example.
Code:
SELECT syscolumns.[name] FROM sysobjects,syscolumns,systypes WHERE
sysobjects.id=syscolumns.id AND systypes.xtype=syscolumns.xtype AND
sysobjects.name='tablename'
Yields all the column names. But I just want to see those that are part of
the primary key. How?
Ryan
> SELECT syscolumns.[name] FROM sysobjects,syscolumns,systypes WHERE
> sysobjects.id=syscolumns.id AND systypes.xtype=syscolumns.xtype AND
> sysobjects.name='tablename'
shuldn't you use information_schema?
.~. Might, Courage, Vision. Sincerity. http://www.linux-sxs.org
/ v \
/( _ )\ (Ubuntu 5.10) Linux 2.6.14.3
^ ^ 22:37:07 up 9 days 2:31 load average: 3.12 1.63 0.80|||Try:
select
*
from
information_schema.constraint_column_usage
where
table_name = 'Order Details'
and objectproperty (object_id (constraint_name), 'IsPrimaryKey') = 1
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Ryan" <Ryan@.discussions.microsoft.com> wrote in message
news:037F8848-C843-4A77-B385-6994E6235CDA@.microsoft.com...
>I want to do a query on a db's system tables to determine what column(s)
>are
> part of the primary key.
> Example.
> Code:
> SELECT syscolumns.[name] FROM sysobjects,syscolumns,systypes WHERE
> sysobjects.id=syscolumns.id AND systypes.xtype=syscolumns.xtype AND
> sysobjects.name='tablename'
> Yields all the column names. But I just want to see those that are part of
> the primary key. How?
> Ryan|||Thank you both of you. Yes, Man-wai Chang, the information_schema looks lik
e
it's a lot easier than joining the system tables like I was doing. Thank
you, both.

Determine table size

Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
Mica
Hi Mica Cooper,
To Check table sizes, Select the db name in the enterprise under Databases,
In the Main Menu Click View and select Taskpad, you will get 3 tabs :
General, Table Info and Wizards, Select Table Info and you'll see all the
tables listed with rows count and size
Mario Aoun
"Mica Cooper" wrote:

> Hi,
> I have a client with a database that is running over 1G in size. Looking at
> the tables, I can't find any that would seem to be larger than a 1-2 meg
> with total size should be around 30meg. Is there any way to find out table
> sizes?
> Thanks,
> Mica
>
>
|||Use sp_spaceused or see: http://vyaskn.tripod.com/sp_show_biggest_tables.htm
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
Mica
|||Database size is a fuzzy thing. I don't know your level of SQL experience,
so forgive me if I start off with some basics.
What do you mean, when you say "over 1GB in size"? Right click on the
database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go to
properties. On the first tab ("General"), there's a "Size", and then a
"Space Available". Is this the 1GB you're talking about? What does the
"Space Available" have beside it?
If that is the size that you're talking about, that includes the log file as
well as the actual data file. Click on the next tab "Data Files", and see
how much space is allocated. Then go to the "Transaction Log" tab, and see
the same thing there. If the transaction log is huge, it may be as simple
as either adjusting your backup procedures, or changing the "Recovery Model"
on the "Options" tab.
The other thing that can happen is that SQL database grow automatically, but
the default settings do not let the databases shrink automatically. To
shrink a database, right click on the database, and go to "All Tasks", then
"Shrink Database". I seem to have the best luck with shrinking by clicking
on the "Files" button on the first form, then manually setting the desired
database file sizes by selecting first the data (.mdf) file, then the log
(.ldf) file. You likely have to either do a complete backup or change the
"Recovery Model" before you'll get a lot of space out of the log file.
To find out the actual size of a particular table, you can execute the
"sp_spaceused <tablename>" function in Query Analyzer. It will give you the
number of rows, reserved size, data size, index size, and unused size.
I hope this helped! I'm not a SQL expert, and I don't play one on TV. But
post back with your results and any new questions, if you need more
guidance.
Clint
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a client with a database that is running over 1G in size. Looking
> at the tables, I can't find any that would seem to be larger than a 1-2
> meg with total size should be around 30meg. Is there any way to find out
> table sizes?
> Thanks,
> Mica
>
|||Mario,
That is COOL! In all the years I've used SQL Server I have never seen that.
Its exactly what I was looking for.
Mica
"Mario Aoun" <MarioAoun@.discussions.microsoft.com> wrote in message
news:8AD1DCE1-0E27-463B-BE9E-59D00D5137C2@.microsoft.com...[vbcol=seagreen]
> Hi Mica Cooper,
> To Check table sizes, Select the db name in the enterprise under
> Databases,
> In the Main Menu Click View and select Taskpad, you will get 3 tabs :
> General, Table Info and Wizards, Select Table Info and you'll see all the
> tables listed with rows count and size
> Mario Aoun
> "Mica Cooper" wrote:
|||Clint,
I have already shrank the db and it didn't change the size. I am looking for
a problem becuase I know the db shouldn't be anywhere near that large and
this info should help. Mario's post was great. I have NEVER seen that before
and it told the story pretty quick.
So guess what? Look at the view told me what a dunce I am. The problem is
the log file is autmatically growing...1.3G. I should have seen that by
looking at the .LDF. Gotta go fix that now.
Thanks Guys,
Mica
"Clint" <nobody@.nowhere.non> wrote in message
news:O6TXngrkFHA.1044@.tk2msftngp13.phx.gbl...
> Database size is a fuzzy thing. I don't know your level of SQL
> experience, so forgive me if I start off with some basics.
> What do you mean, when you say "over 1GB in size"? Right click on the
> database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go
> to properties. On the first tab ("General"), there's a "Size", and then a
> "Space Available". Is this the 1GB you're talking about? What does the
> "Space Available" have beside it?
> If that is the size that you're talking about, that includes the log file
> as well as the actual data file. Click on the next tab "Data Files", and
> see how much space is allocated. Then go to the "Transaction Log" tab,
> and see the same thing there. If the transaction log is huge, it may be
> as simple as either adjusting your backup procedures, or changing the
> "Recovery Model" on the "Options" tab.
> The other thing that can happen is that SQL database grow automatically,
> but the default settings do not let the databases shrink automatically.
> To shrink a database, right click on the database, and go to "All Tasks",
> then "Shrink Database". I seem to have the best luck with shrinking by
> clicking on the "Files" button on the first form, then manually setting
> the desired database file sizes by selecting first the data (.mdf) file,
> then the log (.ldf) file. You likely have to either do a complete backup
> or change the "Recovery Model" before you'll get a lot of space out of the
> log file.
> To find out the actual size of a particular table, you can execute the
> "sp_spaceused <tablename>" function in Query Analyzer. It will give you
> the number of rows, reserved size, data size, index size, and unused size.
> I hope this helped! I'm not a SQL expert, and I don't play one on TV.
> But post back with your results and any new questions, if you need more
> guidance.
> Clint
> "Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
> news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
>

Determine table size

Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
MicaHi Mica Cooper,
To Check table sizes, Select the db name in the enterprise under Databases,
In the Main Menu Click View and select Taskpad, you will get 3 tabs :
General, Table Info and Wizards, Select Table Info and you'll see all the
tables listed with rows count and size
Mario Aoun
"Mica Cooper" wrote:
> Hi,
> I have a client with a database that is running over 1G in size. Looking at
> the tables, I can't find any that would seem to be larger than a 1-2 meg
> with total size should be around 30meg. Is there any way to find out table
> sizes?
> Thanks,
> Mica
>
>|||Use sp_spaceused or see: http://vyaskn.tripod.com/sp_show_biggest_tables.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
Mica|||Database size is a fuzzy thing. I don't know your level of SQL experience,
so forgive me if I start off with some basics.
What do you mean, when you say "over 1GB in size"? Right click on the
database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go to
properties. On the first tab ("General"), there's a "Size", and then a
"Space Available". Is this the 1GB you're talking about? What does the
"Space Available" have beside it?
If that is the size that you're talking about, that includes the log file as
well as the actual data file. Click on the next tab "Data Files", and see
how much space is allocated. Then go to the "Transaction Log" tab, and see
the same thing there. If the transaction log is huge, it may be as simple
as either adjusting your backup procedures, or changing the "Recovery Model"
on the "Options" tab.
The other thing that can happen is that SQL database grow automatically, but
the default settings do not let the databases shrink automatically. To
shrink a database, right click on the database, and go to "All Tasks", then
"Shrink Database". I seem to have the best luck with shrinking by clicking
on the "Files" button on the first form, then manually setting the desired
database file sizes by selecting first the data (.mdf) file, then the log
(.ldf) file. You likely have to either do a complete backup or change the
"Recovery Model" before you'll get a lot of space out of the log file.
To find out the actual size of a particular table, you can execute the
"sp_spaceused <tablename>" function in Query Analyzer. It will give you the
number of rows, reserved size, data size, index size, and unused size.
I hope this helped! I'm not a SQL expert, and I don't play one on TV. But
post back with your results and any new questions, if you need more
guidance.
Clint
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a client with a database that is running over 1G in size. Looking
> at the tables, I can't find any that would seem to be larger than a 1-2
> meg with total size should be around 30meg. Is there any way to find out
> table sizes?
> Thanks,
> Mica
>|||Mario,
That is COOL! In all the years I've used SQL Server I have never seen that.
Its exactly what I was looking for.
Mica
"Mario Aoun" <MarioAoun@.discussions.microsoft.com> wrote in message
news:8AD1DCE1-0E27-463B-BE9E-59D00D5137C2@.microsoft.com...
> Hi Mica Cooper,
> To Check table sizes, Select the db name in the enterprise under
> Databases,
> In the Main Menu Click View and select Taskpad, you will get 3 tabs :
> General, Table Info and Wizards, Select Table Info and you'll see all the
> tables listed with rows count and size
> Mario Aoun
> "Mica Cooper" wrote:
>> Hi,
>> I have a client with a database that is running over 1G in size. Looking
>> at
>> the tables, I can't find any that would seem to be larger than a 1-2 meg
>> with total size should be around 30meg. Is there any way to find out
>> table
>> sizes?
>> Thanks,
>> Mica
>>|||Clint,
I have already shrank the db and it didn't change the size. I am looking for
a problem becuase I know the db shouldn't be anywhere near that large and
this info should help. Mario's post was great. I have NEVER seen that before
and it told the story pretty quick.
So guess what? Look at the view told me what a dunce I am. The problem is
the log file is autmatically growing...1.3G. I should have seen that by
looking at the .LDF. Gotta go fix that now.
Thanks Guys,
Mica
"Clint" <nobody@.nowhere.non> wrote in message
news:O6TXngrkFHA.1044@.tk2msftngp13.phx.gbl...
> Database size is a fuzzy thing. I don't know your level of SQL
> experience, so forgive me if I start off with some basics.
> What do you mean, when you say "over 1GB in size"? Right click on the
> database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go
> to properties. On the first tab ("General"), there's a "Size", and then a
> "Space Available". Is this the 1GB you're talking about? What does the
> "Space Available" have beside it?
> If that is the size that you're talking about, that includes the log file
> as well as the actual data file. Click on the next tab "Data Files", and
> see how much space is allocated. Then go to the "Transaction Log" tab,
> and see the same thing there. If the transaction log is huge, it may be
> as simple as either adjusting your backup procedures, or changing the
> "Recovery Model" on the "Options" tab.
> The other thing that can happen is that SQL database grow automatically,
> but the default settings do not let the databases shrink automatically.
> To shrink a database, right click on the database, and go to "All Tasks",
> then "Shrink Database". I seem to have the best luck with shrinking by
> clicking on the "Files" button on the first form, then manually setting
> the desired database file sizes by selecting first the data (.mdf) file,
> then the log (.ldf) file. You likely have to either do a complete backup
> or change the "Recovery Model" before you'll get a lot of space out of the
> log file.
> To find out the actual size of a particular table, you can execute the
> "sp_spaceused <tablename>" function in Query Analyzer. It will give you
> the number of rows, reserved size, data size, index size, and unused size.
> I hope this helped! I'm not a SQL expert, and I don't play one on TV.
> But post back with your results and any new questions, if you need more
> guidance.
> Clint
> "Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
> news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I have a client with a database that is running over 1G in size. Looking
>> at the tables, I can't find any that would seem to be larger than a 1-2
>> meg with total size should be around 30meg. Is there any way to find out
>> table sizes?
>> Thanks,
>> Mica
>sql

Determine table size

Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
MicaHi Mica Cooper,
To Check table sizes, Select the db name in the enterprise under Databases,
In the Main Menu Click View and select Taskpad, you will get 3 tabs :
General, Table Info and Wizards, Select Table Info and you'll see all the
tables listed with rows count and size
Mario Aoun
"Mica Cooper" wrote:

> Hi,
> I have a client with a database that is running over 1G in size. Looking a
t
> the tables, I can't find any that would seem to be larger than a 1-2 meg
> with total size should be around 30meg. Is there any way to find out table
> sizes?
> Thanks,
> Mica
>
>|||Use sp_spaceused or see: http://vyaskn.tripod.com/sp_show_biggest_tables.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
Mica|||Database size is a fuzzy thing. I don't know your level of SQL experience,
so forgive me if I start off with some basics.
What do you mean, when you say "over 1GB in size"? Right click on the
database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go to
properties. On the first tab ("General"), there's a "Size", and then a
"Space Available". Is this the 1GB you're talking about? What does the
"Space Available" have beside it?
If that is the size that you're talking about, that includes the log file as
well as the actual data file. Click on the next tab "Data Files", and see
how much space is allocated. Then go to the "Transaction Log" tab, and see
the same thing there. If the transaction log is huge, it may be as simple
as either adjusting your backup procedures, or changing the "Recovery Model"
on the "Options" tab.
The other thing that can happen is that SQL database grow automatically, but
the default settings do not let the databases shrink automatically. To
shrink a database, right click on the database, and go to "All Tasks", then
"Shrink Database". I seem to have the best luck with shrinking by clicking
on the "Files" button on the first form, then manually setting the desired
database file sizes by selecting first the data (.mdf) file, then the log
(.ldf) file. You likely have to either do a complete backup or change the
"Recovery Model" before you'll get a lot of space out of the log file.
To find out the actual size of a particular table, you can execute the
"sp_spaceused <tablename>" function in Query Analyzer. It will give you the
number of rows, reserved size, data size, index size, and unused size.
I hope this helped! I'm not a SQL expert, and I don't play one on TV. But
post back with your results and any new questions, if you need more
guidance.
Clint
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a client with a database that is running over 1G in size. Looking
> at the tables, I can't find any that would seem to be larger than a 1-2
> meg with total size should be around 30meg. Is there any way to find out
> table sizes?
> Thanks,
> Mica
>|||Mario,
That is COOL! In all the years I've used SQL Server I have never seen that.
Its exactly what I was looking for.
Mica
"Mario Aoun" <MarioAoun@.discussions.microsoft.com> wrote in message
news:8AD1DCE1-0E27-463B-BE9E-59D00D5137C2@.microsoft.com...[vbcol=seagreen]
> Hi Mica Cooper,
> To Check table sizes, Select the db name in the enterprise under
> Databases,
> In the Main Menu Click View and select Taskpad, you will get 3 tabs :
> General, Table Info and Wizards, Select Table Info and you'll see all the
> tables listed with rows count and size
> Mario Aoun
> "Mica Cooper" wrote:
>|||Clint,
I have already shrank the db and it didn't change the size. I am looking for
a problem becuase I know the db shouldn't be anywhere near that large and
this info should help. Mario's post was great. I have NEVER seen that before
and it told the story pretty quick.
So guess what? Look at the view told me what a dunce I am. The problem is
the log file is autmatically growing...1.3G. I should have seen that by
looking at the .LDF. Gotta go fix that now.
Thanks Guys,
Mica
"Clint" <nobody@.nowhere.non> wrote in message
news:O6TXngrkFHA.1044@.tk2msftngp13.phx.gbl...
> Database size is a fuzzy thing. I don't know your level of SQL
> experience, so forgive me if I start off with some basics.
> What do you mean, when you say "over 1GB in size"? Right click on the
> database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go
> to properties. On the first tab ("General"), there's a "Size", and then a
> "Space Available". Is this the 1GB you're talking about? What does the
> "Space Available" have beside it?
> If that is the size that you're talking about, that includes the log file
> as well as the actual data file. Click on the next tab "Data Files", and
> see how much space is allocated. Then go to the "Transaction Log" tab,
> and see the same thing there. If the transaction log is huge, it may be
> as simple as either adjusting your backup procedures, or changing the
> "Recovery Model" on the "Options" tab.
> The other thing that can happen is that SQL database grow automatically,
> but the default settings do not let the databases shrink automatically.
> To shrink a database, right click on the database, and go to "All Tasks",
> then "Shrink Database". I seem to have the best luck with shrinking by
> clicking on the "Files" button on the first form, then manually setting
> the desired database file sizes by selecting first the data (.mdf) file,
> then the log (.ldf) file. You likely have to either do a complete backup
> or change the "Recovery Model" before you'll get a lot of space out of the
> log file.
> To find out the actual size of a particular table, you can execute the
> "sp_spaceused <tablename>" function in Query Analyzer. It will give you
> the number of rows, reserved size, data size, index size, and unused size.
> I hope this helped! I'm not a SQL expert, and I don't play one on TV.
> But post back with your results and any new questions, if you need more
> guidance.
> Clint
> "Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
> news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
>