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 .
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
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
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.
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?
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 ??
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
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
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.
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.
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
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.
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?
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,
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.
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
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.
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?
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.
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?
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 ?
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
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.
