March 14, 2005 at 4:58 pm
Hi All,
I have a database which grows at about 350MB per week. The database is set to simple recovery mode, and it is currently set to auto grow at a rate of 50MB. Ideally I want to grow this database once a week rather than let it autogrow during business hours. Does anyone have a suggested approach? Or should I simply set the auto grow feature to 350MB so it autogrows less frequently?
Thanks,
Terry
March 14, 2005 at 10:56 pm
You can make a TSQL Statement to grow the database and schedule it with a job. If you do this, I would choose for once a month with 1400Mb
But... I think it is better to let grow the database every month or even every year. That way you have less fragmentation on your harddisk.
March 15, 2005 at 8:22 am
I always leave the autogrow turned on (just in case) but manually grow the database every 6 months (with enough free space to last) and then do a defrag, allowing it to autogrow as needed will cause a fair amount of fragmentation which is something that you don't want.
If you don't have enough disk space for 6 months worth of growth then you're going to need to do something about it PDQ so it's also a good sanity check on your servers.
March 15, 2005 at 9:06 am
Hi,
i'm using a job that every night looks at the actual filesize and grows the database if less 1 GB is free...
(comments are in german, but you should easily get what i'm doing here 😉 )
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[database_sizes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[database_sizes]
GO
CREATE TABLE [dbo].[database_sizes] (
[datum] [datetime] NULL ,
[fileid] [int] NULL ,
[filegroup] [int] NULL ,
[totalextents] [int] NULL ,
[usedextents] [int] NULL ,
[name] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
[filename] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
[db] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
/* ---------------------------------------------------------------------------*/
-- sp_maintenance.sql
-- T-SQL-Script zum Ausführen von Optimierungen auf allen Datenbanken eines
-- beliebigen Microsoft-SQL-Servers (2000)
-- Erstellt am: 13.10.2004
-- Author: Karl Klingler, karl.klingler@dr-stahl.de
/* ---------------------------------------------------------------------------*/
/* ***************************************************************
Tägliches Abfragen der Datenbank-Dateigroessen und Wegschreiben in
eine Protokolltabelle
Vergroessern von Datenbankdateien wenn dies benoetigt wird.
Author: Karl Klingler
Datum: 07.02.2005
*/
CREATE PROCEDURE sp_maintenance
AS
-- Variablen deklarieren
DECLARE @dbname sysname
declare @free float, @next float
declare @cmd varchar(4000)
declare @msg varchar(1000)
SELECT @msg = convert(char(25),getdate(),113)+':+++++++++++++++++++++++ Begin der Wartung ++++++++++++++++++++++++++'
RAISERROR(@msg,10,1) WITH NOWAIT
-- temporaere Tabelle erstellen
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(1024),
filename varchar(1024)
)
-- Serverkonfig auslesen und in Logdatei anzeigen
PRINT convert(char(25),getdate(),113)+': Serverkonfiguration anzeigen...'
execute sp_configure
-- Datenbankinfos ausgeben
execute sp_helpdb
-- Cursor zum Auslesen der DB-Namen
DECLARE dbnames_cursor CURSOR
FOR
SELECT name
FROM master.dbo.sysdatabases
ORDER BY dbid DESC
OPEN dbnames_cursor
-- Alle DB-Namen auslesen und die Befehle jeweils für jede DB ausführen
FETCH NEXT FROM dbnames_cursor INTO @dbname
WHILE (@@FETCH_STATUS -1) BEGIN
IF (@@FETCH_STATUS -2) BEGIN
SELECT @dbname = RTRIM(@dbname)
select @msg = convert(char(25),getdate(),113)+':######## Führe Wartung für Datenbank '+@dbname+' durch...'
RAISERROR(@msg,10,1) WITH NOWAIT
select @msg = convert(char(25),getdate(),113)+': dbcc checkdb...'
RAISERROR(@msg,10,1) WITH NOWAIT
-- Datenbank-Integrität prüfen
dbcc checkdb (@dbname) with no_infomsgs --, tablock
select @msg = convert(char(25),getdate(),113)+': dbcc opentran...'
RAISERROR(@msg,10,1) WITH NOWAIT
-- Auf noch offene Transaktionen abprüfen
dbcc opentran (@dbname) with tableresults, no_infomsgs
select @msg = convert(char(25),getdate(),113)+': exec sp_spaceused...'
RAISERROR(@msg,10,1) WITH NOWAIT
-- Die Größe der Indizes in den Systemtabellen korrigieren (führt gleichzeitig "dbcc updateusage" aus)
EXEC ('use ['+@dbname+'] execute sp_spaceused @updateusage=''true'' use master')
--dbcc updateusage (@dbname)
select @msg = convert(char(25),getdate(),113)+': exec sp_dboption...'
RAISERROR(@msg,10,1) WITH NOWAIT
-- Ausgabe der Datenbank-Optionen
execute sp_dboption @dbname
-- Dateieigenschaften (Größe, Belegung)abfragen
EXEC ('use ['+@dbname+'] insert into #tmp_sfs exec (''dbcc showfilestats with no_infomsgs'')')
-- Mit Datum und db-Name in Protokolldatei schreiben
insert into management.dbo.database_sizes select getdate(),*, @dbname from #tmp_sfs
-- temporaere Tabelle leeren
delete from #tmp_sfs
END
select @msg = convert(char(25),getdate(),113)+':++++++++ Wartung für Datenbank '+@dbname+' fertig'
RAISERROR(@msg,10,1) WITH NOWAIT
WAITFOR DELAY '00:00:05'
FETCH NEXT FROM dbnames_cursor INTO @dbname
END
-- Cursor schliessen und löschen
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
-- temporaere Tabelle löschen
drop table #tmp_sfs
-- Freien Platz in der Navision-Datenbankdatei feststellen
select @free = ( select top 1 cast((totalextents - usedextents) * 64 / 1024 as float)
from management.dbo.database_sizes
where name = 'SITESQL_1_DATA' and db = 'SITESQL'
order by datum desc)
-- MB-Zahl fuer aktuelle Dateigroesse berechnen
select @next = ( select top 1 cast((totalextents) * 64 / 1024 as float)
from management.dbo.database_sizes
where name = 'SITESQL_1_DATA' and db = 'SITESQL'
order by datum desc)
-- Wenn zu wenig Platz frei Datei vergroessern
if (@free) < 1024 BEGIN
select @msg = convert(char(25),getdate(),113)+': DB-Größe ist ' + convert(varchar(20),@next) + ' MB. Freier Platz in der DB ist ' + convert(varchar(20),@free) + ' MB, vergrössern ist notwendig!'
RAISERROR(@msg,10,1) WITH NOWAIT
-- Naechste GB-runde MB-Zahl fuer Dateigroesse berechnen
select @next = (floor(@next /1024) + 1) * 1024
select @msg = convert(char(25),getdate(),113)+': Neue DB-Größe ist ' + convert(varchar(20),@next) + ' MB.'
RAISERROR(@msg,10,1) WITH NOWAIT
-- DB-Datei vergroessern
select @msg = convert(char(25),getdate(),113)+': alter database ''SITESQL'' MODIFY FILE (NAME = SITESQL_1_DATA, SIZE = ' + convert(varchar(20),@next) + 'MB)'
RAISERROR(@msg,10,1) WITH NOWAIT
select @cmd = 'alter database SITESQL
MODIFY FILE
(NAME = SITESQL_1_DATA,
SIZE = ' + convert(varchar(20),@next) + 'MB)'
-- Befehl ausführen (noch nicht!)
exec ( @cmd )
END ELSE BEGIN
select @msg = convert(char(25),getdate(),113)+': DB-Größe ist ' + convert(varchar(20),@next) + ' MB. Freier Platz in der DB ist ' + convert(varchar(20),@free) + ' MB, vergrössern ist nicht notwendig!'
RAISERROR(@msg,10,1) WITH NOWAIT
END
PRINT convert(char(25),getdate(),113)+':++++++++++ Ende der Wartung +++++++++++
'
/* ####################### fertsch ############################ */
GO
Best regards
karl
March 16, 2005 at 5:15 pm
Thanks for the responses! Since I was already taking file growth measurements I implemented a process to notify me when free space dropped below a certain level so I can manually grow the database.
Regards,
Terry
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply