• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

How to set the autogrow file name and size in SQL SERVER for my production Database ?

I am using SQL Server 2008 r2. How to  modify  the autogrow file name and size in SQL SERVER  for my production Database  ?
0
Varshini S
Asked:
Varshini S
  • 3
  • 3
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What do you mean with autogrow file name?
You can alter the autogrow configurations in SSMS. Just right-click on the database name that you want and choose properties then files and finally the '...' in autogrowth column and in the file that you want to modify.

Alternative is running T-SQL command. Example:
ALTER DATABASE [My_DB] MODIFY FILE ( NAME = N'My_DB_Filename', FILEGROWTH = 10240KB )

Open in new window

0
 
Varshini SAuthor Commented:
When I execute this query, I can see the FileName in the output. Is it possible to modify the log file name ?

SELECT
    DatabaseName,
    [FileName],
    SPID,
    Duration,
    StartTime,
    EndTime,
    FileType = CASE EventClass WHEN 92 THEN 'Data' ELSE 'Log' END
 FROM sys.fn_trace_gettable(@path, DEFAULT)
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
It's possible but you need to stop the database to do that.

But why you want to change a file name?
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
Varshini SAuthor Commented:
I have some other databases in the same server.
But the log file names are generated with time stamp so I want to change the name to my log file.
0
 
Varshini SAuthor Commented:
Vitor Montalvão ,

For changing the log file should I have to stop SQL server service ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
No. You just need to stop the database that are using the log file. You can't do any operation on files if the database is online.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now