Transact SQL :: Database Size Does Not Decrease After Dropping All The Tables

Oct 15, 2015

I have a database consisting of two main tables and 12 sub tables.

This was leading to increase in database size. So we thought of storing the sub tables data in the main tables in form of xml  in a column of varchar(2000) type.

So we created a new database that only had 2 tables and we stored the data of the sub tables in the new column of the main table.

Surprisingly we saw that the database size increased rather than decreasing .

View 9 Replies


ADVERTISEMENT

How To Decrease The Database File Size

Jul 19, 2007

Hi all,



How to derease the database file size in Primary filegroup.



Thanks,

View 5 Replies View Related

Transact SQL :: Calculate Size Of All Tables In A Database

Nov 16, 2015

I need to build a query to calculate the size of all tables in a database ...

View 5 Replies View Related

Reducing Database Size After Dropping Text Column

Jul 20, 2005

Hello,A while back I dropped a text column from a SQL Server 7 databaseroughly 3GB in size. I expected the size of the database to decreaseby around 1GB, but no change occurred. After searching usenet, Idiscovered that SQL Server 7 has no way of reclaiming that space, butthat there is some command that can be run in SQL Server 2000 thatwill reclaim it.I have since migrated this database to SQL Server 2000, and am nowtrying to figure out what that command is, but cannot locate anyusenet posts about it... also tried searching books online, but can'tfind anything that way either.Does anyone know what I should run?Thanks,Tom

View 6 Replies View Related

How To Decrease Log File Size

Dec 7, 2005

Dear all,

I wanna ask you how to decrease log file size..?
For example i have database file with data file size is 122,166 Kb
and the database log file is 6,330,176 KB

Thanks,

Oko Sakti Banget

View 4 Replies View Related

How To Decrease The Size Of Data File ?

Jul 30, 2007

Hello Guys,

Just wondering if someone can help me decrease the size of mdf and ldf files. In the past production database "NewUniverse" had been allocated space of 100 GB for mdf file and 8 GB of ldf file. However the data file has only used 30 GB of data. But now due to disk space related reason, I tried to decrease the datafile size from 100 GB to 40 GB. But I am not able to do it.

Any help in this regard would be appreciated.

View 4 Replies View Related

SQL Db Size Will Not Decrease After Deleting Data

Jun 6, 2004

Hi

I installed " Web Wiz Forum ASP SQL 2000 DB "

it work fine but
when i added some data in the forum for example my db size is 1.45 MB

after i delete those data the db size will not decrease

is there any code that i must enter on the sql server setup file

Excuse me i asked this question in the web wix forum site but they don`t
answer me

if know what i must to do plz tell me


Thanks

View 6 Replies View Related

How To Decrease The Size Of The Transaction Log Daily?

Jan 23, 2008





I have a database that the transaction log grows about 1 GB per day. I would like this size was decreased daily. Does anyone have any suggestions?
Some friends told me that after the Full backup that is done daily, I should perform a backup of transanction log with option to truncate and after, make a shrink in the database. That is exactly what should be done?

Thank you,

Best Regards,

Ralph Haddad

View 22 Replies View Related

Does DB Size Decrease When I Delete A Huge Table ??

Jan 15, 2004

Hi,
My DB size (Right click on DB Name, Data Files tab, Space Allocated field) was 10914 MB.

I delete a huge table (1.2 million records * 15 columns).
I checked the db size again. It didnt change.
Shouldn't it decrease because I delete a huge table ??

View 14 Replies View Related

Size Of User Tables In A Database

Nov 4, 2003

The following procedure will display the size of all the user tables in a database.

CREATE proc sp_tablesize
as
if exists (select * from sysobjects where name = 'sp_tablesize')
begin
goto calculate_tablesize
end
else
begin
CREATE TABLE #SpaceUsed
(
TableName sysname,
TableRows int,
TableSize varchar(10),
DataSpaceUsed varchar(10),
IndexSpaceUsed varchar(10),
UnusedSpace varchar(10)
)
goto calculate_tablesize
end

calculate_tablesize:
declare @tablename nvarchar(50)
declare @cmd nvarchar(50)

declare c1 cursor for select name from sysobjects where xtype='u'
open c1
fetch c1 into @tablename
while @@fetch_status = 0
begin
set @cmd='exec sp_spaceused['+@tablename+']'
insert into #SpaceUsed exec sp_executesql @cmd
fetch next from c1 into @tablename
end

select * from #SpaceUsed

drop table #SpaceUsed
deallocate c1

View 20 Replies View Related

Check The Size Of All Tables In My Database

Jun 26, 2007



Hi



I am trying to check the size of each table in my database?



SELECT <TableName> , 'Size in bytes/megabytes' FROM DATABASE



I can't for the lif of me figure out how this is done.



Any help would be greatly appreciated



Kind Regards

Carel Greaves

View 10 Replies View Related

T-SQL (SS2K8) :: To Get Size Of Database Based On Tables

Nov 17, 2014

I have 57 tables, 7 views and 1 stored procedure. Just wanted know based on these requirements how can I find the size of the database. Though the DB contains lots of tables, views and procedures. I am moving these details to new DB server. So I need to put right requirements.

View 2 Replies View Related

Database Size Allocation - Where Other Than Tables, Logs, Etc ????

Jul 12, 2007

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

View 1 Replies View Related

Database Size Info Without Cursors Or Temp Tables

Jan 22, 2004

I have seen a bunch of ways to get the size of all the tables within a database posted on this board. I decided to modify an older one I found here (http://www.sqlteam.com/item.asp?ItemID=282). I set it up so there is no cursors or temp tables. Pretty much just one select statement to return all the info you would need. It seems to be faster than anything I have seen so far. Take it for whats its worth. Thanks to the original creator.



/*
Original by: Bill Graziano (SQLTeam.com)
Modified by: Eric Stephani (www.mio.uwosh.edu/stephe40)
*/

declare @low int

select @low = low from
master.dbo.spt_values
where number = 1 and type = 'E'

select o.id, o.name, ro.rowcnt, (r.reserved * @low)/1024 as reserved,
(d.data * @low)/1024 as data, ((i.used-d.data) * @low)/1024 as indexp,
((r.reserved-d.data-(i.used-d.data)) * @low)/1024 as unused
from
sysobjects o

inner join
(select distinct id, rowcnt
from sysindexes
where keys is not null and first != 0) ro on o.id = ro.id

inner join
(select id, sum(reserved) reserved
from sysindexes
where indid in (0, 1, 255)
group by id) r on o.id = r.id

inner join
(select c.id, dpages+isnull(used, 0) data from
(select id, sum(dpages) dpages
from sysindexes
where indid < 2
group by id) c full outer join
(select id, isnull(sum(used), 0) used
from sysindexes
where indid = 255
group by id) t on c.id = t.id) d on r.id = d.id

inner join
(select id, sum(used) used
from sysindexes
where indid in (0, 1, 255)
group by id) i on d.id = i.id


where o.xtype = 'U'

order by reserved desc

View 3 Replies View Related

Impact Of Empty Tables On Database Size And Performance

Jun 21, 2007

Hello All,

When creating my database I have modeled some of the tables after the Adventureworks sample database.

There are some fields or entire tables in Adventureworks that I do not see an imediate use for, however; I would hate to ommit them to find out later they would have been benificial. (.eg territory table).



In general terms what would the impact be on size and performance of a database which contains tables or fields that do not contain data.



Thanks for your help!

View 1 Replies View Related

Transact SQL :: Dropping Clustered Index On Primary Key Which Is In Replication?

Oct 2, 2015

I am trying to drop a primary key on column LID and then create a clustered index on a new identity column ID and then add the primary key back on the LID. I am not able to do so due the table being in replication. here is the error:

Cannot alter the table '' because it is being published for replication.

How do I get past the error and create the Clustered Index on ID column in both publisher and subscriber?

View 2 Replies View Related

Replicating Database Btw Two Sites Why Aren't The Tables Identical In Size?

Apr 30, 2007

I am replicating an 80GB database between NY can CT and would like toknow why table sizes are different between the two.Here is an example of sp_spaceused::NY IOI_2007_04_23 rows(279,664) reserved(464,832)data(439,960) index_size(24,624)CT IOI_2007_04_23 rows(279,666) reserved(542,232)data(493,232) index_size(48,784)Thanks,

View 1 Replies View Related

Dropping Tables

May 28, 2002

Is there a way we can prevent a object owner from dropping his tables. Example:

There is a user called 'tom' who is given create table permissions, 'tom' creates the table completes his dev and then the table is moved into production where 'tom' is still the owner. However, in production, 'tom' does not have the create table privs. Because 'tom' is the owner of the table, he is still able to drop the table in production. This is what I am trying to avoid. I would like to retain 'tom' as the owner and want to take away his create/drop privs.

View 1 Replies View Related

Dropping A Set Of Tables

Feb 22, 2004

Hi
I am using SQL server 7 database and ASP as front end. I run an application where a text file is loaded into database. After this is done procedures are run to create a set of tables that have snapshots of the data in the text file.. each time i load a new text file i want to create the snapshots.. i hve written a stored procedure to create tables and insert values into the tables.. however how do i delete the tables i created the previous time.. the number of snapshots and their names will depend on the size of the text file.. how do i refer to all the snapshots created and drop them all before creating new ones?
plese guide
regds

View 1 Replies View Related

SQL 2012 :: Check Size Of Database After Implementing Data Compression Across All Tables

Dec 4, 2014

I found it pretty interesting. I checked the size of a database, before implementing database compression across all the user tables in a database. And Post implementation of compression too I checked the size of the database.

I did not find any difference. But if I expand the table and check propetires->storage and I can see that PAGE compression is implemented across all the tables, but no compaction in the size of the db. It still remains the same.

View 6 Replies View Related

Problem With Dropping Tables When Set Showplan_all Is On

Feb 22, 2006

I used the set showplan_all on command to get some statistics; I can't use a server trace and I don't like the graphical thingummy.

I ran a sproc that contained three drop table commands; SQL Server refused to drop those tables when set showplan_all was on. I turned it off and it dropped them fine; why can't I drop tables with set showplan_all on?

View 3 Replies View Related

Dropping The Prefix When Querying Tables

Apr 18, 2006

Hi, while I'm trying to do Select statements, is there a way/setting whereby I don't have to constantly prefix the table names with a prefix?
For example, I'm working in multiple environments and they're differentiated by the prefix in their table names, uat.** vs prod.**
Any help would be appreciated.

Please email me at dickson.lau@rogers.com

Thanks again.
Dickson

View 15 Replies View Related

Dropping Statistics From User Tables

Apr 21, 2008

Hi,

I have founde such a nice code to create statements for dropping all statistics in a database.


DECLARE @tblname sysname, @statname sysname, @sql nvarchar(2000)

DECLARE c CURSOR FOR

SELECT object_name(id), name FROM sysindexes WHERE INDEXPROPERTY(id, name, 'IsStatistics') = 1

OPEN c

FETCH NEXT FROM c INTO @tblname, @statname

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql = 'DROP STATISTICS [' + @tblname + '].[' + @statname + ']'

PRINT @sql

FETCH NEXT FROM c INTO @tblname, @statname

END

CLOSE c

DEALLOCATE c



Please help me with changing this code to take care only on indexes from my own tables (no system ones).

Thanks for your help.

Przemo

View 5 Replies View Related

Dropping Global Temp Tables Programatically

Oct 4, 2006

Hi,

I want to drop a Global temp table within a stored procedure.
But first, I want to check it's existence.

So, what I would like to do is something like this

if exists
(select * from sysobjects where name like '##globaltemptable')
drop table ##globaltemptable

But I don't think the global temp tables get stored in sysobjects.

Any suggestions?

Thanks in advance

View 2 Replies View Related

Dropping User Created Tables From Master Db

Aug 25, 2006

Hi,
I have created a bunch of tables in the Master db by mistake.I want to drop those tables.Please tell me away to drop those.

Thanks!!

View 2 Replies View Related

Transact SQL :: Transfer Tables With Data From One Database To Another

Jul 4, 2015

Transfer tables with Data from one database to another one on a same server? 

View 10 Replies View Related

Transact SQL :: Tables And Database Not Detecting Auto While Coding

Aug 27, 2015

When I type Select statemet like below, Tables names are not populate auto. I need to type full lenght instead of selection in list.

Ex: I have few tables and database in my schema and need to select while coding.

select * from (Table Name) When i type first char in the table space it will not populate list of tables in my sql studio. 

View 12 Replies View Related

Transact SQL :: Determine When Database Was Backed Up Without Using MSDB Tables?

Apr 22, 2015

I was wondering if there was another method to determine when a database was last backed up without using the backup history tables in msdb? whether using DBCC DBINFO, DBCC PAGE on a specific database page...etc.

Also, when restoring a database, is there a trace flag you can use to force restore details to be written to the error log?

View 4 Replies View Related

Transact SQL :: Move All Tables In Database From One To Another File  group

Oct 15, 2015

I have to move  all the tables in a database from one file group to another file  group.All my tables have millions of records and the indexes are in correct file group but not the tables. How much time will it take to complete the whole process ?

View 13 Replies View Related

Transact SQL :: Replicating / Synching Data Between Two Tables On Same Database With Live Transactions

Oct 7, 2015

Client is running X- version of application and corresponding database size is huge. Now client's vendor is releasing Y-version of same application with many database schema changes (like new tables added, new columns added, renamed existing columns and etc) To upgrade to the Y-version, vendor is suggesting to my client that down the system and do the upgrade for application/database to Y-version. We are sure that this process will take days together to upgrade to the Y-version. My client is not ready to down the system for that long. So we are trying to find the solution with minimal down time.The approach we are thinking is, 

1) Create the replicated database to another server (server2) from production server(server1) using golden gate with X-version

2) Create new tables/schema updated tables from Y-version database on same server1. Here for  Updated schema tables we are planning to use the name <table_name_Y_version> as the same table name exists in X-version.

3)With above 2 steps, golden gate replicate the changes from production to server1 and server1 will have the new Y-version table schema (with different concatenate name ' _Y_version'). BTW , there is no affect for the production

4) At this stage we are planning to find best approach, to fill the '<table_name>_Y_version' from X-version tables. two challenges here a) all data needs to be moved to Y-version tables b) they have to sync data in real time.

we thought of going to

a) ssis package to pump the data to Y-version tables, but real time data will not sync.

b) trigger based technique, previous experience said, lot of load

c) thinking about sql replication.

View 5 Replies View Related

Transact SQL :: How To Alter Existing Table Column As Identity Without Dropping Table

Nov 20, 2013

I have created a table as below mentioned. Then I want to alter the ID column as identity(1,1) without dropping the table as well as losing the data.

create table dbo.IdentityTest
(
id int not null,
descript varchar(255) null,
T_date datetime not null
)

View 7 Replies View Related

Dropping Database

Dec 21, 1998

Hi,

After I drop the database, I backed up the master database, I recreate the database with new name. When I tried to do the import on the command level,
It gives me the error:
Attempt to locate entry in the sysdatabases for database 'db1' by name,
failed -- no entry found in that name.

What should I do? Thanks.

View 1 Replies View Related

Dropping Database

Sep 7, 2007

Hi friends,

How I can drop a database insatance from a different server ( data base is SQL Server 2005) from my code

Hoping a early reply ....
Thanks :)

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved