All the database files are set to autogrowth to 10% from the initial sizes. But,the auto growth can happen any time during business hours causing the database slow down. Therefore I am taking the following approach -
1 I have set up a sql job ( calls a script listed here) that logs the current datafile and the log file size in a table in interval of 4 hours daily. I use that historical data to research when the data file needs to grow.
2. Then I run this script from the table to find which database file has more than 95% filled up and those are the candidate for me to manually resize ( expand) the data file.
3. I use the follwing script to resize the datafile
use db1
Go
---select * from sys.sysfiles
alter database client_343
modify file
(
name = client_343,
size = 1024MB
);
This blog is created to add all the information and experiences that I have learnt while working on MS sql server as a DBA. The blog is also updated frequently with the real world problem that I encounter hands on at work and the resolutions to resolve them. I hope this blog will be of some use to you and you will revisit. Thank you for stopping by and you are welcome to leave comments.
Tuesday, October 7, 2014
Modifying database file
Labels:
Administration,
autogrowth,
database growth,
datafile,
resize database,
sysfiles
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment