Maintenance question - SQL Server Forums

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 2000 Forums

SQL Server Administration (2000)

Maintenance question

Author

Topic

keithc1
Yak Posting Veteran

88 Posts

Posted - 2004-06-22 : 13:57:21

Hi, I have a DB that is being used for firewall logging. I initially set the DB size to 300 meg with a MAX size at 2gig.

The DB as it is now is at 1.85 gig and will be Max'd very soon.
I had some questions that maybe someone can help me with. BTW this is just a home sql non production box that i setup to help me learn.

1)If i knew at the time how big this DB was going to get should I have set its initial size to be larger?
2)What will be the effect when the DB grows to MAXSIZE?
3)Is there anyway to shrink a Database that is packed with data?
(I still am uncertain if DBCC shrinkDB is effective in anyway for DB's that have data and am confused by it's use. Seems like its use is restricted by how much data is in the database)
4)I guess what I'd like to hear is what one should do when a situation like this occurrs.

Thanks

Keithc MCSE MCSA

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-06-22 : 14:05:28

Why is the maxsize 2 Gig?

Can some of the data be transfered to a history db after a period?

Jim
Users <> Logic

keithc1
Yak Posting Veteran

88 Posts

Posted - 2004-06-22 : 15:01:15

I max sized it because there is intense logging to the DB and i don't want it consuming my disk space, can you elaborate more on how I would go about transfering to a history DB? The data here is not important, it is just a "LAb" sql box, I am just trying to learn.

Keithc MCSE MCSA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-22 : 15:05:08

When the DB grows to MAXSIZE, your application will receive errors. You can only shrink a database if there is free space.

Tara

keithc1
Yak Posting Veteran

88 Posts

Posted - 2004-06-22 : 16:43:48

So if for example my DB is 800 megs full and maxsize is 1000 then I can shrink the database because I have 200 megs free?

Keithc MCSE MCSA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-22 : 16:47:22

Yes you can. But I wouldn't suggest it. Only shrink the database if you aren't going to use the space. You receive a performance hit when the database has to expand.

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-06-22 : 17:50:51

When you say firewall logging are we talking just a history of traffic or what?

Jim
Users <> Logic

keithc1
Yak Posting Veteran

88 Posts

Posted - 2004-06-22 : 17:58:25

Yes Jim, Packet level logging of any traffic that hits the external interface of my network, source/destination IP's along with numerous other options that I log as well as any outbound access using application level filtering. I am just not sure what to do once maxsize has been reached in the database, i have no idea what steps i should take once this occurrs.

Keithc MCSE MCSA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-22 : 17:59:57

You can just change the setting in Enterprise Manager. Right click on the db in EM, go to properties. Change it in there.

Or use ALTER DATABASE in Query Analyzer.

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-06-22 : 18:04:03

I would do this on a time basis

Ask yourself
How much of a time window do you need to keep in this table?

Do I need direct access to all of this time or can I use Getdate() and create table to make history tables to dump the idle data into untill you back it up and delete it.

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-22 : 18:08:55

I agree. You probably don't care about the old data. Here's some code to help you delete the data slowly so that you don't impact the app:



SET NOCOUNT ON

DECLARE @RowCnt INT

SET @RowCnt = 0

SET ROWCOUNT 5000

WHILE @RowCnt > 0
BEGIN

	DELETE 
	FROM SomeTable
	WHERE SomeColumn < GETDATE() - 14

	SET @RowCnt = @@ROWCOUNT

	WAITFOR DELAY '00:00:05'

END

SET ROWCOUNT 0

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-06-22 : 18:13:38

Neet Idea Tara

Jim
Users <> Logic

keithc1
Yak Posting Veteran

88 Posts

Posted - 2004-06-22 : 20:06:27

That is awesome!!! and yes i dont care about the old data as i have procedures set to run on certain fireld to check for suspicious activity in my losgs. Just out of curiosity though, what if the data was extremely important what would you do in that case?

Keithc MCSE MCSA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-22 : 20:10:47

I would move the data to History tables to save performance on the main tables although it wouldn't solve your maxsize problem. To solve that problem, I would add disk space and increase the maxsize setting.

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-06-23 : 07:32:46

Or you could send it to a Seperate History DB.

Jim
Users <> Logic

mr_mist
Grunnio

1870 Posts

Posted - 2004-06-23 : 07:43:41

quote:
I still am uncertain if DBCC shrinkDB is effective in anyway for DB's that have data and am confused by it's use.

The purpose of shrinkdb is to remove empty space from database files. It wont compress database data.

-------
Moo. :)

keithc1
Yak Posting Veteran

88 Posts

Posted - 2004-06-23 : 09:04:13

Thanks Mist, It is unbeleiveable how hard it was just to get that answer. Is there a way to compress the data?

Keithc MCSE MCSA

mr_mist
Grunnio

1870 Posts

Posted - 2004-06-23 : 09:16:30

Compressing it is not a SQL function, no. You could compress the actual files (in nt) but this is highly not recommended.

-------
Moo. :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-23 : 11:59:16

quote:
Originally posted by keithc1

It is unbeleiveable how hard it was just to get that answer.

So I guess when I said "You can only shrink a database if there is free space" in my first reply that it didn't answer your question?

Tara

keithc1
Yak Posting Veteran

88 Posts

Posted - 2004-06-23 : 15:34:59

I was thinking in terms of data compression to gain more free space not the actual free space like that would be left over with a db creation that would never be used that you wanted to reallocate to the operating system. You answered it fine just at that time it didn't click. Thats where the mix up was and thanks for your help Tara

Keithc MCSE MCSA

keithc1
Yak Posting Veteran

88 Posts

Posted - 2004-06-24 : 00:10:25

Just posting so I get 20 replies and cool looking folder to show it.

Keithc MCSE MCSA

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources