Database space size increased on deleting records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Database space size increased on deleting records

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2008-02-20 : 12:46:47

Hi,

I was running out of space and thus deleted some rows from a table. To my surprise the db size increased. I then shrunk it to bring it back to what it was earlier.

When i deleted some 5000 rows, some space must have been released. Where did the space go and why did the db size increase after deleting the records?

I thght it might be log files..but db is set to Simple Recovery which does not utilize a Log File.

Any reasons?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-02-20 : 12:49:52

Always deleting records will increase the size of log file whether full or simple recovery model. As you are in simple recovery model, try to shrink the log files .

lols
Posting Yak Master

174 Posts

Posted - 2008-02-20 : 22:02:21

When i wrote to my hosting providers..they said that i am not using a log file so they cannot shrink it..

anyway that i can do it using sql queries..how do i find the name of my log file and how can i shrink it using sql query..the UI is disabled and does not show me the db and log file name

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-20 : 22:38:04

You can get file names by 'select * from sysfiles' and shrink it with 'dbcc shrinkfile'.

lols
Posting Yak Master

174 Posts

Posted - 2008-02-21 : 05:29:14

Hi,
~~Can i also get the log files using this command?~~

I ran the command and these are the files i get:

1	1	25160	28800	128	2	0	ASPNETDB_DAT_DAT	D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\dnc\ASPNETDB.MDF
2	0	63	12800	128	66	0	ASPNETDB.MDF_log	D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\dnc\ASPNETDB_log.ldf
3	0	256	12800	1	1048642	0	dnc_log2	D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\dnc\dnc_log2.ldf

What do these nos. mean. I check this article

http://msdn2.microsoft.com/en-us/library/ms178009.aspx

but could not make out much of it. Out of all this '1048642' is the highest no. What does it mean?

lols
Posting Yak Master

174 Posts

Posted - 2008-02-21 : 07:01:35

Hi,

Another thing. I have two main tables which are taking up the most space

name             rows        reserved          data              index_size            unused 

aspnet_Users 79225       48280 KB          23080 KB            25080 KB                    120 KB 

aspnet_Profile 69228       132680 KB      131456 KB              568 KB                     656 KB

Anything that you find odd? Any suggestions to reduce space?

thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-21 : 15:22:51

>> What do these nos. mean.

Column header tells you.

>> Anything that you find odd?

Not much. You can try rebuild clustered index on those tables to defrag them then shrink files.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources